mysql优化-(1)-慢查询日志工具-mysqldumpslow

1. mysqldumpslow简介

mysql安装好后自带的, perl工具.

2. 查看命令用法:mysqldumpslow --help

[root@niewj download]# 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

[root@niewj download]#

3. mysqldumpslow参数之-(1): -v或--verbose

打印明细信息

4. mysqldumpslow参数之-(2): -s

al = 平均锁定时长
ar=平均返送的rows数
at=平均query时长
c=sql查询总数(某一条sql查询了几次)
r=返送的rows总数
t=query的时间总数
-t N = 指定只查前N条, 相当于 limit N

al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time

排序参数:

4.1 -s at (按平均的query time)

查询平均耗时最长的慢sql:

mysqldumpslow -v -s at /var/lib/mysql/niewj-slow.log

[root@niewj download]# mysqldumpslow -v -s at  /var/lib/mysql/niewj-slow.log

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log
Count: 2  Time=25.84s (51s)  Lock=0.00s (0s)  Rows=150000.0 (300000), root[root]@[121.69.51.10]
  select * from goods

Count: 3  Time=23.26s (69s)  Lock=0.00s (0s)  Rows=136666.7 (410000), root[root]@[121.69.51.10]
  select * from goods  where   id>N

Count: 6  Time=11.12s (66s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[121.69.51.10]
  call Proc()

Count: 1  Time=11.00s (11s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[121.69.51.10]
  select sleep(N)

Count: 1  Time=4.68s (4s)  Lock=0.00s (0s)  Rows=32262.0 (32262), root[root]@[121.69.51.10]
  select * from goods  where id>N

[root@niewj download]#

可以看到 Time=25.84s 平均时长最长, 排在最前;

4.2 -s t (按照总的query time)

查询哪个sql查询最耗时(算总时长,忽略次数):

mysqldumpslow -v -s t /var/lib/mysql/niewj-slow.log

[root@niewj download]# mysqldumpslow -v -s t  /var/lib/mysql/niewj-slow.log

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log
Count: 3  Time=23.26s (69s)  Lock=0.00s (0s)  Rows=136666.7 (410000), root[root]@[121.69.51.10]
  select * from goods  where   id>N

Count: 6  Time=11.12s (66s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[121.69.51.10]
  call Proc()

Count: 2  Time=25.84s (51s)  Lock=0.00s (0s)  Rows=150000.0 (300000), root[root]@[121.69.51.10]
  select * from goods

Count: 1  Time=11.00s (11s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[121.69.51.10]
  select sleep(N)

Count: 1  Time=4.68s (4s)  Lock=0.00s (0s)  Rows=32262.0 (32262), root[root]@[121.69.51.10]
  select * from goods  where id>N

[root@niewj download]#

可以看到总时长最大的(69s)的排到最前面了;

4.3 -s ar (按照平均返回的rows量)

查询平均每次查询返回条数最多的慢sql:

mysqldumpslow -v -s ar /var/lib/mysql/niewj-slow.log

[root@niewj download]# mysqldumpslow -v -s ar  /var/lib/mysql/niewj-slow.log

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log
Count: 2  Time=25.84s (51s)  Lock=0.00s (0s)  Rows=150000.0 (300000), root[root]@[121.69.51.10]
  select * from goods

Count: 3  Time=23.26s (69s)  Lock=0.00s (0s)  Rows=136666.7 (410000), root[root]@[121.69.51.10]
  select * from goods  where   id>N

Count: 1  Time=4.68s (4s)  Lock=0.00s (0s)  Rows=32262.0 (32262), root[root]@[121.69.51.10]
  select * from goods  where id>N

Count: 1  Time=11.00s (11s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[121.69.51.10]
  select sleep(N)

Count: 6  Time=11.12s (66s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[121.69.51.10]
  call Proc()

[root@niewj download]#

Rows=150000.0 (300000) 排在最前了: 括号里是总量; 平均值最大, 排最前;

4.4 -s r (按照总的rows返回量)

查询返回行数最多的慢sql:

mysqldumpslow -v -s r /var/lib/mysql/niewj-slow.log

[root@niewj download]# mysqldumpslow -v -s r  /var/lib/mysql/niewj-slow.log

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log
Count: 3  Time=23.26s (69s)  Lock=0.00s (0s)  Rows=136666.7 (410000), root[root]@[121.69.51.10]
  select * from goods  where   id>N

Count: 2  Time=25.84s (51s)  Lock=0.00s (0s)  Rows=150000.0 (300000), root[root]@[121.69.51.10]
  select * from goods

Count: 1  Time=4.68s (4s)  Lock=0.00s (0s)  Rows=32262.0 (32262), root[root]@[121.69.51.10]
  select * from goods  where id>N

Count: 1  Time=11.00s (11s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[121.69.51.10]
  select sleep(N)

Count: 6  Time=11.12s (66s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[121.69.51.10]
  call Proc()

[root@niewj download]#

看到 Rows=136666.7 (410000) 排到最前了, 括号里是总量, 不论查了几次, 总量最大, 排最前了;

4.5 -s c (按照查询次数排序)

查找调用频次最高的慢查询sql:

mysqldumpslow -v -s c /var/lib/mysql/niewj-slow.log

[root@niewj download]# mysqldumpslow -v -s c  /var/lib/mysql/niewj-slow.log

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log
Count: 6  Time=11.12s (66s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[121.69.51.10]
  call Proc()

Count: 3  Time=23.26s (69s)  Lock=0.00s (0s)  Rows=136666.7 (410000), root[root]@[121.69.51.10]
  select * from goods  where   id>N

Count: 2  Time=25.84s (51s)  Lock=0.00s (0s)  Rows=150000.0 (300000), root[root]@[121.69.51.10]
  select * from goods

Count: 1  Time=4.68s (4s)  Lock=0.00s (0s)  Rows=32262.0 (32262), root[root]@[121.69.51.10]
  select * from goods  where id>N

Count: 1  Time=11.00s (11s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[121.69.51.10]
  select sleep(N)

[root@niewj download]#

4.6 -t N(限定返回topN的慢sql)

上面每种情形限定返回前两条:

4.6.1 -s at -t 2 查询平均耗时最长前2条的慢sql-限前2个:

[root@niewj download]# mysqldumpslow -v -s at -t 2 /var/lib/mysql/niewj-slow.log

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log
Count: 2  Time=25.84s (51s)  Lock=0.00s (0s)  Rows=150000.0 (300000), root[root]@[121.69.51.10]
  select * from goods

Count: 3  Time=23.26s (69s)  Lock=0.00s (0s)  Rows=136666.7 (410000), root[root]@[121.69.51.10]
  select * from goods  where   id>N

[root@niewj download]#

4.6.2 -s t -t 2 查询哪2个sql查询最耗时(算总时长,忽略次数)的慢sql-限前2个:

[root@niewj download]# mysqldumpslow -v -s t -t 2 /var/lib/mysql/niewj-slow.log 

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log
Count: 3  Time=23.26s (69s)  Lock=0.00s (0s)  Rows=136666.7 (410000), root[root]@[121.69.51.10]
  select * from goods  where   id>N

Count: 6  Time=11.12s (66s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[121.69.51.10]
  call Proc()

[root@niewj download]#

(69s)-(66s)

4.6.3 -s ar -t 2 查询前2个平均每次查询返回条数最多的慢sql-限前2个:

[root@niewj download]# mysqldumpslow -v -s ar -t 2 /var/lib/mysql/niewj-slow.log 

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log
Count: 2  Time=25.84s (51s)  Lock=0.00s (0s)  Rows=150000.0 (300000), root[root]@[121.69.51.10]
  select * from goods

Count: 3  Time=23.26s (69s)  Lock=0.00s (0s)  Rows=136666.7 (410000), root[root]@[121.69.51.10]
  select * from goods  where   id>N

[root@niewj download]#

Rows=150000.0 (300000)
Rows=136666.7 (410000)

4.6.4 -s r -t 2 查询返回行数最多的慢sql-限前2个:

[root@niewj download]# mysqldumpslow -v -s r -t 2 /var/lib/mysql/niewj-slow.log 

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log
Count: 3  Time=23.26s (69s)  Lock=0.00s (0s)  Rows=136666.7 (410000), root[root]@[121.69.51.10]
  select * from goods  where   id>N

Count: 2  Time=25.84s (51s)  Lock=0.00s (0s)  Rows=150000.0 (300000), root[root]@[121.69.51.10]
  select * from goods

[root@niewj download]#

Rows=136666.7 (410000)
Rows=150000.0 (300000)

4.6.5 -s c -t 2 查找调用频次最高的慢查询sql:-限前2个:

[root@niewj download]# mysqldumpslow -v -s c -t 2 /var/lib/mysql/niewj-slow.log 

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log
Count: 6  Time=11.12s (66s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[121.69.51.10]
  call Proc()

Count: 3  Time=23.26s (69s)  Lock=0.00s (0s)  Rows=136666.7 (410000), root[root]@[121.69.51.10]
  select * from goods  where   id>N

[root@niewj download]#

Count: 6
Count: 3