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

MySQL 查詢歷史記錄啟用與分析：General Log 與 Slow Query Log 實戰

編輯：MySQL 綜合教學

MySQL 查詢歷史記錄啟用與分析：General Log 與 Slow Query Log 實戰

生產環境排查「剛才跑了什麼 SQL」、「誰刪了資料」、「為什麼突然變慢」時，MySQL 內建的 General Log（通用日誌）與 Slow Query Log（慢查詢日誌）是關鍵證據。本文說明啟用方式、效能影響、分析工具與最佳實踐。

一、General Log（通用查詢日誌）

啟用方式

# 方式 1：配置檔永久生效 (my.cnf)
[mysqld]
general_log = ON
general_log_file = /var/lib/mysql/general.log
log_output = FILE  # 或 TABLE (寫入 mysql.general_log 表)

# 方式 2：線上動態開啟（重啟失效）
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/lib/mysql/general.log';
SET GLOBAL log_output = 'FILE';

# 關閉
SET GLOBAL general_log = OFF;

效能影響與風險

⚠️ **高併發下極大影響效能**：每條 SQL 都同步寫磁碟，IOPS 飆升，QPS 下降 30%~50%。
日誌檔案增長極快（GB/小時），需配合 logrotate 切割。
**嚴禁生產環境長期開啟**，僅限短時段排查（如 10~30 分鐘）。

分析技巧

# 即時追蹤
tail -f /var/lib/mysql/general.log

# 篩選特定使用者/資料庫/關鍵字
grep "root@" /var/lib/mysql/general.log
grep "DROP TABLE" /var/lib/mysql/general.log
grep "DELETE FROM" /var/lib/mysql/general.log | head -20

# 統計高頻 SQL（簡易）
awk '{print $NF}' /var/lib/mysql/general.log | sort | uniq -c | sort -nr | head -20

二、Slow Query Log（慢查詢日誌）—— 生產環境長期開啟首選

配置最佳實踐

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1          # 超過 1 秒記錄（建議 0.5~2 秒）
log_queries_not_using_indexes = ON  # 記錄未用索引查詢（含全表掃描）
log_throttle_queries_not_using_indexes = 60  # 每分鐘最多記錄 60 條無索引查詢，防刷日誌
min_examined_row_limit = 100  # 忽略掃描行數 < 100 的查詢
log_output = FILE

動態調整

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;  -- 秒，支援微秒
SET GLOBAL log_queries_not_using_indexes = ON;

三、慢查詢分析工具鏈

工具	用途	典型指令
mysqldumpslow	官方內建，快速統計 Top N	`mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log` (按時間排序 Top 10)
pt-query-digest	Percona Toolkit，專業分析、報表、指紋分組	`pt-query-digest /var/lib/mysql/slow.log --limit 20 --filter '$event->{Query_time} > 1'`
mysqlsla	輕量級分析，HTML 報表	`mysqlsla -lt slow /var/lib/mysql/slow.log`
sys.schema_table_statistics	MySQL 5.7+ sys schema，表級統計	`SELECT * FROM sys.schema_table_statistics_with_buffer ORDER BY rows_fetched DESC LIMIT 10;`

四、Slow Log 關鍵欄位解讀

# Time: 2024-05-20T14:30:00.123456Z
# User@Host: app_user[app_user] @ 10.0.1.5 [10.0.1.5]  Id: 12345
# Query_time: 2.345678  Lock_time: 0.000123  Rows_sent: 100  Rows_examined: 1000000
# Rows_affected: 0  Bytes_sent: 12345
SET timestamp=1716215400;
SELECT * FROM large_table WHERE status = 1 ORDER BY created_at DESC LIMIT 100;

Query_time：總耗時（含鎖等待、網路傳輸）
Lock_time：等待鎖時間
Rows_examined：掃描行數（核心指標，遠大於 Rows_sent 表示索引缺失）
Rows_sent：回傳行數

五、生產環境日誌管理策略

Slow Log 永久開啟，long_query_time=1，配合 logrotate 每日切割、保留 30 天、壓縮。
General Log 預設關閉，僅排查時臨時開啟，結束立即關閉。
日誌存放獨立磁碟/分區，避免佔滿系統盤導致資料庫掛掉。
定期自動化分析：crontab 每天跑 pt-query-digest 產生報表，推送至運維群/郵件。
審計需求：若需完整審計軌跡，考慮 MariaDB Audit Plugin 或 MySQL Enterprise Audit，而非 General Log。

六、logrotate 配置範例

# /etc/logrotate.d/mysql
/var/lib/mysql/slow.log /var/lib/mysql/general.log /var/lib/mysql/error.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql
    sharedscripts
    postrotate
        # 重新打開日誌檔案
        mysqladmin -u root -p${MYSQL_ROOT_PASSWORD} flush-logs 2>/dev/null || true
    endscript
}