MySQL Performance Schema 實戰篇：從統計維度到場景化問題定位的完整分析流程

MySQL Performance Schema 實戰篇

理論知識落地為分析能力，需要掌握 「統計維度查詢 → 場景化關聯分析 → 根因定位」 的完整流程。本文實戰演示如何利用 P_S 回答 DBA 最關心的問題：哪類 SQL 最頻/最慢、哪張表最熱/最冷、哪個鎖最熱、具體 SQL 各階段耗時、鎖等待詳情、MDL 阻塞分析。

一、統計維度分析：三大核心視角

1. SQL 維度：events_statements_summary_by_digest

哪類 SQL 執行最多？

SELECT DIGEST_TEXT, COUNT_STAR, FIRST_SEEN, LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC LIMIT 1\G
-- 典型輸出：
-- DIGEST_TEXT: SELECT `pay_order_id`, `total_fee`... FROM `pay_order` WHERE `pay_order_id` = ?
-- COUNT_STAR: 5282893
-- FIRST_SEEN: 2024-01-15 10:00:00
-- LAST_SEEN: 2024-01-18 16:20:59

其他關鍵指標一鍵查詢：

2. 物件維度：檔案/表/索引熱度

哪張表物理 IO 最大？

SELECT file_name, event_name, SUM_NUMBER_OF_BYTES_READ, SUM_NUMBER_OF_BYTES_WRITE
FROM performance_schema.file_summary_by_instance
ORDER BY (SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE) DESC LIMIT 1\G
-- 輸出範例：
-- file_name: /data/mysql/chuck/test_01.ibd
-- SUM_NUMBER_OF_BYTES_READ: 2.1GB
-- SUM_NUMBER_OF_BYTES_WRITE: 1.3TB

哪張表邏輯 IO 最大（最熱）？

SELECT object_name, COUNT_READ, COUNT_WRITE, COUNT_FETCH, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC LIMIT 1\G
-- 輸出：test_slow 表，讀寫總計數百萬，累計等待時間最高

哪個索引訪問最多？

SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
ORDER BY SUM_TIMER_WAIT DESC LIMIT 1\G
-- 輸出：test_slow 表 PRIMARY 索引，FETCH/UPDATE 佔多數

哪些索引從未被使用（候選刪除）？

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql'
ORDER BY OBJECT_SCHEMA, OBJECT_NAME;
-- 輸出：chuck.test_icp.idx_y_z 從未使用，可評估刪除節省空間

3. 等待事件維度：系統級熱點

哪類等待事件累計耗時最長？

SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name != 'idle'
ORDER BY SUM_TIMER_WAIT DESC LIMIT 1;
-- 輸出：wait/synch/cond/threadpool/worker_cond，累計等待 1.6 秒（皮秒單位），平均 60ms

二、場景化定位分析：三級關聯鏈路

P_S 核心威力在於 Statement → Stage → Wait 三級巢狀關聯，通過 NESTING_EVENT_ID 串聯。

場景 1：剖析具體 SQL 完整執行剖面

目標：分析包含 COUNT(*) 的慢查詢各階段耗時。

步驟 1：從 history 找到目標 SQL 的 EVENT_ID

SELECT EVENT_ID, SQL_TEXT
FROM performance_schema.events_statements_history
WHERE SQL_TEXT LIKE '%count(*)%';
-- EVENT_ID = 1690

步驟 2：關聯 Stage 表獲取各階段耗時

SELECT event_id, EVENT_NAME, SOURCE, TIMER_END - TIMER_START AS duration_ps
FROM performance_schema.events_stages_history_long
WHERE NESTING_EVENT_ID = 1690
ORDER BY event_id;
-- 輸出關鍵階段：
-- stage/sql/Sending data        7,369,072,089,000 ps  (7.3 秒)  ← 核心耗時
-- stage/sql/Opening tables      41,436,934,000 ps
-- stage/sql/statistics          52,845,000 ps
-- ...

啟示：Sending data 階段佔比 99%+，通常意味著大量資料讀取/傳輸，需檢查索引與 LIMIT。

步驟 3：深入 Wait 事件定位 IO/鎖細節

SELECT event_id, EVENT_NAME, SOURCE, TIMER_WAIT, OBJECT_NAME, INDEX_NAME, OPERATION
FROM performance_schema.events_waits_history_long
WHERE NESTING_EVENT_ID = 2647;  -- Sending data 階段的 event_id
-- 輸出：大量 wait/io/table/sql/handler，INDEX_NAME=idx_c1，OPERATION=fetch
-- 說明：通過 idx_c1 索引大量 fetch，但可能未覆蓋查詢導致回表

場景 2：InnoDB 行鎖等待分析

模擬：Session A UPDATE test_icp SET y=y+1 WHERE x=1 不提交；Session B 同語句阻塞 60s 超時。

查詢 Statement + Stage 關聯：

SELECT s.EVENT_ID, st.event_id, s.SQL_TEXT, st.EVENT_NAME, st.TIMER_WAIT
FROM performance_schema.events_statements_history_long s
JOIN performance_schema.events_stages_history_long st ON s.EVENT_ID = st.NESTING_EVENT_ID
WHERE s.SQL_TEXT = 'update test_icp set y=y+1 where x=1';
-- 關鍵發現：stage/sql/updating 耗時 6,001,362,045,000 ps (60 秒) = innodb_lock_wait_timeout

