一、何为慢操作(慢查询)?
慢查询是指使用时间较长的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:\wamp\bin\mysql\mysql5.5.28\mylog\slowquery.log"long_query_time=1log-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: 2SET 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:慢操作日志文件
./mysqldumpslow -t 10 ../logs/slow_log.log
结果类似
例2:查询执行次数最多的10个操作:
./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
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:查询的结果大小