MySQL 복합 인덱스 설계: 컬럼 순서·범위 조건·EXPLAIN ANALYZE

MySQL 복합 인덱스는 여러 단일 인덱스를 합친 목록이 아니라 왼쪽 컬럼부터 정렬된 하나의 키다. 따라서 “선택도가 높은 컬럼을 항상 앞에 둔다”거나 “등호·정렬 컬럼을 차례대로 붙인다”는 규칙만으로 설계하면 실제 쿼리와 어긋날 수 있다.

아래 예제는 MySQL 8.4와 InnoDB의 B-tree 인덱스를 기준으로 한다. 먼저 자주 실행되는 쿼리를 고정하고, 후보 인덱스마다 읽는 행과 정렬 비용을 실행 계획으로 비교하는 것이 출발점이다.

복합 인덱스는 어떻게 정렬되는가

다음 인덱스는 (user_id, status, created_at) 튜플 순서로 정렬된다.

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at);

먼저 user_id로 정렬하고, 같은 사용자 안에서 status, 두 값이 모두 같을 때 created_at 순서가 정해진다.

(1, CANCELLED, 2026-04-01)
(1, PAID,      2026-04-01)
(1, PAID,      2026-04-02)
(2, PAID,      2026-04-01)

이 때문에 다음 왼쪽 접두사는 검색 경로가 된다.

• (user_id)
• (user_id, status)
• (user_id, status, created_at)

반면 status만 보면 인덱스 전체가 상태값 순서로 모여 있지 않다. MySQL은 이 인덱스를 lookup에 쓰지 않거나 다른 접근 방식을 택할 수 있다.

왼쪽 접두사 규칙을 과하게 단순화하지 않기

다음 쿼리는 인덱스의 앞에서부터 조건을 사용한다.

SELECT id, user_id, status, created_at
FROM orders
WHERE user_id = 10
  AND status = 'PAID'
  AND created_at >= '2026-04-01'
  AND created_at <  '2026-05-01';

user_id와 status의 동등 조건으로 검색 구간을 좁힌 뒤 created_at 범위를 읽을 수 있다.

“범위 조건 뒤 컬럼은 전혀 사용되지 않는다”는 설명도 정확하지 않다. 뒤쪽 컬럼은 인덱스에서 조건을 평가해 테이블 행 접근을 줄이는 데 쓰일 수 있다. 다만 앞의 동등 조건과 첫 범위 조건처럼 스캔해야 할 인덱스 구간 자체를 줄이지는 못할 수 있다. EXPLAIN에서 key parts와 필터 조건을 구분해 봐야 한다.

컬럼 순서는 쿼리 묶음으로 결정한다

주문 테이블에서 다음 두 쿼리가 있다고 하자.

-- A: 특정 사용자의 결제 완료 주문을 최근 순으로
SELECT id, created_at
FROM orders
WHERE user_id = ?
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

-- B: 전체에서 특정 기간의 결제 완료 주문 집계
SELECT COUNT(*)
FROM orders
WHERE status = 'PAID'
  AND created_at >= ?
  AND created_at < ?;

(user_id, status, created_at)은 A에 잘 맞을 가능성이 크지만 B의 status부터 바로 찾는 인덱스는 아니다. B의 빈도와 비용이 크다면 (status, created_at) 같은 별도 후보를 비교해야 한다.

여기서 한 인덱스로 모든 쿼리를 해결하려고 하지 않는다. 반대로 쿼리마다 인덱스를 하나씩 만들지도 않는다. 다음 항목을 쿼리 묶음별로 확인한다.

• 동등 조건과 범위 조건
• 필요한 정렬과 LIMIT
• 반환하는 열
• 호출 빈도와 허용 지연
• 기존 인덱스와의 접두사 중복
• 삽입·갱신 부하

ORDER BY까지 인덱스로 처리하는 조건

쿼리 A에서 user_id와 status는 상수다. 그 뒤의 created_at이 인덱스 순서와 맞으므로 MySQL이 인덱스를 역방향으로 읽어 별도 정렬을 피할 수 있다.

