萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 慢查詢日誌配置詳解:開啟、參數調優、動態控制與分析工具

前言

慢查詢日誌是 MySQL 性能調優的「聽診器」,記錄執行時間超過閾值的 SQL,幫助定位全表掃描、索引缺失、鎖等待等問題。本文詳細說明配置啟用、關鍵參數、動態控制、日誌分析工具鏈與最佳實踐。

一、查看當前慢查詢狀態

SHOW VARIABLES LIKE '%slow%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/lib/mysql/hostname-slow.log |
| long_query_time     | 10.000000                     |
| log_queries_not_using_indexes | OFF                 |
| log_slow_admin_statements   | OFF                 |
+---------------------+-------------------------------+

二、配置文件啟用(持久化)

編輯 /etc/my.cnf 或 my.ini [mysqld] 區塊

[mysqld]
# 慢查詢閾值(秒,支持微秒,如 0.5)
long_query_time = 2

# 慢查詢日誌文件路徑(目錄需 mysql 用戶寫權限)
slow_query_log_file = /var/lib/mysql/slow.log

# 開啟慢查詢日誌
slow_query_log = 1

# 記錄未使用索引的查詢(可選,會產生大量日誌)
log_queries_not_using_indexes = 1

# 記錄管理命令(ALTER、ANALYZE 等,可選)
log_slow_admin_statements = 0

保存後重啟:systemctl restart mysqld。

三、動態啟用/關閉(運行時生效,重啟失效)

-- 開啟
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;          -- 即時調整閾值
SET GLOBAL log_queries_not_using_indexes = ON;

-- 關閉
SET GLOBAL slow_query_log = OFF;

動態修改適合臨時排查,生產環境持久化請改配置文件。

四、關鍵參數深度解析

參數建議值說明
long_query_time0.5 - 2太大漏掉慢 SQL,太小產生海量日誌。建議從 2 秒起步,逐步降至 0.5 秒。
slow_query_log_file獨立磁盤/分區避免與數據目錄爭搶 I/O,便於日誌輪轉管理。
log_queries_not_using_indexesOFF (生產)全表掃描但執行快的查詢也會記錄,噪音大。建議定期臨時開啟排查。
log_slow_admin_statementsOFF避免 Binlog Dump、ALTER TABLE 等管理命令污染日誌。
min_examined_row_limit100忽略掃描行數小於此值的查詢(5.6+),過濾微小表全掃描噪音。

五、日誌分析工具鏈

1. mysqldumpslow(官方自帶,聚合相似 SQL)

# 統計前 10 條按平均耗時排序
mysqldumpslow -s at -t 10 /var/lib/mysql/slow.log

# 統計按總耗時排序
mysqldumpslow -s t -t 20 /var/lib/mysql/slow.log

# 含數值佔位符抽象化
mysqldumpslow -a -s at -t 10 /var/lib/mysql/slow.log

2. pt-query-digest(Percona Toolkit,功能最強)


pt-query-digest /var/lib/mysql/slow.log --limit 20 --output json > digest.json
# 輸出:按總響應時間排序、每類 SQL 執行次數、平均/最大耗時、掃描行數、索引使用率、表結構建議

3. 圖形化分析

  • MySQL Workbench:導入慢查詢日誌 → Performance Dashboard 可視化。
  • Grafana + Prometheus + mysqld_exporter:實時監控慢查詢趨勢、Top SQL。

六、日誌輪轉與歸檔


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

七、最佳實踐清單

  1. 生產環境**務必開啟**慢查詢日誌,閾值初始 2 秒,逐步收緊至 0.5-1 秒。
  2. 日誌存放獨立磁盤,配置 logrotate 防止佔滿根分區。
  3. 每日跑批 pt-query-digest 生成報告,推送至運維群/郵件。
  4. 對 Top 10 慢 SQL 建立優化工單:EXPLAIN → 索引優化/改寫 SQL/調整架構。
  5. 定期清理/歸檔歷史日誌,保留最近 30 天明細,聚合報告長期存儲。
  6. 結合 performance_schema、sys schema 進行多維度性能診斷。

總結

慢查詢日誌是 MySQL 性能優化的起點。規範配置參數、建立自動化分析流程、形成「發現→分析→優化→驗證→固化」閉環,才能持續保障數據庫高性能穩定運行。

copyright © 萬盛學電腦網 all rights reserved