MySQL Profiling 性能分析工具實戰指南

啟用 Profiling

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0           |
+-------------+

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

Session 級別生效，斷開連接自動關閉。全域持久化需配置文件 profiling=1（不建議長期開啟）。

實戰對比：緩存命中前後差異

-- 建測試表
CREATE TABLE t_engines SELECT * FROM t_engines1;  -- 57344 行

-- 第一次 COUNT（冷查詢）
SELECT COUNT(*) FROM t_engines;
-- 第二次 COUNT（熱查詢，命中 Query Cache）
SELECT COUNT(*) FROM t_engines;

-- 查看概覽
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                  |
+----------+------------+-------------------------------------------------+

-- 詳細剖析 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 |
+--------------------------------+------------+

-- 詳細剖析 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 |
+-------------------------------------+------------+

關鍵洞察

• 首次查詢經歷完整流程：Opening tables、Table lock、optimizing、executing、storing result in query cache。
• 二次查詢直接 sending cached result to client，跳過解析、優化、執行、鎖表、**耗時降低 ~10 倍**（0.33ms → 0.04ms）。
• checking query cache for query 耗時極短，開銷可忽略。

進階用法：聚合統計

SELECT SUM(FORMAT(DURATION,6)) AS total_duration
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 27;
-- 0.000326 秒

適用場景與限制

• 適用：定位慢 SQL 具體階段耗時（解析、優化、鎖、IO、緩存）、對比優化前後、教學演示。
• 限制：MySQL 5.7.7 棄用，8.0 移除。替代方案：Performance Schema (events_statements_history_long)、EXPLAIN ANALYZE (8.0.18+)、sys.schema 視圖、慢查詢日誌 + pt-query-digest。
• 僅對當前 Session 可見，不適合生產全域監控。

遷移建議

-- Performance Schema 等價查詢 (需啟用 instruments)
SELECT * FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT LIKE 'select count(*) from t_engines%'
ORDER BY TIMER_WAIT DESC;