覆盖索引有何用?
通常开发人员会根据查询的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条件包含索引列或者复合索引的前导列;查询结果的字段长度尽可能少。
相关文章
- Tensor的操作1(索引、切片、维度变换)
- Lucene 4.7 --创建索引
- 【转载】8天学通MongoDB——第四天 索引操作
- elasticsearch最全详细使用教程:入门、索引管理、映射详解、索引别名、分词器、文档管理、路由、搜索详解
- mysql数据库索引失效的常见场景(上)
- DB索引、索引覆盖、索引优化
- SQL Server查询性能优化——覆盖索引(二)
- 一次索引实践
- 复合索引,排第一的效果
- Neo4j 3.5发布,在索引方面大幅增强
- mysql 全表扫描、全索引扫描、索引覆盖(覆盖索引)
- C#索引器
- MySQL索引:回表、索引覆盖,最左匹配原则、索引下推
- 覆盖索引小结
- 前缀索引小结
- Mysql索引覆盖
- java处理数据库唯一索引的异常
- mysql 数据操作。唯一索引