복합 인덱스란?
복합 인덱스는 말 그대로 여러 컬럼을 하나의 인덱스로 묶어놓은 것이다. 아래는 first_name, last_name, birthday 세 컬럼을 하나의 인덱스 multi로 만든 것이다. 각 컬럼에 인덱스를 따로따로 만드는 방식도 어느정도 성능 향상은 있지만 쿼리가 세 컬럼을 동시에 필터링할 때는 복합 인덱스 하나를 잘 설계하는 편이 훨씬 더 강력하다.
ALTER TABLE people ADD INDEX multi (first_name, last_name, birthday);
인덱스를 살펴보면 대략 아래와 같은 형태일 것이다.
SHOW INDEXES FROM people;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type |
|--------|------------|----------|--------------|-------------|-----------|-------------|------------|
| people | 1 | multi | 1 | first_name | A | 3107 | BTREE |
| people | 1 | multi | 2 | last_name | A | 419540 | BTREE |
| people | 1 | multi | 3 | birthday | A | 491583 | BTREE |
여기서 중요한 포인트는 Seq_in_index이다. 복합 인덱스는 이 순서를 기준으로 동작하기 때문에, 컬럼 순서를 어떻게 정하느냐가 중요한 포인트이다.
복합 인덱스의 두가지 핵심 규칙
복합 인덱스를 이해하려면 아래 두 규칙만은 반드시 기억해야 한다.
규칙1. 왼쪽부터, 건너뛰는거 없이 연속해서만 탄다
INDEX multi (first_name, last_name, birthday)가 있을 때 MySQL이 인덱스를 활용할 수 있는 쿼리 패턴은 다음과 같다.
WHERE first_name = 'Aaron'
WHERE first_name = 'Aaron' AND last_name = 'Francis'
WHERE first_name = 'Aaron' AND last_name = 'Francis' AND birthday = '1989-02-14'- 인덱스를 잘타는 경우
WHERE last_name = 'Francis'
WHERE birthday = '1989-02-14'
WHERE last_name = 'Francis' AND birthday = '1989-02-14'- 인덱스를 잘 못타는 경우
이유는 복합 인덱스는 왼쪽부터 연속된 접두사(prefix) 만 사용할 수 있기 때문이다.
- (first_name) ✔
- (first_name, last_name) ✔
- (first_name, last_name, birthday) ✔
- (last_name), (birthday), (last_name, birthday) ❌
그래서 인덱스를 설계할 때는 “실제 쿼리에서 어떤 컬럼 조합을 가장 자주 쓰는지”를 먼저 생각하고, 그 조합이 왼쪽부터 연속되게 나오도록 순서를 정해야 한다.
규칙2. 첫 번째 범위 조건에서 인덱스 사용이 멈춘다
두 번째 규칙은 범위 조건(range) 과 관련이 있다. 다음 두 쿼리를 비교해보자.
-- 1) 모두 동등 조건
SELECT * FROM people
WHERE first_name = 'Aaron'
AND last_name = 'Francis'
AND birthday = '1989-02-14';
-- 2) 중간에 범위 조건이 끼어 있음
SELECT * FROM people
WHERE first_name = 'Aaron'
AND last_name < 'Francis'
AND birthday = '1989-02-14';
EXPLAIN으로 보면 아래와 같은 차이가 난다.
- 1번 쿼리: first_name, last_name, birthday 모두 인덱스에서 사용
- 2번 쿼리: first_name 까지 + last_name(범위 조건)까지만 사용
- birthday 는 인덱스에서 버려짐
즉, 인덱스 컬럼 중에서 처음 등장하는 범위 조건에서 인덱스 활용이 멈춘다고 이해하면 쉽다. 따라서 범위 조건이 많이 붙는 컬럼은 보통 인덱스의 뒤쪽에 두는 것이 좋다.
EXPLAIN으로 실제 인덱스 사용 확인하기
MySQL이 복합 인덱스를 어떻게 사용하는지 이해하려면 EXPLAIN 문을 사용할 수 있다.
EXPLAIN SELECT * FROM people WHERE first_name = 'Aaron' AND last_name = 'Francis';
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered |
|----|-------------|--------|------|---------------|-------|---------|-------------|------|----------|
| 1 | SIMPLE | people | ref | multi | multi | 404 | const,const | 1 | 100.00 |
EXPLAIN 출력 결과 복합 인덱스가 사용되고 있으며 키 길이는 404바이트이다. MySQL이 인덱스의 first_name과 last_name 부분을 모두 사용하고 있음을 나타낸다.
birthday를 추가하면 key_len이 407로 증가한다.
EXPLAIN SELECT * FROM people WHERE first_name = 'Aaron' AND last_name = 'Francis' and birthday = '1989-02-14';
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered |
|----|-------------|--------|------|---------------|-------|---------|-------------------|------|----------|
| 1 | SIMPLE | people | ref | multi | multi | 407 | const,const,const | 1 | 100.00 |
쿼리를 변경하여 last_name에 범위 조건을 포함시키면 key_len이 다시 404로 떨어진다.
EXPLAIN SELECT * FROM people WHERE first_name = 'Aaron' AND last_name < 'Francis' and birthday = '1989-02-14';
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered |
|----|-------------|--------|-------|---------------|-------|---------|-----|------|----------|
| 1 | SIMPLE | people | range | multi | multi | 404 | | 55 | 10.00 |
키 길이는 404바이트로 유지된다. MySQL은 첫 번째 범위 조건(last_name)에서 인덱스 사용을 중단하고 birthday 부분의 인덱스는 사용하지 않는다. filtered의 비율도 10%인 것을 볼 수 있다.
복합 인덱스 설계 팁
- WHERE 절에서 동등 조건(=) 으로 자주 쓰이고, 카디널리티가 높은 컬럼(예: user_id)을 앞쪽에 둔다.
- 범위 조건(BETWEEN, >, < 등)이 자주 붙는 컬럼(예: created_at, price, score)은 뒤에 둔다.
- 실제 액세스 패턴 기준으로 설계한다.
- 순서를 신중히 결정할 것
- 인덱스를 만들었다면, 항상 EXPLAIN으로 정말 그 인덱스를 타는지 확인한다.