说明
本文是《MySQL是怎样运行的-从根儿上理解MySQL》的学习笔记，文中的图全部来自于这本书，强烈建议买一本看看，对MySQL理解会特别深入，非常感谢作者"小孩子4919"。
第四章 InnoDB记录存储结构
InnoDB将数据划分成若干页，以页为作为磁盘和内存之间交互的基本单位。InnoDB中页的大小一般为16KB，也就是说一般情况下，一次最少从磁盘中读取16KB的数据到内存中，一次最少把内存中16KB的内容刷新到磁盘中。
InnoDB行格式
InnoDB行格式分类
- compack
- redundant
- dynamic
- compressed
指定行格式语法
CREATE TABLE 表名(列的信息) ROW_FORMAT=行格式名称;
ALTER TABLE 表名 ROW_FORMAT=行格式名称;
compack行格式详情
首先创建一个表，行格式设定为compack
CREATE TABLE record_format_demo(
c1 VARCHAR(10),
c2 VARCHAR(10) NOT NULL,
c3 CHAR(10),
c4 VARCHAR(10))
CHARSET=ascii ROW_FORMAT=COMPACT;
这里创建了名为record_format_demo的表，并将行格式指定为"COMPACT"，可以看到行格式是"表级别"的，同时设置表的字符集为"ascii"，然后向其插入两条记录
INSERT INTO record_format_demo(c1, c2, c3, c4)
VALUES('aaaa', 'bbb', 'cc', 'd'),
('eeee', 'fff', NULL, NULL);
现在，表中的记录如表4-0
| c1 | c2 | c3 | c4 |
|---|---|---|---|
| aaaa | bbb | cc | d |
| eeee | fff | NULL | NULL |
准备工作完成后，接下来可以看COMPACT的行格式了，如图4-1
COMPACT行格式内容
- 记录的额外信息
- 变长字段列表
varchar(M)、varbinary(M)、text、blog这些数据类型的类称为变长字段，变长字段存储多少字节是不固定的，变长字段占用的存储空间分为"真正的数据内容"以及"该数据占用的字节数"两部分。在COMPACT行格式中，所有变长字段的真实数据占用字节数都存放在开头位置，从而形成一个变长字段列表，各变长字段的真实数据占用的字节数按照列的顺序逆序存放。 - NULL值列表
- 记录头信息
- 变长字段列表
- 记录的真实数据
变长字段列表
以上文的record_format_demo记录为例，变长字段内容长度信息如表4-1
| 列名 | 存储内容 | 内容长度(10进制表示) | 内容长度(16进制) |
|---|---|---|---|
| c1 | aaaa | 4 | 0x04 |
| c2 | bbb | 3 | 0x03 |
| c4 | d | 1 | 0x01 |
注意：由于c3是char(10)类型，在COMPACT行格式下属于定长类型，因此没有列举在表格中。同样第二条记录的变长信息也很容易计算出来，这里给出两条记录存储格式的对比，如图4-3
再次提醒，变长字段长度列表是根据表中列的顺序逆序存放的，对于第一条记录来说，c1、c2、c3的长度是04 03 01，因此变长字段长度列表为"01 03 04"，第二条记录同理。
NULL值列表
一条记录某些列可能为NULL，如果将NULL值存放在记录中会占用较大的存储，因此COMPACT行格式把一条记录中为NULL的列统一管理起来，存储到NULL值列表中。
-
统计表中哪些列允许为NULL值
主键列和NOT NULL修饰的列是不允许存储NULL值的。record_format_demo的3个列c1,c3,c4都允许存储NULL值，而c2列使用NOT NULL进行了修饰，因此不允许存储NULL值。 -
如果表中没有允许为NULL值的列，则NULL值列表也就不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列。二进制位值为1代表该列值为NULL，否则代表该列不为NULL。
-
MySQL规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节高位补0。
表record_format_demo的效果如图4-5所示
如果一个表中有9个值允许为NULL列，则这个表的NULL值列表就需要2个字节来存储了。record_format_demo表中两条记录填充NULL值列表后的情况如图4-8所示
记录头信息
除了变长字段长度列表、NULL值列表外，记录的额外信息中还存储了头信息，头信息由固定长度的5个字节表示，用于描述一些属性，不同的位代表不同的含义，如图4-9所示
他们的含义如表4-2
| 名称 | 大小(位) | 描述 |
|---|---|---|
| 预留位1 | 1 | 未使用 |
| 预留位2 | 1 | 未使用 |
| deleted_flag | 1 | 标记该条记录是否被删除 |
| min_rec_flag | 1 | B+树每层非叶子节点中最小的目录项记录都会添加该标记 |
| n_owned | 4 | 一个页面中的记录会被分成若干组，每个组中有一条记录是”带头大哥“，其余记录是”小弟“。”带头大哥“记录的n_owned值代表该组中所有的记录条数，”小弟“记录的n_owned值都为0 |
| heap_no | 13 | 表示当前记录在页面堆中的相对位置 |
| record_type | 3 | 表示当前记录的类型，0表示普通记录；1表示B+树非叶子节点的目录项记录；2表示Infimum记录；3表示Supremum记录 |
| next_record | 16 | 表示下一条记录的相对位置 |
记录真实数据
对于record_format_demo表来说，记录的真是数据除了c1、c2、c3、c4这几个自定义的列外，MySQL还会为每条记录默认添加一些列（隐藏列），具体情况如表4-3
| 列名 | 是否必须 | 占用空间（字节） | 描述 |
|---|---|---|---|
| db_row_id | 否 | 6 | 行ID，唯一标识一条记录，用户没有定义主键且表中没有NOT NULL 的UNIQUE键 |
| db_trx_id | 是 | 6 | 事务ID |
| db_roll_pointer | 是 | 7 | 回滚指针 |
第五章 InnoDB数据页结构
数据页结构及功能
InnoDB设计了多种不同类型的页，包括存放Change Buffer信息的页，存放INODE信息的页，存放记录的索引页，用户的数据就存放在索引页中，为了方便，这里将索引页称为数据页，图5-1给出了数据页的结构
各结构的大小及作用如表5-1
| 名称 | 中文名 | 占用空间大小(字节) | 描述 |
|---|---|---|---|
| File Header | 文件头部 | 38 | 页的通用信息 |
| Page Header | 页面头部 | 56 | 数据页专有的信息 |
| Infimum+Supremum | 页面最小记录和最大记录 | 26 | 两个虚拟记录 |
| User Records | 用户记录 | 不确定 | 用户存储的记录内容 |
| Free Space | 空闲空间 | 不确定 | 页中尚未使用的空间 |
| Page Dictionary | 页目录 | 不确定 | 页中某些记录的相对位置 |
| File Trailer | 文件尾部 | 8 | 校验页是否完整 |
记录在页中的存储
用户的记录会存放到User Records部分，但是一开始生成页的时候是没有这部分的，每当插入一条记录时，都会从Free Space申请一个记录大小的空间，然后将记录存进去，这部分内容就划到User Records了，当Free Space空间全部用完后，意味着整个页就用完了，如果此时还想继续插入，就需要申请新的页。
记录头信息
为了方便叙述，这里新创建一个表
CREATE TABLE page_demo(
c1 INT,
c2 INT,
c3 varchar(1000),
PRIMARY KEY (c1))
CHARSET=ascii ROW_FORMAT=COMPACT;
page_demo表主键是c1，行格式设置为COMPACT。这里向表中插入4条记录
INSERT INTO page_demo VALUES(1, 100, 'aaa'),
(2, 200, 'bbbb'),
(3, 300, 'cccc'),
(4, 400, 'dddd');
插入4条记录后User Records存储结构如图5-5所示
其中，有几个关键的属性需要再次强调。
- heap_no
用户向表中插入记录本质上都是放到数据页的User Records部分，这些记录一条一条紧密排列着，InnoDB将这样的结构称为堆。为了方便管理堆，InnoDB将一条记录在堆中的相对位置称为heap_no。在页面前边的heap_no相对较小，在页面后边的heap_no相对较大，每申请一条记录的存储空间时，该条记录比物理位置在它前面的那条记录的heap_no值大1。图5-5中heap_no依次是2、3、4、5。InnoDB给每个页加了两条记录，称为伪记录或虚拟记录。一条代表页中最小的记录(写作Infimum记录)，另一条代表最大记录(写作Supremum)，这两条记录也是堆中的一部分，而且这两条记录是在页申请好空间即插入，比用户记录插入页早很多，因此它们在堆中的heap_no值最小。记录的大小就是比较主键的大小，InnoDB规定，任何用户记录都比Infimum记录大，比Supremum记录小，如图5-8所示。
-
record_type(表示当前记录类型)
- 0(普通记录)
- 1(B+树非叶子节点的目录项记录)
- 2(Infimum记录)
- 3(Supremum记录)
-
next_record
表示从当前记录的真实数据到下一条记录的真实数据的距离。如果该属性值为正数，则表示下一条记录在当前记录的后面；如果该属性值为负数，则表示下一条记录在当前记录的前面；如果该属性值为0，则表示没有下一条记录(Supremum记录或已被删除的记录)。从图5-8中可以看到，第一条记录的next_record=32，意味着从第一条记录的真实数据地址往后找32字节便是下一条记录的真实数据。再比如第4条记录的next_record=-111，意味着从第4条记录的真实数据地址往前找111字节便是下一条记录的真实数据。为了更形象展现next_record的作<用，用箭头替代next_record的值，如图5-9所示。
DELETE FROM page_demo WHERE c1=2;
删除第2条记录后如图5-10所示。
从图5-10中可以看到删除之后发生了这些变化：
- 第2条记录并没有从存储空间移除，而是把它的deleted_flag值置为1；
- 第2条记录的next_record值变为0，意味着该记录没有下一条记录了；
- 第1条记录的next_record指向了第3条记录；
- Supremum记录的n_owned值从5变成了4。
另外，之前一直提到的next_record指向的是记录头信息和真实数据之间的位置。这个位置向做读取就是记录头信息，向右读取就是真实数据。前面还说到，变长字段长度列表、NULL值列表都是逆序存放的，也是为了能够快速定位到需要的信息。
刚刚讨论了删除第2条记录的情形，如果此时再执行插入，会有什么变化呢？
INSERT INTO page_demo VALUES(2, 200, 'bbbb');
重新插入之后的情况如图5-11所示。
从5-11中也可以看到，InnoDB并没有因为新记录的插入而重新分配空间，而是直接复用了原来被删除记录的存储空间。
Page Directory(页目录)
上文介绍了记录在页中是按照主键由小到大顺序串联成一个单向链表，如果想根据主键查询页的记录，比如下面的语句
SELECT * FROM page_demo WHERE c1 = 3;
会怎么查找呢？显然，页中主键是有序的，按照顺序查找不可取，InnoDB为了加速查找的过程，制定了如下的规则：
- 将所有正常的记录(包括Infimum和Supremum记录，但不包括已删除的记录)划分成几组。
- 每组的最后一条记录(也就是最大的记录)相当于"带头大哥"，组内其他记录相当于"小弟"。"带头大哥"记录头信息中的n_owned表示该组内共有几条记录。
- 将每组最后一条记录在页面中的地址偏移量单独提取出来，按顺序存储到页尾部的地方，这个地方就是图5-1中的Page Directory。页目录中这些地址偏移量称为槽，每个槽占用2个字节，页目录就是由多个槽组成的。
如图5-12所示，一共有2个槽，也就是说这个页中共有两组。槽0地址偏移量是99，也就是说从页面的0字节开始数99字节就是第一组最后一条记录的地址，InnoDB规定第一组只能有一条记录，即Infimum记录。槽1的偏移量是112，从页面的0字节开始数112字节就是第二组最后一条记录的地址，这里第二组最后一条记录时Supremum记录，这条记录中n_owned=5，表明在这组中共有5条记录(包括自己)。99、112这样的地址偏移量不太直观，可以改为指针，如图5-14所示。
注意图5-14中所有的指针都是指向记录头信息和记录真实数据信息中间的部分。这里只展示了2个槽，如果插入多条记录，情况又有什么不同呢？
INSERT INTO page_demo VALUES
(5, 500, 'eeee'),
(6, 600, 'ffff'),
(7, 700, 'gggg'),
(8, 800, 'hhhh'),
(9, 900, 'iiii'),
(10, 1000, 'jjjj'),
(11, 1100, 'kkkk'),
(12, 1200, 'llll'),
(13, 1300, 'mmmm'),
(14, 1400, 'nnnn'),
(15, 1500, 'oooo'),
(16, 1600, 'pppp')
c
现在page_demo表中共有18条记录，包括16条用户记录和两条默认记录，一共分配了5个槽，槽0永远是只有Infimum记录的那个组。以图5-15为例说明查找主键为11的记录，步骤如下：
- 计算中间槽位置，low=0，high=4，mid=(low+high)/2=2，槽2对应的主键为8<11，low=mid+1=3。
- mid=(low+high)/2=3，槽3对应主键为11<12，而槽2对应主键为8<11，因此主键为11的这条记录一定在槽3所在的组，则从槽2最后一条记录出发，遍历槽3的记录，直到找到主键为11的那条记录。
每一个数据页都有指向上一个数据页的FIL_PAGE_PREV指针和指向下一个数据页的FIL_PAGE_NEXT指针，这样，数据页就形成了一个双向链表结构，如图5-16。
第六章 B+树索引
本章主要介绍索引。在介绍之前，先新建一个表:
CREATE TABLE index_demo(
c1 INT,
c2 INT,
c3 CHAR(1),
PRIMARY KEY(c1))
ROW_FORMAT = COMPACT;
为了更直观窥探记录的存储样式，这里省去了上文中介绍的记录头信息，只保留几个简短的信息，并将其竖着排列，如图6-3所示。
这里往表中插入几条记录。
INSERT INTO index_demo VALUES
(1, 4, 'u'),
(3, 9, 'd'),
(5, 3, 'y');
INSERT INTO index_demo VALUES(4, 4, 'a');
这里的插入分成两部分，第一次插入3条记录，第二次插入一条记录。假设每个数据页最多只能存放3条用户记录(实际可以存放的好多记录，这里只是方便叙述)。
刚开始3条记录按照主键的顺序插入的10号页中，后来又插入了一条id=4的记录，由于一个页只能存放3条记录，因此第4条记录需要新分配一个页(号码为28)，这里也可以看到分配的页并不是连续的。在新分配页之后还要保证后面的页所有的记录都大于前面的页的记录，这里id=4显然不满足这条规则，因此需要调整，调整过程如图6-7所示。
图6-7中可以看到，在对页进行增删改操作过程中，必须通过记录移动的操作来始终保证这样的状态：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程也叫页分裂。
由于数据页编号可能并不连续，在向index_demo表中插入多条记录后，可能形成多个离散的页，不过它们通过pre和next指针联系起来。如果想从这么多页中根据主键值快速定位某些记录所在的页，就需要给它们编制一个目录，每个目录包括两部分：
- 页的用户记录中最小的主键值，用key表示；
- 页号，用page_no表示。
这样的结构如图6-9所示。
以页28为例，它对应目录项2，key=5表示页28中最小的主键值是5，也就是(5,3,‘y’)这条记录。如果想要查找主键值为20的记录，在目录项中发现12<20<209，于是确定主键为20的记录可能在page_no为9的页中，然后再到页9中根据槽二分查找到(20,2,‘e’)这条记录。
InnoDB设计者发现目录项的数据结构跟用户记录很像，只不过目录项的前两列是主键和页号而已，于是目录项复用了用户记录页，这里称表示目录项的记录成为目录项记录。那么，InnoDB是怎么区分一条记录是普通的用户记录还是目录项记录呢？record_type=1就表示目录项记录(0:普通的用户记录；2:Infimum记录；3:Supremum记录)。
于是，将前面的目录项放到数据页中，结果如图6-10所示。
从图6-10中可以看到，InnoDB新分配了一个page_no=30的页存储目录项记录，目录项记录和用户记录不同点在于:
- 目录项记录的record_type=1，用户记录的record_type=0；
- 目录项记录只有主键值和page_no两列，而普通用户记录是用户自定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列；
- 记录的头信息中有个名为min_rec_flag属性 ，只有目录项记录的min_rec_flag值才可能为1，普通用户记录的min_rec_flag值均为0；
一个页的大小为16KB，随着目录项记录插入越来越多，总有一天页的空间被用完，此时就需要申请一个新的页，如图6-12所示。
图6-12的数据结构，就是B+树。无论是目录项记录页还是用户记录页，它们都是B+树的一个节点。真正的用户记录都存放在B+树的叶子节点上，B+树最上边的节点称为根节点。从图6-12也可以看到，一个B+树可以分为好多层。如果B+树有4层，可以存放100亿的数据，因此一般的B+树都不超过4层。所以根据主键查找时最多搜寻4层，比不建索引性能提升太多。
索引的分类
-
聚簇索引
上文中介绍的B+树本身就是一个目录，或者说就是一个索引，它的节点是按照主键从小到大排序好的结构，非叶子节点存储目录信息，叶子节点存储完整的用户记录信息，将具有完整信息并根据主键从小到大排序好的B+树称为聚簇索引。在InnoDB中，聚簇索引就是数据的存储方式，也就是所谓的"索引即数据，数据即索引"。 -
二级索引
上文介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，如果想以别的列作为搜索条件，该如何处理呢？仿照聚簇索引，这里也可以对其他列根据排序规则建立一个B+树索引，如图6-14就是在c2列上建立索引。
图6-14在c2列上建立索引的情况 在c2列上建立的二级索引有以下几个特点- 页内的所有记录都是按照c2从小到大排列成单向链表，页内被划分为若干组，每组c2列最大的记录在页的偏移量会当做槽放到页目录中；
- 各个存放用户记录的页也是根据c2列大小排列成一个双向链表；
- 存放目录项记录的页分为不同层级，同一层级的页也是根据c2列大小组成双向链表；
- B+树种叶子节点存储的并不是完整的用户记录，而是c2列+主键这两个列的值；
- 目录项记录不再是主键+页号 的搭配，而是c2列+主键+页号的搭配；
假设这里需要查找c2=4的记录，就可以使用刚刚建立的索引，不过c2=4的记录可能有多条，因此只需要定位到B+树种叶子节点第一条满足c2=4的那条记录，然后沿着记录一直 向后扫描到不符合条件的记录。搜索完成后取出所有的主键id，再使用主键id在聚簇索引中查找完整的记录，根据主键id在聚簇索引中查找完整记录的过程称为回表。
这里为什么需要回表呢？因为二级索引只存储了c2列和主键id的值，并没有存储其他列，因此查询其他列需要进行回表操作。为什么不在c2列的索引上也存储完整的用户记录呢？这样做太占空间了，相当于每建一个索引都需要将用户记录完整复制一遍。 -
联合索引
有了上文聚簇索引和二级索引的介绍，联合索引就比较好理解了，联合索引是对多个列建立索引，比如对c2、c3两列建立联合索引，建立规则是各个记录首先按照c2列排序，如果c2列相同，再按照c3列排序。需要注意的是联合索引是一个B+树，这里要和在c2、c3两个列建立二级索引区分开。
总结
- 每个索引对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余是内节点。所有用户记录存储在叶子节点上，所有目录项记录都存储在内节点。
- InnoDB会自动为主键建立聚簇索引(如果没有显示指定主键或没有生命不允许存储NULL的UNIQUE建，它会自动添加主键)，聚簇索引的叶子节点包含完整的用户记录。
- 可以为感兴趣的列建立二级索引，二级索引的叶子节点包含用户记录索引列和主键组成。如果想通过二级索引查找完整的用户记录，需要执行回表操作。
- 可以为多个列建立联合索引，比如对c2列和c3列建立联合索引idx_c2_c3(c2, c3)，那么该索引中页面记录按照 c2列的值进行排序，如果c2值相同，按照c3进行排序。
563
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
