MySQL慢查询日志分析（慢查询日志）

一、背景

   MySQL的慢查询日志是MySQL提供的一种日志记录，他用来记录在MySQL中响应的时间超过阈值的语句，具体指运行时间超过long_query_time（默认是10秒）值的SQL，会被记录到慢查询日志中。
  慢查询日志一般用于性能分析时开启，收集慢SQL然后通过explain进行全面分析，一般来说，生产是不会开启的，收集慢查询语句是十分耗性能的，但是我们在测试环境是可以模拟分析的，优化SQL语句，接下来就通过实战，讲解如何开启慢日志查询以及如何分析。

查看慢查询日志是否开启

show variables like 'slow%';

可以看到默认慢日志的保存文件以及状态 ，OFF说明没有开启

查看默认慢查询sql默认时间，大于10秒，没有等于

select @@global.long_query_time;
-- 或者
show VARIABLES like 'long_query_time%'

二、慢查询日志开启

方式打开（重启后失效）

-- 开启慢日志
set @@global.slow_query_log=1;
-- 设置sql查询超时时间
set @@global.long_query_time=2;
-- 或者
-- 开启慢日志
set GLOBAL slow_query_log=1;
-- 设置sql查询超时时间
set GLOBAL long_query_time=2;

配置文件方式打开（永久）, 重启有效

linux ：vi /etc/my.cnf

windows：

[mysqld]
slow_query_log=1
long_query_time=2

客户端模拟，查询超时，睡眠4秒，由于我们上面设置了查询超过2s保存慢日志

select sleep(4);

只要是超过long_query_time的sql都会被记录

查看当前数据有多少条慢sql

show GLOBAL  STATUS like '%slow_queries%'

慢日志分析工具：mysqldumpslow，这个大家有兴趣的可以去详细了解如何使用。

指定日志文件，查看记录集中最多的sql
linux

mysqldumpslow -s r -t 10 /var/lib/mysql/dca928abb464-slow.log

windows

先下载mysqldumpslow.pl文件

再下载运行工具Perl

之后可以使用cmd根据perl工具调用mysqldumpslow.pl中的命令运行mysqldumpslow

运行格式：

perl mysqldumpslow.pl文件路径 mysqldumpslow命令 慢日志文件路径

例如：

D:\environment>perl D:\mysql\bin\mysqldumpslow.pl mysqldumpslow --help

使用方法：mysqldumpslow [ 选项... ] [ 日志文件... ]

解析并总结 MySQL 慢查询日志。可用的选项有：

  --verbose    详细模式
  --debug      调试模式
  --help       输出此文本到标准输出

  -v           详细模式
  -d           调试模式
  -s ORDER     排序方式（al, at, ar, c, l, r, t），默认为 'at'
                al: 平均锁定时间
                ar: 平均发送行数
                at: 平均查询时间
                 c: 查询计数
                 l: 锁定时间
                 r: 发送行数
                 t: 查询时间
  -r           反转排序顺序（从最大到最小，而非最小到最大）
  -t NUM       仅显示前 n 条查询
  -a           不将所有数字抽象为 N，字符串抽象为 'S'
  -n NUM       在名称中抽象至少具有 n 位数字的数字
  -g PATTERN   grep：仅考虑包含此字符串的语句
  -h HOSTNAME  数据库服务器的主机名，用于匹配 *-slow.log 文件名（可以使用通配符），默认为 '*'，即匹配所有
  -i NAME      服务器实例名称（如果使用 mysql.server 启动脚本）
  -l           不从总时间中减去锁定时间

mysqldumpslow命令使用例子：
得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 var/lib/mysql/zhubayi-computer-slow.log

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

mysqldumpslow -s c -t 10 /var/lib/mysql/zhubayi-computer-slow.log

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

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

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

mysqldumpslow -s r -t 10 var/lib/mysql/zhubayi-computer-slow.log.log  more