[Mysql] IN쿼리가 인덱스를 타지 않는 현상

실 운영 환경을 Mysql5.6 -> 5.7로 업그레이드 했을 때 발생하던 현상이였다.

분명히 같은 IN쿼리지만, 5.6에서는 인덱스를 타던 것이 5.7로 넘어오면서 인덱스를 타지 않게 된 것이다.

정확히 말하자면, 아예 안타는 것은 아니고 id IN (대략 만개이상) 의 경우에 인덱스를 타지 않았다.

 

즉, 특정 개수 이상의 ID를 IN쿼리 안에 넣으면 인덱스를 타지 않았다.

결론부터 말하면 쿼리 자체를 메모리에 올리는 것 또한 메모리 제한이 있었다.

 

그 제한은 range_optimizer_max_mem_size 옵션을 통해 확인할 수 있다.

> 참고링크 : https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#range-optimization-memory-use

 

문서를 참고해보면 알겠지만, 해당 옵션을 0으로 셋팅해주면 쿼리를 올리는데 제한이 없다는 것이다.

즉, 우리가 맞이한 이슈 IN쿼리에 무한정 아이디를 넣어도 인덱스가 제대로 타진다는 것이다.

 

만약 0이 아닌 값이 셋팅되어 있다면, 그 값을 초과했을 경우 옵티마이져는 풀스캔 혹은 이외의 인덱스를 태우기도 한다.

IN쿼리는 우리 눈에는 함축적으로 보이지만, 사실 상은 OR쿼리가 조합된 것이므로 메모리에 특이나 주의한다.

SELECT COUNT(\*) FROM t WHERE a=1 OR a=2 OR a=3 ... OR a=N;

하나의 OR당 대략 230byte를 차지한다.

 

그러므로 230byte * N이 이 쿼리의 용량이 되는데, 이 용량이 range_optimizer_max_mem_size보다 크다면 인덱스를 타지 못한다.

(참고로 AND조건은 하나당 대략 125byte를 차지한다.)

 

 

그렇다면 다음과 같은 쿼리의 용량은 어떨까?

SELECT COUNT(\*) FROM t WHERE a IN (1, 2, ..., N) AND b IN (1, 2, ..., M)

위에서 설명한 것을 가지고 계산하면 230byte * N * M이 될것이다.

 

여기서 주의할점은 Mysql5.7.11 이전 버전은 OR쿼리가 230byte가 아닌 대략 700byte라고 한다.

 

 

 

 

 

 

 

 

* 잘못된 점이 있으면 댓글로 남겨주시면 감사하겠습니다.