1 背景

最近組里來(lái)了許多新的小伙伴，大家在一起聊聊技術(shù)，有小兄弟提到了MySQL的優(yōu)化器的內(nèi)部策略，想起了之前在公司出現(xiàn)的一個(gè)線上問(wèn)題，今天借著這個(gè)機(jī)會(huì)，在這里分享下過(guò)程和結(jié)論。排查的過(guò)程中，也是學(xué)習(xí)的過(guò)程，下面把排查的過(guò)程和分析記錄下來(lái)，以供大家參考。

2 過(guò)程和分析

2.1 問(wèn)題發(fā)現(xiàn)

20年的某個(gè)下午，突然收到大量慢查詢的告警，同時(shí)業(yè)務(wù)運(yùn)營(yíng)在群里反饋紅包相關(guān)頁(yè)面加載慢，懷疑系統(tǒng)出問(wèn)題了，問(wèn)題發(fā)到群里之后，經(jīng)過(guò)日志定位和代碼review多重確認(rèn)，有一條sql成了重點(diǎn)懷疑對(duì)象，最終確定的原因是MySQL查詢過(guò)程中，優(yōu)化器沒(méi)有選擇最優(yōu)的索引導(dǎo)致的。

圖片

需要說(shuō)明的是，這里使用的MySQL版本是5.7版本。存儲(chǔ)引擎是默認(rèn)的InnoDB

2.2 問(wèn)題定位

涉及到的表如下：

圖片

問(wèn)題sql如下：

select `bonus_id`, `bonus_code`, `over_at`, `status`, `bonus_num`, `product_id` from `zz_test_table` 
where `user_id` = '16723149' and `over_at` > '2020-11-25 20:45:41' and `status` = 0 
and `bonus_id` in ('382364983', '486697270', '486834963') order by `over_at` asc limit 1;

該sql就涉及一張表zz_test_table（真實(shí)表名已被隱藏），表里面有兩個(gè)索引，一個(gè)是over_at字段對(duì)應(yīng)的idx_over_at索引，另一個(gè)就是bonus_id字段對(duì)應(yīng)的主鍵索引。

可以看到，sql其實(shí)并不復(fù)雜，但是執(zhí)行結(jié)果竟然耗費(fèi)3秒以上，對(duì)于一個(gè)面向app用戶的接口，3秒以上的響應(yīng)簡(jiǎn)直無(wú)法接受，如果對(duì)業(yè)務(wù)影響嚴(yán)重點(diǎn)的話，甚至于都需要寫(xiě)事故報(bào)告了。

果斷祭出explain大法 先來(lái)看看原始的查詢情況，如下圖：

圖片

可以看到mysql并沒(méi)有命中主鍵索引，而是命中的idx_over_at索引，預(yù)估行數(shù)為41314647行，這里大家就不要糾結(jié)了，為什么這么大的表，歷史原因了，后面已經(jīng)優(yōu)化掉了。

MySQL官方文檔中有描述，我們可以直接強(qiáng)制指定優(yōu)化器使用我們指定的索引。

圖片

強(qiáng)制指定使用主鍵索引試試

圖片

發(fā)現(xiàn)使用強(qiáng)制索引之后，sql執(zhí)行0.103秒就返回了。

問(wèn)題定位到這里，好像已經(jīng)比較清楚了，就是MySQL優(yōu)化器沒(méi)有正確選擇索引導(dǎo)致的唄。

MySQL：我可不背這個(gè)鍋，你們自己好好反省下。

MySQL說(shuō)的有道理，為啥好端端的線上會(huì)出現(xiàn)3秒的慢查詢呢，這個(gè)情況之前為什么沒(méi)有呢，我們先不管人家MySQL優(yōu)化器的問(wèn)題，先來(lái)分析下，為什么走了idx_over_at索引之后，3秒都沒(méi)返回?cái)?shù)據(jù)呢?

那么idx_over_at索引本身是不是有問(wèn)題呢？，果然，經(jīng)過(guò)排查，是因?yàn)橛袀€(gè)小兄弟上線的代碼有bug，over_at字段被大量寫(xiě)成同一個(gè)值，導(dǎo)致我們?cè)颈容^均勻的over_at字段存在了大量重復(fù)值，索引檢索行數(shù)指數(shù)上升，已經(jīng)基本類似全表掃描。

還了MySQL清白之后，我們繼續(xù)來(lái)定位下，為什么優(yōu)化器不使用更高效率的主鍵索引呢？在這個(gè)過(guò)程中，我們又發(fā)現(xiàn)一些奇怪的現(xiàn)象。

