最近我们在咨询团队中讨论了 InnoDB 如何处理 TEXT/BLOB 列。更具体地说，争论是围绕具有动态行的 Barracuda file 的格式。
在 InnoDB 官方文档中，您可以找到以下摘录：
当使用 ROW_FORMAT=DYNAMIC 创建表时，InnoDB 可以完全离页存储长可变长度列值（对于 VARCHAR、VARBINARY 和 BLOB 和 TEXT 类型），聚集索引记录仅包含一个 20 字节的指向溢出页面。
…
列是否存储在页外取决于页面大小和行的总大小。当一行太长时，选择最长的列进行页外存储，直到聚集索引记录适合 B 树页面。小于或等于 40 字节的 TEXT 和 BLOB 列存储在行中。
第一段表明 InnoDB可以在溢出页面中完全离页存储长列。在文档的更下方，描述了短值的行为，即长度小于 40 字节的值。这些短值存储在行中。
我们的论点是关于长度超过 40 字节的情况。这些值是存储在行中还是溢出页中。我真的不需要一个理由来开始挖掘一个话题，想象一下我什么时候有一个。借助简单的常用工具，让我们进行实验并找出答案。
TEXT/BLOB 实验
对于我们的实验，我们需要一个 MySQL 数据库服务器、一个带有 TEXT 列的表和hexdump实用程序。我在实验室中使用 Percona-server 8.0 启动了一个简单的 LXC 实例并创建了下表：
CREATE TABLE `testText` (
`id` int(11) unsigned NOT NULL,
`before` char(6) NOT NULL DEFAULT 'before',
`data` text NOT NULL,
`after` char(5) NOT NULL DEFAULT 'after',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
在之前和之后列在那里帮助定位数据文件页面中的文本列。我们可以在表中插入几行，大小为 16 的乘数的 TEXT 值使用hexdump很方便，连续的相同行被替换为“*”。
mysql> insert into testText (id,data) values (1,repeat('0123456789abcdef',1));
Query OK, 1 row affected (0.01 sec)
mysql> insert into testText (id,data) values (2,repeat('0123456789abcdef',2));
Query OK, 1 row affected (0.00 sec)
mysql> insert into testText (id,data) values (3,repeat('0123456789abcdef',3));
Query OK, 1 row affected (0.01 sec)
然后我们在 shell 级别使用hexdump实用程序，第一个叶页从偏移量 0xc000 开始：
Shell # hexdump -C /var/lib/mysql/test/testText.ibd ... 0000c060 02 00 1b 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......| 0000c070 73 75 70 72 65 6d 75 6d 10 00 00 10 00 32 00 00 |supremum.....2..| 0000c080 00 01 00 00 00 05 74 3f d9 00 00 01 5d 01 10 62 |......t?....]..b| 0000c090 65 66 6f 72 65 30 31 32 33 34 35 36 37 38 39 61 |efore0123456789a| 0000c0a0 62 63 64 65 66 61 66 74 65 72 20 00 00 18 00 42 |bcdefafter ....B| 0000c0b0 00 00 00 02 00 00 00 05 74 40 da 00 00 01 5e 01 |........t@....^.| 0000c0c0 10 62 65 66 6f 72 65 30 31 32 33 34 35 36 37 38 |.before012345678| 0000c0d0 39 61 62 63 64 65 66 30 31 32 33 34 35 36 37 38 |9abcdef012345678| 0000c0e0 39 61 62 63 64 65 66 61 66 74 65 72 30 00 00 20 |9abcdefafter0.. | 0000c0f0 ff 7e 00 00 00 03 00 00 00 05 74 45 dd 00 00 01 |.~........tE....| 0000c100 62 01 10 62 65 66 6f 72 65 30 31 32 33 34 35 36 |b..before0123456| 0000c110 37 38 39 61 62 63 64 65 66 30 31 32 33 34 35 36 |789abcdef0123456| * 0000c130 37 38 39 61 62 63 64 65 66 61 66 74 65 72 00 00 |789abcdefafter..| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 # hexdump -C /var/lib/mysql/test/testText.ibd ... 0000c060 02 00 1b 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......| 0000c070 73 75 70 72 65 6d 75 6d 10 00 00 10 00 32 00 00 |supremum.....2..| 0000c080 00 01 00 00 00 05 74 3f d9 00 00 01 5d 01 10 62 |......t?....]..b| 0000c090 65 66 6f 72 65 30 31 32 33 34 35 36 37 38 39 61 |efore0123456789a| 0000c0a0 62 63 64 65 66 61 66 74 65 72 20 00 00 18 00 42 |bcdefafter ....B| 0000c0b0 00 00 00 02 00 00 00 05 74 40 da 00 00 01 5e 01 |........t@....^.| 0000c0c0 10 62 65 66 6f 72 65 30 31 32 33 34 35 36 37 38 |.before012345678| 0000c0d0 39 61 62 63 64 65 66 30 31 32 33 34 35 36 37 38 |9abcdef012345678| 0000c0e0 39 61 62 63 64 65 66 61 66 74 65 72 30 00 00 20 |9abcdefafter0.. | 0000c0f0 ff 7e 00 00 00 03 00 00 00 05 74 45 dd 00 00 01 |.~........tE....| 0000c100 62 01 10 62 65 66 6f 72 65 30 31 32 33 34 35 36 |b..before0123456| 0000c110 37 38 39 61 62 63 64 65 66 30 31 32 33 34 35 36 |789abcdef0123456| * 0000c130 37 38 39 61 62 63 64 65 66 61 66 74 65 72 00 00 |789abcdefafter..|
显然，这些值存储在行内，即使是长度为 48 的第三行。
截止到溢出页面
如果我们继续增加长度，行为保持不变，直到 8080 字节的长度（505 次重复）。如果我们将长度增加 16 个字节，该行将变得大于页面上可用空间的一半。此时，TEXT 值被移动到溢出页，并由行本身中的 20 字节指针替换。行中的指针如下所示：
0000c060 02 00 1c 69 6e 66 69 6d 75 6d 00 02 00 0b 00 00 |...infimum......| 0000c070 73 75 70 72 65 6d 75 6d 14 c0 00 00 10 ff f1 00 |supremum........| 0000c080 00 00 01 00 00 00 05 74 80 a4 00 00 01 fe 01 10 |.......t........| 0000c090 62 65 66 6f 72 65 00 00 01 02 00 00 00 04 00 00 |before..........| 0000c0a0 00 26 00 00 00 00 00 00 1f a0 61 66 74 65 72 00 |.&........after.| 0000c0b0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| The 20 bytes pointer is: 00 00 01 02 00 00 00 04 00 00 00 26 00 00 00 00 00 00 1f a0 Space ID: 00 00 01 02 First overflow page: 00 00 00 04 (4 x 0x4000 = 0x10000) Offset in overflow page: 00 00 00 26 Version number: 00 00 00 00 Total length of the TEXT value: 00 00 1f a0 (0x1fa0 = 8096 = 16*506)
溢出页面：
00010000 c1 06 3d 24 00 00 00 04 00 00 00 00 00 00 00 00 |..=$............| 00010010 00 00 00 00 74 dd 8e 3f 00 0a 00 00 00 00 00 00 |....t..?........| 00010020 00 00 00 00 01 02 00 00 1f a0 ff ff ff ff 30 31 |..............01| 00010030 32 33 34 35 36 37 38 39 61 62 63 64 65 66 30 31 |23456789abcdef01| * 00011fc0 32 33 34 35 36 37 38 39 61 62 63 64 65 66 00 00 |23456789abcdef..| 00011fd0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| The value header starting @ 0x10026: 00 00 1f a0 ff ff ff ff Length in that overflow page: 00 00 1f a0 (0x1fa0 = 8096 = 16*506) Next overflow page: ff ff ff ff (this means it is the last)
综上所述，我们可以得出一些有趣的观察结果：
- 20 字节的指针包含一个 space_id 值，因此理论上，溢出页可能位于另一个表空间（ibd 文件）中。
- 即使这是一个 TEXT 而不是 LONGTEXT 列，总长度也使用了 4 个字节。2 个字节就足够了。
- 即使最大可能的 InnoDB 页面大小为 64KB，溢出页面块的长度也使用 4 个字节作为长度。
性能影响
TEXT/BLOB 列是许多与性能相关的影响的来源。让我们回顾一下最明显的那些。
贮存
第一个性能影响与存储效率低下有关。如果使用默认的 InnoDB 页面大小，则 TEXT/BLOB 值存储在 16KB 的块中。这意味着，当使用溢出页时，每个值平均会丢失大约 8KB。这会导致更大的数据文件和更低的缓存效率。
读取
TEXT/BLOB 列的存在可以显着增加查询所需的读取 IOP 数量。例如，让我们考虑以下简单查询：
SELECT * from myTable limit 10;
如果我们忽略缓存，没有 TEXT/BLOB 列，上面的查询将只需要在 myTable 的主键 btree 中的每一级读取 IOP。对于小表，这可能是一两个读取 IOP。现在，如果每行都有一个 1MB 的 TEXT/BLOB 列，那么相同的简单查询将需要超过 640 次读取 IOP，因为每个 TEXT/BLOB 值是一个由 64 个 16KB 页组成的链。
写
在本节中，让我们假设最坏的情况，启用基于行的复制和完整的行映像。现在，如果我们在 Percona 服务器 8.0 实例中插入一个具有 1MB 值的行作为 longText 列，我们有：
+----------------------------------+---------------------------+
| FILE_NAME | SUM_NUMBER_OF_BYTES_WRITE |
+----------------------------------+---------------------------+
| /var/lib/mysql/#ib_16384_0.dblwr | 1572864 |
| /var/lib/mysql/ib_logfile0 | 1174528 |
| /var/lib/mysql/test/testText.ibd | 1130496 |
| /var/lib/mysql/binlog.000003 | 1048879 |
| /tmp/MLfd=41 | 1048783 |
| /var/lib/mysql/undo_001 | 278528 |
| /var/lib/mysql/undo_002 | 131072 |
| /var/lib/mysql/ibdata1 | 16384 |
| /var/lib/mysql/mysql.ibd | 16384 |
+----------------------------------+---------------------------+
总共大约 6.4 MB。这并不奇怪，有两个日志文件，数据也因为双写缓冲区而被写入两次。临时文件用于磁盘 binlog 缓存，除非事务很长，否则它实际上不会写入存储。
无论如何，这只是为更新后发生的事情做准备。如果我只是更改 longText 值的最后一个字母，写入的数据量会增加到大约 10 MB。
+----------------------------------+---------------------------+
| FILE_NAME | SUM_NUMBER_OF_BYTES_WRITE |
+----------------------------------+---------------------------+
| /var/lib/mysql/#ib_16384_0.dblwr | 2260992 |
| /var/lib/mysql/test/testText.ibd | 2211840 |
| /var/lib/mysql/binlog.000003 | 2097475 |
| /tmp/MLfd=41 | 2097379 |
| /var/lib/mysql/ib_logfile0 | 1129472 |
| /var/lib/mysql/undo_001 | 32768 |
| /var/lib/mysql/ibdata1 | 16384 |
+----------------------------------+---------------------------+
longText 值没有在原地修改，它被复制到一组新的溢出页面。然后需要将新旧溢出页面刷新到存储中。此外，由于我们使用全行图像的最坏情况，二进制日志条目存储了旧值和新值，但 InnoDB 日志文件只有新版本。
我希望这能说明为什么在 TEXT/BLOB 列中存储可变数据是一个坏主意。
JSON
尽管使用 MySQL JSON 数据类型存储的列存储为 TEXT/BLOB 列，但 MySQL 8.0 添加了一些逻辑以允许就地更新。8.0 中对大型 JSON 列的更新的影响没有 5.7 中那么严重。
如何最好地处理 TEXT/BLOB 列？
数据压缩
数据压缩是 TEXT/BLOB 值的一个非常引人注目的选项 根据定义，这些值通常很大，因此通常压缩得很好。这不是什么新鲜事，之前已经报道 过。例如，PostgreSQL 默认压缩其 TEXT/BLOB 列（称为 TOAST）。
当然，压缩的最佳选择始终是应用程序。正如我们刚刚看到的，这减少了写负载并使数据库免于压缩的 CPU 负担。
MySQL 的另一个选择是 InnoDB Barracuda 表压缩。使用时，TEXT/BLOB 值在写入溢出页面之前会被压缩。这比一次压缩一页要有效得多。
最后，如果您使用 Percona Server 或 MariaDB，您可以访问透明列压缩。这是第二好的选择，性能方面，在应用程序压缩之后是不可能的。
避免返回 TEXT/BLOB 列
当表中存在较大的 TEXT/BLOB 列时，访问这些列的成本很高。因此，仅选择需要的列并避免默认使用“select * from”非常重要。不幸的是，许多 ORM 框架通过抓取所有列来实例化对象。使用此类框架时，应考虑将 TEXT/BLOB 存储在与原始表具有松散 1:1 关系的单独表中。这样，ORM 就能够实例化对象，而不必强制读取 TEXT/BLOB 列。
结论
我希望这篇文章能提高您对 InnoDB 中 TEXT/BLOB 值的理解。如果使用得当，TEXT/BLOB 列可能很有用，并且对数据库性能的影响有限。
