萬盛學電腦網

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

MySQL 慢查詢日誌完整指南:開啟、分析、工具與最佳實踐

啟用慢查詢日誌

Linux (my.cnf)

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slowquery.log
long_query_time = 5
log_queries_not_using_indexes = ON
# log_throttle_queries_not_using_indexes = 60  # 限制無索引查詢記錄頻率

Windows (my.ini)

[mysqld]
slow_query_log = 1
slow_query_log_file = E:\mysql\log\mysqlslowquery.log
long_query_time = 5

參數說明:

  • long_query_time:超過此秒數記錄,預設 10 秒,建議 1-5 秒
  • log_queries_not_using_indexes:記錄未使用索引的查詢,易產生大量日誌,建議配合 log_throttle_queries_not_using_indexes 限流

日誌分析工具與指令

mysqldumpslow(官方內建)

# 顯示前 20 筆最耗時查詢
mysqldumpslow -s t -t 20 /var/lib/mysql/slowquery.log

# 顯示前 20 筆執行次數最多查詢
mysqldumpslow -s c -t 20 /var/lib/mysql/slowquery.log

# 含 left join 的前 10 筆最慢查詢
mysqldumpslow -t 10 -s t -g "left join" /var/lib/mysql/slowquery.log

排序參數: t(時間), c(次數), l(鎖定時間), r(返回行數);前綴 a 表示反序。

mysqlsla(進階分析,Perl 腳本)

安裝

wget https://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
tar zxvf mysqlsla-2.03.tar.gz
cd mysqlsla-2.03
perl Makefile.PL
make && make install

# 缺少模組時
cpan DBI
cpan DBD::mysql

常用參數

  • -lt slow:指定慢查詢日誌類型
  • -sf "+SELECT,UPDATE":只分析 SELECT/UPDATE
  • -sort t_sum:按總耗時排序(預設)
  • -top 10:顯示前 10 筆
  • -db dbname:指定資料庫

實戰範例

# 耗時最久的 10 條 SELECT
mysqlsla -lt slow -sf "+SELECT" -top 10 slow.log > slow_top10_time.log

# 執行次數最多的 100 條 SELECT/UPDATE
mysqlsla -lt slow -sf "+SELECT,UPDATE" -top 100 -sort c_sum -db ultraxsmutf8 slow.log > slow_top100_count.log

輸出關鍵指標解讀

  • Count:執行次數與佔比
  • Time:總/平均/最小/最大耗時,佔總慢查詢時間比例
  • 95% of Time:去除極值後 95% 覆蓋率耗時
  • Lock Time:鎖等待時間
  • Rows sent/examined:返回/掃描行數
  • Query abstract/sample:抽象化與實際 SQL

日誌維護與常見問題

手動刷新日誌(未自動產生新檔時)

mysql> FLUSH LOGS;
# 或動態切換
SET GLOBAL slow_query_log = OFF;
SET GLOBAL slow_query_log = ON;

清理舊日誌(建議截斷而非刪除)

# 截斷保留檔案 inode,避免 MySQL 寫入失敗
> /var/lib/mysql/slowquery.log
# 或
cp /dev/null /var/lib/mysql/slowquery.log

常見錯誤與解決

  • Can't locate DBI.pm → cpan DBI
  • Use of uninitialized value ... at mysqlsla line 2962 → 移除 -sort t_sum(預設即為時間排序)

最佳實踐建議

  1. 生產環境 long_query_time = 1-2,配合 log_throttle_queries_not_using_indexes
  2. 定期(每日/每週)輪轉日誌,保留 30 天
  3. 結合 EXPLAIN 分析 Top N 慢查詢,優化索引或 SQL
  4. 監控 Slow_queries 狀態變數,設定告警閾值
copyright © 萬盛學電腦網 all rights reserved