MySQL 深入理解InnoDB行格式

MySQL 行格式

Innodb支持四种行格式：

• Compact
• Redundant
• Dynamic
• Compressed

行格式介绍及语法

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

 

• 行格式的应用都是针对于表的，只有在创建表的时候才能使用row_format，在创建库的时候无法指定行格式。
• 表的行格式也是决定了这张表下面的物理存储方式，会影响查询和DML操作的性能。
• 随着更多的行适合单个的磁盘页面，查询和索引查找可以更快的工作，因为更多的行在同一个页面上，所需要写或者读的IO会需要的更少，所以能更大程度的提升性能。
• 每个表的数据一般的话会被分成几个页，每个表里面的页都会组成相应的B树索引，根据索引去排列数据的顺序。
• 可变长度的列值，因为其变长的特性，可能会无法容纳在B树页面上，而是会单独分配到磁盘页面上，这些磁盘页面的话称为溢出页面。这些列的话是称为页外列。这些列的话会存储在溢出页面的单链表中，然而这样的列如果过多的话，是会有一个或者多个这种溢出页面的列表。
• 根据列的长度，所有的列值或可变长度的列值的前缀都会存储在B树中，用来避免浪费存储空间而去读取单独的页面。这样的话会降低磁盘使用率，还增加了IO的负载。
• 一般的变长字段列类型有：varchar、varbinary、blob、text类型

行格式类型

Redundant行格式

Redundant 行格式会把该条记录中所有列 (包括隐藏列) 的长度信息都按照逆序存储到 '字段长度偏移列表' 中
储存的值为两个相邻数值的差值
所有数据逆序存放

记录头信息

名称	大小 (bit)	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在页面堆的位置信息
n_field	10	表示记录中列的数量
1byte_offs_flag	1	标记字段长度偏移列表中每个列对应的偏移量是使用1字节还是2字节表示的
next_record	16	表示下一条记录的相对位置

记录数据的特点

• 存储变长列的前768 Bytes在索引记录中，剩余的存储在overflow page中，对于固定长度且超过768 Bytes会被当做变长字段存储在off-page中。
• 索引页中的每条记录包含一个6 Bytes的头部，用于链接记录用于行锁。
• 聚簇索引的记录包含用户定义的所有列。另外还有一个6字节的事务ID（DB_TRX_ID）和一个7字节长度的回滚段指针(Roll pointer)列。
• 如果创建表没有显示指定主键，每个聚簇索引行还包括一个6字节的行ID（row ID）字段。
• 每个二级索引记录包含了所有定义的主键索引列。
• 一条记录包含一个指针来指向这条记录的每个列，如果一条记录的列的总长度小于128字节，这个指针占用1个字节，否则2个字节。这个指针数组称为记录目录（record directory）。指针指向的区域是这条记录的数据部分。
• 固定长度的字符字段比如CHAR(10)通过固定长度的格式存储，尾部填充空格。
• 固定长度字段长度大于或者等于768字节将被编码成变长的字段，存储在off-page中。
• 一个SQL的NULL值存储一个字节或者两个字节在记录目录（record dirictoty）。对于变长字段null值在数据区域占0个字节。对于固定长度的字段，依然存储固定长度在数据部分，为null值保留固定长度空间允许列从null值更新为非空值而不会引起索引的分裂。
• 对varchar类型，Redundant行记录格式同样不占用任何存储空间，而CHAR类型的NULL值需要占用空间。

其中变长类型是通过长度 + 数据的方式存储，不同类型长度是从1到4个字节（L+1 到 L + 4），对于TEXT类型的值需要L Bytes存储value，同时需要2个字节存储value的长度。同时Innodb最大行长度规定为65535 Bytes，对于Text类型，只保存9到12字节的指针，数据单独存在overflow page中。

Compact行格式

Compact 中记录的额外信息包含三类：变长字段长度列表、NULL 值列表、记录头信息

变长字段长度列表

MySQL 支持一些变长的数据类型 (VARCHAR)，这些数据在存储时不仅要存储数据内容，还需要将占用的字节数存储起来
定长数据类型 (CHAR) 在以一些变长字符集储存数据时，由于储存长度为变长，所以也需要在该列表中存储其长度
在记录的开头部位就是各个变长字段占用的字节数，这些数据逆序存放。

