[Mysql] mysqldumpslow参数详解


本文总阅读量次

1、mysql慢查询日志包含了执行花费较长时间的查询语句信息。

mysqldumpslow解析慢日志文件并打印出内容摘要。
mysqldumpslow进行分组查询，除了有特殊数值和字符串数据值之外。当现实摘要输出是，将数值抽象化为n（数字）和s（字符串）。参数-a和-n可以用于修改抽象化行为。

0、-a 表示不使用抽象的字符串s，数字n替换查询sql语句的内容

不指定-a参数，查询结果中数值和字符串分别用n和s代替
/data/mysql/bin/mysqldumpslow -t 2 /data/mysql/log/slow.log 

1
2
3
4
5
Reading mysql slow query log from /data/mysql/log/slow.log
Count: 7  Time=132.03s (924s)  Lock=94.51s (661s)  Rows=0.0 (0), root[root]@localhost
  UPDATE test SET state = 'S' where state='S' and id in (select id from (select id from test ORDER BY id ASC LIMIT N,N) as tmp)
Count: 7  Time=81.63s (571s)  Lock=75.80s (530s)  Rows=0.0 (0), root[root]@localhost
  UPDATE test SET state = 'S' where state='S' and id >=N and id <= N

添加-a参数，查询结果中数值和字符串用各自的对应值表示，未被抽象化：
/data/mysql/bin/mysqldumpslow -a -t 2 /data/mysql/log/slow.log 

