1.5万字+30张图盘点索引常见的11个知识点（中篇）

二级索引

二级索引也被称为非聚簇索引，本身也就是一颗B+树，一个二级索引对应一颗B+树，但是二级索引B+树存储的数据跟聚簇索引不一样。

聚簇索引前面也说了，叶子节点存的就是我们插入到数据库的数据，非叶子节点存的就是数据的主键id和对应的数据页号。

而二级索引叶子节点存的是索引列的数据和对应的主键id，非叶子节点除了索引列的数据和id之外，还会存数据页的页号。

前面提到的数据页，其实真正是叫索引页，因为叶子节点存的是实际表的数据，所以我就叫数据页了，接下来因为真正要讲到索引了，所以我就将二级索引的页称为索引页，你知道是同一个，但是存储的数据不一样就可以了。

单列索引

假设，我们现在对name字段加了一个普通非唯一索引，那么name就是索引列，同时name这个索引也就是单列索引

此时如果往表中插入三条数据，那么name索引的叶子节点存的数据就如下图所示

mysql会根据name字段的值进行排序，这里我假设张三排在李四前面，当索引列的值相同时，就会根据id排序，所以索引实际上已经根据索引列的值排好序了。

这里肯定有小伙伴疑问，name字段存储的中文也可以排序么？

答案是可以的，并且mysql支持很多种排序规则，我们在建数据库或者是建表的时候等都可以指定排序规则，并且后面文章涉及到的字符串排序都是我随便排的，实际情况可能不一样。

对于单个索引列数据查找也是跟前面说的聚簇索引一样，也会对数据分组，之后可以根据二分查找在单个索引列来查找数据。

当数据不断增多，一个索引页存储不下数据的时候，也会用多个索引页来存储，并且索引页直接也会形成双向链表

当索引页不断增多是，为了方便在不同索引页中查找数据，也就会抽取一个索引页，除了存页中id，同时也会存储这个id对应的索引列的值

当数据越来越多越来越多，还会抽取，也会形成三层的一个B+树，这里我就不画了。

联合索引

除了单列索引，联合索引其实也是一样的，只不过索引页存的数据就多了一些索引列

比如，在name和age上建立一个联合索引，此时单个索引页就如图所示

先以name排序，name相同时再以age排序，如果再有其它列，依次类推，最后再以id排序。

相比于只有name一个字段的索引来说，索引页就多存了一个索引列。

最后形成的B+树简化为如下图

小结

其实从上面的分析可以看出，聚簇索引和非聚簇索引主要区别有以下几点

• 聚簇索引的叶子节点存的是所有列的值，非聚簇索引的叶子节点只存了索引列的值和主键id
• 聚簇索引的数据是按照id排序，非聚簇索引的数据是按照索引列排序
• 聚簇索引的非叶子节点存的是主键id和页号，非聚簇索引的非叶子节点存的是索引列、主键id、页号

由于后面这个索引树会经常用到，为了你方便比较，所以我根据上面索引树的数据在表中插入了对应的数据，sql在文末

实际情况下索引B+树可能并不是按照我图中画出来的那样排序，但不耽误理解。

回表

讲完二级索引，接下来讲一讲如何使用二级索引查找数据。

这里假设对name字段创建了一个索引，并且表里就存了上面示例中的几条数据，这里我再把图拿过来

那么对于下面这条sql应该如何执行？

select * from `user` where name = '赵六';

由于查询条件是​​name = '赵六'​​，所以会走name索引

整个过程大致分为以下几个步骤：

• 从最上面那层索引页开始二分查找，我们图中就是索引页113，如果索引页113上面还有一层，就从上面一层二分查找
• 在索引页113查找到​​赵六​​​在​​王五​​​和​​刘七​​​之间，之后到​​王五​​​对应的索引页111上去查找​​赵六​​
• 在索引页111找到​​赵六​​的第一条记录，也就是id=4的那条
• 由于是​​select *​​，还要查其它字段，此时就会根据id=4到聚簇索引中查找其它字段数据，这个查找过程前面说了很多次了，这个根据id=4到聚簇索引中查找数据的过程就被称为回表
• 由于是非唯一索引，所以​​赵六​​​这个值可能会有重复，所以接着就会在索引页111顺着链表继续遍历，如果name还是​​赵六​​​，那么还会根据id值进行回表，如此重复，一直这么遍历，直至name不再等于​​赵六​​为止，对于图示，其实就是两条数据

从上面的二级索引的查找数据过程分析，就明白了回表的意思，就是先从二级索引根据查询条件字段值查找对应的主键id，之后根据id再到聚簇索引查找其它字段的值。

覆盖索引

上一节说当执行​​select * from user where name = '赵六';​​​这条sql的时候，会先从索引页中查出来​​name = '赵六';​​对应的主键id，之后再回表，到聚簇索引中查询其它字段的值。

那么当执行下面这条sql，又会怎样呢？

select id from `user` where name = '赵六';

这次查询字段从​​select *​​​变成​​select id​​，查询条件不变，所以也会走name索引

所以还是跟前面一样了，先从索引页中查出来​​name = '赵六';​​对应的主键id之后，惊讶的发现，sql中需要查询字段的id值已经查到了，那次此时压根就不需要回表了，已经查到id了，还回什么表。

而这种需要查询的字段都在索引列中的情况就被称为覆盖索引，索引列覆盖了查询字段的意思。

当使用覆盖索引时会减少回表的次数，这样查询速度更快，性能更高。

所以，在日常开发中，尽量不要select * ，需要什么查什么，如果出现覆盖索引的情况，查询会快很多。

索引下推

假设现在对表建立了一个name和age的联合索引，为了方便理解，我把前面的图再拿过来

接下来要执行如下的sql

select * from `user` where name > '王五' and age > 22;

在MySQL5.6(不包括5.6)之前，整个sql大致执行步骤如下：

• 先根据二分查找，定位到​​name > '王五'​​的第一条数据，也就是id=4的那个赵六
• 之后就会根据id=4进行回表操作，到聚簇索引中查找id=4其它字段的数据，然后判断数据中的age是否大于22，是的话就说明是我们需要查找的数据，否则就不是
• 之后顺着链表，继续遍历，然后找到一条记录就回一次表，然后判断age，如此反复下去，直至结束

所以对于图上所示，整个搜索过程会经历5次回表操作，两个赵六，两个刘七，一个王九，最后符合条件的也就是id=6的赵六那条数据，其余age不符和。

虽然这么执行没什么问题，但是不知有没有发现其实没必要进行那么多次回表，因为光从上面的索引图示就可以看出，符合​​name > '王五' and age > 22​​的数据就id=6的赵六那条数据

所以在MySQL5.6之后，对上面的​​age > 22​​判断逻辑进行了优化

前面还是一样，定位查找到id=4的那个赵六，之后就不回表来判断age了，因为索引列有age的值了，那么直接根据索引中age判断是否大于22，如果大于的话，再回表查询剩余的字段数据（因为是​​select *​​），然后再顺序链表遍历，直至结束

所以这样优化之后，回表次数就成1了，相比于前面的5次，大大减少了回表的次数。

而这个优化，就被称为索引下推，就是为了减少回表的次数。

之所以这个优化叫索引下推，其实是跟判断​​age > 22​​逻辑执行的地方有关，这里就不过多赘述了。

文章转载自公众号：三友的java日记