InnoDB 行记录格式

行记录格式

目前，InnoDB支持4中行记录格式，分别是 Compact、Redundant、Dynamic和Compressed 行格式。

四种行格式的特性对比如下：

InnoDB 表的默认行格式由参数 innodb_default_row_format 定义，默认值为 DYNAMIC。

mysql> show variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+

我们可以通过如下语法来指定表的行格式：

CREATE TABLE <table_name(column_name)> ROW_FORMAT=行格式名称
    
ALTER TABLE <table_name> ROW_FORMAT=行格式名称

COMPACT 行记录格式

Compact 设计目标是高效地存储数据，一个页中存放的行数据越多，其性能就越高。

下图显示了 Compact 行记录格式的存储方式：

变长字段长度列表

MySQL中有一些变长字段类型，如 VARCHAR(M)、TEXT、BLOB 等，变长字段的长度是不固定的，所以在存储数据的时候要把这些数据占用的字节数也存起来，读取数据的时候才能根据这个长度列表去读取对应长度的数据。

变长字段长度列表就是用来记录一行中所有变长字段的真实数据所占用的字节长度，并且各变长字段数据占用的字节数是按照列的顺序逆序存放。

变长字段长度列表中只存储值为非NULL的列内容占用的长度，值为 NULL 的列的长度是不储存的。如果表中所有的列都不是变长的数据类型的话，就不需要变长字段长度列表了。

若变长字段的长度小于 255字节，就用1字节表示；若大于 255字节，用2字节表示，最大不会不超过2字节，因为MySQL中VARCHAR类型的最大字节长度限制为65535。

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

NULL值列表

表中的某些列可能会存储NULL值，如果把这些NULL值都放到记录的真实数据中会比较浪费空间，所以Compact行格式把这些值为NULL的列存储到NULL值列表中。

如果表中所有列都不允许为 NULL，就不存在NULL值列表了。如果存在允许NULL值的列，则每个列对应一个二进制位，二进制位按照列的顺序逆序排列。

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

另外，NULL值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补0。

记录头信息

记录头信息是由固定的5个字节组成，5个字节也就是40个二进制位，不同的位代表不同的意思，这些头信息会在后面的一些功能中看到。

每个位的含义如下表：

记录真实数据

最后的部分就是实际存储每个列的数据。注意 NULL 不占该部分任何空间，即 NULL 除了占有NULL值列表的标志位，实际存储不占有任何空间。

每行数据除了用户定义的列外，在开头还有两个隐藏列，事务ID列（DB_TRX_ID）和回滚指针列（DB_ROLL_PTR），分别为6字节和7字节的大小。若InnoDB表没有定义主键，每行还会增加一个6字节的行ID列（DB_ROW_ID）。

隐藏主键列

如果我们没有为某个表显式的定义主键，并且表中也没有定义唯一索引，那么InnoDB会自动为表添加一个row_id的隐藏列作为主键。

为这个row_id隐藏列赋值的方式如下：

服务器会在内存中维护一个全局变量，每当向某个包含隐藏的row_id列的表中插入一条记录时，就会把该变量的值当作新记录的row_id列的值，并且把该变量自增1。
每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间的页号为7的页面中一个Max Row ID的属性处。
当系统启动时，会将页中的Max Row ID属性加载到内存中，并将该值加上256之后赋值给全局变量，因为在上次关机时该全局变量的值可能大于页中Max Row ID属性值。

数据存储演示

