InnoDB 物理存储格式简介分析(五)

程序员潇然

行格式

逻辑上数据分为表和行。

在物理存储上，也有数据行的概念。

表的行格式决定了其行的物理存储方式，这反过来又会影响查询和 DML 操作的性能。

当单个磁盘页中容纳更多行时，查询和索引查找可以更快地工作，缓冲池中需要的缓存内存更少，写出更新值所需的 I/O 也更少。

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html

有这样几种行数据的格式

格式	说明
REDUNDANT	冗余行格式
COMPACT	紧凑行格式
DYNAMIC	动态行格式
COMPRESSED	压缩行格式

可以手动指定，也可以配置默认值。

默认值通过 innodb_default_row_format进行设置，默认值的默认值是DYNAMIC

这话比较绕口，就是有一个变量决定了默认的行格式，如果这个值你不设置，这个变量就是DYNAMIC

随变找一个表，查看下信息，如果没设置过，他就是DYNAMIC的

Compact行格式是Dynamic和Compressed两种行格式的基础，了解了它就了解了其他两种结构。

如下图所示，官方文档中已经很明显指出。

变长存储

varchar(M),VARBINARY(M),各种TEXT以及各种BLOB类型，mysql把拥有这些数据类型的列称为变长字段

变长字段列表中存储的是非空的变长字段的数据长度，变长字段存储的数据是不固定的，所以我们需要将数据占用的字节数也存起来。就好像挨在一起的两个字母序列abc和de拼在一起是abcde，你不知道如何断句。

对于拥有VARCHAR(M)这样类型的列称为变长字段。在把存储真实数据的时候需要顺便把这些数据占用的字节数页存储起来，所以这些变长字段占用的存储空间分为两部分：真实的数据内容和占用的字节数列表

1. 占用的字节数：Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而行程一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放。
2. M是指M个字符，实际允许存储的最大字节数为M*4（如果是utf8mb4）（ASCII用一个字节，GB2312用2个字节，utf8使用3个字节，utf8mb4用最大4个字节(如果存储emoj表情就要用这个字符集)）。
3. 如果内容占用字节数较少，就用一个字节表示变长字节长度，如果内容占用较多，就用两个字节表示。 2个字节大小就是16位，最大可以表示65535个字节。
4. 变长字段长度列表中只存储值为非NULL的列内存占用的长度，值为NULL的列的长度是不存储的。 并不是所有记录都有这个变长字段长度列表部分，如果没有这个数据类型就不需要。

比如:

一行数据有VARCHAR(10) VARCHAR(5) VARCHAR(20) CHAR(1) CHAR(1)，一共5个字段，其中三个是变长字段，此时假设一行数据是这样的：abcde ab abc a a

前三个是变长，长度分别为 5 2 3，十六进制就是0x05、0x02、0x03

又因为是逆序，所以是0x03、0x02、0x05，然后接着就是NULL值列表、记录头信息..........

PS：实际存储的都是二进制

行溢出

一个数据页默认是16KB，16*1024字节=16,384 字节

对于varchar来说，很可能就超出了一页的范围了，怎么办？

这个时候实际上会在那一页里存储你这行数据，然后在那个字段中，仅仅包含他一部分数据，同时包含一个20个字节的指针，指向了其他的一些数据页，如果数据页太多，那些数据页用链表串联起来，存放这个VARCHAR超大字段里的数据。

（上图省略了行的一些附加信息）

简单说：一页放不下一行数据了，所以就要换个页存放，这就是行溢出。

关于允许字节数

varchar(M)，M是字符个数，根据一个字符占用的字节设为N（ASCII用一个字节，GB2312用2个字节，utf8使用3个字节，utf8mb4用4个字节），那么实际需要用到的字节空间是M*N。

假设它实际存储的字符串占⽤的字节数是L：

---

如果M×N <= 255，那么使⽤1个字节来表⽰真正字符串占⽤的字节数。

InnoDB在读记录的变长字段长度列表时先查看表结构，如果某个变长字段允许存储的最⼤字节数不⼤于255时，可以认为只使⽤1个字节来表⽰真正字符串占⽤的字节数。

如果M×N > 255，则分为两种情况： 如果L <= 127，则⽤1个字节来表⽰真正字符串占⽤的字节数。 如果L > 127，则⽤2个字节来表⽰真正字符串占⽤的字节数。

InnoDB在读记录的变长字段长度列表时先查看表结构，如果某个变长字段允许存储的最⼤字节数⼤于255时，该怎么区分它正在读的某个字节是⼀个单独的字段长度还是半个字段长度 呢？

