| 作者 王文安，騰訊CSIG資料庫專項的資料庫工程師，主要負責騰訊云資料庫 MySQL 的相關的作業，熱愛技術，歡迎留言進行交流，
作為一個 DBA，想必都有過被慢查詢折騰的經歷，一個慢查詢有時候真的很讓人抓狂，本文對常規和非常規手段進行了整理，由淺及深，簡單介紹幾個慢查詢的分析手段，
需要說明的是，下面所有的手段都是原生支持的功能（≥MySQL 5.6），因此在各類 RDS 和原生資料庫中都不會有什么使用上的差異，這里圖方便就用騰訊云資料庫 MySQL 來作為測驗環境了，版本為 5.7，
第一步：EXPLAIN
最先登場的毫無疑問就是 EXPLAIN 陳述句了，用過 MySQL 的人應該都知道這個查看 SQL 陳述句執行計劃的命令，詳細的資料在網上有很多，這里就略過了，一般來說，95% 的慢查詢問題只需要 EXPLAIN 就可以解決了，手工執行的時候，在 Extra 列里面，避免出現Use Temporary Table和Using file sort這類關鍵字，TYPE 列中也盡量避免 ALL 型別（全表掃描）出現，
其實目前這個最常用的功能在騰訊云上可以直接用 DBbrain 來進行操作了，DBbrain 會分析 SQL 陳述句并給出加索引的建議，在DBbrain中選擇對應的實體，進入 SQL 診斷的 tab 下，點擊具體的慢查詢就可以看到加索引的建議了：
第二步：PROFILE
既然 EXPLAIN 能看到 SQL 的執行計劃，能判斷出來有沒有好好利用索引，DBbrain 也能給出索引的優化建議，那么慢查詢的分析為什么還會有三步曲？
原因很簡單，MySQL 慢查詢，并不一定慢在有沒有索引；SQL 的執行環節中任意一環出了問題都會表現為查詢變慢，所以用了索引，EXPLAIN 的結果也很完美，但是還是慢，怎么辦？
這時候，就需要 PROFILE 來幫忙了，這個命令可以詳細的列出 SQL 陳述句在每一個步驟消耗的時間，前提（缺點）是先執行一遍陳述句，
PROFILE 默認是關閉的，所以需要在 client 端先打開，操作如下：
set session profiling = 1;
在實際的生產環境中，可能會需要加大profile的佇列，保證想要查看的 PROFILE 結果還保存著，因此可以用如下操作來增加 PROFILE 的佇列大小
set session profiling_history_size = 50;
到這一步，PROFILE 的功能就開啟了，這里先洗掉索引，簡單試一下 SQL 陳述句，EXPLAIN 一下看看輸出
TYPE 列是 ALL，顯然這種陳述句是不合格的，“假設”索引“覺得”沒問題，但是這個陳述句還是比預想的要慢，那么可以看看這條陳述句各個階段的耗時，先執行一次 select，然后再查看 PROFILE 的結果：
可以看到 id 為 11 的那一行就是執行過的陳述句，這時候使用show profile block io,cpu,memory,source for query 11;來查看統計資訊：
Sending data 并不只是在服務器端和客戶端之間 Sending data，還包括了從磁盤讀取資料的時間，因為這個查詢執行了全表掃描，所以這個時間會比較高，當然索引的效率不高也會導致這部分時間比較久，
如果還有 order by 的話，這里面也會出現 Sort 相關的資訊，
經過了這兩部曲之后，基本上一個 SQL 為什么慢，慢在哪里基本上可以定位出來了，那么最后的手段主要是解決什么問題呢？
第三步：OPTIMIZER_TRACE
OPTIMIZER_TRACE 是 MySQL 5.6 添加的新功能，顧名思義，這個功能可以看到內部查詢計劃的 TRACE 資訊，從而可以知道 MySQL 是如何在眾多索引中選中最“棒”的那個，一般來說，這個最“棒”的索引選錯了，就需要根據 OPTIMIZER_TRACE 的資訊來判斷為什么會選錯，是 MySQL 的配置原因，還是 SQL 某些地方寫的不好導致 MySQL 誤判了，
開啟這個功能的方式如下：
set session optimizer_trace='enabled=on';
隨便執行一個 EXPLAIN 陳述句，生成一個執行計劃，然后在information_chema.optimizer_trace的表里面查找這一條陳述句對應的資訊：
內容是非常長的 JSON 格式，所以推薦把結果轉存到其他地方，然后用 JSON 的轉換工具來輔助查看，如果要看索引的選擇情況，就重點關注這個 JSON 的ref_optimizer_key_uses，rows_estimation 及之后的部分，這里會展示索引選擇相關的資訊，截取一部分結果作為示例：
在這里面能看到詳細的統計資訊，包括 cost，預計的 rows，在之后的內容中也會顯示最終選擇的索引：
通常來說，cost 數值越低，代表這個執行計劃的執行速度越快，
總結
其實在絕大多數的情況下，EXPLAIN 完全可以勝任，在騰訊云平臺上的話，用 DBbrain 即可，PROFILE 一般是用來決定分析和判斷的方向，看看是哪個階段比較慢，OPTIMIZER_TRACE 主要用來分析各種疑難雜癥，比如說優化器為什么沒有選擇索引而是全表掃描？為什么優化器沒有選擇效率較好的索引，而是選擇了一個效率較差的索引（order by，limit）等等，
總而言之，通過這三步曲的排查，基本上 SQL 的問題就都能找出來了，好好掌握這些基本技能對于 DBA 來說還是很有用的，
本文由博客一文多發平臺 OpenWrite 發布！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/233001.html
標籤：其他
上一篇：.NET Core 3.0X及以上版本的EFCore連接MySql 8.022
下一篇：Redis為什么這么快？