我们创建下面一张表：其中 username 非空，nickname、address、email 都可为空。

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(60) NOT NULL COMMENT '用户名',
  `nickname` varchar(240) DEFAULT NULL COMMENT '昵称',
  `address` varchar(240) DEFAULT NULL COMMENT '地址',
  `email` varchar(60) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_uk_username` (`username`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;

再插入两条数据，查询结果如下：

第一条数据的行存储格式：

变成字段列表中，按列逆序存储变长字段的长度，address 列的值为 NULL，不存储长度。
NULL值列表中，按列逆序存储可为空的字段，address 列的值为NULL，二进制标志位为1。username 列非NULL就没有标志位。一个字节没用完，高位补0。
数据段中先记录了事务ID和回滚指针两个隐藏列，然后存储了值不为NULL的列，NULL值不占该部分任何空间。

接着看第二条数据的行存储格式：

变成字段列表中，按列逆序存储变长字段的长度，email 列的值为 NULL，不存储长度。
NULL值列表中，按列逆序存储可为空的字段，email 列的值为NULL，二进制标志位为1。
数据段中先记录了事务ID和回滚指针两个隐藏列，然后存储了值不为NULL的列，NULL值不占该部分任何空间。

Redundant行记录格式

Redundant 是 MySQL5.0 版本之前 InnoDB 的行记录存储方式，已经比较老了，现在基本也不再使用这种格式，下面简单了解下就行了。

Redundant 的记录格式大致如下图所示：

字段长度偏移列表

Redundant 行记录格式的首部是一个字段长度偏移列表，同样是按照列的顺序逆序放置的。该条记录中所有列（包括隐藏列、NULL值列）的长度信息都按照逆序存储到字段长度偏移列表。

多了个偏移两个字，也就是列表存储的是每个字段的偏移量，那他就是采用两个相邻数值的差值来计算各个列值的长度。

Redundant 并没有NULL值列表，它是将字段长度偏移列表中的各个列对应的偏移量的第一个比特位作为是否为NULL的依据，该比特位也可以被称之为NULL比特位。也就是说在解析一条记录的某个列时，首先看一下该列对应的偏移量的NULL比特位是不是为1，如果为1，那么该列的值就是NULL，否则不是NULL。

记录头信息

Redundant 行格式的记录头信息占用6字节，48个二进制位。

每个位的含义如下表所示：

与 Compact 格式相比，多了 n_fields、1byte_offs_flag 两个属性，少了 record_type 属性。n_fields 值代表一行中列的数量，占用10位，这也说明了 Redundant 行格式一行最多支持1023列。1byte_offs_flags 值表示偏移列表占用1字节还是2字节。

VARCHAR 数据类型

字符集

在介绍后面的内容前，先了解下字符集，我们在建表时往往都会设置表的字符集。计算机中只能存储二进制数据，字符集就是字符与二进制数据的映射关系。

可以通过 SHOW CHARSET; 命令查看 MySQL 支持的字符集。可以看到这个MySQL版本一共支持41种字符集，其中的Default collation 列表示这种字符集默认的比较规则。最后一列 Maxlen 代表该种字符集表示一个字符最多需要几个字节。

mysql> SHOW CHARSET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.14 sec)

几个常用的字符集如下：例如 latin1 一个字符最大占用 1字节，utf8mb4 一个字符最大占用 4字节。

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
+----------+---------------------------------+---------------------+--------+

单字节 VARCHAR 长度限制

我们经常会用到变成类型 VARCHAR(M)，其中的 M 代表该类型最多存储的字符数量，我们可能还知道 VARCHAR 最大可存放 65535 字节的长度，那实际上是这样吗，下面我们来验证下。

我们创建下面的一张表，指定 C1 列为 VARCHAR(65535)，注意字符集是 latin1，也就是1个字符占用1字节。

mysql> CREATE TABLE `test` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `C1` VARCHAR(65535) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. 
    This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

从创建报错的信息可以了解到，一行数据除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节，否则需要将一些过长的列转为 TEXT 或 BLOBs 类型。

也就是说一行数据除了 TEXT、BLOBs 类型的列，限制最大为 65535字节，注意是一行的总长度，不是一列。

我们预测一下，C1 这个 VARCHAR 最大能设置多大？从 Compact 行格式可以知道，主要有如下几部分的数据：

变长字段长度列表：C1 列超过 255字节，需要 2字节表示长度
NULL值列表：C1 列可为空，所以需要 1字节标识 C1 列的值是否为空
ID列：ID列为 BIGINT 类型，占 8字节

所以 C1 列最多还剩：65535 - 1 - 2 - 8 = 65524。

先设置为 65525 长度试试：可以看到还是报同样的错误。

mysql> CREATE TABLE `test` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `C1` VARCHAR(65525) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. 
    This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

再设置为 65524：创建成功，验证了上面的预测。

mysql> CREATE TABLE `test` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `C1` VARCHAR(65524) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)

如果将 C1 列设置为非空，那 NULL 值列表应该就不存在了，所以 VARCHAR 又可以增加 1字节就是 65525。

下面将 C1 设置为非NULL，长度为 65525：创建成功。

mysql> DROP TABLE test;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `test` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `C1` VARCHAR(65525) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)

多字节 VARCHAR 长度限制

接着将字符集换成 utf8mb4，一个字符最多占用 4字节。这个时候 C1 列 VARCHAR(M) 这个 M 设置多大呢？

要知道 M 指的是字符长度，而不是字节长度，而前面在 latin1 字符集且C1可为空的情况下算出的 65524 表示的既是字符长度又是字节长度。所以这时 C1 的长度实际应该是 M = 65524 / 4 = 16381。

下面验证下，先将长度设置为 16382，注意设置的字符集为 utf8mb4，可以看到创建报了同样的行太大的错误。

mysql> CREATE TABLE `test` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `C1` VARCHAR(16382) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;
1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. 
    This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

接着设置为 16381，创建成功，验证了我们的计算结果。

mysql> CREATE TABLE `test` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `C1` VARCHAR(16381) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)

最后这里总结一下VARCHAR类型：

一行数据，除了 TEXT、BLOB 等大对象类型，总长度最大 65535字节。而且这个 65535 最大长度是包含变长字段长度列表、NULL值列表的。
VARCHAR(M) 中的 M 指的是字符长度，而不是字节长度，计算时，要用总长度除以字符集最大长度，例如 utf8mb4 字符集每个字符的最大长度为 4字节。
VARCHAR 类型如果小于255字节，要在变长字段长度列表占 1字节，否则占 2字节；如果可为NULL，还要在NULL值列表占 1字节，不过这一个字节可以存8个可为NULL的列的状态。所以一个 VARCHAR(M) 的字节长度最大为 65532字节。

CHAR 数据类型

我们一般会认为 CHAR(M) 是定长类型，M 与 VARCHAR(M) 中的 M 是一样的，指的是字符的长度。类型为CHAR(M)时，对于长度不足的值会用空格来补足，就算存的是空值，也会用空格补足，查询的时候会去除首尾的空格，而VARCHAR就不会。

从下面的列表可以看出，存储 CHAR(4) 只需要4字节，VARCHAR(4)则至少需要1字节用于存储长度。而且 CHAR(4) 会用空格补足长度，这样应该就不需要记录这个字段的长度了。

那 CHAR(M) 的长度会存到变长字段长度列表吗？

在我参考的书籍中，有这样的结论：

如果是定长字符类型，例如 latin1，一个字符就是1字节，CHAR(M) 会用空格补足，不需要在变长字段长度列表记录长度。
如果是变长字符类型，例如 utf8mb4，一个字符占用1~4 字节，CHAR(M) 就会占用 M~4M 字节，会被当成变长字符类型，会将实际长度存储到变长字段长列表中。

但我对这个结论有点迷，我们看下面的测试。

下面新增了一个 C2 列，类型为 VARCHAR(1)，原本的 C1 长度减1。可以看到会创建失败，这个可以明确知道原因，因为C2列是变长类型，要在变长字段长度列表占用1字节，所以总长度就就超过了 65535字节。

8(ID字节) + 16380 * 4(C1字节) + 1 * 4(C2字节) + 1(NULL列表) + 2(C1长度) + 1(C2长度) = 65536

CREATE TABLE `test` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `C1` VARCHAR(16380) DEFAULT NULL,
  `C2` VARCHAR(1) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;
1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. 
    This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

如果将C2改为 CHAR(1)，这时就创建成功了。从这里可以看出，是不是可以说明 CHAR(M) 是定长类型，不会在变长字段长度列表占用空间，或者就算占用了也不会计算到总长度列表中？这里先留个疑问。

mysql> CREATE TABLE `test` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `C1` VARCHAR(16380) DEFAULT NULL,
  `C2` CHAR(1) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)

行溢出数据

MySQL中磁盘和内存交互的基本单位是页，一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列最多可以存储65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的溢出页中。

InnoDB 规定一页至少存储两条记录，如果页中只能存放下一条记录，InnoDB存储引擎会自动将行数据存放到溢出页中。在一般情况下，InnoDB 的数据都是存放在 FIL_PAGE_INDEX 类型的数据页中的。但是当发生行溢出时，溢出的数据会存放到 FIL_PAGE_TYPE_BLOB 类型的溢出页中。

当发生行溢出时，数据页只保存了前768字节的前缀数据，接着是20个字节的偏移量，指向行溢出页，大致如下图所示。

COMPRESSED 和 DYNAMIC 行记录格式

Compressed 和 Dynamic 行记录格式与 Compact 行记录格式是类似的，只不过在处理行溢出数据时有些区别。

这两种格式采用完全的行溢出方式，数据页不会存储真实数据的前768字节，只存储20个字节的指针来指向溢出页。而实际的数据都存储在溢出页中，看起来就像下面这样：

Compressed 与 Dynamic 相比，Compressed 存储的行数据会以zlib的算法进行压缩以节省空间，因此对于 BLOB、TEXT、VARCHAR 这类大长度类型的数据能够进行非常有效的存储。

MySQL5.7 默认的行记录格式是 Dynamic。

MySQL系列(3)— InnoDB行记录格式

InnoDB 行记录格式

行记录格式

COMPACT 行记录格式

变长字段长度列表

NULL值列表

记录头信息

记录真实数据

数据存储演示

Redundant行记录格式

字段长度偏移列表

记录头信息

VARCHAR 数据类型

字符集

单字节 VARCHAR 长度限制

多字节 VARCHAR 长度限制

CHAR 数据类型

行溢出数据

COMPRESSED 和 DYNAMIC 行记录格式

相关推荐

取消回复欢迎你发表评论:

Google 黑客常用搜索语句一览原力计划

npx简介（npxvip是哪国的）

在 Android 模拟器上运行 ARM 应用（android模拟器原理）

GB28181,B接口协议之SIPRTSPRTPRTMP协议从入门到精通

安装使用Hoppscotch构建API请求访问与测试

手机实时提取SIM卡打电话的信令和声音-辅助外设与商用通话方案

Python自动化办公——后台截图（python 自动截图）

轻松转换!AppleNumbers到Excel的快捷教程

电脑端腾讯文档如何导出excel

用OpenCV测量图像中物体的大小（基于opencv的物体尺寸检测算法实现）

MySQL系列(3)— InnoDB行记录格式

InnoDB 行记录格式

行记录格式

COMPACT 行记录格式

变长字段长度列表

NULL值列表

记录头信息

记录真实数据

数据存储演示

Redundant行记录格式

字段长度偏移列表

记录头信息

VARCHAR 数据类型

字符集

单字节 VARCHAR 长度限制

多字节 VARCHAR 长度限制

CHAR 数据类型

行溢出数据

COMPRESSED 和 DYNAMIC 行记录格式

相关推荐

取消回复欢迎 你 发表评论:

Google 黑客常用搜索语句一览 原力计划

npx简介（npxvip是哪国的）

在 Android 模拟器上运行 ARM 应用（android模拟器原理）

GB28181,B接口协议之SIPRTSPRTPRTMP协议从入门到精通

安装使用Hoppscotch构建API请求访问与测试

手机实时提取SIM卡打电话的信令和声音-辅助外设与商用通话方案

Python自动化办公——后台截图（python 自动截图）

轻松转换!AppleNumbers到Excel的快捷教程

电脑端腾讯文档如何导出excel

用OpenCV测量图像中物体的大小（基于opencv的物体尺寸检测算法实现）

取消回复欢迎你发表评论:

Google 黑客常用搜索语句一览原力计划