快速查询的秘籍——B+树索引

本文参考：第7章 好东西也得先学会怎么用-B+树索引的使用 (relph1119.github.io)

 建议查看官方书籍进行学习

页和记录的关系示意图

 

先来回顾一下上一节课的最重要的内容：InnoDB在一个页里面根据主键查找数据的过程是什么？

1. 通过二分法去page Directionary里面里面确定该记录所在的槽，并找到该槽里面主键最小的那条记录。
2. 通过记录的next_record 属性遍历该槽所在的组中的各个记录

如果不是在同一个页面中呢？于是引出来了本节课的重点——索引

终极问题：索引是什么？为什么要索引？索引是为了解决什么样的问题呢？索引怎么解决了这些问题？

没有索引的查找是什么？

分为两种情况，一种是在一个页中查找，另外一种是在多个页面中查找

我们现在来看在一个页面中的查找 ：

以主键为搜索条件：通过二分法定位到记录所在的的Page Directionary 中的槽，找到该槽的最小记录，然后通过该记录的next_record属性来依次遍历。

以其他列作为搜索条件：

数据页中没有对非主键列建立的页目录Page Directory ，所以我们无法通过二分法定位到槽。 这个时候只能是从最小记录开始依次遍历单链表中的每条记录。 这种查询很低效。 

在多个页面中的查找

分为两步：一是定位到记录所在的页，二是从所在的页内查找响应的记录，也就是上面的在一个页上面的查找。

在没有索引的情况下，不管是根据主键或者是其他列的值进行查找，由于不能快速定位到记录所在的页，所以只能从第一页开始，遍历每一个页，然后在页中根据我们刚才介绍的方法（主键或者是其他列）来查找记录。 

所以，索引就是为了解决 无法定位到记录具体在某个页 的难题。 （页内不由索引负责，有槽来做页内的工作）

索引的工作原理是什么呢？

再重复一下：由于我们无法定位到一条记录在具体的某个页中，所以我们需要想办法来定位到一条记录在那个页中，这样，查找就会很高效，否则的话，我们需要遍历所有的页，查询就会很低效。

借鉴我们在页内通过主键的查找，我们是在页里面设置了一个 page Directory 页目录来快速确定主键所在的槽，那么我们也可以想一个类似的办法，来定位记录所在的数据页，我们也可以建立一个别的目录，从而达到这个效果。 参考之前的page Directory，这个目录需要满足什么条件呢？

之前的页目录，把数据分组，并且是按照主键的大小分组，每个组的最大记录和最小记录都是可以通过二分法定位到的。

现在的目录，也应该进行分组，每个组的最小记录和最大记录（可以是主键，也可以是其他列）也应该可以定位到，需要排序，组与组之间是连接起来的。 现在来一个简易的做法。

一：下一个数据页中的用户记录的主键值必须大于上一个页中的用户的记录的主键值，同一个页内主键值递增。

假设一个页里面只能存放三条数据，现在如果再新插入数据的话，应该是再建立一个新的页

 这里面页的物理地址可以是不连续的。上面图中有些问题，新插入的数据的主键是4，没有递增，所以必须进行页分裂，来交换位置

二：给所有的页建立一个目录项。 这些目录项必须连续存储，可以把他们放在一个数组里面。一个目录项包含两个数据，一个是主键值，一个是页号。 

这个目录项就是索引

做了这么多，不要忘记我们最初的目的：通过主键定位一条数据。

假如说我们想要定位主键为20的记录，分为两步

一：遍历目录项，根据二分法确定出来记录所在的目录项

二：根据之前在的在页内查找记录的方式去具体的页中查找具体的记录。

看完了简易的方法，我们来看看InnoDB官方的做法。

首先上面的简单的索引方案是有一些问题的

问题一：随着页的增多，目录项的记录也会增多，上述简易方案中的目录项是连续存储的，如果目录项过多的话，需要很大的连续的存储空间才能存得下来。

问题二：我们时长会对记录进行增删，如果我们把某个页给删除了，那这个页就不存在了，由于存储目录项的空间是连续的，该目录项后面的所有目录都需要向前移动，这时候，牵一发而动全身。

自己的思考：感觉上面说的都是因为存储目录是用的是连续的存储空间，上面说的问题，就是数组这种存储结构的问题，链表可以解决这个问题，但是链表又没有办法使用二分法进行定位，官方会用什么方式解决呢？

官方做法：官方发现，这些目录项其实长得跟我们的用户记录差不多，只不过目录项中的两个列是主键和页号，于是官方复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下，我们把这些用来表示目录项的记录成为目录项记录。 InnoDB是如何来区分一条记录是用户记录还是目录项记录呢？ 主要是通过行格式里面的记录头信息中的record_type 属性。 0是用普通用户记录，1是目录项记录，2是最小记录，3是最大记录。

再来总结一下目录项和普通的用户记录的不同点：

一：目录项记录中record_type是1，而普通用户的目录项记录是0

