데이터베이스 인덱스를 추가하기 전에 보는 것들

데이터베이스 인덱스를 추가하기 전에 보는 것들 인덱스가 왜 빠른지(B-Tree, O(log n)), 선택도가 낮으면 왜 안 듣는지, 추가 전 무엇을 확인할지를 SQL 예제와 함께 정리한다.

---

들어가며

조회가 느리면 가장 먼저 떠오르는 처방이 인덱스다. 그리고 대개는 효과를 본다. 문제는 인덱스가 공짜가 아니라는 점이다.
읽기를 빠르게 만드는 대신 쓰기를 느리게 하고, 저장 공간을 더 쓴다.
그래서 "느리니까 인덱스 추가"가 아니라, 무엇을 얻고 무엇을 내주는지 알고 추가해야 한다.

인덱스는 검색을 빠르게 만드는 도구지만, 쓰기 성능과 저장 공간을 대가로 가져간다.

이 글에서는 인덱스가 왜 빠른지(B-Tree와 복잡도), 언제 효과가 있는지(선택도), 그리고 추가 전에 무엇을 확인해야 하는지를 SQL 예제와 함께 정리한다.

---

인덱스는 왜 빠른가 — B-Tree와 복잡도

인덱스가 없으면 DB는 원하는 행을 찾기 위해 테이블 전체를 처음부터 끝까지 훑는다. 이걸 풀 테이블 스캔(Full Table Scan) 이라 하고, 행이 $n$개면 비용은 다음과 같다.

$$T_{\text{full scan}} = O(n)$$

대부분의 RDBMS 인덱스는 B-Tree(정확히는 B+Tree) 구조다. 정렬된 트리라서, 찾으려는 값을 루트에서 시작해 단계적으로 좁혀간다. 한 노드가 $b$개의 자식을 가질 수 있다면 탐색 비용은 트리의 높이에 비례한다.

$$T_{\text{index scan}} = O(\log_b n)$$

이 차이는 데이터가 커질수록 극적이다. 예를 들어 행이 100만 개($n = 10^6$)이고 한 노드가 자식을 100개씩 가진다면($b = 100$):

$$\log_{100}(10^6) = 3$$

풀 스캔은 최대 100만 번 읽어야 하지만, B-Tree는 단 3단계만에 도달한다. 인덱스가 빠른 이유가 바로 이것이다.

B-Tree 탐색 (user_id = 42 찾기)

           [50]                  ← 루트: 42 < 50, 왼쪽으로
          /    \
       [20]    [80]              ← 42 > 20, 오른쪽으로
       /  \    /  \
    ...  [30,42,48] ...          ← 리프 노드에서 42 발견

---

카디널리티와 선택도 — 인덱스가 항상 빠른 건 아니다

여기서 중요한 함정이 있다. 인덱스를 탄다고 항상 빠른 게 아니다. 핵심 개념이 선택도(selectivity) 다.

$$\text{선택도} = \frac{\text{고유값(distinct)의 개수}}{\text{전체 행의 개수}}$$

선택도가 1에 가까울수록(고유값이 많을수록) 인덱스가 효과적이다. 반대로 0에 가까우면(중복이 많으면) 인덱스를 타도 결국 많은 행을 읽어야 해서 이득이 적다.

컬럼	고유값	선택도	인덱스 효과
email	행마다 거의 고유	≈ 1.0	매우 좋음
user_id	사용자 수만큼	높음	좋음
status (5종)	5개	낮음	약함
gender	2개	≈ 0.0	거의 무의미

gender처럼 값이 두 종류뿐인 컬럼에 인덱스를 걸면, WHERE gender = 'M' 조건이 전체의 절반을 반환하기 때문에 옵티마이저는 차라리 풀 스캔을 택한다. 선택도가 낮은 컬럼의 단일 인덱스는 추가해도 안 쓰인다.

실무 경험칙으로, 쿼리가 전체 행의 대략 5~10% 이하를 반환할 때 인덱스가 의미 있다.

---

EXPLAIN으로 실제 확인하기

추측 대신 실행 계획을 봐야 한다. EXPLAIN ANALYZE는 옵티마이저가 어떤 경로를 택했고 실제 시간이 얼마나 걸렸는지 보여준다.

인덱스가 없을 때:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

