慢查詢日誌基礎配置

預設關閉，需在 my.cnf [mysqld] 區段啟用：

slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2          -- 超過 2 秒記錄
log_queries_not_using_indexes = ON  -- 可選：記錄未用索引查詢
log_slow_admin_statements = ON      -- 記錄管理命令
log_slow_slave_statements = ON      -- 記錄 Slave 複製慢查詢

動態修改（無需重啟）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

分析利器：mysqldumpslow

官方內建 Perl 腳本，支援多維排序：

# 按查詢時間倒序，取前 10，含 left join
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/slow.log

# 參數速查
-s c/t/l/r  : 按次數/時間/鎖時間/返回行數排序（加 a 為倒序）
-t N        : Top N
-g "regex"  : 正則過濾（不區分大小寫）

進階分析：mysqlsla（已停更，推薦 pt-query-digest）

# 安裝 Percona Toolkit
wget https://www.percona.com/downloads/percona-toolkit/3.5.5/binary/redhat/8/x86_64/percona-toolkit-3.5.5-1.el8.x86_64.rpm
yum install -y percona-toolkit-3.5.5-1.el8.x86_64.rpm

# 一鍵生成報表
pt-query-digest --limit 20 --filter '$event->{Query_time} > 1' /var/log/mysql/slow.log

# 持久化到資料庫供歷史趨勢分析
pt-query-digest --review h=localhost,D=percona,t=query_review --create-review-table \
  --user=root --password=xxx /var/log/mysql/slow.log

關鍵狀態變數監控

變數	含義	健康基準
Slow_queries	慢查詢累計數	持續增長需介入
Max_used_connections / max_connections	連接使用率	≈ 85% 為宜
Threads_created / Connections	線程緩存命中率	< 0.1 為佳，否則調大 thread_cache_size
Created_tmp_disk_tables / Created_tmp_tables	磁盤臨時表比率	< 10%，否則調大 tmp_table_size/max_heap_table_size
Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests	緩存未命中率	< 1%，否則擴充 Buffer Pool
Key_reads / Key_read_requests	MyISAM 索引未命中率	< 0.01，否則調大 key_buffer_size

完整調優閉環

1. 開啟慢查詢，設定合理閾值（生產建議 1~2 秒）。
2. 定期分析：pt-query-digest 產生 Top N 報表，聚焦高頻、高耗時、高掃描行數 SQL。
3. EXPLAIN 驗證：確認索引使用、存取類型、臨時表/排序情況。
4. 優化手段：加索引、改寫 SQL、調整參數、分表分庫。
5. 驗證回歸：上線後持續監控慢查詢趨勢與關鍵指標。

避坑指南

• log_queries_not_using_indexes 會產生大量噪音，生產環境建議關閉，改用 performance_schema 或 sys.schema 監控全量 SQL。
• 慢查詢日誌本身會佔用磁盤 I/O，建議掛載獨立磁盤或定期輪轉（logrotate）。
• RDS/雲資料庫無法直接讀檔案，需用 mysql.slow_log 表或雲廠商提供的下載介面。