IN 리스트가 eq_range_index_dive_limit를 넘으면 옵티마이저가 index dive를 포기해 잘못된 plan을 고른다

요약

MySQL은 IN (...) 같은 동등 범위가 여러 개일 때 각 값이 몇 행을 매칭하는지 index dive로 정확히 추정한다.
리스트 길이가 eq_range_index_dive_limit(기본 200)을 넘으면 dive를 포기하고 rough한 index statistics로 추정 → 행수 오추정 → 잘못된 plan 선택.
거대한 IN/NOT IN의 진짜 해악은 자기 실행비용이 아니라 옵티마이저의 추정을 망가뜨려 plan 선택을 깨는 것이다.

index dive vs index statistics

쿼리 실행 전 옵티마이저는 "이 조건이 몇 행을 거를까?"를 추정해 plan을 고른다. 추정 방법이 둘 있다.

방식	정확도	비용
index dive	정확 (인덱스 B-tree를 실제로 탐색해 범위별 행수를 셈)	범위마다 탐색해서 비쌈
index statistics	부정확 (미리 계산된 평균 카디널리티만 사용)	쌈

eq_range_index_dive_limit(기본 200)가 둘을 가르는 임계값이다.

IN 리스트 값 개수 ≤ 200 → 값마다 index dive (정확)
IN 리스트 값 개수 > 200 → dive 포기, 통계로 대충 추정 (1000개짜리 IN을 1000번 dive하면 추정에만 오래 걸리므로 포기하는 것)

왜 plan이 깨지나

dive를 포기하고 통계로 추정하면, 인덱스의 선택도를 실제보다 나쁘게 오추정하게 된다. 그 결과 "이 인덱스 써봐야 별로네 → 차라리 풀스캔/다른 테이블 드라이빙이 낫겠다"는 잘못된 결론으로 이어진다.

실제 사례 (추천 쿼리)

-- 후보(최근 60분 신규 ≈ 수백 명)를 뽑는 쿼리에 exclude 대상 343개가 NOT IN으로 들어감
WHERE ... AND id NOT IN ( /* 343개 */ ) AND date_joined >= '...'

검증(prod 클론에서):

NOT IN을 제거하니 옵티마이저가 즉시 account_user 인덱스를 드라이빙 → 추정 cost 177176 → 0.84, 실측 6초 → 0.1초
date_joined 인덱스를 추가만 하는 것으로는 plan이 바뀌지 않음 (NOT IN이 추정을 깨는 동안엔 인덱스를 무시). 강제(STRAIGHT_JOIN + FORCE INDEX)해야 0.1초가 나옴 → 인덱스 추가 단독은 무효.

부수 효과: 들쭉날쭉한 성능

IN 리스트 길이에 좌우되므로, exclude 대상이 적은 입력은 빠르고(≤200) 많은 입력은 느려진다(>200). "어떤 유저는 빠르고 헤비 유저는 느린" 비결정적 성능의 원인이 된다.

해결