1
2
3
4
5
Reading mysql slow query log from /data/mysql/log/slow.log
Count: 1  Time=244.60s (244s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  UPDATE test SET state = 'CANCELLED' where state='CANCEL' and id in (select id from (select id from test ORDER BY id ASC LIMIT 1,1000000) as tmp)
Count: 1  Time=230.44s (230s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  UPDATE test SET state = 'CANCELLED' where state='CANCEL' and id in (select id from (select id from test ORDER BY id ASC LIMIT 4000000,1000000) as tmp)

2、-n 名称中抽象化数字的最少个数

3、–debug 写入调试信息

4、-g 后面可以写正则表达式匹配，大小写不敏感。根据个人需要，过滤需要的关键字：

/data/mysql/bin/mysqldumpslow -t 5 -l -g ‘test’ /data/mysql/log/slow.log

1
2
3
4
5
6
7
8
9
10
11
Reading mysql slow query log from /data/mysql/log/slow.log
Count: 7  Time=226.54s (1585s)  Lock=94.51s (661s)  Rows=0.0 (0), root[root]@localhost
  UPDATE test SET state = 'S' where state='S' and id in (select id from (select id from test ORDER BY id ASC LIMIT N,N) as tmp)
Count: 7  Time=157.43s (1101s)  Lock=75.80s (530s)  Rows=0.0 (0), root[root]@localhost
  UPDATE test SET state = 'S' where state='S' and id >=N and id <= N
Count: 35  Time=59.29s (2075s)  Lock=0.00s (0s)  Rows=9207179.6 (322251287), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `test`
Count: 1  Time=44.68s (44s)  Lock=0.00s (0s)  Rows=2.0 (2), root[root]@localhost
  select distinct state from test  order by id
Count: 2  Time=27.04s (54s)  Lock=0.00s (0s)  Rows=2.0 (4), root[root]@localhost
  select distinct state from test where id in (select id from (select id from test ORDER BY id ASC LIMIT N,N) as tmp)

5、–help 显示帮助信息并退出

6、-h 日志文件名中服务的主机名

使用-h hostname参数，可使用通配符去过滤慢日志
查看当前目录下的慢日志文件名称：

1
2
3
ll *-slow.log
-rw-r--r-- 1 root  root   583 Sep 12 06:21 10.0.7.4-slow.log
-rw-r----- 1 mysql mysql 1889 Sep 12 05:51 test-slow.log

如果使用-slow.log去过滤慢日志，会把所有-slow.log为后缀的文件都过滤。
mysqldumpslow -a -t 20 -h dax-mysql-slave /data/mysql/log/-slow.log

1
2
3
4
5
6
7
8
9
10
Reading mysql slow query log from /data/mysql/log/10.0.7.4-slow.log /data/mysql/log/test-slow.log
Count: 1  Time=137.68s (137s)  Lock=0.00s (0s)  Rows=5.0 (5), root[root]@localhost
  SELECT * FROM test WHERE trade_asset = 'BCH' AND price_asset = 'ETH' AND broker_id = '1022668762972741633' AND  state in ('WAITING', 'PROCESSING') order by id limit 5
Count: 1  Time=120.01s (120s)  Lock=0.00s (0s)  Rows=15.0 (15), repl[repl]@[10.0.7.51]
  SELECT * FROM test WHERE trade_asset = 'BCH' AND price_asset = 'ETH' AND broker_id = '1022668762972741633' AND  state in ('WAITING', 'PROCESSING') order by id limit 20
Count: 2  Time=90.84s (181s)  Lock=0.00s (0s)  Rows=10.0 (20), 2users@2hosts
  SELECT * FROM test WHERE trade_asset = 'BCH' AND price_asset = 'ETH' AND broker_id = '1022668762972741633' AND  state in ('WAITING', 'PROCESSING') order by id limit 10
Count: 1  Time=82.82s (82s)  Lock=0.00s (0s)  Rows=12.0 (12), repl[repl]@[10.0.7.51]
  SELECT * FROM test WHERE trade_asset = 'BCH' AND price_asset = 'ETH' AND broker_id = '1022668762972741633' AND  state in ('WAITING', 'PROCESSING') order by id limit 12
Died at /data/mysql/bin/mysqldumpslow line 161, <> chunk 5.

也可以指定匹配主机范围：
mysqldumpslow -a -t 20 -h dax-mysql-slave /data/mysql/log/10.0.7.*-slow.log

1
2
3
4
Reading mysql slow query log from /data/mysql/log/10.0.7.4-slow.log
Count: 1  Time=82.82s (82s)  Lock=0.00s (0s)  Rows=12.0 (12), repl[repl]@[10.0.7.51]
  SELECT * FROM test WHERE trade_asset = 'BCH' AND price_asset = 'ETH' AND broker_id = '1022668762972741633' AND  state in ('WAITING', 'PROCESSING') order by id limit 12
Died at /data/mysql/bin/mysqldumpslow line 161, <> chunk 1.

-h 后面如果不加任何参数，会匹配出test-slow.log为日志文件名的日志：
[root@dax-mysql-slave log]# mysqldumpslow -a -t 20 -h /data/mysql/log/*-slow.log

1
2
3
4
5
6
7
8
Reading mysql slow query log from /data/mysql/log/test-slow.log
Count: 1  Time=137.68s (137s)  Lock=0.00s (0s)  Rows=5.0 (5), root[root]@localhost
  SELECT * FROM test WHERE trade_asset = 'BCH' AND price_asset = 'ETH' AND broker_id = '1022668762972741633' AND  state in ('WAITING', 'PROCESSING') order by id limit 5
Count: 1  Time=120.01s (120s)  Lock=0.00s (0s)  Rows=15.0 (15), repl[repl]@[10.0.7.51]
  SELECT * FROM test WHERE trade_asset = 'BCH' AND price_asset = 'ETH' AND broker_id = '1022668762972741633' AND  state in ('WAITING', 'PROCESSING') order by id limit 20
Count: 2  Time=90.84s (181s)  Lock=0.00s (0s)  Rows=10.0 (20), 2users@2hosts
  SELECT * FROM test WHERE trade_asset = 'BCH' AND price_asset = 'ETH' AND broker_id '1022668762972741633' AND  state in ('WAITING', 'PROCESSING') order by id limit 10
Died at /data/mysql/bin/mysqldumpslow line 161, <> chunk 4.

-h 后面不加任何参数，日志文件名使用过滤条件会报错：
[root@dax-mysql-slave log]# mysqldumpslow -a -t 20 -h /data/mysql/log/10.0.7.*-slow.log

1
2
3
Reading mysql slow query log from /data/mysql/data//data/mysql/log/10.0.7.4-slow.log-slow.log
Can't open /data/mysql/data//data/mysql/log/10.0.7.4-slow.log-slow.log: No such file or directory at /data/mysql/bin/mysqldumpslow line 91.
Died at /data/mysql/bin/mysqldumpslow line 161.

7、-i 服务的实例名（官方文档说通过mysql.server启动查看服务实例名，测试之后未找到）

8、-l 不从总时间内减去锁时间

9、-r 反转排序顺序

10、-s 何种方式排序：t，at：按查询时间或平均查询时间排序;l，al：按锁定时间或平均锁定时间排序;r，ar：按发送的行或发送的平均行排序;c：按计数排序;默认情况下，按平均查询时间（相当于-s at）排序。

按总时间排序：
/data/mysql/bin/mysqldumpslow -s t -t 5 -l /data/mysql/log/slow.log 

1
2
3
4
5
6
7
8
9
10
Count: 50831  Time=1.49s (75892s)  Lock=0.00s (4s)  Rows=1.0 (50831), cdax[cdax]@4hosts
  SELECT count(N) FROM test WHERE (trade_asset = 'S' AND price_asset = 'S' AND broker_id = 'S' AND broker_uid = N AND state IN ('S', 'S') AND create_time >= 'S' AND create_time <= 'S')
Count: 12053  Time=1.14s (13735s)  Lock=0.00s (0s)  Rows=1.0 (12053), 2users@5hosts
  SELECT count(N) FROM test WHERE (trade_asset = 'S' AND price_asset = 'S' AND broker_id = 'S' AND broker_uid = N AND state IN ('S', 'S'))
Count: 6674  Time=1.52s (10151s)  Lock=0.00s (0s)  Rows=9.9 (66259), 2users@5hosts
  SELECT id, order_no, broker_id, broker_uid, plat_id, trade_asset, price_asset, trade_type, order_type, price, number, client_order_no, traded_number, over_number, traded_money, fee_asset, fee, broker_fee, cloud_fee, create_time, update_time, state, send_state, error_code, error_msg FROM test WHERE (trade_asset = 'S' AND price_asset = 'S' AND broker_id = 'S' AND broker_uid = N AND state IN ('S', 'S')) order by create_time desc LIMIT N
Count: 84  Time=40.42s (3394s)  Lock=0.00s (0s)  Rows=1.0 (84), 42users@42hosts
  SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM performance_schema.replication_group_members WHERE channel_name = 'S'
Count: 16300  Time=0.20s (3291s)  Lock=0.00s (1s)  Rows=1.0 (16300), cdax[cdax]@[10.1.110.9]
  SELECT count(N) FROM test WHERE (state = 'S' AND send_state = 'S' AND update_time < 'S')

按平均时间排序：
/data/mysql/bin/mysqldumpslow -s at -t 5 -l /data/mysql/log/slow.log 

1
2
3
4
5
6
7
8
9
10
11
Reading mysql slow query log from /data/mysql/log/slow.log
Count: 7  Time=226.54s (1585s)  Lock=94.51s (661s)  Rows=0.0 (0), root[root]@localhost
  UPDATE test SET state = 'S' where state='S' and id in (select id from (select id from test ORDER BY id ASC LIMIT N,N) as tmp)
Count: 7  Time=157.43s (1101s)  Lock=75.80s (530s)  Rows=0.0 (0), root[root]@localhost
  UPDATE test SET state = 'S' where state='S' and id >=N and id <= N
Count: 35  Time=79.40s (2779s)  Lock=0.00s (0s)  Rows=18592104.7 (650723665), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `finance_detail`
Count: 35  Time=59.29s (2075s)  Lock=0.00s (0s)  Rows=9207179.6 (322251287), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `test`
Count: 1  Time=44.68s (44s)  Lock=0.00s (0s)  Rows=2.0 (2), root[root]@localhost
  select distinct state from test  order by id

按锁表时间排序
[root@mw-mysql-2 ~]# /data/mysql/bin/mysqldumpslow -s l -t 5 -l /data/mysql/log/slow.log 

1
2
3
4
5
6
7
8
9
10
11
Reading mysql slow query log from /data/mysql/log/slow.log
Count: 7  Time=226.54s (1585s)  Lock=94.51s (661s)  Rows=0.0 (0), root[root]@localhost
  UPDATE test SET state = 'S' where state='S' and id in (select id from (select id from test ORDER BY id ASC LIMIT N,N) as tmp)
Count: 7  Time=157.43s (1101s)  Lock=75.80s (530s)  Rows=0.0 (0), root[root]@localhost
  UPDATE test SET state = 'S' where state='S' and id >=N and id <= N
Count: 50831  Time=1.49s (75892s)  Lock=0.00s (4s)  Rows=1.0 (50831), cdax[cdax]@4hosts
  SELECT count(N) FROM test WHERE (trade_asset = 'S' AND price_asset = 'S' AND broker_id = 'S' AND broker_uid = N AND state IN ('S', 'S') AND create_time >= 'S' AND create_time <= 'S')
Count: 16300  Time=0.20s (3291s)  Lock=0.00s (1s)  Rows=1.0 (16300), cdax[cdax]@[10.1.110.9]
  SELECT count(N) FROM test WHERE (state = 'S' AND send_state = 'S' AND update_time < 'S')
Count: 12053  Time=1.14s (13735s)  Lock=0.00s (0s)  Rows=1.0 (12053), 2users@5hosts
  SELECT count(N) FROM test WHERE (trade_asset = 'S' AND price_asset = 'S' AND broker_id = 'S' AND broker_uid = N AND state IN ('S', 'S'))

按锁表平均时间排序
[root@mw-mysql-2 ~]# /data/mysql/bin/mysqldumpslow -s al -t 5 -l /data/mysql/log/slow.log 

1
2
3
4
5
6
7
8
9
10
11
Reading mysql slow query log from /data/mysql/log/slow.log
Count: 7  Time=226.54s (1585s)  Lock=94.51s (661s)  Rows=0.0 (0), root[root]@localhost
  UPDATE test SET state = 'S' where state='S' and id in (select id from (select id from test ORDER BY id ASC LIMIT N,N) as tmp)
Count: 7  Time=157.43s (1101s)  Lock=75.80s (530s)  Rows=0.0 (0), root[root]@localhost
  UPDATE test SET state = 'S' where state='S' and id >=N and id <= N
Count: 1  Time=44.68s (44s)  Lock=0.00s (0s)  Rows=2.0 (2), root[root]@localhost
  select distinct state from test  order by id
Count: 1  Time=0.98s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select count(*) from test where broker_id = 'S' AND broker_uid = N
Count: 2  Time=27.04s (54s)  Lock=0.00s (0s)  Rows=2.0 (4), root[root]@localhost
  select distinct state from test where id in (select id from (select id from test ORDER BY id ASC LIMIT N,N) as tmp)

按返回行排序
/data/mysql/bin/mysqldumpslow -s r -t 5 -l /data/mysql/log/slow.log 

1
2
3
4
5
6
7
8
9
10
11
Reading mysql slow query log from /data/mysql/log/slow.log
Count: 35  Time=79.40s (2779s)  Lock=0.00s (0s)  Rows=18592104.7 (650723665), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `finance_detail`
Count: 35  Time=59.29s (2075s)  Lock=0.00s (0s)  Rows=9207179.6 (322251287), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `test`
Count: 4  Time=7.10s (28s)  Lock=0.00s (0s)  Rows=1121601.2 (4486405), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `mq_produce_log_1`
Count: 4  Time=4.29s (17s)  Lock=0.00s (0s)  Rows=1121591.2 (4486365), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `mq_produce_log_2`
Count: 4  Time=4.72s (18s)  Lock=0.00s (0s)  Rows=1121587.5 (4486350), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `mq_produce_log_3`

按平均返回行排序
/data/mysql/bin/mysqldumpslow -s ar -t 5 -l /data/mysql/log/slow.log 

1
2
3
4
5
6
7
8
9
10
11
Reading mysql slow query log from /data/mysql/log/slow.log
Count: 35  Time=79.40s (2779s)  Lock=0.00s (0s)  Rows=18592104.7 (650723665), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `finance_detail`
Count: 35  Time=59.29s (2075s)  Lock=0.00s (0s)  Rows=9207179.6 (322251287), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `test`
Count: 4  Time=7.10s (28s)  Lock=0.00s (0s)  Rows=1121601.2 (4486405), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `mq_produce_log_1`
Count: 4  Time=4.29s (17s)  Lock=0.00s (0s)  Rows=1121591.2 (4486365), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `mq_produce_log_2`
Count: 4  Time=4.72s (18s)  Lock=0.00s (0s)  Rows=1121587.5 (4486350), root[root]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `mq_produce_log_3`

按查询次数排序
/data/mysql/bin/mysqldumpslow -s c -t 5 -l /data/mysql/log/slow.log 

1
2
3
4
5
6
7
8
9
10
11
12
13
Reading mysql slow query log from /data/mysql/log/slow.log
Count: 50831  Time=1.49s (75892s)  Lock=0.00s (4s)  Rows=1.0 (50831), cdax[cdax]@4hosts
  SELECT count(N) FROM test WHERE (trade_asset = 'S' AND price_asset = 'S' AND broker_id = 'S' AND broker_uid = N AND state IN ('S', 'S') AND create_time >= 'S' AND create_time <= 'S')
Count: 16300  Time=0.20s (3291s)  Lock=0.00s (1s)  Rows=1.0 (16300), cdax[cdax]@[10.1.110.9]
  SELECT count(N) FROM test WHERE (state = 'S' AND send_state = 'S' AND update_time < 'S')
Count: 12053  Time=1.14s (13735s)  Lock=0.00s (0s)  Rows=1.0 (12053), 2users@5hosts
  SELECT count(N) FROM test WHERE (trade_asset = 'S' AND price_asset = 'S' AND broker_id = 'S' AND broker_uid = N AND state IN ('S', 'S'))
Count: 6674  Time=1.52s (10151s)  Lock=0.00s (0s)  Rows=9.9 (66259), 2users@5hosts
  SELECT id, order_no, broker_id, broker_uid, plat_id, trade_asset, price_asset, trade_type, order_type, price, number, client_order_no, traded_number, over_number, traded_money, fee_asset, fee, broker_fee, cloud_fee, create_time, update_time, state, send_state, error_code, error_msg FROM test WHERE (trade_asset = 'S' AND price_asset = 'S' AND broker_id = 'S' AND broker_uid = N AND state IN ('S', 'S')) order by create_time desc LIMIT N
Count: 619  Time=1.20s (741s)  Lock=0.00s (0s)  Rows=10.0 (6181), cdax[cdax]@[10.1.110.9]
  SELECT  id,order_no,broker_id,broker_uid,plat_id,trade_asset,price_asset,trade_type,order_type,price,number,client_order_no,traded_number,over_number,traded_money,fee_asset,fee,broker_fee,cloud_fee,create_time,update_time,state,send_state,error_code,error_msg  FROM test  WHERE       (  state = 'S'
  and send_state = 'S'
  and update_time < 'S' ) LIMIT N

添加-r参数对取到值反转排序：
/data/mysql/bin/mysqldumpslow -s c -t 5 -l -r /data/mysql/log/slow.log 

1
2
3
4
5
6
7
8
9
10
11
12
13
Reading mysql slow query log from /data/mysql/log/slow.log
Count: 619  Time=1.20s (741s)  Lock=0.00s (0s)  Rows=10.0 (6181), cdax[cdax]@[10.1.110.9]
  SELECT  id,order_no,broker_id,broker_uid,plat_id,trade_asset,price_asset,trade_type,order_type,price,number,client_order_no,traded_number,over_number,traded_money,fee_asset,fee,broker_fee,cloud_fee,create_time,update_time,state,send_state,error_code,error_msg  FROM test  WHERE       (  state = 'S'
  and send_state = 'S'
  and update_time < 'S' ) LIMIT N
Count: 6674  Time=1.52s (10151s)  Lock=0.00s (0s)  Rows=9.9 (66259), 2users@5hosts
  SELECT id, order_no, broker_id, broker_uid, plat_id, trade_asset, price_asset, trade_type, order_type, price, number, client_order_no, traded_number, over_number, traded_money, fee_asset, fee, broker_fee, cloud_fee, create_time, update_time, state, send_state, error_code, error_msg FROM test WHERE (trade_asset = 'S' AND price_asset = 'S' AND broker_id = 'S' AND broker_uid = N AND state IN ('S', 'S')) order by create_time desc LIMIT N
Count: 12053  Time=1.14s (13735s)  Lock=0.00s (0s)  Rows=1.0 (12053), 2users@5hosts
  SELECT count(N) FROM test WHERE (trade_asset = 'S' AND price_asset = 'S' AND broker_id = 'S' AND broker_uid = N AND state IN ('S', 'S'))
Count: 16300  Time=0.20s (3291s)  Lock=0.00s (1s)  Rows=1.0 (16300), cdax[cdax]@[10.1.110.9]
  SELECT count(N) FROM test WHERE (state = 'S' AND send_state = 'S' AND update_time < 'S')
Count: 50831  Time=1.49s (75892s)  Lock=0.00s (4s)  Rows=1.0 (50831), cdax[cdax]@4hosts
  SELECT count(N) FROM test WHERE (trade_asset = 'S' AND price_asset = 'S' AND broker_id = 'S' AND broker_uid = N AND state IN ('S', 'S') AND create_time >= 'S' AND create_time <= 'S')

11、-t 按指定数字显示行输出

12、–verbose 详细模式

13、如果不小心删掉了slow.log可通过flush-log参数来重新生成慢日志文件（定期做日志切割的时候可能会用到）

1
mysqladmin -u root -ptest flush-logs slow
mysql
目录
  1. 1. 1、mysql慢查询日志包含了执行花费较长时间的查询语句信息。
  2. 2. 0、-a 表示不使用抽象的字符串s，数字n替换查询sql语句的内容
  3. 3. 2、-n 名称中抽象化数字的最少个数
  4. 4. 3、–debug 写入调试信息
  5. 5. 4、-g 后面可以写正则表达式匹配，大小写不敏感。根据个人需要，过滤需要的关键字：
  6. 6. 5、–help 显示帮助信息并退出
  7. 7. 6、-h 日志文件名中服务的主机名
  8. 8. 7、-i 服务的实例名（官方文档说通过mysql.server启动查看服务实例名，测试之后未找到）
  9. 9. 8、-l 不从总时间内减去锁时间
  10. 10. 9、-r 反转排序顺序
  11. 11. 10、-s 何种方式排序：t，at：按查询时间或平均查询时间排序;l，al：按锁定时间或平均锁定时间排序;r，ar：按发送的行或发送的平均行排序;c：按计数排序;默认情况下，按平均查询时间（相当于-s at）排序。
  12. 12. 11、-t 按指定数字显示行输出
  13. 13. 12、–verbose 详细模式
  14. 14. 13、如果不小心删掉了slow.log可通过flush-log参数来重新生成慢日志文件（定期做日志切割的时候可能会用到）

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog