顾名思义，慢查询日志中记录的是执行时间较长的query，从5.6开始slow log位置的参数从
log-slow-queries变为
slow_query_log_file
##
不指定的话默认名为 主机名-slow.log
slow_query_log = 1
##
开启慢查询
long_query_time = 2
##
指定超时时间2S
记录到慢查询日志，默认是10s。
log_queries_not_using_indexes = 1
##log下来没有使用索引的query 我们是开启的
log_slow_admin_statements = 1 ##记录管理操作，alter/analyze table
log_slow_slave_statements = 1 ##记录由slave产生的查询日志
expire_logs_days = 90 ##binlog 日志保留时间，超时会自动清理
min_examined_row_limit = 100
##
扫描记录小于该值的SQL不记录到慢查询日志中，
eg：当我执行如下命令时，对应的慢查询日志中，可以看到只有超过涉及到100行数据才会记录到慢查询日志中。而limit 40 limit 49的sql都没有记录。
mysql> insert into yao select * from yao limit 50;
Query OK, 50 rows affected (0.01 sec)
Records: 50 Duplicates: 0 Warnings: 0
mysql> insert into yao select * from yao limit 40;
Query OK, 40 rows affected (0.00 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> insert into yao select * from yao limit 49;
Query OK, 49 rows affected (0.00 sec)
Records: 49 Duplicates: 0 Warnings: 0
# Query_time: 0.009162 Lock_time: 0.000078 Rows_sent: 0 Rows_examined: 100
SET timestamp=1537844390;
insert into yao select * from yao limit 50;
# Time: 2018-09-25T03:00:12.174658Z
# User@Host: root[root] @ localhost [] Id: 6
# Query_time: 0.007614 Lock_time: 0.000107 Rows_sent: 0 Rows_examined: 118
SET timestamp=1537844412;
insert into yao select * from yao limit 59;
mysqldumpslow
[root@slave mysql_data]# mysqldumpslow -s ORDER -t 2 slow.log
Reading mysql slow query log from slow.log
Count: 5 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into yao select * from yao limit N
Count: 1 Time=24.85s (24s) Lock=0.00s (0s) Rows=0.0 (0), []@[]
throttle: N ‘S’ warning(s) suppressed.
一般线上slow log文件很大，我们一般tail 部分行来采样解析。
pt_query_digest安装
yum install -y perl-CPAN perl-Time-HiRes
wget percona.com/get/pt-query-digest
yum -y install perl-Digest-MD5
#分析最近12小时的慢日志
./pt-query-digest –since=12h /home/db/mysql/node1/logs/slow.log > slow_pt.log
#指定时间段慢日志
./pt-query-digest slow.log –since ‘2020-06-09 00:00:00’ –until ‘2020-06-10 00:00:00’ > aaa.log
#分析只包含select语句的慢查询
./pt-query-digest –filter ‘$event->{fingerprint} =~ m/^select/i’ slow.log> slow_pt.log
#查询特定用户的慢sql
./pt-query-digest –filter ‘($event->{user} || “”) =~ m/^root/i’ slow.log> slow_pt.log
… …