慢查询SQL排查

阅读最近更新：https://www.caituotuo.top/c56bd0c5.html

---

0. 前言

假设一次执行20条SQL，我们如何判断哪条SQL是执行慢的烂SQL，这里就需要用到慢查询日志。

在SQL中，广义的查询就是crud操作，而狭义的查询仅仅是select查询操作，慢查询指的是广义的查询，包括增删改查，一般是查询，所以称为慢查询。

1. 什么是慢查询日志？

MySQL提供的一种日志记录，用于记录MySQL中响应时间超过阈值[yù zhí]的SQL语句（long_query_time，默认10秒）

慢查询日志默认是关闭的，开启会消耗一定的性能，一般是开发调优时打开，而部署时会关闭。

2. 检查是否开启了慢查询日志

执行语句：

show variables like '%slow_query_log';

默认是OFF关闭状态

3. 如何开启慢查询日志？

有两种开启方式，临时开启和永久开启。

3.1 临时开启

一般使用临时开启，即在内存中开启，MySQL退出就自动关闭。

set global slow_query_log = 1; 
set global slow_query_log_file="D:/MySQL Server 5.5/slow_query_log.log";

临时开启，重启MySQL服务会变成OFF状态

Linux：

service mysql restart

Windows：

右击开始菜单——计算机管理——找到MySQL服务——重启：

重启后的可以看到变成关闭状态：

3.2 永久开启

修改配置文件

Linux：/etc/my.cnf 中追加配置

vi /etc/my.cnf

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow-query-log.log

Windows：D:\MySQL Server 5.5\my.ini

[mysqld]
slow_query_log=1
slow_query_log_file="D:/MySQL Server 5.5/slow_query_log.log"

4. 慢查询阈值修改

4.1 查看默认值

show variables like '%long_query_time%';

4.2 临时设置阈值

设置完毕后，需要重新登录MySQL才能生效（注意是重新登录MySQL，不是重启MySQL服务）

set global long_query_time = 5;

4.3 永久设置阈值

配置文件，修改完成后需要重启MySQL服务

[mysqld]
long_query_time=3

5. 慢SQL排查测试

5.1 模拟慢SQL数据

执行如下SQL语句休眠4秒，模拟慢SQL：

select sleep(4);

查询超过阈值的SQL的数量：

show global status like '%slow_queries%';

5.2 查询超过阈值的具体SQL语句

1. 通过查看日志文件，也就是前面设置的slow_query_log_file：

2. 通过mysqldumpslow工具查看慢SQL

作用：假设执行了1000条SQL，其中有30条SQL都超过了阈值，如果直接查看日志文件，无法快速定位到具体的SQL，因此mysqldumpslow可以通过一些过滤条件，快速查找出需要定位的慢SQL。

Linux：

mysqldumpslow命令

查看帮助文档：

mysqldumpslow --help

-s ORDER排序   what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count（访问次数）
                 l: lock time（锁的时间）
                 r: rows sent（返回的记录数）
                 t: query time（查询时间）
-r：逆序 reverse the sort order (largest last instead of first)
-l：锁定时间 don't subtract lock time from total time
-g：后面跟一个正则匹配模式，大小写不敏感
-t：top n，即为返回前面多少条的数据

例子：

• 获取返回记录最多的3个SQL

  mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow-query-log.log
  

• 获取访问次数最多的3个SQL

  mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow-query-log.log
  

• 按照时间排序，前10条包含left join查询语句的SQL

  mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow-query-log.log
  

Windows：

MySQL安装后以后在bin下有mysqldumpslow.pl文件，如果没有可以自行下载

D:\MySQL Server 5.5\bin\mysqldumpslow.pl

mysqldumpslow是一个perl脚本，要想在Windows执行，首先需要安装Perl

安装过程很简单，从官网 http://strawberryperl.com/ 下载windows安装包，安装完成后通过perl -v命令测试是否安装成功，如果能显示版本号，表示安装成功。

百度网盘：

链接：https://pan.baidu.com/s/1MiJ3FNUGEoSE1U6dJzOsAg
提取码：slt7

查看帮助文档：

perl mysqldumpslow.pl --help

例子：

• 获取返回记录最多的3个SQL

  perl mysqldumpslow.pl -s r -t 3 "D:/MySQL Server 5.5/slow_query_log.log"
  

• 获取访问次数最多的3个SQL

  perl mysqldumpslow.pl -s c -t 3 "D:/MySQL Server 5.5/slow_query_log.log"
  

• 按照时间排序，前10条包含left join查询语句的SQL

  perl mysqldumpslow.pl -s t -t 10 -g "left join" "D:/MySQL Server 5.5/slow_query_log.log"