InnoDB数据存储结构

我们常说数据是存储在数据库中，那数据库中的底层是怎么存储的?

下面我们一起来探讨下。

目录结构如下：

少巴巴，直接上正文。

数据的存储结构

索引是在存储引擎中实现的，MySQL 服务器上的 存储引擎负责对表数据的读取和写入。

但是不同存储引擎对 数据存放格式一般是不同的，甚至有的存储引擎都不用磁盘存储数据，比如：Memory。

MySQL 默认的存储引擎是 InnoDB，所以下文均以 InnoDB 展开叙述。

MySQL 数据存储目录

先看下 MySQL 数据库的文件存放在哪个目录中？

使用命令：

show variables like 'datadir';
或
select @@datadir

这个是修改后的目录路径，实际上 MySQL 默认的存储路径为：/var/lib/mysql。

我们每创建一个数据库 database_name，这个目录下（包括自定义的）就会创建一个以 数据库名为名的目录，然后里面存储表结构和表数据文件。

Innodb 存储引擎创建的任何一张表的都会有两个文件：

• test_table.frm：存储 表结构的文件，保存表的原数据信息
• test_table.ibd：存储 表数据的文件，表数据既可以存储共享表空间文件中（ibdata1中），也可以存储在独占表空间中（后缀 .idb中），是否存储在独占表中，可以通过参数 innodb_file_per_table控制，设置为 1，则会存储在独占表空间中，从 MySQL 5.6.6 版本之后，innodb_file_per_table默认值就为 1 了，因此此后的版本，表数据都是存储在独占表中的。

表数据文件存储结构是怎么样的？

表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB存储引擎的逻辑存储结构大致如下图（图源：小林 coding）：

这个图很是形象，也很到位了。

咱从下往上看，介绍下各名词的含义：

• 行（row）： 数据库中的数据都是按行（row）存储的，行记录（是统称）根据不同的 行格式，有不同的存储结构。下文我们重点介绍 InnoDB 存储引擎的行格式。
• 页（page）： 记录是按行存储的，但是数据库的读取并不是以 行单位，而是以 页 为单位，每页的大小为 16KB，否则读取一次只能读取一行数据（也就是一次 I/O 操作），只能处理一行数据，效率太低了。
• 区（extent）： B+Tree 的每一层节点之间都是通过双向链表链接的，以页为单位，相邻的两个页之间位置并不是连续的，可能离的非常远，那么数据量大的情况在查询的时候就会产生大量的随机 I/O 操作（效率低）。为了解决这个问题，为某个索引分配空间的时候按照 区为单位，一个区的大小为 1MB，对于 16KB大小的页来说，连续的 64 个页划分为一个 区，这样就使得相邻的页的物理位置也是相邻的，就可以使用顺序 I/O 了。
• 段（segment）： 表空间由多个段组成，段时由多个区组成。段一般分为数据段、索引段和回滚段等。
  • 索引段：存放 B+Tree 非叶子节点区的集合
  • 数据段：存放 B+Tree 叶子节点的集合
  • 回滚段：存放回滚数据区的集合（MVCC 就是利用了回滚段实现了多版本查询控制）

再对比下康师傅画的图（原理是一样的）：

补充：

• 段是数据库中的分配单位
• 不同类型的数据库对象以不同的段形式存储
• • 创建一张表默认会创建一个表段（数据段）
  • 创建一个索引默认会创建一个索引段
• 表空间（Tablespace） 是一个逻辑容器，表空间存储的对象时段，在一个空间中可以由一个段或多个段，但是一个段只能属于一个表空间。
• 数据库是由一个或多个表空间组成，表空间从管理是哪个可以划分为 系统表空间、用户表空间、撤销表空间、临时表空间等。
• 系统表空间，/var/lib/mysql/下有一个文件 ibdata1文件，这个文件就被称为 系统表空间。加入创建一个表 test_table:
• • 表结构的元数据会存储在 test_table.frm中
  • 如果采用 系统表空间 模式，数据信息和索引信息都会存储在 ibdata1中
  • 如果采用 独立表空间 模式，数据信息和索引信息都会存储在 test_table.ibd中
• 

根据上图，我们对 InnoDB 数据的存储结构有了大致的了解，下面咱接着来。

数据页结构

了解行记录存储格式之前，我们先了解下页的内部存储构造。

InnoDB 默认将数据划分为若干个页，页的大小默认为 16KB。

