
一款超级强大的慢SQL排查工具!
开启慢查询日志
在项目中我们会经常遇到慢查询,当我们遇到慢查询的时候一般都要开启慢查询日志,并且分析慢查询日志,找到慢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为没有启用
可以看到我的没有启用,可以通过如下两种方式开启慢查询
修改配置文件
修改配置文件my.ini,在[mysqld]段落中加入如下参数
「需要重启 MySQL 才可以生效,命令为 service mysqld restart」
设置全局变量
我在命令行中执行如下2句打开慢查询日志,设置超时时间为0.001s,并且将日志记录到文件以及mysql.slow_log表中
「想要永久生效得到配置文件中配置,否则数据库重启后,这些配置失效」
分析慢查询日志
因为mysql慢查询日志相当于是一个流水账,并没有汇总统计的功能,所以我们需要用一些工具来分析一下
mysqldumpslow
mysql内置了mysqldumpslow这个工具来帮我们分析慢查询日志。
常见用法
pt-query-digest
pt-query-digest是我用的最多的一个工具,功能非常强大,可以分析binlog、General log、slowlog,也可以通过show processlist或者通过tcpdump抓取的MySQL协议数据来进行分析。pt-query-digest是一个perl脚本,只需下载并赋权即可执行
下载和赋权
用法介绍
「常用OPTIONS」
「常用DSN」
DSN使用key=value的形式配置;多个DSN使用,分隔
「使用示例」
用法实战
「编写存储过程批量造数据」
在实际工作中没有测试性能,我们经常需要改造大批量的数据,手动插入是不太可能的,这时候就得用到存储过程了
如何定义一个存储过程呢?
举个例子,插入id为1-100000的100000条数据
用Navicat执行
存储过程的三种参数类型
参数类型 | 是否返回 | 作用 |
IN | 否 | 向存储过程传入参数,存储过程中修改该参数的值,不能被返回 |
OUT | 是 | 把存储过程计算的结果放到该参数中,调用者可以得到返回值 |
INOUT | 是 | IN和OUT的结合,即用于存储过程的传入参数,同时又可以把计算结构放到参数中,调用者可以得到返回值 |
用MySQL执行
得用DELIMITER 定义新的结束符,因为默认情况下SQL采用(;)作为结束符,这样当存储过程中的每一句SQL结束之后,采用(;)作为结束符,就相当于告诉MySQL可以执行这一句了。但是存储过程是一个整体,我们不希望SQL逐条执行,而是采用存储过程整段执行的方式,因此我们就需要定义新的DELIMITER ,新的结束符可以用(//)或者($$)
因为上面的代码应该就改为如下这种方式
查询已经定义的存储过程
「开始执行慢sql」
可以执行如下sql查看慢sql的相关信息。
查看一下慢日志存储位置
执行后的文件如下
从最上面的统计sql中就可以看到执行慢的sql
可以看到响应时间,执行次数,每次执行耗时(单位秒),执行的sql
下面就是各个慢sql的详细分析,比如,执行时间,获取锁的时间,执行时间分布,所在的表等信息
「不由得感叹一声,真是神器,查看慢sql超级方便」
最后说一个我遇到的一个有意思的问题,有一段时间线上的接口特别慢,但是我查日志发现sql执行的很快,难道是网络的问题?
为了确定是否是网络的问题,我就用拦截器看了一下接口的执行时间,发现耗时很长,考虑到方法加了事务,难道是事务提交很慢?
于是我用pt-query-digest统计了一下1分钟左右的慢日志,发现事务提交的次很多,但是每次提交事务的平均时长是1.4s左右,果然是事务提交很慢。
事务提交为什么这么慢了?下一篇揭晓
文章转载自公众号:Java识堂
