40张图总结MySQL是如何组织数据的！

开发中，免不了和 MySQL 数据库打交道，为了弄明白 MySQL 到底是如何存储数据的，花费了一些时间，详细阅读相关资料后，写下此文，方便后续自己复习相关知识。

本文的主要参考资料：

• 书籍：从根上理解MySQL

重要的事情说三遍！！！

注：图中的 File Tail 对应的是 File Trailer，并不影响理解，因此我错了但我不就是不改！(手动狗头保命.jpg)。

写在前面一些无关紧要的内容：

在 MySQL 中，可通过如下命令查看默认的行格式

show variables like '%row_format%';

可以通过如下命令修改表的行格式

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
    
ALTER TABLE 表名 ROW_FORMAT=行格式名称

不同版本的 MySQL 的默认行格式不一样，MySQL 支持四种行格式

• Compact: 本文主要介绍的行格式。
• Redundant: 5.0 之前的行格式，不做介绍。
• Dynamic: 和Compact行格式差不多，在处理页溢出时不同于 Compact，不介绍。
• Compressed：和Compact行格式差不多 会采用压缩算法，对页面压缩，节省空间，不介绍。

开始吧！

图片来自网络

SHOW ENGINES;

可通过此输出查看数据库的默认存储引擎。

Compact 行格式主要由几部分构成，如下图所示：

其中可变长字段长度列表，空值列表都不一定会存在，取决于具体的建表语句！

可变长字段长度列表

可变长字段列表：该列表不一定存在，取决于在定义字段时，是否使用VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型字段来进行定义字段，如果存在上述类型的定义的列，则列表就会存在；该列表中字段长度的顺序为字段自定义顺序的逆序。 除此之外变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不会储存的。以下表为例，说明逆序

CREATE TABLE `test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `char_col` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `nickname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
​
SET FOREIGN_KEY_CHECKS = 1;
INSERT INTO `test` VALUES (1, 'aaaa', 'bbb', NULL, 'bababa');

在上面的SQL语句中，添加了一条记录，其变长字段的长度顺序如下图所示：

上面的情况比较简单，因为变长字段的值长度比较小，当字段的值较大时，MySQL 是如何知道我该用一个字节还是两个字节来存储数据呢？

InnoDB操作的最小单元是页，一个页的大小为16kb即 24 Kb，因此 2 个字节 16个bit完全够用！

这个和我们在创建数据库时使用的字符集有关！以utf8为例。 可变长列的长度计算 W M L

• W：该字符集中一个字符能够占用最大的字节数，在utf8中一个行字代表3个字节。
• M：该列最多存储的字符数，即创建列时 varchar(M) 中的值 M
• L ：实际的存储长度
• M * W ≤ 255：当 W * M的值小于等于255时 则用一个字节来表示
• M * W ＞255
  • 如果 L ≤ 127 则用一个字节
  • 如果 L > 127 则用两个字节 通过判断字节的最高位是否为 1 来判断当前字节的有效数据是半字节还是一个字节，极限情况下取值为 214 。

NULL 值列表

和可变长列表一样，该列表不一定存在，是否存在取决于在自定义表字段时，是否允许值为null，如果没有允许为null的字段，则没有null值列表，如果允许字段为null，则一个字段对应一个bit位，该列表中的顺序是字段定义顺序的逆序，为 1 时代表该列的值为null，为 0 时代表不为null，null值列表的长度必须是整个字节，不满一个字节则高位补 0。如下图，4 个列的值可以取 null ，则只需要一个字节即可，如果是 9 个列，则需要两个字节！

记录头信息

记录头信息：是 MySQL 给每一条记录添加的信息，每一条记录都有！！共五个字节长度！具体组成由以下基本分构成

• 2个预留位：1个1bit，共 2 个bit，没有用到！
• delete_mask：删除位，1 个bit， 1 删除 0未删除！从这里也可以看出，MySQL 底层做的也是逻辑删除。
• min_rec_mask：b+树每层非叶子节点中的最小记录会添加此标记，将值置为 1 占据1个bit
• n_owned： 当前记录拥有的记录数 取值不会超过8， 4个bit。后面详细说！
• heap_no: 堆号，占据13bit
• record_type：记录类型 3个bit
• next_record: 下一条记录的相对位置，2个字节，实际上就是一个指针！

简单图示如下图所示：

详细构成：

第一个字节：

第2-3字节：

第4-5字节：

五个字节分别所起的作用在图中已说明！

隐藏列

隐藏列也是 MySQL 给我们额外添加的数据，主要由三部分组成！

