⼀款超级强⼤的慢SQL排查⼯具！
作者⼁李⽴敏
来源：经授权转⾃ Java识堂（ID：erlieStar）
开启慢查询⽇志
在项⽬中我们会经常遇到慢查询，当我们遇到慢查询的时候⼀般都要开启慢查询⽇志，并且分析慢查询⽇志，到慢sql，然后⽤explain来分析
系统变量
MySQL和慢查询相关的系统变量如下
参数含义
slow_query_log是否启⽤慢查询⽇志， ON为启⽤，OFF为没有启⽤，默认为OFF
log_output⽇志输出位置，默认为FILE，即保存为⽂件，若设置为TABLE，则将⽇志记录到mysql.show_log表中，⽀持设置多种格式
slow_query_log_file指定慢查询⽇志⽂件的路径和名字
long_query_time执⾏时间超过该值才记录到慢查询⽇志，单位为秒，默认为10
执⾏如下语句看是否启⽤慢查询⽇志，ON为启⽤，OFF为没有启⽤
show variables like '%slow_query_log%'
可以看到我的没有启⽤，可以通过如下两种⽅式开启慢查询
修改配置⽂件
修改配置⽂件my.ini，在[mysqld]段落中加⼊如下参数
[mysqld]
log_output='FILE,TABLE'
slow_query_log='ON'
long_query_time=0.001
「需要重启 MySQL 才可以⽣效，命令为 service mysqld restart」
设置全局变量
我在命令⾏中执⾏如下2句打开慢查询⽇志，设置超时时间为0.001s，并且将⽇志记录到⽂件以及mysql.slow_log表中
set global slow_query_log = on;
set global log_output = 'FILE,TABLE';
set global long_query_time = 0.001;
「想要永久⽣效得到配置⽂件中配置，否则数据库重启后，这些配置失效」
分析慢查询⽇志
因为mysql慢查询⽇志相当于是⼀个流⽔账，并没有汇总统计的功能，所以我们需要⽤⼀些⼯具来分析⼀下
mysqldumpslow
mysql内置了mysqldumpslow这个⼯具来帮我们分析慢查询⽇志。
常见⽤法
# 取出使⽤最多的10条慢查询
mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log
# 取出查询时间最慢的3条慢查询
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log
# 得到按照时间排序的前10条⾥⾯含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
pt-query-digest
pt-query-digest是我⽤的最多的⼀个⼯具，功能⾮常强⼤，可以分析binlog、General log、slowlog，也可以通过show processlist或者通过tcpdump抓取的MySQL协议数据来进⾏分析。pt-query-digest是⼀个perl脚本，只需下载并赋权即可执⾏
下载和赋权
wget www.percona/get/pt-query-digest
chmod u+x pt-query-digest
ln -s /opt/soft/pt-query-digest /usr/bin/pt-query-digest
⽤法介绍
// 查看具体使⽤⽅法
pt-query-digest --help
// 使⽤格式
pt-query-digest [OPTIONS] [FILES] [DSN]
「常⽤OPTIONS」
--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可以分析⼀段时间内的慢查询。
「常⽤DSN」
A 指定字符集
D 指定连接的数据库
P 连接数据库端⼝
S 连接Socket file
h 连接数据库主机名
p 连接数据库的密码
t 使⽤--review或--history时把数据存储到哪张表⾥
u 连接数据库⽤户名
DSN使⽤key=value的形式配置；多个DSN使⽤，分隔
「使⽤⽰例」
# 展⽰slow.log中最慢的查询的报表
pt-query-digest slow.log
# 分析最近12⼩时内的查询
pt-query-digest --since=12h slow.log
# 分析指定范围内的查询
pt-query-digest slow.log --since '2020-06-20 00:00:00' --until '2020-06-25 00:00:00'
# 把slow.log中查询保存到query_history表
pt-query-digest --user=root --password=root123 --review h=localhost,D=test,t=query_history --create-review-
table slow.log
# 连上localhost，并读取processlist，输出到slowlog
pt-query-digest --processlist h=localhost --user=root --password=root123 --interval=0.01 --output slowlog
# 利⽤tcpdump获取MySQL协议数据，然后产⽣最慢查询的报表
# tcpdump使⽤说明：blog.csdn/chinaltx/article/details/87469933
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 >
pt-query-digest --type p.txt
# 分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql
# 分析general log
pt-query-digest --type=genlog localhost.log
⽤法实战
「编写存储过程批量造数据」
在实际⼯作中没有测试性能，我们经常需要改造⼤批量的数据，⼿动插⼊是不太可能的，这时候就得⽤到存储过程了
CREATE TABLE `kf_user_info` (
`id` int(11) NOT NULL COMMENT '⽤户id',
`gid` int(11) NOT NULL COMMENT '客服组id',
`name` varchar(25) NOT NULL COMMENT '客服名字'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户信息表';
如何定义⼀个存储过程呢？
CREATE PROCEDURE 存储过程名称 ([参数列表])
mysql连接工具BEGIN
需要执⾏的语句
END
举个例⼦，插⼊id为1-100000的100000条数据
⽤Navicat执⾏
-- 删除之前定义的
DROP PROCEDURE IF EXISTS create_kf;
-- 开始定义
CREATE PROCEDURE create_kf(IN loop_times INT)
BEGIN
DECLARE var INT;
SET var = 1;
WHILE var < loop_times DO
INSERT INTO kf_user_info (`id`,`gid`,`name`)
VALUES (var, 1000, var);
SET var = var + 1;
END WHILE;
END;
-- 调⽤
call create_kf(100000);
存储过程的三种参数类型
参数类型是否
返回
作⽤
IN否向存储过程传⼊参数，存储过程中修改该参数的值，不能被返回OUT是把存储过程计算的结果放到该参数中，调⽤者可以得到返回值
INOUT是IN和OUT的结合，即⽤于存储过程的传⼊参数，同时⼜可以把计算结构放到参数中，调⽤者可以得到返回值
⽤MySQL执⾏
得⽤DELIMITER 定义新的结束符，因为默认情况下SQL采⽤（;）作为结束符，这样当存储过程中的每⼀句SQL结束之后，采⽤（；）作为结束符，就相当于告诉MySQL可以执⾏这⼀句了。但是存储过
程是⼀个整体，我们不希望SQL逐条执⾏，⽽是采⽤存储过程整段执⾏的⽅式，因此我们就需要定义新的DELIMITER ，新的结束符可以⽤（//）或者（$$）
因为上⾯的代码应该就改为如下这种⽅式
DELIMITER //
CREATE PROCEDURE create_kf_kfGroup(IN loop_times INT)
BEGIN
DECLARE var INT;
SET var = 1;
WHILE var <= loop_times DO
INSERT INTO kf_user_info (`id`,`gid`,`name`)
VALUES (var, 1000, var);
SET var = var + 1;
END WHILE;
END //
DELIMITER ;
查询已经定义的存储过程
show procedure status;
「开始执⾏慢sql」
select * from kf_user_info where id = 9999;
select * from kf_user_info where id = 99999;
update kf_user_info set gid = 2000 where id = 8888;
update kf_user_info set gid = 2000 where id = 88888;
可以执⾏如下sql查看慢sql的相关信息。
SELECT * FROM mysql.slow_log order by start_time desc;
查看⼀下慢⽇志存储位置
show variables like 'slow_query_log_file'
pt-query-digest /var/lib/mysql/VM-0-14-centos-slow.log
执⾏后的⽂件如下
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== =================================== ============= ===== ====== ====
# 1 0xE2566F6154AFF41948FE497E53631B43 0.1480 56.1% 4 0.0370 0.00 UPDATE kf_user_info
# 2 0x2DFBC6DBF0D68EF2EC2AE954DC37A1A4 0.1109 42.1% 4 0.0277 0.00 SELECT kf_user_info
# MISC 0xMISC 0.0047 1.8% 2 0.0024 0.0 <2 ITEMS>
从最上⾯的统计sql中就可以看到执⾏慢的sql
可以看到响应时间，执⾏次数，每次执⾏耗时（单位秒），执⾏的sql
下⾯就是各个慢sql的详细分析，⽐如，执⾏时间，获取锁的时间，执⾏时间分布，所在的表等信息
「不由得感叹⼀声，真是神器，查看慢sql超级⽅便」
最后说⼀个我遇到的⼀个有意思的问题，有⼀段时间线上的接⼝特别慢，但是我查⽇志发现sql 执⾏的很快，难道是⽹络的问题？
为了确定是否是⽹络的问题，我就⽤看了⼀下接⼝的执⾏时间，发现耗时很长，考虑到⽅法加了事务，难道是事务提交很慢？
于是我⽤pt-query-digest统计了⼀下1分钟左右的慢⽇志，发现事务提交的次很多，但是每次提交事务的平均时长是1.4s左右，果然是事务提交很慢。
1、活久见！⼀技术总监因“拖延研发进度”被索赔90万
2、微软Windows字体被起诉侵权，⽹友：没想到微软也有翻车的⼀天
3、⼈脸识别⾛光引热议！原来后台能看到的不只有脸……
4、域名这玩意⼉，竟然成了⼩国们的摇钱树？
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论