该字节的第⼀个⼆进制位作为标志位：如果该字节的第⼀个位为0，那该字节就是⼀个单独的字段长度（使⽤⼀个字节表⽰不⼤于127(01111111)的⼆进制的第⼀个位都 为0）；

如果该字节的第⼀个位为1，那该字节就是半个字段长度。

对于⼀些占⽤字节数⾮常多的字段，比如说某个字段长度⼤于了16KB，那么如果该记录在单个页⾯中⽆法存储 时，InnoDB会把⼀部分数据存放到所谓的溢出页中，在变长字段长度列表处只存储留在本页⾯中的长度，所以使⽤两个字节也可以存放下来。

总结⼀下就是说：如果该可变字段允许存储的最⼤字节数（M×W）超过255字节并且真实存储的字节数（L）超过127字节，则使⽤2个字节，否则使⽤1个字节。

例如：

新建了一个表，表中只有一个字段，utf8mb4，一个字符，最大4个字节，可以看到提示信息设置为16384都是不可以的

16383可以设置，但是可以设置进去这么多字符吗？保存满这么多个字符的话，就是16383*4=65,532个字节

行最大长度是65535字节，但是只允许设置16383个字符，即使没到长度，也是不可以设置的。

例如：

utf8mb4，一个字符最大4个字节，使用单个字母测试，一个a占用一个字节

很显然，65532个字节是不行的，因为本身最大只能是16383个字符。

65532是肯定不行的，16383个是否可以？

例：

选用emoji 表情，他将会占用4个字节。

可以成功保存，查询到的数据是65532个字节

刚才有提到，目前这个表只有一个字段，是VARCHAR（16383），而且，数据也已经填满了

打开设计表，尝试新增一个字段，直接报错了

其实，不管是否保存数据，也一样不可以添加新的字段，因为空间已经被那个字段占光了，用不用是他的事情，别人已经不能用了。

创新创建一个表，初始状态：

MYSQL中BIGINT 占用8个字节，INT占用4个字节

VARCHAR的长度，是按照最大4个字节计算出来的，所以：

16383-1（四个字节）=16382，就可以新增一个int字段

16383-2（八个字节）=16381，就可以新增一个BIGINT字段

新增INT：

新增BIGINT，16831可以，16832不可以

所以VARCHAR（M） 还受制于其他的字段的，归根结底一句话，一行65535，如果只有一个字段，也就是一列，那么在utf8mb4下最多可以16383，如果先设置了别的字段，这个都不可以设置到16383，要进行计算，减去相关字节占用

NULL值

NULL，一行数据中也可能有多个NULL，如果都保存一份数据，很显然浪费空间，所以也是使用值列表进行标记，这样节省空间。

1. 首先统计表接口中允许为NULL值的列(主键和unique key是不允许为NULL的)
2. 如果表中没有允许存储 NULL 的列，则 NULL值列表 也不存在了，否则将每个允许存储NULL的列对应⼀个⼆进制位，⼆进制位按照列的顺序也是 逆序排列

• ⼆进制位的值为1时，代表该列的值为NULL。
• ⼆进制位的值为0时，代表该列的值不为NULL。

3. MySQL规定NULL值列表必须⽤整数个字节的位表⽰，如果使⽤的⼆进制位个数不是整数个字节，则在字节的⾼位补0。

比如：假设数据为('a', 'b', NULL, NULL); 第1、3、4列 允许为null。

对应的数据 第一列允许为null，但不是null，值为0；

第三列允许为null，是null，值是1 ；

第四列，允许位null，是null，值为1；

所以是011，又因为要逆序，所以数据为110

又因为需要整数字节，最少需要一个字节，一个字节8位，那么补齐前面5个0，结果就是0000 0110

记录头

记录头是固定的5个字节，也就是5*8=40位

• 预留位1、2 ：暂未使用
• delete_mask ：当前记录被删除的标志位，
• min_rec_mask ：B+树的每层非叶子节点中的最小记录的标志位
• n_owned ：当前记录拥有的记录数
• heap_no ：当前记录在记录堆中的位置
• record_type ：当前记录类型。具体地，0: 普通记录；1: B+树非叶子节点记录（即所谓的目录项记录）；2: 最小记录；3: 最大记录
• next_record ：下一条记录的相对位置

所以，一行记录的格式大致是这样子的

其他字段

