MySQL SQL 執行時間分析：SHOW PROFILE 與 TIMESTAMPDIFF 兩大實戰方法

前言

網站變慢時，SQL 效能往往是核心瓶頸。準確測量單條 SQL 的執行耗時、各階段耗時分佈，是優化的前提。MySQL 提供 SHOW PROFILE（Session 級、階段級）與手動 TIMESTAMPDIFF（簡易測量）兩種方式。本文詳細演示兩種方法的開啟、使用、輸出解讀與避坑指南。

方法一：SHOW PROFILE —— 官方分析工具

1. 開啟 Profiling

-- 查看狀態
SHOW VARIABLES LIKE 'profiling%';
-- profiling=OFF, profiling_history_size=15

-- 開啟（Session 級，斷開連線失效）
SET profiling = 1;

2. 執行測試 SQL

SHOW TABLES;
SELECT * FROM aa;
UPDATE aa SET name='d';
DELETE FROM bb;

3. 查看 Profile 列表

SHOW PROFILES;
+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
| 1        | 0.00054775 | SHOW TABLES            |
| 2        | 0.00022400 | SELECT * FROM aa       |
| 3        | 0.00026275 | UPDATE aa SET name='d' |
| 4        | 0.00043000 | DELETE FROM bb         |
+----------+------------+------------------------+

4. 查看特定 Query 詳細階段耗時

SHOW PROFILE FOR QUERY 3;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             | 0.0000475 |
| checking permissions | 0.0000077 |
| Opening tables       | 0.000026  |
| System lock          | 0.0000042 |
| Table lock           | 0.0000045 |
| init                 | 0.0000205 |
| Updating             | 0.0000787 |
| end                  | 0.0000567 |
| query end            | 0.000004  |
| freeing items        | 0.0000067 |
| closing tables       | 0.000004  |
| logging slow query   | 0.000002  |
+----------------------+-----------+

5. 進階：指定分析維度

-- CPU、Block IO、Context Switches、Page Faults 等
SHOW PROFILE CPU, BLOCK IO FOR QUERY 3;

6. 注意事項

• MySQL 5.7+ 已棄用，官方建議使用 Performance Schema（events_statements_history_long）。
• 僅限當前 Session，無法分析其他連線的 SQL。
• 歷史記錄有限（預設 15 條），可調 profiling_history_size。
• 開啟後有微小效能開銷，生產環境慎用，建議測試環境使用。

方法二：TIMESTAMPDIFF 手動計時 —— 簡易、無依賴

原理

利用用戶變數記錄開始時間，緊接執行目標 SQL，再計算差值。

單條 SQL 測量（必須在同一連線、同一批次執行）

SET @start = NOW(6);  -- 微秒精度
SELECT * FROM comment WHERE id < 10000;
SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW(6)) AS elapsed_us;

批次測量腳本範例

-- 測試不同 LIMIT 的查詢耗時
SET @sql = 'SELECT * FROM t_order WHERE user_id=1001 ORDER BY create_time DESC LIMIT ?';
PREPARE stmt FROM @sql;

SET @lim = 10;  SET @s = NOW(6); EXECUTE stmt USING @lim; SELECT TIMESTAMPDIFF(MICROSECOND, @s, NOW(6));
SET @lim = 100; SET @s = NOW(6); EXECUTE stmt USING @lim; SELECT TIMESTAMPDIFF(MICROSECOND, @s, NOW(6));
SET @lim = 1000; SET @s = NOW(6); EXECUTE stmt USING @lim; SELECT TIMESTAMPDIFF(MICROSECOND, @s, NOW(6));
DEALLOCATE PREPARE stmt;

避坑關鍵

1. 三條語句必須連續、同批次執行（命令行下最後一條後要多按一個 Enter 送出空行），否則網路延遲、鍵盤輸入時間會嚴重汙染結果。
2. 使用 NOW(6) 獲得微秒精度（MySQL 5.6.4+）。
3. 僅適合粗略對比，**不可作為精準基準**，因包含網路 RTT、解析、權限檢查等非執行核心耗時。

進階方案：Performance Schema / Slow Query Log

總結

SHOW PROFILE 適合開發/測試環境快速定位單條 SQL 熱點階段；TIMESTAMPDIFF 適合無權限、臨時對比場景。生產環境**請依賴 Performance Schema + Slow Query Log + pt-query-digest** 建立常態化 SQL 效能監控體系。工具只是手段，**讀懂 EXPLAIN、建立正確索引、改寫惡臭 SQL** 才是優化本質。