MySQL ‐ Multi Column Index - thought-corner/backend-roadmap GitHub Wiki

MySQL - Multi Column Index

복합 인덱스란?

복합 컬럼 인덱스는 데이터베이스에서 두 개 이상의 컬럼을 묶어서 생성하는 인덱스이다.
단일 인덱스만으로는 쿼리의 성능을 충분히 끌어올릴 수 없거나, 여러 컬럼을 동시에 조건으로 필터링하는 경우가 잦을 때 사용하는 강력한 성능 최적화 도구이다.

복합 인덱스는 어떤 구조로 저장되는지?

InnoDB의 인덱스는 B+Tree 자료구조이며, 복합 인덱스 (a, b, c)는 a로 먼저 정렬하고, a가 같은 값들 안에서 다시 b로 정렬하고, b까지 같으면 c로 정렬한 상태로 저장된다. 즉, 컬럼을 이어 붙인 하나의 정렬 키처럼 동작한다.

복합 인덱스 설계 시 핵심 고려사항

복합 인덱스를 생성할 때는 순서 배치가 생명이다.

1. WHERE절에 자주 등장하는 컬럼을 앞으로 : 쿼리의 조건절에 항상 혹은 가장 빈번하게 사용되는 컬럼이 앞으로 와야 한다.

근거 : 앞 컬럼에 조건이 없으면 인덱스의 정렬 자체를 활용할 수 없다.
(a, b) 인덱스에서 WHERE b = 10만으로 b가 전체적으로 정렬되어 있지 않기 때문에 인덱스를 타지 못하거나 전체를 훑어야 한다.
이를 왼쪽 접두사 규칙(leftmost prefix rule)이라고 한다. (a, b, c) 인덱스는 (a), (a, b), (a, b, c) 조합은 활용할 수 있지만 (b), (c), (b, c)만으로는 활용할 수 없다.

2. 동등 조건(=)을 앞쪽에 배치 : 범위 검색이 들어간 컬럼 뒤에 있는 컬럼들은 인덱스의 정렬 기능을 제대로 활용하지 못한다. 따라서 = 조건으로 검색되는 컬럼을 인덱스 앞쪽에 두는 것이 유리하다.

근거 : 범위 검색이 들어간 컬럼 뒤에 있는 컬럼들은 인덱스의 정렬 기능을 활용할 수 없다.
(a, b) 인덱스에서 WHERE a > 1 AND b = 5를 실행하면, a가 여러 값(2, 3, 4 …)에 걸치는 순간 각 a 블록마다 b는 다시 정렬되므로, b = 5를 인덱스로 한 번에 좁힐 수 없다.
반대로 WHERE a = 1 AND b > 5는 a = 1 블록 안에서 b가 정렬되어 있으므로 b > 5 범위를 인덱스로 스캔할 수 있다. 따라서 = 조건 컬럼을 앞으로, 범위 조건 컬럼을 뒤에 두는 것이 유리하다.

3. 카디널리티(Cardinality) : 중복되는 값이 적은 컬럼을 앞쪽에 두는 것이 일반적으로 검색 대상을 빠르게 좁히는 데 도움이 된다.

근거 : 앞 컬럼에서 검색 대상을 얼마나 좁히느냐가 뒤의 탐색량을 결정한다. 중복 값이 적은(=선택도가 높은) 컬럼을 앞에 두면 한 번의 탐색으로 결과 범위가 크게 줄어든다.
단, ①·②(자주 쓰는 조건, 동등 조건 우선)와 충돌하면 실제 쿼리 패턴이 우선이다. 카디널리티는 조건이 동등한 상황에서의 보조 기준으로 보는 편이 안전하다.

근거 검증 : 인덱스를 어디까지 실제로 탔는가?

설계 원칙이 맞았는지는 EXPLAIN의 key_len으로 확인할 수 있다. key_len은 인덱스에서 실제로 사용된 바이트 수로, 복합 인덱스 중 몇 번째 컬럼까지 탐색에 쓰였는지를 알려준다.
컬럼별 바이트 계산 예시 : INT(4) + NULL 허용 시 +1, DATE(3), VARCHAR(20) utf8mb4(20 × 4 + 길이 저장 2 = 82) 등. 즉 어떤 컬럼까지 더해졌는지 역산하면 인덱스 사용 깊이를 알 수 있다.
EXPLAIN의 Extra 컬럼도 함께 본다.
- Using index condition : 인덱스로 못 거른 조건을 스토리지 엔진에서 미리 걸러줌(ICP)
- Using where : 인덱스 밖에서 추가 필터링이 일어남(뒤 컬럼이 인덱스로 못 좁혀졌다는 신호)
- Using filesort : 정렬을 인덱스로 해결하지 못하고 별도 정렬 발생

근거 검증: 어떤 방식으로 인덱스를 탔는가(type)

type	의미	인덱스
`const`	PK/유니크 인덱스 `=` 조건으로 1건 확정	✅ 최상
`eq_ref`	조인 시 PK/유니크로 1건씩 매칭	✅ 조인 최상
`ref`	비유니크 인덱스 `=` 조건 (여러 건 가능)	✅ 좋음
`range`	인덱스 범위 스캔 (`>`, `<`, `BETWEEN`, `IN`, `LIKE 'abc%'`)	✅ 좋음
`index`	인덱스 풀스캔 (인덱스 전체를 순차로 읽음)	⚠️ 주의
`ALL`	테이블 풀스캔 (인덱스 못 탐)	❌ 최악

튜닝의 핵심은 좋은 type이 나오도록 인덱스와 쿼리를 설계하고, type + key + key_len + Extra를 함께 읽는 것이다.

트레이드오프

쓰기 성능 저하 : 테이블에 INSERT, UPDATE, DELETE가 발생할 때마다 복합 인덱스 트리도 함께 재정렬되어야 한다. 컬럼이 많을수록 갱신 비용이 커진다.
저장 공간 차지 : 인덱스도 데이터베이스의 메모리와 디스크 공간을 상당히 차지하는 별도의 자료구조라는 점을 인지해야 한다.
중복 인덱스 주의 : (a, b) 인덱스가 있으면 (a) 단일 인덱스는 왼쪽 접두사 규칙상 대개 중복이므로 불필요하다.

-- 주식 거래 이력 테이블 생성 예시
CREATE TABLE stock_trade_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    stock_code VARCHAR(20) NOT NULL,    -- 종목코드
    trade_date DATE NOT NULL,           -- 거래일자
    closing_price DECIMAL(10, 2),       -- 종가
    trade_volume BIGINT,                -- 거래량
    
    -- (stock_code, trade_date) 순서로 복합 인덱스 생성
    INDEX idx_stock_trade (stock_code, trade_date)
);

-- (참고) 이미 만들어진 테이블에 나중에 인덱스를 추가할 때
-- CREATE INDEX idx_stock_trade ON stock_trade_history (stock_code, trade_date);