本文主要是介绍MySQL 中 聚集索引、非聚集索引、覆盖索引、索引下推 到底是什么,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
一、什么是 聚集索引、非聚集索引
在MySQL
数据库中,索引是提高查询效率的关键。而聚集索引、非聚集索引、覆盖索引、索引下推其实是索引优化的重要策略之一。那这些名词的含义到底是什么呢?
在开始分析前,先来了解下 B+
树的索引结构 和 回表查询:
B+ 树结构
B+
树是 B
树的变体,将树的结构分为了叶子节点和非叶子节点。其中非叶子节点不存储具体数据,只存放主键和指向下一级数据的指针。而叶子节点在最尾端,存放主键和指向数据行的地址。叶子节点和非叶子节点采用指针连接,例如下面结构:
也就是叶子节点包含了主键ID
和数据行信息,找到具体叶子节点也就找到了具体数据。
什么是回表查询
回表查询主要针对于非主键索引的情况下,在主键索引中叶子节点包含了主键和数据行信息。但是非主键索引的叶子节点就不是包含主键和数据行信息了,而是索引和该数据的主键ID
,所以要拿到完整的行信息,就需要再拿着主键去主键索引中查询一次获得数据,这个过程就叫做回表查询。
了解到这里就可以先来看下 聚集索引 和 非聚集索引了。
聚集索引
聚集索引其实就是上面的 B+
树结构,也就是主键索引 就是 聚集索引,它按照数据存储顺序排列,叶子节点包含了整行数据,数据行的物理顺序和索引顺序一致,这样可以减少磁盘 I/O
操作,提高查询效率。
假设有一个名为 user
的表,其中包含 id
(主键)、name
和 age
和 sex
字段。如果我们在id
上创建了聚集索引,那么当我们根据 id
进行查询时,数据库引擎会直接通过索引找到对应的数据行,而不需要再进行二次查找,从而提高查询速度。
非聚集索引
非聚集索引其实也就是上面提到的非主键索引的情况下,非聚集索引并不影响数据在磁盘上的存储顺序,而是在磁盘上额外创建一个索引文件,用于存储索引的键和指向对应数据的主键。当根据非聚集索引的列进行查询时,会首先在索引文件中找到对应的主键,然后再查询到实际的数据行。
还是以 user
表为例,如果我们在 name
字段上创建了一个非聚集索引,那么当根据 name
进行查询时,会首先查找索引文件中的 name
键,获得主键 id
,然后再通过主键 id
找到对应的数据行。这种情况下可以加速基于非主键字段的查询,但相比聚集索引会稍慢一些。
二、什么是覆盖索引、索引下推
上面讲述的 聚集索引 和 非聚集索引主要关注点在单索引上的设计不同,而 覆盖索引 和 索引下推,则会关注到 联合索引 或 多个索引 的情况下。
覆盖索引
覆盖索引就是指查询的结果可以直接通过索引返回,而不必再去查找表的数据。当查询语句所需的字段都包含在索引中时,就可以利用覆盖索引,减少对数据表的访问,提高查询性能。也就是减少回表查询的过程。
继续以上面的users
表为例,如果我们需要查询所有满足 age > 30
条件的 id
、age
、name
字段,其中在 age
和 name
字段上创建了联合索引,那么数据库引擎可以直接通过索引返回满足条件的数据,而不必再去查找原始数据行信息,从而提高查询效率。
但是如何还要查询 sex
字段,此时覆盖索引就不满足了,需要进行回表查询补充完整的数据信息。
索引下推
索引下推(Index Condition Pushdown
,简称ICP
)是MySQL 5.6
版本引入的一种优化技术,将应该在 server
层进行筛选的条件,尽量下推到存储引擎层来进行筛选判断,目的也是减少 回表查询 的过程。它可以在索引的扫描过程中,对索引条件进行判断和过滤,减少不必要的行访问,从而提高查询性能。
在 MySQL
中该功能默认是开启的,可以使用下面指令查看:
select @@optimizer_switch;
如果想要关闭或开启可以使用下列指令:
关闭索引下推:
set optimizer_switch = 'index_condition_pushdown=off'
开启索引下推:
set optimizer_switch = 'index_condition_pushdown=on'
这里举个例子来理解下索引下推,还是上面 user
表,假如给 name
和 age
字段设置了联合索引,然后进行查询:
select id,name,age from user where name like '小%' and age > 15
当没有索引下推的时候:
- 通过联合索引找到
name like '小%'
的主键ID
。 - 然后进行回表找个数据行信息,交给
server
。 server
再根据其余条件进行筛选。
当有索引下推的时候:
- 通过联合索引找到
name like '小%'
的索引信息。 - 判断索引中是否有
age
,如果有则一起筛选。 - 返回信息给到
server
。
索引下推的目的是为了减少回表次数,主要减少磁盘的IO
操作。主要针对于非聚集索引的一种优化手段。
通过 explain
也可以看出是否进行了索引下推:
explain select * from user where name like '小%' and age > 15
当开启了 索引下推 时,Extra
使用的是 Using index condition
当关闭 索引下推 时,Extra
使用的是 Using where
这篇关于MySQL 中 聚集索引、非聚集索引、覆盖索引、索引下推 到底是什么的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!