2.3 問(wèn)題延伸

奇怪現(xiàn)象一：

圖片

驚奇的事情發(fā)生了，limit 由1 變更為3之后，走了主鍵索引。

奇怪現(xiàn)象二：

圖片

驚奇的事情又發(fā)生了，order by 把主鍵ID加上之后，也走了主鍵索引。

奇怪現(xiàn)象三：

圖片

驚奇的事情繼續(xù)發(fā)生，套了一層子查詢，也走了主鍵索引。

2.4 問(wèn)題分析

MySQL：是不是很懵逼，如果碰到此類情況，請(qǐng)問(wèn)閣下應(yīng)該如何應(yīng)對(duì)？

得，超出理解范疇了，沒(méi)辦法去翻文檔吧。MySql5.7官方文檔

圖片

相對(duì)來(lái)說(shuō)，官方的文檔關(guān)于優(yōu)化器的說(shuō)明較為分散，想要快速上手的小伙伴，可以考慮觀看阿里云藏經(jīng)閣出版的深入MySQL實(shí)戰(zhàn)一書(shū)。

附書(shū)中關(guān)于mysql執(zhí)行的過(guò)程圖

圖片

再來(lái)看書(shū)中關(guān)于優(yōu)化器的執(zhí)行過(guò)程圖

圖片

從以上資料中，我們得出了一些結(jié)論，基于這些結(jié)論，最后我們可以思考一些解決辦法：

• 在MySQL里面，優(yōu)化器的優(yōu)化依據(jù)是執(zhí)行成本，它的本質(zhì)是CBO【Cost-based Optimizer，基于成本的優(yōu)化器】，也就是說(shuō)執(zhí)行計(jì)劃的生成是基于成本的。
• MySQL優(yōu)化器工作的前提是了解數(shù)據(jù)，工作的目的是解析SQL，生成執(zhí)行計(jì)劃。但是優(yōu)化器并沒(méi)有想象中的那么完善，執(zhí)行成本主要基于行數(shù)去決定，但是掃描行數(shù)并不是唯一的執(zhí)行策略，優(yōu)化器同時(shí)會(huì)結(jié)合是否使用臨時(shí)表、是否排序、查詢數(shù)量等因素進(jìn)行綜合判斷。
• 總的來(lái)說(shuō)，我們上面出現(xiàn)的三種奇怪現(xiàn)象都可以用上面優(yōu)化器的判斷標(biāo)準(zhǔn)去解釋，子查詢（臨時(shí)表）、order by（排序） 、limit（查詢數(shù)量）。

這里我考慮使用優(yōu)化器的trace工具來(lái)詳細(xì)分析下limit 1 和 limit 3為什么走了不同索引。由于trace會(huì)影響性能，我們把部分?jǐn)?shù)據(jù)還原到本地進(jìn)行測(cè)試，兩次執(zhí)行sql分別如下：

trace分析LIMIT 3

set optimizer_trace="enabled=on";
select `bonus_id`, `bonus_code`, `over_at`, `status`, `bonus_num`, `product_id` from `zz_test_table` where `user_id` = '16723149' and `over_at` > '2020-11-25 20:45:41' and `status` = 0 and `bonus_id` in ('382364983', '486697270', '486834963') order by `over_at` asc limit 3
select * FROM information_schema.optimizer_trace;
set optimizer_trace="enabled=off";

LIMIT 3 分析結(jié)果

圖片

具體參數(shù)解析如下：

• "range_analysis": {"table_scan": {"rows": 1446041, "cost": 695910 }} 表示全表掃描操作預(yù)估會(huì)掃描到大約1446041行數(shù)據(jù)，屬于非常大的操作量，全表掃描的預(yù)計(jì)代價(jià)（時(shí)間或資源消耗）為695910。
• "potential_range_indices":  列出了查詢優(yōu)化器分析后認(rèn)為可以使用的索引。
• PRIMARY 索引，在本次查詢中是可用的。這個(gè)索引基于 bonus_id 這一列，idx_over_at 索引，也在本次查詢中是可用的。

trace分析LIMIT 1

set optimizer_trace="enabled=on";
select `bonus_id`, `bonus_code`, `over_at`, `status`, `bonus_num`, `product_id` from `zz_test_table` where `user_id` = '16723149' and `over_at` > '2020-11-25 20:45:41' and `status` = 0 and `bonus_id` in ('382364983', '486697270', '486834963') order by `over_at` asc limit 1
select * FROM information_schema.optimizer_trace;
set optimizer_trace="enabled=off";

