MySQL 性能分析利器：SHOW PROFILE 與 SHOW PROFILES 實戰詳解

前置條件

• MySQL ≥ 5.0.37（Community 版專有功能）
• 會話變數 profiling 需每次連線重新啟用

啟用與基本用法

mysql> SET profiling = 1;
mysql> SHOW TABLES;
mysql> SELECT COUNT(*) FROM t;
mysql> SHOW PROFILES;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
| 1        | 0.02717000 | show tables                  |
| 2        | 0.74770100 | select count(*) from t       |
+----------+------------+------------------------------+

詳細剖析：SHOW PROFILE FOR QUERY n

mysql> SHOW PROFILE FOR QUERY 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| (initialization)               | 0.000004 |
| checking query cache for query | 0.000044 |
| Opening tables                 | 0.000012 |
| System lock                    | 0.000017 |
| Table lock                     | 0.000030 |
| init                           | 0.000013 |
| optimizing                     | 0.000008 |
| statistics                     | 0.000013 |
| preparing                      | 0.000011 |
| executing                      | 0.000006 |
| Sending data                   | 0.747313 |  -- 核心耗時
| end                            | 0.000014 |
| query end                      | 0.000006 |
| storing result in query cache  | 0.000006 |
| freeing items                  | 0.000012 |
| closing tables                 | 0.000009 |
| logging slow query             | 0.000183 |
+--------------------------------+----------+

多維度分析：CPU、I/O、記憶體、上下文切換

mysql> SHOW PROFILE CPU, BLOCK IO, MEMORY, SWAPS FOR QUERY 4;
mysql> SHOW PROFILE CPU, BLOCK IO, MEMORY, SWAPS, CONTEXT SWITCHES, SOURCE FOR QUERY 5;

輸出欄位含義

實戰案例：大表 INSERT/UPDATE 剖析

INSERT 200 萬行：34.3 秒

• Copying to tmp table 1.26 秒
• Converting HEAP to MyISAM 0.38 秒
• Copying to tmp table on disk 1.42 秒
• Sending data 31.1 秒（佔 90%+）

UPDATE 400 萬行：44.8 秒

• Updating 44.5 秒（CPU 用戶態 27.9 秒、核心態 2.8 秒）
• 自願上下文切換 9,949 次、非自願 1,584 次
• 區塊寫入操作 0（可能記憶體足夠未落盤）

優化洞察

1. Sending data 耗時長：結果集大、網路傳輸、客戶端處理慢
2. Creating tmp table / Converting HEAP to MyISAM：臨時表超過 tmp_table_size 落盤，調大參數或優化 SQL 避免臨時表
3. Updating 階段 CPU 高：大量行更新、索引維護開銷，考慮分批、延遲索引、pt-online-schema-change
4. Context switches 多：鎖競爭、I/O 等待，檢查隔離級別、索引設計

遷移建議

MySQL 5.7+ 已棄用 SHOW PROFILE，改用 Performance Schema（events_statements_history_long、events_stages_history_long）進行更完整的效能分析。