mysql 慢查询日志文件详解 mysql的慢日志

转载

恋上一只猪 2023-08-20 18:20:53

文章标签 mysql 慢查询日志文件详解 mysql 性能优化数据库慢查询 文章分类 MySQL 数据库

序言

前面讲到了使用 explain 来分析 SQL 语句的性能，那么在真实的生产环境中，如果出现了性能慢的问题，不可能卓条 SQL 语句去分析，这个时候，就需要开启慢查询日志，将影响性能的SQL 语句定位出来，然后再通过执行计划来分析优化相对应的 SQL 语句。

一、什么是慢查询日志？

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

默认情况下，MySQL 数据库没有开启慢查询日志 ，需要我们手动来设置这个参数。

如果不需要调优的话，一般不建议启动该参数，因为开启慢查询日志会或多或少的带来一定的性能影响。慢查询日志支持将日志记录写入到文件中。

二、使用慢查询日志

查看是否开启慢查询日志

show variables like '%slow_query_log%';

mysql 慢查询日志文件详解 mysql的慢日志_慢查询

从图中可以看出，我本地的已经开启了慢查询日志分析，默认情况下，是关闭的 OFF

开启慢查询语句（重启后失效）：

set global slow_query_log=1;

执行此语句命令后，开启慢查询日志功能。注意：只对当前数据库生效，MySQL 重启后会失效。

开启慢查询语句（永久生效）：

#如果要永久生效，需要修改配置文件 my.cnf；
#[mysqld] 下增加或修改参数，slow_query_log 和 slow_query_log_file，重启即可，如下：

#开启慢查询
slow_query_log=1
#慢查询日志存放的位置
slow_query_log_file=/home/services/mysqllog/mysql-slow.log
#规定慢 SQL 的查询阙值，超过这个值将会被记录到慢查询文件中，单位：秒
long_query_time=3
#慢查询日志以文件的形式输出
log_output=FILE

什么样的 SQL 才会被记录到慢查询日志文件中？

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒。
命令: SHOW VARIABLES LIKE ‘long_query_time%’;
可以使用命令修改，也可以在mycnf参数里面修改
假如运行时间正好等于long_querytime的情况，并不会被记录下来。也就是说，在mysql源码里是 判断大于long_query_time，而非大于等于。

如何设置慢的阙值时间？

通过命令： set global long_query_time = 3;

为什么设置后看不出变化？

需要重新连接或新开一个会话，才能看到修改后的值。
使用命令： show global variables like ‘long_query_time’;

记录慢 SQL 并后续分析

根据上面的配置，可以到服务器对应的目录下找到对应的慢查询日志，里面会将查过阙值的慢 SQL 记录到里面去。

查询当前系统中有多少条慢查询记录

查看系统中记录了多少次慢查询的 SQL，值越小说明性能越高；
命令： show global status like ‘%slow_queries%’;

慢查询日志分析工具 - mysqldumpslow

在生产环境中，如果要手动分析日志，查找、分析 SQL，显然是个体力活，MySQL 提供了日志分析工具 – mysqldumpslow

常用命令：
mysqldumpslow -help

-s:表示按照合种方式排序；
-c:访问次数
-l:锁定时间
-r:返回记录
-t:查询时间
-al:平均锁定时间
-ar:平均返回记录数
-at:平均查询时间
-t: 返回前面多少条的数据
-g: 后面搭配一个正则匹配模式，大小写不敏感

工作中常用的语句举例：

# 得到返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /home/services/mysqllog/mysql-slow.log

# 得到访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /home/services/mysqllog/mysql-slow.log

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

# 另外建议再使用这些命令时结合 | 和 more 使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /home/services/mysqllog/mysql-slow.log | more

通常情况，使用慢查询日志即可定位到性能不好的 SQL。下面也可以使用 show profile 进行分析排查。