MySQL的慢查询日志

小天天天天    数据库    999+ 次    2023-03-27 10:48:51


在项目里面,多多少少都隐藏着一些执行比较慢的SQL, 不同的开发测试人员在平时使用的过程中多多少少都能够遇到,但是无法立马有时间去排查解决。那么如果有一个文件能够将这些使用过程中比较慢的SQL记录下来,定期去分析排查,那该多美好啊。这种情况MySQL也替我们想到了,它提供了SQL慢查询的日志,本文就分享下如何使用吧。
什么是慢查询日志?
MySQL的慢询日志,提供了记录在MySQL中响应时间超过指定阈值语句的功能,比如设定阈值为3秒,那么任何SQL执行超过3秒都会被记录下来。
我们借助慢查询日志功能可以发现哪些那些执行时间特别长的询,并且有针对性地进行优化,从而提高系统的整体效率。
怎么开启慢查询日志?
默认情况下,MySQL数据库没有开启慢查询日志,因为多多少少会带来一定性能的影响。我们可以在开发测试环境、或者生产环境做调优的时候开启,那怎么查看是否开启了呢?

查看慢SQL是否开启

执行下面命令查看是否开启慢SQL

show variables like '%slow_query_log';

OFF: 未开启
ON: 开启

如何开启慢查询

执行下面的命令开启慢查询日志

set global slow_query_log='ON';

修改慢查询阈值

前面介绍了SQL执行到达了制定的时间阈值后记录到慢查询日志中,那么如何设置呢?

set global long_query_time = N;

set long_query_time = N

设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句

N表示设置的阈值,单位为秒

这里的show global variables like '%long_query_time%';可以查看阈值大小

如何设置永久生效

前面是通过命令行的方式设置,如果MySQL重启,那么配置就会重置。我们可以通过修改MySQL的配置my.cfg或者my.ini永久生效。

[mysqld]
slow_query_log=ON # 开启慢查询日志开关
slow_query_log_file=/var/lib/mysql/alvin-slow.log # 慢查询日志的目录和文件名信息
long_query_time=3 # 设置慢查询的阈值为3秒,超出此设定值的SQL即被记录到慢查询日志
log_output=FILE

慢查询日志在哪里呢?
前面讲解了如何开启MySQL的慢查询日志,那么它把日志记录在哪里了呢?

查看慢查询日志位置

通过命令可以查看慢SQL文件位置,如下图所示:

show variables like '%slow_query_log_file%';


修改慢查询日志位置

也很简单,执行下面的命令即可:

set global slow_query_log_file = '/usr/local/mysql/data/alvin-slow-slow.log';

查看慢查询数目

执行下面命令查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

value=1表示有一条慢查询日志


查看日志内容

通过cat命令查看文件内容,可以看到对应的慢SQL。

cat /usr/local/mysql/data/alvin-slow-slow.log


慢查询日志分析工具mysqldumpslow

果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具 mysqldumpslow 。
mysqldumpslow 命令的具体参数如下:

-a: 不将数字抽象成N,字符串抽象成S
-s: 是表示按照何种方式排序:

c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间 (默认方式)
ac:平均查询次数

-t: 即为返回前面多少条的数据;
-g: 后边搭配一个正则匹配模式,大小写不敏感的;

可mysqldumpslow位置mysql的bin目录下,以通过执行 mysqldumpslow --help命令查看使用。
举例: 我们想要按照查询时间排序,查看前五条 SQL 语句,这样写即可:

mysqldumpslow -s t -t 5 /usr/local/mysql/data/alvin-slow-slow.log


常见的用法:

得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /usr/local/mysql/data/alvin-slow-slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /usr/local/mysql/data/alvin-slow-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /usr/local/mysql/data/alvin-slow-slow.log

另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现爆屏情况

mysqldumpslow -s r -t 10 /usr/local/mysql/data/alvin-slow-slow.log | more


怎么删除慢SQL日志?

现在慢查询日志很多了啊,为了不互相混淆,我要删除一些慢SQL日志,怎么删除呢?

手动删除慢查询日志文件即可, 也就是rm命令。
使用命令mysqladmin flush-logs 重置慢sql日志内容,完整命令如下 :

mysqladmin -uroot -p flush-logs slow


总结
本文讲解了慢SQL日志该如何打开以及使用,是非常重要有效的排查手段,最后记得在排查完毕以后,要关闭慢SQL日志,不然可能影响性能哦。


如果你觉得本篇文章对您有帮助,请打赏作者

上一篇: 使用Apache ab进行压力测试

下一篇: 为什么 MySQL 不推荐使用 join?

最新评论

暂无评论

热门文章

最新评论

网站数据

网站文章数:483

今日UV/PV/IP:1/1/1

昨日UV/PV/IP:25/31 /25

TOP