MySQL慢查询日志

 慢查询日志是 MySQL 中最核心的性能诊断工具，用于记录执行时间超过指定阈值的 SQL 语句，帮助开发者和 DBA 定位性能瓶颈。

一、查看慢查询日志状态

在优化之前，首先检查当前慢查询日志的配置状态。

 查看慢查询日志是否开启及日志文件位置

SHOW VARIABLES LIKE 'slow_query_log%';

 查看慢查询时间阈值（单位：秒）

SHOW VARIABLES LIKE 'long_query_time';

 查看未使用索引的查询是否记录到慢日志

SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

 查看慢查询日志记录数量

SHOW GLOBAL STATUS LIKE 'Slow_queries';

输出示例：

 Variable_name        Value                               

 slow_query_log       OFF                                 

slow_query_log_file  /var/lib/mysql/server-slow.log      

 Variable_name    Value    

 long_query_time  10.000000

二、配置慢查询日志

1. 临时开启（重启后失效）

适合临时诊断问题使用。

 开启慢查询日志

SET GLOBAL slow_query_log = 'ON';

 设置慢查询时间阈值为2秒（默认10秒）

SET GLOBAL long_query_time = 2;

 记录未使用索引的查询（即使执行时间很快）

SET GLOBAL log_queries_not_using_indexes = 'ON';

 设置日志输出格式（FILE | TABLE | NONE）

SET GLOBAL log_output = 'FILE';

2. 永久配置（修改配置文件）

编辑 MySQL 配置文件 `my.cnf`（Linux）或 `my.ini`（Windows）：

[mysqld]

 启用慢查询日志

slow_query_log = 1

 指定日志文件路径

slow_query_log_file = /var/lib/mysql/mysql-slow.log

 设置慢查询时间阈值（单位：秒）

long_query_time = 1

记录未使用索引的查询

log_queries_not_using_indexes = 1

 限制慢查询日志大小（避免磁盘占满）

max_slowlog_size = 100M

 慢查询日志输出方式

log_output = FILE

修改配置后需要重启 MySQL 服务：

 Linux

sudo systemctl restart mysql

 Windows

net stop mysql

net start mysql

三、分析慢查询日志

1. 直接查看日志内容

慢查询日志是文本格式，可以直接用文本编辑器或命令行工具查看。

 查看慢查询日志

tail -f /var/lib/mysql/mysql-slow.log

 查找最慢的查询

grep "Query_time" /var/lib/mysql/mysql-slow.log | sort -nr | head -10

2. 使用 mysqldumpslow 工具分析

MySQL 自带的分析工具，可以汇总慢查询信息。

 分析慢查询日志（按执行时间排序）

mysqldumpslow -s t /var/lib/mysql/mysql-slow.log

 显示最慢的10条查询

mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log

 按出现次数排序

mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log

 分析特定数据库的慢查询

mysqldumpslow -s t -g "database_name" /var/lib/mysql/mysql-slow.log

3. 使用 pt-query-digest 工具（推荐）

Percona Toolkit 中的高级分析工具，功能更强大。

 安装后使用

pt-query-digest /var/lib/mysql/mysql-slow.log

 输出分析报告到文件

pt-query-digest /var/lib/mysql/mysql-slow.log > slow_report.txt

四、慢查询日志内容解读

日志中的典型条目包含以下信息：

 Time: 2023-10-27T08:15:32.123456Z

 User@Host: root[root] @ localhost []  Id:   123

 Query_time: 5.123456  Lock_time: 0.001234 Rows_sent: 100  Rows_examined: 1000000

SET timestamp=1698394532;

SELECT * FROM large_table WHERE unindexed_column = 'value';

关键字段说明：

 `Query_time`：SQL 执行总时间（重点关注的指标）

 `Lock_time`：等待锁的时间

 `Rows_sent`：返回给客户端的行数

 `Rows_examined`：服务器扫描的行数（扫描行数远大于返回行数通常意味着索引问题）

 `timestamp`：查询执行的时间点

五、日志维护与管理

1. 日志轮询与删除

 使用 mysqladmin 刷新日志（创建新日志文件）

mysqladmin -u root -p flush-logs

 手动备份并删除旧日志

mv /var/lib/mysql/mysql-slow.log /var/lib/mysql/mysql-slow.log.old

mysqladmin -u root -p flush-logs

2. 自动日志清理

将以下命令加入定时任务（crontab）：

 每周清理一次30天前的慢查询日志

find /var/lib/mysql/ -name "mysql-slow.log.*" -mtime +30 -delete

六、最佳实践与注意事项

1. 生产环境谨慎开启：慢查询日志会对性能有轻微影响（约1-3%），建议在需要诊断时开启。

2. 设置合理的阈值：

    开发环境：0.1-0.5秒

    生产环境：1-2秒

3. 定期分析：建立定期分析慢查询日志的制度，每周或每月分析一次。

4. 关注重点：优先优化执行次数多、执行时间长的"热点"SQL。

5. 结合 EXPLAIN 使用：找到慢查询后，使用 `EXPLAIN` 分析其执行计划。

6. 日志文件大小监控：避免日志文件过大占用磁盘空间。

示例工作流程：

 1. 临时开启慢查询日志

SET GLOBAL slow_query_log = 'ON';

SET GLOBAL long_query_time = 1;

 2. 重现问题场景（运行应用程序）

 3. 分析慢查询日志

 使用 mysqldumpslow 或 pt-query-digest 分析

 4. 对找到的慢SQL使用EXPLAIN分析

EXPLAIN SELECT * FROM large_table WHERE unindexed_column = 'value';

 5. 添加索引优化

ALTER TABLE large_table ADD INDEX idx_unindexed_column (unindexed_column);

 6. 验证优化效果后关闭慢查询日志（生产环境）

SET GLOBAL slow_query_log = 'OFF';