您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 性能分析工具 Profile 使用完整教程

編輯：MySQL 綜合教學

MySQL 性能分析工具 Profile 使用完整教程

SQL 執行開銷分析是查詢優化的核心手段。MySQL 提供的 profiling 功能可在 Session 或 Global 層級啟用，記錄 SQL 執行過程中的資源消耗（IO、CPU、內存、上下文切換、鎖等待等），幫助定位效能瓶頸。本文詳細介紹 Profile 的啟用、使用、結果解讀與遷移建議。

⚠ 重要提示： Profiling 功能自 MySQL 5.6.7 起標記為 Deprecated（棄用），未來版本將移除。官方建議遷移至 Performance Schema。本文僅供維護舊版本或學習原理參考。

一、Profile 相關系統變量

mysql> SHOW VARIABLES LIKE '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |  -- 唯讀，指示伺服器是否支援 profiling
| profiling              | OFF   |  -- 開關：ON/OFF 啟用/禁用 SQL 剖析
| profiling_history_size | 15    |  -- 保留剖析記錄數量（0-100，0 為禁用）
+------------------------+-------+

二、啟用 Profiling

-- Session 級別啟用（推薦，僅影響當前連接）
SET profiling = 1;
-- 或
SET profiling = ON;

-- 驗證
SHOW VARIABLES LIKE '%profil%';
-- profiling = ON

-- Global 級別啟用（影響全實例，需 SUPER 權限）
SET GLOBAL profiling = 1;

三、執行 SQL 並查看剖析結果

-- 執行待分析的查詢
SELECT COUNT(*) FROM sakila.customer;

-- 查看當前 Session 所有已記錄的 Profile
SHOW PROFILES;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00253600 | show variables like '%profil%' |
|        2 | 0.00138150 | select count(*) from customer  |
+----------+------------+--------------------------------+

-- 查看特定 Query_ID 的詳細開銷
SHOW PROFILE FOR QUERY 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000148 |
| checking permissions | 0.000014 |
| Opening tables       | 0.000047 |
| init                 | 0.000023 |
| System lock          | 0.000035 |
| optimizing           | 0.000012 |
| statistics           | 0.000019 |
| preparing            | 0.000014 |
| executing            | 0.000006 |
| Sending data         | 0.000990 |  <-- 關鍵：資料傳輸耗時最長
| end                  | 0.000010 |
| query end            | 0.000011 |
| closing tables       | 0.000010 |
| freeing items        | 0.000016 |
| cleaning up          | 0.000029 |
+----------------------+----------+

四、多維度資源開銷分析

SHOW PROFILE 支援指定資源類型：

-- CPU 開銷（用戶態/系統態）
SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| Sending data         | 0.000990 | 0.001000 | 0.000000   |  <-- CPU 主要消耗在發送數據
...

-- 內存開銷
SHOW PROFILE MEMORY FOR QUERY 2;

-- 區塊 IO 開銷
SHOW PROFILE BLOCK IO FOR QUERY 2;

-- 組合查看
SHOW PROFILE BLOCK IO, CPU, MEMORY FOR QUERY 2;

-- 所有類型
SHOW PROFILE ALL FOR QUERY 2;

五、進階分析：按開銷佔比排序

SET @query_id = 2;

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

+----------------------+----------+-------+-------+--------------+
| STATE                | Total_R  | Pct_R | Calls | R/Call       |
+----------------------+----------+-------+-------+--------------+
| Sending data         | 0.000990 | 71.53 |     1 | 0.0009900000 |  <-- 71.5% 佔比
| starting             | 0.000148 | 10.69 |     1 | 0.0001480000 |
| Opening tables       | 0.000047 |  3.40 |     1 | 0.0000470000 |
| System lock          | 0.000035 |  2.53 |     1 | 0.0000350000 |
| cleaning up          | 0.000029 |  2.10 |     1 | 0.0000290000 |
...

六、底層儲存：INFORMATION_SCHEMA.PROFILING 表

-- 查看原始剖析資料（含源碼級別資訊）
SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 2 \G

*************************** 1. row ***************************
QUERY_ID: 2
SEQ: 1
STATE: starting
DURATION: 0.000148
CPU_USER: 0.000000
CPU_SYSTEM: 0.000000
CONTEXT_VOLUNTARY: 0
CONTEXT_INVOLUNTARY: 0
BLOCK_OPS_IN: 0
BLOCK_OPS_OUT: 0
MESSAGES_SENT: 0
MESSAGES_RECEIVED: 0
PAGE_FAULTS_MAJOR: 0
PAGE_FAULTS_MINOR: 0
SWAPS: 0
SOURCE_FUNCTION: mysql_execute_command
SOURCE_FILE: sql_parse.cc
SOURCE_LINE: 4567
...

七、關閉與清理

-- 關閉當前 Session Profiling
SET profiling = 0;
-- 或
SET profiling = OFF;

-- Session 斷開時自動關閉並清理歷史記錄
-- profiling_history_size = 0 可全局禁用

八、遷移指引：Performance Schema 替代方案

MySQL 5.6+ 推薦使用 Performance Schema 進行效能分析：

-- 確認啟用
SHOW VARIABLES LIKE 'performance_schema';
-- 需為 ON（5.6.6+ 預設啟用）

-- 核心消費者表
SELECT * FROM performance_schema.setup_consumers;
-- 關鍵：events_statements_history、events_statements_history_long

-- 啟用語句級別收集
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_statements%';

-- 查詢最近執行語句統計
SELECT * FROM performance_schema.events_statements_history_long 
ORDER BY TIMER_END DESC LIMIT 10;

-- 關聯等待事件分析
SELECT * FROM performance_schema.events_waits_history_long 
WHERE THREAD_ID = ;

Performance Schema 提供更精細的等待事件、鎖、階段、內存、事務等多維度分析，且為持久化設計，適合生產環境持續監控。