mysqldumpslow – 读懂MySQL慢查询日志
3,235 total views, 2 views today
一、慢查询日志介绍
MySQL中,SQL的执行效率直接影响了MySQL的性能。为了便于DBA及时发现并处理有性能异常的SQL，MySQL专门设立了慢查询日志（slow query log）。SQL执行时间大于long_query_time设定的时候，MySQL就会将这条SQL记录到慢查询日志中。
MySQL慢查询日志既可以保存成文件，又可以保存在MySQL的表中。由于保存在表中，会给数据库带来额外的压力，所以一般都保存在文件中，其保存路径由slow_query_log_file变量决定。
慢查询日志记录了SQL的执行日期时间、SQL查询消耗时间、锁定时间、以及SQL语句。慢查询日志是一个流水账，并不能很好的用于统计分析。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
# Time: 150808 11:48:10 # User@Host: root[root] @ localhost [] # Query_time: 6.847994 Lock_time: 0.000018 Rows_sent: 32260 Rows_examined: 32260 use hccdb; SET timestamp=1439005690; SELECT /*!40001 SQL_NO_CACHE */ * FROM `osdb_third_api_log`; /export1/mysql/bin/mysqld, Version: 5.5.33-log (Source distribution). started with: Tcp port: 3306 Unix socket: /export1/mysql/mysql.sock Time Id Command Argument # Time: 150809 1:10:09 # User@Host: root[root] @ localhost [] # Query_time: 6.980040 Lock_time: 0.000017 Rows_sent: 32416 Rows_examined: 32416 use hccdb; SET timestamp=1439053809; SELECT /*!40001 SQL_NO_CACHE */ * FROM `osdb_third_api_log`; /export1/mysql/bin/mysqld, Version: 5.5.33-log (Source distribution). started with: Tcp port: 3306 Unix socket: /export1/mysql/mysql.sock Time Id Command Argument |
二、mysqldumpslow的用法
所以，为了便于阅读MySQL慢查询日志，MySQL引入了 mysqldumpslow。mysqldumpslow能将相同的慢SQL归类，并统计出相同的SQL执行的次数，每次执行耗时多久、总耗时，每次返回的行数、总行数，以及客户端连接信息等。
mysqldumpslow的用法：
|
1 |
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] |
下面是mysqldumpslow格式化过后的慢查询日志。
其含义是 “SELECT * FROM sdb_site_widgets_instance LIMIT N, N”这条SQL，一共执行了2次；每次执行耗时68.85秒，总耗时137秒；锁定时间0秒；每次返回49.5行，总返回99行；客户端为hccdb[hccdb]@[10.10.6.155] 。
|
1 2 3 4 |
# mysqldumpslow slow.log Reading mysql slow query log from slow.log Count: 2 Time=68.85s (137s) Lock=0.00s (0s) Rows=49.5 (99), hccdb[hccdb]@[10.10.6.155] SELECT * FROM `sdb_site_widgets_instance` LIMIT N, N |
要注意上面SQL语句中的N，下面会提到。
mysqldumpslow的参数说明：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-s ，按照什么方式起来排序。默认at，也就是按照平均查询时间来排序。都是按照倒序排列。 al: average lock time 平均锁定时间 ar: average rows sent 平均返回行数 at: average query time 平均查询时间 c: count 总执行次数 l: lock time 总锁定时间 r: rows sent 总返回行数 t: query time 总查询时间 -t ，show the top n queries，显示前多少名的记录 -a ，默认不开启这个选项。mysqldumpslow将相似的SQL的值（字符串或者数字）替换为N，开启该选项，则显示真实值。不开启该选项，有点类似于Oracle的绑定变量的记录。 -g ，类似于grep命令，过滤出需要的信息。如，只查询A表的慢查询记录。 -l ，总时间中包含锁定时间 |
三、例子
平均执行时间最长的前10条SQL
|
1 |
# mysqldumpslow -s at -t 10 slow-query.log |
平均锁定时间最长的前10条SQL
|
1 |
# mysqldumpslow -s al -t 10 slow-query.log |
执行次数最多的前10条SQL（2017-2-13增加）
|
1 |
# mysqldumpslow -s c -t 10 slow-query.log |
所有和“sdb_image_image”表相关的慢查询SQL
|
1 |
# mysqldumpslow -g 'sdb_image_image' slow.log |
显示SQL的具体值，而不是由N代替。便于查看执行计划并优化
|
1 |
# mysqldumpslow -a slow.log |
显示所有sdb_image_image表相关的慢SQL的具体值
|
1 |
# mysqldumpslow -a -g 'sdb_image_image' slow.log |