MySQL 5.0.37+ 內建效能剖析器 SHOW PROFILE 實戰：從開啟到解讀 CPU、I/O、上下文切換全維度指標

MySQL 5.0.37（Community 版）引入 SHOW PROFILE / SHOW PROFILES，提供查詢級別的執行階段耗時、CPU、塊 I/O、記憶體、Swap、上下文切換、源碼行號等微觀指標，是 SQL 調優與內核行為分析的利器。

啟用前提與注意

• 版本 ≥ 5.0.37（SHOW VARIABLES LIKE 'have_profiling'; 驗證）。
• profiling 為 Session 級變數，每新連線需重新 SET profiling = 1;。
• 預設保留最近 100 條查詢，可調 profiling_history_size。

實戰流程示範

SET profiling = 1;
SELECT COUNT(*) FROM t;          -- Query_ID = 1
INSERT INTO t SELECT * FROM t;   -- Query_ID = 2
SHOW PROFILES;                   -- 列出所有已剖析查詢
SHOW PROFILE FOR QUERY 2;        -- 基礎階段耗時
SHOW PROFILE CPU, BLOCK IO FOR QUERY 2;  -- 含 CPU、磁塊 I/O
SHOW PROFILE CPU, BLOCK IO, MEMORY, SWAPS, CONTEXT SWITCHES, SOURCE FOR QUERY 2; -- 全維度

關鍵輸出欄位解讀

實測案例解析（節錄原文數據）

案例 1：全表掃描 SELECT COUNT(*) FROM t（209 萬行）

• Sending data 耗時 0.747 秒，CPU_user 0.746 秒 → 純 CPU 計算密集（無索引掃描）。
• 無磁塊 I/O（資料全在緩衝池）。

案例 2：大量插入 INSERT ... SELECT（209 萬行 → 34 秒）

• Copying to tmp table 1.26 秒、converting HEAP to MyISAM 0.38 秒、Copying to tmp table on disk 1.42 秒 → 臨時表溢寫磁碟 是關鍵瓶頸。
• Sending data 31 秒，CPU_user 13.9 秒 → 大量網路傳輸與行寫入。

案例 3：全表更新 UPDATE t SET username='waill'（419 萬行 → 44.8 秒）

• Updating 階段 44.5 秒，CPU_user 27.9 秒、CPU_system 2.8 秒、Context_involuntary 1584 次 → 寫入鎖競爭、CPU 繁忙、上下文切換頻繁。

進階用法建議

1. 對比不同索引/寫法：同一查詢改寫前後跑 Profile，量化 Duration、Block_ops 變化。
2. 定位臨時表溢寫：出現 Copying to tmp table on disk → 檢查 tmp_table_size、max_heap_table_size、是否含 BLOB/TEXT、GROUP BY/ORDER BY 列超長。
3. 鎖爭用分析：高 Context_involuntary + 高 System lock/Table lock → 檢查隔離級別、行鎖持有時間、長事務。
4. 結合 PERFORMANCE_SCHEMA (5.5+) / sys schema (5.7+)：Profile 適合單查詢深度剖析，P_S 適合全實例持續觀測。

局限性提醒

• 僅 Community 版支援，Enterprise 版早期無此功能。
• Session 級，無法跨連線彙總；生產環境謹慎開啟（有微小效能開銷）。
• MySQL 5.7 起標記 Deprecated，8.0 移除，建議遷移至 PERFORMANCE_SCHEMA + sys.schema 體系。