这篇文章主要介绍“超级强大的慢SQL排查工具有哪些优点”,在日常操作中,相信很多人在超级强大的慢SQL排查工具有哪些优点问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”超级强大的慢SQL排查工具有哪些优点”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!

开启慢查询日志

在项目中我们会经常遇到慢查询,当我们遇到慢查询的时候一般都要开启慢查询日志,并且分析慢查询日志,找到慢sql,然后用explain来分析

系统变量

MySQL和慢查询相关的系统变量如下:

执行如下语句看是否启用慢查询日志,ON为启用,OFF为没有启用

showvariableslike"%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表中

setglobalslow_query_log=on;setgloballog_output='FILE,TABLE';setgloballong_query_time=0.001;

「想要永久生效得到配置文件中配置,否则数据库重启后,这些配置失效」

分析慢查询日志

因为mysql慢查询日志相当于是一个流水账,并没有汇总统计的功能,所以我们需要用一些工具来分析一下

mysqldumpslow

mysql内置了mysqldumpslow这个工具来帮我们分析慢查询日志。

常见用法

#取出使用最多的10条慢查询mysqldumpslow-sc-t10/var/run/mysqld/mysqld-slow.log#取出查询时间最慢的3条慢查询mysqldumpslow-st-t3/var/run/mysqld/mysqld-slow.log#得到按照时间排序的前10条里面含有左连接的查询语句mysqldumpslow-st-t10-g“leftjoin”/database/mysql/slow-logpt-query-digest

pt-query-digest是我用的最多的一个工具,功能非常强大,可以分析binlog、General log、slowlog,也可以通过show processlist或者通过tcpdump抓取的MySQL协议数据来进行分析。pt-query-digest是一个perl脚本,只需下载并赋权即可执行

下载和赋权

wgetwww.percona.com/get/pt-query-digestchmodu+xpt-query-digestln-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%位置截止。--hostmysql服务器地址--usermysql用户名--passwordmysql用户密码--history将分析结果保存到表中,分析结果比较详细,下次再使用--history时,如果存在相同的语句,且查询所在的时间区间和历史表中的不同,则会记录到数据表中,可以通过查询同一CHECKSUM来比较某类型查询的历史变化。--review将分析结果保存到表中,这个分析只是对查询条件进行参数化,一个类型的查询一条记录,比较简单。当下次使用--review时,如果存在相同的语句分析,就不会记录到数据表中。--output分析结果输出类型,值可以是report(标准分析报告)、slowlog(Mysqlslowlog)、json、json-anon,一般使用report,以便于阅读。--since从什么时间开始分析,值为字符串,可以是指定的某个”yyyy-mm-dd[hh:mm:ss]”格式的时间点,也可以是简单的一个时间值:s(秒)、h(小时)、m(分钟)、d(天),如12h就表示从12小时前开始统计。--until截止时间,配合—since可以分析一段时间内的慢查询。

「常用DSN」

A指定字符集D指定连接的数据库P连接数据库端口S连接Socketfileh连接数据库主机名p连接数据库的密码t使用--review或--history时把数据存储到哪张表里u连接数据库用户名

DSN使用key=value的形式配置;多个DSN使用,分隔

「使用示例」

#展示slow.log中最慢的查询的报表pt-query-digestslow.log#分析最近12小时内的查询pt-query-digest--since=12hslow.log#分析指定范围内的查询pt-query-digestslow.log--since'2020-06-2000:00:00'--until'2020-06-2500:00:00'#把slow.log中查询保存到query_history表pt-query-digest--user=root--password=root123--reviewh=localhost,D=test,t=query_history--create-review-tableslow.log#连上localhost,并读取processlist,输出到slowlogpt-query-digest--processlisth=localhost--user=root--password=root123--interval=0.01--outputslowlog#利用tcpdump获取MySQL协议数据,然后产生最慢查询的报表#tcpdump使用说明:https://blog.csdn.net/chinaltx/article/details/87469933tcpdump-s65535-x-nn-q-tttt-iany-c1000port3306>mysql.tcp.txtpt-query-digest--typetcpdumpmysql.tcp.txt#分析binlogmysqlbinlogmysql-bin.000093>mysql-bin000093.sqlpt-query-digest--type=binlogmysql-bin000093.sql#分析generallogpt-query-digest--type=genloglocalhost.log用法实战

