인덱스를 만들었는데 왜 안 빨라질까 — MySQL 인덱스 스캔 4가지
들어가며
인덱스를 걸면 쿼리가 빨라진다는 건 다들 압니다. 그런데 실제로 인덱스를 만들어놓고도 기대만큼 빨라지지 않는 경우를 종종 만납니다.
그 이유는 “인덱스를 탄다”는 말이 사실 한 가지가 아니기 때문입니다. MySQL이 인덱스를 읽는 방식은 여러 가지이고, 어떤 방식으로 읽느냐에 따라 성능 차이가 크게 납니다.
이 글에서는 인덱스 스캔 방식 네 가지와, 정순·역순 스캔이 인덱스 설계에 어떤 영향을 주는지 정리해보려고 합니다.
전제 — B-Tree 인덱스는 정렬되어 있다
모든 이야기의 출발점은 이겁니다. B-Tree 인덱스는 키 값이 정렬된 상태로 저장되어 있고, 리프 노드끼리는 양방향으로 연결되어 있습니다.
이 두 가지 성질이 아래 모든 스캔 방식의 근거가 됩니다.
- 정렬되어 있으니 → 시작 지점을 빠르게 찾아 거기서부터 읽을 수 있습니다.
- 리프가 연결되어 있으니 → 찾은 지점부터 순차적으로 쭉 읽어나갈 수 있습니다.
- 양방향이니 → 역순으로도 읽을 수 있습니다.
1. 인덱스 레인지 스캔 (index range scan)
가장 일반적이고, 우리가 보통 “인덱스를 탔다”고 말할 때의 그 방식입니다.
동작은 단순합니다. 인덱스가 정렬되어 있으니 조건에 맞는 시작 지점을 찾고, 거기서부터 필요한 범위(range)까지만 읽고 멈춥니다.
graph LR
A["1. 루트 → 브랜치 → 리프<br/>시작 지점 탐색"] --> B["2. 리프 노드를 따라<br/>범위만큼 순차 읽기"]
B --> C["3. 필요하면 실제<br/>테이블 레코드 조회"]
전체를 읽지 않고 필요한 구간만 읽기 때문에 효율적입니다. 인덱스 설계가 잘 됐다면 대부분의 쿼리가 이 방식으로 처리되는 게 이상적입니다.
한 가지 주의할 점은 3번 단계입니다. 조회하려는 컬럼이 인덱스에 없으면, 인덱스에서 찾은 위치를 가지고 실제 테이블 레코드를 다시 읽어야 합니다. 이 과정이 랜덤 I/O라서, 읽어야 할 건수가 많아지면 차라리 테이블을 통째로 읽는 게 나을 수도 있습니다. 인덱스를 걸었는데도 안 빨라지는 원인 중 하나가 여기에 있습니다.
2. 인덱스 풀 스캔 (index full scan)
이름 그대로 인덱스를 처음부터 끝까지 전부 훑는 방식입니다.
레인지 스캔처럼 시작 지점을 찾아 들어가지 못하는 경우에 발생합니다. 대표적으로 복합 인덱스의 선두 컬럼이 조건에 없을 때입니다. (A, B) 인덱스가 있는데 B만으로 검색하면, 정렬 기준의 첫 번째가 A라서 시작 지점을 특정할 수 없습니다.
그런데도 MySQL이 이 방식을 선택하는 이유가 있습니다. 조회하려는 컬럼이 전부 인덱스 안에 있다면, 테이블을 읽지 않고 인덱스만 읽고 끝낼 수 있기 때문입니다. 인덱스는 보통 테이블 데이터보다 훨씬 작으므로, 테이블 풀 스캔보다는 인덱스 풀 스캔이 나은 경우가 많습니다.
정리하면 이렇습니다.
레인지 스캔보다는 느리지만, 테이블 풀 스캔보다는 빠른 차선책.
3. 루스 인덱스 스캔 (loose index scan)
앞의 두 방식이 인덱스를 촘촘히(tight) 읽었다면, 이 방식은 듬성듬성(loose) 건너뛰며 읽습니다.
주로 GROUP BY 최적화에서 등장합니다. 예를 들어 그룹별 최솟값이나 최댓값만 필요한 경우를 생각해보면, 각 그룹의 모든 행을 읽을 필요가 없습니다. 인덱스가 정렬되어 있으니 그룹마다 필요한 지점만 짚고, 나머지는 통째로 건너뛰면 됩니다.
읽지 않아도 되는 구간을 아예 스킵하기 때문에, 조건이 맞을 때는 성능 차이가 상당히 큽니다.
4. 인덱스 스킵 스캔 (index skip scan)
MySQL 8.0에서 추가된 기능으로, 루스 인덱스 스캔의 아이디어를 확장한 것입니다. (이 순서가 중요합니다. 스킵 스캔이 먼저가 아니라, 루스 인덱스 스캔이 먼저입니다.)
앞서 인덱스 풀 스캔에서 이야기했던 문제를 다시 봅시다. (A, B) 복합 인덱스가 있는데 B 조건만 있으면 인덱스를 제대로 쓰기 어렵습니다.
스킵 스캔은 여기서 이런 접근을 합니다. A가 가질 수 있는 값들을 뽑아낸 다음, 각각의 A 값에 대해 B 조건으로 레인지 스캔을 반복하는 것입니다. 선두 컬럼 조건이 없어도 인덱스를 활용할 길이 열리는 셈입니다.
다만 만능은 아닙니다. 선두 컬럼의 고유값 개수가 적을 때 유효합니다. A가 가질 수 있는 값이 수천 개라면 그만큼 스캔을 반복해야 하니 오히려 손해입니다.
정순 스캔과 역순 스캔
B-Tree의 리프 노드가 양방향으로 연결되어 있다는 성질 덕분에, 인덱스는 정순(오름차순)으로도, 역순(내림차순)으로도 읽을 수 있습니다.
그렇다면 둘의 성능은 같을까요? 그렇지는 않습니다. 일반적으로 정순 스캔이 역순 스캔보다 조금 더 빠른 경우가 많습니다. 페이지 내부에서 레코드가 정방향으로 연결된 구조라거나, 잠금 방식의 차이 같은 이유들이 있습니다.
여기서 MySQL 8.0의 내림차순 인덱스(descending index)가 등장합니다. 인덱스를 만들 때 아예 내림차순으로 정의할 수 있게 된 기능인데, ORDER BY ... DESC가 잦은 컬럼이라면 이쪽이 유리할 수 있습니다.
특히 의미가 큰 경우는 정렬 방향이 섞일 때입니다.
1
ORDER BY a ASC, b DESC
이런 정렬은 단일 방향 인덱스만으로는 그 순서를 그대로 따라갈 수 없습니다. 결국 DB가 데이터를 다시 줄 세우는 작업(filesort)이 발생합니다. (a ASC, b DESC)로 인덱스를 정의할 수 있게 되면서 이런 혼합 정렬도 인덱스만으로 처리할 수 있게 되었습니다.
그래서 인덱스를 어떻게 설계해야 할까
여기까지 정리하고 나면, 자연스럽게 설계 기준이 나옵니다.
정렬 방향은 실제 쿼리 패턴을 보고 정합니다. 어떤 방향으로, 어떤 컬럼 순서로 자주 정렬하는지를 분석한 다음 인덱스의 방향과 컬럼 순서를 맞춰줍니다. 그러면 인덱스를 읽는 순서가 곧 정렬 결과가 되어 filesort를 줄일 수 있습니다.
복합 인덱스의 컬럼 순서는 대략 이 우선순위를 따릅니다.
- 동등 조건(
=)에 쓰이는 컬럼 - 범위 조건(
>,<,BETWEEN)에 쓰이는 컬럼 - 정렬(
ORDER BY)에 쓰이는 컬럼
정렬만 보고 인덱스를 만들면 검색 조건에서 손해를 볼 수 있습니다. 이 인덱스가 검색과 정렬 중 무엇을 주로 담당할 것인지를 같이 고려해야 하는 이유입니다.
정리
- 레인지 스캔 — 시작 지점을 찾아 필요한 범위만 읽음. 가장 효율적이고 일반적.
- 풀 스캔 — 인덱스 전체를 훑음. 선두 컬럼을 못 쓸 때의 차선책. 인덱스만으로 처리 가능하면 테이블 풀 스캔보다 낫다.
- 루스 인덱스 스캔 — 필요한 지점만 듬성듬성 읽고 건너뜀.
GROUP BY최적화. - 스킵 스캔 — 루스 인덱스 스캔의 확장(8.0). 선두 컬럼 조건이 없어도 인덱스를 쓸 수 있게 해주지만, 선두 컬럼의 고유값이 적을 때만 유효.
- 정순/역순 — 양방향 모두 가능하나 정순이 조금 유리한 경우가 많다. 혼합 정렬이 잦다면 내림차순 인덱스를 고려.
인덱스를 “걸었다/안 걸었다”로만 보면 성능 문제의 원인을 찾기 어렵습니다. 어떤 방식으로 읽히고 있는지를 EXPLAIN으로 확인하는 습관이 결국 튜닝의 출발점인 것 같습니다.
실무에 바로 적용하려면 EXPLAIN 결과에서 type, key, rows, Extra를 함께 확인해보세요. 같은 인덱스라도 실행 계획이 달라지면 체감 성능이 크게 달라질 수 있습니다.
📚 시리즈: Database
- 인덱스를 만들었는데 왜 안 빨라질까 — MySQL 인덱스 스캔 4가지 ← 지금 글
- MySQL은 ORDER BY를 어떻게 처리할까 — 정렬 세 가지 경로와 Sort Buffer