網站瓶頸分析：MySQL 慢查詢與進程列性能診斷實戰

網站瓶頸分析：MySQL 慢查詢日誌與 SHOW PROCESSLIST 實戰診斷

MySQL 性能瓶頸 80% 以上源於 SQL 語句本身。本文聚焦兩大核心診斷手段：慢查詢日誌配置分析與實時進程列監控，幫助快速定位並解決線上性能問題。

一、慢查詢日誌：從配置到分析

1. 核心參數查看

-- 查看慢查詢閾值（默認 10 秒，建議改為 1 秒）
SHOW VARIABLES LIKE 'long_query_time';
-- 查看慢查詢開關與文件路徑
SHOW VARIABLES LIKE 'slow_query_log%';
-- 查看是否記錄未使用索引的查詢
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

2. 推薦生產環境配置（動態生效，無需重啟）

SET GLOBAL long_query_time = 1;              -- 1 秒閾值
SET GLOBAL slow_query_log = 'ON';            -- 開啟慢查詢日誌
-- 可選：記錄未走索引的查詢（高併發慎開，日誌量大）
-- SET GLOBAL log_queries_not_using_indexes = 'ON';

持久化配置：修改 my.cnf [mysqld] 區段：

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
# log_queries_not_using_indexes = 1

3. 日誌分析工具鏈

• mysqldumpslow：官方自帶，按查詢時間、行數、次數排序。
• pt-query-digest：Percona Toolkit 核心工具，生成詳細報告，支持按指紋聚合。
• mysqlslowlog：圖形化分析工具。

# 典型用法
pt-query-digest /var/lib/mysql/slow.log --limit 20 --order-by Query_time:cnt

二、SHOW PROCESSLIST：實時會話診斷

1. 關鍵列含義

2. 進階用法

-- 顯示完整 SQL（不截斷）
SHOW FULL PROCESSLIST;

-- 只看非 Sleep 的活躍會話
SHOW FULL PROCESSLIST WHERE Command != 'Sleep';

-- 結合 Information Schema 做聚合統計
SELECT Command, COUNT(*), AVG(Time) 
FROM information_schema.PROCESSLIST 
GROUP BY Command;

三、綜合診斷流程

1. 告警觸發：監控系統發現 QPS 下降、RT 飆升、CPU 飆高。
2. 即時快照：SHOW FULL PROCESSLIST 定位阻塞鏈、長事務、大查詢。
3. 歷史歸因：分析慢查詢日誌，找出 Top N 消耗資源的 SQL 模式。
4. 執行計劃：EXPLAIN 驗證索引命中、掃描行數、臨時表、文件排序。
5. 優化落地：加索引、改 SQL、調參數、拆大查詢、上緩存。
6. 驗證回歸：預發環境壓測對比，確認無退化後發布。

四、避坑指南

• 慢查詢閾值設太大（>5s）會漏掉大量中等查詢；設太小（<0.5s）日誌噪音過大。
• log_queries_not_using_indexes 在高併發下會產生海量日誌，建議定時開啟採樣而非常開。
• SHOW PROCESSLIST 本身有鎖開銷，極高併發下慎用，優先查 performance_schema.threads。
• 發現大量 Sleep 連接，檢查連接池配置（wait_timeout、max_idle）與應用釋放邏輯。

建立「慢查詢日誌常態化分析 + 進程列實時巡檢」的雙軌機制，是 MySQL 性能治理的基石。