인덱스

대용량 데이터를 다루는 현대의 데이터베이스 시스템에서 성능은 곧 생산성과 직결됩니다. 수천, 수만 건의 데이터를 대상으로 검색, 정렬, 조인 같은 연산을 빠르게 처리하기 위해서는 단순한 테이블 구조만으로는 한계가 있습니다.
이때, 성능을 획기적으로 개선해주는 중요한 도구가 바로 인덱스(Index) 입니다. 인덱스는 책의 목차처럼, 원하는 데이터를 빠르게 찾을 수 있도록 돕는 구조로, 데이터 검색 속도를 비약적으로 향상시켜줍니다.

 

인덱스

인덱스는 데이터베이스에서 특정 데이터를 빠르게 찾기 위해 사용하는 자료구조

일종의 책갈피라고 생각하시면 됩니다. 어떠한 표시로 인해서 빠르게 탐색이 가능합니다.

 

일반적으로 인덱싱이 걸리지 않은 테이블에서 where name = '개바리' 라고 한다면

db는 해당 테이블의 모든 데이터를 풀스캔 할 것 입니다. 풀스캔은 보통 O(N)의 시간복잡도를 가지게 됩니다.

B-tree 기반 인덱싱을 사용한다면 시간 복잡도를 O(logN) 까지 향상시킬 수 있습니다.

 

 

인덱스 생성 (B-tree)

① 기본 B-tree 인덱스

CREATE INDEX index_name ON table_name (column_name);

 

② 유니크 인덱스

CREATE UNIQUE INDEX index_name ON table_name (column_name);

 

③ 복합 인덱스 (다중 컬럼)

CREATE INDEX index_name ON table_name (column1, column2);

 

 

 

인덱스 조회 (해당 테이블에 인덱스가 있는지 없는지)

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table_name';

 

 

 

B트리 기반 인덱싱 원리

 

트리형태로 표현되며 일반적으로 키(테이블 인덱스 컬럼)을 기준으로 정렬된 상태를 유지합니다.

 

이러한 형태를 가지게 되는데 각 노드는 Key와 레코드의 위치정보를 가지는 포인터를 저장하고 있습니다.

 

SELECT * FROM users WHERE age = 25;

 

이 구문에 대한 동작 예시로는

 

age 컬럼에 B-트리 인덱스가 있다면:

1. 루트 노드부터 25가 있는 리프 노드까지 이동
2. 리프 노드에서 해당 값의 위치를 추적
3. 데이터 페이지에서 실제 row 검색

 

만약 복합인덱스(인덱스 컬럼이 여러개) 라면 어떻게 동작할까?

B-트리의 기준 정렬 순서는 먼저 a → 그다음 b이다.

 

다음과 같이 구성된다.

(a1, b1)  
(a1, b2)  
(a2, b1)  
(a2, b3)  
(a3, b1)

 

a값을 기준으로 정렬한 뒤, b값으로 정렬합니다.

SELECT * FROM users WHERE a = 10 AND b = 95;

 

해당 쿼리는 인덱스를 최대한으로 활용이 가능합니다. 아래와 같이 찾게 됩니다,

a가 10인 노드들을를 찾은 후 그 노드들 중에서도 b가 95인 노드를 정렬순서로  찾게된다.

 

테이블의 형태로 보자면

 

이렇게 되게 된다 a가 10인 노드를 찾고 그 후 아래에서 b=95를 빠르게 탐색한다.

 

그냥 SELECT * FROM users WHERE a = 10;

이렇게 복합키이지만 a로만 활용한 쿼리도 인덱스 사용이 원할하게 적용된다.

 

하지만

SELECT * FROM users WHERE b = 95;

 

해당 쿼리는 인덱싱이 이루어지지 않고 풀스캔이 일어나거나 더욱 안좋은 성능을 낼 수 있다.

이유는 인덱스는 맨처음으로 잡힌 a를 기준으로 정렬하였기 때문에 지표가 없기 때문에 b 만으로는 인덱스 탐색이 불가

 

이러한 원리로 인덱스 사용 순서도 중요하다 

