InnoDB页简介 | 我命由我不由天

InnoDB是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想知道表中获取某些记录时，InnoDB存储引擎需要一条一条的把记录从磁盘读出来么？不，那样会慢死，InnoDB采用的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB。一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

InnoDB行格式

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。设计了4种不同类型的行格式：Compact、Redundant、Dynamic、Compressed行格式

指定行格式的语法

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

COMPACT行格式

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

记录的额外信息

这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是变长字段长度列表，NULL值列表和记录头信息

变长字段长度列表

我们知道MySQL支持一些变长的数据类型，比如VARCHR（M）、VARBINARY（M）、各种TEXT类型，我们也可以把拥有这些数据类型的列称为变长字段，变长字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来，存储空间分为两部分

真正的数据内容
占用的字节数

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

InnoDB有它一套规则，我们申明一下W、M和L的意思：

假设某个字符集中表示一个字符最多需要使用的字节数为W，也就是使用SHOW CHARSET语句的结果中的Maxlen列，比方说utf8字符集中的w就是3，gbk字符集中的w就是2，ascii字符集中的w就是1
对于变长类型VARCHAR（M）来说，这种类型表示能存储最多M个字符，所以这个类型能表示的字符串最多占用的字节数就是M*W
假设它实际存储的字符串占用的字节数是L

所以确定使用1个字节还是2个字节表示真正字符串占用的字节数的规则就是这样：

如果M*W<=255，那么使用1个字节来表示真正字符串占用的字节数
如果M*W>255，则分为两种情况
- 如果L <= 127，则用1个字节来表示真正字符串占用的字节数
- 如果L>127，则用2个字节来表示真正字符串占用的字节数

InnoDB在读记录的变长字段长度列表时先查看表结构，如果某个变长字段允许存储的最大字节数大于255时，使用该字节的第一个二进制位作为标志位：如果该字节的第一个位为0，那该字节就是一个单独的字段长度，如果该字节的第一个位为1，那该字节就是半个字段长度。

变长字段长度列只存储值为非NULL的列内容占用的长度，值为NULL的列的长度是不存储的

小贴士：并不是所有记录都有这个变长字段长度列表部分，比方说表中所有的列都不是变长的数据类型的话，这一部分就不需要有

NULL值列表

我们知道表中的某些列可能存储NULL值，如果把这些NULL值都放到记录的真实数据中存储会很占地方，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中

首先统计表中允许存储NULL的列有哪些
如果表中没有允许存储NULL的列，则NULL值列表也就不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列
MySQL规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0

记录头信息

