InnoDB中索引的推演

目录

• 1、InnoDB中索引的推演
  • 1.1索引之前的查找
    • 1、在一个数据页中的查找
    • 2、在很多数据页中查找
  • 1.2设计索引
    • 1、一个简单的索引设计方案
    • 2、InnoDB中的索引方案
      • ①迭代一次：目录项记录的页
      • ②迭代2次：多个目录项记录的页
      • ③迭代3次：目录项记录页的目录页
      • ④B+Tree
• InnoDB的B+树注意事项
  • 1、根页面位置万年不动
  • 2、内节点中目录项记录的唯一性
  • 3、一个页面最少存储2条记录

1、InnoDB中索引的推演

1.1索引之前的查找

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

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

1、在一个数据页中的查找

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

​ 以主键为搜索条件

​ 可以在页目录中使用二分法快速定位到对应的槽，然后再便利该槽对应分组中的记录即可快速找到指定的记录。

​ 以其它列作为搜索条件

​ 因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

2、在很多数据页中查找

大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤:

​ 1、定位到记录所在的数据页；

​ 2、从所在的页内中查找相应的记录。

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们上面的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的。如果一个表有一亿条记录呢?此时索引应运而生。

1.2设计索引

建一个表：

mysql> CREATE TABLE index_demo(
	-> c1 INT,
    -> C2 INT,
    -> C3 CHAR(1),
    -> PRIMARY KEY(C1)
) ROW_FORMAT = Compact;

该表的行格式简化图如下所示

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

把一些记录放到页中的示意图就是：

1、一个简单的索引设计方案

我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢?因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办?我们可以为快速定位记录所在的数据页而建立一个目录，建这个目录必须完成下边这些事:

​ 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。

​ 假设:每个数据页最多能存放3条记录（实际上一个数据页非常大，可以存放下好多记录)。有了这个假设之后我们向index_demo表插入3条记录:

mysql> INSERT INTO index_demo VALUES(1, 4, 'U'), (3, 9, 'd'), (5, 3, 'y');

那么这些记录已经按照主键值的大小串联成一个单向链表了，如图所示：

从图中可以看出来, index_demo表中的3条记录都被插入到了编号为10的数据页中了。此时我们再来插入—条记录:

mysql> INSET INTO index_demo VALUES(4, 4, 'a');

因为页10最多只能放3跳记录，所以我们不得不再分配一个新页：

注意，新分配的数据页编号可能并不是连续的。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系。另外，页10中用户记录最大的主键值是5，而页28中有一条记录的主键值是4，因为5>4，所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以在插入主键值为4的记录的时候需要伴随着一次记录移动，也就是把主键值为5的记录移动到页28中，然后再把主键值为4的记录插入到页10中，这个过程的示意图如下:

这个过程表明了在对页中的记录进行增删改操作的过程中，我们必须通过一些诸如记录移动的操作来始终保证这个状态一直成立:下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程我们称为页分裂。

给所有的页建立一个目录项

由于数据页的编号可能是不连续的，所以在向index_demo表中插入许多条记录后，可能是这样的效果:

因为这些16KB的页在物理存储上是不连续的，所以如果想从这么多页中根据主键值快速定位某些记录所在的页，我们需要给它们做个目录，每个页对应一个目录项，每个目录项包括下边两个部分:

​ 页的用户记录中最小的主键值，我们用key来表示。

​ 页号。我们用page_no表示。

所以我们为上边几个页做好的目录就像这样：

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

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

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

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

2、InnoDB中的索引方案

①迭代一次：目录项记录的页

上边称为一个简易的索引方案，是因为我们为了在根据主键值进行查找时使用二分法快速定位具体的目录项而假设所有目录项都可以在物理存储器上连续存储，但是这样做有几个问题:

● InnoDB是使用页来作为管理存储空间的基本单位，最多能保证16KB的连续存储空间，而随着表中记录数量的增多，需要非常大的连续的存储空间才能把所有的目录项都放下，这对记录数量非常多的表是不现实的。

● 我们时常会对记录进行增删，假设我们把页28中的记录都删除了，那意味着目录项2也就没有存在的必要了，这就需要把目录项2后的目录项都向前移动一下，这样牵一发而动全身的操作效率很差。

所以，我们需要一种可以灵活管理所有目录项的方式。我们发现目录项其实长得跟我们的用户记录差不多，只不过目录项中的两个列是主键和页号而已，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为目录项记录。那InnoDB怎么区分一条记录是普通的用户记录还是目录项记录呢?使用记录头信息里的
record_type属性，它的各个取值代表的意思如下:
● 0∶普通的用户记录
● 1∶目录项记录
● 2 : 最小记录
● 3∶最大记录

从图中可以看出来，我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调目录项记录和普通的用户记录的不同点:

● 目录项记录的record_type值是1，而普通用户记录的record_type值是o。
● 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。
● 了解:记录头信息里还有一个叫min_rec_mask 的属性，只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0。

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

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

​ 1、先到存储目录项记录的页，也就是页30中通过二分法快速定位到对应目录项，因为12 < 20 < 209，所以定位到对应的记录所在的页就是页9。

​ 2、再到存储用户记录的页9中根据二分法快速定位到主键值为20的用户记录。

②迭代2次：多个目录项记录的页

