MySQL 查詢日誌與慢查詢日誌完整配置指南

MySQL 查詢日誌與慢查詢日誌完整配置指南

MySQL 提供了多種日誌類型，其中通用查詢日誌與慢查詢日誌是日常運維與性能調優最常用的兩種。本文將詳細介紹如何啟用、配置、查看以及分析這兩類日誌，並說明新舊參數的差異與最佳實踐。

一、通用查詢日誌

通用查詢日誌會記錄 MySQL 接收到的所有 SQL 語句（包括 SELECT），適合短期排查特定問題，但會產生大量 I/O，生產環境不建議長期開啟。

1. 查看預設狀態

mysql> SHOW VARIABLES LIKE '%log';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| general_log                    | OFF   |
| log                            | OFF   |
| slow_query_log                 | OFF   |
+--------------------------------+-------+

2. 動態開啟（臨時，重啟失效）

mysql> SET GLOBAL general_log = ON;
mysql> SET GLOBAL general_log_file = '/tmp/mysql_general.log';

3. 持久化配置（my.cnf）

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql_general.log

注意： MySQL 5.1+ 建議使用 general_log / general_log_file，舊參數 log 已棄用，啟動時會報警告。

4. 日誌內容示例

/usr/sbin/mysqld, Version: 5.5.22-log. started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time            Id  Command    Argument
130731 7:55:41  1   Query      show databases
130731 7:55:56  1   Query      SELECT DATABASE()
130731 7:55:59  1   Query      show tables

二、慢查詢日誌

慢查詢日誌僅記錄執行時間超過閾值的 SQL，是性能調優的核心依據。

1. 關鍵參數

• slow_query_log：開關
• slow_query_log_file：日誌路徑
• long_query_time：閾值（秒，支援微秒），預設 10s
• log_queries_not_using_indexes：記錄未使用索引的查詢

2. 配置範例

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

3. 動態調整

mysql> SET GLOBAL long_query_time = 0.5;
mysql> SET GLOBAL log_queries_not_using_indexes = ON;

4. 日誌格式解讀

# Time: 2023-08-01T02:36:25.123456Z
# User@Host: root[root] @ localhost []
# Query_time: 2.274219  Lock_time: 0.000322  Rows_sent: 0  Rows_examined: 5440
use test;
SET timestamp=1375295785;
INSERT INTO t SELECT * FROM t;

三、mysqldumpslow 分析工具

MySQL 內建的 Perl 腳本，可快速聚合慢查詢日誌。

常用參數

實戰範例

# 找出最耗時的 5 條查詢
mysqldumpslow -s at -t 5 /var/log/mysql/mysql_slow.log

# 找出出現次數最多的 10 條查詢
mysqldumpslow -s c -t 10 /var/log/mysql/mysql_slow.log

四、最佳實踐建議

1. 生產環境：長期開啟慢查詢日誌，long_query_time 設 0.5~1 秒；通用查詢日誌僅在排查時短暫開啟。
2. 日誌輪轉：配合 logrotate 定期切割、壓縮、清理，避免磁碟耗盡。
3. 輸出到表：設 log_output = TABLE 可寫入 mysql.general_log / mysql.slow_log，便於 SQL 查詢分析，但需定期 TRUNCATE 防膨脹。
4. 結合 pt-query-digest：Percona Toolkit 提供更強大的分析報表，建議生產環境搭配使用。