MySQL SHOW PROFILE 性能分析實戰指南

MySQL SHOW PROFILE 性能分析實戰指南

SHOW PROFILE 是 MySQL 會話級的性能剖析工具，能精確展示 SQL 執行各階段的耗時、CPU、I/O、上下文切換等資源消耗，是定位慢查詢瓶頸、驗證優化效果的利器。本文詳解開啟方法、輸出解讀、進階參數、實戰案例與局限性。

一、啟用與基本使用

-- 1. 當前會話開啟剖析
SET profiling = 1;
-- 或
SET SESSION profiling = 1;

-- 2. 執行目標查詢
SELECT COUNT(DISTINCT actor.first_name) AS cnt_name, COUNT(*) AS cnt
FROM sakila.film_actor
INNER JOIN sakila.actor USING (actor_id)
GROUP BY sakila.film_actor.film_id
ORDER BY cnt_name DESC;

-- 3. 查看剖析記錄列表
SHOW PROFILES;
-- 輸出：Query_ID, Duration, Query 文本

-- 4. 查看指定 Query_ID 詳細剖析
SHOW PROFILE FOR QUERY 1;
-- 默認顯示 Status（階段）和 Duration（秒）

二、剖析階段解讀

典型輸出階段及含義：

三、進階參數：資源維度剖析

-- CPU 時間（用戶態/內核態）
SHOW PROFILE CPU FOR QUERY 1;

-- 塊 I/O 操作
SHOW PROFILE BLOCK IO FOR QUERY 1;

-- 上下文切換
SHOW PROFILE CONTEXT SWITCHES FOR QUERY 1;

-- IPC 消息
SHOW PROFILE IPC FOR QUERY 1;

-- 頁錯誤
SHOW PROFILE PAGE FAULTS FOR QUERY 1;

-- 源碼函數定位（需 debug 構建）
SHOW PROFILE SOURCE FOR QUERY 1;

-- 所有維度
SHOW PROFILE ALL FOR QUERY 1;

CPU 剖析輸出示例

+------------------------------+-----------+----------+------------+
| Status                       | Duration  | CPU_user | CPU_system |
+------------------------------+-----------+----------+------------+
| starting                     | 0.000183  | 0.000000 | 0.000000   |
| ...                          | ...       | ...      | ...        |
| rename result table          | 70.364027 | 7.470864 | 41.612674  |  -- 高 CPU 系統態：內核操作（如文件重命名、鎖）
| ...                          | ...       | ...      | ...        |
+------------------------------+-----------+----------+------------+

四、實戰案例：查詢緩存命中效果驗證

SET profiling = 1;

-- 第一次查詢：未命中緩存
SELECT COUNT(1) AS cnt FROM tran_excution;  -- 0.003875s

-- 第二次查詢：命中緩存
SELECT COUNT(1) AS cnt FROM tran_excution;  -- 0.000036s

SHOW PROFILE SOURCE FOR QUERY 1;  -- 未命中
SHOW PROFILE SOURCE FOR QUERY 2;  -- 命中

未命中緩存 (Query 1) 關鍵階段

| executing                  | 0.000015 | exec             | sql_select.cc | 1836 |
| Sending data               | 0.003875 | exec             | sql_select.cc | 2380 |  -- 核心耗時
| storing result in query cache | 0.000017 | end_of_result | sql_cache.cc | 1020 |

命中緩存 (Query 2) 關鍵階段

| checking query cache for query | 0.000016 | send_result_to_client | sql_cache.cc | 1561 |
| sending cached result to clien | 0.000036 | send_result_to_client | sql_cache.cc | 1749 |  -- 直接返回，跳過執行引擎
| logging slow query             | 0.000017 | log_slow_statement    | sql_parse.cc | 1461 |

結論：緩存命中跳過 executing、Sending data 等核心引擎階段，耗時降低 **100 倍**。但緩存適用性需評估：寫入頻繁、大結果集、分區表等場景可能得不償失。

五、配置與局限性

六、最佳實踐流程

1. 識別慢查詢（慢查詢日志 / SHOW PROCESSLIST / APM）
2. 測試環境復現，開啟 SET profiling=1
3. 執行查詢，記錄 Query_ID
4. SHOW PROFILE CPU, BLOCK IO FOR QUERY id 定位高消耗階段
5. 結合 EXPLAIN 分析執行計劃，針對性優化（索引、SQL改寫、配置調整）
6. 再次剖析驗證優化效果，對比 Duration、CPU、Tmp table 等指標

七、總結

• SHOW PROFILE 提供**微秒級、階段級、多維度**的執行剖析，是「看得見」SQL 執行內幕的顯微鏡
• 核心價值：量化 Creating tmp table、Copying to group table、Sorting result 等優化痛點
• 局限：會話級、有開銷、無計劃樹視圖。生產環境首選 PERFORMANCE_SCHEMA + sys.schema + 慢查詢日志分析
• 掌握 PROFILE，讓優化從「憑感覺」變為「憑數據」