Mysql专栏 - mysql索引（二）

Mysql专栏 - mysql索引（二）

前言

本文接续Mysql专栏 - mysql索引（一）这篇文章，在这篇文章的最后介绍了关于索引页也就是BTree索引页的设计形式，首先需要牢记在Btree索引中索引页也是数据页，在数据页的数据行扩展之后，慢慢扩展出索引页，最后索引页向上继续扩展，他们底层由双向链表进行串联，并且数据行其实也是链表的表现形式，最终组成的结构就是叶子节点是数据页，而上层则是链表组成的索引树。

概述

1. 聚簇索引和二级索引以及三级索引的逻辑和维护细节
2. 多级索引是如何进行数据查找的，本文介绍了如下的查找方式
   1. 等值匹配
   2. 最左原则
   3. 最左前缀匹配
   4. 范围查找
   5. 等值匹配和范围匹配
3. 最后简述排序以及回表的危害，最后简单介绍覆盖索引的相关内容。

聚簇索引

那么什么是聚簇索引呢？这个索引其实是对正常的索引也就是上面的图进行进一步的扩展，假设我们要查找某个数据行，首先需要二分查找找到索引页，然后索引页再遍历链表通过id找到对应的数据页的页号，然后通过数据页的页号找到对应的数据页，最后通过数据页找到数据行，这些过程都是使用二分查找的方式进行的，因为他们在链表串联的时候通常会按照特定的属性进行排序：

从上面的图可以看到，其实索引页跳转到数据页其实是有一个指针进行关联的，所以索引页与数据页之间也是通过了链表进行串联的，但是需要注意在查找的时候查找的是数据页中的页目录，存放了各行数据的主键值和行的实际物理位置，通过页目录的二分查找 。

这种结构的最后的特点是可以发现「索引页和数据页其实整体都是一个链表进行串联」的，抽象来看其实相当于也是一个树，同时聚簇索引会对于数据页的结构提供对应的索引页进行维护，也就是说「一旦数据页出现页分裂的现象，索引页也会同样在他的上层进行对应的索引处理」。

最后我们可以对于聚簇索引下定义：「如果一颗大的B+树索引数据结构里，叶子节点就是数据页自己本身，那么此时我们就可以称这颗B+树索引为聚簇索引!」

所以这个聚簇索引默认是按照主键来组织的，所以你在增删改数据的时候，一方面会更新数据页，一方面其实会给你自动维护B+树结构的聚簇索引，给新增和更新索引页，这个聚簇索引是默认就会给你建立的。

❝小贴士：什么是页目录？ 为了维护数据页，每一个数据页的头部会包含页目录，根据数据行的主键进行存放，「数据行同时被分散到不同的槽位」上去。

索引基础结构❞

二级索引是如何维护的？

了解了什么是聚簇索引之后，我们来了解二级索引是如何维护的。其实建立比如说name,age 这种字段的时候，会同时建立一个B+树，叶子结点还是数据页，但是数据页「仅仅存放主键和name字段」，从下面的图可以看到他存放时name字段的索引树，叶子结点依然使用数据页保存name和主键。

所以其实你的索引扫描也是从name字段对应的索引树通过根节点遍历查找。

什么是回表操作？

如果按照二级索引查找到时候比如按照这样的查询where name ='xx'，按照这个方式进行查找之后其实「只是找到对应的主键值」，我们都知道主键值是没有数据的，所以此时还需要根据主键值找到对应的数据行，这种操作被称为：“「回表」”。

联合索引是如何处理的？

其实联合索引的运行原理也是一样的，只不过是建立一颗独立的B+树，叶子节点的数据页里放了 id+name+age，然后默认按照name排序，name一样就按照age排序，不同数据页之间name+age值 的排序等等。

聚簇索引的维护总结

我们串联上面的内容来看下整个聚簇索引的维护过程总结：

• 刚开始的时候，数据行放到不同的数据页，当数据满了之后，就会新加一个页目录，并且把主键较大的值放到另一个页目录里面。
• 页目录塞满之后，会使用页分裂的方式进行处理。
• 此时索引页要如何处理？可以发现此时数据页的根页就是最开始的那一个页就会被升级为一个索引页的根页，通过根页的方式索引到对应的数据页的根页，当数据页继续增加放不下的时候，会出现更多的索引页，同时索引页的根页也会出现对应的下层索引页，类似于一棵树的生长过程。
• 针对这种情况，通过索引页进行处理的情况会更多，所以索引页不可能是总是膨胀的。
• 针对主键之外的字段建立索引的话，实际上本质就是为那个字段的值重新建立另外一颗B+树 索引，那个索引B+树的叶子节点，存放的都是数据页，里面放的都是你字段的值和主键值，然后每一层索引页里存放的都是下层页的引用，包括页内的排序规则，页之间的排序规则，B+树索引的搜索规则，都是一样的。

