InnoDB 行格式

参考资料

https://relph1119.github.io/mysql-learning-notes/#/mysql/

疑问

常常有如下疑问：

• 往MySQL中新增的一行数据是怎么存储的？

• 行溢出是什么？

• 为什么说varchar字段最大可存储空间为65535字节？正确吗？

行格式

InnoDB中提供了四种行格式，Compact、Redundant、Dynamic和Compressed，最新版本的MySQL5.7中默认使用的行格式为Dynamic。

Dynamic格式

InnoDB中一行记录由 记录的额外信息 和 记录的真实数据 两大部分组成

记录的额外信息

变长字段长度列表

MySQL 支持一些变长的数据类型，比如 VARCHAR(M) 、VARBINARY(M) 、各种 TEXT 类型，各种 BLOB 类 型。同时还有一些变长的字符集，utf8存储一个字符时的字节长度在[1,3]之间，搭配定长类型时，如char(5) utf8，占用存储大小在[5,15]之间变化。变长类型和变长字符集存储多少字节的数据是不固定的，所以InnoDB在存储真实数据的时候把这些数据占用的字节数也存储了起来。

这些存储空间不定的字段，存储时占用的空间分为两部分：

1. 真正的数据内容
2. 占用的字节数

在 Dynamic行格式中，所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放。真实数据占用字节小于等于127时，使用一个字节存储长度，大于127时，使用两个字节存储。

NULL值列表

我们在创建表时，可以指定列是否可为NULL，如char(10) NULL。如果在值为NULL时将其作为真实数据存储，则会造成空间浪费，所以Dynamic行格式对可为NULL的列进行统一管理，存储在NULL值列表中，避免浪费空间。它的处理过程是这样的：

1. 首先统计表中允许存储 NULL 的列有哪些，除了主键列和被NOT NULL 修饰的列，其它都可以存储 NULL值
2. 将每个允许存储 NULL 的列对应一个二进制位，二进制位按照列的顺序逆序排列，二进制位表示的意义如下： 二进制位的值为 1 时，代表该列的值为 NULL 。 二进制位的值为 0 时，代表该列的值不为 NULL 。

例如，一行记录中， c1 、 c3 、 c4 这3个列的值都不为 NULL ，那它们对应的二进制位都是 0，如下图所示

一个字节有八个二进制位，可以对应八个列，当列数量增加，对应NULL列表所使用的字节数也相应增加。

记录头信息

用于描述记录，它是由固定的 5 个字节组成。 5 个字节也就是 40 个二进制位，不同的位代表不同的意思，

记录头字段信息

记录的真实数据

隐藏字段

存储真实数据时，除了在表中定义的列的数据 以外，MySQL 会为每行记录默认的添加一些列（也称为隐藏列），具体如下

其中DB_ROW_ID，是在创建表没有定义主键、也没有定义唯一索引时，自动生成隐式主键ID。 InnoDB 表对主键的生成策略：优先使用自定义主键，如果没有定义主键，则选取一个Unique键作为主键，如果没有Unique键定义，则为表添加一个隐藏的DB_ROW_ID，当做自增主键。

行溢出

InnoDB中，一个数据页的大小是16KB，而一行记录的大小限制是64KB，在页中空间不足于存储行中数据时，会将多余的数据存储在其他数据页。这个过程叫做行溢出，存储溢出数据的页面称为溢出页。

例如，现在有个varchar字段值需要的存储空间是32KB，在Dynamic行格式下，会将溢出的字段值全部储存在溢出页，然后在行中存储溢出页的地址。

Compact和Redundant行格式则会在行中保留该字段值的前768字节大小的数据，然后将其余数据存储在溢出页，再记录该溢出页地址。

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

ALTER TABLE 表名 ROW_FORMAT=行格式名称

解答疑问

为什么说varchar字段最大可存储空间为65535字节？

创建一个表test_demo，并定义VARCHAR字段，长度是65535，使用ascii字符集，执行结果如下

mysql> CREATE TABLE test_demo(  
-> c VARCHAR(65535)  
-> ) CHARSET=ascii ROW_FORMAT=Compact; 

ERROR 1118 (42000): 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 c hange some columns to TEXT or BLOBs

为什么创建失败？

因为MySQL 对一行记录占用的最大存储空间是有限制的，除了 BLOB 或者 TEXT 类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。

这个 65535 个字节除了列本身的数据之外，还包括其关联数据（ storage overhead ），比如说存储一个 VARCHAR(M) 类型的列，其实需要占用3部分存储空间：

• 真实数据
• 真实数据占用字节的长度
• NULL 值标识，如果该列有 NOT NULL 属性则可以没有这部分存储空间

综上，varchar字段最大可存储空间为65535字节是不正确的。