Mysql数据库优化-mysqldumpslow命令

mysql没有那么多性能视图可以查询SQL执行情况，但是也提供mysqldumpslow命令来解析日志来获取数据库SQL语句执行情况，为运维、开发人员提供了获取需要具体优化SQL语句的一个方法。

mysqldumpslow
命令大概形式： slowlog是需要分析的日志
mysqldumpslow -s c -t 10 {$PATH}/slow-log
主要参数：

A: -s, 是sort的意思，表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒序；
B: -t, 是top n的意思，即为返回前面多少条的数据；
C: -g, 是grep的意思，后边可以写一个正则匹配模式，大小写不敏感的。

用法举例：

mysqldumpslow -t 10 slow.log      ---耗时最长的10个SQL

Reading mysql slow query log from slow.log

Count: 2  Time=455.96s (911s)  Lock=0.00s (0s)  Rows=7502.5 (15005), 

  call procedure_loop_insert_with_var(N)

Count: 15  Time=246.77s (3701s)  Lock=0.00s (0s)  Rows=101.4 (1521), 2users@2hosts

  call procedure_loop_insert_with_var1(N)

Count: 3  Time=3.05s (9s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

  delete from loop_insert where id>=N

Count: 1  Time=2.43s (2s)  Lock=0.00s (0s)  Rows=100000.0 (100000), root[root]@localhost

  select fun_loop_insert1(id) from loop_insert where id<=N

Count: 1  Time=2.01s (2s)  Lock=0.00s (0s)  Rows=90000.0 (90000), root[root]@localhost

  select fun_loop_insert1(id) from loop_insert where id>=N and id <N

Count: 2  Time=1.95s (3s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

  delete from loop_insert where id>N

Count: 2  Time=1.92s (3s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

  delete from loop_insert

Count: 2  Time=0.45s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

  delete from loop_insert1

Count: 26  Time=0.33s (8s)  Lock=0.00s (0s)  Rows=1.0 (26), root[root]@localhost

  select count(*) from loop_insert

Count: 1  Time=0.27s (0s)  Lock=0.01s (0s)  Rows=9999.0 (9999), root[root]@localhost

  select fun_loop_insert1(id) from loop_insert where id<N

 mysqldumpslow -s c -t 10 slow.log    -- 执行次数最多的10个sql语句

Reading mysql slow query log from slow.log

Count: 26  Time=0.33s (8s)  Lock=0.00s (0s)  Rows=1.0 (26), root[root]@localhost

  select count(*) from loop_insert

Count: 15  Time=246.77s (3701s)  Lock=0.00s (0s)  Rows=101.4 (1521), 2users@2hosts

  call procedure_loop_insert_with_var1(N)

Count: 3  Time=0.17s (0s)  Lock=0.01s (0s)  Rows=0.0 (0), root[root]@localhost

  truncate table loop_insert

Count: 3  Time=0.13s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

Count: 3  Time=3.05s (9s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

  delete from loop_insert where id>=N

Count: 2  Time=455.96s (911s)  Lock=0.00s (0s)  Rows=7502.5 (15005), zgpdba[zgpdba]@centos01

  call procedure_loop_insert_with_var(N)

Count: 2  Time=1.92s (3s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

  delete from loop_insert

Count: 2  Time=0.45s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

  delete from loop_insert1

Count: 2  Time=1.95s (3s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

  delete from loop_insert where id>N

Count: 1  Time=0.27s (0s)  Lock=0.01s (0s)  Rows=9999.0 (9999), root[root]@localhost

  select fun_loop_insert1(id) from loop_insert where id<N

mysqldumpslow -s r -t 10 slow.log   -- 获取条数最多的10个sql 

Reading mysql slow query log from slow.log

Count: 1  Time=2.43s (2s)  Lock=0.00s (0s)  Rows=100000.0 (100000), root[root]@localhost

  select fun_loop_insert1(id) from loop_insert where id<=N

Count: 1  Time=2.01s (2s)  Lock=0.00s (0s)  Rows=90000.0 (90000), root[root]@localhost

  select fun_loop_insert1(id) from loop_insert where id>=N and id <N

Count: 2  Time=455.96s (911s)  Lock=0.00s (0s)  Rows=7502.5 (15005), zgpdba[zgpdba]@centos01

  call procedure_loop_insert_with_var(N)

Count: 1  Time=0.27s (0s)  Lock=0.01s (0s)  Rows=9999.0 (9999), root[root]@localhost

  select fun_loop_insert1(id) from loop_insert where id<N

Count: 15  Time=246.77s (3701s)  Lock=0.00s (0s)  Rows=101.4 (1521), 2users@2hosts

  call procedure_loop_insert_with_var1(N)

Count: 1  Time=0.26s (0s)  Lock=0.02s (0s)  Rows=329.0 (329), root[root]@localhost

  SELECT table_schema, table_name, data_free/N/N AS data_free_MB FROM information_schema.tables

Count: 26  Time=0.33s (8s)  Lock=0.00s (0s)  Rows=1.0 (26), root[root]@localhost

  select count(*) from loop_insert

Count: 3  Time=0.17s (0s)  Lock=0.01s (0s)  Rows=0.0 (0), root[root]@localhost

  truncate table loop_insert

Count: 3  Time=0.13s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

Count: 2  Time=1.92s (3s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
————————————————
版权声明：本文为CSDN博主「小尖一步」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zhougongping/article/details/122377129