针对于每一行数据，还需要有一个行唯一标识，也就是唯一ID，标识这行数据。如果没有指定主键和unique key唯一索引的时候，他就内部自动加一个DB_ROW_ID作为主键，如果有那么就不需要了，占用6个字节。

然后是DB_TRX_ID字段，这是跟事务相关的，他是说这是哪个事务更新的数据，这是事务ID，占用6字节

最后是DB_ROLL_PTR字段，这是回滚指针，是用来进行事务回滚的，占用7个字节

所以完整的形式为：

再说记录头

delete_mask：

这个属性标记着当前记录是否被删除，占用1个二进制位，值为0的时候代表记录并没有被删除，为1的时候代表记录被删除掉了。

被删除的记录不立即从磁盘上移除，因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。这个delete_mask位设置为1和将被删除的记录加入到垃圾链表中是两个阶段。

PS：这种打标记先不删除是很常见的一种操作，比如，是否听过如果删除了文件，如果没有新写入的数据，还能够找回文件？

min_rec_mask：

B+树的每层非叶子节点中的最小记录都会添加该标记。值为1，表示该条记录是B+树的非叶子节点中的最小记录；值为0，意味着该条数据不是B+树的非叶子节点中的最小记录。

n_owned：

表示当前记录拥有的记录数。

heap_no：

这个属性表示当前记录在本页中的位置。

record_type：

这个属性表示当前记录的类型，一共有4种类型的记录，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录。从图中我们也可以看出来，我们自己插入的记录就是普通记录，它们的record_type值都是0，而最小记录和最大记录的record_type值分别为2和3。

next_record：

表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。

从第一条记录的真实数据的地址处向后找N个字节便是下一条记录的真实数据。

带有最大最小纪录的记录样式

另外，MySQL自动给每个页里加了两个记录，因为这两个记录并不是我们自己插入的，所以有时候也称为伪记录或者虚拟记录。

这两个伪记录一个代表最小记录，一个代表最大记录。

因为有隐藏的插入的最小纪录和最大记录，而且本身数据记录行是有顺序的，那么他们的逻辑就是下图所示

最小纪录的下一个是第一个记录，最后一个记录的下一个是最大记录

next_record指针之所以指向记录头信息和真实数据之间的位置，而不是指向下一条整条记录的开头位置，也就是记录的额外信息开头的位置 因为这个位置向左读取就是记录头信息，向右读取就是真实数据。

我们前边还说过变长字段长度列表、NULL值列表中的信息都是逆序存放，这样可以使记录中位置靠前的字段和它们对应的字段长度信息在内存中的距离更近，可能会提高高速缓存的命中率。

因为文件加载、程序运行、缓存等都有局部性原理，访问这个指针的时候，当然是离他越近越有可能同时就被加载到内存中了，缓存命中率自然高。

数据页

对于磁盘文件是以数据页的形式存储的，也就是对应着前文提到的Buffer Pool缓存页。

数据页是由行数据，以及一些附加信息组合而成的。

每个数据页，实际上是默认有16kb的大小（可以设置 innodb_page_size）

InnoDB 中的「页」并非只有一种，比如有存放 Insert Buffer 的页、存放 undo log 的页、存放数据的页等等。

其中我们最关注的还是存放我们表数据的页，又称「索引页」，或者数据页。

就是这个索引页(聚簇索引)保存了所有的数据。

后续提到的数据页指的就是这个索引页。

一个数据页拆分成了很多个部分，大体上来说包含了文件头、数据页头、最小记录和最大记录、多 个数据行、空闲空间、数据页目录、文件尾部。

他的逻辑与Buffer Pool一样，想要管理缓存页，通过各种链表进行组织管理；

数据页也需要很多额外的信息对他们进行管理，否则怎么能够知道这些数据到底都是什么呢

上面是介绍InnoDB数据页，还有很多类型，并不是说所有的页面结构都如上图所示。

但是所有的页，都是下图这种样式的。

FIleHeader

名称	占用字节	说明
FIL_PAGE_SPACE_OR_CHKSUM	4	页的校验和（checksum值）
FIL_PAGE_OFFSET	4	页号
FIL_PAGE_PREV	4	上一个页的页号
FIL_PAGE_NEXT	4	下一个页的页号
FIL_PAGE_LSN	8	页面被最后修改时对应的日志序列位置（英文名是：Log Sequence Number）
FIL_PAGE_TYPE	2	该页的类型
FIL_PAGE_FILE_FLUSH_LSN	8	仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	4	页属于哪个表空间