Compact首部是一个非NULL变长字段长度的列表，并且是按列的顺序逆序放置的，若列的长度小于255字节，用1字节表示；若大于255个字节，用2字节表示。变长字段最大不可以超过2字节，这是因为MySQL数据库中varchar类型最大长度限制为65535。变长字段之后的第二个部分是NULL标志位（以下介绍），表示该行数据是否有NULL值。有则用1表示，该部分所占的字节应该为1字节。

所以在创建表的时候，尽量使用NOT NULL DEFAULT ''，如果表中列存储大量的NULL值，一方面占用空间，另一个方面影响索引列的稳定性。

如果表中没有变长字段，就没有变长字段长度列表

NULL 值列表

NULL 值列表存储有表中所有 NULL 值，可以节约许多空间
值为 1 时，代表 NULL；值为 0 时，代表非 NULL。当位数不够整数个字节的时候，在高位补 0
所有数据逆序存放
如果表中没有允许储存 NULL 值的列，就没有 NULL 值列表

记录头信息

名称	大小 (bit)	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在记录堆的位置信息
record_type	3	表示当前记录的类型，0 表示普通记录，1 表示B+树非叶子节点记录，2 表示最小记录，3 表示最大记录
next_record	16	表示下一条记录的相对位置

隐藏列

除了一些自定义列的数据外，MySQL 还会为每个记录默认添加一些隐藏列：

列名	是否必须	占用空间 (bit)	描述
DB_ROW_ID	否	6	行ID，唯一标识一条记录
DB_TRX_ID	是	6	事务ID
DB_ROLL_PTR	是	7	回滚指针

只有当用户没有指定主键，且表中没有 Unique 键时才会添加 DB_ROW_ID 作为主键

记录数据的特点

• 索引的每条记录包含一个5个字节的头部，头部前面可以有一个可变长度的头部。这个头部用来将相关连的记录链接在一起，也用于行锁。
• 记录头部的变长部分包含了一个表示null 值的位向量(bit vector)。如果索引中可以为null的字段数量为N，这个位向量包含 N/8 向上取整的字节数。比例如果有9-16个字段可以为NULL值，这个位向量使用两个字节。为NULL的列不占用空间，只占用这个位向量中的位。头部的变长部分还包含了变长字段的长度。每个长度占用一个或者2个字节，这取决了字段的最大长度。如果所有列都可以为null 并且制定了固定长度，记录头部就没有变长部分。
• 对每个不为NULL的变长字段，记录头包含了一个字节或者两个字节的字段长度。只有当字段存储在外部的溢出区域或者字段最大长度超过255字节并且实际长度超过127个字节的时候会使用2个字节的记录头部。对应外部存储的字段，两个字节的长度指明内部存储部分的长度加上指向外部存储部分的20个字节的指针。内部部分是768字节，因此这个长度值为 768+20， 20个字节的指针存储了这个字段的真实长度。
• NULL不占该部分任何空间，即NULL除了占用NULL标志位，实际存储不占任何空间。
• 记录头部跟着非空字段的数据部分。
• 聚簇索引的记录包含了所以用户定于的字段。另外还有一个6字节的事务ID列和一个7字节的回滚段指针。
• 如果没有定于主键索引，则聚簇索引还包括一个6字节的Row ID列。
• 每个辅助索引记录包含为群集索引键定义的不在辅助索引中的所有主键列。如果任何一个主键列是可变长度的，那么每个辅助索引的记录头都有一个可变长度的部分来记录它们的长度，即使辅助索引是在固定长度的列上定义的。
• 固定长度的字符字段比如CHAR(10)通过固定长度的格式存储，尾部填充空格。
• 对于变长的字符集，比如uft8mb3和utf8mb4， InnoDB试图用N字节来存储 CHAR(N)。如果CHAR(N)列的值的长度超过N字节，列后面的空格减少到最小值。CHAR(N)列值的最大长度是最大字符编码数 x N。比如utf8mb4字符集的最长编码为4，则列的最长字节数是 4*N。

Dynamic行格式

MySQL 8.0 中默认的行格式为 Dymatic

由于Dynamic是Compact变异而来，结构大同而已（不同的是Dynamic不会在记录真实数据处储存真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中，并且只储存其他页面的地址），现在默认都是Dynamic格式。

Dynamic有着更大的索引键前缀的存储能力(maximum byte length)，支持索引键前缀3072字节。

Compressed行格式

该行格式与DYNAMIC行格式有相同的存储特性和功能，COMPRESSED主要是对表和索引数据进行压缩，一般适用于使用率低的归档，备份类的需求。如果要使用该行格式的话需要启用innodb_file_per_table。