背景交代
用 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行結果，正常情況下不應該需要這么久才對，肯定是哪里有問題。
再次手動執(zhí)行這條SQL，發(fā)現的確是這么慢，并且在最后還有個 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開始引入的，用于控制當優(yōu)化器采用范圍（RANGE）查詢優(yōu)化方案時使用的內存消耗限制。
其默認值為8MB（5.7.12及以上版本），當設置為0時，表示不做任何限制。當WHERE查詢條件里有很多OR、AND組成時，優(yōu)化器判斷超過內存消耗限制，則會調整SQL執(zhí)行計劃，變成其他執(zhí)行方案，甚至可能是全表掃描。
這也就是為什么執(zhí)行上面的大SQL后，MySQL會有這樣的告警提示了。
經過幾次簡單嘗試，把 range_optimizer_max_mem_size 選項值調大到 24MB 后，這個SQL就可以正常執(zhí)行，并且運行速度很快：
# 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萬次索引列等值條件查詢。
查詢效率提升非常顯著。
進一步優(yōu)化
線上生產環(huán)境中，各式各樣的SQL層出不窮，這次可能是一萬條OR條件，下次可能是其他的，是不能無限度增加數據庫內存消耗的。
針對本案中的SQL，更好的優(yōu)化辦法是找出這些OR條件的范圍規(guī)律，并改寫成一條更簡單的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執(zhí)行代價：
# 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條記錄。
相比上面調高內存上限的優(yōu)化方案，本次的做法則更為徹底，耗時從6.7秒直接降為6.3毫秒，提升了1000倍；掃描行數、次數和page數也下降了很多。
不過要注意的是，改寫后的SQL查詢結果和原來并不是完全一致的，實際應用中，可能還要再做進一步篩選或者增加 LIMIT N 來控制。
最后再次提醒，WHERE條件后跟著N多個OR/AND條件的寫法非常不可取，尤其是在用一些開發(fā)框架構造查詢SQL時，尤其要注意規(guī)避這個問題，否則可能造成嚴重性能問題。
延伸閱讀
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
