MySQL中WHERE後跟著N多個OR條件會是你期望的結果嗎？

導讀	不過要注意的是，改寫後的SQL查詢結果和原來並不是完全一致的，實際應用中，可能還要再做進一步篩選或者增加 LIMIT N 來控制。

用 tpcc-mysql 工具生成 50個倉庫 的測試資料，表 order_line 共有 37970973 條記錄。

某工具在執行過程中，會產生下面的SQL進行查詢，WHERE後跟了N多個條件：

mysql> select * from order_line where 
   (ol_w_id = '1' and ol_d_id = '1' and ol_o_id = '2221' and ol_number = '5') 
or (ol_w_id = '1' and ol_d_id = '1' and ol_o_id = '2225' and ol_number = '1')
or (ol_w_id = '1' and ol_d_id = '1' and ol_o_id = '2155' and ol_number = '2')
...

這裡說的N多個，是指總共有10000個OR條件，這條SQL的長度大概將近800KB。

這條SQL在我的測試伺服器上，執行了約56秒（另一個效能略差的機器上跑了1800秒左右才完成），共掃描75563行記錄，返回8192行結果：

# Query_time: 56.031955  Lock_time: 0.047795 Rows_sent: 8129  Rows_examined: 75563 ... Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 75563 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 ...
...
#   InnoDB_pages_distinct: 501
...
select * from order_line where ...

相當於只做了1次索引範圍查詢，但總共要掃描7.5萬條資料。

只需要掃描 7.5萬行記錄，501個page，返回8192行結果，正常情況下不應該需要這麼久才對，肯定是哪裡有問題。

再次手動執行這條SQL，發現的確是這麼慢，並且在最後還有個 warnings 提醒，檢視下是啥內容：

mysql> show warnings\G
...
  Level: Warning
   Code: 3170
Message: Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.

第一次見到這種告警，先檢查MySQL手冊，看看 range_optimizer_max_mem_size 這個選項是幹嘛用的：

文件出處：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_range_optimizer_max_mem_size
The limit on memory consumption for the range optimizer. A value of 0 means “no limit.” 
If an execution plan considered by the optimizer uses the range access method but 
the optimizer estimates that the amount of memory needed for this method would 
exceed the limit, it abandons the plan and considers other plans. For more 
information, see Limiting Memory Use for Range Optimization.

這個選項是從MySQL 5.7.9開始引入的，用於控制當最佳化器採用範圍（RANGE）查詢最佳化方案時使用的記憶體消耗限制。

其預設值為8MB（5.7.12及以上版本），當設定為0時，表示不做任何限制。當WHERE查詢條件裡有很多OR、AND組成時，最佳化器判斷超過記憶體消耗限制，則會調整SQL執行計劃，變成其他執行方案，甚至可能是全表掃描。

這也就是為什麼執行上面的大SQL後，MySQL會有這樣的告警提示了。

經過幾次簡單嘗試，把 range_optimizer_max_mem_size 選項值調大到 24MB 後，這個SQL就可以正常執行，並且執行速度很快：

# Query_time: 6.721209  Lock_time: 0.044637 Rows_sent: 8129  Rows_examined: 8129 Read_first: 0 Read_last: 0 Read_key: 10000 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 ...
...
#   InnoDB_pages_distinct: 81

注意到幾個變化：

• 耗時從56秒降到6.7秒；
• 掃描行數從7.5萬行降到8192行（返回結果數不變）；
• Read_key從1增加到10000；
• Read_next從75563降到0；
• 掃描的page數從501降到81。

相當於做了1萬次索引列等值條件查詢。

查詢效率提升非常顯著。

進一步最佳化

線上生產環境中，各式各樣的SQL層出不窮，這次可能是一萬條OR條件，下次可能是其他的，是不能無限度增加資料庫記憶體消耗的。

針對本案中的SQL，更好的最佳化辦法是找出這些OR條件的範圍規律，並改寫成一條更簡單的SQL，類似下面這樣：

mysql> select * from order_line where
ol_w_id = 1 and ol_d_id = 1 and (ol_o_id between 2007 and 2997) 
and (ol_number between 1 and 15 );

新的SQL執行代價：

# Query_time: 0.006338  Lock_time: 0.000084 Rows_sent: 9883  Rows_examined: 9883...Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 9883 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0...
...
#   InnoDB_pages_distinct: 81

相當於只做了1次索引範圍查詢，且只需掃描9883條記錄。

相比上面調高記憶體上限的最佳化方案，本次的做法則更為徹底，耗時從6.7秒直接降為6.3毫秒，提升了1000倍；掃描行數、次數和page數也下降了很多。

不過要注意的是，改寫後的SQL查詢結果和原來並不是完全一致的，實際應用中，可能還要再做進一步篩選或者增加 LIMIT N 來控制。

最後再次提醒，WHERE條件後跟著N多個OR/AND條件的寫法非常不可取，尤其是在用一些開發框架構造查詢SQL時，尤其要注意規避這個問題，否則可能造成嚴重效能問題。

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

Limiting Memory Use for Range Optimization,https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-optimization-memory-use

原文來自：