MySQL Profiling 實戰：從開啟到分析，精準定位 SQL 效能瓶頸

MySQL 內建的 Profiling 是剖析 SQL 執行全生命週期資源消耗的利器。本文通過實測對比「首次查詢」與「緩存命中查詢」的執行細節，演示如何用 Profiling 診斷效能問題。

啟用 Profiling 與基礎操作

SELECT @@profiling;  -- 預設 0 (關閉)
SET profiling = 1;   -- 開啟
SELECT @@profiling;  -- 確認 1 (開啟)

實測場景：建表、首次 COUNT、二次 COUNT

CREATE TABLE t_engines SELECT * FROM t_engines1;  -- 耗時 0.10 秒
SELECT COUNT(*) FROM t_engines;                   -- Query_ID 27
SELECT COUNT(*) FROM t_engines;                   -- Query_ID 28 (命中查詢緩存)

SHOW PROFILES;  -- 列出近期查詢及耗時

結果摘要：

+----------+------------+-------------------------------------------------+
| Query_ID | Duration   | Query                                           |
+----------+------------+-------------------------------------------------+
| 26       | 0.10213775 | create table t_engines select * from t_engines1 |
| 27       | 0.00032775 | select count(*) from t_engines                  |
| 28       | 0.00003850 | select count(*) from t_engines                  |
+----------+------------+-------------------------------------------------+

深度剖析：SHOW PROFILE FOR QUERY

Query 27：首次執行（物理讀）

SHOW PROFILE FOR QUERY 27;
+--------------------------------+------------+
| Status                         | Duration   |
+--------------------------------+------------+
| (initialization)               | 0.00000425 |
| checking query cache for query | 0.00004050 |
| checking permissions           | 0.00001050 |
| Opening tables                 | 0.00018250 |
| System lock                    | 0.00000450 |
| Table lock                     | 0.00001775 |
| init                           | 0.00001075 |
| optimizing                     | 0.00000550 |
| executing                      | 0.00002775 |
| end                            | 0.00000450 |
| query end                      | 0.00000325 |
| storing result in query cache  | 0.00000400 |
| freeing items                  | 0.00000400 |
| closing tables                 | 0.00000500 |
| logging slow query             | 0.00000300 |
+--------------------------------+------------+

關鍵階段：Opening tables、Table lock 等物理 I/O 操作耗時佔大頭。

Query 28：二次執行（緩存命中）

SHOW PROFILE FOR QUERY 28;
+-------------------------------------+------------+
| Status                              | Duration   |
+-------------------------------------+------------+
| (initialization)                    | 0.00000350 |
| checking query cache for query      | 0.00000750 |
| checking privileges on cached query | 0.00000500 |
| checking permissions                | 0.00000525 |
| sending cached result to client     | 0.00001275 |
| logging slow query                  | 0.00000450 |
+-------------------------------------+------------+

僅 6 個步驟，核心是 sending cached result to client，總耗時 0.000039 秒，約為首次查詢的 1/8。

進階用法：INFORMATION_SCHEMA.PROFILING 彙總

SELECT SUM(FORMAT(DURATION,6)) AS DURATION 
FROM INFORMATION_SCHEMA.PROFILING 
WHERE QUERY_ID = 27 ORDER BY SEQ;  -- 0.000326
SELECT SUM(FORMAT(DURATION,6)) AS DURATION 
FROM INFORMATION_SCHEMA.PROFILING 
WHERE QUERY_ID = 28 ORDER BY SEQ;  -- 0.000039

實戰價值總結

• 直觀對比「冷/熱」查詢的執行路徑差異
• 快速識別 Opening tables、filesort、tmp table 等慢速操作
• 驗證索引、緩存、SQL 改寫的優化效果
• 注意：MySQL 5.7+ 已棄用 SHOW PROFILE，建議遷移至 Performance Schema。

Profiling 讓 SQL 效能優化從「猜測」變為「數據驅動」，是 DBA 與開發者的必備技能。