Seq Scan on orders  (cost=0.00..18584.00 rows=12 width=64)
                    (actual time=0.512..142.339 rows=11 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 999989
Planning Time: 0.084 ms
Execution Time: 142.401 ms

Seq Scan(순차 스캔)으로 100만 행을 훑어 999,989행을 버리고 11행만 건졌다. 142ms가 걸렸다.

이제 인덱스를 추가한다.

CREATE INDEX idx_orders_user_id ON orders(user_id);

Index Scan using idx_orders_user_id on orders
                    (cost=0.42..8.59 rows=11 width=64)
                    (actual time=0.031..0.045 rows=11 loops=1)
  Index Cond: (user_id = 42)
Planning Time: 0.121 ms
Execution Time: 0.067 ms

Index Scan으로 바뀌었고 실행 시간이 142ms → 0.067ms로 약 2000배 빨라졌다. Rows Removed by Filter가 사라진 것에 주목하자. 버리는 행 없이 필요한 행만 정확히 읽었다.

---

복합 인덱스 — 컬럼 순서가 전부다

여러 컬럼을 묶는 복합 인덱스(composite index)는 컬럼 순서가 성능을 좌우한다. B-Tree는 왼쪽 컬럼부터 정렬하기 때문에, 맨 왼쪽 컬럼(leftmost prefix) 부터 조건에 써야 인덱스를 탄다.

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

이 인덱스는 다음 쿼리에 모두 효과적이다.

-- O: user_id 단독 (맨 왼쪽 컬럼)
SELECT * FROM orders WHERE user_id = 42;

-- O: user_id + created_at (왼쪽부터 순서대로)
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;

하지만 다음 쿼리는 이 인덱스를 제대로 못 쓴다.

-- X: created_at만 조건 (맨 왼쪽 user_id를 건너뜀)
SELECT * FROM orders WHERE created_at > '2026-01-01';

created_at만으로 조회하려면 인덱스의 첫 컬럼 user_id를 건너뛰게 되는데, B-Tree는 이런 접근을 효율적으로 처리하지 못한다. 그래서 복합 인덱스의 컬럼 순서는 자주 쓰는 조건 → 정렬 조건 순으로, 그리고 선택도가 높은 컬럼을 앞에 두는 것이 일반적이다.

전화번호부가 "성 → 이름" 순으로 정렬돼 있으면 성으로는 빨리 찾지만, 이름만 알 때는 처음부터 다 뒤져야 하는 것과 같다.

---

커버링 인덱스 — 테이블을 아예 안 읽는다

인덱스로 행의 위치를 찾은 뒤, 실제 데이터를 읽으러 테이블에 다시 접근하는 과정을 보통 동반한다. 그런데 쿼리가 필요로 하는 컬럼이 모두 인덱스 안에 있다면, 테이블을 읽지 않고 인덱스만으로 결과를 낼 수 있다. 이걸 커버링 인덱스(covering index) 또는 인덱스 온리 스캔이라 한다.

-- user_id로 조회해서 created_at만 필요한 경우
SELECT created_at FROM orders WHERE user_id = 42;

-- 이 인덱스는 user_id와 created_at을 모두 담고 있다
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at);

Index Only Scan using idx_orders_user_created on orders
  Index Cond: (user_id = 42)

Index Only Scan이 뜨면 테이블 접근을 생략한 것이다. 자주 함께 조회되는 컬럼을 인덱스에 포함시키면 추가 I/O를 줄일 수 있다. (PostgreSQL은 INCLUDE 절로, 검색에는 안 쓰지만 결과에 필요한 컬럼을 따로 담을 수도 있다.)

---

인덱스의 비용 — 공짜가 아니다

여기까지만 보면 인덱스를 많이 만들수록 좋아 보인다. 하지만 인덱스는 분명한 대가가 있다.

1. 쓰기 성능 저하. 행을 INSERT/UPDATE/DELETE할 때마다, 그 테이블에 걸린 모든 인덱스도 함께 갱신해야 한다. 인덱스가 5개면 INSERT 한 번에 인덱스 5개를 수정한다. 대량 배치 입력에서 이 비용이 특히 크다.

2. 저장 공간. 인덱스는 별도의 디스크 공간을 차지한다. 컬럼이 많은 복합 인덱스나 텍스트 컬럼 인덱스는 테이블 못지않게 커지기도 한다.

3. 옵티마이저 부담. 인덱스가 너무 많으면 옵티마이저가 어떤 인덱스를 쓸지 고르는 비용 자체가 늘고, 잘못된 인덱스를 선택할 여지도 생긴다.

그래서 안 쓰는 인덱스는 오히려 해롭다. 실제로 사용되는지 통계로 확인할 수 있다.

-- PostgreSQL: 인덱스 사용 횟수 확인 (idx_scan = 0이면 미사용)
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan ASC;

idx_scan이 0인 인덱스는 한 번도 안 쓰인 것이니 제거 후보다.

---

인덱스를 추가하면 안 되는 경우

다음 상황에서는 인덱스가 득보다 실이 크다.

• 선택도가 낮은 컬럼: gender, status 같이 값 종류가 적은 단일 컬럼
• 쓰기가 압도적으로 많은 테이블: 로그 테이블처럼 INSERT 위주면 인덱스 갱신 비용이 더 큼
• 작은 테이블: 수백 행짜리는 풀 스캔이 더 빠를 수 있음
• 자주 바뀌는 컬럼: UPDATE가 잦은 컬럼은 인덱스도 그만큼 자주 재구성됨

---

적용 전 체크리스트

인덱스를 추가하기 전에 아래를 확인하면 대부분의 실수를 피할 수 있다.

• 느린 쿼리의 EXPLAIN ANALYZE를 캡처했는가 (추측 금지)
• 대상 컬럼의 선택도가 충분히 높은가
• 복합 인덱스라면 컬럼 순서를 쿼리 조건·정렬에 맞췄는가
• 인덱스 추가 후 쓰기 성능도 측정했는가
• 운영 데이터 분포와 비슷한 환경에서 테스트했는가 (개발 DB의 작은 데이터로 판단 금지)
• 기존에 안 쓰이는 인덱스(idx_scan = 0)는 없는가

---

마치며

인덱스는 "느리면 추가하는 것"이 아니라, 읽기 속도와 쓰기 비용·저장 공간 사이의 트레이드오프를 이해하고 선택하는 것이다. 핵심은 세 가지다. B-Tree 덕분에 $O(\log n)$으로 빨라진다는 원리, 선택도가 낮으면 효과가 없다는 점, 그리고 인덱스는 쓰기 비용을 동반한다는 사실.

추측으로 인덱스를 늘리기보다 EXPLAIN ANALYZE로 실제 실행 계획을 보고, 운영과 비슷한 데이터에서 전후를 비교하는 습관이 가장 확실하다. 익숙한 개념일수록 이 작은 판단 차이가 운영 품질을 크게 바꾼다.

---

참고 출처

• PostgreSQL 공식 문서: Indexes — https://www.postgresql.org/docs/current/indexes.html
• Use The Index, Luke! (B-Tree 인덱스 설명) — https://use-the-index-luke.com/
• MySQL: Optimization and Indexes — https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html