mysql慢查询分析

1.生成实验数据

使用sql蠕虫复制足够多的数据

insert into payment (name,amount,address) select name,amount,addrss from payment

2.慢日志查询设置

（1）开启慢查询日志

set global slow_query_log = on; 
show variables like 'slow_query_log';

设置完毕后，查询是否开启

set global slow_query_log = on; 

 

（2）设置慢查询临界点

set long_query_time = 1;

（3）设置慢查询存储的方式

set globle log_output = file;

（4）查询慢查询的配置信息

show variables like '%quer%';

 

3.慢查询测试

（1）执行sql

select * from payment where payment_id<100000;

（2）查看日志

日志文件通过参数得知在/var/lib/mysql目录中

查看该日志文件

 

可以看到刚才插入数据的sql和查询sql都被记录起来了，且sql执行时间都超过了1s

字段	说明
Query_time	SQL的查询时间
Lock_time	锁定时间
Rows_sent	所发送的行数
Rows_examined	锁扫描的行数
SET timestamp=1565697747	sql执行时间
select * from payment	sql执行语句

4.慢查询日志分析工具

（1）mysqldumpslow

显示出慢查询日志中最慢的10条sql
mysqldumpslow -t 10 /var/lib/mysql/iz2ze6p8tou7n39ta90oziz-slow.log

注意：在mysql数据库所在的服务器上，而不是在mysql>命令行中

（2）pt-query-digest

快速安装

wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum localinstall -y  percona-toolkit-2.2.16-1.noarch.rpm

查看服务器信息

pt-summary

查看磁盘开销使用信息

pt-diskstats

查看mysql数据库信息

pt-mysql-summary --user=root --password=root

分析慢查询日志

pt-query-digest /var/lib/mysql/iz2ze6p8tou7n39ta90oziz-slow.log

查找mysql的从库和同步状态

pt-slave-find --host=localhost --user=root --password=root

查看mysql的死锁信息

pt-deadlock-logger --user=root --password=root localhost

从慢查询日志中分析索引使用情况

pt-index-usage slow_20131009.log

查找数据库表中重复的索引

pt-duplicate-key-checker --host=localhost --user=root --password=root

查看mysql表和文件的当前活动IO开销

pt-ioprofile

pt-find查找数据库里大于2G的表：

pt-find --user=root --password=root --tablesize +2G

5.如何分析

（1）查询次数多且每次查询占用时间长的sql

通常为pt-query-digest分析的前几个查询；该工具可以很清楚的看出每个SQL执行的次数及百分比等信息，执行的次数多，占比比较大的SQL

（2）IO大的sql

注意pt-query-digest分析中的Rows examine项。扫描的行数越多，IO越大。

（3）未命中的索引的SQL

注意pt-query-digest分析中的Rows examine （扫描行数）和Rows Sent（发送行数）的对比。如果扫描行数远远大于发送行数，说明该SQL的索引命中率不高，对于这种SQL，我们要重点进行关注。