[MySQL] 복합 인덱스 설계: 컬럼 순서, 커버링 인덱스, 인덱스 못 타는 조건

[MySQL] 복합 인덱스 설계: 컬럼 순서, 커버링 인덱스, 인덱스 못 타는 조건

인덱스를 걸었는데 안 빨라지는 경우의 대부분은 "잘못 걸어서"예요. 컬럼 순서가 쿼리와 안 맞거나, 조건이 인덱스를 무력화하는 모양이거나요. 앞 편에서 본 B+Tree 구조(정렬 기반 + 두 번 찾기)를 알면, 인덱스 설계는 암기가 아니라 추론이 돼요.

복합 인덱스의 컬럼 순서 원칙(왼쪽 접두·동등 먼저 범위 뒤), 커버링 인덱스, 카디널리티, 그리고 인덱스를 무력화하는 흔한 패턴까지 차례로 풀어요.

01. 복합 인덱스는 전화번호부예요

(성, 이름)으로 정렬된 전화번호부를 떠올리면 복합 인덱스의 모든 규칙이 보여요. (a, b) 인덱스는 a로 정렬하고 a가 같으면 b로 정렬한 거예요.

• WHERE 성='김' AND 이름='지훈' — "김" 구간으로 점프한 뒤 그 안에서 "지훈". 완벽 활용 ⭕
• WHERE 성='김' — 왼쪽 접두만 써도 "김" 구간 전체로 점프 ⭕
• WHERE 이름='지훈' — "지훈"은 김·박·이·최 모든 성에 흩어져 있어요. 탐색 시작점이 없으니 인덱스를 못 타요 ❌

이게 왼쪽 접두(leftmost prefix) 법칙이에요. (a, b, c) 인덱스는 a / a,b / a,b,c 조합의 조건만 활용해요. b나 c만으로는 못 타요. "인덱스가 있는데 안 타요"의 1순위 원인이 이거예요 — 인덱스의 첫 컬럼이 WHERE에 없는 거죠.

02. 동등은 앞에, 범위는 뒤에

두 번째 순서 원칙이에요. 범위 조건(>, BETWEEN, LIKE '...%')이 들어간 컬럼 뒤의 인덱스 컬럼은 탐색에 못 써요.

왜냐면 범위로 잡은 구간 안에서는 그다음 컬럼의 정렬이 흩어져 있거든요. "김~박 구간 전체"에서 이름은 더 이상 한 덩어리가 아니에요. 그래서 (status, created_at, user_id) 인덱스에 status = ? AND created_at > ? AND user_id = ? 쿼리면, user_id는 인덱스 탐색이 아니라 읽으면서 거르는 필터로만 쓰여요. 다만 MySQL 5.6+의 Index Condition Pushdown 덕에 이 필터링은 인덱스 단계에서 처리돼요(Using index condition). 테이블까지 안 가는 것만으로도 이득이에요.

여기서 컬럼 순서 원칙이 나와요. 동등(=) 조건으로 쓰는 컬럼들을 앞에, 범위 조건 컬럼을 맨 뒤에. 위 쿼리라면 (status, user_id, created_at)이 맞는 순서예요. 세 조건이 전부 탐색에 쓰여요.

03. 커버링 인덱스, 두 번 찾기를 없앤다

앞 편에서 본 대로, 세컨더리 인덱스 조회는 "인덱스에서 PK 찾기 → 클러스터드에서 행 가져오기" 두 번이에요. 결과가 많으면 이 왕복이 부담이죠. 그런데 쿼리에 필요한 컬럼이 전부 인덱스 안에 있으면 두 번째 단계가 사라져요.

Querydsl group by 쿼리에 커버링 인덱스를 적용한 기록은 Querydsl 성능개선 3편 (group by, 커버링 인덱스)(2021)에 있어요.

-- 인덱스: (user_id, status, created_at)
SELECT user_id, status, created_at
FROM orders
WHERE user_id = 7 AND status = 'PAID';
-- SELECT·WHERE의 모든 컬럼이 인덱스에 있음
-- → 테이블(클러스터드) 접근 0번. EXPLAIN Extra에 "Using index"

이게 커버링 인덱스예요. 자주 도는 무거운 조회라면, SELECT 컬럼 한두 개를 인덱스 끝에 추가해 커버링으로 만드는 게 강력한 튜닝이에요. 세컨더리 리프에 PK가 들어 있으니 PK 컬럼은 공짜로 커버돼요.

