MySQL慢查询日志，让“慢”无所遁形

作者：IT邦德
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
（Web\java\Python）工作，主要服务于生产制造
现拥有 Oracle 11g  OCP/OCM、
Mysql、Oceanbase（OBCA）认证
分布式TBase\TDSQL数据库、国产达梦数据库以及红帽子认证
从业8年DBA工作，在数据库领域有丰富的经验

擅长主流数据Oracle、MySQL、PG 运维开发，
备份恢复，安装迁移，性能优化、故障应急处理等。

文章目录

前言

慢查询日志可以帮助 DBA 找出执行效率缓慢的 SQL 语句，为数据库优化工作提供帮助

⛳️ 1.概述

🐴 1.1 功能

MySQL 的慢查询日志是 MySQL 提供的一种日志记录，
它用来记录在 MySQL 中响应时间超过阀值的语句，
具体指 运行时间超过 e long_query_time 的 值的 SQL ，
则会被记录到慢查询日志中。
long_query_time 的默认值为 10，意思是运行 10S 以上的语句。
默认情况下，Mysql 数据库并不启动慢查询日志，
需要我们手动来设置这个参数，
当然，如果不是调优需要的话，一般不建议启动该参数，
因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

🐴 1.2 要点

当查询超过一定时间没有返回结果的时候，才会记录进慢查询日志。
• 慢查询日志可以帮助 DBA 找出执行效率缓慢的 SQL 语句，为数据库优化工作提供帮助。
• 慢查询日志默认是不开启的，建议开启慢查询日志。
• 当需要进行采样分析时手工开启。

mysql> show variables like ‘%long_query_time%’;

⛳️ 2.慢查询日志使用

🐴 2.1 开启日志

关于慢查询日志，主要涉及到下面几个参数：
slow_query_log ：是否开启慢查询日志功能（必填）
long_query_time ：超过设定值，将被视作慢查询，并记录至慢查询日志文件中（必填）
log-slow-queries ：慢查询日志文件（不可选），自动在 \data\ 创建一个 [hostname]-slow.log 文件
也就是说，只有满足以上三个条件，“慢查询功能”才可能正确开启或关闭。

mysql> show variables like ‘%slow_%’;

mysql> set global slow_query_log=1;
mysql> show variables like ‘%slow_%’;

🐴 2.2 日志修改

修改慢查询时长及日志路径方法如下：
修改配置文件my.cnf，在[mysqld]下的下方加入
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
重启MySQL服务service mysqld restart即可
测试慢查询日志：
mysql> select sleep(4);

🐴 2.2 日志记录

我写了几条 SQL 语句，按照预先的设定，查询时间超过 1s 的查询将被写入日志。

SELECT t.* FROM subscribe t limit 0,1;
SELECT t.product, t.vendor,    COUNT(DISTINCT t.id) AS vulcnt FROM    temp t
 WHERE 1 = 1 GROUP BY t.vendor, t.product ORDER BY vulcnt DESC;

第一条 SQL 执行时间 0.001s：

第二条 SQL 执行时间大于 2s：

只有第二条 SQL 被“慢查询日志”记录：

# Time: 170407 14:44:23
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     2
# Query_time: 2.133122  Lock_time: 0.106006 Rows_sent: 23878  Rows_examined: 160312
use testDB;
SET timestamp=1491547463;
SELECT
    t.product,
    t.vendor,
    COUNT(DISTINCT t.id) AS vulcnt
FROM
    temp t
WHERE
    1 = 1
GROUP BY
    t.vendor,
    t.product
ORDER BY
    vulcnt DESC;

⛳️ 3.分析工具mysqldumpslow

我们通过查看慢查询日志可以发现，很乱，数据量大的时候，
可能一天会产生几个G的日志，根本没有办法去清晰明了的分析。
所以，这里，我们采用MySQL自带的慢查询日志分析工具析日志 mysqldumpslow工具进行分析。

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

常用命令
得到返回记录集最多的10个SQL：
mysqldumpslow -s r -t 10 /var/lib/mysql/show.log
得到访问次数最多的10个SQL：
mysqldumpslow -s c -t 10 /var/lig/mysql/show.log
得到按照时间排序的前10条里面含有左连接的查询语句：
mysqldumpslow -s t -t 10 -g “left join” /var/lig/mysql/show.log
另外建议在使用这些命令时结构 | 和more使用，否则有可能出现爆屏情况：
mysqldumpslow -s r -t 10 /var/lig/mysql/show.log | more