LIMIT 1 分析結(jié)果

圖片

具體參數(shù)解析如下：

• "rechecking_index_usage": 代表查詢優(yōu)化器對(duì)我們的索引進(jìn)行了重新檢查和考慮。
• {"recheck_reason": "low_limit", "limit": 1, "row_estimate": 3,} ：原因(recheck_reason)是因?yàn)?LIMIT 參數(shù)比較低（只有1），即查詢只需要返回一行記錄，而先前的索引選擇可能返回的記錄大于1（estimated 3行）。
• "range_analysis": {"table_scan": {"rows": 1446041, "cost": 1.74e6 }} 這是查詢優(yōu)化器對(duì)主鍵（通常被視作一種默認(rèn)索引）進(jìn)行全表掃描的預(yù)估，大約有1446041行數(shù)據(jù)，預(yù)計(jì)的成本（用時(shí) or IO次數(shù)）是1.74e6。
• "potential_range_indices": 這列出了查詢優(yōu)化器考慮過(guò)的索引和它們可用性。
• PRIMARY 是第一個(gè)索引，也就是主鍵索引。它在這次查詢中并不可用。原因 not_applicable 表示這個(gè)索引在查詢時(shí)并不適用。idx_over_at 是另一個(gè)被考慮的索引，結(jié)果是可用的。

通過(guò)這段日志，我們可以知道查詢優(yōu)化器為了優(yōu)化查詢操作（特別是對(duì) LIMIT 1的優(yōu)化）做出了一系列的決策和調(diào)整，當(dāng)limit 1的時(shí)候，查詢優(yōu)化器認(rèn)為不使用主鍵索引的成本會(huì)更小。因?yàn)檫@在優(yōu)化器的成本分析中是更優(yōu)更快的查詢方式。老實(shí)說(shuō)，這里感覺(jué)MySQL有點(diǎn)自作聰明了。

3 解決思路

當(dāng)我們認(rèn)為SQL的執(zhí)行計(jì)劃不合理時(shí)，可以使用explain 結(jié)合 trace工具去監(jiān)聽(tīng)整個(gè)索引的使用、以及優(yōu)化器進(jìn)行優(yōu)化的一些過(guò)程信息，如有必要，可以通過(guò)適當(dāng)?shù)氖侄稳ジ深A(yù)優(yōu)化器。

• 最快的解決方式應(yīng)該就是強(qiáng)制指定主鍵索引了，這種方式在我們需要快速解決線上問(wèn)題的時(shí)候，還是很好用的。但是需要注意的是，強(qiáng)制指定索引是有一定風(fēng)險(xiǎn)的，如果哪天哪個(gè)小伙伴在不清楚這里的邏輯之下，修改了索引，極有可能會(huì)發(fā)生線上事故。
• 在MySQL的官方文檔以及一些其他文章有特別說(shuō)到，優(yōu)化器的掃描行數(shù)，會(huì)隨著表的數(shù)據(jù)新增、刪除、字段變更等因素，統(tǒng)計(jì)的行數(shù)會(huì)變的不準(zhǔn)確。這里可以考慮使用analyze table table_name 的方式去修復(fù)。需要注意的是，這個(gè)操作一般小伙伴是沒(méi)有權(quán)限的，涉及線上操作。安全起見(jiàn)，如果需要驗(yàn)證，可以考慮把備份表down到本地去進(jìn)行驗(yàn)證。
• 通過(guò)order by 、臨時(shí)表、limit 等去干擾優(yōu)化器。
• 設(shè)計(jì)合理的索引，編寫(xiě)合適的查詢語(yǔ)句。MySQL：你這也太泛了

4 總結(jié)

這篇文章是基于工作實(shí)際中碰到的問(wèn)題，把問(wèn)題產(chǎn)生的原因和解決思路總結(jié)了下。文中針對(duì)提到的一些索引選擇差異情況我們結(jié)合了解到的優(yōu)化器執(zhí)行策略，使用trace工具進(jìn)行了驗(yàn)證。優(yōu)化器有一套非常復(fù)雜的算法策略，本人對(duì)于MySQL的理解深度有限，這里就不詳細(xì)分析了，還需要繼續(xù)學(xué)習(xí)。

另外了解到MySQL 8.0優(yōu)化器對(duì)查詢執(zhí)行計(jì)劃的選擇做了進(jìn)一步的改進(jìn)，理想狀態(tài)下，會(huì)基于估算成本選擇最有效的執(zhí)行計(jì)劃。感興趣的小伙伴可以去試試。