1 背景
最近組里來了許多新的小伙伴，大家在一起聊聊技術，有小兄弟提到了MySQL的優化器的內部策略，想起了之前在公司出現的一個線上問題，今天借著這個機會，在這里分享下過程和結論。排查的過程中，也是學習的過程，下面把排查的過程和分析記錄下來，以供大家參考。
2 過程和分析
2.1 問題發現
20年的某個下午，突然收到大量慢查詢的告警，同時業務運營在群里反饋紅包相關頁面加載慢，懷疑系統出問題了，問題發到群里之后，經過日志定位和代碼review多重確認，有一條sql成了重點懷疑對象，最終確定的原因是MySQL查詢過程中，優化器沒有選擇最優的索引導致的。
圖片
需要說明的是，這里使用的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其實并不復雜，但是執行結果竟然耗費3秒以上，對于一個面向App用戶的接口，3秒以上的響應簡直無法接受，如果對業務影響嚴重點的話，甚至于都需要寫事故報告了。
果斷祭出explAIn大法 先來看看原始的查詢情況，如下圖：
圖片
可以看到mysql并沒有命中主鍵索引，而是命中的idx_over_at索引，預估行數為41314647行，這里大家就不要糾結了，為什么這么大的表，歷史原因了，后面已經優化掉了。
MySQL官方文檔中有描述，我們可以直接強制指定優化器使用我們指定的索引。
圖片
強制指定使用主鍵索引試試
圖片
發現使用強制索引之后，sql執行0.103秒就返回了。
問題定位到這里，好像已經比較清楚了，就是MySQL優化器沒有正確選擇索引導致的唄。
MySQL：我可不背這個鍋，你們自己好好反省下。
MySQL說的有道理，為啥好端端的線上會出現3秒的慢查詢呢，這個情況之前為什么沒有呢，我們先不管人家MySQL優化器的問題，先來分析下，為什么走了idx_over_at索引之后，3秒都沒返回數據呢?
那么idx_over_at索引本身是不是有問題呢？，果然，經過排查，是因為有個小兄弟上線的代碼有bug，over_at字段被大量寫成同一個值，導致我們原本比較均勻的over_at字段存在了大量重復值，索引檢索行數指數上升，已經基本類似全表掃描。
還了MySQL清白之后，我們繼續來定位下，為什么優化器不使用更高效率的主鍵索引呢？在這個過程中，我們又發現一些奇怪的現象。
2.3 問題延伸
奇怪現象一：
圖片
驚奇的事情發生了，limit 由1 變更為3之后，走了主鍵索引。
奇怪現象二：
圖片
驚奇的事情又發生了，order by 把主鍵ID加上之后，也走了主鍵索引。
奇怪現象三：
圖片
驚奇的事情繼續發生，套了一層子查詢，也走了主鍵索引。
2.4 問題分析
MySQL：是不是很懵逼，如果碰到此類情況，請問閣下應該如何應對？
得，超出理解范疇了，沒辦法去翻文檔吧。MySql5.7官方文檔
圖片
相對來說，官方的文檔關于優化器的說明較為分散，想要快速上手的小伙伴，可以考慮觀看阿里云藏經閣出版的深入MySQL實戰一書。
附書中關于mysql執行的過程圖
圖片
再來看書中關于優化器的執行過程圖
圖片
從以上資料中，我們得出了一些結論，基于這些結論，最后我們可以思考一些解決辦法：
- 在MySQL里面，優化器的優化依據是執行成本，它的本質是CBO【Cost-based Optimizer，基于成本的優化器】，也就是說執行計劃的生成是基于成本的。
- MySQL優化器工作的前提是了解數據，工作的目的是解析SQL，生成執行計劃。但是優化器并沒有想象中的那么完善，執行成本主要基于行數去決定，但是掃描行數并不是唯一的執行策略，優化器同時會結合是否使用臨時表、是否排序、查詢數量等因素進行綜合判斷。
- 總的來說，我們上面出現的三種奇怪現象都可以用上面優化器的判斷標準去解釋，子查詢（臨時表）、order by（排序） 、limit（查詢數量）。
這里我考慮使用優化器的trace工具來詳細分析下limit 1 和 limit 3為什么走了不同索引。由于trace會影響性能，我們把部分數據還原到本地進行測試，兩次執行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 分析結果
圖片
具體參數解析如下：
- "range_analysis": {"table_scan": {"rows": 1446041, "cost": 695910 }} 表示全表掃描操作預估會掃描到大約1446041行數據，屬于非常大的操作量，全表掃描的預計代價（時間或資源消耗）為695910。
- "potential_range_indices": 列出了查詢優化器分析后認為可以使用的索引。
- 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 分析結果
圖片
具體參數解析如下：
- "rechecking_index_usage": 代表查詢優化器對我們的索引進行了重新檢查和考慮。
- {"recheck_reason": "low_limit", "limit": 1, "row_estimate": 3,} ：原因(recheck_reason)是因為 LIMIT 參數比較低（只有1），即查詢只需要返回一行記錄，而先前的索引選擇可能返回的記錄大于1（estimated 3行）。
- "range_analysis": {"table_scan": {"rows": 1446041, "cost": 1.74e6 }} 這是查詢優化器對主鍵（通常被視作一種默認索引）進行全表掃描的預估，大約有1446041行數據，預計的成本（用時 or IO次數）是1.74e6。
- "potential_range_indices": 這列出了查詢優化器考慮過的索引和它們可用性。
- PRIMARY 是第一個索引，也就是主鍵索引。它在這次查詢中并不可用。原因 not_applicable 表示這個索引在查詢時并不適用。idx_over_at 是另一個被考慮的索引，結果是可用的。
通過這段日志，我們可以知道查詢優化器為了優化查詢操作（特別是對 LIMIT 1的優化）做出了一系列的決策和調整，當limit 1的時候，查詢優化器認為不使用主鍵索引的成本會更小。因為這在優化器的成本分析中是更優更快的查詢方式。老實說，這里感覺MySQL有點自作聰明了。
3 解決思路
當我們認為SQL的執行計劃不合理時，可以使用explain 結合 trace工具去監聽整個索引的使用、以及優化器進行優化的一些過程信息，如有必要，可以通過適當的手段去干預優化器。
- 最快的解決方式應該就是強制指定主鍵索引了，這種方式在我們需要快速解決線上問題的時候，還是很好用的。但是需要注意的是，強制指定索引是有一定風險的，如果哪天哪個小伙伴在不清楚這里的邏輯之下，修改了索引，極有可能會發生線上事故。
- 在MySQL的官方文檔以及一些其他文章有特別說到，優化器的掃描行數，會隨著表的數據新增、刪除、字段變更等因素，統計的行數會變的不準確。這里可以考慮使用analyze table table_name 的方式去修復。需要注意的是，這個操作一般小伙伴是沒有權限的，涉及線上操作。安全起見，如果需要驗證，可以考慮把備份表down到本地去進行驗證。
- 通過order by 、臨時表、limit 等去干擾優化器。
- 設計合理的索引，編寫合適的查詢語句。MySQL：你這也太泛了
4 總結
這篇文章是基于工作實際中碰到的問題，把問題產生的原因和解決思路總結了下。文中針對提到的一些索引選擇差異情況我們結合了解到的優化器執行策略，使用trace工具進行了驗證。優化器有一套非常復雜的算法策略，本人對于MySQL的理解深度有限，這里就不詳細分析了，還需要繼續學習。
另外了解到MySQL 8.0優化器對查詢執行計劃的選擇做了進一步的改進，理想狀態下，會基于估算成本選擇最有效的執行計劃。感興趣的小伙伴可以去試試。
