MySQL PROFILING 性能分析工具實戰指南

MySQL PROFILING 性能分析工具實戰指南

SHOW PROFILE / SHOW PROFILES 是 MySQL 內建的會話級性能剖析工具，能展示一條 SQL 在各執行階段 (Parsing、Optimizing、Executing、Sending data 等) 的耗時、CPU、IO、上下文切換、頁錯誤等資源消耗，比 EXPLAIN 更貼近實際執行。

啟用與基本流程

-- 1. 查看狀態 (預設關閉)
SHOW VARIABLES LIKE '%profiling%';

-- 2. 開啟
SET profiling = 1;

-- 3. 執行目標 SQL
SELECT goods_name FROM ecs_goods WHERE goods_id < 5000;

-- 4. 列出最近查詢 ID
SHOW PROFILES;

-- 5. 查看指定 Query ID 詳細剖析
SHOW PROFILE FOR QUERY 4;

-- 6. 可選：查看特定資源類型
SHOW PROFILE CPU, BLOCK IO, CONTEXT SWITCHES FOR QUERY 4;

輸出欄位含義

典型分析場景

1. Sending data 耗時長：網絡傳輸大結果集、或 Server 層逐行處理慢 → 減少返回列、分頁、推算下推。
2. Opening tables / statistics 高：表數多、統計信息過舊 → ANALYZE TABLE、增大 table_open_cache。
3. Block_ops_out 激增：大量臨時表寫磁盤 → 增大 tmp_table_size/max_heap_table_size、優化 SQL 避免臨時表。
4. Context_switches 多：高併發鎖爭用、或線程頻繁調度 → 檢查事務長度、隔離級別、索引設計。

注意事項

• 僅對當前會話生效，不影響其他連接。
• MySQL 5.7.6+ 標記為 Deprecated，8.0 移除，建議遷移至 PERFORMANCE_SCHEMA (events_statements_history_long、events_stages_history_long) 或 sys.schema 視圖。
• Profiling 本身有微小開銷，生產環境慎用，測試環境全開。

遷移建議：Performance Schema

-- 開啟消耗統計 (預設部分關閉)
UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE '%stage/%';
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE '%events_stages%';

-- 查詢歷史階段耗時
SELECT EVENT_NAME, TIMER_WAIT/1000000000 AS duration_sec
FROM performance_schema.events_stages_history_long
WHERE THREAD_ID = (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID())
ORDER BY EVENT_ID;

Profiling 是「顯微鏡」，適合單條 SQL 深度剖析；Performance Schema 是「體檢報告」，適合全庫持續觀測。兩者互補。