MySQL-慢日志分析

1.MySQL自带参数mysqldumpslow

mysqldumpslow --help
   参数：
   -a: 不将数字抽象成N，字符串抽象成S
   -s: 是表示按照何种方式排序：
       c: 访问次数
       l: 锁定时间
       r: 返回记录
       t: 查询时间
       al:平均锁定时间
       ar:平均返回记录数
       at:平均查询时间 （默认方式）
       ac:平均查询次数
   -t: 即为返回前面多少条的数据
   -g: 后边搭配一个正则匹配模式，大小写不敏感的
   举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可 mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log
   工作常用参考：
   #得到返回记录集最多的10个SQL 
   mysqldumpslow -a -s r -t 10 /var/lib/mysql/atguigu-slow.log 
   
   #得到访问次数最多的10个SQL 
   mysqldumpslow -a -s c -t 10 /var/lib/mysql/atguigu-slow.log 
   
   #得到按照时间排序的前10条里面含有左连接的查询语句 
   mysqldumpslow -a -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 
   
   #另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况 
   mysqldumpslow -a -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

2.pt工具

pt-query-digest
   作用：分析日志（包括binlog、General log、slowlog）、processlist以及tcpdump中的查询
   参数：
   --create-review-table  当使用--review参数把分析结果输出到表中时，如果没有表就自动创建
   --create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建
   --filter 输出符合条件的内容
   --limit  限制输出的百分比或数量。可指定百分比或数字，例如90%表示按响应时间从小到大排序，输出90%的结果；20表示输出最慢的20条
   --host   指定MySQL地址，也可用-h指定
   --port   指定MySQL端口
   --user   指定MySQL用户名，也可用-u指定
   --password  指定MySQL密码，也可用-p指定
   --ask-pass  连接 MySQL 时提示输入密码。
   --history  将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，可通过查询同一CHECKSUM来比较某类型查询的历史变化
   --review  将分析结果保存到表中，从而方便未来review。这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中
   --output  指定将结果输出输出到哪里，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读
   --since  指定分析的起始时间，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计
   --until  指定分析的截止时间，配合--since可以分析一段时间内的慢查询

   举例：
   #切换至慢日志目录
   cd /data/mysql/mysql3306/data
   
   # 展示slow.log中最慢的查询的报表
   pt-query-digest slow_query.log
   
   # 分析最近12小时内的查询
   pt-query-digest --since=12h slow_query.log
   
   # 分析指定范围内的查询
   pt-query-digest slow_query.log --since '2021-08-16 00:00:00' --until '2021-08-16 23:59:00'
   
   # 把slow.log中查询保存到query_history表  (必须先有test库)
   pt-query-digest --user=root --port=3306 --ask-pass --review h=127.0.0.1,D=test,t=query_history --create-review-table  slow_query.log
   
   #分析指含有select语句的慢查询
   pt-query-digest --filter '$event->{fingerprint}=~m/^select/i' slow_query.log> /tmp/slow_report.log
   
   #针对某个用户的慢查询
   pt-query-digest --filter '($event->{user}||"")=~m/^root/i' slow_query.log> /tmp/slow_report.log
   
   #分析binlog
   mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000007 > mysql-bin000007.sql
   pt-query-digest --type=binlog mysql-bin000007.sql