二：目录项记录只有主键值和页的编号两个列，而普通用户记录的列是用户自己定义的，可能包含很多个列，还有InnoDB创建的隐藏的列。

三： 记录头信息中有一个属性是min_rec_mask，只有在存储目录项记录的页中的主键最小的目录项记录的min_rec_mask为1，其他的都是0. 

目录项记录和普通用户记录的共同点：

一：他们用的是一样的数据页，页面类型都是0x 45BF，这个属性是在页中的File Header 中

二：页的组成结构相似，都是七个部分组成

三：都会为主键生成Page Directiory （页目录，页目录里面就是槽）

目录项记录如果过多了，页中存不下了怎么办？

会继续申请新的页来存储目录项记录，这个时候我们就有两个页来存储目录项记录。

那么我们再查找数据的时候，如何确定目录项记录所在的具体是哪个页呢？ 

1. 确定目录项记录页
2. 通过目录项记录页确定用户记录所在真实的页
3. 在真实存储用户记录的页中通过槽来定位到具体的记录

那么又有新的问题，如果第一步的“确定目录项记录页”也有很多的话，那我们怎么定位目录项记录页呢？

 其实答案也很简单，我们记录往上嵌套新的目录即可......

这个页中的两条记录分别代表页30 和页32 ，如果用户记录的主键值在[1, 320 ) 之间，那么就在页30里面，如果大于230的话，则去页32中去查找

不论是存储用户记录的数据页，还是存储目录项记录的数据页，都被放在这个B+树上面了，这些数据页都是B+树上的节点。 用户的记录都在最下面的一层叶子节点上面，目录项记录都放在了非叶子节点的位置。 

在上面的讨论中，存储用户记录的页只能存放3条记录，存放目录项记录的页最多只能存放4条记录。 现在我们假设存放用户记录的叶子节点（数据页）可以存放100条用户记录，存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么：

如果B+树有1层，那只有1个数据页用来存放用户记录，可以存放100条记录

如果B+树有2层，那有1000 （每页有1000个目录项记录） * 1 个数据页用来存放用户记录，可以存放1000*100条记录

如果B+树有3层，那只有1000 * 1000 个数据页用来存放用户记录，可以存放1000 * 1000 * 100条记录

如果B+树有4层，那只有1000 * 1000 * 1000 个数据页用来存放用户记录，可以存放1000 * 1000 * 100 * 100条记录

一般情况下，不会超过4层，通过主键去查找数据的话，只需要4个页面内的查找（查找3个目录项页和一个用户记录页），又因为每个页里面有page directiory ，所以在页里面也可以通过二分法快速实现定位记录。

聚簇索引

通过主键排列的B+树就是一个大的目录，同时也是一个索引，叫做聚簇索引，具有如下特点：

1. 使用记录主键值的大小进行记录的排序和页的排序，具体体现为

• 页内的记录按照主键值的大小进行排序，形成一个单向链表
• 用户记录页之间根据页中用户记录的主键大小排序成一个双向链表
• 存放目录项记录的页分为不同的层次，在同一个层次的页也是根据页中目录项记录的主键大小顺序排成一个双向链表

2. B+树叶子节点存储的是用户完整的用户记录，索引即数据，数据即索引

3. 不需要使用index显式创建，InnoDB存储引擎会自动创建

什么是二级索引？二级索引是做什么呢？是为了解决什么难题呢？

上面的聚簇索引，只能是以主键为搜索条件的时候才能发挥作用，我们在业务中查找是纷繁复杂的，如果我们想要用不是索引的列来进行查找的话，我们只能是从头到尾遍历整个链表吗？

答案显而易见是否定的，我们可以多建立几颗B+树，不同的B+树中的数据采用不同的排序规则

 上述B+树与上面介绍的聚簇索引有以下几处不同：

• 页内的记录是按照c2列的大小顺序排成一个单向链表
• 各个存放用户记录的页也是根据页中记录的c2列的大小顺序排成一个双链表
• 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表。
• B+树的叶子节点不再是存储完整的用户记录，而是只存储c2列+主键
• 目录项记录中不再是主键+页号，而是c2列 + 页号

如果我们通过c2列的值查找的话，也上面聚簇索引查找过程相似，最终能找到c2列，此时能拿到c2列的值和其对应的主键，然后再通过回表的方式去聚簇索引中查找，然后最终即可查找成功。

具体过程是

• 根据根页面查找目录项记录页
• 根据目录项记录页查找记录所在的真实所在的页
• 在真实存储用户记录的页
• 可能会进行回表

什么是联合索引？联合索引可以用来做什么？解决什么问题？

我们可以使用多个列的大小作为排序规则，也就是同时使用多个列建立索引，现在我们想要让B+树按照c2列和c3列的大小进行排序，如果c2列相同，按照c3列进行排序。

InnoDB的B+树索引的注意事项——根节点万年不动

B+树的形成过程是：

