왜 복합 인덱스 순서가 문제인가
복합 인덱스 (a, b, c)는 "a로 정렬하고, 같은 a 안에서 b로 정렬하고, 같은 b 안에서 c로 정렬한" 정렬된 목록이다. 전화번호부가 성 → 이름 순으로 정렬돼 있으면 성만 알아도 찾을 수 있지만 이름만으로는 전체를 훑어야 하는 것과 같다. 컬럼 순서를 잘못 잡으면 인덱스가 있어도 옵티마이저가 이를 활용하지 못하고 풀 스캔으로 떨어진다.
핵심 규칙은 왼쪽 접두어(leftmost prefix)다. (a, b, c) 인덱스는 WHERE a=?, WHERE a=? AND b=?, WHERE a=? AND b=? AND c=?에는 쓰이지만, WHERE b=?나 WHERE c=? 단독에는 쓰이지 못한다.
실제로 어떻게 실패하는가
주문 테이블에서 특정 상태의 최근 주문을 조회한다고 하자.
-- 잘못된 순서: 카디널리티 높은 컬럼을 앞에 둠
CREATE INDEX idx_orders_bad ON orders (created_at, status);
-- 이 쿼리에서 status 조건은 인덱스로 좁혀지지 않는다
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'PENDING'
ORDER BY created_at DESC
LIMIT 20;
status가 접두어의 뒤쪽에 있으면, 등치 조건인 status를 인덱스로 필터링하지 못한다. created_at 범위 없이 정렬만 필요할 때는 인덱스 앞쪽이 created_at이라 정렬은 되지만 status는 인덱스에서 걸러지지 않아 대량 행을 읽고 버린다.
등치 조건 먼저, 범위·정렬은 나중에
설계 원칙은 명확하다. 등치(=) 조건 컬럼을 앞에, 범위(>, <, BETWEEN)와 정렬 컬럼을 뒤에 둔다. 범위 조건 컬럼 이후의 인덱스 컬럼은 정렬·필터에 활용되지 못하기 때문이다.
-- 올바른 순서: 등치(status) → 정렬(created_at)
CREATE INDEX idx_orders_good ON orders (status, created_at DESC);
-- status로 범위를 좁힌 뒤 created_at 정렬을 인덱스가 그대로 제공
SELECT * FROM orders
WHERE status = 'PENDING'
ORDER BY created_at DESC
LIMIT 20;
이제 status='PENDING'으로 인덱스의 특정 구간을 지목하고, 그 구간이 이미 created_at DESC로 정렬돼 있으므로 LIMIT 20만큼만 읽고 멈춘다. 정렬 연산(filesort)도 사라진다.
순서 선택 기준 비교
| 기준 | 앞쪽에 둘 컬럼 | 이유 |
|---|---|---|
| 조건 형태 | 등치(=) | 범위는 뒤쪽 컬럼의 활용을 차단 |
| 정렬 요구 | ORDER BY 컬럼 | 인덱스 순서를 그대로 재사용해 filesort 제거 |
| 선택도 | 등치 조건 중 더 자주 쓰는 컬럼 | 단일 인덱스로 더 많은 쿼리 커버 |
흔한 오해가 "카디널리티 높은 컬럼을 무조건 앞에"인데, 이는 등치 조건일 때만 유효한 보조 기준이다. 조건 형태(등치/범위)와 정렬 요구가 항상 우선한다.
커버링 인덱스로 한 걸음 더
조회 컬럼까지 인덱스에 포함하면 테이블 접근(random I/O)을 없앨 수 있다.
-- PostgreSQL: INCLUDE로 조회 전용 컬럼 추가
CREATE INDEX idx_orders_cover
ON orders (status, created_at DESC)
INCLUDE (amount, customer_id);
-- 아래 쿼리는 테이블을 건드리지 않고 인덱스만으로 처리(Index Only Scan)
SELECT amount, customer_id FROM orders
WHERE status = 'PENDING'
ORDER BY created_at DESC
LIMIT 20;
MySQL(InnoDB)에는 INCLUDE가 없으므로 조회 컬럼을 인덱스 뒤쪽에 직접 나열한다. 다만 인덱스 크기가 커져 쓰기 비용이 오르므로, 실제로 반복되는 쿼리에만 적용한다.
실무 주의점
- 중복 인덱스 정리:
(a)는(a, b)의 접두어이므로 대개 불필요하다. 반면(a, b)는(b, a)를 대체하지 못한다. - 범위 조건은 하나만: 서로 다른 두 컬럼에 범위 조건이 걸리면 인덱스는 첫 범위까지만 효율적이다.
- 함수·형변환 주의:
WHERE DATE(created_at)=?처럼 컬럼을 감싸면 인덱스를 못 쓴다. 범위 조건(created_at >= ? AND < ?)으로 바꾸거나 표현식 인덱스를 만든다. - 정렬 방향 혼합:
ORDER BY a ASC, b DESC는 인덱스도 같은 방향 조합((a ASC, b DESC))이어야 filesort가 사라진다.
설계 전에 반드시 EXPLAIN ANALYZE로 실제 실행 계획을 확인하라. 인덱스는 "만들면 빨라지는 것"이 아니라 "쿼리 패턴에 맞춰야 쓰이는 것"이다.