MySQL 복합 인덱스의 순서와 중복 인덱스 판단하기

MySQL 복합 인덱스의 순서와 중복 인덱스 판단하기

한눈에 보기

  • 복합 인덱스는 컬럼의 집합이 아니라 순서가 있는 자료구조다.
  • 인덱스 순서는 스키마보다 WHERE, ORDER BY, LIMIT이 만드는 실제 쿼리 패턴에서 시작한다.
  • 중복 인덱스는 바로 삭제하지 말고 EXPLAIN ANALYZE와 사용량으로 검증한다.

목차

B+Tree 인덱스는 앞 컬럼부터 정렬된다

핵심

(a, b, c, d) 인덱스는 일반적으로 (a), (a, b), (a, b, c), (a, b, c, d) 접두사에 가장 자연스럽게 사용된다. 중간 컬럼을 건너뛰면 뒤 컬럼의 탐색 효과는 제한될 수 있다.

MySQL InnoDB의 일반적인 보조 인덱스는 B+Tree 구조를 사용한다. 복합 인덱스가 다음과 같다면:

INDEX (baby_id, is_removed, type, start_time)

인덱스 키는 대략 다음 순서로 정렬된다.

baby_id
  └─ is_removed
       └─ type
            └─ start_time

이때 앞에서부터 연속된 컬럼을 조건으로 사용할수록 탐색 범위를 잘 줄일 수 있다. 이를 복합 인덱스의 최좌선 접두사 규칙으로 설명하곤 한다.

다음 조건은 인덱스의 앞 세 컬럼을 모두 사용한다.

WHERE baby_id = ?
  AND is_removed = false
  AND type = ?

반면 type을 건너뛰고 start_time만 조건으로 사용하면, start_time은 인덱스 탐색 범위를 줄이는 데 충분히 사용되지 못할 수 있다.

WHERE baby_id = ?
  AND is_removed = false
  AND start_time >= ?

가운데 type이 비어 있기 때문이다. MySQL이 인덱스 컨디션 푸시다운으로 뒤 컬럼을 필터링에 활용할 수는 있지만, 앞 컬럼처럼 초기 탐색 범위를 결정하는 것과는 다르다.

인덱스는 스키마가 아니라 쿼리에서 시작한다

베베스냅의 대표 조회 패턴

특정 아이의 삭제되지 않은 기록을 최신 순으로 조회한다. 이 패턴을 기준으로 필터 컬럼과 정렬 컬럼의 순서를 검토한다.

이 서비스에서 가장 자주 보이는 기록 목록 조회를 단순화하면 다음과 같다.

SELECT ...
FROM record
WHERE baby_id = ?
  AND is_removed = false
ORDER BY start_time DESC
LIMIT ?, ?;

이 쿼리에서 중요한 일은 다음 순서다.

  1. baby_id로 특정 아이의 기록만 선택한다.
  2. is_removed = false인 활성 기록만 남긴다.
  3. start_time DESC로 최신 기록부터 읽는다.

이 패턴에 직관적으로 맞는 인덱스 후보는 다음이다.

INDEX (baby_id, is_removed, start_time)

두 등가 조건 뒤에 정렬 컬럼이 이어진다. MySQL 8에서는 오름차순으로 저장된 인덱스도 역순으로 스캔할 수 있으므로, 이 정렬 하나만을 위해 반드시 별도의 DESC 인덱스가 필요한 것은 아니다.

중간 컬럼이 정렬을 막을 수 있다

다음 인덱스는 특정 기록 유형의 최신 값을 조회할 때 유용하다.

INDEX (baby_id, is_removed, type, start_time)

WHERE baby_id = ?
  AND is_removed = false
  AND type = ?
ORDER BY start_time DESC
LIMIT 1;

type까지 등가 조건이므로, 그 뒤의 start_time이 정렬에 자연스럽게 사용될 수 있다.

하지만 type이 조건에 없는 목록 쿼리에서는 상황이 다르다.

WHERE baby_id = ?
  AND is_removed = false
ORDER BY start_time DESC;

