慢查询日志是 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';
C语言网提供由在职研发工程师或ACM蓝桥杯竞赛优秀选手录制的视频教程,并配有习题和答疑,点击了解:
一点编程也不会写的:零基础C语言学练课程
解决困扰你多年的C语言疑难杂症特性的C语言进阶课程
从零到写出一个爬虫的Python编程课程
只会语法写不出代码?手把手带你写100个编程真题的编程百练课程
信息学奥赛或C++选手的 必学C++课程
蓝桥杯ACM、信息学奥赛的必学课程:算法竞赛课入门课程
手把手讲解近五年真题的蓝桥杯辅导课程