MySQL 5.1+ 內建 Profiling 功能，配合 SHOW PROFILE、SHOW STATUS、EXPLAIN、Slow Log、OS 級工具，構建多維性能分析體系。本文匯總核心命令、變量、輸出解讀與實戰組合拳。
SHOW VARIABLES LIKE '%profiling%';
-- profiling=OFF (默認)
SET profiling = 1; -- 當前 Session 開啟
SELECT @@profiling; -- 確認=1| 命令 | 用途 | 典型場景 |
|---|---|---|
SHOW PROFILES | 列出最近執行語句的 Query_ID、總耗時、SQL 文本 | 快速定位慢語句 |
SHOW PROFILE FOR QUERY # | 拆解指定 Query_ID 的執行階段耗時 (CPU、IPC、鎖、I/O 等) | 深度剖析單條 SQL |
SHOW PROFILE CPU, BLOCK IO FOR QUERY # | 顯示 CPU 時間、塊 I/O 等待 | 定位 CPU 密集或 I/O 瓶頸 |
FLUSH STATUS; SQL; SHOW STATUS; | 對比執行前後狀態變量增量 | 量化 Handler_read_key、Created_tmp_tables 等 |
EXPLAIN SELECT ... | 查看優化器執行計劃 (type、key、rows、Extra) | 索引優化、連接優化 |
SHOW PROCESSLIST | 當前線程狀態、執行時間、SQL 文本 | 實時阻塞排查 |
Handler_read_key：索引讀取次數Handler_read_rnd_next：全表掃描次數Created_tmp_tables / Created_tmp_disk_tables：臨時表/落盤臨時表Select_scan / Select_full_join：全表掃描/無索引連接Sort_merge_passes：排序溢出磁盤次數Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests：命中率 = 1 - reads/requestsInnodb_row_lock_waits / Innodb_row_lock_time_avg：行鎖等待SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE '%long_query_time%';
-- long_query_time=2 (建議 1~2 秒)
-- slow_query_log=ON
-- slow_query_log_file=/var/log/mysql/slow.log
-- log_queries_not_using_indexes=ON (慎用)
-- log_slow_admin_statements=ON| 工具 | 關注指標 |
|---|---|
vmstat 1 | r (運行隊列)、b (阻塞)、si/so (換頁)、bi/bo (塊 I/O)、us/sy/id (CPU) |
iostat -x 1 | %util (設備飽和)、await (平均等待)、svctm (服務時間)、rkB/s、wkB/s |
mpstat -P ALL 1 | 各 CPU 核心 us/sy/id/iowait |
strace -p PID -c | 系統調用統計 (read/write/poll/futex 耗時佔比) |
perf top -p PID | 函數級熱點 (需內核支援、debug 符號) |
SHOW PROFILES 找到 Query_ID，SHOW PROFILE CPU, BLOCK IO FOR QUERY # 看階段分布。Sending data 佔比高 → EXPLAIN 檢查索引、Handler_read_rnd_next 確認全掃描。Opening tables/Table lock 高 → 檢查 table_open_cache、鎖爭用。Block I/O 高 → iostat 確認磁盤壓力、Innodb_buffer_pool_reads 確認命中率。MySQL 5.7+ 棄用 SHOW PROFILE，官方推薦 Performance Schema：
SELECT * FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT LIKE '%target%';
SELECT * FROM performance_schema.events_stages_history_long
WHERE EVENT_ID = ...; -- 階段耗時Performance Schema 預設開啟、持久化、支援更豐富的等待事件、內存/鎖/階段統計，學習成本略高但功能強大得多。