在数据库中，不论是读取一行还是读取多行数据，都是将这些行所在的页一次性从磁盘中加载到内存中（一次 I/O 操作），数据库 I/O 操作的最小单位就是页 。

查看 InnoDB 存储引擎一个数据页的大小：

show variables like '%innodb_page_size%'
或者
select @@innodb_page_size

扩展：

SQL Server 中页的大小为 8KB，而在 Oracle 中我们用术语 块 (Block)来代表 页，Oracle 支持的块大小有：2KB、4KB、8KB、32KB 和 64KB。

页的内部结构

这 7 个部分作用分别如下所示：

归类为三大部分：

第 1 部分：文件头和文件尾

File Header（文件头）

描述各种页的通用信息（比如：页的编号、其上一页、下一页是谁等）。

文件头的大小为 38 字节。构成如下：

重点讲解上述标为黄色的属性。

FIL_PAGE_OFFSET（4 字节）： 页号、页码，好比人的身份证号一样，InnoDB 可以通过页号 唯一确定一个页。

FIL_PAGE_TYPE（2 字节）： 代表当前页的类型，页的类型有以下分类（重点是 Undo 日志页 、系统页）。

FIL_PAGE_PREV（4 字节）和 FIL_PAGE_NEXT（4 字节）： InnoDB 是以页为单位存储数据的，数据分散到多个不连续的页中需要把这些页关联起来，FIL_PAGE_PREV 和 FIL_PAGE_NEXT就是记录上一页和下一页的页号。这也就是上一篇 索引的数据结构中所说的，页与页之间是通过双向链表关联起来的。

从而保证：页与页之间在物理上不连续，但在逻辑上连续。

FIL_PAGE_SPACE_OR_CHKSUM（4 字节）： 代表当前页面的校验和（checksum）。

什么是校验和？

简单理解，就是一个很长的字符串，通过某种特定的算法将整个字符串计算出一个比较短的值，这个值就是这个字符串的 校验和。最常见的是 Hash 算法。

校验和有什么作用？

eg：如果要比较两个很长的字符串，如果直接进行比较，会比较慢，如果通过比较两个字符串的校验和（生成校验和耗时可以忽略不计），如果校验和相同，则代表两个字符串相同，反之则不同。

重点： 文件头和文件尾中都有这个属性：FIL_PAGE_SPACE_OR_CHKSUM。

在页面中的作用：

InnoDB 存储引擎以页为单位进行 I/O 操作，如果某个页从磁盘加载到内存中被修改了，那么 在修改后的某个时间段内需要将数据同步到磁盘中，假如在同步到一半的时候断电了，会造成该页数据传输的不完整。

为了验证一个页是否完整（也就是在同步的时候有没有发生只同步到一半的情况），这个时候可以通过 文件头的校验和和 文件尾的校验和进行比对，如果两个值不同则说明页的传输有问题，需要重新进行传输或回滚，否则任务页的传输已经完成。

再具体一点的过程：

每当一个页面在内存中被修改了，在同步之前要把他的校验和算出来，因为 File Header 在页面的最前面，所以最下被同步到磁盘中，当完全写完时，校验和也会被同步到 File Trailer 中，如果完全同步成功，文件头部和尾部的校验和应该相同，如果同步的过程中发生了异常，则文件头的校验和代表已经修改过的页，文件尾的校验和代表原来的页，这就说明同步数据出现了差错，需要进行 数据重试 或者 回滚 等操作。这里的校验方式就是采用的 Hash 算法。

FIL_PAGE_LSN（8 字节）： 页面最后被修改时对应的日志序列位置（Log Sequence Number，简称：LSN）。

File Trailer（文件尾）

前 4 个字节： 代表校验和，和文件头中的校验和相对应。

后 4 个字节： 代表页面最后被修改时的日志序列位置（LSN），这个部分也是为了校验页的完整性，如果文件头和文件尾的 LSN 值不同，也说明在同步的过程中出错了。

第 2 部分：空闲空间、用户记录、最大最小记录

这部分主要是存储记录，所以 用户记录 和 最大最小记录占据了主要空间。

Free Space（空闲空间）

存储的记录会按照指定的 行格式存储到 User Record部分。在最开始生成页的时候，并没有 User Record部分。每次插入一条数据的时候，都会从 Free Space（空闲空间）中申请一条记录大小的空间划分为 User Record部分，当 Free Space 的空间被申请完之后，也就代表 Free Space 全部被 User Record 替代了，这个时候如果要在插入新的数据，就要申请新的数据页了。