단, 커버링을 노리고 인덱스에 컬럼을 계속 붙이면 인덱스가 비대해져요 — 쓰기 비용과 메모리를 그만큼 내는 거예요. "가장 자주, 가장 무겁게 도는 쿼리"에만 커버링을 설계하고 화면 하나를 위해 컬럼 7개짜리 인덱스를 만드는 건 보통 과해요.

04. 카디널리티, 거른 만큼 가치 있다

어떤 컬럼에 인덱스를 걸 가치가 있느냐는 카디널리티(고유값 수)로 가늠해요. user_id처럼 고유값이 많은 컬럼은 조건 하나로 대부분을 걸러내니 인덱스 가치가 커요. 반대로 성별(2~3개)이나 boolean 플래그는 인덱스를 타도 절반밖에 못 거르니, 두 번 찾기 비용까지 생각하면 풀 스캔보다 못할 수도 있어요. 옵티마이저가 아예 인덱스를 안 고르기도 하고요.

복합 인덱스 안에서의 위치는 좀 달라요. 카디널리티가 낮은 컬럼도 동등 조건으로 항상 쓰인다면 앞에 둘 가치가 있어요. (status, created_at)에서 status가 4개 값뿐이어도, 모든 쿼리가 status = 'PAID'를 깔고 가면 그 구간 점프만으로 1/4을 걸러내고 시작하니까요. 원칙은 "이 컬럼 조합이 실제 쿼리를 얼마나 걸러주는가"예요.

05. 인덱스를 무력화하는 단골 패턴

공통 원리는 하나예요 — B+Tree는 저장된 모양 그대로 비교할 수 있어야 시작점을 잡아요.

• 컬럼 가공 — WHERE DATE(created_at) = ?처럼 컬럼에 함수를 씌우면, 인덱스에 저장된 건 원본 값이라 못 타요. 조건을 범위로 풀어요: created_at >= '2026-09-24' AND created_at < '2026-09-25'.
• 암묵적 형변환 — 문자열 컬럼에 WHERE phone = 01012345678(숫자)처럼 타입이 다르면, MySQL이 컬럼 쪽을 변환해버려서 가공과 같은 효과가 나요. 따옴표 하나로 풀 스캔이 갈리는, 가장 억울한 사고예요.
• 앞 % LIKE — '%검색어%'는 시작점이 없어요. 전방 일치('검색어%')만 타요. 중간 검색이 필요하면 풀텍스트 인덱스나 검색엔진의 영역이에요.
• 서로 다른 컬럼의 OR — 단일 인덱스로는 처리 못 해요. 각각 인덱스가 있으면 index merge가 되기도 하지만 UNION으로 나누는 게 예측 가능해요.

06. 자주 만나는 문제

인덱스를 만들었는데 안 타요

순서대로 점검해요 — ① WHERE에 인덱스 첫 컬럼이 있는가(왼쪽 접두) ② 컬럼을 가공·형변환하고 있지 않은가 ③ 카디널리티가 너무 낮아 옵티마이저가 풀 스캔을 고른 건 아닌가. 확인 도구는 다음 편 EXPLAIN이에요.

인덱스가 너무 많아졌어요

(a), (a,b), (a,b,c)가 다 있으면 (a,b,c) 하나로 앞의 둘을 대체할 수 있어요(왼쪽 접두 덕분에). 중복 인덱스를 정리하면 쓰기 비용이 바로 줄어요.

ORDER BY가 느려요 (filesort)

정렬 컬럼이 인덱스 정렬과 맞으면 정렬 작업 자체가 생략돼요. WHERE user_id = ? ORDER BY created_at DESC면 (user_id, created_at) 인덱스가 정렬까지 해결해요. WHERE만 보고 인덱스를 설계하지 말고 ORDER BY까지 같이 봐요.

정리

복합 인덱스 설계는 세 원칙으로 요약돼요. ① 왼쪽 접두 — 첫 컬럼이 조건에 있어야 하고, ② 동등 앞, 범위 뒤 — 범위 뒤 컬럼은 탐색에 못 쓰고, ③ 자주 도는 무거운 쿼리는 커버링으로 두 번 찾기를 없애요. 그리고 컬럼을 가공하지 말고 저장된 모양 그대로 비교하게 쿼리를 써요.

인덱스를 새로 그리기 전에 한 줄만 점검하면 대부분 가려져요. "이 인덱스의 첫 컬럼이 WHERE에 있는가? 범위 조건은 맨 뒤인가? 컬럼을 함수·형변환으로 가공하고 있지 않은가?" 그게 맞는지 눈으로 확인하는 도구, EXPLAIN을 다음 편에서 다뤄요. 이 글은 인덱스 동작 원리 위에 서 있어요.

출처: MySQL — Multiple-Column Indexes · MySQL — Index Condition Pushdown