SELECT * FROM users WHERE b = 95 and a = 10;

 

해당 쿼리는 인덱스 활용을 못한다. a를 기준으로 정렬하였기 때문에 순서가 중요하다.

 

 

만약에 인덱스가 중첩으로 걸렸다면 DB는 어떠한 선택을 할까?

DB 옵티마이저는 내부적으로 다양한 비용 기반 분석(cost-based optimization)**을 통해 어떤 인덱스를 사용할지 자동으로 결정

 

SELECT * FROM users WHERE age = 30 AND city = 'Seoul';

 

• age 컬럼에 인덱스 있음 (idx_age)
• city 컬럼에도 인덱스 있음 (idx_city)

age + city에 복합 인덱스가 없을 때,
DB는 각각의 인덱스를 따로 조회한 후 교집합을 취해서 결과를 가져올 수 있음 → 이걸 인덱스 인터섹션이라고 함.

 

age + city에 복합 인덱스가 있고 인덱스가 중첩  될때,

1. idx_age_city (age, city) ← 복합 인덱스
2. idx_city (city) ← 단일 인덱스

일반적으로는 복합 인덱스가 우선선택 된다.

왜냐하면 age + city를 모두 포함하고 있고,
age가 선두 컬럼이므로 정렬/탐색에 유리하기 때문이야.

WHERE city = 'Seoul'만 썼다면 복합 인덱스는 무시된다.

 

 

수동으로 인덱스를 선택하거나 강제하는 방법 (Postgresql 기준)

SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM users WHERE age = 30;

 

SET enable_seqscan = off;  -->  시퀀스 스캔(전체 테이블 스캔)을 비활성화

 

 

mysql에서는 다음과 같이 적용한다.

SELECT * FROM users FORCE INDEX (idx_age) WHERE age = 30;

 

강제로 (FORCE) idx_age라는 인덱스를 사용하도록 명령을 한다.

 

 

인덱스의 종류

 

- Covering Index

쿼리가 필요한 모든 컬럼이 인덱스에 포함되어 있어, 테이블을 따로 읽을 필요가 없는 인덱스

 

이 인덱스에서 해당 데이터튜플에 대한 위치 정보를 찾았다고 했을때,

그 후에 조회테이블로 넘어가서 해당 튜플을 찾고 결과를 반환한다.

하지만 Covering Index 는 조회값들이 이미 인덱스 B트리 노드에서 가지고 있으므로 굳이 조회테이블로의

접근을 안해도된다.  --> 접근이 최소화 되어 비용이 줄어든다.

 

 

- Hash index

 

Hash 함수를 사용해서 값을 인덱싱하는 방식.
주어진 키 값을 해시 함수로 변환하여 저장 → 매우 빠르게 조회 가능!

 

하지만 정렬은 불가능하고 범위 검색도 지원하지 않는다.

해시값을 통하여 equals 기반 검색에 특화 되어있다.

 

제약은 많지만 조회속도는 B-tree 보다도 빠를 수 있다.

 

 

그렇다면 인덱스 많이 걸면 무조건 좋은것 아닌가?

 

❌ “인덱스를 많이 걸면 무조건 좋은 건 아니다!”

 

이유

✔ 쓰기 성능 저하 : INSERT, UPDATE, DELETE 할 때마다 모든 인덱스를 갱신, 인덱스가 많을수록 부하 증가

✔ 옵티마이저 혼란 :  인덱스가 너무 많으면 쿼리 옵티마이저가 선택하는 데 더 많은 시간과 리소스사용

✔ 디스크 공간 낭비 : 인덱스는 실제 데이터와 별도로 저장됨. 너무 많으면 디스크 사용량 급증.

✔ 유지보수 어려움 : 어느 인덱스가 실제로 사용되고 있는지 파악하기 어려워져 관리가 복잡해짐.

 

 

그럼 어느상황에서 인덱스가 유리한가?

 

- WHERE, JOIN, ORDER BY, GROUP BY에 자주 등장하는 컬럼

- 읽기가 위주인 프로세스 (쓰기작업이 많다면 최소한의 인덱스만)