User Record（用户记录）

User Record 中的记录按照 指定的行格式相互之间形成 单链表。

这里的每一行的用户记录对应下文中的 InnoDB 一行记录是如何存储的？这里先不描述，下文逐步讲解。

Infimum+Supremum(最大和最小记录)

对于一条完整的记录来说，比较记录的大小是通过 主键值来判断的，记录会按照主键值大小依次递增排列存储。

InnoDB 规定的最小记录和最大记录构造很简单，都是由 5 字节大小的记录头信息和 8 字节大小的固定部分组成，如下图所示：

这两条记录不是我们自定义的，是 InnoDB 在生成页的时候默认创建的，所以它们来并不存放在 User Records 部分，而是单独存放在 Infimum + Supremum部分，如图所示：

这里有个特殊属性 heap_no，当前页的记录序列号，我们插入数据的记录 heap_no值都是从 2开始，就是因为会默认创建两条最大记录和最小记录，分别占了 0和 1。

第 3 部分：页目录、页面头部

Page Directory（页目录）

假设一条查询 SQL

select * from page_demo where c1 = 3;

方式 1：顺序查找

从 Infimum 记录（最小记录）开始，沿着链表一直往后找，数据量非常大的时候，新能非常差。

方式 2：使用页目录，二分法查找

每个页中的记录分组之后如下图所示：

根据上文举的例子，库中现在有 4 条真是用户记录，还有两条隐含的最大和最小记录，分组之后如下图所示：

上图的槽位：

• 槽 0：指向的是最小记录的地址偏移量
• 槽 1：指向的是最大记录的地址偏移量

再换个角度，单纯从逻辑上看一下这些记录和页目录的关系：

页目录中分组的个数是如何确定的？

这个问题也就是上述分组中，为什么第 1 组中最小记录的n_owned为 1，第 2 组中最大记录的n_owned为 5的问题？

InnoDB 规定：第 1 组只有一条记录，最小记录所在的组。最后一组 也就是最大记录所在的分组，会有 1-8条记录。其他分组，数量在 4-8条记录。【这样做的好处是除了第 1 组外，其余组的记录数会 尽量平分】

分组的步骤如下所示：

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

页目录结构下如何快速查找记录？

为了模拟大数据量下如何查找记录的过程，新增了 12 条数据：

insert into page_demo values
(5, 500, 'zhou'),
(6, 600, 'chen'),
(7, 700, 'deng'),
(8, 800, 'yang'),
(9, 900, 'wang'),
(10, 1000, 'zhao'),
(11, 1100, 'qian'),
(12, 1200, 'feng'),
(13, 1300, 'tang'),
(14, 1400, 'ding'),
(15, 1500, 'jing'),
(16, 1600, 'quan');

根据 InnoDB 规定，分为以下几组槽位：

这里为了方便展示，只保留了 16 条记录的头信息中的 n_owned和 next_record属性，省略了各个记录之间的箭头。

上图中左边的槽位数组就可以采用二分法查找，查询过程如下：

• 找到对应的槽位之后，如果要查找的记录的主键值恰巧为 8，对应上述槽 2 中的最大记录，直接返回
• 如果要查找的记录的主键值为 6，从上图中可以看出也是在槽 2 中，但是我们之前说过，记录与记录之间是通过单链表的形式链接的，所以直接定位到槽 2 是无法往前扫描主键值小的记录
• 这个时候我们可以找到槽 1 对应的最大记录 主键值为 4，根据 next_record往后查找两个位置即可找到主键值为 6的记录

小结：

在一个数据页中查找指定主键值记录的过程分为两步：

Page Header（页面头部）

为了能得到一个数据页中存储的记录的状态信息，

• 比如本页中已经存储了多少条记录？
• 第一条记录的地址是什么？
• 页目录中存储了多少个槽等

特意在页中定义了一个叫 Page Header 的部分，这个部分占用了固定的 56 个字节，专门存储当前页的各种状态信息。

有以下属性：

PAGE_DIRECTION

假如新插入的一条记录的主键值比上一条插入记录的主键值大，我们称这条记录的插入方向是向右，反之则向左。这个标识用来表示最后一条记录插入方向的状态 PAGE_DIRECTION 。

PAGE_N_DIRECTION

