mysql工具使用

1、mysqldumpslow工具
优缺点：这个工具是最常用的工具，通过安装mysql进行附带安装，但是该工具统计的结果比较少，对我们的优化所提供的信息比较少，比如cpu，io等信息都没有
简介:如果开启了慢查询日志，就会产生大量的数据,然后我们就通过对日志的分析，生成分析报表，通过报表进行优化

#查看帮助文档
mysqldumpslow --help

#进行信息汇总展示（两种写法）
mysqldumpslow --verbose /var/lib/mysql/izbp1isjfk2rw8pwnovjoxz-slow.log
mysqldumpslow --v /var/lib/mysql/izbp1isjfk2rw8pwnovjoxz-slow.log

#进行次数排序展示
mysqldumpslow --s c /var/lib/mysql/izbp1isjfk2rw8pwnovjoxz-slow.log

#进行执行时间排序展示
mysqldumpslow --s t /var/lib/mysql/izbp1isjfk2rw8pwnovjoxz-slow.log

#进行平均时间排序展示
mysqldumpslow --s at /var/lib/mysql/izbp1isjfk2rw8pwnovjoxz-slow.log

2、pt-query-digest工具
简介:pt-query-digest是用于分析mysql慢查询的一个第三方工具，它可以分析binlog、General log、slowlog,
也可以通过 SHOWPROCESSLIST 或者通过tcpdump抓取的mysql协议数据来进行分析。可以把分析结果输出到文件中，
分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各种查询的执行
时间、次数、占比等们可以借助分析结果找到问题进行优化

安装步骤
1）pt-query-digest本子是perl脚本，所以首先安装perl模块
yum install -y perl-CPAN perl-Time-HiRes
2）https://www.percona.com/downloads/percona-toolkit/LATEST/进入网站进行下载
wget https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/redhat/7/x86_64/percona-toolkit-3.2.1-1.el7.x86_64.rpm
yum localinstall -y percona-toolkit-3.2.1-1.el7.x86_64.rpm

#查看帮助文档
pt-query-digest --help

#检查服务器信息
pt-summary

#查看磁盘开销使用信息
pt-diskstats

#查看mysql数据库信息
pt-mysql-summary --user='root' --password='密码'

#分析慢查询日志（命令铺展100%）
pt-query-digest --limit 100% /var/lib/mysql/izbp1isjfk2rw8pwnovjoxz-slow.log

#查找mysql的从库和同步状态
pt-slave-find --host=localhost --user='root' --password='密码'

#查看mysql的死锁信息,命令会创建一个deadlocks表进行存储数据，并把死锁的信息放到这个表里面
pt-deadlock-logger --run-time=10 --interval=3 --create-dest-table --dest D=test1,t=deadlocks u='root',p='密码'

#测试死锁情况
1）查看自动提交状态
show variables like 'autocommit'

2）设置手动提交
set autocommit=0;

3）设定死锁情况
select * from t1 where id=1 for update;
select * from t2 where id=1 for update;

#从慢查询日志中分析索引使用情况
pt-index-usage --host=localhost --user='root' --password='密码' /var/lib/mysql/izbp1isjfk2rw8pwnovjoxz-slow.log;

#从慢查询查找数据库表中重复的索引
pt-duplicate-key-checker --host=localhost --user='root' --password='密码';

#查看mysql表和文件的当前活动IO开销（不要在高峰时候使用）
pt-ioprofile

#查看不同mysql配置文件的差异（集群常用，双方都生效的变量）
pt-config-diff /etc/my.cnf /root/my_master.cof

#查找时刻大于1M的表
pt-find --user='root' --password='密码' --tablesize +1M

#查看表和索引大小并排序
pt-find --user='root' --password='密码' --printf '%T\t%D%N\n' | sort -rn

#查询时间大于3秒的查询sql
pt-kill --user='root' --password='密码' --busy-time 3 --print

#kill掉大于3秒的查询
pt-kill --user='root' --password='密码' --busy-time 3 --kill

#查看mysql授权（集群常用，授权复制）
pt-show-grants --user='root' --password='密码'

pt-show-grants --user='root' --password='密码' --separate --revoke

#验证数据库的复制的完整性（集群常用，主从复制后验证）
pt-table-checksum --user='root' --password='密码'

三大类有问题的SQL
查询次数多且每次查询占用时间长的SQL
1）通常为pt-query-digest分析的前几个查询，该工具可以很清楚的看出每个SQL执行的次数及等比信息系，执行的次数多，占比比较大的SQL

IO大的SQL
2）注意pt-query-digest分析中的Rows examine项，扫描的行数越多，IO越大

未命中的索引的SQL
3）pt-query-digest分析中的Rows examine和Rows Send的对比，说明该SQL的索引命中率不高，对于这种SQL ,我们要重点进行关注

explain执行计划分析
#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语句中，但是这个查询要受到外部查询的影响 #explain select * from film_category where film_id in (select film_id from film  union all select film_id from film_actor)\G
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列
system:表中只有一行数据或者是空表，且只能用于myisam和memory表，如果是Innodb引擎，type列在这个情况通常都是all或者index
const:使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const，其他数据库也叫做唯一索引扫描
eq_ref:出现在要链接多个表的查询计划中，驱动表循环获取数据，这行数据是第二个表的主键或者唯一索引，作为条件查询只返回
一条数据，且必须为not null,唯一索引和主键是多列时，所有的列都用作比较时才会出现eq_ref
ref:不像eq_ref那样要求链接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与服务索引的等值查找或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现
fulltext:全文索引检索，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
ref_on_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层进行过滤返回符合要求的记录

type列总结
依次性能从好到差:system，const，eq_ref,ref,fulltext,ref_or_null,unique_subquery,index,subquery,range,index_merge,index,ALL,除了all之外，其他的type都已使用到索引，除了index_merge之外，取它的type只可以用到一个索引，好的sql查询至少要达到range级别，最好要达到ref级别

#possible_key列
查询可能使用到的索引

#key
查询真正使用到的索引

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

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

#row列
这里是执行计划中估算的扫描行数，不是精确数值

#extra列
no table used:不带from字句的查询或者From dual查询
NULL：查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引
Using where Using index:查询的列被索引覆盖，并且where筛选条件是索引列质疑，但是不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据
Using index condition:与Using where类似，查询的列不完全被索引覆盖，where条件中是一个前导列的范围
Using temporary:标识使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看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查询信息量大时，先查询主键，然后进行排序合并后返回结果集
Fistnatcg(tb_name):5.6.X开始引入的优化只查询的新特性之一，常见于where字句含有in()类型的子查询，如果表内的数据量比较大，就可能出现这个
Loosescan(m...n):5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个

#filtered列
使用explain extended时会出现这个列，5.7之后的版本默认就有了这个字段，不需要使用explain extended了，这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足条件的查询记录数据量的比例，注意是百分比，不是具体记录数

#join_buffer_size
mysql连接缓冲区的大小，这个是在关联时候必不可少的，主要是控制关联时候加载驱动表的数据进入到缓冲区大小，如果缓冲区开辟的越大，说明存储的表偏移量数据越多，查询效率越快

#缓冲区处理零时临时处理数据的大小
show rariables like '%sort_buffer_size%';

#二次读取缓冲区的大小
show rariables like '%read_rnd_buffer_size%';