기본키를 in 조건에 넣었을 때 full scan 되는 현상(range_optimizer_max_mem_size)

mysql version : 8.0.31

 

사내에서 prisma interactive transaction의 타임아웃을 60초로 설정하여 사용하고 있다. 하지만 트랜잭션에서 수행되는 로직이 60초를 넘어 time out이 발생하는 일이 간헐적으로 발생했다. 단순히 트랜잭션의 time out 시간을 늘려도 되지만, 어떤 쿼리가 왜 오래 걸리는지 확인해 보았다.

 

prisma query log 옵션을 줘서 쿼리 실행시간을 측정했을 때 아래의 쿼리가 오래 걸리는 것을 확인할 수 있었다.

해당 테이블에 있는 row수는 5571220개 이다.

explain SELECT *
FROM CourierInvoice
WHERE id IN (....)

 

 

in에 들어가는 변수 10302개 기준으로 16s가 소요되었다.

 

innodb는 기본적으로 클러스터링 인덱스를 지원하기 때문에, range 인덱스 레인지 스캔을 한다고 생각했는데 16s가 걸리는좀 이상하다고 생각했다.

 

실행계획을 보면, type이 ALL로 되어있다. 이는 옵티마이저가 실행계획을 수립하는 과정에서 풀스캔을 사용하여 데이터를 조회했다는 것을 의미한다. 또한, key값이 null이라는 것은 최종 선택된 인덱스가 존재하지 않는다는 것이다. possible_keys의 PRIMARY는 옵티마이저가 최적의 실행 계획을 만들기 위해 후보이며, 실제로 사용할 수도 있었던 키를 의미한다.

 

rows는 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해서 비용을 산정하는데, 그 값이 실제 row 5백만과 거의 같다.

 

 

옵티마이저가 full scan을 사용한 이유는 range_optimizer_max_mem_size 값과 연관이 있다.

range optimizer에 사용가능한 메모리를 제어하는 변수이다.

옵티마이저는 range access 방법을 사용할 때, 쿼리의 메모리 소비량을 계산하고 만약 그 값이 range_optimizer_max_mem_size보다 크면, range access 방법을 버리고 full scan을 포함한 다른 방법을 사용하게 된다. 

 

range_optimizer_max_mem_size값을 0으로 변경하고 실행계획을 보면 원하는 옵티마이저가 range scan을 하는 것을 알 수 있다. 또한 최종적으로 PRIMARY KEY를 사용하여 index range scan을 한 것을 알 수 있다.

 

또한 실행시간도 16s -> 1s로 90%이상 감소한 것을 확인할 수 있다.

 

0으로 설정하고 메모리 제한을 해제하는것이 맘이 편할(?)수 있지만, in에 들어가는 값은 가변적이기 때문에 0으로 변경하는 것보단 적당한 값을 선택하여 변경하는 것이 좋을 것 같다.

 

---

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_range_optimizer_max_mem_size

https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-optimization-memory-use