一、什么是 聚集索引、非聚集索引
在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