假设连续 N 次插入的记录的方向都是一致的，InnoDB 会把沿着同一个方向插入记录的条数记下来，这个条数就用 PAGE_N_DIRECTION这个状态表示。当然如果最后一条记录的插入方向改变的话，这个状态的值就会被清零重新统计。

第 4 部分：从数据页的角度看 B+Tree 如何查询

B+Tree 数据是如何记性记录检索的？

通过 B+Tree 的索引查询记录，首先通过根节点开始逐层检索，直到找到记录所在的叶子节点，然后将整个数据页从磁盘中加载到内存中，页目录中的槽（slot）可以通过 二分查找的方式定位到记录所在的槽（分组），通过 链表遍历的方式查找到记录。

普通索引和唯一索引在查询效率上有什么不同？

唯一索引就是在普通索引上增加了约束，也就是关键字唯一，找到关键字之后就停止检索。

而普通索引存在关键字重复的情况，我们知道 InnoDB 存储引擎索引的一个数据页的大小为 16KB，每次 I/O 操作会将记录所在的整个数据页加载到内存中，因为关键字存在重复的情况，所以查找到关键字的记录之后，相比 唯一索引还会继续往后再多判断几次记录是否符合关键字查询条件，但是在 CPU 中，多的几次判断消耗的时间可以忽略不计，整体上来来说，普通索引和唯一索引在查询效率上没有多大差别。

InnoDB 行格式

InnoDB 一行记录是如何存储的？

这个问题是本文的重点，也是面试中经常问到的问题，所以就引出了下文的 InnoDB 行格式内容。

InnoDB 指定行格式语法

先看下指定行格式的简单语法

#创建表指定行格式
create table table_name(列信息) row_format = 行格式名称

#修改表行格式
alter table table_name row_format = 行格式名称

Compact 行格式

Compact 行数据存储结构

在 MySQL5.1 版本中，默认设置为 Compact 行格式。一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分。

举例： 采用 Compact 行格式创建一张表 page_demo

create table page_demo (
  c1 int,
  c2 int,
  c3 varchar(10000),
  primary key(c1)
) CAHRSET=ascii ROW_FORMAT=Compact

• 字符集：ascii
• 行格式：Compact

表中的每一行记录的行格式如下所示：

这些记录头信息中的各个属性如下（主要 6 个属性）：

其中有两个预留位置没有使用，我们简化之后的行格式如下所示：

向库中插入 4 条数据：

insert into page_demo 
values
(1, 100, 'song'),
(2, 200, 'tong'),
(3, 300, 'zhan'),
(4, 400, 'lisi');

这 4 条记录的行格式如下所示：

上图各方块属性：

• 蓝色方块为记录头信息
• 绿色方块为 数据信息，这里为了展示方便，写的是 10 进制 ，实际上底层存储的是 2 进制

变长字段长度列表

创建一张表 record_test_table：

create table record_test_table(
  col1 varchar(8),
  col2 varchar(8) not null,
  col3 vhar(8),
  col4 varchar(8)
) charset=ascii row_format=Compact

向表里面插入两个数据：

insert into record_test_table(col1, col2, col3, col4)
values
('zhangsan', 'lisi', 'wangwu', 'songhk'),
('tong', 'chen', NULL, NULL);

MySQL 支持一些变成的数据类型，比如 varchar(M)、varbinary(M)、text、blob 等类型，这些数据类型修饰的列被称为 变成字段。边长字段中存储多少个字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存储起来。

在 Compact 行格式中，把所有变长字段的真实数据占用的字节长度存放在记录的开头部位，从而形成一个变长字段长度列表。

注意：

这里存储的变长字段的长度的顺序和表字段创建时的真实顺序是翻过来的，比如：两个 varchar 字段在表中的顺序是 a(10)，b(15)。那么在变长字段长度列表中的顺序是 15,10，翻过来存储的。

根据上面插入的两条真实数据，分析一下各个变长字段真实数据占用的字节长度：

NULL 值列表

Compact 行格式会把可以为 NULL 值的列统一管理起来，存在一个标记为 NULL 值列表中。

如果表中没有可以为 NULL 值的列，那这个 NULL 值列表也就不存在。

为什么要定义 NULL 值列表？

之所以要存储 NULL值，是因为数据都是需要对齐的。如果没有标注出 NULL 值的位置，就有可能在查询数据的时候出现混乱 的情况。如果 使用一个特殊符号代替 NULL 值放到对应的位置，虽然可以达到效果，但是大量为 NULL 值的列会严重 浪费空间，所以直接在 行数据的头部开辟出一块空间 专门用来存储该行数据有哪些是非空数据，哪些是空数据， 格式如下：

