MySQL慢查日志

监控系统监控到我们的程序变慢了，怀疑是sql的原因，要怎么去分析排查呢？一般按照如下几个步骤进行：

• 开启慢查询日志，设置阀值，将慢sql从日志中抓取出来；
• 分析慢sql，查看其执行计划；
• show profile，查询sql的执行细节和生命周期情况;
• MySQL数据库参数调优。

本文先来讲讲慢查日志。

1. 是什么？

MySQL慢查日志是一种记录执行很慢的sql的日志。MySQL中可以通过long_query_time来设置一个阀值，如果执行时间超过这个阀值，就会被认为是慢sql，就会记录到慢查日志中。该阀值默认是10s，即执行超过10s的sql就会被记录。

2. 怎么用？

• 开启方法：

MySQL默认是没有开启慢查日志的，如果不是调优需要的话，不建议开启，因为这个也会带来性能的影响。执行以下sql可以查询是否开启慢查日志以及慢查日志的路径：

show variables like '%slow_query_log%';

执行结果

可以看到默认是off的。执行以下sql即可开启：

set global slow_query_log = 1;

执行结果

可以看到，已经变成on了，也就是开启了。不过这样设置并不是永久生效，重启后就失效了，如果要永久生效，就得修改MySQL的配置文件my.cnf，在[mysqld]下增加如下配置：

slow_query_log=1
slow_query_log_file=/var/lib/mysql/sql-slow.log
long_query_time=3
log_output=FILE

增加了这两行配置后，重启MySQL就可以了。

• 修改阀值：

执行如下sql可以查看当前设置的阀值：

show variables like 'long_query_time';

执行结果

可以看到，当前设置的是10秒。要大于10s才会触发，等于是不会触发的。但是10s这个阀值太大了，我们一般会改小一点，通过以下sql可以修改该值：

set long_query_time = 3;

执行结果

那么我们要怎么验证修改是否生效呢？也就是超过3s的sql到底有没有被记录到慢查日志中呢？执行如下sql：

select sleep(4);

这条sql就要睡4秒之后才会执行，所以最少都要执行4秒多，我们设置的阀值是3秒，所以肯定会被记录。

刚才查看了知道慢查日志名字叫localhost-slow.log，那么到底在哪个目录下？可以通过执行如下命令来查找：

find / -name "localhost-slow.log"

执行结果

我们cat一下这个文件，看看刚刚执行的select sleep(4)到底有没有被记录，结果如下：

执行结果

可以看到，确实生效了，sql被记录到了慢查日志中。

我们还可以通过执行如下语句，来查询当前有多少条慢sql：

show global status like "%Slow_queries%";

执行结果

• 日志分析工具：mysqldumpslow

生产环境可能会出现很多的慢查日志，如果都要手工分析，也是比较费时的，MySQL提供了慢查日志分析工具，mysqldumpslow。

执行如下命令可以查看帮助文档：

mysqldumpslow --help

帮助文档

常见命令如下：

# 得到返回记录集最多的10个sql
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log

# 得到访问次数最多的10个sql
mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log

# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join"  /var/lib/mysql/localhost-slow.log

# 一般执行上面的语句都要和 | more 一起使用，否则爆屏
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log | more