큰 숫자를 처리하기 위한 MySQL 'IN' 연산자

Question

나는 이해하려고 노력하는 이상한 행동을 관찰하고 있습니다.

MySQL 버전: 5.7.33 다음과 같은 쿼리가 있습니다:

으아악

a_table 在 time、guid 上有主键，在 guid

에 대한 색인 위에서 작성한 쿼리는 성능이 매우 좋으며 설명 계획에 따르면 using index condition;使用地点；使用MRR

in 절의 값 개수를 늘리면 성능에 큰 영향을 미칩니다.

몇번의 훈련 끝에 대략적인 수치를 얻었습니다. ~14500보다 작은 값의 경우 해석 방식은 위와 동일합니다. 이보다 많은 수량의 경우 계획만 해석되며 使用 where 쿼리를 실행하는 데 시간이 오래 걸립니다.

즉, 예를 들어 in 子句中放入 14,000 个值，则解释计划将具有预期的 14,000 行。但是，如果我在 in 절에 14,000개의 값을 넣으면 설명 계획의 예상 행은 14,000개가 됩니다. 그런데

절에 15,000개의 값을 넣으면 설명이 221200324행이 됩니다. 전체 테이블에 그렇게 많은 행이 없습니다.

저는 이 동작을 이해하려고 노력 중이며 이 문제를 해결할 방법이 있는지 알고 싶습니다.

감사합니다🎜

Answer 1

범위 최적화를 위한 메모리 제한을 이해하세요.

IN() 조건자에 값이 많으면 쿼리 최적화 단계에서 더 많은 메모리를 사용합니다. 이는 어떤 경우에는 문제로 간주되어 최신 버전의 MySQL에서는 최대 메모리 제한(기본값은 8MB)을 설정했습니다.

최적화 프로그램에서 제한보다 더 많은 메모리가 필요하다고 판단하면 쿼리에 최적화할 다른 조건이 없는 것이므로 최적화 시도를 포기하고 테이블 스캔을 사용합니다. 귀하의 테이블 통계에 따르면 실제로 테이블에 약 2억 2천 1백만 개의 행이 있는 것으로 나타났습니다(테이블 통계는 부정확한 추정치임에도 불구하고).

주어진 값 목록에 필요한 메모리 양에 대한 정확한 공식을 알 수는 없지만, 관찰한 동작을 토대로 14,000개 항목을 고려하면 항목당 평균 약 600바이트가 효과적인 것으로 추측할 수 있습니다. , 하지만 그 이상은 그렇지 않습니다.

range_optimizer_max_mem_size = 0을 설정하여 메모리 제한을 비활성화할 수 있습니다. 이로 인해 메모리가 과도하게 커밋될 위험이 있지만 최적화 프로그램이 "포기"되는 것을 방지할 수 있습니다. 우리는 이전 작업에서 모든 MySQL 인스턴스에 이 값을 설정했습니다. 왜냐하면 개발자들에게 쿼리에서 엄청난 양의 값 목록을 생성하지 않도록 교육할 수 없었기 때문입니다.