본 포스팅은 패스트캠퍼스 환급 챌린지 참여를 위해 작성하였습니다.
강의 요약
오늘은 성능 테스트 후 시스템을 개선하는 리팩토링 실습을 진행했다. 강의에서는 아키텍처, 애플리케이션, 데이터베이스 세 가지 레이어로 나누어 성능 개선 방법을 다뤘는데 아키텍처 레이어에서는 스케일 아웃과 분산 시스템 구조, 서버 리소스 최적화를 소개했고, 애플리케이션 레이어에서는 캐싱 전략과 비동기 처리, 비즈니스 로직 개선을 소개했다. 데이터베이스 레이어에서는 인덱스 최적화와 데이터베이스 분리, 정규화와 역정규화 전략을 소개했다.
강의에서는 스트레스 테스트와 스파이크 테스트에서 데이터베이스 CPU 사용률이 높게 나타나 인덱스를 추가했고, 인듀어런스 테스트에서는 TPS가 낮게 나와 캐시 만료 시간을 늘리고 비동기 처리를 적용했다. 하지만 약간의 아쉬움이 있었다. 우선 로컬 환경 제약으로 아키텍처 리팩토링은 스킵되었고, 데이터베이스 개선은 단일 컬럼 인덱스 추가였다. 그래서 이 기회에 MySQL 복합 인덱스에 대해 조금 정리해보려고 한다.
복합 인덱스란?
복합 인덱스는 말 그대로 여러 컬럼을 하나의 인덱스로 묶어놓은 것이다. 아래는 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으로 정말 그 인덱스를 타는지 확인한다.
참고 출처
- https://www.percona.com/blog/understanding-mysql-indexes-types-best-practices/
- https://planetscale.com/learn/courses/mysql-for-developers/indexes/composite-indexes
- https://planetscale.com/learn/courses/mysql-for-developers/queries/sorting-with-composite-indexes
