MySQL(十二)：索引分析
数据库优化是一个很常见的面试题，下面就针对这一问题详细聊聊如何进行索引与sql的分析与优化。
一、执行计划（EXPLAIN）
MySQL 提供了一个 EXPLAIN 命令，它「可以对 sql语句进行分析，并输出sql执行的详细信息」，可以让我们有针对性的优化。例如：
1 | explain select * from student where id > 2; |
这里需要注意一下版本差异
「MySQL 5.6.3」
MySQL 5.6.3以前只能 EXPLAIN SELECT ；MYSQL 5.6.3以后可以 EXPLAIN SELECT，UPDATE，DELETE
「MySQL 5.7」
MySQL 5.7以前想要显示 partitions 需要使用 explain partitions 命令；想要显示filtered 需要使用 explain extended 命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。
1.1执行计划详解
「在使用索引的时候首先应该学会分析SQL的执行，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，可以知道MySQL是如何处理SQL语句」。
使用格式：
1 | #explain sql语句 如下： |
从执行计划输出的结果可以看出，它有很多的字段，每个字段都有自己的含义
「id」
「选择标识符」：在一个查询语句中每个【SELECT】关键字都对应一个唯一的 id。两种例外的情况：
「id相同」优化器对子查询做了「半连接（semi-jion）优化」时，两个查询的 id 是一样的
1
explain select * from student where id in(select id from student where id > 1);
「id为null」
1
explain select * from student union select * from student where id > 1;
因为「union会对结果去重，内部创建了一个 <union1,2> 名字的临时表，把查询 1 和查询 2 的结果集都合并到这个临时表中，利用唯一键进行去重，这种情况下查询 id 就为 NULL」。
「select_type」
「查询的类型」，常用的值如下：
查询的类型 类型含义 SIMPLE 简单的select查询，不包含子查询或union查询，是最常见的。 PRIMARY 若查询中包含有子查询，最外层查询会别标记为PRIMARY UNION 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED SUBQUERY 在SELECT或WHERE列表中包含了子查询 DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生);MySQL会递归执行这些子查询, 把结果放在临时表里。 UNION RESULT 从UNION表获取结果的SELECT DEPENDENT SUBQUERY 在SELECT或WHERE列表中包含了子查询,子查询基于外层 UNCACHEABLE SUBQUREY 无法被缓存的子查询 「table」
输出结果集的表，即查询的表名
「partitions」
匹配的分区
「type」
表示存储引擎查询数据时采用的方式。它「可以判断出查询是全表扫描还是基于索引的部分扫描」。
常用属性值如下，从上至下效率依次增强。
- ALL：表示全表扫描，性能最差。
- index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
- range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
- ref：表示使用非唯一索引进行单值查询。
- eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一 行结果。
- const：表示使用主键或唯一索引做等值查询，常量查询。
- NULL：表示不用访问表，速度最快。
「possible_keys」
表示在某个查询语句中，对某个表执行单表查询时「可能用到的索引列表」
「key」
表示在某个查询语句中，列表示「实际用到的索引」有哪些。
「key_len」
表示查询使用索引的字节数量。可以判断是否全部使用了组合索引。
如果键是 NULL，则长度为 NULL。「使用的索引的长度」。在不损失精确性的情况下，长度越短越好 。
「ref」
当使用索引列等值匹配的条件去执行查询时，ref 列展示「与索引列作等值匹配的对象」。
「rows」
「扫描出的行数(估算的行数)」，
- 如果查询优化器决定使用全表扫描的方式对某个表执行查询时，rows 列就代表预计需要扫描的行数；
- 如果使用索引来执行查询时，rows 列就代表预计扫描的索引记录行数。
「filtered」
按表条件过滤的行百分比
- 如果是全表扫描，filtered 值代表满足 where 条件的行数占表总行数的百分比
- 如果是使用索引来执行查询，filtered 值代表从索引上取得数据后，满足其他过滤条件的数据行数的占比。
「Extra」
Extra 是 EXPLAIN 输出中另外一个很重要的列，各种操作都会在Extra提示相关信息，常见几种如下：
Using where：表示查询需要通过索引回表查询数据。
Using index：表示查询需要通过索引，索引就可以满足所需数据。
Using filesort：表示查询出来的结果需要额外排序，
数据量小在内存排序，数据量大在磁盘排序，因此有Using filesort 建议优化。
Using temprorary：查询使用到了临时表，一般出现于去重、分组等操作。
二、回表查询
在之前《索引基本原理》 中提到InnoDB索引有聚簇索引和辅助索引。
- 聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。
- 辅助索引的叶子节点存储的是主键值和索引字段值
由上图可知：「通过辅助索引无法直接定位行记录，通常情况下，需要扫两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，即回表查询」。性能比扫一遍索引树低。
三、覆盖索引
索引覆盖：「只需要在一棵索引树上就能获取SQL所需的所 有列数据，无需回表，速度更快」
覆盖索引形式：，搜索的索引键中的字段恰好是查询的字段
实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。
四、最左前缀原则
在之前《索引基本原理》 中提到组合索引的概念，在组合索引的使用中最关键的就是最左前缀原则。
「组合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列， 那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效」。
五、索引与排序
5.1排序方式
MySQL查询支持filesort和index两种方式的排序，
- filesort是先把结果查出，然后在缓存或磁盘进行排序 操作，效率较低。
- index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。
5.2 排序方式的选择
「使用index方式的排序的场景」
ORDER BY 子句索引列组合满足索引最左前列
1
explain select id from user order by id; //对应(id)、(id,name)索引有效
WHERE子句+ORDER BY子句索引列组合满足索引最左前缀
1
2#对应(age,name)组合索引
explain select id from user where age=18 order by name;
「使用filesort方式的排序的场景」
对索引列同时使用了ASC和DESC
1
2#对应(age,name)组合索引
explain select id from user order by age asc,name desc;WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in 等）
1
2#对应(age,name)组合索引
explain select id from user where age>10 order by name;ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前缀
1
2#对应(age,name)组合索引
explain select id from user order by name;使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引
1
2#对应(name)、(age)两个索引
explain select id from user order by name,age;WHERE子句与ORDER BY子句，使用了不同的索引
1
2#对应(name)、(age)索引
explain select id from user where name='tom' order by age;WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式
1
2#对应(age)索引
explain select id from user order by abs(age);5.3排序算法
filesort有两种排序算法：双路排序和单路排序。
- 双路排序：需要两次磁盘扫描读取，得到最终数据。第一次将排序字段读取出来，然后排序；第二 次去读取其他字段数据。
- 单路排序：从磁盘查询所需的所有列数据，然后在内存排序将结果返回。
- 如果查询数据超出缓存 sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。
- 解决方案：少使用select *；增加sort_buffer_size容量和max_length_for_sort_data容量。
如果Explain分析SQL时Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。如果Extra属性显示Using index时，表示覆盖索引，所有操作在索引上完成。