• 二进制位为 1：代表列值为 NULL
• 二进制为为 0：代表列值不为 NULL

这样我们回答一个问题，MySQL 中的 NULL 值是怎么存储的？

答：NULL 值是由 NULL 列表记录的，用二进制逆序表示每一行记录中的每一列是否为 NULL 值，0 代表不为 NULL，1 代表为 NULL 值。

假设有一张表有 4 个字段，col1、col2、col3、col4

插入一条记录：'a', NULL, NULL, 'dd'

那 NULL 值列表用二进制表示为：0 1 1 0，转化为 10 进制就是 06。

记录头信息（5 字节）

delete_mask（删除标记）

这个属性标记着当前记录是否被删除，占用 1 个 bit：

• 值为 0：代表记录没有被删除
• 值为 1：代表记录被删除了

被删除的记录为什么还在页中存储？

这些被删除的记录之所以不立即从磁盘的页中移除，是因为移除他们之后，紧跟着他们的记录需要 重新排列，特别是对 聚簇索引的叶子节点，假设移除的是主键值为 1的记录， 那整个聚簇索引的叶子节点会因为这一条记录的删除全部重新排序，导致性能消耗。所以只是将这些删除的记录做一个删除标记和正常记录做个区分，实际上这些被删除的记录会组成一个 垃圾链表，它们所占用的空间被称为 可重用空间，之后再插入的数据，可能会把这些被删除记录占用的空间直接 覆盖掉（复用）。

min_rec_mask（最小记录标记）

B+Tree 的每层非叶子节点中的最小记录都会添加该标记，并且 min_rec_mask的值为 1。

我们自己插入的数据记录的 min_rec_mask的值为 0，所以它们都不是 B+Tree 的非叶子节点中的最小记录（这句话自己理解就行，不要纠结）。

record_type（记录类型）

这个属性代表当前记录的类型，一共有 4 种类型的记录：

• 0：表示普通记录
• 1：表示 B+Tree 非叶子节点记录
• 2：表示最小记录
• 3：表示最大记录

从图中可以看出，我们自己插入的记录的 record_type的值为 0，最大最小记录的 record_type的值分别为 2和 3：

非叶子节点记录 record_type的值为 1的情况（索引的数据结构一文中讲述的内容）：

heap_no（记录位置）

这个属性代表代表当前记录在当前页中的下标位置。

下标为 0、1 的两条记录分别为最大和最小记录，在上文【Infimum + Supremum（最大记录和最小记录） 】中已经提到了，因为这两个记录不是我们插入的，所以有时候也称为 伪记录或 虚拟记录。

n_owned（每组记录数）

页目录（有多个组）中每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned字段的值。

next_record（下一条记录的地址偏移量，非指针）

记录头中该属性非常重要，它表示从 当前记录的真实数据 到 下一条记录的真实数据 之间的 地址偏移量。

比如：第一条记录中的 next_record值为 32，意味着从第一条记录的真实数据的地址处向后找 32 个字节，便是下一条记录的真实数据。

注意： 下一记录并不是按照我们插入顺序的下一条记录，而是按照主键值顺序排列的下一条记录。

InnoDB 底层规定 Infimum 记录（最小记录）的下一条记录就是当前页中主键值最小的记录，而当前页中主键值最大的记录指向的下一条记录就是 Supremum 记录（最大记录）。

下图用箭头指向代替地址偏移量，来表示 next_record。

演示：删除一条记录的操作

根据上图所示，假设删除上图第 2 条记录：

# 删除主键值为2的记录
delete from page_demo where c1 = 2;

删除之后，整个链表也会跟着变化，第一条记录的 next_record就会直接指向第 3 条记录，但是第 2 条记录并没有被真实删除，只是将 delete_mask值变成了 1。下图所示：

变化内容如下：

• 第 2 条记录的 delete_mask变为 1

• 第 2 条记录的 next_record变为 0，代表不再指向真实数据了

• 最大记录的 n_owned的值从 5=> 4，因为当前组少了一条记录

  • 原本当期页算上最大最小记录，总共 6 条记录，分为两个组，最小记录为一个组
  • 四条真实记录和最大记录为一组，所以最大记录中的n_owned的值为 5
  • 现在第二组中删除了一条记录，所以n_owned的值从 5=> 4

演示：增加一条记录的操作

