一、什么是 聚集索引、非聚集索引在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中该功能默认是开启的可以使用下面指令查看selectoptimizer_switch;如果想要关闭或开启可以使用下列指令关闭索引下推setoptimizer_switchindex_condition_pushdownoff开启索引下推setoptimizer_switchindex_condition_pushdownon这里举个例子来理解下索引下推还是上面user表假如给name和age字段设置了联合索引然后进行查询selectid,name,agefromuserwherenamelike小%andage15当没有索引下推的时候通过联合索引找到name like 小%的主键ID。然后进行回表找个数据行信息交给server。server再根据其余条件进行筛选。当有索引下推的时候通过联合索引找到name like 小%的索引信息。判断索引中是否有age如果有则一起筛选。返回信息给到server。索引下推的目的是为了减少回表次数主要减少磁盘的IO操作。主要针对于非聚集索引的一种优化手段。通过explain也可以看出是否进行了索引下推explainselect*fromuserwherenamelike小%andage15当开启了 索引下推 时Extra使用的是Using index condition当关闭 索引下推 时Extra使用的是Using where