除了变长字段长度列表、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`	表示下一条记录的相对位置

记录的真实数据

MySQL会为每个记录默认的添加一些列

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

小贴士：实际上这几个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR，我们为了美观才写成了row_id、transaction_id和roll_pointer。

这里需要提一下InnoDB表对主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。所以我们从上表中可以看出：InnoDB存储引擎会为每条记录都添加transacetion_id和roll_pinter这两个列，但是row_id是可选的。这些隐藏列的值不用我们操心，InnoDB存储引擎会自己帮我们生成

CHAR（M）列的存储格式

对于CHAR（M）类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。

变长字符集的CHAR（M）类型的列要求至少占用M个字节，而VARCHAR（M）却没有这个要求。比方说对于使用utf8字符集的CHAR（10）的列来说，该列存储的数据字节长度的范围是10-30个字节。一个空字符串也会占10个字节

Redundant行格式

记录的额外信息

字段长度偏移列表

与Compact行格式的开头是变长字段长度列表，而Redundant行格式的开头是字段长度偏移列表。与变长字段长度列表有两出不同
- 没有了变长两个字，意味着Redundant行格式会把该条记录中所有列（包括隐藏列）的长度信息都按照逆序存储到字段长度偏移列表。
- 多了个偏移两个字，这意味着计算列值长度的方式不像Compact行格式那么直观，它是采用两个相邻数值的差值来计算各个列值的长度。

记录头信息

Redundant行格式的记录头信息占用6字节，48个二进制位，这些二进制位代表的意思如下：

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

第一条记录中的头信息是：

00 00 10 0F 00 BC

根据这六个字节可以计算出各个属性的值，如下：

预留位1：0x00
预留位2：0x00
delete_mask: 0x00
min_rec_mask: 0x00
n_owned: 0x00
heap_no: 0x02
n_field: 0x07
1byte_offs_flag: 0x01
next_record:0xBC

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

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

Redundant行格式中NULL值的处理

因为Redundant行格式并没有NULL值列表，所以需要别的方式来存储字段的NULL值，具体策略如下：
- 如果该存储NULL值的字段是变长数据类型的，则在字段长度偏移列表中记录即可，并不占用记录的真实数据部分。
  
  比如record_format_demo表的c4列是VARCHAR(10)类型的，而第二条记录的c4列存储的是NULL值，我们回过头看一下第二条记录的字段长度偏移列表如下：
```
A4 A4 1A 17 13 0C 06
```
  按照列的顺序排放就是：
```
06 0C 13 17 1A A4 A4
```
  可以看到第二条记录的c4列的偏移长度和c3列的相同都是A4，意味着c4列的长度为0，也就意味着存储的是NULL值。
- 如果该存储NULL值的字段是CHAR(M)数据类型的，则将占用记录的真实数据部分，并把该字段对应的数据使用0x00字节填充。
  
  如图第二条记录的c3列的值是NULL，而c3列的类型是CHAR(10)，占用记录的真实数据部分10字节，所以我们看到在Redundant行格式中使用0x00000000000000000000来表示NULL值。

除了以上的几点之外，Redundant行格式和Compact行格式还是大致相同的。

CHAR（M）列的存储格式

我们知道Compact行格式在CHAR(M)类型的列中存储数据的时候还挺麻烦，分变长字符集和定长字符集的情况，而在Redundant行格式中十分干脆，不管该列使用的字符集是啥，只要是使用CHAR(M)类型，占用的真实数据空间就是该字符集表示一个字符最多需要的字节数和M的乘积。比方说使用utf8字符集的CHAM(10)类型的列占用的真实数据空间始终为30个字节，使用gbk字符集的CHAM(10)类型的列占用的真实数据空间始终为20个字节。由此可以看出来，使用Redundant行格式的CHAR(M)类型的列是不会产生碎片的。

行溢出数据

VARCHAR（M）最多能存储的数据

从报错信息里可以看出，MySQL对一条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。所以MySQL服务器建议我们把存储类型改为TEXT或者BLOB的类型。这个65535个字节除了列本身的数据之外，还包括一些其他的数据（storage overhead），比如说我们为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：

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

如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为真实数据的长度可能占用2个字节，NULL值标识需要占用1个字节：

如果VARCHAR(M)类型的列使用的不是ascii字符集，那会怎么样呢？来看一下：

从执行结果中可以看出，如果VARCHAR(M)类型的列使用的不是ascii字符集，那M的最大取值取决于该字符集表示一个字符最多需要的字节数。在列的值允许为NULL的情况下，gbk字符集表示一个字符最多需要2个字符，那在该字符集下，M的最大取值就是32766（也就是：65532/2），也就是说最多能存储32766个字符；utf8字符集表示一个字符最多需要3个字符，那在该字符集下，M的最大取值就是21844，就是说最多能存储21844（也就是：65532/3）个字符。

记录中的数据太多产生的溢出

其中的REPEAT('a', 65532)是一个函数调用，它表示生成一个把字符'a'重复65532次的字符串。前边说过，MySQL中磁盘和内存交互的基本单位是页，也就是说MySQL是以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。而一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65532个字节，这样就可能造成一个页存放不了一条记录的尴尬情况。

在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页

最后需要注意的是，不只是 VARCHAR(M) 类型的列，其他的 TEXT、BLOB 类型的列在存储数据非常多的时候也会发生行溢出。

行溢出的临界点

那发生行溢出的临界点是什么呢？也就是说在列存储多少字节的数据时就会发生行溢出？

MySQL中规定一个页中至少存放两行记录，至于为什么这么规定我们之后再说，现在看一下这个规定造成的影响。以上边的varchar_size_demo表为例，它只有一个列c，我们往这个表中插入两条记录，每条记录最少插入多少字节的数据才会行溢出的现象呢？这得分析一下页中的空间都是如何利用的。

每个页除了存放我们的记录以外，也需要存储一些额外的信息，乱七八糟的额外信息加起来需要136个字节的空间（现在只要知道这个数字就好了），其他的空间都可以被用来存储记录。
每个记录需要的额外信息是27字节。

这27个字节包括下边这些部分：
- 2个字节用于存储真实数据的长度
- 1个字节用于存储列是否是NULL值
- 5个字节大小的头信息
- 6个字节的row_id列
- 6个字节的transaction_id列
- 7个字节的roll_pointer列

假设一个列中存储的数据字节数为n，那么发生行溢出现象时需要满足这个式子：

136 + 2×(27 + n) > 16384

求解这个式子得出的解是：n > 8098。也就是说如果一个列中存储的数据不大于8098个字节，那就不会发生行溢出，否则就会发生行溢出。不过这个8098个字节的结论只是针对只有一个列的varchar_size_demo表来说的，如果表中有多个列，那上边的式子和结论都需要改一改了，所以重点就是：你不用关注这个临界点是什么，只要知道如果我们想一个行中存储了很大的数据时，可能发生行溢出的现象。

Dynamic和Compressed行格式

下边要介绍另外两个行格式，Dynamic和Compressed行格式，我现在使用的MySQL版本是5.7，它的默认行格式就是Dynamic，这俩行格式和Compact行格式挺像，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址，就像这样：

Compressed行格式和Dynamic不同的一点是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。

CHAR(M)中的M值过大的情况

CHAR(M)类型的列可以存储的最大字节长度等于该列使用的字符集表示一个字符需要的最大字节数和M的乘积。如果某个列使用的是CHAR(M)类型，并且它可以存储的最大字节长度超过768字节，那么不论我们使用的是上述4种的哪种行格式，InnoDB都会把该列当成变长字段看待。比方说采用utf8mb4的CHAR(255)类型的列将会被当作变长字段看待，因为4×255 > 768。