FIL_PAGE_SPACE_OR_CHKSUM

校验和，简单说就是数据校验工具，用来快速确认这一串字节序列是否有问题，比如网络发送有校验。

FIL_PAGE_OFFSET 每一个 页 都有一个单独的页号，就跟你的身份证号码一样， InnoDB 通过页号来可以唯一定位一个页

FIL_PAGE_TYPE 这个代表当前 页 的类型

类型名称	十六进制	描述
FIL_PAGE_TYPE_ALLOCATED	0x0000	最新分配，还没使用
FIL_PAGE_UNDO_LOG	0x0002	Undo日志页
FIL_PAGE_INODE	0x0003	段信息节点
FIL_PAGE_IBUF_FREE_LIST	0x0004	Insert Buffer空闲列表
FIL_PAGE_IBUF_BITMAP	0x0005	Insert Buffer位图
FIL_PAGE_TYPE_SYS	0x0006	系统页
FIL_PAGE_TYPE_TRX_SYS	0x0007	事务系统数据
FIL_PAGE_TYPE_FSP_HDR	0x0008	表空间头部信息
FIL_PAGE_TYPE_XDES	0x0009	扩展描述页
FIL_PAGE_TYPE_BLOB	0x000A	BLOB页
FIL_PAGE_INDEX	0x45BF	索引页，也就是我们所说的 数据页

FIL_PAGE_PREV 和 FIL_PAGE_NEXT

InnoDB 都是以页为单位存放数据的，而表中的数据基本上是不可能都只分配一个页就足够了，页之间也不可能要求必须是连续的，因为如果要求连续可能没有那么大完整的空间。所以就有了这个上一个下一个指针，用来链接相关的页面。

并不是所有类型的页都有上一个和下一个页的属性，数据也是有的。

Page Directory

页内记录的检索。

因为是连续有序的，所以从最小纪录就可以遍历循环得到想要的结果，这是最自然的想法，但是效率也确实不高。

是否还可以优化检索？

Page directory 就类似书目录的索引，通过这个索引可以快速定位到内容。

提出了一个槽（slot）的概念，用于快速定位页面中行记录（槽也可以理解成分组的概念）。

slot中记录了行记录在页面内偏移。

但是，目录槽和行记录并非一一对应的关系，每个slot最多可管理8个物理行记录。

定位时首先是二分查找dir slot，再通过其中记录的偏移找到行记录，进而再进行顺序查找定位特定行。

slot类型	记录数说明
最小纪录 infimum slot	只能有 1 条记录
最大记录 supremum slot	1~8 条之间记录
其他slot	只能 4~8 条之间记录

初始时，有一个最大记录和最小纪录。

创建infimum和supremum record后，同样会为这两条系统行记录初始化相应的slot

每个slot占据2个字节，slots被存储在整个页面数据的最后，也就是File Trailer前面，File Trailer 占用8个字节infimum slot位于页面的PAGE_SIZE-8-2个字节处，supremum slot则位于页面的PAGE_SIZE-8-4个字节处。

再次提及上面记录字段中的两个相关字段

n_owned：

表示当前记录拥有的记录数。

next_record：

表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。

初始时，两个slot，分别指向最小和最大的地址偏移量，地址偏移量就是在一条行记录的头和数据中间。

图中的箭头，就表示指针，也就是保存了相对应的位置地址。

n_owned 表示个数，初始时都是1。

继续插入数据后

过程概括：

1. 将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）分组；
2. 每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的 n_owned 属性表示该记录拥有多少条记录，也就是该组内共有几条记录，被管理的保存的是0；
3. 将每个组的最后一条记录（最大一条记录）的地址偏移量，按顺序存储到靠近页的尾部的地方，这个地方就是所谓的 Page Directory ，也就是 页目录 （此时应该返回头看看页面各个部分的图）。

槽 slot就是一个一个的偏移量挨在一起。

再简单点就是：

分成了好多组，每组都会有一个最大值记录，和他自身的偏移量，最大值存在他自己这条记录的n_owned，偏移量保存在Page Directory上。

数据就像这条横着的箭头，从小到大排列，被按照一定的距离进行分割，分割点就是slot。

先找到中间的那个slot，可以找到他的数据，进而可以进行判断；然后就可以决定下一步在左边，还是在右边，典型的二分法查找，效率比从一头循环遍历大大提高。

Page Header

