mysql慢查询日志

// 慢查询
缓慢的查询,低效的性能导致影响正常业务
MySQL默认10秒内没有响应SQL结果,为慢查询

// 检查慢查日志是否开启:
show variables like 'slow_query_log';
// 检查慢日志路径
show variables like '%slow_query_log%';
// 开启慢日志
set global slow_query_log=on;
// 慢日志判断标准(默认查询时间大于10s的sql语句)
show variables like 'long_query_time';
// 慢日志测试,检查慢日志记录情况
select sleep(12);
// 显示慢查询次数
show status like 'show_queries'
// 修改慢日志判断标准,修改为1秒 ---修改为一秒但是重启mysql之后，long_query_time依然是my.ini中的值,永久生效需要修改my.ini
set global long_query_time=1;
// 为了测试方便,所有查询都记录进慢日志(生产环境不要打开,否则产生大量无用日志,如建立索引)
set global log_queries_not_using_indexes=on;
show variables like '%log%';
// mysql数据库启动花费多少时间
show status like 'uptime'
// 显示mysql数据库的连接数
show status like 'connections'
// 显示数据的查询,更新,添加,删除次数
show status like 'com [select|insert|update|delete]'
// session是当前窗口的执行次数,global是启动至此的执行次数
show [session|global] status like
// 监听慢日志(慢日志路径注意不同)
tail -f /var/lib/mysql/izwz9hiye4lft7f85poremz-slow.log

第一行,SQL查询执行的时间 
第二行,执行SQL查询的连接信息,用户和连接IP 
第三行,记录了一些我们比较有用的信息,如下解析
    (1) Query_time,这条SQL执行的时间,越长则越慢
    (2) Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
    (3) Rows_sent,查询返回的行数
    (4) Rows_examined,查询检查的行数,越长就当然越费时间
