MySQL Performance Schema 與 PROFILING 深度剖析：SQL 效能分析實戰

背景與定位

SHOW PROFILE / SHOW PROFILES 是 MySQL 5.0-5.6 的舊式剖析工具，5.6.7 起標記棄用，8.0 移除。現代標準為 Performance Schema。本文同時介紹兩者，助你平滑遷移。

PROFILING 快速上手（舊版/相容模式）

啟用與配置

-- Session 級啟用
SET profiling = 1;
-- 或全域（影響所有連線）
SET GLOBAL profiling = 1;

-- 保留記錄數（預設 15，最大 100，0 禁用）
SET profiling_history_size = 50;

執行查詢並查看剖析

SELECT COUNT(*) FROM customer;

-- 列出所有剖析記錄
SHOW PROFILES;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
| 1        | 0.00253600 | show variables like '%profil%'|
| 2        | 0.00138150 | select count(*) from customer|
+----------+------------+------------------------------+

-- 查看指定 Query 詳細階段耗時
SHOW PROFILE FOR QUERY 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000148 |
| checking permissions | 0.000014 |
| Opening tables       | 0.000047 |
| ...                  | ...      |
| Sending data         | 0.000990 |  -- 關鍵：資料傳輸階段
| ...                  | ...      |
+----------------------+----------+

-- 指定資源類型：CPU、BLOCK IO、MEMORY、CONTEXT SWITCHES 等
SHOW PROFILE CPU, BLOCK IO FOR QUERY 2;

-- 彙總統計：各階段總耗時、佔比、呼叫次數
SELECT STATE, SUM(DURATION) AS Total_R,
       ROUND(100*SUM(DURATION)/(SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=2),2) AS Pct_R,
       COUNT(*) AS Calls, SUM(DURATION)/COUNT(*) AS "R/Call"
FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=2
GROUP BY STATE ORDER BY Total_R DESC;

底層儲存

資料寫入 INFORMATION_SCHEMA.PROFILING 表，含 QUERY_ID, SEQ, STATE, DURATION, CPU_USER, CPU_SYSTEM, CONTEXT_VOLUNTARY, CONTEXT_INVOLUNTARY, BLOCK_OPS_IN, BLOCK_OPS_OUT, MESSAGES_SENT, MESSAGES_RECEIVED, PAGE_FAULTS_MAJOR, PAGE_FAULTS_MINOR, SWAPS, SOURCE_FUNCTION, SOURCE_FILE, SOURCE_LINE。

Performance Schema：現代標準（5.6+ 推薦，5.7+ 成熟）

核心啟用

-- my.cnf
[mysqld]
performance_schema=ON
performance_schema_instrument='%=ON'  -- 全開，生產可按需精簡

關鍵表與實戰查詢

1. SQL 指紋聚合統計（找出最慢/最頻繁 SQL）

SELECT DIGEST_TEXT AS query,
       COUNT_STAR AS exec_count,
       ROUND(SUM_TIMER_WAIT/1e12,4) AS total_latency_s,
       ROUND(AVG_TIMER_WAIT/1e12,4) AS avg_latency_s,
       ROUND(MAX_TIMER_WAIT/1e12,4) AS max_latency_s,
       ROUND(SUM_ROWS_EXAMINED/COUNT_STAR) AS avg_rows_examined,
       ROUND(SUM_ROWS_SENT/COUNT_STAR) AS avg_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'mydb'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

2. 當前執行中的語句

SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST,
       SQL_TEXT, TIMER_START, TIMER_WAIT/1e12 AS latency_s
FROM performance_schema.events_statements_current
WHERE SQL_TEXT IS NOT NULL;

3. 階段級剖析（對應舊 PROFILE 的 Status）

SELECT EVENT_NAME, TIMER_WAIT/1e12 AS latency_s
FROM performance_schema.events_stages_history_long
WHERE THREAD_ID = 
ORDER BY EVENT_ID;

4. 等待事件分析（鎖、I/O、同步）

-- 表級 I/O 等待
SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_s
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

-- 鎖等待
SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_s
FROM performance_schema.table_lock_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

5. 記憶體分配分布

SELECT EVENT_NAME, CURRENT_COUNT_USED, HIGH_COUNT_USED,
       CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS current_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE CURRENT_NUMBER_OF_BYTES_USED > 0
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;

遷移對照表

最佳實踐建議

• 生產環境預設開啟 Performance Schema，開銷極低（<1%）
• 使用 sys schema 封裝視圖（如 sys.statements_with_runtimes_in_95th_percentile）快速分析
• 定期清理 events_statements_history_long 避免記憶體膨脹
• 結合 EXPLAIN ANALYZE (8.0.18+) 獲得真實執行計劃與剖析

小結

PROFILING 是歷史工具，適合快速臨時排查；Performance Schema 才是生產級觀測基石。掌握 events_statements_summary_by_digest、events_stages_history_long、table_io_waits_summary_by_table 三大核心表，覆蓋 90% SQL 效能分析需求。