MySQL SHOW PROFILE 性能分析實戰：定位 CPU/IO 瓶頸

MySQL Profiling 使用指南

SHOW PROFILE 是會話級性能分析工具，能精確拆解單條 Query 的資源消耗：CPU、IO、上下文切換、頁錯誤、鎖等待等，定位瓶頸利器。

啟用與基本流程

-- 1. 開啟 profiling（會話級，重連失效）
SET profiling = 1;

-- 2. 執行目標查詢
SELECT * FROM large_table WHERE created_at > '2024-01-01';

-- 3. 查看所有已記錄查詢摘要
SHOW PROFILES;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
| 1        | 0.00123456 | SELECT * FROM large_table ...     |
+----------+------------+-----------------------------------+

-- 4. 針對單個 Query 詳細分析
SHOW PROFILE ALL FOR QUERY 1;
-- 或指定類型
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

Profile 類型詳解

實戰輸出解讀範例

SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000050 | 0.000000 | 0.000000   | 0            | 0             |
| checking permissions | 0.000010 | 0.000000 | 0.000000   | 0            | 0             |
| Opening tables       | 0.000020 | 0.000000 | 0.000000   | 0            | 0             |
| System lock          | 0.000015 | 0.000000 | 0.000000   | 0            | 0             |
| Table lock           | 0.000030 | 0.000000 | 0.000000   | 0            | 0             |
| init                 | 0.000025 | 0.000000 | 0.000000   | 0            | 0             |
| optimizing           | 0.000015 | 0.000000 | 0.000000   | 0            | 0             |
| statistics           | 0.000020 | 0.000000 | 0.000000   | 0            | 0             |
| preparing            | 0.000015 | 0.000000 | 0.000000   | 0            | 0             |
| executing            | 0.000010 | 0.000000 | 0.000000   | 0            | 0             |
| Sending data         | 0.001000 | 0.000800 | 0.000100   | 120          | 0             |
| end                  | 0.000005 | 0.000000 | 0.000000   | 0            | 0             |
| query end            | 0.000005 | 0.000000 | 0.000000   | 0            | 0             |
| closing tables       | 0.000010 | 0.000000 | 0.000000   | 0            | 0             |
| freeing items        | 0.000010 | 0.000000 | 0.000000   | 0            | 0             |
| cleaning up          | 0.000005 | 0.000000 | 0.000000   | 0            | 0             |
+----------------------+----------+----------+------------+--------------+---------------+

關鍵洞察

• Sending data 階段：耗時 1ms，CPU 0.9ms，Block_ops_in=120 → 磁盤讀取為主要瓶頸
• 若 CPU_user 佔比極高 → 複雜計算/函數/排序，考慮索引優化或下推計算
• 若 Context_switches 高 → 鎖爭用/併發過大，檢查隔離級別/索引減少掃描行
• 若 Page_faults 高 → 內存不足，增加 innodb_buffer_pool_size

進階用法

-- 限制輸出行數
SHOW PROFILE ALL FOR QUERY 1 LIMIT 5 OFFSET 2;

-- 從 INFORMATION_SCHEMA 查詢（可跨會話持久化分析）
SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 1;

局限性與替代方案

最佳實踐建議

1. 結合 EXPLAIN 使用：EXPLAIN 看執行計劃，PROFILE 看實際資源消耗
2. 對比優化前後：同一查詢優化前後 PROFILE 對比，量化效果
3. 關注 Sending data、Waiting for table lock、Sorting result 等高耗時階段
4. 生產環境改用 Performance Schema + sys.schema 做持續觀測