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

当没有索引下推的时候：

1. 通过联合索引找到 name like '小%' 的主键 ID 。
2. 然后进行回表找个数据行信息，交给 server 。
3. server 再根据其余条件进行筛选。

当有索引下推的时候：

1. 通过联合索引找到 name like '小%' 的索引信息。
2. 判断索引中是否有 age ，如果有则一起筛选。
3. 返回信息给到 server 。

索引下推的目的是为了减少回表次数，主要减少磁盘的IO操作。主要针对于非聚集索引的一种优化手段。

通过 explain 也可以看出是否进行了索引下推：

explain select * from user where name like '小%' and age > 15

当开启了 索引下推 时，Extra 使用的是 Using index condition

当关闭 索引下推 时，Extra 使用的是 Using where