mysql 7 慢查询+慢查询工具

一、何为慢操作(慢查询)？

慢查询是指使用时间较长的sql操作，这里的慢是相对的，主要是指超过指定操作时间的sql操作都被称为慢操作。

二、mysql慢操作（慢查询)日志的相关配置

注：以下操作都是基于mysql 5.6(低版本的mysql配置项可能不同)

1、查看是否开启慢查询日志：

show variables like 'slow_query%';

得到结果

• slow_query_log:表示是否开启慢操作日志(ON表示开启，OFF表示关闭)
• slow_query_log_file:表示慢操作日志文件位置

如何开启慢查询日志

开启慢查询日志
set global slow_query_log = 1
 
慢查询日志所存储的文件位置
show variables like 'slow_query_log_file'
设置慢查询日志位置
set global slow_query_log_file = '/var/lib/mysql/iZbp1akzlq26t30fbsdwh7Z-slow.log'
 
是否把没有使用索引的sql记录到慢查询日志当中 PFF关闭 ON开启
show variables like 'log_queries_not_using_indexes'
设置吧没有使用索引的sql记录添加到慢查询日志当中
set global log_queries_not_using_indexes = 1;
 
查看long_query_time设置的时间
show variables like 'long_query_time';
超过多少秒之后的查询记录到慢查询日志当中
set [session|global] long_query_time = 1

也可在mysql安装目录种配置文件种配置

在Mysql配置文件里(windows下My.ini,linux下my.cnf)
里的mysqld的下面添加

log_slow_queries="D:wampinmysqlmysql5.5.28mylogslowquery.log"
long_query_time=1
log-queries-not-using-indexes(如果查询的sql没使用索引(无论表有没有索引),也会被记录)

这儿更多参数可参考上面添加

开启慢查询日志后，最终在sql执行过后，会在相关日志文件（或日志表中记录日志） 慢查询日志格式如下

# Time: 2018-03-17T02:48:42.857232Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 13
# Query_time: 0.000000 Lock_time: 0.000000 Rows_sent: 2 Rows_examined: 2
SET timestamp=1521254922;
SELECT * from store limit 10;

注：

• Time表示操作的时间
• User@Host表示访问的客户端的连接信息
• Query_time表示查询花费的时间
• Lock_time：锁表或锁行的时间
• Rows_sent:表示查询结果行数
• Rows_examined:表示扫描的行数
• SET timestamp表示执行的时间
• 最后一行表示执行的SQL语句

查看慢操作日志，主要是定位哪些操作语句是慢操作

三  、慢操作日志分析工具

1  慢查询日志分析工具  mysqldumpslow 
windows下是一个perl脚本(mysqldumpslow.pl) 需要安装perl

• options:选项
• s ORDER:根据ORDER来对结果进行排序(ORDER可以为al(平均锁表或锁行时间)、ar(平均查询结果)、at(平均查询时间)、c(执行次数)、l(锁表或锁行时间)、r(查询结果行数)、t(查询时间))
• r：倒序排列
• t NUM:显示最大的N行(默认是按查询时间来排序)
• g PATTERN:包含PATTERN这个字符串的
• h HOSTNAME:主机名或者数据库服务器名为HOSTNAME的
• log：慢操作日志文件

1. ./mysqldumpslow -t 10 ../logs/slow_log.log

结果类似

例2：查询执行次数最多的10个操作：

1. ./mysqldumpslow -t 10 -s c ../logs/slow_log.log

结果注解：

• Count:表示执行的次数
• Time:操作消耗的时间
• Lock:锁表或锁行的时间
• Rows:结果的行数
• root[root]@[10.2.84.249]:表示客户端和使用的数据库用户名
• SELECT * ……:表示执行的SQL语句

2  慢查询查看日志工具  pt-query-digest  

第一步：安装pt-query-digest

下载地址：https://www.percona.com/downloads/percona-toolkit

wget https://www.percona.com/downloads/percona-toolkit/2.2.19/deb/percona-toolkit_2.2.19-1.tar.gz
tar -zxvf percona-toolkit_2.2.19-1.tar.gz
mv percona-toolkit_2.2.19 /usr/local/pt-query-digest
ln -s /usr/local/pt-query-digest/bin/pt-query-digest  /usr/local/bin/pt-query-digest

第二步：使用pt-query-digest分析慢操作日志

options:常用的选项

• --host=hostname(-h):连接的数据库主机名
• --port=port(-P)：连接的数据库主机的端口
• --user=username(-u):连接的数据库主机的用户名
• --password=password(-p):连接的数据库主机的密码
• --database=dbname(-D):连接的数据库名称
• --charset=charset:使用的字符编码
• --review:将分析结果保存到表中
• --socket(-S):连接使用的socket文件
• t=tablename:将分析结果保存到数据库表的名称
• --create-review-table:当使用—review参数时，保存结果的表不存在时则创建
• --limit：限制输出结果的百分比或数据
• --since从什么时候开始分析
• --until:与—since一起配合使用，分析一个时间段内的慢操作
• --output:分析结果输出类型(可以为report(标准分析报告)、slowlog、json等)
• log:慢操作日志文件

例：将报告内容保存到另外一个文件：

   pt-query-digest --since=24h /usr/local/mysql/data/mysql_slow.log>>/usr/local/mysql/logs/mysql_slow_today.report

• Overall：慢查询记录数
• unique:独立的查询数(去掉重复的记录)
• QPS:每秒钟处理的查询数
• concurrency:并发量
• Time range:慢查询记录的时间范围
• Execute Time:SQL语句执行时间
• Lock time:锁表时间
• Rows sent:查询结果记录数
• Rows examine:扫描行数
• Query size:查询的结果大小