mysql慢查询功能介绍及其相关工具的使用

【Backgroud】
在以MySQL为数据库的应用系统中，可以让MySQL记录下超过指定时间的SQL语句，这些SQL语句查询称为“慢查询”。开发者在掌握了这些慢查询的SQL语句后，可以根据自己的需求进行优化，从而提高整个系统的性能。

【Proposal】

 

（1）mysql自带的慢查询分析工具mysqldumpslow

①设置慢查询的指定时间
mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.500000 |
+-----------------+----------+
1 row in set (0.02 sec)

由上面的查询结果可知，当前long_query_time=0.5。也就是受执行时间超过0.5秒的都算慢查询。

如果根据需求，应该设置执行时间超过1秒的才算慢查询，那么应该执行下面的设置：
mysql> set long_query_time = 1;

②开启慢查询功能
mysql> show variables like 'slow_query%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | ON                           |
| slow_query_log_file | /var/lib/mysql/rdb4-slow.log |
+---------------------+------------------------------+
2 rows in set (0.01 sec)

设置参数slow_query_log的值为ON时，开启MySQL的慢查询分析记录功能；
参数slow_query_log_file的值是慢查询日志存放路径；

#在MySQL的配置文件/etc/my.cnf中也可以同样可以设置参数long_query_time和slow_query_log_file的值。
这种配置方法和前面提到的在mysql客户端中设置的不同点是，通过/etc/my.cnf来配置时，这种有效性是永久的；而通过mysql客户端设置时，是即时性的；

③执行慢查询日志分析
具体举例如下：
找出应用程序中使用次数最多的前两个慢查询SQL语句。
[root@rdb4 db]# mysqldumpslow -s c -t 2 /var/lib/mysql/rdb4-slow.log
Reading mysql slow query log from /var/lib/mysql/rdb4-slow.log
Count: 28  Time=1.62s (45s)  Lock=0.00s (0s)  Rows=1.0 (28), root[root]@rap1
  SELECT * FROM contents WHERE id = N

Count: 13  Time=0.83s (10s)  Lock=0.02s (0s)  Rows=1.0 (13), root[root]@2hosts
  SELECT * FROM `accounts` WHERE (identity_url='S' and status!='S' and status!='S')  LIMIT N

常用的几个参数大概意思如下：
-s，是order的顺序。
还有c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时表示倒叙。
-t，是top n的意思，即为返回前面多少条的数据。
-g，后边可以写一个正则匹配模式，大小写不敏感的。

再举几个例子：
mysqldumpslow -s c -t 20 /var/lib/mysql/rdb4-slow.log
mysqldumpslow -s r -t 20 /var/lib/mysql/rdb4-slow.log
上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。

mysqldumpslow -t 10 -s t -g “left join” host-slow.log

这个是按照时间返回前10条里面含有左连接的sql语句。

#还有很多参数的应用，具体可以参考mysqldumpslow -help

 

（2）其他几款常用的慢查询分析工具介绍

①mysqlsla
hackmysql.com推出的一款日志分析工具(该网站还维护了 mysqlreport, mysqlidxchk 等比较实用的mysql工具)
a)下载地址：http://hackmysql.com/mysqlsla

b)linux安装方法：
   tar xzvf mysqlsla-2.03.tar.gz
   perl Makefile.PL
   make
   make install

c)简介
整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等.

格式说明如下:
总查询次数 (queries total), 去重后的sql数量 (unique)
输出报表的内容排序(sorted by)
最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数.

Count, sql的执行次数及占总的slow log数量的百分比.
Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.
95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.
Lock Time, 等待锁的时间.
95% of Lock , 95%的慢sql等待锁时间.
Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.
Rows examined, 扫描的行数量.
Database, 属于哪个数据库
Users, 哪个用户,IP, 占到所有用户执行的sql百分比

Query abstract, 抽象后的sql语句
Query sample, sql语句

除了以上的输出, 官方还提供了很多定制化参数, 是一款不可多得的好工具。

②mysql-explain-slow-log, 德国人写的一个perl脚本。
http://www.willamowius.de/mysql-tools.html

功能上有点瑕疵, 不仅把所有的 slow log 打印到屏幕上, 而且统计也只有数量而已。 不推荐使用。

③mysql-log-filter, google code上找到的一个分析工具.提供了 python 和 php 两种可执行的脚本。
http://code.google.com/p/mysql-log-filter/

能上比官方的mysqldumpslow, 多了查询时间的统计信息(平均,最大, 累计), 其他功能都与 mysqldumpslow类似。
特色功能除了统计信息外, 还针对输出内容做了排版和格式化, 保证整体输出的简洁. 喜欢简洁报表的朋友, 推荐使用一下。

④myprofi, 纯php写的一个开源分析工具.项目在 sourceforge 上。
http://myprofi.sourceforge.net/

功能上, 列出了总的慢查询次数和类型, 去重后的sql语句, 执行次数及其占总的slow log数量的百分比.
从整体输出样式来看, 比mysql-log-filter还要简洁. 省去了很多不必要的内容. 对于只想看sql语句及执行次数的用户来说, 比较推荐。

（3）以上几款慢查询分析工具比较
总结
工具/功能                         一般统计信息      高级统计信息     脚本                   优势
mysqldumpslow (推荐)   支持                   不支持              perl                   mysql官方自带
mysqlsla (推荐)               支持                   支持                perl                   功能强大,数据报表齐全,定制化能力强.
mysql-explain-slow-log  支持                   不支持             perl                   无
mysql-log-filter               支持                   部分支持          python or php   不失功能的前提下,保持输出简洁
myprofi                           支持                   不支持             php                   非常精简