MySQL 慢查詢定位兩大神器：慢查詢日誌與 SHOW PROCESSLIST 實戰指南

核心思路

定位低效 SQL 兩條主線：**事後分析（慢查詢日誌）** + **實時監控**

一、慢查詢日誌：事後複盤利器

1. 開啟配置

# my.cnf [mysqld] 區塊
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2          # 閾值：超過 2 秒記錄
log_queries_not_using_indexes = 1  # 記錄無索引查詢

# 動態生效（無需重啟）
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;

2. 實戰測試

-- 查看當前閾值
SHOW VARIABLES LIKE 'long_query_time';

-- 臨時調整為 5 秒測試
SET GLOBAL long_query_time = 5;

-- 查詢 1：0.00 秒，不記錄
SELECT COUNT(*) FROM order2008;

-- 查詢 2：11.07 秒，記錄
SELECT COUNT(*) FROM t_user;

3. 日誌分析

# 直接查看
cat /var/log/mysql/slow.log
# Time: 081026 19:46:34
# User@Host: root[root] @ localhost []
# Query_time: 11  Lock_time: 0  Rows_sent: 1  Rows_examined: 6552961
SELECT COUNT(*) FROM t_user;

4. mysqldumpslow 聚合分析（必會工具）

mysqldumpslow /var/log/mysql/slow.log
# 輸出：
# Count: 2  Time=11.00s (22s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
#   SELECT COUNT(N) FROM t_user

優勢：自動參數化（N 代替具體值）、聚合統計、快速定位高頻慢查詢。

二、SHOW PROCESSLIST：實時監控雷達

1. 基礎用法

SHOW PROCESSLIST;
-- 或完整版（不截斷 Info）
SHOW FULL PROCESSLIST;

2. 關鍵欄位解讀

3. 實戰場景

-- 找出運行超過 60 秒的查詢
SELECT * FROM information_schema.PROCESSLIST
WHERE TIME > 60 AND COMMAND = 'Query';

-- 找出鎖等待
SELECT * FROM information_schema.PROCESSLIST
WHERE STATE LIKE '%lock%';

-- 殺死問題線程
KILL 12345;

三、組合拳最佳實踐

1. 常態化：慢查詢日誌**永久開啟**，定期 mysqldumpslow 出 Top 10 優化
2. 告警：監控系統對接 SHOW PROCESSLIST，Time > 30s 觸發告警
3. 關聯分析：慢查詢日誌找到高頻 SQL → EXPLAIN 分析執行計劃 → 加索引/改寫 SQL
4. 線上排查：業務反應卡頓 → 即時 SHOW PROCESSLIST 定位阻塞源 → KILL 或優化

四、進階工具推薦

• pt-query-digest（Percona Toolkit）：慢查詢日誌深度分析，生成可視化報告
• mysqlslap：官方壓測工具，模擬並發驗證優化效果
• Sysbench：標準化基準測試，容量規劃必備
• Grafana + Prometheus + mysqld_exporter：全鏈路監控大盤

慢查詢日誌是**戰略複盤**，SHOW PROCESSLIST 是**戰術雷達**。雙劍合璧，無慢 SQL 可藏。建議將 long_query_time=1 寫入基線配置，配合 log_queries_not_using_indexes 全方位捕獲低效查詢。