前言
-
总结下之前的内容
-
InnoDB
数据页的7个组成部分,知道了各个数据页可以组成一个双向链表
,而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表
,每个数据页都会为存储在它里边儿的记录生成一个页目录
,在通过主键查找某条记录的时候可以在页目录
中使用二分法快速定位到对应的槽,然后再遍历该槽对应分组中的记录即可快速找到指定的记录
- 其中页a、页b、页c ... 页n 这些页可以不在物理结构上相连,只要通过双向链表相关联即可。
一、索引
- 先建一个表:
mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;
Query OK, 0 rows affected (0.03 sec)
- 这个新建的
index_demo
表中有2个INT
类型的列,1个CHAR(1)
类型的列,而且我们规定了c1
列为主键,这个表使用Compact
行格式来实际存储记录的。为了我们理解上的方便,我们简化了一下index_demo
表的行格式示意图:
我们只在示意图里展示记录的这几个部分:
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');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
- 那么这些记录已经按照主键值的大小串联成一个单向链表了,如图所示:
- 从图中可以看出来,
index_demo
表中的3条记录都被插入到了编号为10
的数据页中了。此时我们再来插入一条记录:
mysql> INSERT INTO index_demo VALUES(4, 4, 'a');
Query OK, 1 row affected (0.00 sec)
- 因为
页10
最多只能放3条记录,所以我们不得不再分配一个新页:
- 怎么分配的页号是
28
呀,不应该是11
么?再次强调一遍,新分配的数据页编号可能并不是连续的,也就是说我们使用的这些页在存储空间里可能并不挨着。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系。另外,页10
中用户记录最大的主键值是5
,而页28
中有一条记录的主键值是4
,因为5 > 4
,所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求,所以在插入主键值为4
的记录的时候需要伴随着一次记录移动,也就是把主键值为5
的记录移动到页28
中,然后再把主键值为4
的记录插入到页10
中,这个过程的示意图如下:
- 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程我们也可以称为
页分裂
。 - 在向
index_demo
表中插入许多条记录后,可能是这样的效果:
-
因为这些
16KB
的页在物理存储上可能并不挨着,所以如果想从这么多页中根据主键值快速定位某些记录所在的页,我们需要给它们做个目录,每个页对应一个目录项,每个目录项包括下边两个部分:- 页的用户记录中最小的主键值,我们用
key
来表示。 - 页号,我们用
page_no
表示。
- 页的用户记录中最小的主键值,我们用
以页28
为例,它对应目录项2
,这个目录项中包含着该页的页号28
以及该页中用户记录的最小主键值5
。我们只需要把几个目录项在物理存储器上连续存储,比如把他们放到一个数组里,就可以实现根据主键值快速查找某条记录的功能了。比方说我们想找主键值为20
的记录,具体查找过程分两步:
- 先从目录项中根据二分法快速确定出主键值为
20
的记录在目录项3
中(因为12 < 20 < 209
),它对应的页是页9
。 - 再根据前边说的在页中查找记录的方式去
页9
中定位具体的记录。- 在通过页类的 二分+顺序查找,找到最终的 主键是20的
2、InnoDB中的索引方案
-
那
InnoDB
怎么区分一条记录是普通的用户记录
还是目录项记录
呢?别忘了记录头信息里的record_type
属性,它的各个取值代表的意思如下:0
:普通的用户记录1
:目录项记录2
:最小记录3
:最大记录
-
原来这个值为
1
的record_type
是这个意思呀,我们把前边使用到的目录项放到数据页中的样子就是这样: -
从图中可以看出来,我们新分配了一个编号为
30
的页来专门存储目录项记录
。这里再次强调一遍目录项记录
和普通的用户记录
的不同点: -
目录项记录
的record_type
值是1,而普通用户记录的record_type
值是0。 -
目录项记录
只有主键值和页的编号两个列,而普通的用户记录的列是用户自己定义的,可能包含很多列,另外还有InnoDB
自己添加的隐藏列。 -
还记得我们之前在唠叨记录头信息的时候说过一个叫
min_rec_mask
的属性么,只有在存储目录项记录
的页中的主键值最小的目录项记录
的min_rec_mask
值为1
,其他别的记录的min_rec_mask
值都是0
。 -
如果此时我们再向上图中插入一条主键值为
320
的用户记录的话,那就需要分配一个新的存储目录项记录
的页喽:
- 这些存储
目录项记录
的页再生成一个更高级的目录,就像是一个多级目录一样,大目录里嵌套小目录,小目录里才是实际的数据,所以现在各个页的示意图就是这样子:
3、单表存放上限
-
假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录,所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录,那么:
- 如果
B+
树只有1层,也就是只有1个用于存放用户记录的节点,最多能存放100
条记录。 - 如果
B+
树有2层,最多能存放1000×100=100000
条记录。 - 如果
B+
树有3层,最多能存放1000×1000×100=100000000
条记录。 - 如果
B+
树有4层,最多能存放1000×1000×1000×100=100000000000
条记录
- 如果
-
一般情况下,我们用到的
B+
树都不会超过4层,那我们通过主键值去查找某条记录最多只需要做4个页面内的查找(查找3个目录项页和一个用户记录页),又因为在每个页面内有所谓的Page Directory
(页目录),所以在页面内也可以通过二分法实现快速定位记录,这不是很牛么,哈哈!
聚簇索引
聚簇索引
就是数据的存储方式(所有的用户记录都存储在了叶子节点
)
二级索引
- 我们用
c2
列的大小作为数据页、页中记录的排序规则,再建一棵B+
树,效果如下图所示:
-
这个
B+
树与上边介绍的聚簇索引有几处不同: -
使用记录
c2
列的大小进行记录和页的排序,这包括三个方面的含义:- 页内的记录是按照
c2
列的大小顺序排成一个单向链表。 - 各个存放用户记录的页也是根据页中记录的
c2
列大小顺序排成一个双向链表。 - 存放目录项记录的页分为不同的层次,在同一层次中的页也是根据页中目录项记录的
c2
列大小顺序排成一个双向链表。
- 页内的记录是按照
-
B+
树的叶子节点存储的并不是完整的用户记录,而只是c2列+主键
这两个列的值。 -
目录项记录中不再是
主键+页号
的搭配,而变成了c2列+页号
的搭配。 -
记住需要回表
联合索引
-
方说我们想让
B+
树按照c2
和c3
列的大小进行排序,这个包含两层含义: -
先把各个记录和页按照
c2
列进行排序。 -
在记录的
c2
列相同的情况下,采用c3
列进行排序
4、InnoDB的B+树索引的注意事项
(1)根页面不会移动
- B+ 树的形成过程
- 每当为某个表创建一个
B+
树索引(聚簇索引不是人为创建的,默认就有)的时候,都会为这个索引创建一个根节点
页面。最开始表中没有数据的时候,每个B+
树索引对应的根节点
中既没有用户记录,也没有目录项记录。 - 随后向表中插入用户记录时,先把用户记录存储到这个
根节点
中。 - 当
根节点
中的可用空间用完时继续插入记录,此时会将根节点
中的所有记录复制到一个新分配的页,比如页a
中,然后对这个新页进行页分裂
的操作,得到另一个新页,比如页b
。这时新插入的记录根据键值(也就是聚簇索引中的主键值,二级索引中对应的索引列的值)的大小就会被分配到页a
或者页b
中,而根节点
便升级为存储目录项记录的页。
- 每当为某个表创建一个
一个B+树索引的根节点自诞生之日起,便不会再移动。 这样只要我们对某个表建立一个索引,那么它的根节点
的页号便会被记录到某个地方,然后凡是InnoDB
存储引擎需要用到这个索引的时候,都会从那个固定的地方取出根节点
的页号,从而来访问这个索引。
(2)内节点中目录项记录的唯一性
- 知道
B+
树索引的内节点中目录项记录的内容是索引列 + 页号
的搭配,但是这个搭配对于二级索引来说有点儿不严谨。还拿index_demo
表为例,假设这个表中的数据是这样的:
- 如果二级索引中目录项记录的内容只是
索引列 + 页号
的搭配的话,那么为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条记录
InnoDB
的一个数据页至少可以存放两条记录,这也是我们之前唠叨记录行格式的时候说过一个结论(我们当时依据这个结论推导了表中只有一个列时该列在不发生行溢出的情况下最多能存储多少字节,忘了的话回去看看吧)。