mysql(四) mysql开启慢日志并使用mysqldumpslow分析

查看mysql是否开启慢日志命令:

show variables like 'slow_query_log';

一、第一种方式，修改配置文件

1、修改配置文件
linux下可能在/usr/local/mysql/my.cnf，windows下是my.ini
在[mysqld]下添加如下参数：

# 添加慢查询日志
log_output=file
slow_query_log=on #开启慢日志
slow_query_log_file = /tmp/mysql-slow.log  #慢日志存储位置
log_queries_not_using_indexes=on  #是否将不走索引的查询都记录到慢日志
long_query_time = 1 #慢日志临界值（秒）

2、重启mysql

二、第二种方式，使用命令

set global  slow_query_log = ON;
set global slow_query_log_file = '/var/lib/mysql/test-slow.log';
set global long_query_time = 2;

三、windows下使用mysqldumpslow分析慢日志

1、下载perl
下载地址:https://www.activestate.com/products/activeperl/downloads/

2、安装perl

3、使用mysqldumpslow
进入mysql安装bin目录
运行命令

F:\mysql8.0.13\mysql-8.0.13-winx64\bin>perl mysqldumpslow.pl -t 5 h:/mysql-slow.log

Reading mysql slow query log from h:/mysql-slow.log
Count: 1  Time=2.61s (2s)  Lock=0.00s (0s)  Rows=1163242.0 (1163242), root[root]@localhost
  select * from download_file_status

Count: 1  Time=1.91s (1s)  Lock=0.00s (0s)  Rows=31.0 (31), root[root]@DESKTOP-IGRQ8V6
  SELECT
  t0.STATUS AS id,
  IFNULL( t3.count, N ) AS count,
  t3.name
  FROM
  file_download_status_cfg t0
  LEFT JOIN (
  SELECT
  count( t2.id ) count,
  t2.STATUS, t1.name
  FROM
  ( SELECT req_id, name FROM data_info WHERE name = 'S' or name = 'S' or name = 'S' or name = 'S' or name = 'S' or name = 'S' or name = 'S' or name = 'S' or name = 'S' or name = 'S' or name = 'S' or name = 'S' or name = 'S' ) t1
  INNER JOIN ( SELECT id, req_id, STATUS, create_time FROM download_file_history  ) t2 ON t1.req_id = t2.req_id
  GROUP BY
  t2.STATUS, name
  ) t3 ON t0.STATUS = t3.STATUS order by t3.name, id

Count: 1  Time=1.78s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@DESKTOP-IGRQ8V6
  SELECT COUNT(N) FROM (SELECT r6.name,r6.type,r6.source,r6.detail,r5.size FROM (SELECT r3.name,CONVERT(SUM(r4.size)/N,DECIMAL(N,N)) AS size FROM (SELECT r1.req_id,r1.name FROM data_info r1 INNER JOIN req r2 ON r1.req_id = r2.id WHERE r2.type='S') r3 INNER JOIN (SELECT req_id, sum(size) AS size FROM download_file_history t WHERE t.status = 'S' GROUP BY t.req_id ) r4 ON r3.req_id=r4.req_id GROUP BY r3.name) r5 INNER JOIN data_info_cfg r6 ON r5.name=r6.name) t

Count: 1  Time=1.48s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@DESKTOP-IGRQ8V6
  SELECT COUNT(N) FROM (SELECT * FROM (SELECT s.req_id,s.list_id,s.name,s.create_time,s.status,p.meta finish_file_cnt,p.process,s.docker_id,s.id,p.meta_process,s.finish_time FROM (SELECT req_id,list_id,name,create_time,status,docker_id,id,finish_time FROM download_list_status WHERE STATUS !='S') s INNER JOIN (SELECT dh.meta,dm.list_id,CONCAT(ROUND((IFNULL(dh.finish/dm.sum*N, N)),N ), 'S') process,dh.finish,CONCAT(ROUND((IFNULL(dh.meta/dm.sum*N,N)),N),'S') meta_process FROM (SELECT fs.list_id,IFNULL(dfs.sum,N) sum FROM download_list_status fs LEFT JOIN (SELECT list_id,COUNT(id) sum FROM download_file_status GROUP BY list_id) dfs ON dfs.list_id = fs.list_id) dm INNER JOIN (SELECT dp.list_id,dp.finish + ma.meta finish,ma.meta FROM (SELECT ds.list_id,IFNULL(df.finish,N) AS finish FROM download_list_status ds LEFT JOIN (SELECT COUNT(id) AS finish, list_id FROM download_file_status WHERE STATUS='S' GROUP BY list_id) df ON ds.list_id=df.list_id) dp INNER JOIN (SELECT da.list_id,IFNULL(dfa.meta,N) AS meta FROM download_list_status da LEFT JOIN (SELECT COUNT(id) AS meta, list_id FROM download_file_status WHERE STATUS='S' GROUP BY list_id) dfa ON da.list_id = dfa.list_id) ma ON dp.list_id = ma.list_id) dh ON dm.list_id = dh.list_id) p ON s.list_id = p.list_id ORDER BY s.id DESC) t) t

Count: 1  Time=1.15s (1s)  Lock=0.00s (0s)  Rows=8.0 (8), root[root]@DESKTOP-IGRQ8V6
  SELECT t.status id,IFNULL(t1.count,N) count FROM file_download_status_cfg t LEFT JOIN (SELECT d3.status,count(d3.id) count FROM (SELECT d2.`status`,d2.create_time,d2.id FROM data_info d1 LEFT JOIN (SELECT * FROM download_file_history ) d2 ON d1.req_id=d2.req_id WHERE d1.type='S' AND d1.name='S') d3 GROUP BY d3.status) t1 ON t.status = t1.status

4、mysqldumpslow命令参数

mysqldumpslow的命令参数列举如下：
--help    输出帮助信息

-v           输出详细信息 
-d          调试
-s          按照什么排序，默认是'at'，显示顺序为倒序
              al: 平均锁表时间

ar: 平均结果行数
                at: 平均查询时间
                 c: 次数
                 l: 锁表时间
                 r: 总结果行数
                 t: 总查询时间  
 -r          正序排序，即从小到大排序

-t NUM       限制显示的条数

-a           显示出数字和字符串，默认数字为 N 字符串为 'S'
-g PATTERN   过滤字符串，后接正则表达式，如'10$' 以10为结尾的条件