背景交代

用 tpcc-mysql? 工具生成 50個(gè)倉庫 的測試數(shù)據(jù)，表 order_line 共有 37970973 條記錄。

某工具在運(yùn)行過程中，會產(chǎn)生下面的SQL進(jìn)行查詢，WHERE后跟了N多個(gè)條件：

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多個(gè)，是指總共有10000個(gè)OR條件，這條SQL的長度大概將近800KB。

這條SQL在我的測試服務(wù)器上，運(yùn)行了約56秒（另一個(gè)性能略差的機(jī)器上跑了1800秒左右才完成），共掃描75563行記錄，返回8192行結(jié)果：

# 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 ...

相當(dāng)于只做了1次索引范圍查詢，但總共要掃描7.5萬條數(shù)據(jù)。

問題分析

只需要掃描 7.5萬行記錄，501個(gè)page，返回8192行結(jié)果，正常情況下不應(yīng)該需要這么久才對，肯定是哪里有問題。

再次手動(dòng)執(zhí)行這條SQL，發(fā)現(xiàn)的確是這么慢，并且在最后還有個(gè) warnings 提醒，查看下是啥內(nèi)容：

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 這個(gè)選項(xiàng)是干嘛用的：

文檔出處：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.

這個(gè)選項(xiàng)是從MySQL 5.7.9開始引入的，用于控制當(dāng)優(yōu)化器采用范圍（RANGE）查詢優(yōu)化方案時(shí)使用的內(nèi)存消耗限制。

其默認(rèn)值為8MB（5.7.12及以上版本），當(dāng)設(shè)置為0時(shí)，表示不做任何限制。當(dāng)WHERE查詢條件里有很多OR、AND組成時(shí)，優(yōu)化器判斷超過內(nèi)存消耗限制，則會調(diào)整SQL執(zhí)行計(jì)劃，變成其他執(zhí)行方案，甚至可能是全表掃描。

這也就是為什么執(zhí)行上面的大SQL后，MySQL會有這樣的告警提示了。

經(jīng)過幾次簡單嘗試，把 range_optimizer_max_mem_size 選項(xiàng)值調(diào)大到 24MB 后，這個(gè)SQL就可以正常執(zhí)行，并且運(yùn)行速度很快：

# 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

注意到幾個(gè)變化：

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

相當(dāng)于做了1萬次索引列等值條件查詢。

查詢效率提升非常顯著。

進(jìn)一步優(yōu)化

線上生產(chǎn)環(huán)境中，各式各樣的SQL層出不窮，這次可能是一萬條OR條件，下次可能是其他的，是不能無限度增加數(shù)據(jù)庫內(nèi)存消耗的。

針對本案中的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í)行代價(jià)：

# 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

相當(dāng)于只做了1次索引范圍查詢，且只需掃描9883條記錄。

相比上面調(diào)高內(nèi)存上限的優(yōu)化方案，本次的做法則更為徹底，耗時(shí)從6.7秒直接降為6.3毫秒，提升了1000倍；掃描行數(shù)、次數(shù)和page數(shù)也下降了很多。

不過要注意的是，改寫后的SQL查詢結(jié)果和原來并不是完全一致的，實(shí)際應(yīng)用中，可能還要再做進(jìn)一步篩選或者增加 LIMIT N 來控制。

最后再次提醒，WHERE條件后跟著N多個(gè)OR/AND條件的寫法非常不可取，尤其是在用一些開發(fā)框架構(gòu)造查詢SQL時(shí)，尤其要注意規(guī)避這個(gè)問題，否則可能造成嚴(yán)重性能問題。

延伸閱讀

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