萬盛學電腦網

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

利用 MySQL 慢查詢日志分析定位 CPU 高佔用根因並優化

MySQL CPU 飆高 90%+ 時,慢查詢日志是定位「兇手」的最直接工具。本文詳述慢日志開啟配置、mysqldumpslow 分析實戰、線上安全清理流程,助你快速鎖定性能殺手。

慢查詢日志配置

MySQL 5.0

[mysqld]
long_query_time = 1
log-slow-queries = /var/log/mysql/slow.log

MySQL 5.1+ (推薦)

[mysqld]
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log

關鍵參數:

  • long_query_time:閾值(秒),建議生產環境設 1~2 秒
  • slow_query_log_file:日志路徑,確保 MySQL 進程有寫權限

mysqldumpslow 實戰分析

運行一段時間(如 30 分鐘)後關閉慢日志,避免持續寫入影響性能。

常用參數

參數含義
-s c按查詢次數排序(ac 倒序)
-s t按查詢時間排序(at 倒序,預設)
-s l按鎖定時間排序
-s r按返回行數排序
-t N顯示前 N 條
-g "pattern"正則過濾(不區分大小寫)

經典分析命令

# 最高頻 Top 20
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log

# 返回行數最多 Top 20(疑似全表掃描)
mysqldumpslow -s r -t 20 /var/log/mysql/slow.log

# 耗時最長的含 LEFT JOIN 查詢 Top 10
mysqldumpslow -t 10 -s t -g "left join" /var/log/mysql/slow.log

線上安全清空/輪轉慢日志流程

直接 rm slow.log 會導致 MySQL 寫入失敗(文件句柄未釋放),需按以下流程:

-- 1. 查看狀態
SHOW VARIABLES LIKE '%slow%';

-- 2. 關閉慢日志
SET GLOBAL slow_query_log = 0;

-- 3. 修改日志路徑(輪轉)
SET GLOBAL slow_query_log_file = '/mysqllog/slow_log/slow_queries_3306_new.log';

-- 4. 重新開啟
SET GLOBAL slow_query_log = 1;

-- 5. 驗證新日志寫入
SELECT SLEEP(10);
-- 觀察新文件是否記錄該查詢

-- 6. 備份舊大文件
mv /mysqllog/slow_log/slow_queries_3306.log /mysqlbackup/slow_log/slow_queries_3306.log.bak.20240113

優化閉環:從慢 SQL 到索引優化

  1. 提取高頻/高耗時 SQL
  2. EXPLAIN 分析執行計劃(關注 type、key、rows、Extra)
  3. 判斷缺失索引、索引失效原因(函數運算、隱式轉換、最左前綴違反)
  4. 添加組合索引 / 調整 SQL 寫法 / 拆分大查詢
  5. 驗證優化效果(再次觀察慢日志)

進階工具推薦

  • pt-query-digest (Percona Toolkit):功能更強大,支援按 fingerprint 聚合、生成報表
  • MySQL Enterprise Monitor / Prometheus + Grafana:持續監控慢查詢趨勢
  • sys.schema (5.7+):sys.statements_with_high_avg_latency 等視圖直接查詢

慢查詢日志不是終點,而是優化的起點。建立「開啟 → 分析 → 優化 → 驗證 → 關閉」的標準化流程,讓數據庫性能持續在線。

copyright © 萬盛學電腦網 all rights reserved