您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 配置優化系列（一）：慢查詢日誌啟用、分析與 mysqldumpslow 實戰

編輯：MySQL 綜合教學

MySQL 配置優化系列（一）：慢查詢日誌

慢查詢日誌是 SQL 調優的「體檢報告」。本文詳解三大日誌體系定位，聚焦慢查詢日誌啟用配置、參數調優、mysqldumpslow 分析實戰與生產環境最佳實踐。

MySQL 三大核心日誌體系

日誌類型	用途	關鍵配置
Error Log (`log_error`)	啟動/停止/嚴重錯誤	`log_error=/var/log/mysql/error.log`
Binary Log (`log_bin`)	增量變更、主從復制、時間點恢復	`log_bin=mysql-bin`, `binlog_format=ROW`
Slow Query Log (`slow_query_log`)	執行超時 SQL 審計、調優依據	見下文詳細配置

慢查詢日誌核心配置

[mysqld]
# 啟用慢查詢日誌
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

# 閾值：超過 1 秒記錄（微秒級精度支援）
long_query_time = 1

# 記錄未使用索引的查詢（可選，噪音較大）
log_queries_not_using_indexes = 1

# 記錄管理員命令（可選）
log_slow_admin_statements = 1

# 運行時動態開啟（無需重啟）
# SET GLOBAL slow_query_log = 1;

關鍵參數深度解析

long_query_time：不含鎖等待時間，僅計算執行器層耗時。建議生產設 0.5-1，分析期可臨時設 0 秒全采樣。
log_queries_not_using_indexes：全表掃描會大量觸發，建議關閉，改用 log_throttle_queries_not_using_indexes 限流（如每分鐘 10 條）。
min_examined_row_limit：掃描行數低於此值不記錄（默認 0），可過濾小表全掃噪音。

mysqldumpslow 實戰分析


輸出字段含義
Count: 15          # 執行次數
Time=2.34s (35s)   # 平均/總耗時
Lock=0.01s (0.1s)  # 平均/總鎖等待
Rows=120.0 (1800)  # 平均/總返回行數
user@host          # 執行用戶
Query: SELECT ...  # 抽象化 SQL（參數用 N/S 佔位）

實戰輸出示例
root@kallen:~# mysqldumpslow /var/log/mysql/mysql-slow.log
Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 1  Time=0.88s (0s)  Lock=0.00s (0s)  Rows=0.0 (0)
  debian-sys-maint@localhost
  SELECT COUNT(*) INTO @discard FROM `information_schema`.`COLUMNS`

Count: 1  Time=0.16s (0s)  Lock=0.00s (0s)  Rows=35.0 (35)
  debian-sys-maint@localhost
  SELECT CONCAT('S', TABLE_SCHEMA, 'S', TABLE_NAME, 'S') FROM information_schema.TABLES WHERE ENGINE='S'

生產環境最佳實踐

日誌輪轉：配置 logrotate 按天切割，保留 30 天，壓縮舊文件
集中收集：Filebeat/Fluentd 採集至 ELK/ClickHouse，支援全文檢索與趨勢圖表
定期審計：每周跑 mysqldumpslow -t 20 -s at，產出 Top SQL 優化工單
PT 工具鏈：pt-query-digest 功能更強，支援報告導出、指紋分組、EXPLAIN 自動執行
動態調整：大促前臨時設 long_query_time=0 全采樣，事後復原，避免磁盤 IO 抖動


避坑指南

慢查詢日誌本身有性能開銷（文件寫入、鎖），高並發核心庫建議 log_output=FILE 而非 TABLE
log_queries_not_using_indexes=1 易導致日誌爆增，務必配合 log_throttle_queries_not_using_indexes
僅分析不執行 EXPLAIN 等於白看，必須結合執行計劃定位索引缺失/類型轉換/關聯順序問題


慢查詢日誌是「被動發現」利器，配合 Performance Schema（主動監控）與 EXPLAIN ANALYZE（深度診斷），構建立體 SQL 調優體系。