InnoDB에서 여러 컬럼을 묶어 복합 인덱스를 만들면 정의된 순서대로 값을 결합해 B-Tree에 저장됨. 예를 들어 (colA, colB, colC) 인덱스면 colA 기준으로 정렬되고 같은 colA 그룹 내에서 colB, 이어서 colC 순으로 정렬됨. 이 구조 때문에 왼쪽 접두사(Left-most prefix) 원칙이 성립하며, 인덱스 정의의 선두 컬럼부터 조건이 주어질수록 효율이 높아짐
개념과 배경
- 복합 인덱스는 정의 순서대로 키를 묶어 B-Tree에 저장
- InnoDB의 보조 인덱스는 리프에 보조키와 함께 PK를 포함함. PK 길이가 길면 보조 인덱스 크기와 I/O에 직접 영향
- left-most prefix 원칙이 핵심. 인덱스의 선두 컬럼부터 연속해서 매칭될 때 탐색 범위가 급격히 줄어듦
동작 방식 예시 (colA, colB)
- 인덱스 구조
- 정렬 키 순서:
colA→colB→ 내부적으로 PK 참조
- 왼쪽 컬럼 우선 필터링
WHERE colA = ?또는WHERE colA = ? AND colB = ?형태는 인덱스를 온전히 사용 가능WHERE colB = ?처럼 선두 컬럼 없이 후행 컬럼만 조건일 때는 인덱스 이용이 제한되거나 범위가 매우 넓어짐. 최신 버전에서 옵티마이저가 제한적으로 우회 전략을 쓰는 경우가 있으나 일반적인 기대값으로 두기 어려움
- 왼쪽 접두사 부분 사용
(colA, colB)에서colA만 조건으로 사용해도 인덱스 사용 가능colA와colB를 모두 조건으로 사용하면 탐색 범위가 더 좁아짐colB만 조건이면 left-most prefix가 깨져 효과가 급감할 수 있음
- 정렬과 결합 사용의 기본
- 인덱스 키 순서와 같은
ORDER BY colA, colB는 추가 정렬 비용 감소에 도움 - WHERE 조건이 인덱스 선두를 건너뛰거나 정렬 순서가 키 정의와 다르면 이점이 줄어듦
여러 컬럼을 묶는 이유
- 다중 조건 검색 성능 향상. 예:
WHERE colA = ? AND colB BETWEEN ? AND ?는(colA, colB)로 빠른 범위 스캔 가능 - 커버링 인덱스 활용. 예:
SELECT colA, colB FROM T WHERE colA = ? AND colB = ?에서 테이블 추가 접근 없이 인덱스만으로 결과 충족 가능 - 쿼리 패턴 최적화. 실제 WHERE 절에서 자주 함께 쓰는 컬럼을 순서까지 고려해 묶는 설계가 유효. 드물게 쓰이거나 고카디널리티 이점이 없는 컬럼을 무리하게 포함하면 쓰기 비용만 증가
주의할 점
- 인덱스 정의 순서가 성능을 좌우. 자주 필터링되는 컬럼, 카디널리티가 높은 컬럼을 선두에 두는 것이 일반적 권장
- 너무 많은 컬럼을 묶으면 인덱스 폭과 관리 비용 증가. 보통 2~3개, 많아도 4개 이하로 목적에 맞게 제한
(A, B, C)일 때WHERE B = ?처럼 선두가 빠지면 인덱스 사용이 제한. 패턴에 따라(B, A)같은 보완 인덱스 검토- InnoDB 보조 인덱스는 PK를 함께 가진다는 점을 고려. PK가 길면 복합 인덱스 크기와 캐시 효율, 쓰기 비용에 불리
- ORDER BY, GROUP BY와 결합 시 인덱스 정의 순서와 일치할 때 이점. 중간에 범위 조건이 끼거나 순서가 어긋나면 효과 하락
- 범위 조건 이후 컬럼 활용 제한 가능. 예를 들어
colA BETWEEN ...가 선두에 오면 그 뒤colB는 정렬이나 추가 필터에서 기대만큼 활용되지 않을 수 있음
마무리
- 복합 인덱스는 왼쪽부터 순차적으로 정렬되는 구조이며 left-most prefix 원칙이 적용됨
- 다중 컬럼 조건, 범위 스캔, 정렬이나 그룹 연산에 유용하지만 인덱스 정의 순서가 쿼리 패턴과 맞지 않으면 이점을 잃음
- 불필요하게 많은 컬럼을 포함하면 쓰기 부담과 스토리지 사용량만 증가. 실제 트래픽 패턴과 카디널리티를 근거로 최소한의 조합과 올바른 순서를 선택하는 것이 핵심