MySQL慢查询分析工具之mysqldumpslow

https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

开启慢查询

查看慢查询相关参数

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: 2018-12-25T08:00: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=1545724848;

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:

Example of usage:

mysqldumpslow supports the following options.

Table 4.23 mysqldumpslow Options

• --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.