❝「B+索引的优缺点」 优点： 查找非常快，logn的查找速度 缺点： 空间上：每次建立一个索引都要新增一颗b+树并且查找效率都要降低 时间上：因为每次多一颗树，所以都要多查找一次树 ❞

索引的建立和维护过程如上所示，但是有个问题就是索引页过多的时候也会出现页分裂，也就是会把维护的最小主键值较大的放到一边。

多级索引如何查找数据？

假如现在存在一个聚簇索引，并且现在建立了多个索引，比如「班级，姓名，科目，主键」这四个数据，我们现在使用下面的语句：

select * from student_score where class_name='1班' and student_name='张小强' and subject_name='数学'

等值匹配规则

上面这种等值匹配的方法其实都是按照正常联合索引的方式进行查找，这个查找的速度非常快，也就是说先查找第一个索引的树，然后找第二个，找第三个，按照顺序查找，所以叫做等值匹配规则。

最左匹配规则

最左匹配规则是指在索引查找的时候，不一定要查找where语句的方式，也可以按照联合索引的的最左顺序匹配，但是不能跳过索引进行查找，比如从顺序建立索引的反方向进行查找。

案例：where class_name='1班' and student_name='张小强'，假设class_name和student_name的顺序建立索引，则可以使用索引，如果反过来，则不能使用索引。

最左前缀匹配规则（模糊匹配）

如果按照索引的顺序并且按照like 语句的「顺序」进行查找的模糊匹配，也是可以使用索引的。但是需要注意下面特殊情况存在的时候可能「不能使用索引」：

• Like 'test%'：可以使用索引，可以匹配到因为左侧的内容是固定的右侧是模糊匹配
• Like '%test'：「不能」使用索引，因为不知道最左的匹配到底是啥，所以最后还是走全表索引

❝结论：尽量按照最左匹配的规则进行查找，这样可以尽可能的使用索引扫描的方式。 ❞

范围查找规则

其实也可以按照范围的顺序使用索引，比如age > 21 and age <29，但是只能适用于「首次的范围查询」，但是如果你要是写select * from student_score where class_name>'1班' and class_name<'5班' and student_name>''，这里只有「class_name」是可以基于索引来找的，student_name的范围查询是没法用到索引的!

等值匹配+范围匹配

如果你要是用select * from student_score where class_name='1班' and student_name>? and subject_name<?，那么此时你首先可以用「class_name」在 索引里精准定位到一波数据，接着这波数据里的student_name都是按照顺序排列的，所以 student_name>''也会基于索引来查找，但是接下来的subject_name<xxx是不能用索引的，因为它不符合 「最左匹配原则」。

其他内容补充

关于排序

如果对于排序的规则稍微有点了解，就会知道排序的时候是不使用索引的如果排序大量的数据，需要使用一个临时表，这样的操作方式称之为「filesort」，并且排序的时候数据量越大就越需要借用磁盘操作了。

但是如果按照「最左匹配原则」，就可以让排序按照索引的顺序进行查找，但是需要注意，如果你排序的时候哪怕都是走索引，一旦有「一个升序，一个降序」是「不能」走索引的。

按照同样的道理，如果分页也按照索引最左匹配的原则，同样可以作为排序使用。但是现实情况可能和我们预想的完全不同，但是实际上有一定的补救措施，后续的文章内容会进行扩展介绍。

回表的危害

哪怕是走的索引的内容，但是如果出现回表查询的操作，回表的操作通常出现在全表查询里面，其实速度也是非常慢的。

为了更好的理解这个危害性，这里举个例子：有的时候MySQL的执行引擎甚至可能会认为，你要是类似select * from table order by xx1,xx2,xx3的 语句，相当于是得把联合索引和聚簇索引，两个索引的所有数据都扫描一遍了，那还不如就不走联合索 引了，直接全表扫描得了，所以这种看似帮忙的正向优化反而变成“负优化”了。

什么是覆盖索引？

最后再来说下什么是覆盖索引，覆盖索引说白了就是在索引扫描的时候也就是联合索引查询的时候，如果数据可以直接从索引中查询到的话，那么此时的查询方式就是覆盖索引的查询方式。所以覆盖索引页比较理解，「覆盖索引不是索引，而是查询方式」。

总结

我们讲述了关于聚簇索引的细节，以及多级索引是如何查找数据的，另外介绍了mysql通常情况下使用索引的规则，其中需要重点关注的原则是：最左匹配规则，另外在文章末尾补充了和mysql相关的其他内容。

写在最后

内容也比较简单，下一节将会介绍执行计划以及索引优化的相关内容。