建站
咨詢
售后
建站咨詢
新聞中心
背景交代
用 tpcc-mysql? 工具生成 50個倉庫 的測試數(shù)據(jù)，表 order_line 共有 37970973 條記錄。
成都創(chuàng)新互聯(lián)專注于企業(yè)成都營銷網(wǎng)站建設(shè)、網(wǎng)站重做改版、桃源網(wǎng)站定制設(shè)計、自適應(yīng)品牌網(wǎng)站建設(shè)、H5響應(yīng)式網(wǎng)站、商城建設(shè)、集團公司官網(wǎng)建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)公司、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁設(shè)計等建站業(yè)務(wù)，價格優(yōu)惠性價比高，為桃源等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。
某工具在運行過程中，會產(chǎn)生下面的SQL進(jìn)行查詢，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在我的測試服務(wù)器上，運行了約56秒（另一個性能略差的機器上跑了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個page，返回8192行結(jié)果，正常情況下不應(yīng)該需要這么久才對，肯定是哪里有問題。
再次手動執(zhí)行這條SQL，發(fā)現(xiàn)的確是這么慢，并且在最后還有個 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 這個選項是干嘛用的：
文檔出處：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開始引入的，用于控制當(dāng)優(yōu)化器采用范圍（RANGE）查詢優(yōu)化方案時使用的內(nèi)存消耗限制。
其默認(rèn)值為8MB（5.7.12及以上版本），當(dāng)設(shè)置為0時，表示不做任何限制。當(dāng)WHERE查詢條件里有很多OR、AND組成時，優(yōu)化器判斷超過內(nèi)存消耗限制，則會調(diào)整SQL執(zhí)行計劃，變成其他執(zhí)行方案，甚至可能是全表掃描。
這也就是為什么執(zhí)行上面的大SQL后，MySQL會有這樣的告警提示了。
經(jīng)過幾次簡單嘗試，把 range_optimizer_max_mem_size 選項值調(diào)大到 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秒；
- 掃描行數(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í)行代價：
# 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)化方案，本次的做法則更為徹底，耗時從6.7秒直接降為6.3毫秒，提升了1000倍；掃描行數(shù)、次數(shù)和page數(shù)也下降了很多。
不過要注意的是，改寫后的SQL查詢結(jié)果和原來并不是完全一致的，實際應(yīng)用中，可能還要再做進(jìn)一步篩選或者增加 LIMIT N 來控制。
最后再次提醒，WHERE條件后跟著N多個OR/AND條件的寫法非常不可取，尤其是在用一些開發(fā)框架構(gòu)造查詢SQL時，尤其要注意規(guī)避這個問題，否則可能造成嚴(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
網(wǎng)站欄目：MySQL中WHERE后跟著N多個OR條件會怎樣...
本文來源：http://www.dlmjj.cn/article/dpjhese.html
