MySQL原理 - InnoDB引擎 - 行记录存储 - Redundant行格式

r囧r小猫 2023-02-24 15:22 44阅读 0赞

> 本文基于 MySQL 8

在[上一篇：MySQL原理 - InnoDB引擎 - 行记录存储 - Compact格式][MySQL_ - InnoDB_ - _ - Compact] 中，我们介绍了什么是 InnoDB 行记录存储以及 Compact 行格式，在这一篇中，我们继续介绍其他三种行格式。

## Redundant 行格式 ##

这个是最古老的，最简单粗暴的行格式了，现在基本上已经不用了，因为占用空间最多，从而导致内存碎片化最严重，是最低效的行格式了（针对现在varchar字段使用的更多，而对于 varchar 字段改变长度的更新大部分情况下就是将原有行的数据标记为已删除，然后在其他空间足够的地方新建记录，Redundant 顾名思义，占用空间更多，所以碎片化，空间浪费会更严重）。

MySQL官网的 `Internal Mannual` 给出的行格式示例，其实就是 Redundant 格式的： [InnoDB Record High-Altitude Picture][]

创建一个和上一篇中的示例一样的表，插入相同的数据：

CREATE TABLE `record_test_2` (
      `id` bigint(20) DEFAULT NULL,
      `score` double DEFAULT NULL,
      `name` char(4) DEFAULT NULL,
      `content` varchar(8) DEFAULT NULL,
      `extra` varchar(16) DEFAULT NULL,
      `large_content` varchar(1024) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT

INSERT INTO `record_test_2`(`id`, `score`, `name`, `content`, `extra`, `large_content`) VALUES (1, 78.5, 'hash', 'wodetian', 'nidetiantadetian', 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz');
    INSERT INTO `record_test_2`(`id`, `score`, `name`, `content`, `extra`, `large_content`) VALUES (65536, 17983.9812, 'zhx', 'shin', 'nosuke', 'lex');
    INSERT INTO `record_test_2`(`id`, `score`, `name`, `content`, `extra`, `large_content`) VALUES (NULL, -669.996, 'aa', NULL, NULL, NULL);
    INSERT INTO `record_test_2`(`id`, `score`, `name`, `content`, `extra`, `large_content`) VALUES (2048, NULL, NULL, 'c', 'jun', '');
    INSERT INTO `record_test_2`(`id`, `score`, `name`, `content`, `extra`, `large_content`) VALUES (-1, 26.75, 'xxxx', 'aaaa', 'bbbb', 'cccc');

我们来直接看底层存储的数据是什么样子的：

![image][]

所有字段长度列表：00 c1 00 3f 00 2f 00 27 00 23 00 1b 00 13 00 0c 00 06 
    记录头信息：00 00 10 12 01 65 
    隐藏列DB_ROW_ID：00 00 00 00 09 00 
    隐藏列DB_TRX_ID：00 00 00 03 cb 08 
    隐藏列DB_ROLL_PTR：a8 00 00 01 1c 01 10 
    列数据id(1)：80 00 00 00 00 00 00 01 
    列数据score(78.5)：00 00 00 00 00 a0 53 40 
    列数据name(hash)：68 61 73 68 
    列数据content(wodetian)：77 6f 64 65 74 69 61 6e 
    列数据extra(nidetiantadetian)：6e 69 64 65 74 69 61 6e 74 61 64 65 74 69 61 6e 
    列数据large_content(abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz)：61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f 70 71 72 73 74 75 76 77 78 79 7a 61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f 70 71 72 73 74 75 76 77 78 79 7a 61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f 70 71 72 73 74 75 76 77 78 79 7a 61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f 70 71 72 73 74 75 76 77 78 79 7a 61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f 70 71 72 73 74 75 76 77 78 79 7a 
    
    所有字段长度列表：34 31 2b 27 23 1b 13 0c 06 
    记录头信息：00 00 18 13 01 a8 
    隐藏列DB_ROW_ID：00 00 00 00 09 01 
    隐藏列DB_TRX_ID：00 00 00 03 cb 09 
    隐藏列DB_ROLL_PTR：a9 00 00 02 01 01 10 
    列数据id(65536)：80 00 00 00 00 01 00 00 
    列数据score(17983.9812)：b5 15 fb cb fe 8f d1 40 
    列数据name(zhx)：7a 68 78 20 
    列数据content(shin)：73 68 69 6e 
    列数据extra(nosuke)：6e 6f 73 75 6b 65 
    列数据large_content(lex)：6c 65 78 
    
    所有字段长度列表：a7 a7 a7 27 23 9b 13 0c 06 
    记录头信息：00 00 00 13 01 de 
    隐藏列DB_ROW_ID：00 00 00 00 09 02 
    隐藏列DB_TRX_ID：00 00 00 03 cb 0e 
    隐藏列DB_ROLL_PTR：ac 00 00 01 00 01 10 
    列数据id(null)：00 00 00 00 00 00 00 00 
    列数据score(-669.996)：87 16 d9 ce f7 ef 84 c0 
    列数据name(aa)：61 61 20 20 
    
    所有字段长度列表：ab 2b 28 a7 a3 1b 13 0c 06 
    记录头信息：00 00 28 13 02 18 
    隐藏列DB_ROW_ID：00 00 00 00 09 03 
    隐藏列DB_TRX_ID：00 00 00 03 cb 0f 
    隐藏列DB_ROLL_PTR：ad 00 00 01 21 01 10 
    列数据id(2048)：80 00 00 00 00 00 08 00 
    列数据score(null)：00 00 00 00 00 00 00 00 
    列数据name(null)：00 00 00 00 
    列数据content(c)：63 
    列数据extra(jun)：6a 75 6e 
    
    所有字段长度列表：33 2f 2b 27 23 1b 13 0c 06 
    记录头信息：00 00 30 13 00 74 
    隐藏列DB_ROW_ID：00 00 00 00 09 04 
    隐藏列DB_TRX_ID：00 00 00 03 cb 10 
    隐藏列DB_ROLL_PTR：ae 00 00 01 22 01 10
    列数据id(-1)：7f ff ff ff ff ff ff ff 
    列数据score(26.75)：00 00 00 00 00 c0 3a 40 
    列数据name(xxxx)：78 78 78 78 
    列数据content(aaaa)：61 61 61 61 
    列数据extra(bbbb)：62 62 62 62 
    列数据large_content(cccc)：63 63 63 63

### Redundant - 所有字段长度列表 ###

不同于 Compact 行格式，Redundant 的开头是所有字段长度列表，而不是变长字段列表 + NULL 值列表。这个字段长度列表的格式是：

*  记录所有字段的长度偏移，包括隐藏列。偏移就是，第一个字段长度为 a，第二个字段长度为 b，那么列表中第一个字段就是 a，第二个字段就是 a + b。
 *  所有字段倒序排列

对于长度存储，是一字节还是两字节，以及存储的内容，Redundant 的规则比较特殊：

*  根据整行记录的长度决定，到底每个字段用一个字节还是两个字节，**每个字段用一个字节还是两个字节，在记录头信息里面有标记**：
    
     *  如果整行长度小于 128，则用一字节存储
     *  如果大于等于128，则每个字段用两个字节
 *  对于一字节存储，**最高位标记字段是否为 NULL**，如果为 NULL，则最高位为1，否则为0. 剩下的 7 位用来存储长度，所以最多是 127
 *  对于两字节存储，**最高位还是标记字段是否为NULL**。**第二位标记这条记录是否在同一页**，如果在则为0，如果不在则为1，这其实就涉及到了后面要说的**溢出页**。剩下的 14 位表示长度，所以最多是 16383

来推算一下第一行的所有字段长度列表：

由于第一行实际存储的长度超过了128，所以需要两字节。第一列到最后一列的长度，分别是：隐藏列DB\_ROW\_ID-6字节，隐藏列DB\_TRX\_ID-6字节，隐藏列DB\_ROLL\_PTR-7字节，列数据id-int-固定8字节，列数据score-double-固定8字节，列数据name-char-固定4字节，列数据content-varchar-变长8字节，列数据extra-varchar-变长14字节，large\_content-变长130字节。转换成偏移后为：0x06，0x0c，0x13，0x1b，0x23，0x27，0x2f，0x3f，0xc1。变成两字节，倒序过来就是：`00 c1 00 3f 00 2f 00 27 00 23 00 1b 00 13 00 0c 00 06`。

对于第三行，包含了 NULL 列，记录长度小于 128，用一字节存储。。第一列到最后一列的长度，分别是：隐藏列DB\_ROW\_ID-6字节，隐藏列DB\_TRX\_ID-6字节，隐藏列DB\_ROLL\_PTR-7字节，列数据id-int-固定8字节，列数据score-double-固定8字节，列数据name-char-固定4字节，列数据content-varchar-变长0字节，列数据extra-varchar-变长0字节，large\_content-变长0字节。转换成偏移后为：0x06，0x0c，0x13，0x1b，0x23，0x27，0x27，0x27，0x27。由于第一列和最后三列为 NULL，所以将 0x1b，最后三个 0x27，0x27，0x27 的最高位设置为1，变成 0x9b，0xa7，0xa7，0xa7.倒序过来就是：`a7 a7 a7 27 23 9b 13 0c 06`

### Redundant - 记录头信息 ###

Redundant 行格式的记录头（48位）信息比 Compact 的（40位）多了：

<table> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>大小（bits）</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>无用位</td> 
   <td>2</td> 
   <td>目前没用到</td> 
  </tr> 
  <tr> 
   <td>deleted_flag</td> 
   <td>1</td> 
   <td>记录是否被删除</td> 
  </tr> 
  <tr> 
   <td>min_rec_flag</td> 
   <td>1</td> 
   <td>B+树中非叶子节点最小记录标记</td> 
  </tr> 
  <tr> 
   <td>n_owned</td> 
   <td>4</td> 
   <td>该记录对应槽所拥有记录数量</td> 
  </tr> 
  <tr> 
   <td>heap_no</td> 
   <td>13</td> 
   <td>该记录在堆中的序号，也可以理解为在堆中的位置信息</td> 
  </tr> 
  <tr> 
   <td>n_field</td> 
   <td>10</td> 
   <td>该记录的列数量，范围从1到1023</td> 
  </tr> 
  <tr> 
   <td>1byte_offs_flag</td> 
   <td>1</td> 
   <td>1代表每个字段长度为1字节，0代表2字节</td> 
  </tr> 
  <tr> 
   <td>next_record pointer</td> 
   <td>16</td> 
   <td>页中下一条记录的相对位置</td> 
  </tr> 
 </tbody> 
</table>

Redundant 行格式的记录头与 Compact 行格式的记录头的区别就是少了`record_type`位，多了`n_field`和`1byte_offs_flag`这两个。

`n_field`用来表示该记录的列数量，范围从1到1023。这里的每一行都是 9 列，所以`n_field`都是9，也就是`0000001001`。`1byte_offs_flag`用来表示字段长度列表每一列占用的字节数，1代表每个字段长度为1字节，0代表2字节。这里只有第一行为两字节，所以第一行的这一位为0

第一行记录头信息：00 00 10 12 01 65 
    转换为2进制：00000000 00000000 00010000 00010010 00000001 01100101
    n_field：000 0001001
    1byte_offs_flag：0
    
    第二行记录头信息：00 00 18 13 01 a8 
    转换为2进制：00000000 00000000 00011000 00010011 00000001 10101000
    n_field：000 0001001
    1byte_offs_flag：1
    
    第三行记录头信息：00 00 00 13 01 de
    转换为2进制：00000000 00000000 00011000 00010011 00000001 11011110
    n_field：000 0001001
    1byte_offs_flag：1
    
    第四行记录头信息：00 00 28 13 02 18 
    转换为2进制：00000000 00000000 00101000 00010011 00000010 00011000
    n_field：000 0001001
    1byte_offs_flag：1
    
    第四行记录头信息：00 00 30 13 00 74 
    转换为2进制：00000000 00000000 00110000 00010011 00000000 01110100
    n_field：000 0001001
    1byte_offs_flag：1

### Redundant - 具体列记录存储与 Compact 区别 ###

#### 1. 对 NULL 值的处理 ####

对于 NULL，不像 Compact 那样有 NULL 值列表，仅在字段长度列表的每个字段长度最高位标记 1 表示这个字段为 NULL。

同时对于定长字段，还会占用相同长度的字节空间，每个字节都填充上 00，例如第三，四行：

所有字段长度列表：a7 a7 a7 27 23 9b 13 0c 06 
    记录头信息：00 00 00 13 01 de 
    隐藏列DB_ROW_ID：00 00 00 00 09 02 
    隐藏列DB_TRX_ID：00 00 00 03 cb 0e 
    隐藏列DB_ROLL_PTR：ac 00 00 01 00 01 10 
    列数据id(null)：00 00 00 00 00 00 00 00 
    列数据score(-669.996)：87 16 d9 ce f7 ef 84 c0 
    列数据name(aa)：61 61 20 20 
    
    所有字段长度列表：ab 2b 28 a7 a3 1b 13 0c 06 
    记录头信息：00 00 28 13 02 18 
    隐藏列DB_ROW_ID：00 00 00 00 09 03 
    隐藏列DB_TRX_ID：00 00 00 03 cb 0f 
    隐藏列DB_ROLL_PTR：ad 00 00 01 21 01 10 
    列数据id(2048)：80 00 00 00 00 00 08 00 
    列数据score(null)：00 00 00 00 00 00 00 00 
    列数据name(null)：00 00 00 00 
    列数据content(c)：63 
    列数据extra(jun)：6a 75 6e

bigint 为空时，填充了8个字节的 0x00。double 为空时，填充了8个字节的 0x00。char(4) 为空时，填充了4个字节的 0x00. 这样，对于这些**定长字段**的修改，无论是从 NULL 改成非 NULL 还是从非 NULL 改成 NULL，或者更新为不同长度（但是在原始限制内），都**不用将原有记录标记为删除，之后再寻找新的空间重建更新后的记录了，直接在原有记录上面修改**。对于 Compact，从 NULL 改成非 NULL 还是从非 NULL 改成 NULL，是需要这种麻烦的更新方式的，因为 NULL 不占用空间。

对于**可变长度字段**，Redundant 和 Compact 是相同的，为 NULL 不占用空间。**只要改变长度，就会将原有记录标记为删除，之后再寻找新的空间重建更新后的记录**。

#### 2. CHAR 类型存储 ####

无论字段是否为 NULL，或者长度是多少，**char(M) 都会占用 M \* 字节编码最大长度那么多字节**。为 NULL 的话，填充的是 0x00，不为 NULL，长度不够的情况下，末尾补充 0x20.

例如上面的第四行：

列数据name(null)：00 00 00 00

还有第二行：

列数据name(zhx)：7a 68 78 20

我们将 name 的编码修改为 utf-8：

ALTER TABLE `record_test_2` 
    MODIFY COLUMN `name` char(4) CHARACTER SET utf8 NULL DEFAULT NULL AFTER `score`;

再来看第四行的数据，变成了：

列数据name(null)：00 00 00 00 00 00 00 00 00 00 00 00

因为 utf8 最大字节占用为3字节，所以这里占用 12字节。同理，第二行：

列数据name(zhx)：7a 68 78 20 20 20 20 20 20 20 20 20

对于不同编码的处理，Compact 和 Redundant 有明显的区别，**Compact 不会占用那么多字节**，而是**在某些情况下像 varchar 一样处理:**

*  NULL 还是不占用空间
 *  字段所有字符占用1字节，则按照1字节大小填充末尾的 0x20
 *  如果有其他不同字节长度的字符，则按照实际占用字节大小存储，不补充末尾的 20

举个例子，将上一节的 Compact 行格式的表，name 这一列修改编码为 utf8，同时修改数据：

ALTER TABLE `record_test_1` 
    MODIFY COLUMN `name` char(4) CHARACTER SET utf8 NULL DEFAULT NULL AFTER `score`;
    update `record_test_1` set name = "我们" where id = 2048;

来看 id 为 2048 的数据，变成了：

列数据name(我们)：e6 88 91 e4 bb ac

和 varchar 一样，占用 6 字节，正好是存储数据的大小。

其他行的数据存储不变，例如：

列数据name(zhx)：7a 68 78 20

[MySQL_ - InnoDB_ - _ - Compact]: https://blog.csdn.net/zhxdick/article/details/107049949
[InnoDB Record High-Altitude Picture]: https://dev.mysql.com/doc/internals/en/innodb-overview.html
[image]: /images/20230209/24f9899d426a4b0c8538b25a424997c4.png