啟用慢查詢日誌

配置文件（持久化）

[mysqld]
# 核心開關
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1              # 超過 1 秒記錄（可設毫秒：0.5）
log_queries_not_using_indexes = 1 # 記錄未走索引的查詢（建議開啟）

# 可選：只記錄管理員查詢、指定輸出格式
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_output = FILE               # 或 TABLE 寫入 mysql.slow_log 表

動態生效（無需重啟）

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 1;

Windows 環境路徑寫法

[mysqld]
slow_query_log_file = "C:/ProgramData/MySQL/MySQL Server 8.0/Data/slow.log"
# 路徑含空格需雙引號，反斜槓轉義或用正斜槓

mysqldumpslow 官方分析工具深度用法

核心參數

參數	含義	常用值
-s	排序方式	c計數 t時間 l鎖定 r返回行 at平均時間 al平均鎖定 ar平均返回行
-t N	Top N 筆	-t 10 -t 20
-g "pattern"	正則過濾（忽略大小寫）	-g "left join" -g "table_a"
-a	不抽象化數值/字串（顯示完整 SQL）	除錯具體語句時使用

實戰範例

# 1. 最高頻 Top 20
mysqldumpslow -s c -t 20 /var/lib/mysql/slow.log

# 2. 耗時最長 Top 10
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# 3. 含 LEFT JOIN 且耗時最長 Top 10
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

# 4. 平均鎖定時間最高 Top 10
mysqldumpslow -s al -t 10 /var/lib/mysql/slow.log

# 5. 完整 SQL 不抽象化（配合 less 搜尋）
mysqldumpslow -a -s t -t 5 /var/lib/mysql/slow.log | less

進階分析：pt-query-digest（Percona Toolkit）

功能更強：按指紋分組、生成報表、支援 JSON/CSV 輸出、可對比不同時段。

# 安裝
yum install percona-toolkit -y  # 或 apt

# 分析慢日誌生成報表
pt-query-digest /var/lib/mysql/slow.log --limit 20 --output report > digest_report.txt

# 直接對比兩個時間段
pt-query-digest slow.log.2024-01-01 slow.log.2024-01-02 --compare

慢查詢優化標準流程

1. 定位：mysqldumpslow -s t -t 10 取最耗時 SQL。
2. EXPLAIN：EXPLAIN SELECT ... 觀察 type、key、rows、Extra。
3. 索引優化：補覆蓋索引、調整列順序、移除冗餘索引。
4. SQL 改寫：拆分大事務、避免 SELECT *、優化 OR/IN/LIKE%、衍生表物化。
5. 架構層面：分表分庫、引入 Redis/ES 承擔高頻讀、異步寫入。
6. 驗證：FLUSH SLOW LOGS 切割日誌，再跑負載對比。

常見慢查詢模式與對策

模式	EXPLAIN 特徵	對策
全表掃描	type=ALL, key=NULL	加 WHERE 列索引、覆蓋索引
範圍掃描過大	type=range, rows 巨大	縮小範圍、分區表、延遲關聯
臨時表+檔案排序	Extra=Using temporary; Using filesort	索引順序匹配 ORDER BY/GROUP BY、增大 sort_buffer_size
隱式類型轉換	Extra=Using where 但索引失效	字段類型與參數一致、避免函數包裹列
深分頁	LIMIT 100000, 20	延遲關聯/游標分頁/覆蓋索引掃描

日誌維運自動化

# /etc/logrotate.d/mysql-slow
/var/lib/mysql/slow.log {
    daily
    rotate 30
    compress
    missingok
    notifempty
    create 640 mysql mysql
    sharedscripts
    postrotate
        mysqladmin flush-logs > /dev/null 2>&1 || true
    endscript
}

總結

• 慢查詢日誌是效能調優的「體檢報告」，務必常態化開啟並定期分析。
• long_query_time=1 為起步值，核心業務建議降至 0.1-0.5 秒。
• 工具鏈：mysqldumpslow 快速排查 → pt-query-digest 深度報表 → EXPLAIN 定向優化。
• 優化閉環：發現 → 分析 → 索引/SQL/架構調整 → 驗證 → 固化最佳實踐。