MySQL慢查询分析工具之mysqldumpslow（转载）
mysqldumpslow工具
https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
mysqldumpslow -s at -t 5 mysql_slowlog.log
开启慢查询
查看慢查询相关参数
slow_query_log：慢查询是否开启
slow_query_log_file：慢查询文件位置
long_query_time：超过多长时间记录
log_queries_not_using_indexes：没有使用索引的sql
mysql> show variables like 'slow_query%';
+---------------------+----------------------------+
| Variable_name | Value |
+---------------------+----------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/db-slow.log |
+---------------------+----------------------------+
2 rows in set (0.01 sec)
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> show variables like 'log_queries_not%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
设置方法
方法1：全局变量设置
mysql> set global slow_query_log='ON';
mysql> set global slow_query_log_file='/var/lib/mysql/db-slow.log';
mysql> set global long_query_time=2;
mysql> set global log_queries_not_using_indexes=1;
方法2：配置文件设置
修改配置文件my.cnf在[mysqld]下加入如下内容
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/db-slow.log
long_query_time = 2
log_queries_not_using_indexes=1
修改后重启mysql服务
测试
mysql> select sleep(3);
[root@db ~]# tail -f /var/lib/mysql/db-slow.log
# Time: 2019-12-25T20:08:48.199670Z
# User@Host: root[root] @ localhost [] Id: 2443797
# Query_time: 3.000198 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=153892974;
select sleep(3);
[root@db ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
常用参数
-s 排序方式 后面跟排序列
al 平均锁定时间
ar 平均返回记录时间
at 平均查询时间（默认）
c 计数
l 锁定时间
r 返回记录
t 查询时间
-r 反向排序，最大的在最后
-t 后面跟数字，返回行数
-a 不将所有的数字抽象为N，字符串抽象为S
-n 后面跟数字，在名称中至少有n个数字抽象为数字
-g 正则后边可以写一个正则匹配模式，大小写不敏感的
案例：
1.根据计数返回5条记录
mysqldumpslow -s c -t 5 /var/lib/mysql/db-slow.log
2.根据返回记录返回5条记录
mysqldumpslow -s r -t 5 /var/lib/mysql/db-slow.log
3.根据查询时间返回5条记录
mysqldumpslow -s t -t 5 /var/lib/mysql/db-slow.log
4.根据计数统计，不抽象数字及字符串
mysqldumpslow -s c -a -t 5 /var/lib/mysql/db-slow.log
得到返回记录最多的20个sql
mysqldumpslow -s r -t 20 sqlslow.log
得到平均访问次数最多的20条sql
mysqldumpslow -s ar -t 20 sqlslow.log
得到平均访问次数最多,并且里面含有ttt字符的20条sql
mysqldumpslow -s ar -t 20 -g "ttt" sqldlow.log
注：
1、如果出现 -bash: mysqldumpslow: command not found 错误，请执行
ln -s /usr/local/mysql/bin/mysqldumpslow /usr/bin
2、如果出现如下错误，Died at /usr/bin/mysqldumpslow line 161, <> chunk 405659.说明你要分析的sql日志太大了，请拆分后再分析
拆分的命令为：
tail -100000 mysql-slow.log>mysql-slow.20180725.log
-- 取出执行次数最多的前5条SQL
[root@localhost log]# /software/bin/mysqldumpslow -s c -t 5 slow_query.log |more
Reading mysql slow query log from slow_query.log
Count: 2 Time=43.54s (87s) Lock=0.00s (0s) Rows=0.0 (0), system[system]@localhost
lock table t10 write
Count: 1 Time=14.53s (14s) Lock=0.00s (0s) Rows=73088.0 (73088), system[system]@localhost
SELECT /*!N SQL_NO_CACHE */ * FROM `t_idb_big`
Count: 1 Time=12.22s (12s) Lock=0.00s (0s) Rows=0.0 (0), system[system]@localhost
INSERT INTO `t_idb_big` VALUES ('S','S','S','S',N,'S','S','S',NULL,NULL,N,N,NULL,NULL,'S','S','S','S','S',N)
--取出耗时最长的前10条慢SQL
mysqldumpslow -s t -t 10 slow.log
https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
The MySQL slow query log contains information about queries that take a long time to execute (see Section 5.4.5, “The Slow Query Log”). mysqldumpslow parses MySQL slow query log files and summarizes their contents.
Normally, mysqldumpslow groups queries that are similar except for the particular values of number and string data values. It “ abstracts” these values to N and 'S' when displaying summary output. To modify value abstracting behavior, use the -a and -n options.
Invoke mysqldumpslow like this:
shell> mysqldumpslow [options] [log_file ...]
Example of usage:
shell> mysqldumpslowReading mysql slow query log from /usr/local/mysql/data/mysqld57-slow.log
Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1
Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1 limit N
Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t1 select * from t1
mysqldumpslow supports the following options.
Table 4.23 mysqldumpslow Options
Option Name Description
-a Do not abstract all numbers to N and strings to 'S'
-n Abstract numbers with at least the specified digits
--debug Write debugging information
-g Only consider statements that match the pattern
--help Display help message and exit
-h Host name of the server in the log file name
-i Name of the server instance
-l Do not subtract lock time from total time
-r Reverse the sort order
-s How to sort output
-t Display only first num queries
--verbose Verbose mode
--help
Display a help message and exit.
-a
Do not abstract all numbers to N and strings to 'S'.
--debug, -d
Run in debug mode.
This option is available only if MySQL was built using WITH_DEBUG. MySQL release binaries provided by Oracle are not built using this option.
-g pattern
Consider only queries that match the ( grep-style) pattern.
-h host_name
Host name of MySQL server for *-slow.log file name. The value can contain a wildcard. The default is * (match all).
-i name
Name of server instance (if using mysql.server startup script).
-l
Do not subtract lock time from total time.
-n N
Abstract numbers with at least N digits within names.
-r
Reverse the sort order.
-s sort_type
How to sort the output. The value of sort_type should be chosen from the following list:
t, at: Sort by query time or average query time
l, al: Sort by lock time or average lock time
r, ar: Sort by rows sent or average rows sent
c: Sort by count
By default, mysqldumpslow sorts by average query time (equivalent to -s at).
-t N
Display only the first N queries in the output.
--verbose, -v
Verbose mode. Print more information about what the program does.