上述主键值为 2 的记录被删除后（变成了垃圾链表），但是存储空间并没有被收回，如果再次把这条记录插入表中，会发生什么？

insert into page_demo values(2, 200, 'tong');

如下图所示：

变化内容如下：

• 新插入的数据，因为指定了主键值为 2，所以按照聚簇索引结构这条记录会按照顺序插入原来第 2 条记录的位置
• 因为原来被删除的第 2 条记录并没有被真实删除，仍然占有空间，所以这次新插入的数据会复用原有的空间
• 第 2 条记录的 delete_mask的值变为 0
• 第 2 条记录的 next_record的值变为 32
• 第 1 条记录的 next_record指向第 2 条记录，第 2 条记录的next_record指向第 3 条记录
• 最大记录的 n_owned的值从 4 => 5

记录的真实数据

记录的真实数据，除了我们自定义的列的数据以外，还会有三个隐藏列：

实际上这几个列的真实名称是：

• db_row_id
• db_trx_id
• db_roll_ptr

其中 row_id 字段的含义，如果一个表没有手动定义主键，则会选取一个 Unique 键（值唯一的列）作为主键，如果连 Unique 键都没有定义的话，则会为表默认添加一个名为 row_id 的隐藏列作为主键。所以 row_id 是在没有手动定义主键以及不存在 Unique 键的情况下才会存在。

transaction_id 和 roll_pointer 涉及到事务，后面学到再讲解。

举例：创建一张表 mytest：

create table mytest(
  col1 varchar(10),
  col2 varchar(10),
  col3 char(10),
  col4 varchar(10)
)engine=innodb charset=latin1 row_format=compact

插入三条数据：

insert into mytest values
('a', 'bb', 'bb', 'ccc'),
('d', 'ee', 'ee', 'fff'),
('d', NULL, NULL, 'fff');

找到存储表文件 mytest.ibd 的位置，用 notepad++打开，

刚打开可能会乱码，可以安装一个解析插件（自行解决），解析为十进制的数据格式。

格式化之后，二进制文件如下，只需要看真实数据存储的二进制即可：

我们对照下插入的三行记录：

('a', 'bb', 'bb', 'ccc'),
('d', 'ee', 'ee', 'fff'),
('d', NULL, NULL, 'fff');

解析上面的二进制文件，因为 col3 列是定长，不计入变长字段列表，下面解析第一行记录：

• 【变长字段区域】：03 02 01 对照 col3 列 ccc 长度为 03，col2 列 bb 长度为 02，col1 列 a 长度为 01
• 【NULL 值列表区域】：00 代表都是非空的字段，实际上是按照字段的逆序组成的二进制 0 0 0 0 ，转化为十进制就是 00
• 【记录头信息】：00 00 10 00 2c 对照记录头信息（5 个字节），其中 2c对应 next_record ，偏移 2c 个字节到下一条记录的位置
• 【row_id】：00 00 00 2b 68 00 对照隐藏主键（6 字节），当没有手动指定主键，且没有 Unique 建时，InnoDB 会默认创建 row_id
• 【transaction_id】：00 00 00 00 06 05 对照事务id（6 字节）
• 【roll_pointer】：80 00 00 00 32 01 10 对照回滚指针（7 字节）
• 【真实记录】：61 对照第一行记录 col1 的值 a
• 【真实记录】：62 62 对照第一行记录 col2 的值 bb
• 【真实记录】：62 62 20 20 20 20 20 20 20 20 对照第一行记录 col3 的值 bb ，后面的 20 作为一个空值，因为 col3 字段是定长 char(10)10 个字节，而一个字符 b 只占 1 个字节，所以用 8 个 20 填充 8 个空字节位
• 【真实记录】：63 63 63 对照第一行记录 col3 的值 ccc

根据上面的分析我们大致知道了，一行完整数据底层二进制文件的存储格式是怎样的。

第二行记录和第一行内容想通，根据行格式自行推断。

我们重点来看第三行记录是如何存储的？

• 【变长字段列表】：03 01 对照字段 col4 和 col1，col3 和 col2 为 NULL 值不记录
• 【NULL 值列表】：06 对照四个字段是否为 NULL 值的二进制 0 1 1 0，转化为十进制就是 06
• 【记录头信息】：00 00 20 ff 98 对照记录头信息（5 个字节），其中 98 是 next_record
• 【row_id】：00 00 00 2b 68 02 对照 row_id（6 字节）
• 【transaction_id】：00 00 00 00 06 07 对照事务 id（6 字节）
• 【roll_pointer】：80 00 00 00 32 01 10 对照回滚指针（7 字节）
• 【真实记录】：64 对照第三行记录的 col1 字段的值 d
• 【真实记录】：66 66 66 对照第三行记录的 col4 字段的值 fff ，因为 col2 和 col3 都是 NULL值所以没有记录