인덱스 안에서 start_time보다 type이 먼저 배치되어 있다. 하나의 baby_id, is_removed 구간 안에서 행은 start_time보다 type별로 먼저 묶인다. 따라서 이 인덱스만으로 전체 유형의 start_time 순서를 바로 얻기는 어렵다. 실행 계획에 Using filesort가 나타날 수 있는 지점이다.

이 때 목록 조회와 유형별 최신 조회가 둘 다 중요하다면 서로 다른 인덱스가 필요할 수 있다.

INDEX (baby_id, is_removed, start_time)
INDEX (baby_id, is_removed, type, start_time)

인덱스를 두 개 두는 것이 항상 정답은 아니다. 두 쿼리의 빈도와 실제 스캔 행 수를 비교해야 한다.

IN 조건과 정렬은 별도로 확인한다

다음처럼 여러 type을 묶어 최신 기록 하나를 찾는 쿼리도 있을 수 있다.

WHERE baby_id = ?
  AND is_removed = false
  AND type IN (?, ?, ?)
ORDER BY start_time DESC
LIMIT 1;

type = ?와 달리 IN은 여러 인덱스 구간을 읽고 합쳐야 할 수 있다. (baby_id, is_removed, type, start_time)이 후보 행을 줄이는 데는 유용하지만, 여러 type 구간 전체의 최신 순서까지 무조건 보장한다고 가정하면 안 된다.

선택지는 두 가지다.

어느 쪽이 나은지는 type의 선택도와 항목별 데이터 분포에 따라 다르다. 이론만으로 결정하지 말고 운영 데이터와 비슷한 분포에서 측정해야 한다.

범위 조건 뒤의 컬럼은 주요 탐색 범위를 줄이기 어렵다

기간별 기록 조회는 다음과 같다.

WHERE baby_id = ?
  AND is_removed = false
  AND type = ?
  AND start_time >= ?
  AND start_time < ?
ORDER BY start_time DESC;

이 패턴에는 다음 순서가 잘 맞는다.

INDEX (baby_id, is_removed, type, start_time)

등가 조건이 먼저 나오고 범위·정렬 컬럼이 뒤에 나온다. 일반적으로 인덱스가 첫 범위 조건을 만나면, 그 뒤 컬럼은 주요 탐색 범위를 줄이는 데 제약을 받는다. 따라서 복합 인덱스는 대체로 다음 순서로 검토한다.

등가 조건 -> 범위 조건·정렬 -> 커버링에 필요한 컬럼

하지만 선택도가 낮은 컬럼을 무조건 앞에 두는 공식으로 받아들이면 안 된다. 핵심은 실제 쿼리의 조건 조합이다.

Boolean 컬럼도 복합 인덱스에서는 의미가 있다

is_removed는 true/false 두 값만 가지므로 단독 인덱스로는 선택도가 낮다. 그렇다고 복합 인덱스에서도 무의미한 것은 아니다.

INDEX (baby_id, is_removed, start_time)

baby_id로 이미 범위를 줄인 뒤 활성 데이터만 연속된 구간으로 만들고, 그 안에서 start_time으로 읽을 수 있다. 거의 모든 조회가 is_removed = false를 사용하는 논리 삭제 구조라면 의미 있는 순서다.

기본 키와 겹치는 인덱스를 확인한다

중복으로 보여도 바로 삭제하지 않기

외부 배치·관리자 도구·과거 쿼리가 사용할 수 있다. 운영 사용량, EXPLAIN ANALYZE, invisible index 관찰 후 제거한다.

다음과 같은 인덱스가 있다고 하자.

PRIMARY KEY (id)
INDEX (id, is_removed)

id는 기본 키이므로 하나의 행을 이미 직접 찾을 수 있다. 그런 다음 is_removed 조건을 한 행에 대해 확인하는 비용은 매우 작다. 따라서 (id, is_removed)는 중복 인덱스인지 검토할 가치가 큰 후보다.

다만 즉시 삭제하면 안 된다. 다음을 먼저 확인한다.

