MySQL ‐ Index Dive Using In Query

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;