Mysql 慢查询日志分析方法说明
一、分析sql慢查询的意义
在运营网站的过程中,可能会遇到网站突然变慢的问题,一般情况下和 MySQL 慢有关系,可以通过开启慢查询来记录超过指定时间的SQL语句(目前慢查询时间为2秒),找到影响效率的SQL语句,然后采取相应的措施进行优化,从而提高整个系统的性能。
二、linux下5种分析工具的简要使用说明
以下命令均在centos下执行。
# mysqldumpslow slow_query_9.log (推荐)
# mysqlsla -lt slow slow_query_9.log (强烈推荐)
# php parser.php -slow slow_query_9.log (php强烈推荐)
# ./mysql_explain_slow_log --user=root --password=123456 < slow_query_9.log (不推荐)
# python mysql_filter_slow_log.py --no-duplicates slow_query_9.log (推荐)
三、各种分析工具的详细说明
1、mysqldumpslow
mysql官方提供的慢查询日志分析工具,包含在mysql-server安装包里。
输出样例如下:
格式说明: 统计不同慢sql的
出现次数(Count),
执行最长时间(Time),
累计总耗费时间(Time),
等待锁的时间(Lock),
发送给客户端的行总数(Rows),
扫描的行总数(Rows),
用户以及sql语句本身(抽象了一下格式,比如 limit 1,20 用 limit N,N 表示)。
2、Mysqlsla
hackmysql.com推出的一款日志分析工具(该网站还维护了 mysqlreport, mysqlidxchk 等比较实用的mysql工具)。整体来说,功能非常强大。数据报表,非常有利于分析慢查询的原因,包括执行频率,数据量,查询消耗等。
Linux下安装方法
需要perl-DBI和per-DBD-Mysql两模块的支持,centos下安装mysql自动安装了这2个模块。
# tar xvfz mysqlsla-2.03.tar.gz
# cd mysqlsla-2.03
# perl Makefile.PL
# make
# make install
输出样例如下:
格式说明:
总查询次数 (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语句
除了以上的输出,官方还提供了很多定制化参数,是一款不可多得的好工具。
3、mysql-explain-slow-log
mysql-explain-slow-log(http://www.willamowius.de/mysql-tools.html)
德国人写的一个perl脚本,需要在本机先将慢查询日志文件塞入mysql然后使用收集带有EXPLAIN index使用情况的统计(Feed a mysqld slow log back into mysql and collect statistics about index usage with EXPLAIN.)。功能上有点瑕疵,,不仅把所有的 slow log 打印到屏幕上,而且统计也只有数量而已,不推荐使用。
输出样例如下:
功能上有点瑕疵,不仅把所有的 slow log 打印到屏幕上,而且统计也只有数量而已。不推荐使用。
4、mysql-log-filter
mysql-log-filter(http://code.google.com/p/mysql-log-filter/)
提供python和php两种版本的脚本,python版本要比php版本快3-5倍;还可以指定分析起止时间。
输出样例如下:
功能上比官方的mysqldumpslow,多了查询时间的统计信息(平均,最大, 累计),其他功能都与 mysqldumpslow类似。特色功能除了统计信息外, 还针对输出内容做了排版和格式化, 保证整体输出的简洁。喜欢简洁报表的朋友,推荐使用。
5、Myprofi
纯php写的一个开源分析工具,项目在 sourceforge 上,http://myprofi.sourceforge.net/。
输出样例如下:
格式说明:
功能上, 列出了总的慢查询次数和类型, 去重后的sql语句, 执行次数及其占总的slow log数量的百分比。
从整体输出样式来看,比mysql-log-filter还要简洁。省去了很多不必要的内容。对于只想看sql语句及执行次数的用户来说,强烈推荐。