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

期权梦想家
• 阅读 1123

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

  1. al = 平均锁定时长
  2. ar=平均返送的rows数
  3. at=平均query时长
  4. c=sql查询总数(某一条sql查询了几次)
  5. r=返送的rows总数
  6. t=query的时间总数
  7. -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

5. mysqldumpslow工具的缺点

1. 它是mysql自带的, perl脚本写的工具;

2. 无法提供cpu/io等扩展信息;

点赞
收藏
评论区
推荐文章
blmius blmius
•
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s
5236
0
0
胖大海 胖大海
•
2年前
MySQL 通用查询日志与慢查询日志
MySQL中的日志包括：错误日志、二进制日志、通用查询日志、慢查询日志等等。这里主要介绍下比较常用的两个功能：通用查询日志和慢查询日志。1）通用查询日志：记录建立的客户端连接和执行的语句。2）慢查询日志：记录所有执行时间超过long
1222
0
0
Stella981 Stella981
•
3年前
CentOS下MySQL的安装过程
1查看CentOS自带的mysql输入命令：rpmqa|grepmysql!(https://oscimg.oschina.net/oscnet/367356b04c849b52d537a8fe0edb1dd9aa5.jpg)2将自带的MySQL卸载了
853
0
0
Wesley13 Wesley13
•
3年前
MySQL——性能优化
性能优化的思路1、首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句。MySQL——慢查询2、其次使用explain命令去查看有问题的SQL的执行计划。MySQL——执行计划EXPLAIN3、最后可以使用showprofile\s\查看有问题的SQL的性能使用情况。MySQL高级：showprofile
1211
0
0
Wesley13 Wesley13
•
3年前
mysql配置调优
工作中，会遇到需要查看mysql的top20慢sql，逐个进行优化，加上必要的索引这种需求，这时就需要开启数据库的慢查询日志的功能1.查询当前慢查询日志的状态\默认为关闭状态mysqlshowvariableslike"
974
0
0
Wesley13 Wesley13
•
3年前
MySQL高级
1mysql的架构介绍Mysql简介概述高级MySQLmysql内核sql优化工程师mysql服务器的优化查询语句优化主重复制软硬件升级容灾备份sql编程MysqlLinux版本的安装mysql5.5查看MySQL的安装位置：whichmysqld参考网址：https://ww
938
0
0
Wesley13 Wesley13
•
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败，请联系管理员！');中国在东8区所以加8方法二：selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
1759
0
0
Wesley13 Wesley13
•
3年前
mysql 工具
mysql增加慢日志vim my.conf\超过1s的查询会记录到日志文件中long\_query\_time1logslowqueries/data/var/mysql\_slow.log\记录没用索引的查询logqueriesnotusinginde
891
0
0
那年烟雨落申城 那年烟雨落申城
•
1年前
MySQL慢日志分析，资源消耗监控，执行计划
慢日志Mysql提供了慢日志记录，可以监控执行时间超过设定值的sql，并予以记录。查看是否开启了慢查询日志：sqlshowvariableslike'%slow%';sqlshowvariableslike'%longquerytime%';可以看到慢查询
545
0
0
美凌格栋栋酱 美凌格栋栋酱
•
5个月前
Oracle 分组与拼接字符串同时使用
SELECTT.,ROWNUMIDFROM(SELECTT.EMPLID,T.NAME,T.BU,T.REALDEPART,T.FORMATDATE,SUM(T.S0)S0,MAX(UPDATETIME)CREATETIME,LISTAGG(TOCHAR(
1958
0
0
期权梦想家
期权梦想家
Lv1
雁啼红叶天，人醉黄花地，芭蕉雨声秋梦里。
文章
10
粉丝
0
获赞
0
热门文章
为什么很多人会觉得IT门槛低？
你可能不知道的JS函数
有关 Grunt - 自动化构建工具的奇技淫巧
MobPush智能推送：数智化运营加速用户转化
挑战100+Python项目2：中英文翻译