虽然说目录项记录中只存储主键值和对应的页号，比用户记录需要的存储空间小多了，但是不论怎么说一个页只有16KB大小，能存放的目录项记录也是有限的，那如果表中的数据太多，以至于一个数据页不足以存放所有的目录项记录，如何处理呢?

这里我们假设一个存储目录项记录的页最多只能存放4条目录项记录，所以如果此时我们再向上图中插入一条主键值为320的用户记录的话，那就需要分配一个新的存储目录项记录的页:

从图中可以看出，我们插入了一条主键值为320的用户记录之后需要两个新的数据页:

● 为存储该用户记录而新生成了页31。
● 因为原先存储目录项记录的页30的容量已满(我们前边假设只能存储4条目录项记录)，所以不得不需要一个新的页32来存放页31对应的目录项。

现在因为存储目录项记录的页不止一个，所以如果我们想根据主键值查找一条用户记录大致需要3个步骤，以查找主键值为20的记录为例:

1、确定目录项记录页

我们现在的存储目录项记录的页有两个，即页30和页32，又因为页30表示的目录项的主键值的范围是[1,320)，页32表示的目录项的主键值不小于320，所以主键值为20的记录对应的目录项记录在页30中。

2、通过目录项记录页确定用户记录真实所在的页。

在一个存储目录项记录的页中通过主键值定位一条目录项记录的方式说过了。

3、在真实存储用户记录的页中定位到具体的记录。

③迭代3次：目录项记录页的目录页

问题来了，在这个查询步骤的第1步中我们需要定位存储目录项记录的页，但是这些页是不连续的，如果我们表中的数据非常多则会产生很多存储目录项记录的页，那我们怎么根据主键值快速定位一个存储目录项记录的页呢? 那就为这些存储目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据，所以现在各个页的示意图就是这样子:

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

随着表中记录的增加，这个目录的层级会继续增加，如果简化一下，那么我们可以用下边这个图来描述它：

这个数据结构，它的名称是B+树

④B+Tree

不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到B+树这个数据结构中了，所以我们也称这些数据页为节点。从图中可以看出，我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。

一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第0层，之后依次往上加。之前我们做了一个非常极端的假设: 存放用户记录的页最多存放3条记录，存放目录项记录的页最多存放4条记录。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么:

● 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放100条记录。

● 如果B+树有2层，最多能存放1000×100=180008条记录。
● 如果B+树有3层，最多能存放1080×1080×100=100880000条记录。
● 如果B+树有4层，最多能存放1080×1000×1000×100=1000,0008,8080条记录。相当多的记录！！！

你的表里能存放100000000000条记录吗?所以一般情况下，我们用到的B+树都不会超过4层，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页)，又因为在每个页面内有所谓的Page Directory (页目录)，所以在页面内也可以通过二分法实现快速定位记录。

InnoDB的B+树注意事项

1、根页面位置万年不动

我们前边介绍B+树索引的时候，为了大家理解上的方便，先把存储用户记录的叶子节点都画出来，然后接着画存储目录项记录的内节点，实际上B+树的形成过程是这样的:

● 每当为某个表创建一个B+树索引(聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
● 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
● 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值)的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

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

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

我们知道B+树索引的内节点中目录项记录的内容是索引列＋页号的搭配，但是这个搭配对于二级索引来说有点儿不严谨。还拿index_demo表为例，假设这个表中的数据是这样的:

c1	c2	c3
1	1	'u'
3	1	'd'
5	1	'y'
7	1	'a'

如果二级索引中目录项记录的内容只是索引列＋页号的搭配的话，那么为c2列建立索引后的B+树应该长这样:

如果我们想新插入一行记录，其中c1、c2、c3的值分别是: 9、1、 'c'，那么在修改这个为c2列建立的二级索引对应的B+树时便碰到了个大问题:由于页3中存储的目录项记录是由c2列＋页号的值构成的，页3中的两条目录项记录对应的c2列的值都是1，而我们新插入的这条记录的c2列的值也是1，那我们这条新插入的记录到底应该放到页4中，还是应该放到页5中啊?答案是:对不起，懵了。

为了让新插入记录能找到自己在那个页里，我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的:

● 索引列的值

● 主键值

● 页号

也就是我们把主键值也添加到二级索引内节点中的目录项记录了，这样就能保证B+树每一层节点中各条目录项记录除页号这个字段外是唯一的，所以我们为c2列建立二级索引后的示意图实际上应该是这样子的:

这样我们再插入记录(9，1，'c')时，由于页3中存储的目录项记录是由c2列＋主键＋页号的值构成的，可以先把新记录的c2列的值和页3中各目录项记录的c2列的值作比较，如果c2列的值相同的话，可以接着比较主键值，因为B+树同一层中不同目录项记录的c2列＋主键的值肯定是不一样的，所以最后肯定能定位唯一的一条目录项记录，在本例中最后确定新记录应该被插入到页5中。

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

一个B+树只需要很少的层级就可以轻松存储数亿条记录，查询速度相当不错! 这是因为B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问到存储真实数据的目录。那如果-个大的目录中只存放一个子目录是个啥效果呢?那就是目录层级非常非常非常多，而且最后的那个存放真实数据的目录中只能存放一条记录。费了半天劲只能存放一条真实的用户记录?所以InnoDB的一个数据页至少可以存放两条记录。