MySQL ‐ Index Dive Using In Query - thought-corner/backend-roadmap GitHub Wiki
Index Dive
- MySQL은 같은 쿼리라도 여러 실행 방법(인덱스 A, 인덱스 B, 풀스캔) 중 하나를 고른다.
- 이 때, 아무거나 고르는 게 아니라 각 방법의 예상 비용(Cost)을 계산해 가장 싼 것을 택한다. 이를 비용 기반 옵티마이저(Cost-Based Optimizer)라고 한다.
- 비용을 계산하려면 "이 조건에 몇 건이나 걸리는지"를 먼저 알아야 한다. 인덱스 다이브는 바로 이 행 수를 추정하는 기술이다.
왜 "걸리는 행 수"가 중요한가(선택도)
- 선택도(Selectivity) : 어떤 조건이 전체 데이터에서 몇 %를 걸러내는지를 나타내는 개념이다.
- 예시 : 100만 건 중 조건에 10건만 남으면 → 선택도 높음 → 인덱스 레인지 스캔이 매우 유리
- 100만 건 중 90만 건이 남으면 → 선택도 낮음 → 차라리 풀스캔이 유리할 수 있음
- 즉 옵티마이저는 "몇 건 걸리는지"를 알아야 인덱스를 쓸지 말지 판단할 수 있다.
행 수를 추정하는 두 가지 방법
A. 인덱스 통계 (Index Statistics)
ANALYZE TABLE로 미리 수집해 둔 카디널리티 값으로 대략 계산한다.- 빠르지만 부정확하다. 데이터가 고르게 분포한다고 가정하기 때문이다.
B. 인덱스 다이브 (Index Dive)
- 실행 시점에 실제 인덱스 B-Tree로 직접 내려가서(dive), 범위의 시작 경계와 끝 경계 위치를 찾고 그 사이에 몇 건이 있는지 추정한다.
- 정확하지만 비싸다. 트리를 실제로 탐색하기 때문이다.
- MySQL은 범위(range) 조건에 대해 기본적으로 이 방식을 쓴다.
| 구분 | 인덱스 통계 | 인덱스 다이브 |
|---|---|---|
| 방식 | 미리 수집한 카디널리티 | 실행 시 B-Tree 직접 탐색 |
| 정확도 | 낮음 | 높음 |
| 비용 | 쌈 | 비쌈 |
EXPLAIN의 rows는 "추정치"다
EXPLAIN의 rows 컬럼은 다이브(또는 통계)가 계산한 예상 행 수이지, 실제 값이 아니다.- 이 추정이 실제와 크게 어긋나면 옵티마이저가 잘못된 인덱스를 골라 성능이 나빠질 수 있다.
- 그래서 EXPLAIN을 볼 때 rows는 "옵티마이저가 이렇게 예상했다"로 읽어야 한다.
EXPLAIN
SELECT * FROM stock_trade_history
WHERE trade_date BETWEEN '2026-01-01' AND '2026-03-31';
IN 절의 함정과 eq_range_index_dive_limit
- 이 폭주를 막는 설정이
eq_range_index_dive_limit(기본값 200)이다
- IN 값 개수가 한도 이하 → 정확한 index dive 사용
- 그러나 한도 초과 → 다이브를 포기하고 부정확한 통계로 대체(계획 품질 저하 가능)
-- IN 값이 1,000개면 다이브도 1,000번 → 쿼리 실행 전 '최적화 단계'에서 시간 소모
WHERE stock_code IN ('005930', '000660', /* ... */ );
SHOW VARIABLES LIKE 'eq_range_index_dive_limit';
통계가 낡으면 계획이 나빠진다
- 다이브를 타지 않는 경우(또는 통계로 대체된 경우) 옵티마이저는 인덱스 통계에 의존한다.
- 대량 삽입·삭제로 데이터 분포가 크게 바뀌었는데 통계가 낡아 있으면 옵티마이저가 헛다리를 짚는다.
- 이럴 때 ANALYZE TABLE로 통계를 갱신하면 계획이 정상화될 수 있다.
ANALYZE TABLE stock_trade_history;