慢查詢日誌三大核心開關
| 變數 | 作用 | 建議值 | 動態修改 |
|---|
slow_query_log | 總開關 | ON | SET GLOBAL slow_query_log = ON; |
long_query_time | 閾值(秒)，超過記錄 | 1 (生產) / 0.1 (分析期) | SET GLOBAL long_query_time = 1; |
log_queries_not_using_indexes | 記錄未用索引查詢 | ON (慎用，量大) | SET GLOBAL log_queries_not_using_indexes = ON; |
永久生效配置 (my.cnf)
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_output = FILE
# 可選：限制日誌大小，配合 logrotate
注意： 動態修改 long_query_time 需重新連接生效。log_queries_not_using_indexes=ON 會產生海量日誌，磁碟易寫滿，建議僅分析期短期開啟，或配合 log_throttle_queries_not_using_indexes (5.6+) 限流。
慢查詢日誌格式深度解析
# Time: 2024-01-15T10:30:45.123456Z
# User@Host: app_user[app_user] @ 10.0.1.5 [10.0.1.5] Id: 12345
# Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 100 Rows_examined: 1000000
# Rows_affected: 0 Bytes_sent: 12345
SET timestamp=1705315845;
SELECT * FROM huge_table WHERE status = 1 ORDER BY create_time LIMIT 100;
| 字段 | 含義 | 優化參考價值 |
|---|
Query_time | 總執行時間 | 核心指標，> long_query_time 即記錄 |
Lock_time | 等待鎖時間 | 高鎖等待提示熱點行爭用或長事務 |
Rows_examined | 掃描行數 | 關鍵：Rows_examined >> Rows_sent 表明全表掃描/索引失效 |
Rows_sent | 返回行數 | 大結果集傳輸開銷 |
五大分析工具對比
| 工具 | 特點 | 適用場景 | Windows 支援 |
|---|
| mysqldumpslow | MySQL 內建，Perl 腳本 | 快速聚合 Top N、按時間/次數/掃描行排序 | 需 Perl 環境 (Strawberry Perl) |
| pt-query-digest (Percona Toolkit) | 功能最強，支援多維度報表、歷史趨勢、EXPLAIN 自動分析 | 生產環境標準分析工具 | 需 Perl，Windows 可用 |
| mysqlsla | 較老，功能豐富 | 歷史項目維護 | Perl 依賴複雜 |
| MySQL Workbench | 圖形化，視覺化分析 | 偶爾手工分析、團隊協作 | 原生支援 |
| GoAccess / Graylog / ELK | 日誌集中化平台 | 大規模集群、實時告警 | Docker 部署跨平台 |
pt-query-digest 實戰命令
# 安裝 Percona Toolkit
yum install -y percona-toolkit # 或下載二進制
# 基礎分析：生成報表
pt-query-digest /var/log/mysql/slow.log --limit 20 --output json > slow_report.json
# 進階：按 Query_time 降序，顯示 EXPLAIN
pt-query-digest /var/log/mysql/slow.log \
--order-by Query_time:desc \
--limit 10 \
--explain h=localhost,u=root,p=pass \
--filter '$event->{fingerprint} =~ /SELECT/'
# 實時監控模式
pt-query-digest --processlist h=localhost,u=root,p=pass --interval 5 --run-time 300
生產環境最佳實踐
- 分級閾值：核心業務庫
long_query_time=0.5，非核心 1-2
- 日誌輪轉：
logrotate 每日切割，保留 30 天，壓縮舊檔
- 定期分析排程：每日跑
pt-query-digest，推送 Top 10 到監控群/郵件
- 索引優化閉環：慢查詢 → EXPLAIN → 建索引 → 驗證 → 回歸測試
- 避免 log_queries_not_using_indexes 長期開啟：改用
Performance Schema 或 sys.schema_unused_indexes 定期掃描
