您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL Profile 性能分析實戰：從開啟到瓶頸定位全流程

編輯：MySQL 資料庫相關知識

前言：為什麼需要 Profile？

SHOW PROFILE 是 MySQL 5.0.37+ 內建的 SQL 剖析工具，能將一條查詢拆解為 20+ 個內部執行步驟，並量化每步的耗時、CPU、I/O、上下文切換等資源消耗。它是定位單條 SQL 瓶頸的「顯微鏡」。

一、開啟與驗證

-- 查看狀態
SHOW VARIABLES LIKE '%profil%';
-- 會話級開啟
SET profiling = 1;
-- 驗證
SELECT @@profiling; -- 1 表示生效

profiling_history_size（預設 15）控制保留多少條記錄，設為 0 即禁用。

二、實戰演示：一條查詢的完整剖析

1. 執行目標 SQL

SELECT * FROM hx_line WHERE id = '1455023';

2. 查看最近執行的查詢列表

SHOW PROFILES;

輸出包含 Query_ID、Duration、Query 文本。

3. 詳細剖析指定 Query_ID

SHOW PROFILE FOR QUERY 1;

輸出解讀（關鍵階段）：

Status 階段	含義	關注點
starting	啟動查詢	通常極快
checking query cache	查詢緩存檢查	若關閉緩存可減少此步
Opening tables / System lock	打開表、系統鎖	元數據鎖競爭會在此顯現
optimizing / statistics / preparing	優化器階段	統計資訊不準會導致錯誤計劃
executing	執行階段	核心計算邏輯
Sending data	發送數據給客戶端	通常最耗時，含磁盤讀取、網絡傳輸
end / query end / cleaning up	收尾階段	釋放資源、記錄慢查詢日誌

4. 關閉 Query Cache 後的對比

關閉緩存後步驟從 24 步減至 16 步，耗時減少 0.000087s。結論：簡單點查詢場景下，Query Cache 反而增加開銷。

三、進階剖析：CPU 與 Block I/O

-- CPU 使用情況
SHOW PROFILE CPU FOR QUERY 1;
-- 綜合 Block I/O + CPU
SHOW PROFILE BLOCK IO, CPU FOR QUERY 1;

輸出新增 CPU_user、CPU_system、Block_ops_in、Block_ops_out 欄位，可識別是 CPU 密集還是 I/O 密集。

四、聚合分析：找出佔比最高的階段

SELECT STATE, SUM(DURATION) AS Total_R,
  ROUND(100 * SUM(DURATION) / (SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 1), 2) AS Pct_R,
  COUNT(*) AS Calls, SUM(DURATION) / COUNT(*) AS "R/Call"
FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 1
GROUP BY STATE ORDER BY Total_R DESC;

實戰案例中，Copying to tmp table 佔 81.26%、Sending data 佔 17.76% —— **臨時表操作是核心瓶頸**，提示需優化 GROUP BY/ORDER BY 或增加內存臨時表大小。