到这我们就分析完了，应该对底层二进制文件的存储有了一定的认知吧。

Dynamic 和 Compressed 行格式

字段的长度限制

在了解行溢出之前我们要先了解下一个字段的最大长度。

回顾一下，char 和 varchar 的区别

一个 varchar 类型的字段，最大容量为 65535 个字节。

我们创建一张表，验证一下是否真的可以指定为 65535 个字节？

首先我们查看一下 MySQL8.0.26 默认字符集

说明默认字符集采用 utf8mb4。

再查看一下 MySQL5.7.34 默认字符集

说明默认字符集采用 utf8。

这里我们统一采用 8.0.26 版本去实践验证。

首先我们明确一点，不同字符集字符和字节的对等关系：

• utf8 字符集： 1 个字符等于 3 个字节
• utf8mb4 字符集： 1 个字符等于 4 个字节
• ascii 字符集： 1 个字符等于 1个字节

第一步我们采用默认字符集创建一张表 varchar_size_demo，行格式统一采用 Compact。

• utf8mb4 字符集： 1 个字符等于 4 个字节

create table varchar_size_demo  (
  c varchar(65535)
) row_format=COMPACT;

报错提示，字段长度最大不能超过 16383，因为 8.0.26 版本默认字符集为utf8mb4，也就是一个字符等于 4 个字节，但是16383 * 4 = 65532，65532 还差了 3 个字节到 65535，按理论我们应该用 65535 除以 4 等于 16383.75，但是字段长度不能带小数，那我们字舍五入将字段长度改为 16384再试下：

显示还是不能超过 16383，那我们将字段长度改为 16383，再次尝试：

创建成功！！！

思考一下，那 3 个字节跑哪去了？

16383 * 4 = 65532

65535 - 65532 = 3

原因是：每一行记录的头信息中都会默认有 变长字段长度列表（2 字节）和 NULL 值列表 （1 字节），所以每一行记录都会默认空出 3 个字节，用户存储变长字段和 NULL 值的标识。

上述我们采用的是 8.0.26 默认的字符集 utf8mb4，下面我们验证一下指定字符集采用 utf8。

• utf8 字符集： 1 个字符等于 3 个字节

根据上述所知要预留 3 个字节，65535 - 3 = 65532，65532 / 3 = 21844。

也就是说字符集 utf8 字段的最大长度限制为 21844。

那我们假设长度为 21845，创建表 varchar_size_demo1

-- utf8字符集，1个字符等于3个字节
create table varchar_size_demo1 (
  c varchar(21845)
)charset=utf8;

创建报错，显示字段过长。

那我们指定字段长度为 21884再次创建：

-- utf8字符集，1个字符等于3个字节
CREATE TABLE varchar_size_demo1 (
  c VARCHAR(21844)
)CHARSET=utf8;

创建成功，那就说明我们上述的逻辑是对的。

再指定字符集为 ASCII创建表 varchar_size_demo2：

• ascii 字符集： 1 个字符等于 1个字节

预留 3 个自己，那字段长度最大为 65532，如果指定长度为 65533看下效果：

-- ascii字符集，1个字符等于1个字节
create table varchar_size_demo2 (
  c varchar(65533)
)charset=ascii;

创建失败，将字段长度改为 65532再次创建：

-- ascii字符集，1个字符等于1个字节
create table varchar_size_demo2 (
  c varchar(65532)
)charset=ascii;

OK 创建成功，撒花！！！

行溢出

根据上文所说的单个字段的最大长度根据不同的字符集，会有不同的限制，8.0.26 默认采用 utf8mb4字符集

• utf8mb4 字符集： 1 个字符等于 4 个字节

varchar 类型最大为 65535 个字节，预留 3 个字节，一个 varchar 字段最大的容量为 65533 字节，而 InnoDB 的一个数据页的大小为 16KB，16 * 1024 = 16384个字节，一个 varchar 的容量远远大于一个数据页的大小，这样就可能出现一个页存不下一行记录，这种现象成为 行溢出。