第四行,设置时间戳,没有实际意义,只是和第一行对应执行时间
第五行及后面所有行(第二个#Time:之前),执行的sql语句记录信息,因为sql可能会很长

// mysqldumpslow
// 简介
如果开启了慢查询日志,就会生产大量的数据,然后我们就可以通过对日志的分析,生产分析报表,通过报表进行优化
// 用法帮助
执行mysqldumpslow --help 查看详细用法
注意 在mysql数据库所在的服务器上 而不是mysql>命令行中
// 常用命令
(1) 统计:mysqldumpslow --verbose /var/lib/mysql/izwz9hiye4lft7f85poremz-slow.log
(2) 时间排序: mysqldumpslow -s c /var/lib/mysql/izwz9hiye4lft7f85poremz-slow.log
// 优缺点
这个工具是最常用的工具,通过安装mysql进行附带安装,但是该工具统计的结果比较少,对我们的优化所提供的信息还是比较少,比如cpu,io等信息都没有

// 简介
pt-query-digest是用于分析mysql慢查询的一个第三方工具,它可以分析binlog、Generallog、slowlog
也可以通过SHOWPROCESSLIST或者过tcpdump抓取的mysql协议数据来进行分析。
可以把分析结果输出到文件中,分析过程中先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,可以借助分析结果找出问题进行优化
// 功能
// (1) pt-query-digest本质是perl脚本,所以首先安装perl模块
yum install -y perl-CPAN perl-Time-HiRes
// (2) 快速安装
wget https://www.percona.com/downloads/percona-toolkit/3.2.0/binary/redhat/7/x86_64/percona-toolkit-3.2.0-1.el7.x86_64.rpm
yum localinstall -y percona-toolkit-3.2.0-1.el7.x86_64.rpm
// (3) 检查是否安装完成
pt-query-digest --help

// 常用命令
(1) 查看服务器信息
pt-summary
(2) 查看磁盘开销使用信息
pt-diskstats

// mysql相关命令
// 查看mysql数据库信息
pt-mysql-summary --user=root --password=1234
// 分析慢查询日志
pt-query-digest --limit 100% /var/lib/mysql/izwz9hiye4lft7f85poremz-slow.log
// 从库和同步状态
pt-slave-find --host=localhost --user=root --password=1234
// 如果报错DBI connect(';host=localhost;mysql_read_default_group=client','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at /usr/bin/pt-slave-find line 2023.
(1) use mysql;
(2) select user,host ,plugin from mysql.user where user='root'; // perconaToolkit3.2.1不支持caching_sha2_password
(3) alter user root@'localhost' identified  with mysql_native_password by 'mysqlpassword'; // 让mysql支持percona的身份验证插件
// 查看mysql的死锁信息,在test库中建立一张deadlocks表,用于记录死锁信息
pt-deadlock-logger --run-time=10 --interval=3 --create-dest-table --dest D=test,t=deadlocks u=root,p=mysqlpassword
// 从慢查询日志中分析索引使用情况
pt-index-usage --user=root --password=mysqlpassword --host=localhost /var/lib/mysql/izwz9hiye4lft7f85poremz-slow.log
// 从慢查找数据库表中重复的索引
pt-duplicate-key-checker --host=localhost --user=root --password=mysqlpassword
// 查看mysql表和文件的当前活动IO开销(不要在高峰时使用)
pt-ioprofile
// 查看不同mysql配置文件的差异(集群常用,双方都生效的变量)
pt-config-diff /etc/my.cnf /root/my_master.cnf
// 查找数据库里大于1M的表
pt-find --user=root --password=mysqlpassword --tablesize +1M
// 查找表和索引大小并排序
pt-find --user=root --password=mysqlpassword --printf "%T\t%D.%N\n" | sort -rn
// 杀掉显示查询时间大于3秒的查询,--print仅为打印,--kill为杀死
pt-kill --user=root --password=mysqlpassword --busy-time 3 --print(打印) --kill(杀死)
// 查看mysql授权(集群常用,授权复制) 示例如下
pt-show-grants --user=root --password=mysqlpassword
pt-show-grants --user=root --password=mysqlpassword --separate --revoke
// 验证数据库复制的完整性(集群常用,主从复制后校验),示例如下
pt-table-checksum --user=root --password=mysqlpassword

// pt-query-digest排除有问题的SQL
// (1) 查询次数多且每次查询占用时间长的sql
通常为pt-query-digest分析的前几个查询,该工具可以很清楚的看出每个SQL执行的次数及百分比等信息,执行的次数多,占比较大的SQL
// (2) IO大的sql
注意pt-query-digest分析中的Rows examine项,扫描的行数越多,IO越大
// (3) 未命中的索引sql
pt-query-digest分析中的Rows examine(检测的数据)和Rows Send(真正发给客户端的数据)的对比。如果相差较大,说明该SQL的索引命中率不高,对于这种SQL,我们要重点进行关注

// 执行计划
SQL的执行计划反映出SQL的执行效率,在执行的SQL前面加上explain即可,如 explain select * from actor;
官方文档 https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

// id
数字越大越先执行,如果数字一样大,那么就从上往下执行,id列为null就表示这是一个结果集,不需要使用它来查询
// select_type
simple: 表示不需要union操作或者不包含子查询的简单select查询,有连接查询时,外层的查询为simple,其只有一个
primary: 一个需要union操作或者含有子查询的select,位于最外层的查询,select_type即为primary,且只有一个
union: union连接的两个select查询,第一个查询时dervied派生表,除第一个表外,第二个以后的表select_type都是union
union result: 包含union的结果集,在union和union all语句中,因此它不需要参与查询,所有id字段为null
dependent union: 与union一样,出现在union或union all语句中,但是这个查询要受到外部查询的影响,比如where in
subquery: 除了from字句中包含的子查询外,其他地方出现的子查询都可能是subquery
dependent subquery: 与dependent union类似,表示这个subquery的查询要受到外部表查询的影响
derived: from字句中出现的子查询,也叫做派生表,其他数据库中可能叫做内联视图或嵌套select
materialization: 物化子查询通过将子查询结果作为一个临时表来加快查询执行速度,正常来说是常驻内存,下次查询会再次引用临时表

// table
显示的查询表名,如果查询使用了别名,那么这里显示的是别名,如果不涉及对数据表的操作,那么这里显示为null,如果显示为尖括号括起来的<derived N>就表示这个是临时表
后边的N就是执行计划中的id,表示结果来自于这个查询产生.如果是尖括号括起来的<union M,N>,与<derived N>类似,也是一个临时表,表示这个结果来自于union查询的id为M,N结果集
// type,好的索引至少达到range,最好达到ref
system: 表中只有一行数据或者空表,且只能用于myisam和memory表,如果是innodb引擎表,type列在这个情况通常都是all或者index
const: 使用*唯一索引或者主键*,返回记录一定是*一行记录的等值*where条件时,通常type是const,其他数据库也叫做唯一索引扫描
eq_ref: 出现在要连接多个表的查询计划中,驱动表循环获取数据,这行数据是第二个表的主键或者唯一索引,作为条件查询只返回*一条数据*,且必须为not null,唯一索引和主键是多列时,只有所有的列都用作比较时才会出现eq_ref
ref: 不像eq_ref那样要求连接顺序,也没有主键和唯一索引的要求,只要使用相等条件检索时就可能出现,常见与辅助索引的等值查找或者多列主键、唯一索引中,使用第一个列之外的列作为等值查找也会出现,总之,返回数据*不唯一的等值*查找就可能出现
fulltext: 全文索引检索,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引 ft_idx_t1_nickname_remark
ref_or_null: 与ref方法类似,只是增加了null值的比较,实际用的不多
unique_subquery: 用于where中的in形式子查询,子查询返回不重复唯一值
index_subquery: 用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可能使用索引将子查询去重
range: 索引范围扫描,常见于使用>,<,is null,between,in,like等运算符的查询中
index_merge: 表示查询使用了两个以上的索引,最后取交集或者并集,常见于and,or的条件使用了不同的索引,
            官方排序这个在ref_or_null之后,但是实际上由于要读取多个索引,性能可能大部分时间都不如range
index: 索引全表扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询,换句话说,所有的数据就在索引里,不需要回表
all: 这个就是全表扫描数据文件,然后再在server层进行过滤返回符合要求的记录

// possible_keys
查询可能使用到的索引

// key
查询真正使用到的索引,select_type为index_merge时,这里可能出现两个以上的索引,其他的select_type这里只会出现一个

// key_len
用于处理查询的索引长度,如果是单列索引,那就是整个索引长度,如果是多列索引,那么查询不一定都能使用到所有的列,具体使用到了多少个列的索引,这里就会计算进去,没有使用的列不会计算进去。留一下这个值,算一下你的多列索引总长度就知道有没有使用到所有的列了。另外,key_len只计算where条件用到的长度,而排序和分组就算用到了索引,也不会计算到key_len中

// ref
如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件,使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func

// rows
这里是执行计划中估算的扫描行数,不是精确值

// extra
no tables used: 不带from字句的查询或者from dual查询
NULL: 查询的列未被索引覆盖,并且where筛选条件是索引的前导列(where条件字段复合索引连续连起来的索引),意味着用到了索引,但是部分字段未被索引覆盖,必须通过"回表"来实现,不是纯粹的用到了索引,也不是完全没用到索引
using index: 查询时不需要回表查询,直接通过索引就可以获取到查询的数据
using where: 查询的列未被索引覆盖,where筛选条件*非索引*的前导列
using where using index: 查询的列被索引覆盖,并且where筛选条件是索引列之一但是*不是索引的前导列*,意味着无法直接通过索引查找来查询到符合条件的数据
using index condition: 与using where类似,查询的列不完全被索引覆盖,where条件中是一个前导列的范围
using temporary: 表示使用了*临时表存储中间结果*比如select distinct。临时表可以是内存临时表和磁盘临时表,执行计划中看不出来,需要查看status变量,used_tmp_table,used_tmp_disk_table才能看出来
using filesort: mysql会对结果使用一个外部索引排序(外部临时文件),而不是按索引次序从表里读取行,此时mysql会根据联接类型浏览所有符合条件的记录,并保存排序关键字和行指针,然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
using intersect: 表示使用and的各个索引的条件时,该信息表示从处理结果获取交集
using union: 表示使用or连接各个使用索引的条件时,该信息表示从处理结果获取并集
using sort_union和using sort_intersection: 用and和or查询信息量大时,先查询主键,然后进行排序合并后返回结果集
firstmatch(tb_name): 5.6.x开始引入的优化子查询的新特性之一,常见于where字句含有in()类型的子查询,如果内表的数据量比较大的,就可能出现这个
loosescan(m..n): 5.6.x之后引入的优化子查询的新特性之一,在in()类型的子查询中,子查询返回的可能有重复记录时,就可能出现这个

// filtered
filtered: 使用explain extended时会出现这个列,5.7之后的版本默认就有这个字段,不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后,剩下多少