1 背景

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

2 過程和分析

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

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

圖片

需要說明的是，這里使用的MySQL版本是5.7版本。存儲引擎是默認的InnoDB

2.2 問題定位

涉及到的表如下：

圖片

問題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（真實表名已被隱藏），表里面有兩個索引，一個是over_at字段對應的idx_over_at索引，另一個就是bonus_id字段對應的主鍵索引。

可以看到，sql其實并不復雜，但是執(zhí)行結果竟然耗費3秒以上，對于一個面向app用戶的接口，3秒以上的響應簡直無法接受，如果對業(yè)務影響嚴重點的話，甚至于都需要寫事故報告了。

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

圖片

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

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

圖片

強制指定使用主鍵索引試試

圖片

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

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

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

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

那么idx_over_at索引本身是不是有問題呢？，果然，經(jīng)過排查，是因為有個小兄弟上線的代碼有bug，over_at字段被大量寫成同一個值，導致我們原本比較均勻的over_at字段存在了大量重復值，索引檢索行數(shù)指數(shù)上升，已經(jīng)基本類似全表掃描。

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

2.3 問題延伸

奇怪現(xiàn)象一：

圖片

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

奇怪現(xiàn)象二：

圖片

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

奇怪現(xiàn)象三：

圖片

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

2.4 問題分析

MySQL：是不是很懵逼，如果碰到此類情況，請問閣下應該如何應對？

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

圖片

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

附書中關于mysql執(zhí)行的過程圖

圖片

再來看書中關于優(yōu)化器的執(zhí)行過程圖

圖片

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

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

這里我考慮使用優(yōu)化器的trace工具來詳細分析下limit 1 和 limit 3為什么走了不同索引。由于trace會影響性能，我們把部分數(shù)據(jù)還原到本地進行測試，兩次執(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 分析結果

圖片

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

• "range_analysis": {"table_scan": {"rows": 1446041, "cost": 695910 }} 表示全表掃描操作預估會掃描到大約1446041行數(shù)據(jù)，屬于非常大的操作量，全表掃描的預計代價（時間或資源消耗）為695910。
• "potential_range_indices":  列出了查詢優(yōu)化器分析后認為可以使用的索引。
• PRIMARY 索引，在本次查詢中是可用的。這個索引基于 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 分析結果

圖片

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

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

通過這段日志，我們可以知道查詢優(yōu)化器為了優(yōu)化查詢操作（特別是對 LIMIT 1的優(yōu)化）做出了一系列的決策和調整，當limit 1的時候，查詢優(yōu)化器認為不使用主鍵索引的成本會更小。因為這在優(yōu)化器的成本分析中是更優(yōu)更快的查詢方式。老實說，這里感覺MySQL有點自作聰明了。

3 解決思路

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

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

4 總結

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

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