Mysql存储引擎---InnoDB的行格式及行溢出问题

一、局部性原理

在InnoDB中，数据会存储到磁盘上，在真正处理数据时需要先将数据加载到内存。
表中读取某些记录时，InnoDB存储引擎不需要一条一条的把记录从磁盘上读出来，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB，也就是说，当需要从磁盘中读数据时每一次最少将从磁盘中读取16KB的内容到内存中，每一次最少也会把内存中的16KB内容写到磁盘中。

页是 MySQL 中磁盘和内存交互的基本单位，也是 MySQL 是管理存储空间的基本单位。

二、InnoDB行格式

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。

一行记录可以以不同的格式存在InnoDB中，行格式分别是Compact、Redundant、Dynamic和Compressed行格式。

我们可以在创建或修改表的语句中指定行格式：

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

默认格式为Dynamic格式

（一）Compact格式

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

一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分。

记录的额外信息：这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息

1. 变长字段长度列表
MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。

变长字段占用的存储空间分为两部分：

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

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

例如：

因为这些长度值需要按照列的逆序存放，所以最后变长字段长度列表的字节串用十六进制表示的效果如图：

注意：

1. 如果该可变字段允许存储的最大字节数超过255字节并且真实存储的字节数超过127字节，则使用2个字节，否则使用1个字节。
2. 变长字段长度列表中只存储值为非NULL的列内容占用的长度，值为 NULL 的列的长度是不储存的。
3. 并不是所有记录都有这个变长字段长度列表部分，比方说表中所有的列都不是变长的数据类型的话，这一部分就不需要有。
4. 对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。（如： ascii 字符集是一个定长字符集）

2. NUll值列表
我们知道表中的某些列可能存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中存储会很占地方，所以 Compact 行格式把这些值为 NULL 的列统一管理起来，存储到 NULL 值列表中，如果表中没有允许存储NULL 的列，则 NULL值列表也不存在了。

它的处理过程是这样的：

1. 首先统计表中允许存储 NULL 的列有哪些。
   主键列、被 NOT NULL 修饰的列都是不可以存储 NULL 值的，所以在统计的时候不会把这些列算进去。
2. 如果表中没有允许存储 NULL 的列，则NULL值列表也不存在了，否则将每个允许存储 NULL 的列对应一个二进制位，二进制位按照列的顺序逆序排列，二进制位表示的意义如下：
   • 二进制位的值为 1 时，代表该列的值为 NULL 。
   • 二进制位的值为 0 时，代表该列的值不为 NULL 。

MySQL规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补 0 。

3. 记录头信息
除了变长字段长度列表、NULL值列表之外，还有一个用于描述记录的记录头信息，它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思。

名称	大小（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	表示下一条记录的相对位置

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

列名	是否必须	是否必须	描述
row_id	否	6字节	行ID，唯一标识一条记录
transaction_id	是	6字节	事务ID
roll_pointer	是	7字节	回滚指针

• 实际上这几个列的真正名称其实是，我们为了美观才写成了row_id、transaction_id和roll_pointer：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。
• 一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。

*行溢出数据问题

对于 VARCHAR(M) 类型的列最多可以占用 65535 个字节。

CREATE TABLE varchar_size_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 change some columns to TEXT or BLOBs
<--报错信息表达的意思是：MySQL对一条记录占用的最大存储空间是有限制的，除BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。-->

从报错信息里可以看出， MySQL 对一条记录占用的最大存储空间是有限制的，除了 BLOB 或者 TEXT 类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。所以 MySQL 服务器建议我们把存储类型改为 TEXT 或者 BLOB 的类型。

问：为什么上例刚好65535个字节却超出范围了呢？
答：因为这个65535个字节除了列本身的数据之外，还包括一些其他的数据，比如说我们为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：真实数据、变长字段真实数据的长度、NULL值标识。

一行中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节

然而该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为变长字段的长度占用2个字节，NULL值标识需要占用1个字节。（MySQL中定义的65535长度是指所有varchar列的总和）。如果 VARCHAR 类型的列有 NOT NULL 属性，那最多只能存储 65533 个字节的数据。

行溢出：一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65535个字节，这样就可能出现一个页存放不了一条记录。就是说一行数据存储的内容太多了，一个数据页都放不下了，此时只能溢出这个数据页，把数据溢出存放到其他数据页里去，那些数据页就叫做溢出页。

TEXT、BLOB 类型的列在存储数据非常多的时候也会发生 行溢出

MySQL 中规定一个页中至少存放两行记录

第一个数据页中不仅存放了数据还存放了下一页（溢出页）的地址。

InnoDB可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为BLOB、LOB这类的大对象列类型的存储会把数据存放在数据页面之外。

（二）Dynamic和Compressed行格式

这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储一部分数据，而是把所有的数据都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。另外，Compressed行格式会采用压缩算法对页面进行压缩。

Compressed、Dynamic格式对于存放在BLOB中的数据采用了完全的行溢出方式。数据页中只存放20个字节的指针（储存其他页面的地址），实际的数据都存放在其他页面中。