MySQL SHOW PROFILE 性能分析實戰：從開啟到解讀執行剖析

SHOW PROFILE 核心價值

會話級性能剖析工具，將 SQL 執行拆解為細粒度狀態步驟（如 parsing、optimizing、executing、Sending data），精確到微秒級耗時，支持 CPU、IPC、上下文切換、頁錯誤等多維度資源消耗查看。MySQL 5.7+ 默認啟用，8.0 標記棄用轉向 Performance Schema，但仍廣泛用於快速診斷。

啟用與基本使用流程

-- 1. 當前會話開啟
SET profiling = 1;
-- 或 SET profiling_history_size = 20;  -- 默認 15，最大 100

-- 2. 執行目標 SQL
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                            |
-- +----------+------------+----------------------------------+
-- | 1        | 0.02596900 | SELECT COUNT(DISTINCT ...        |
-- +----------+------------+----------------------------------+

-- 4. 剖析指定 Query_ID
SHOW PROFILE FOR QUERY 1;
-- 默認顯示 Status + Duration

-- 5. 多維度分析
SHOW PROFILE CPU FOR QUERY 1;        -- 用戶/系統 CPU 時間
SHOW PROFILE BLOCK IO FOR QUERY 1;   -- 磁塊讀寫次數
SHOW PROFILE CONTEXT SWITCHES FOR QUERY 1; -- 自願/非自願上下文切換
SHOW PROFILE SOURCE FOR QUERY 1;     -- 源碼函數/文件/行號（需 debug build）

輸出字段詳細解讀

實戰案例：ALTER TABLE 耗時分析

SHOW PROFILE FOR QUERY 2;
-- +------------------------------+-----------+
-- | Status                       | Duration  |
-- +------------------------------+-----------+
-- | starting                     | 0.000183  |
-- | checking permissions         | 0.000057  |
-- | init                         | 0.000060  |
-- | Opening tables               | 0.000071  |
-- | System lock                  | 0.000062  |
-- | setup                        | 0.000080  |
-- | creating table               | 0.005052  |
-- | After create                 | 0.000220  |
-- | copy to tmp table            | 0.000244  |
-- | rename result table          | 70.364027 |  <-- 核心耗時：重命名/刷盤
-- | end                          | 0.000575  |
-- +------------------------------+-----------+

結論：rename result table 階段佔用 99.9% 時間，說明 ALTER TABLE 採用「拷貝表 → 重命名」模式，大表操作極其緩慢。

查詢緩存命中對比實測

SET profiling=1;
SELECT COUNT(1) FROM tran_execution;  -- Query 1: 冷查詢
SELECT COUNT(1) FROM tran_execution;  -- Query 2: 緩存命中

SHOW PROFILE SOURCE FOR QUERY 1;
-- 25 行，包含 optimizing、statistics、executing、Sending data 等完整流程

SHOW PROFILE SOURCE FOR QUERY 2;
-- 僅 8 行：starting → checking query cache → sending cached result → cleaning up
-- 耗時從毫秒級降至微秒級，源碼路徑 sql_cache.cc 證明走查詢緩存路徑

information_schema.PROFILING 程序化訪問

SELECT * FROM information_schema.profiling 
WHERE query_id = 1 ORDER BY seq;
-- 等價於 SHOW PROFILE ALL FOR QUERY 1，便於腳本化分析

限制與注意事項

• 會話隔離：無法分析其他會話的 SQL
• 全量采集：開啟後該會話所有語句（含錯誤 SQL）均被剖析，除 SHOW PROFILE/SHOW PROFILES 自身
• 開銷：采集本身有微小性能影響，生產環境慎用
• 版本棄用：MySQL 8.0 標記 deprecated，建議遷移至 performance_schema.events_statements_history_long + events_stages_history_long
• MEMORY 類型未實現：SHOW PROFILE MEMORY 返回空

Performance Schema 替代方案速覽

-- 開啟語句/階段歷史
UPDATE performance_schema.setup_consumers SET ENABLED='YES' 
WHERE NAME IN ('events_statements_history_long','events_stages_history_long');

-- 查看最近 10 條語句耗時
SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1e12 AS sec 
FROM performance_schema.events_statements_history_long ORDER BY EVENT_ID DESC LIMIT 10;

-- 查看某語句各階段耗時
SELECT EVENT_NAME, TIMER_WAIT/1e12 AS sec 
FROM performance_schema.events_stages_history_long 
WHERE NESTING_EVENT_ID =  ORDER BY EVENT_ID;