名称	占用字节	描述
PAGE_N_DIR_SLOTS	2	在页目录中的槽数量
PAGE_HEAP_TOP	2	还未使用的空间最小地址，也就是说从该地址之后就是 Free Space
PAGE_N_HEAP	2	本页中的记录的数量（包括最小和最大记录以及标记为删除的记录）
PAGE_FREE	2	第一个已经标记为删除的记录地址（各个已删除的记录通过 next_record 也会组成一个单链表，这个单链表中的记录可以被重新利用）
PAGE_GARBAGE	2	已删除记录占用的字节数
PAGE_LAST_INSERT	2	最后插入记录的位置
PAGE_DIRECTION	2	记录插入的方向
PAGE_N_DIRECTION	2	一个方向连续插入的记录数量
PAGE_N_RECS	2	该页中记录的数量（不包括最小和最大记录以及被标记为删除的记录）
PAGE_MAX_TRX_ID	8	修改当前页的最大事务ID，该值仅在二级索引中定义
PAGE_LEVEL	2	当前页在B+树中所处的层级
PAGE_INDEX_ID	8	索引ID，表示当前页属于哪个索引
PAGE_BTR_SEG_LEAF	10	B+树叶子段的头部信息，仅在B+树的Root页定义
PAGE_BTR_SEG_TOP	10	B+树非叶子段的头部信息，仅在B+树的Root页定义

行记录会按照我们指定的 行格式 存储到 User Records 部分。

每当我们插入一条记录，都会从 Free Space 部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到 User Records 部分，当 Free Space 部分的空间全部被 User Records 部分替代掉之后，这个页就使用完了，如果还有新的记录插入的话，就需要去申请新的页

聚簇索引与B+树

而数据页，则被组织成一颗B+树，而且是聚簇索引。

为什么非要找到一个唯一标识呢？

其中一部分原因就是要构造一颗B+树。

请参考B+树相关介绍 https://www.crazybytex.com/thread-234-1-1.html

CREATE TABLE Customer(
`custId` INT,
`name` VARCHAR(50),
`mobileNo` VARCHAR(11)
);
ALTER TABLE `Customer` ADD PRIMARY KEY (`custId`);
ALTER TABLE `mobileNo` ADD UNIQUE INDEX (`MobileNo`);

CustId是主键。

假设数据如下：

+--------+--------+---------+
| CustId |  Name  | PhoneNo |
+--------+--------+---------+
|      1 | Jerry  |     111 |
|      2 | Moly   |     112 |
|      3 | Tom    |     108 |
|      4 | Tony   |     107 |
|      5 | Roc    |     101 |
|      6 | Moly   |     103 |
|      7 | Sunny  |     102 |
|      8 | Jack   |     110 |
|      9 | Lily   |     108 |
|     10 | Emily  |     105 |
|     11 | Lee    |     104 |
|     12 | Will   |     113 |
|     13 | Chen   |     109 |
|     14 | An     |     106 |
+--------+--------+---------+

• 每个表的主键有且只有一个 每行的都是独一无二的 非空

其实聚集索引的思想是在B 树结构中存储一个完整的表。

聚簇索引是一个物理 概念，有了聚簇索引 ，主键和数据记录本身就“聚簇 ”在一起。数据记录均按主键顺序存储 。这就是我们称之为聚簇索引的原因。

https://www.crazybytex.com/thread-234-1-1.html中有说明B+其实就是B的改进版本

聚集索引定义数据在表中的物理存储顺序。表数据只能以一种方式排序，因此，每个表只能有一个聚集索引。

MySQL 如何选择集群密钥？

1. 当存在主键时，将其用作群集键 。
2. 当没有主键时，使用第一个NOT NULL 唯一 键作为群集键。
3. 如果以上都不是，InnoDB将生成一个隐藏列作为集群键。

在聚集索引中，叶节点包含基础表的数据****页 。

根级和中间级节点包含保存索引行的索引页 。

每个索引行都包含一个键值和一个指针，指向 B 树中的中间级别页或索引叶级别中的数据行。

其实说来说去，去掉一些复杂细节的地方。

简单说就是一颗B+树，叶子结点有了完整的数据，其他节点只有索引。

二级索引(非辅助索引)

聚簇索引是叶子结点存储了所有的数据，如果一个索引结构叶子结点并没有存储数据，只是存储了索引和主键，那么就是二级索引。

因为如果需要除了索引字段以外的其他数据，全部都需要通过主键再次查询聚簇索引来获得数据，这也是二级这个二的由来。