處理大量數值的MySQL 'IN'運算符

Question

我正在觀察我試圖理解的奇怪行為。

MySQL版本：5.7.33 我有以下查詢：

select * from a_table where time>='2022-05-10' and guid in (102,512,11,35,623,6,21,673);

a_table 在 time、guid 上有主鍵，在 guid 上有索引

我上面寫的查詢具有非常好的性能，根據解釋計劃是 using index condition;使用地點；使用MRR

當我增加 in 子句中的值數量時，效能會受到顯著影響。

經過一些演練，我得到了一個粗略的數字。對於小於 ~14500 的值，解釋計劃與上方相同。對於高於此值的數量，僅解釋計劃 使用 where 並且需要永遠運行我的查詢。

換句話說，例如，如果我在 in 子句中放入 14,000 個值，則解釋計劃將具有預期的 14,000 行。但是，如果我在 in 子句中放入 15,000 個值，則解釋有 221200324 行。我的整個表中甚至沒有這麼多行。

我正在嘗試理解這種行為，並知道是否有任何方法可以解決這個問題。

謝謝

Answer 1

了解限制記憶體用於範圍最佳化。

當 IN() 謂詞中有大量值時，它會在查詢最佳化步驟中使用更多記憶體。在某些情況下這被認為是一個問題，因此最近版本的 MySQL 設定了最大記憶體限制（預設為 8MB）。

如果優化器發現它需要比限制更多的內存，則查詢中沒有其他條件可以用來優化，它會放棄嘗試優化，並訴諸表掃描。我推斷您的表統計資料實際上顯示該表有約 2.21 億行（儘管表統計資料是不準確的估計）。

我不能說我知道給定值列表需要多少記憶體的確切公式，但根據您觀察到的行為，我們可以猜測，考慮到14k 項，每個項平均約為600 位元組有效，但更多則無效。

您可以設定range_optimizer_max_mem_size = 0來停用記憶體限制。這會產生過度使用記憶體的風險，但它避免了優化器「放棄」。我們在上一份工作中在所有 MySQL 實例上設定了這個值，因為我們無法教育開發人員避免在他們的查詢中建立巨大的值清單。