18、什么是覆盖索引？

什么是覆盖索引？

通常开发人员会根据查询的where条件来创建合适的索引，但是优秀的索引设计应该考虑到整个查询。其实mysql可以使用索引来直接获取列的数据。如果索引的叶子节点包含了要查询的数据，那么就不用回表查询了，也就是说这种索引包含（亦称覆盖）所有需要查询的字段的值，我们称这种索引为覆盖索引。

注：引入数据表t_user,插入约1千万条记录，用作下文例子使用。

1、工欲善其事，必先利其器

 explain命令是查看查询优化器如何决定执行查询的主要方法。要使用此命令，只需要在select关键字之前添加这个命令即可。当执行查询时，它会返回信息，显示出执行计划中的每一部分和执行的次序，而并非真正执行这个查询。如下所示，是执行explain的显示结果，其中sql语句中的\G表示将输出按列显示：

当发起一个被索引覆盖的查询时，在explain的Extra列可以看到 Using index的标识。

 2、场景查询表中name列有值的记录数

（1）查询表中name列有值的记录数

 

查询语句用SQL_NO_CACHE关键字来禁止缓存查询结果。此查询耗时6.43秒

（2）执行计划

 

从图的执行计划得知，type:ALL，表示MySQL扫描整张表，从头到尾去找到需要的行。下面对此查询列建立索引。

 （3）为name列建立索引

（4）重新执行的查询sql

 

如图所示，为name列建立索引之后，重新执行查询。此时查询耗时3.80秒，比未加索引提高了2.63秒

（5）重新查看执行计划

 

从图的查询计划可知，type：index，这个跟全表扫描一样，只是MySQL扫描表时按索引次序进行而不是行。但是看到Extra：Using index，说明MySQL正在使用覆盖索引，它只扫描索引的数据，而不是按索引次序的每一行。它比按索引次序全表扫描的开销少很多。

 3、分页查询email

 （1）分页查询email

 

从图可知，分页查询耗时53.99

 （2）分页查询执行计划

 如图所示，type：All，说明MySQL进行了全表扫描。下面在password和email列上创建联合索引。

 

（3）添加联合索引

 

（4）重新分页查询

 

如图所示，分页查询基本不耗时间。

（5）重新执行查询计划

 

从图可知，Extra：Using index，MySQL使用了覆盖索引进行查询。查询效率得到极大的提升。

 4、覆盖索引总结

 回想一下，如果查询只需要扫描索引而无须回表，将带来诸多好处。

（1）索引条目通常远小于数据行大小，如果只读取索引，MySQL就会极大地减少数据访问量。

（2）索引按照列值顺序存储，对于I/O密集的范围查询会比随机从磁盘中读取每一行数据的I/O要少很多。

（3）InnoDB的辅助索引（亦称二级索引）在叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，则可不必对主键索引进行二次查询了。

覆盖索引就是从索引中直接获取查询结果，要使用覆盖索引需要注意select查询列中包含在索引列中；where条件包含索引列或者复合索引的前导列；查询结果的字段长度尽可能少。