一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第9天，点击查看活动详情
前几篇文章讲到查询优化、join连接优化、子查询优化、order by排序优化和group by分组优化，下面我来介绍MySQL优化器的索引相关的优化，index_cover索引覆盖、index_condition_pushdown索引下推（icp）
1.index_cover索引覆盖
什么是索引覆盖？
概念：非主键索引也就是非聚集索引的索引字段和主键在一个查询语句select包含了查询的字段叫做索引覆盖。 通俗点来讲就是，select xxxfield1,xxxfield2 ...... from ,在是select 和 from之间的字段是索引字段+主键的子集（可以等于）。
举个例子：
当前表student有联合索引 idx_classId_id_name，如下图：
执行如下SQL：explain select classId,name from student where name like 'x%' and classId = 1;
根据前面我们讲到的联合索引知识我们可以知道，对于联合索引需要遵守最左匹配原则，也就是说索引字段需要按照索引的顺序使用，明显上述SQL并没有遵循这个规则，显然不会使用到索引，我们看下执行计划，如下图：
what？怎么回事，不是说好不能使用到索引吗，key_len的值为5，说明使用到了classId索引字段，但是这个SQL并没有遵循最左匹配原则啊为什么还是会使用到索引呢？会不会是MySQL优化器将上面的SQL优化成这样子了：
explain select classId,name from student where name classId = 1 and like 'x%';
这样子就符合key_len长度为5的情况了，也符合联合索引的最左匹配原则，且刚好只能使用classId索引字段，那如果我们不加索引条件呢？那肯定不会会使用到索引了吧！执行如下SQL，查看执行计划：
explain select classId,name from student;
可以发现还是使用到了索引，并且key_len为72居然还是完全使用索引，这就是索引覆盖，为什么会这样呢？
其实也好理解，因为对这些字段建立了联合索引，所以在索引数据里包含了这些索引字段和主键，并且索引说排好序的，还避免了随机io，相对于全表扫描获取数据来说，直接去索引树获取数据更快。
如果查询里的字段在索引字段里不存在会是什么情况，看如下SQL执行计划：
explain select classId,name,age from student;
可以看到是全表扫描了，因此索引覆盖的前提就是查询字段不能不存在索引字段。
小结：1.索引覆盖可以避免回表 2.避免随机io变成顺序io。这两点都可以加快查询速度。
2.前缀索引的使用场景
给表student新增字段email,alter table student add email varchar(50);
新增前缀索引idx_email_pre, alter table student add index idx_email_pre(email(6));
执行如下SQL：explain select * from student where email = '159904@qq.com' ;
可以发现索引命中，但是看起来好像和普通索引没什么区别，其实是有的前缀索引更加节省空间，并且在数据不重复基数较大的情况下，速度会比普通索引快，但是如果重复数据较多，那不能建立前缀索引，前缀索引的执行是根据前几位去索引树匹配，如果重复数据较多匹配度数据也很多，匹配次数和判断次数成正比。
3.index_condition_pushdown索引下推
新建联合索引idx_classId_name_email;
alter table student add index idx_classId_name_email(classId,name,email);
执行如下SQL：
explain select * from student where classId < 200 and name like 'x%' and email like '1%'
根据联合索引最左匹配原则只有索引字段classId会生效，所以key_len等于5，执行计划如下图：
但是注意看执行计划字段extra有输出using index condition,这就是索引下推，有什么用？
我们来看下执行效率：
执行SQL:select * from student where classId < 200 and name like 'x%' and email like '1%'如下
关闭索引下推，set optimizer_switch = 'index_condition_pushdown=off',临时不使用直接执行：
select /*+ no_icp(student) */ * from student where classId < 200 and name like 'x%' and email like '1%'
执行如下：
可以看到索引下推的效果还是很大的。MySQL默认是开启索引下推的。
