05索引原理：深入浅出索引（下）

Posted 2021-04-17 17:00 +0800 by ZhangJie ‐ 1 min read

前面介绍了常见的查询类型，支持查询的常用数据结构和算法，然后介绍了InnoDB执行引擎B+树的优势，主要是和机械硬盘的特性结合起来实现高性能的读写，也描述了B+树层高与可存储的数据量的计算方式，等等吧。

这里重点介绍下索引的设计、使用方面的一些知识点，下面讨论的都是InnoDB B+树。

user(id,name,age), pk(id),index(name)

• 主键索引

  b+树中索引节点存储的是关键字以及指针，指向指向索引节点或者数据节点的page。索引中的关键字，可以是一个列字段，也可以是多个列字段，如果是多个列字段的话，它们出现的顺序就是定义索引时写的顺序。

  innodb主键索引是聚集索引，叶子节点中的数据直接包含了记录行的数据。

• 普通索引

  其他普通索引，比如id为主键，在name上创建索引，这种索引的叶子节点中记录的主键的id，如果要通过name去查age之类的其他字段信息，要先通过这里的name索引查到id，然后回表，也就是通过主键索引查找到对应的记录后（实际上是主键索引查找到指针对应的page，然后遍历page里面的各个记录找到的），再去拿到age等其他信息。

  所以普通索引这样查起来会多一次回表的操作。

• 覆盖索引

  假如说现在我想通过name，直接查找到对应的age行不行啊，不想先查到id，再回表查。不回表的话，就可以考虑索引覆盖，比如创建个索引index(name,age)，这样就会先通过这个组合索引中的name找到对应的叶子节点，叶子节点中也包含了age这个关键字，就可以直接返回age。

  当然name有可能重复，所以可能查询结果不是单条记录，那还得沿着这个第一次找到的节点，向右遍历叶子节点列表，知道name不满足为止。

  由于索引中包含了age，也就不需要回表了。

• 最左前缀原则

  创建组合（联合）索引之后，查询的时候一定要注意，要用最左匹配原则才能应用索引，否则用不上索引，为什么呢？比如我们定义索引的时候是index(name,age)，那么你查询到时候where age=xxx and name=yyy，这种就没有优先用name，查询比较的时候就用不上索引，但是where name=yyy and age=xxx就能用上索引。

  为什么会这样呢？b+数索引节点中关键字，是按照定义索引时字段的顺序设置的，比较的时候也是按照这个顺序来比较。

  • 如何安排组合索引内的字段顺序？将更容易用到的放前面，这样可以提高复用的程度。
  • 由于建立了索引index(a,b)，最左前缀可以在a上引用索引，也就不需要再单独为a建立索引index(a)了。
  • 另外就是要考虑空间原则，是不是一定要(name，age)建联合索引，那就得考虑用的频率了，如果这种查询场景不多，查询效率要求也不高，那么确实不适合建立联合索引，浪费存储空间啊。但是也不能全表扫描那么慢吧，这个时候为name建个索引，回表查age，还是可以接受的。

• 索引下推

  对于组合索引index(a,b,c)，我们建议使用最左前缀匹配的方式来应用索引，那么如果查询的时候第一列是匹配的，第二列不配的，这种情况下会怎么处理呢？

  5.6以前的话，会直接根据匹配到的a回表，查出记录后再对比b是否匹配，不匹配再过滤掉，很明显这种效率是比较低的。

  5.6以后的话，引入了索引下推，什么意思呢，就是在组合索引上遍历的时候就直接比较其他几个索引列字段是否匹配，不匹配直接过滤掉，也不用回表了，减少了回表次数，效率自然也就高了。

  范围查询：说下范围查询大致是怎么工作的？

  最后说下范围查询，比如 user表上的索引有pk(id), age(name)，现在查询select name,age from user where age>25 and age<30 ，这个时候会现在index(age)这个索引上找到age>25的一个叶子节点，然后从这个节点开始，沿着叶子节点链表，直接向右遍历，因为都是按照age有序的嘛，每遍历一个叶子节点，回表查询name假如结果集，直到发现age<30不成立结束。

g