看本篇之前建議先看上篇：MySQL優化：定位慢查詢的兩種方法以及使用explain分析SQL
在上一節我們學習了定位慢 SQL 及使用 explain 分析慢 SQL，我們也提到了分析慢 SQL 還有 show profile 和 trace 等方法，本節就重點補充學習這兩種方法。
show profile 分析慢查詢
NO.1 使用 profile 分析慢查詢
大致步驟：
確定這個 MySQL 版本是否支持 profile；確定 profile 是否關閉；如果關閉開啟 profile；在伺服器端發送要執行的 SQL；查看執行完 SQL 的 query id；通過 query id 查看 SQL 的每個狀態及耗時時間；停止profile；
獲取profile 使用 help profile即可
mysql> help profile;
1.1 確定是否支持 profile
mysql> select @@have_profiling;
如果結果是YES表示支持。
1.2 查看 profiling 狀態
mysql> select @@profiling;
0表示關閉，1表示開啟，默認是關閉的。
使用如下命令開啟：mysql> set profiling=1;
1.3 執行要分析的sql語句
mysql> select * from test_table where d=90000;
執行完成SQL後再執行：show profiles；得到profile id。
1.4 根據profile id查詢指定SQL執行詳情
通過 show profile for query id 可看到執行過的 SQL 每個狀態和消耗時間：
MySQL [test]> show profile for query 1;
通過以上結果，可以確定 SQL 執行過程具體在哪個過程耗時比較久，從而更好地進行 SQL 優化與調整。
注意：「show profiles」已棄用，SHOW PROFILES將來會被Performance Schema替換掉，但是現在還是非常非常實用的，8.0目前還在支持中。
小計：實用show profile 可以直接查看上一條SQL語句的執行開銷
MySQL [test]> show profile;
NOTE：show profile自身不會產生Profiling。
查看指定SQL的CPU開銷：MySQL [test]> show profile cpu for query 1;
查詢指定SQL的內存開銷：MySQL [test]> show profile memory for query 1;
小技巧：給大家分享一條SQL，查詢某條SQL開銷並且按照耗時倒敘排序
SET @query_id = 1; // 設置要查詢的 profile id，然後執行如下SQL即可SELECT STATE, SUM(DURATION) AS Total_R,ROUND(100 * SUM(DURATION) /(SELECT SUM(DURATION)FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query_id ), 2) AS Pct_R,COUNT(*) AS Calls,SUM(DURATION) / COUNT(*) AS "R/Call" FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query_id GROUP BY STATE ORDER BY Total_R DESC;
注意：Mysql所有的profile都被記錄到了information_schema.profiling表。
1.5 關閉profile
mysql> set profiling=off;
trace 分析 SQL 優化器
從前面學到了 explain 可以查看 SQL 執行計劃，但是無法知道它為什麼做這個決策，如果想確定多種索引方案之間是如何選擇的或者排序時選擇的是哪種排序模式，有什麼好的辦法嗎？
從 MySQL 5.6 開始，可以使用 trace 查看優化器如何選擇執行計劃。
通過trace，能夠進一步了解為什麼優化器選擇A執行計劃而不是選擇B執行計劃，或者知道某個排序使用的排序模式，幫助我們更好地理解優化器行為。
如果需要使用，先開啟 trace，設置格式為 JSON，再執行需要分析的 SQL，最後查看 trace 分析結果（在 information_schema.OPTIMIZER_TRACE 中）。
注意：開啟該功能，會對 MySQL 性能有所影響，因此只建議分析問題時臨時開啟。
這裡利用我們上篇：MySQL優化：定位慢查詢的兩種方法以及使用explain分析SQL中創建的數據表test_table;
在test_table中除了d欄位，abc欄位都有索引。執行如下sql
explain select * from test_table where a=90000 and b=90000 order by a;
通過上面執行計劃中 key 這個欄位可以看出，該語句使用的是 b 欄位的索引 idx_a。實際表 t1 中，a、b 兩個欄位都有索引，為什麼條件中有這兩個索引欄位卻偏偏選了 a 欄位的索引呢？這時就可以使用 trace 進行分析。
大致步驟如下：
開啟trace分析器執行要查詢的sql查看分析結果關閉trace分析器
NO.1 開啟trace分析器
MySQL [test]> set session optimizer_trace="enabled=on";
NO.2 執行要查詢的SQL
MySQL [test]> select * from test_table where a=90000 and b=90000 order by a;
NO.3 查詢分析結果
MySQL [test]> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
注意：在返回的steps數組中可以查看詳細mysql都幹了什麼。
不好意思，因為排版導致內容太長大家可以百度一下參數含義。
NO.4 關閉trace分析器
mysql> set session optimizer_trace="enabled=off";
TRACE 欄位中整個文本大致分為三個過程
準備階段：對應文本中的 join_preparation優化階段：對應文本中的 join_optimization執行階段：對應文本中的 join_execution
使用時，重點關注優化階段和執行階段。
知識點拓展：
知識點一：MySQL 常見排序模式：
< sort_key, rowid >雙路排序（又叫回表排序模式）：是首先根據相應的條件取出相應的排序欄位和可以直接定位行數據的行 ID，然後在 sort buffer 中進行排序，排序完後需要再次取回其它需要的欄位；< sort_key, additional_fields >單路排序：是一次性取出滿足條件行的所有欄位，然後在sort buffer中進行排序；< sort_key, packed_additional_fields >打包數據排序模式：將 char 和 varchar 欄位存到 sort buffer 中時，更加緊縮。
三種排序模式比較：
第二種模式相對第一種模式，避免了二次回表，可以理解為用空間換時間。由於 sort buffer 有限，如果需要查詢的數據比較大的話，會增加磁碟排序時間，效率可能比第一種方式更低。MySQL 提供了一個參數：max_length_for_sort_data，當「排序的鍵值對大小」 > max_length_for_sort_data 時，MySQL 認為磁碟外部排序的 IO 效率不如回表的效率，會選擇第一種排序模式；否則，會選擇第二種模式。第三種模式主要解決變長字符數據存儲空間浪費的問題。知識點二：優化器在估計符合條件的行數時有兩個選擇：
index diver：dive 到 index 中利用索引完成元組數的估算；特點是速度慢，但可以得到精確的值；
index statistics：使用索引的統計數值，進行估算；特點是速度快，但是值不一定準確。
總結
今天我們分享了 show profile 和 trace 的使用方法，我們來對比一下三種分析 SQL 方法的特點：
explain：獲取 MySQL 中 SQL 語句的執行計劃，比如語句是否使用了關聯查詢、是否使用了索引、掃描行數等；
profile：可以清楚了解到SQL到底慢在哪個環節；
trace：查看優化器如何選擇執行計劃，獲取每個可能的索引選擇的代價。
三種方法各有其適用場景，如果你有其它分析 SQL 的工具，歡迎在留言區分享。
最後推薦大家一款小米開源的sql分析工具：soar-web
你們的支持是我寫優質文章最大的動力，如果喜歡請關注我。謝謝