查詢 Wait 事件：

SELECT event_id, EVENT_NAME, TIMER_WAIT, OBJECT_NAME, INDEX_NAME, OPERATION
FROM performance_schema.events_waits_history_long
WHERE NESTING_EVENT_ID = 5832;  -- updating 階段 event_id
-- 僅顯示 wait/io/table/sql/handler fetch，無細粒度行鎖等待事件
-- 結論：P_S 目前未暴露 InnoDB 內部行鎖等待細節，需結合 innodb_status / innodb_metrics

場景 3：MDL 元資料鎖阻塞分析

模擬：Session A 大查詢 SELECT COUNT(*) FROM test_slow；Session B ALTER TABLE test_slow MODIFY c2 VARCHAR(152) 阻塞。

查詢 ALTER 語句 Stage 明細：

SELECT s.EVENT_ID, st.event_id, s.SQL_TEXT, st.EVENT_NAME, st.TIMER_WAIT
FROM performance_schema.events_statements_history_long s
LEFT JOIN performance_schema.events_stages_history_long st ON s.EVENT_ID = st.NESTING_EVENT_ID
WHERE s.SQL_TEXT = 'alter table test_slow modify c2 varchar(152)';
-- 關鍵發現：多次出現 stage/sql/Waiting for table metadata lock，每次約 1 秒，間隔 1 秒重試

定位具體 Wait 事件：

SELECT event_id, EVENT_NAME, SOURCE, TIMER_WAIT
FROM performance_schema.events_waits_history_long
WHERE NESTING_EVENT_ID = 326526850;  -- 某次 Waiting for table metadata lock 的 event_id
-- 輸出：
-- wait/synch/cond/sql/MDL_context::COND_wait_status   562,417,991,328 ps  (0.56 秒)  mdl.cc:1327
-- wait/synch/mutex/mysys/my_thread_var::mutex           733,248 ps                    sql_class.h:3481

結論：MDL 阻塞本質是條件變數 MDL_context::COND_wait_status 等待，源碼定位至 mdl.cc:1327，可指導內核級分析。

三、實戰分析模板（可直接套用）

-- 1. Top 10 高頻 SQL
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e12 AS avg_sec, SUM_ROWS_EXAMINED/COUNT_STAR AS avg_rows_examined
FROM events_statements_summary_by_digest
WHERE DIGEST_TEXT NOT LIKE 'SET%' AND DIGEST_TEXT NOT LIKE 'COMMIT%'
ORDER BY COUNT_STAR DESC LIMIT 10;

-- 2. Top 10 耗時 SQL（平均）
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e12 AS avg_sec, SUM_ROWS_EXAMINED
FROM events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

-- 3. 物理 IO 熱點表 Top 10
SELECT file_name, (SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE)/1024/1024 AS total_mb
FROM file_summary_by_instance
ORDER BY total_mb DESC LIMIT 10;

-- 4. 邏輯 IO 熱點表 Top 10
SELECT object_name, COUNT_READ+COUNT_WRITE+COUNT_FETCH AS total_ops, SUM_TIMER_WAIT/1e12 AS total_sec
FROM table_io_waits_summary_by_table
ORDER BY total_ops DESC LIMIT 10;

-- 5. 未使用索引候選清單
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM table_io_waits_summary_by_index_usage
WHERE COUNT_STAR = 0 AND INDEX_NAME IS NOT NULL AND OBJECT_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema');

-- 6. 等待事件熱點 Top 10
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_sec, AVG_TIMER_WAIT/1e6 AS avg_ms
FROM events_waits_summary_global_by_event_name
WHERE EVENT_NAME NOT LIKE 'idle%'
ORDER BY total_sec DESC LIMIT 10;

四、避坑指南與最佳實踐

1. History 表易滿：預設 history=10, history_long=10000，高併發下秒級覆蓋。關鍵分析必須趁熱查詢 history_long，或增大參數（performance_schema_events_waits_history_long_size=100000）。
2. Digest 依賴 Consumer：必須啟用 performance_schema_consumer_statements_digest=ON，否則 DIGEST/DIGEST_TEXT 為 NULL。
3. 皮秒單位換算：1 秒 = 1e12 皮秒，1 毫秒 = 1e9 皮秒，查詢時建議直接除以 1e12 得到秒。
4. 生產環境開銷：全開約 3-5% CPU 開銷，建議核心儀表（wait/stage/statement/digest）全開，其餘按需。
5. 結合 sys schema：MySQL 5.7+ 內建 sys schema 封裝了大量 P_S 常用視圖（如 sys.schema_table_statistics, sys.user_summary_by_statement_latency），優先使用 sys 視圖提效。

Performance Schema 將 MySQL 從「黑盒」變為「白盒」。掌握 統計維度宏觀巡檢 + 三級關聯微觀剖析 的雙重分析範式，配合 sys schema 與自動化巡檢腳本，能讓 DBA 從「事後查日誌」進化為「事中看儀表、事前預風險」，真正實現數據庫效能的主動治理。