MySQL ‐ Index Dive Using in Query

📚 Index Dive

• MySQL 서버는 여러 통계 정보를 이용해 가장 빠른 쿼리 실행 방식을 결정하는데, 이를 우리는 흔히 최적화라고 한다.
• Oracle이나 PostgreSQL 서버는 쿼리의 실행 계획을 캐시해서 재활용하는 반면, MySQL 서버에서는 아직 쿼리 실행 계획의 캐시 또는 재활용이 필수적으로 사용되지 않는 편이다. 왜냐하면 MySQL 서버의 쿼리 실행 계획이 커넥션내에서만 캐시되고 재활용될 수 있기 때문이다.
• MySQL 서버에서 쿼리가 실행되면, 옵티마이저(Optimizer)는 통계 정보뿐만 아니라 실제 테이블의 데이터를 샘플링해서 확인 후, 최종 사용할 실행 계획을 선택한다.
• 이 때, 실제 데이터를 샘플링해서 확인하는 과정을 Index Dive 또는 Random Index Dive라고 한다.

주로 WHERE절에 IN이나 OR조건이 여러 개 사용될 때

SELECT * FROM users WHERE user_id IN (1, 2, 3, ..., 100);

• 위의 쿼리를 받은 옵티마이저는 user_id 인덱스를 사용해 1, 2, 3, 100일 때 각각 얼마나 많은 데이터가 있는지 확인하기 위해 인덱스를 반복적으로 탐색한다.
• IN절 안의 값이 적다면 문제가 되지 않는데, 수백, 수천 개로 늘어나면 이 확인 과정 자체에 상당한 시간과 자원이 소모되어 오히려 쿼리의 실행 속도가 현저히 느려지게 된다.
• 이처럼 Index Dive로 인해 쿼리 최적화 단계에서 과도한 시간이 소요되어 전체적인 쿼리 성능이 저하되는 현상을 Index Dive 문제라고 부른다.

참고 - Index Range Scan의 작동 원리

• 참고 문서에 따르면 MySQL 서버에는 eq_range_index_dive_limit라는 옵션에 따라 실행 계획 선택 방식이 IN절 개수에 따라 달라진다고 한다.
• 현대 MySQL 서버는 대부분 8.0 이상을 지원하기 때문에 기본적으로는 200개로 정해져있다.
• 200개를 초과할 경우 생각하지 못한 성능 저하가 발생할 수 있다고 알고 있으면 될 것 같다.