0


详解MySQL覆盖索引、索引下推

1.覆盖索引

1.1.概述

覆盖索引,是为了避免“回表查询”,从而降低查询耗时的一种使用索引的方法,所以要聊覆盖索引首先我们要知道什么是"回表查询,“回表查询”是因为MySQL的索引结构决定的,是因为非聚集索引要找聚集索引拿数据而出现的现象,所以我们又要先了解MySQL中的聚集索引和非聚集索引。

文章的脉络就是先聊聚集索引、非聚集索引是怎么带来了“回表查询”的问题,然后怎么用用覆盖索引解决这个问题。

1.2.聚集索引、非聚集索引

关于索引基础、B树、B+树等相关的详细内容可以看博主之前的两篇文章:

数据结构(8)树形结构——B树、B+树(含完整建树过程)_b+树构造过程__BugMan的博客-CSDN博客

详解MySQL索引__BugMan的博客-CSDN博客

众所周知mysql的索引有两种:

  • 聚集索引
  • 非聚集索引

聚集索引:

聚集索引的叶子节点上挂着的是一条分支上的所有索引对应的数据。

MySQL会默认为每张表维护一个聚集索引,如果表有主键,那么这个聚集索引就是主键索引,如果没有主键,MySQL仍然会为每个表维护一个隐藏的聚集索引。在innodb引擎中,这个隐藏的聚集索引是一个6字节长的ROWID,每个数据行都会有一个隐藏的ROWID。

非聚集索引:

非聚集索引叶子结点上挂的是索引值和对应值指向的聚集索引的值,也就是说真正的数据一定是要找聚集索引拿的。

1.3.回表查询

上面聊完非聚集索引一定会找聚集索引拿数据后,其实“回表查询”问题就已经引出来了,所谓的“回表查询”,指的是当我们使用非聚集索引查询时我们是无法直接得到数据,而需要通过叶子结点上的聚集索引的值去聚集索引的B+树里查找数据的过程。

“回表查询”带来的问题一目了然,索引是存在磁盘上的,查完一棵B+树后,还要去查第二棵B+树进行遍历匹配,查第二棵B+树就带来了额外的IO耗时,磁盘IO本来就是磁盘旋转+磁头上下摆动的机械动作,这个动作在计算机的运行体系里是个极度慢的动作,极度的耗时。

1.4.覆盖索引

覆盖索引,指的是select具体字段代替select *,将复合索引建立在要查询的具体字段上,这样在非聚集索引上,也就是第一颗B+树上就能拿到数据,从而不用“回表查询”。

做个实验:

我建了一个sys_user表,里面随即插入了100万条数据:

然后在username上建立一个索引:

create index index_username on sys_user(username);

未使用覆盖索引查询其中一条数据:

explain select * from sys_user where username='test_852107';

使用覆盖索引查询其中一条数据:

explain select username from sys_user where username='test_852107';

2.索引下推

索引下推,是自MySQL5.6版本引入的一个新特性,目的也是减少“回表查询”,从而提升整体的查询效率。

假设表中有三个字段name、age、sex,

SQL写成这样是没问题的,完美满足左前缀原则:

select * from tuser where name ='zou' and age=10 and sex=1;

但如果SQL写成这样,就会出问题,索引断开了,sex这个索引值是没有用上的:

select * from tuser where name ='zou' and sex=1;

在5.6之前索引断了就不会往下走了,匹配到的所有name='zou'的数据,挨个“回表”,无疑这将会造成很多无意义的额外IO开销:

而索引下推的意思是,索引断裂、走不下去后,不会立即回表,还会向下推一步再继续比较其它索引字段,从而减少无意义的额外IO。以上表为例,就是会匹配到name='zou'后发现索引断掉了也不会立即回表,而是继续向下比对sex是不是等于1:


本文转载自: https://blog.csdn.net/Joker_ZJN/article/details/130631391
版权归原作者 _BugMan 所有, 如有侵权,请联系我们删除。

“详解MySQL覆盖索引、索引下推”的评论:

还没有评论