【Mysql优化三章】第二章

第二种工具就是pt-query-digest

安装的方法很简单，这里我们使用快速安装即可：

wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum localinstall -y  percona-toolkit-2.2.16-1.noarch.rpm

如果我们使用 pt-query-digest --help来看是否安装成功：

ok，安装成功了。然后我们直接分析我们的慢查询日志：

pt-query-digest 

我们使用explain来分析sql查询：

table：显示查询的哪一个表

type：显示使用了哪种连接，从const，eq_reg,ref,range,index和All

possible_keys:显示可能的索引

key：实际使用的索引

key_len:索引的长度，不损失精确性的前提下，越短越好。

ref：显示索引的哪一列被使用了，有可能的话，返回一个常数。

rows：mysql认为必须检查用来返回数据的行数。

extra：有两种一种是Using filesort一种是Using temporary，也就是排序和临时表，如果出现这两种，就需要对sql语句进行优化了，临时表的出现一般出现在ORDER BY而不是GROUP BY。

count（）和max（）的优化

我们根据图表可以看出来，我们这个表是countrylanguage表，没有索引，扫描了984条数据，当我们的数据上十万百万的时候，这条数据无疑会给我们的查询带来影响，那么我们怎么优化呢？

ok，我们在字段Percentage上面创建索引，如图。

然后我们再次查询：

ok，我们的优化起到了效果，我们不需要再扫描全表的数据了，而是根据索引就可以实现查询的功能，然后我们再搞count()

比如我们要查询百分比是100%和4.9%的语言数量分别是什么？

那我们需要怎么做呢？

我这里给大家写个示范

select count(Percentage=‘100’ OR NULL),count(Percentage = '4.9' OR NULL) from countrylanguage;

我们要注意的是，COUNT()如果直接写COUNT（*）还有COUNT（某个字段）所得的结果可能是不一样的。

ok，我们在查询的时候，经常会用到的就是子查询，那么我们怎么来优化子查询呢？

我们一般用join来连接表，代替子查询，但是我们需要注意的是，注意绑定的查询字段是否有一对多的关系，避免查询重复。

举个简单的小例子吧：我们创建两个表分别是table1和table2，两个表各有一个字段id和tid 我们查询在table2中的id在table1中的id值我们一般的写法是：

select id from table1 where table1.id in(select tid from table2);

我们对其进行一些优化，使用join：

select table1.id from table1 join table2 on table1.id=table2.tid;

但是如果我们的table2中存在重复的数据，也就是和table1形成了数据1对多的关系我们该怎么办呢？

我们使用关键字distinct就可以完美的解决这个问题：

select distinct  table1.id from table1 join table2 on table1.id=table2.tid;

我们再使用关键字limit与order by的时候难免会涉及到filesort数据排序的问题，但是我们的数据量一旦非常大以后，势必会增加我们IO的负担，那么我们怎么优化呢？

ok ，我们最简单的第一步就是使用有索引的字段或者主键进行ORDER BY的操作，这样可以避开排序，减少我们的IO操作，但是如果我们往后面查询的时候，还是会扫描很多很多数据，增加我们的IO那么我们应该怎么做呢？其实很简单，我们可以在上一次扫描完成的时候记录下我们的扫描的主键ID，然后我们在进行下一次查询的时候，直接使用主键过滤。

我们这样做的缺点就是，我们的主键ID是不可以断续的，比如我们查询600行后的5行，但是我们601,603都删除了，那么我们查询的结果就会不足5行。

索引的优化：

1.我们一般的索引都创建在ORDER BY和GROUP BY和WHERE从句中出现的列字段。

2.在不影响查询的准确性的前提下，我们的索引字段越小越好。

3.离散度较大的列放在联合索引的前面。（那么什么是离散度呢，我们只需要计算联合索引中的列的唯一值的数量，数量越多离散度越好）。

那么我们的索引是不是越多越好呢？答案是否定的，因为索引在增加我们查询效率的同时会降低我们的写入效率，而且索引过多的时候，查询效率也会受到影响。因为我们索引过多的时候，分析数据的时间就会增长，所以我们需要删除和维护重复的索引和亢余的索引。什么是重复索引呢，比如我们设置某个表的id为主键，还设置了他的id是唯一索引unique，这就是一种重复索引，那么什么是亢余索引呢，很简单的例子，比如我们在联合索引中使用了我们作为主键的ID，这个时候就会造成亢余索引。

那么我们如何查找重复和亢余的索引呢、我们需要查询information schema这个数据库，就可以找到重复或者亢余的索引，但是这样就很麻烦，我们这里提供给大家一个工具，那就是pt-duplicate-key-checker来检查重复或者亢余的索引：

ok我们使用pt-duplicate-key-checker -uroot -p‘密码’ -h 127.0.0.1 命令就可以帮我们分析数据库中的情况，并且给出优化的建议。

随着我们的业务发生变化，我们可能有些索引不再使用了，这个时候我们就需要去除不需要的索引来增加效率，mysql没有像MariaDB那样有自己的INDEX STATISTIC可以用来检测未使用的索引，所以我们在这里继续使用工具，pt-index-usage来分析我们的慢查询日志，来检验索引的使用情况：

我们使用命令：pt-index-usage -uroot -p‘’密码 mysql_slow.log来分析我们的慢查询日志,需要注意的一点是，如果我们的mysql服务器进行了主从的操作，那么我们在查询分析索引的时候要把所有的日志一起分析，避免发生在一台从服务器上该索引没有使用而在另一台从服务器上该索引在使用，而我们把索引误删的情况。