EXPLAIN FORMAT=TREE
SELECT id, created_at
FROM orders
WHERE user_id = 10
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

그러나 ORDER BY 컬럼의 순서·방향이 인덱스와 맞지 않거나, 앞쪽 컬럼이 상수로 고정되지 않거나, 테이블 행을 읽는 비용이 더 크면 별도 정렬이 생길 수 있다. “정렬 컬럼을 인덱스 마지막에 붙이면 된다”가 아니라 실제 계획의 Sort와 읽은 행 수를 확인한다.

커버링 인덱스의 이득과 비용

쿼리에 필요한 열이 모두 인덱스에 있으면 InnoDB가 클러스터드 인덱스의 전체 행을 다시 읽지 않고 결과를 만들 수 있다. MySQL 문서에서는 이를 covering index라고 설명한다.

SELECT user_id, status, created_at
FROM orders
WHERE user_id = 10
  AND status = 'PAID';

이 쿼리는 세 열이 모두 후보 인덱스에 있다. 하지만 커버링을 만들겠다고 큰 문자열이나 자주 바뀌는 열을 계속 붙이면 인덱스가 커진다. 더 많은 페이지를 읽고, 캐시 효율이 떨어지고, 쓰기 비용도 증가할 수 있다.

커버링은 테이블 접근이 없어진다는 사실만으로 확정하지 않고 중요한 읽기 경로에서 절약하는 비용이 인덱스 비대화보다 큰지로 판단한다.

단일 인덱스 여러 개와 무엇이 다른가

다음 두 구성은 같은 의미가 아니다.

-- 단일 인덱스 두 개
CREATE INDEX idx_orders_user   ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);

-- 복합 인덱스 하나
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

MySQL은 별도 인덱스를 Index Merge로 조합하거나 더 선택적인 하나를 고를 수 있다. 복합 인덱스는 두 조건이 결합된 정렬 구조에서 바로 범위를 좁힐 수 있다. 어느 쪽이 빠른지는 데이터 분포, 조건 조합과 반환 열에 달려 있다.

자주 함께 쓰는 조건이라는 이유만으로 복합 인덱스를 확정하지 말고 두 계획을 대표 데이터에서 비교한다.

EXPLAIN ANALYZE로 후보를 비교하는 순서

EXPLAIN ANALYZE
SELECT id, created_at
FROM orders
WHERE user_id = 10
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

다음 순서로 읽으면 된다.

1. Table scan, Index lookup, Index range scan 중 무엇을 선택했는가
2. 어떤 인덱스와 키 부분을 사용했는가
3. 예상 행 수와 실제 행 수가 얼마나 다른가
4. 각 iterator가 몇 번 반복됐는가
5. 별도 Sort와 테이블 row lookup이 있는가
6. 후보 인덱스가 쓰기 부하와 저장 공간을 얼마나 늘리는가

EXPLAIN ANALYZE는 문장을 실제로 실행한다. 운영 데이터에서 바로 시험하기보다 읽기 전용 복제본이나 안전한 검증 환경을 사용하고, 대상 문장이 변경을 일으키지 않는지 확인해야 한다.

복합 인덱스 설계의 결론은 “등호 → 범위 → 정렬”이라는 암기 문장이 아니다. 중요한 쿼리가 적은 페이지와 행을 읽도록 정렬 구조를 만들고, 그 가정을 실행 계획으로 검증하는 일이다. B+Tree 탐색 자체는 InnoDB 인덱스와 풀 스캔, 단일 컬럼의 선택도는 MySQL 단일 인덱스 판단 기준에서 이어서 볼 수 있다.

참고 자료

• MySQL 8.4: Multiple-Column Indexes
• MySQL 8.4: How MySQL Uses Indexes
• MySQL 8.4: ORDER BY Optimization
• MySQL 8.4: EXPLAIN Statement
• MySQL 8.4: Index Statistics Collection