覆盖索引有何用?

覆盖索引有何用?

通常开发人员会根据查询的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条件包含索引列或者复合索引的前导列;查询结果的字段长度尽可能少。

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

posted @ 2016-11-29 15:04  成风魄郎  阅读(11554)  评论(4编辑  收藏  举报