mysql索引优化

​ 描述使用explain命令中的每列所代表的含义，以及出现某些慢查询时的一些优化。

通过explain命令可以知道MySQL是怎么执行语句，包括有关如何连接表以及连接表的顺序、扫描的行数、是否使用索引、排序使用的是内存排序还是磁盘排序等等。所以一般的语句优化手段都是通过explain来查询语句的处理过程，修改全表扫描的语句，优化查询效率。

上图是一个最简单的explain select * from xxx的执行结果，以下依次解释每一项的含义

id

假设执行语句为explain select * from words where id in (select id from words where word like 'aa%')
输出结果为:

这里的id值有两个1和2，实际上对于这条查询语句来说，是先执行了括号内的查询，在将括号内的结果赋予外层查询语句进行第二次查询，所以在expian结果有两条记录，id值越高越先执行，id值相同时从上往下执行。

select_type

表示查询的类型：

1. SIMPLE: 简单语句，比如select * from xxx 。
2. SUBQUERY: 子查询
3. PRIMARY: 主查询，最外层查询，表示最后一个到它
4. DERIVED: 衍生表，查询语句中会生成新的表，比如select * from words as t1, (select false) as t2
5. UNION: 连接表
6. UNION RESULT: 连接结果，一般与UNION配套出现

table

表示某一个子查询使用到的表是什么，一般的查询语句直接就是某一个表，但是一些特殊处理，比如select * from words as t1, (select false) as t2这里的table就是空，因为它是临时生成的不是数据库中存在的表结构，再或者是使用union联合的时候，select_type=UNION RESULT的子查询是将两个查询结果联合，所以会标识是那两个id值所对应的查询进行联合<union id1, id2>

type

type表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：
system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

1. system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。
2. const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，速度非常快。
3. eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
4. ref：常用于非主键和唯一索引扫描。
5. ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行
6. index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
7. unique_subquery：类似于eq_ref，条件用了in子查询
8. index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
9. range：常用于范围查询，比如：between … and 或 In 等操作，如果是这个类型需要注意，可能会因为数据量的原因导致索引失效
10. index：全索引扫描
11. ALL：全表扫描

possible_keys和key

possible_key这个一般来说用不上，意思就是理论上可能会使用到的索引，但是实际执行时可能会用不上，说了等于没说。
key表示使用到的索引。

key_len

使用的到的索引长度，key_len可以衡量索引的好坏，key_len越小，索引的效果越好，但是要注意如果索引是建立在一个字符串字段上，且字符串字段长度比较长，进行索引时可能会将字符串截断，也就是这一列的前n个字符参与索引。
MySQL innodb引擎对于索引的长度是有限制的，最大为767字节，不同字符编码方式对应的字节数不一样，比如最常用的utf8mb4字符集是4字节字符集，767/4=191，所以在字符串上创建索引的前191字符才会参与索引。

ref

显示索引的哪一列被使用了，如果是where word = 'name'，那么显示的就是const

rows

该列表示MySQL估算要找到我们所需的记录，需要读取的行数。对于InnoDB表，此数字是估计值，并非一定是个准确值，InnoDB的优化器也会通过需要扫描的行数来判断选择哪个索引。可以通过analyze table t命令，来重新统计索引信息。

Extra

Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句
Using index ：表示是否用了覆盖索引。
Using temporary： 表示是否使用了临时表,性能特别差，需要重点优化。一般多见于group by语句，或者union语句。
Using where ： 表示使用了where条件过滤.
Using index condition：MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

常用优化手段

1. in内容过多

使用in查询最好内容不要超过200个，因为in查询底层是通过n*m的方式去搜索，也就是每一个条件逐条遍历，默认临界值eq_range_index_dive_limit=200，如果超过这个值，可能会导致代价计算存在问题，从而导致mySQL选择的索引不准确。

2. 磁盘排序

如果查询语句中存在order by但是Extra中没有Using filesort那么恭喜你，你的排序命中了索引，沿用了索引已然有序的特性直接查询出数据。但是实际情况并不是这么刚好，不命中索引的情况下，排序下的Extra的结果为Using filesort。排序分为全字段排序和rowid排序。至于是使用全字段排序和rowid排序主要影响因素是max_length_for_sort_data参数，当单行长度超过这个参数值时使用的就是rowid排序。rowid需要回表，查询性能比全字段查询差。

3.不等号

查询使用不等号或者是not in这些时，索引形同虚设，这其实是被优化器处理了，因为优化器认为即便是走索引，还是需要扫描很多行，所以它觉得不划算，所以直接不走索引。还有NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE都会导致索引失效。

4.group by

explain select wrod, num from words group by word

可以看到在Extra中有Using filesort和Using temporary，表示使用到了排序和临时表。
group by的执行流程：

1. 创建内存临时表，表里设置字段(word, num)
2. 全表扫描words，依次取出word的值，判断临时表中是否有这个值，如果没有就插入（word, 1），如果有就对这一行的num加一。
3. 遍历完成后，再对word排序后返回结果。

优化方式：可以对group by后面的字段添加索引，提高步骤1的执行效率；使用order by null，既然默认是要排序，那么手动设置为不要排序，提高步骤3的执行效率。

5. delete + in

delete form xx where xx in (xx)这个删除语句不会命中索引，但是select * form xx where xx in (xx) 这个是会命中索引的，原因是对于这个查询语句实际上将SQL优化为可以命中索引的方式。

6. asc和desc

如果出现asc和desc混用，也会导致索引失效。