code宝藏

笔记整理自【宋红康】MySQL数据库（mysql安装/基础/高级/优化），还参考网上的其他技术文章作为补充，具体参考链接在文末

💻InnoDB中索引的推演

💡首先我们先了解一下索引是什么？

索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现 高级查找算法

👉接下来我们一步一步思考如何设计一个索引的数据结构

🏳️‍🌈索引之前的查找

先来看一个精确匹配的例子：

SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

1、📃在一个页中的查找

假设目前表中的记录比较少，所有的记录都可以被存放到一个页中，在查找记录的时候可以根据搜索条件的不同分为两种情况：

以主键为搜索条件

可以在页目录📚中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
💡页目录创建的过程如下：
1. 将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录；
2. 每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（上图中粉红色字段）
3. 页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。
以其他列作为搜索条件
因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

2、📃📃在很多页中查找

大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤：
1，定位到记录所在的页。
2，从所在的页内中查找相应的记录。

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们上面的查找方式去查找指定的记录。

因为要遍历所有的数据页，所以这种方式显然是超级耗时的。如果一个表有一条记录呢？此时索引应运而生。

🏳️设计索引

表中每行数据的格式

建一个表：

mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact

这对于这个表，我们把其每行的格式简化如下👇：

说明：

record_type ：记录头信息的一项属性，表示记录的类型， 0 表示普通记录、1 表示目录项记录、 2 表示最小记录、 3 表示最大记录
next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁
各个列的值 ：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3
其他信息 ：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

将记录格式示意图的其他信息项暂时去掉并把它竖起来的效果就是这样👇

后面的图中均会将其竖起

把一些记录放到页里的示意图就是👇

💡索引的设计方案

为快速定位记录所在的数据页，我们需要建立一个目录，建这个目录必须完成下边这些事：

下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
给所有的页建立一个目录项。

于是我们可以设计出如下方案👇：

以页28为例，它对应目录项2，这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值5。我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能了。

比如：查找主键值为20的记录，具体查找过程分两步：

1，先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中（因为12 < 20 < 209），它对应的页是页9

2，再根据前边说的在页中查找记录的方式去页9中定位具体的记录。

至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为索引

InnoDB中的索引方案

🕐迭代1次：目录项纪录的页

新分配了一个编号为30的页来专门存储目录项记录

现在以查找主键为 20 的记录为例，根据某个主键值去查找记录的步骤就可以大致拆分成下边两步：

先到存储 目录项记录 的页，也就是页30中通过 二分法 快速定位到对应目录项，因为 12 < 20 < 209 ，所以定位到对应的记录所在的页就是页9。
再到存储用户记录的页9中根据 二分法 快速定位到主键值为 20 的用户记录。

💡目录项记录和普通用户记录的区别

目录项记录的 record_type 值是1，而普通用户记录的 record_type 值是0。
目录项记录只有 主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列

相同点：两者用的是一样的数据页，都会为主键值生成 Page Directory （页目录），从而在按照主键值进行查找时可以使用二分法来加快查询速度

🕑迭代2次：多个目录项纪录的页

当目录记录变多了之后

我们插入了一条主键值为320的用户记录之后需要两个新的数据页，为存储该用户记录而新生成了页31

此时我们查找用户记录需要三步，以查找主键值为 20 的记录为例：

确定 目录项记录页

我们现在的存储目录项记录的页有两个，即 页30 和页32 ，又因为页30表示的目录项的主键值的范围是 [1, 320) ，页32表示的目录项的主键值不小于 320 ，所以主键值为 20 的记录对应的目录项记录在页30 中。
通过目录项记录页 确定用户记录真实所在的页 。在一个存储目录项记录 的页中通过主键值定位一条目录项记录的方式说过了。
在真实存储用户记录的页中定位到具体的记录

👆如图，我们生成了一个存储更高级目录项的页33 ，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在 [1, 320) 之间，则到页30中查找更详细的目录项记录，如果主键值不小于320 的话，就到页32中查找更详细的目录项记录。

🏁B+树

在InnoDB中，最终演化成了如下图所示

我们再看看 B+ 树如何实现快速查找主键为 6 的记录，以上图为例子：

从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为 6，在[1, 7)范围之间，所以到页 30 中查找更详细的目录项；
在非叶子节点（页30）中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于 5，所以就到叶子节点（页16）查找记录；
接着，在叶子节点（页16）中，通过槽查找记录时，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为 6 的记录。

可以看到，在定位记录所在哪一个页时，也是通过二分法快速定位到包含该记录的页。定位到该页后，又会在该页内进行二分法快速定位记录所在的分组（槽号），最后在分组内进行遍历查找。

⚠️InnoDB的B+树索引的注意事项：

根页面位置万年不动

内节点中目录项记录的唯一性

一个页面最少存储2条记录

🖥️MyISAM中的索引方案

MyISAM的索引方式都是“非聚簇”的，这意味着MyISAM必须进行一次回表操作
MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的
InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
MyISAM可以没有主键

参考链接：

换一个角度看 B+ 树

[MySQL进阶]——索引的数据结构