MySQL 8의 invisible index를 사용할 수 있는 환경이라면 즉시 삭제하기 전에 옵티마이저에게만 숨겨 영향을 관찰할 수 있다.

비슷한 복합 인덱스는 실제 쿼리로 비교한다

다음 두 인덱스는 컬럼이 비슷하지만 순서가 다르다.

INDEX (baby_id, is_removed, type, start_time)
INDEX (baby_id, type, is_removed)

대부분의 쿼리가 baby_id, is_removed, type을 모두 등가 조건으로 사용한다면 첫 번째 인덱스도 앞 세 컬럼을 모두 활용할 수 있다. 두 번째 인덱스가 중복일 가능성이 있다.

하지만 다음 쿼리가 많다면 얘기가 달라진다.

WHERE baby_id = ?
  AND type = ?;

첫 번째 인덱스는 is_removed를 건너뛰어야 하지만, 두 번째는 (baby_id, type) 접두사를 바로 사용한다. 즉, 중복 여부는 스키마 모양만으로 결정할 수 없고 실제 쿼리 목록이 필요하다.

인덱스는 읽기를 가속하지만 쓰기 비용을 만든다

인덱스 하나를 추가하면 다음 비용도 함께 생긴다.

인덱스가 많은 것은 최적화가 많이 된 것과 같지 않다. 중요한 조회 패턴을 적은 수의 인덱스가 명확하게 지원하는 편이 낫다.

EXPLAIN ANALYZE로 가설을 검증한다

측정 데이터도 운영과 비슷해야 한다

행 수만 늘리지 말고 baby_id, type, is_removed, 날짜 분포와 페이지 접근 패턴을 비슷하게 만든다.

인덱스를 설계한 뒤에는 추측으로 끝내지 않는다.

EXPLAIN ANALYZE
SELECT id, type, start_time
FROM record
WHERE baby_id = 1
  AND is_removed = false
ORDER BY start_time DESC
LIMIT 20;

MySQL 8.0.18 이상의 EXPLAIN ANALYZE는 실제로 쿼리를 수행하고 예상치와 실제 행 수·시간을 보여준다. 다음을 확인한다.

개발 DB에 행이 몇 백 개밖에 없다면 대부분의 쿼리가 빠르다. 운영과 비슷한 분포의 데이터를 만들어 검증해야 한다. 단순히 행 수만 많이 복사하는 것보다 baby_id, type, is_removed, 날짜 분포를 비슷하게 만드는 것이 중요하다.

실전 점검 순서

복합 인덱스를 정리할 때는 다음 순서를 사용한다.

  1. Slow query log와 API 코드에서 중요 쿼리를 모은다.
  2. 각 쿼리의 등가, 범위, 정렬, LIMIT을 표시한다.
  3. 등가 조건 뒤에 범위·정렬 컬럼을 두는 후보를 만든다.
  4. 현재 인덱스의 접두사가 새 인덱스에 포함되는지 확인한다.
  5. EXPLAIN ANALYZE로 스캔 행 수와 정렬 여부를 비교한다.
  6. 쓰기 비용을 포함해 최소한의 인덱스만 남긴다.
  7. 인덱스 제거는 invisible 적용과 관찰 후 진행한다.

결론

핵심 정리

  • 등가 조건 → 범위·정렬 조건 순서를 기본 후보로 삼는다.
  • 인덱스는 컬럼이 아니라 쿼리 패턴을 기준으로 설계한다.
  • 추가·제거 전에 반드시 실제 데이터 분포에서 실행 계획을 비교한다.

복합 인덱스의 순서를 결정할 때 컬럼의 선택도만 보면 부족하다. 실제 쿼리가 어떤 컬럼을 등가 조건으로 사용하고, 어디서 범위를 만들며, 어떤 순서로 결과를 읽는지를 봐야 한다.

이 사례에서 얻은 핵심은 다음과 같다.

인덱스는 많이 만드는 것이 아니라 주요 쿼리의 읽기 경로를 의도적으로 설계하는 일이다.

관련 노트