一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第20天，点击查看活动详情

前言

juejin.cn/post/708719… 这篇文章中如何监控慢SQL，当慢查询开启后，慢查询日志记录从开启时间起一直记录，随着时间推移日志文件会越来越大，无法准确的分析，那么有没有专门的分析慢查询分析的工具呢？

常见慢查询分析工具

图片.png

mysqldumpslow

mysqldumpslow慢查询自带的分析慢查询工具，一般只要安装了mysql就会有该工具。

常见参数

mysqldumpslow -help


  -s            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)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

注意：如果提示mysqldumpslow: command not found，解决方案添加软引用

ln -s /usr/local/mysql/bin/mysqldumpslow /usr/bin

参数说明

c: 访问次数

l: 锁定时间

r: 返回记录的行数

t: 查询时间

al:平均锁定时间

ar:平均返回记录的行数

at:平均查询时间

-t  是top n的意思，即为返回前面多少条的数据

-g  后边可以写一个正则匹配模式，大小写不敏感的

常见用法

//查询使用最多的10条语句,输出到文件
mysqldumpslow -s c -t 10 /var/log/slow_query.log  > /data/mysql/slow1.log

//取出查询时间最慢的10条慢查询，输出到文件
mysqldumpslow -s t -t 10 /data/mysql/log/mysqld-slow.log > /data/mysql/slow2.log

//扫描行数最多的,输出到文件
mysqldumpslow -s r -t 10 -g 'left join' /var/log/slow_query.log  > /data/mysql/slow3.log

mysqldumpslow缺点

1.mysqldumpslow的分析结果不会显示具体完整的sql语句,只会显示sql的组成结构

例如：SELECT * FROM t_user where username='zhangsan' GROUP BY oid LIMIT 0, 1000

mysqldumpslow输出信息如下：

Count: 1  Time=1.91s (1s)  Lock=0.00s (0s)  Rows=1000.0 (1000), vgos_dba[vgos_dba]@[10.130.229.196]
SELECT * FROM t_user where username='zhangsan' GROUP BY oid LIMIT N, N;

2.mysqldumpslow不能按照具体时间来统计SQL

mysqldumpslow 只能统计全量的慢SQL日志，无法按照具体的来统计，例如只统计24小时以内的查询使用最多的10条语句。

pt-query-digest

简介

pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

安装

pt-query-digest 是基于perl脚本语言编写的，所以需要安装perl的编译环境。具体步骤如下

下载安装包

cd /usr/local
wget percona.com/get/percona-toolkit.tar.gz

解压

tar zxf percona-toolkit.tar.gz

执行安装命令

cd /usr/local/src/percona-toolkit

perl Makefile.PL

安装过程会会出现如下错误。

1.Can't locate ExtUtils/MakeMaker.pm in @INC

缺少相关的依赖模块，需要进行安装

解决办法：

yum install perl-ExtUtils-MakeMaker
yum -y install perl-CPAN

2.prerequisite DBD::mysql 3 not found

解决办法：

yum install perl-DBD-MySQL

编译

make && make install

执行命令

查询最近12小时以内的慢查询语句

pt-query-digest --since=12h  /data/mysql/slow.log > /usr/local/slowlog/slow_report1.log

执行命令的过程中会出现如下错误：

出现pt-query-digest: command not found 错误，是因为没有找到pt-query-digest，默认情况linux 命令都是从 /usr/bin目录下查找的，我们只需要添加相关软引用即可。

ln -s /usr/local/percona-toolkit/bin/pt-query-digest  /usr/bin/

出现Can't locate Digest/MD5.pm in @INC错误

需要安装依赖模块

yum -y install perl-Digest-MD5

基本语法

--create-review-table  当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。

--create-history-table  当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host  mysql服务器地址
--user  mysql用户名
--password  mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。

基本使用

1.导出所有的慢SQL日志

pt-query-digest  /data/mysql/slow.log  >  /usr/local/slowlog/slow_report0.log

2.分析最近12小时内的查询

pt-query-digest --since=12h  /data/mysql/slow.log > /usr/local/slowlog/slow_report1.log

3.分析指定时间范围内的查询

pt-query-digest  /data/mysql/slow.log --since '2022-04-18 09:30:00' --until '2022-04-18 23:59:59'  > /usr/local/slowlog/slow_report2.log

4.查询所有所有的全表扫描或full join的慢查询

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' /data/mysql/slow.log> /usr/local/slow_report3.log

5.慢SQL输出到表中
  pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_review --create-review-table  /var/log/slow_query.log

上述列举了一些常用的命令，还有其他命令大家可以根据需求自定义分析。

总结

本文详细的讲解了慢SQL查询的分析工具，有了这个工具可以详细进行分析，工具只能为我们发现问题，我们应该从实际的工作中规避问题才是关键，关于Mysql如何优化，后续重点讲解。

mysql之慢查询详解

前言