1. 每当为某个表创建一个B+树索引的时候，都会为这个索引创建一个根节点，最开始表中没有数据的时候，B+树索引对应的根节点既没有用户记录，也没有目录项记录。
2. 随后向这个表中插入用户记录时，先把用户记录存储到这个根节点中
3. 当 根节点 中的可用空间用完的时候，如果继续插入记录，此时根节点会讲记录复制到一个新分配的页中，比如页a，然后对这个新页进行页分裂的操作，得到另外一个新页b，这个时候新插入的记录根据键值（聚簇索引的话，是主键，普通二级索引的话，就是对应的列值）的大小进行排序分配到页a或者是页b中，然后根节点升级为目录项记录的页。

这个过程需要大家特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

如何理解内节点中目录项记录的唯一性？

我们目前知道的是B+树索引的内节点中的目录项记录的内容是 索引列+ 页号，但是对于二级索引来说其实是有些错误的，举例来说

假设现在的表中数据如下，其中c1列是主键

 我们对c2列建立二级索引，假设每个页面只能存放三条数据，所以现在建立的B+树应该是这样的

 这个时候，我们插入一条数据，该数据是（9，1，‘c’），这个时候，这条记录应该放在页4 还是 页5 中呢 ？ 我们肉眼可以很容易的看来是放在页5中，但是我们使用B+书的思路来看一下这个问题：

B+树会先页3 找到对应的目录项记录，然后就会犹豫不决，因为目录项记录有两条，并且索引列的值都是1 ，由于目录项记录里面的数据只有c2列和页号，这个时候就不知道具体应该插入到那个页面中了。 

所以，这个时候，如果页3 中的目录项记录需要再包含一个数据——主键，这时候，就是索引列+主键+页号，这个时候，一定能确定目录项记录的唯一性

 此时当我们再次插入的时候，因为9 > 1 ，并且9 > 7 并且页3后面没有记录了，所以我们插入在页5中。  

为什么一个页面中最少存储2条记录？

B+树只要很少的层级就可以轻松存储数亿条数据，查询速度极快，那是因为 B+ 树本质上就是一个大的多层级目录，每次经过一个目录就可以过滤掉许多无效的子目录，最后访问到真实数据的目录，然后拿到真实数据。 如果每个页面只能存储一条记录的话，那就会变成一条线，无法发挥过滤作用。

MySQL中创建索引和删除索引的语句是什么？

CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1),
    INDEX idx_c2_c3 (c2, c3)
);

ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);

ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

我们再来高度概括一下最初的问题

终极问题：索引是什么？为什么要索引？索引是为了解决什么样的问题呢？索引怎么解决了这些问题？

索引是一种目录，类似于字典那样，查询一个字之前先查看一下目录，从而快速定位到这个字在哪里。  如果没有索引的话，只能从字典的第一页去一页一页的查找，效率低下。 

InnoDB中的索引是为了解决快速定位一条记录，聚簇索引是为了解决通过主键定位数据，普通的二级索引是为了定位 非主键列 为查询条件的数据。 B+树中的非叶子节点 是一个个页，页中存储的是目录项记录，叶子节点 的用户存储的的完整数据或者是部分数据。  索引只解决目录项记录定位问题，不直接解决定位用户记录。

为什么MySQL单表数据不要超过200w？

MySQL中的表数据是存在表明.ipd文件中，这个叫做表空间；表空间中有许多的页，每一个页都是16k

 索引页的内部结果如下面所示：

 

页是通过B+树的形式连接起来的

 

记录的总数量Total = x^(z-1) * y ，其中x是非叶子节点指向其他页的数量，y是也叶子节点内能容纳的数据行数，z是B+树的层数

x是多少呢？ 

File Header + Page Header + Infimum+ Supermum + File Trailer 再加上页目录，大概是1k左右

然后剩余15k用来存数据，这个数据主要是目录，两部分组成，一部分是出主键，一部分是页号，主键假设是Bigint（8byte） ，而页号是固定的4byte，那么一条数据是12byte  ，所以x = 15*1024 /12 = 1280行

y是多少呢？

叶子节点和非叶子节点的结构是一样的，同理，能放数据的空间也是 15k。

叶子节点里面存放的数据就比较多而且不固定了，是根据用户存入表中的具体字段决定的，如果按照一条数据1k来算，那一页能存15条

Z是多少？

z是根据表中记录的多少来定的

如果z=2, Total = （1280 ^1 ）*15 = 19200

如果z=3, Total = （1280 ^2 ）*15 = 24576000 （约 2.45kw）

一般的B+树带上根节点也就3层，如果是4层的话，查询的时候会增加磁盘IO，MySQL默认是以页为单位进行磁盘IO，页之间也不是物理相邻的，所以每经过一层就要做一下磁盘IO。

MySQL 为了提高性能，会将表的索引装载到内存中，在 InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。

但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降，所以增加硬件配置（比如把内存当磁盘使），可能会带来立竿见影的性能提升哈。