「编写存储过程批量造数据」

在实际工作中没有测试性能,我们经常需要改造大批量的数据,手动插入是不太可能的,这时候就得用到存储过程了

CREATETABLE`kf_user_info`(`id`int(11)NOTNULLCOMMENT'用户id',`gid`int(11)NOTNULLCOMMENT'客服组id',`name`varchar(25)NOTNULLCOMMENT'客服名字')ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='客户信息表';

如何定义一个存储过程呢?

CREATEPROCEDURE存储过程名称([参数列表])BEGIN需要执行的语句END

举个例子,插入id为1-100000的100000条数据

用Navicat执行

--删除之前定义的DROPPROCEDUREIFEXISTScreate_kf;--开始定义CREATEPROCEDUREcreate_kf(INloop_timesINT)BEGINDECLAREvarINT;SETvar=1;WHILEvar<loop_timesDOINSERTINTOkf_user_info(`id`,`gid`,`name`)VALUES(var,1000,var);SETvar=var+1;ENDWHILE;END;--调用callcreate_kf(100000);

存储过程的三种参数类型

用MySQL执行

得用DELIMITER 定义新的结束符,因为默认情况下SQL采用(;)作为结束符,这样当存储过程中的每一句SQL结束之后,采用(;)作为结束符,就相当于告诉MySQL可以执行这一句了。但是存储过程是一个整体,我们不希望SQL逐条执行,而是采用存储过程整段执行的方式,因此我们就需要定义新的DELIMITER ,新的结束符可以用(//)或者($$)

因为上面的代码应该就改为如下这种方式

DELIMITER//CREATEPROCEDUREcreate_kf_kfGroup(INloop_timesINT)BEGINDECLAREvarINT;SETvar=1;WHILEvar<=loop_timesDOINSERTINTOkf_user_info(`id`,`gid`,`name`)VALUES(var,1000,var);SETvar=var+1;ENDWHILE;END//DELIMITER;

查询已经定义的存储过程

showprocedurestatus;

「开始执行慢sql」

select*fromkf_user_infowhereid=9999;select*fromkf_user_infowhereid=99999;updatekf_user_infosetgid=2000whereid=8888;updatekf_user_infosetgid=2000whereid=88888;

可以执行如下sql查看慢sql的相关信息。

SELECT*FROMmysql.slow_logorderbystart_timedesc;

查看一下慢日志存储位置

showvariableslike"slow_query_log_file"

pt-query-digest/var/lib/mysql/VM-0-14-centos-slow.log

执行后的文件如下:

#Profile#RankQueryIDResponsetimeCallsR/CallV/M#===================================================================#10xE2566F6154AFF41948FE497E53631B430.148056.1%40.03700.00UPDATEkf_user_info#20x2DFBC6DBF0D68EF2EC2AE954DC37A1A40.110942.1%40.02770.00SELECTkf_user_info#MISC0xMISC0.00471.8%20.00240.0<2ITEMS>

从最上面的统计sql中就可以看到执行慢的sql

可以看到响应时间,执行次数,每次执行耗时(单位秒),执行的sql

下面就是各个慢sql的详细分析,比如,执行时间,获取锁的时间,执行时间分布,所在的表等信息

「不由得感叹一声,真是神器,查看慢sql超级方便」

最后说一个我遇到的一个有意思的问题,有一段时间线上的接口特别慢,但是我查日志发现sql执行的很快,难道是网络的问题?

为了确定是否是网络的问题,我就用拦截器看了一下接口的执行时间,发现耗时很长,考虑到方法加了事务,难道是事务提交很慢?

于是我用pt-query-digest统计了一下1分钟左右的慢日志,发现事务提交的次很多,但是每次提交事务的平均时长是1.4s左右,果然是事务提交很慢。

到此,关于“超级强大的慢SQL排查工具有哪些优点”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注亿速云网站,小编会继续努力为大家带来更多实用的文章!