在 Compact 和 Redundant 行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据（768 个前缀字节），把剩余的数据分散存储在其他的页中，这叫作 分页存储。

然后记录的真实数据处用 20 个字节存储指向这些分散页的地址（这 20 个字节中还包括存储了分散在各个页中的真实数据占用的字节数），从而可以找打剩余数据所在的页，这称为页的扩展，如下图所示：

Dynamic 和 Compressed 行格式

在 MySQL8.0 中，默认的行格式为 Dynamic，Dynamic 和 Compressed 这两种行格式和 Compact 行格式类似，只不过在处理行溢出数据时方式不同，区别如下：

• Compact 和 Redundant 两种行格式会在记录的真实数据处存储一部分数据（768 个前缀字节）。
• Dynamic 和 Compressed 两种行格式对于存放在 Blob 中的数据采用了完全的行溢出存储方式。如下图所示，如果一行记录数据溢出了，在数据页中只存储 20 个字节的指针地址（存储真实数据的溢出页的地址），实际的数据都存储在 Off Page（溢出页）中。

Compressed 和 Dynamic 是什么区别呢？

Compressed 是在 Dynamic 的基础上优化了一层，存储在其中的行数据会以 zlib 算法进行压缩存储，因此对于 Blob、Text、Varchar 这类大长度类型的数据能够进行非常有效的存储。

Redundant 行格式

Redundant 是 MySQL5.0 版本之前 InnoDB 的行记录存储格式，MySQL 5.0 支持 Redundant 是为了兼容之前版本的页格式。

比如直接修改表的行格式为 Redundant：

alter table record_test_table row_rormat=Redundant;

Redundant 行格式存储格式如下所示：

对比 Compact 行格式主要有两大处不同：

• Compact 是 变长字段长度 列表，Redundant 是 字段长度偏移 列表
• Compact 有 NULL 值列表，Redundant 没有 NULL 值列表

字段长度偏移列表

为什么说 Redundant 行格式会有冗余说法？

因为 Redundant 行格式的字段长度便宜列表会将该行记录中所有列（包括隐藏列）的长度信息都按照逆序存储起来。

偏移 两字，意味着 Redundant 行格式计算列值的长度的方式不想 Compact 行格式那么直观，它是采用两个相邻数值的差值来计算各个列值的长度。

比如第一行记录的字段长度偏移列表（逆序）是：

• 2B 25 1F 1B 13 0C 06

因为它是按照逆序排列的，所以按照顺序排列就是：

• 06 0C 13 1B 1F 25 2B

可以看出有三个隐藏列和四个字段列。

按照两个相邻数值的差值来计算各个字段列值的长度的如下表所示：

记录头信息（record header）

不同于 Compact 行格式，Redundant 行格式中的记录头信息固定占用 6 个字节（48 位），每位的含义如下：

与 Compact 行格式的记录头信息对比来看，有两处不同：

• Redundant 行格式多了 n_field和 1byte_offs_flag这两个属性
• Redundant 行格式没有 record_type这个属性

其中

• n_field代表一行中列的数量，占用 10 位，所以 MySQL5.0 之前的版本最多只能包含 1023 个列。

• 1byte_offs_flags该属性定义了字段长度偏移列表占用 1 个字节，还是 2 个字节。

  • 当值为 1 时，表示占用 1 个字节；
  • 当值为 2 时，表示占用 2 个字节。

小结

到这我们就把 MySQL 的行格式了解的差不多了，当然更底层的知识点我们也用不到，也不会去用它，了解到这个层面其实在工作中也已经足够用了。

本文内容总结借鉴于康师傅的 MySQL 视频课:www.bilibili.com/video/BV1iq…

一起学编程，让生活更随和！如果你觉得是个同道中人，欢迎关注博主公众号：【随和的皮蛋桑】。专注于Java基础、进阶、面试以及计算机基础知识分享🐳。偶尔认知思考、日常水文🐌。

● InnoDB数据存储结构

● Java并发编程系列（一）进程与线程的概念

● Java并发编程系列（二）线程组、线程优先级以及守护线程

● Java并发编程系列（三）线程的六种状态及上下文切换

● Java并发编程系列（四）线程的创建、启动和终止

● Java并发编程系列（五）Java中的内存模型（JMM）详解

● JavaSE系列（一）Java基础

● JavaSE系列（二）Java基础进阶

● HashMap的底层原理和线程安全的替代方案！！！

● Spring之RestTemplate常用API实践