覆盖索引有何用？

覆盖索引有何用？

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

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

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

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

图1.1 explain显示查询执行计划

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

 

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

图2.1 查询name列有值的记录数

 

图2.2 执行计划

       如上图2.1所示，其中查询语句用SQL_NO_CACHE关键字来禁止缓存查询结果。此查询耗时6.43秒。从图3的执行计划得知，type:ALL，表示MySQL扫描整张表，从头到尾去找到需要的行。下面对此查询列建立索引。

图2.3 为name列建立索引

 

图2.4 重新执行的查询sql

图2.5 重新查看执行计划

    如图2.3所示，为name列建立索引之后，重新执行查询。此时查询耗时3.80秒，比未加索引提高了2.63秒。从图2.5的查询计划可知，type：index，这个跟全表扫描一样，只是MySQL扫描表时按索引次序进行而不是行。但是看到Extra：Using index，说明MySQL正在使用覆盖索引，它只扫描索引的数据，而不是按索引次序的每一行。它比按索引次序全表扫描的开销少很多。

3分页查询email

图3.1 分页查询email

图3.2 分页查询执行计划

    从图3.1可知，分页查询耗时53.99，如图3.2所示，type：All，说明MySQL进行了全表扫描。下面在password和email列上创建联合索引。

图3.3 添加联合索引

图3.4 重新分页查询

图3.5 重新执行查询计划

如图3.4所示，分页查询基本不耗时间。从图3.5可知，Extra：Using index，MySQL使用了覆盖索引进行查询。查询效率得到极大的提升。

4覆盖索引总结

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

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

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

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

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

-------------------------------------------------------------------------------------------------
写点代码，写点科技。
微信公众号「软件开发资讯」，遇见了不妨就关注看看。