慢查询日志是 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';
点赞(0)

C语言网提供由在职研发工程师或ACM蓝桥杯竞赛优秀选手录制的视频教程,并配有习题和答疑,点击了解:

一点编程也不会写的:零基础C语言学练课程

解决困扰你多年的C语言疑难杂症特性的C语言进阶课程

从零到写出一个爬虫的Python编程课程

只会语法写不出代码?手把手带你写100个编程真题的编程百练课程

信息学奥赛或C++选手的 必学C++课程

蓝桥杯ACM、信息学奥赛的必学课程:算法竞赛课入门课程

手把手讲解近五年真题的蓝桥杯辅导课程

Dotcpp在线编译      (登录可减少运行等待时间)