• row_id：6个字节，非必须，当建表时，没有主键且没有唯一列时，会生成此列。
• trx_id：6个字节，必须有，最后修改此纪录的事务id，混个脸熟，本文不涉及。
• roll_point：7个字节，必须，混个脸熟，本文不涉及。

示意图如下：

行格式小结

注：char(M)这种列，在定长字符集如ASCII字符集，不会加入到变长字符集列表，在变长字符集下，会加入到可变长列表，同时要求必须占用M个字节，即使是一个空字符串，也会占据这么多。

其中 File Head 和 File Tail 是通用的，唯一的就是中间部分的不同，存储我们存入的数据的数据页的结构如下图

上图中的几部分主要有

• File Head 38 Bytes
• Page Head 56 Bytes
• infimum + supremum 26 Bytes
• user record
• Free Space
• Page Directory >= 4 Bytes
• File Tail 8 Bytes

首先还是从记录部分说起即 User Record部分

User Record

首先是最大最小记录 Infimum supremum，这两条记录各占 13 个字节即 5 个字节的记录头信息和8个字节的数据组成，其图示如下图

最大记录图示：

最小记录图示：

暂时不要管图中的数字！

在我们创建表后，我们通常会插入数据，不然建表的意义干啥？当我们 insert 数据之后，此时数据页的user record 部分的结构就如下图所示。

上图中有如下几个属性要关注：

n_owned：最小记录里的值是 1，最大记录的值是 4，而三条用户记录是 0，图中没有画出！ 为什么他们的值不是一样，这是因为为了方便我们高效的查找数据，InnoDB 把记录分了组，最小记录只能自己一组，这就是为什么它的值为 1，最大记录的组取值范围为 1~8，其余的组的取值范围只能是 4~8。这里只有三条插入的记录加上自身所以最大记录的取值为 4！

heap_no: 当前记录在本数据页的位置， 插入的记录的 heap_no 是从 2 开始的，这是因为最小最大记录的堆号为 0 和 1。

next_record：当前记录的下一条记录，这个下一条记录不是指的我们插入的顺序，是指按照主键大小的进行排序的下一条记录。next_record 指向的不是变长字段，而是下一条记录的 next_record 位置，这是因为这个位置向右就是数据，向左就是记录相关的统计信息！！

Page Directory

当在数据页中插入了大量的数据之后，假设数据页布局的示意图如下图：

此时如何进行数据搜索呢？这就进行需要这一部分的主角出场了 Page Directory 页目录！我们在前面已经知道了，数据是按照主键大小进行排列的，也就是说他们实际上是一个主键递增的序列！想到了什么！二分查找算法！在说如何利用二分查找算法进行查找数据之前，首先来看看页目录是如何形成的。

将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。

每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。

将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的Page Directory，也就是页目录（此时应该返回头看看页面各个部分的图）。页面目录中的这些地址偏移量被称为槽（英文名：Slot）占据2个字节，所以这个页面目录就是由槽组成的。

假设数据页上有三条用户插入的数据，此时页目录结构如下图所示：

99 和 112 代表的是距离文件开头的偏移距离为 99 字节 和 112 字节。

如果数据页有超过8条数据时，那么 slot 的情况是如何呢？这就需要讨论下，InnoDB是如何进行数据分组的了！

• 初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
• 之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。
• 在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。

假定此时插入了 9 条数据 ，则情况如下：

上面说了，在数据页内数据较多，因此在查找记录时，最好不要遍历该链表，而我们提到了二分查找。因此，就来看看这个查找过程！

• 首先找到对应数据所在的页面中的那一组，即所在的 slot
• 因为 slot 记录的是该组的最大记录，因此只要找到上一个slot的记录，该记录的下一条记录就是所在slot 的最小记录
• 通过 next_record 遍历即可

假定上图中的 heap_no 和记录的主键是一样的，查找主键为8的数据其过程如下

• ( 0 + 2 ) / 2 = 1，找到第2个槽，他对于的 id 为 5 < 8
• 此时可以确定数据在槽 2 所对应的组里
• 从 5 + 1 = 6 处向后遍历即可！

Page Header

Page Header的作用就是为了得到数据页中存储数据的信息，该部分占据56个字节是固定的，具体构成如下表：

File Header

Page Header 描述的业内相关的信息，那么 File Header 的主要作用就是描述当前页的一些通用信息！就有一点感觉一个主内一个主外一样！

File Header的结构。固定占据38个字节

从具体作用来看：

主外的负责和其他数据页形成双向链表，如下图

File Trailer

即图中的 File Tail，写错了！但是意思是那么个意思！，占据八个字节！

• 前4个字节代表页的校验和
• 后4个字节代表页面被最后修改时对应的日志序列位置（LSN）