InnoDB 中常用的可变长度的数据类型有 VARCHAR 、TEXT 、BLOB 等，对于这些数据类型字段的存储方式，MySQL 官方文档中有多个地方提及，但相互之间又有一些出入。
- 在
Data Type Storage Requirements部分
Variable-length string types are stored using a length prefix plus data. The length prefix requires from one to four bytes depending on the data type可变长度字符串在存储时会在开始位置额外增加 1 到 4 字节来存储数据长度，具体增加的字节数量取决于字段的数据类型
- 在
InnoDB Row Formats部分
When a table is created with ROW_FORMAT=DYNAMIC, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page.当数据表的
ROW_FORMAT为DYNAMIC时，InnoDB 可以将可变长度数据类型的字段完全存储在当前page之外的page（溢出页）当中，而聚簇索引中只存储一个长度 20 字节的指针指向数据的实际存储位置。Whether columns are stored off-page depends on the page size and the total size of the row. When a row is too long, the longest columns are chosen for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are stored in line.
字段是否存储在当前
page之外取决于page的大小以及当前行的大小。如果行特别长，那么最长的字段会选择存储到溢出页，直到当前page中可以容纳当前行。长度不超过 40 字节的TEXT以及BLOB类型的字段可以存储在当前行中。
为了搞清楚可变长度类型的字段在 InnoDB 中的存储方式，我们需要进行实际测试。首先创建数据表：
CREATE TABLE testText (
`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
`begin` CHAR(5) NOT NULL DEFAULT 'begin' COMMENT '开始标记',
`content` TEXT NOT NULL COMMENT '内容数据',
`end` CHAR(3) NOT NULL DEFAULT 'end' COMMENT '结束标记'
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci;
其中，begin 和 end 作为开始和结束的标记，来标记记录中 content 内容的起止位置。首先向数据表中插入 3 条记录：
INSERT INTO testText (`content`) VALUES (REPEAT('0123456789abcdef', 1));
INSERT INTO testText (`content`) VALUES (REPEAT('0123456789abcdef', 2));
INSERT INTO testText (`content`) VALUES (REPEAT('0123456789abcdef', 3));
现在来查看数据在 InnoDB 中的实际存储方式：
hexdump -C /usr/local/mysql/var/test/testText.ibd
00010060 02 00 1d 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......|
00010070 73 75 70 72 65 6d 75 6d 03 10 05 00 00 10 00 31 |supremum.......1|
00010080 80 00 00 01 00 00 00 02 58 19 82 00 00 01 09 01 |........X.......|
00010090 10 62 65 67 69 6e 30 31 32 33 34 35 36 37 38 39 |.begin0123456789|
000100a0 61 62 63 64 65 66 65 6e 64 03 20 05 00 00 18 00 |abcdefend. .....|
000100b0 41 80 00 00 02 00 00 00 02 58 1a 81 00 00 01 54 |A........X.....T|
000100c0 01 10 62 65 67 69 6e 30 31 32 33 34 35 36 37 38 |..begin012345678|
000100d0 39 61 62 63 64 65 66 30 31 32 33 34 35 36 37 38 |9abcdef012345678|
000100e0 39 61 62 63 64 65 66 65 6e 64 03 30 05 00 00 20 |9abcdefend.0... |
000100f0 ff 7e 80 00 00 03 00 00 00 02 58 1f 82 00 00 00 |.~........X.....|
00010100 8d 01 10 62 65 67 69 6e 30 31 32 33 34 35 36 37 |...begin01234567|
00010110 38 39 61 62 63 64 65 66 30 31 32 33 34 35 36 37 |89abcdef01234567|
*
00010130 38 39 61 62 63 64 65 66 65 6e 64 00 00 00 00 00 |89abcdefend.....|
到目前为止，第三条记录中 content 的长度虽然超过了 40 bytes（0x10137 - 0x10107 = 0x30 = 48 bytes），但仍然存储在当前行。
继续测试，发现当 content 的长度为 16 x 505 = 8080 bytes 时仍然存储在当前行；但当其长度为 16 x 506 时就会存储到溢出页当中。
INSERT INTO testText (content) VALUES (REPEAT('0123456789abcdef', 505));
INSERT INTO testText (content) VALUES (REPEAT('0123456789abcdef', 506));
hexdump -C /usr/local/mysql/var/test/testText.ibd
00010060 02 00 1d 69 6e 66 69 6d 75 6d 00 06 00 0b 00 00 |...infimum......|
00010070 73 75 70 72 65 6d 75 6d 03 10 05 00 00 10 00 31 |supremum.......1|
00010080 80 00 00 01 00 00 00 02 58 19 82 00 00 01 09 01 |........X.......|
00010090 10 62 65 67 69 6e 30 31 32 33 34 35 36 37 38 39 |.begin0123456789|
000100a0 61 62 63 64 65 66 65 6e 64 03 20 05 00 00 18 00 |abcdefend. .....|
000100b0 41 80 00 00 02 00 00 00 02 58 1a 81 00 00 01 54 |A........X.....T|
000100c0 01 10 62 65 67 69 6e 30 31 32 33 34 35 36 37 38 |..begin012345678|
000100d0 39 61 62 63 64 65 66 30 31 32 33 34 35 36 37 38 |9abcdef012345678|
000100e0 39 61 62 63 64 65 66 65 6e 64 03 30 05 00 00 20 |9abcdefend.0... |
000100f0 00 52 80 00 00 03 00 00 00 02 58 1f 82 00 00 00 |.R........X.....|
00010100 8d 01 10 62 65 67 69 6e 30 31 32 33 34 35 36 37 |...begin01234567|
00010110 38 39 61 62 63 64 65 66 30 31 32 33 34 35 36 37 |89abcdef01234567|
*
00010130 38 39 61 62 63 64 65 66 65 6e 64 03 90 9f 05 00 |89abcdefend.....|
00010140 00 28 1f b2 80 00 00 04 00 00 00 02 58 20 81 00 |.(..........X ..|
00010150 00 01 56 01 10 62 65 67 69 6e 30 31 32 33 34 35 |..V..begin012345|
00010160 36 37 38 39 61 62 63 64 65 66 30 31 32 33 34 35 |6789abcdef012345|
*
000120e0 36 37 38 39 61 62 63 64 65 66 65 6e 64 03 14 c0 |6789abcdefend...|
000120f0 05 00 00 30 df 7a 80 00 00 05 00 00 00 02 58 25 |...0.z........X%|
00012100 82 00 00 01 1b 01 10 62 65 67 69 6e 00 00 00 63 |.......begin...c|
00012110 00 00 00 05 00 00 00 01 00 00 00 00 00 00 1f a0 |................|
00012120 65 6e 64 00 00 00 00 00 00 00 00 00 00 00 00 00 |end.............|
# 存储在当前 page 之外的 page 当中的 content 内容数据
000142b0 00 00 00 00 00 00 00 00 30 31 32 33 34 35 36 37 |........01234567|
000142c0 38 39 61 62 63 64 65 66 30 31 32 33 34 35 36 37 |89abcdef01234567|
*
00016250 38 39 61 62 63 64 65 66 00 00 00 00 00 00 00 00 |89abcdef........|
此时发现，第五条记录的 content 字段的内容已经存储到了溢出页中。而第五条记录中只存储了 20 bytes 的与溢出页中数据相关的信息。
00 00 00 63 00 00 00 05 00 00 00 01 00 00 00 00 00 00 1f a0
头四位 00 00 00 63 表示溢出页所处的 tablespace 的 ID
00 00 00 05 表示溢出页的编号：第五页
00 00 00 01 表示数据在溢出页中的开始位置（偏移量）
00 00 00 00 表示版本号
最后四位 00 00 00 1f a0 表示数据的长度 （0x1fa0 = 8096 bytes）
继续测试，当 content 的长度为 990 x 16 = 14400 bytes 时，需要两个溢出页来存储 content 的数据。
INSERT INTO testText (content) VALUES (REPEAT('0123456789abcdef', 980));
INSERT INTO testText (content) VALUES (REPEAT('0123456789abcdef', 990));
INSERT INTO testText (content) VALUES (REPEAT('0123456789abcdef', 1000));
现在再来查看数据的存储方式
hexdump -C /usr/local/mysql/var/test/testText.ibd
00010150 00 01 56 01 10 62 65 67 69 6e 30 31 32 33 34 35 |..V..begin012345|
00010160 36 37 38 39 61 62 63 64 65 66 30 31 32 33 34 35 |6789abcdef012345|
*
000120e0 36 37 38 39 61 62 63 64 65 66 65 6e 64 03 14 c0 |6789abcdefend...|
000120f0 05 00 00 30 00 36 80 00 00 05 00 00 00 02 58 25 |...0.6........X%|
00012100 82 00 00 01 1b 01 10 62 65 67 69 6e 00 00 00 63 |.......begin...c|
00012110 00 00 00 05 00 00 00 01 00 00 00 00 00 00 1f a0 |................|
00012120 65 6e 64 03 14 c0 05 00 00 38 00 36 80 00 00 06 |end......8.6....|
00012130 00 00 00 02 58 26 81 00 00 01 58 01 10 62 65 67 |....X&....X..beg|
00012140 69 6e 00 00 00 63 00 00 00 06 00 00 00 01 00 00 |in...c..........|
00012150 00 00 00 00 3d 40 65 6e 64 03 14 c0 05 00 00 40 |....=@end......@|
00012160 00 36 80 00 00 07 00 00 00 02 58 2b 82 00 00 01 |.6........X+....|
00012170 0d 01 10 62 65 67 69 6e 00 00 00 63 00 00 00 07 |...begin...c....|
00012180 00 00 00 01 00 00 00 00 00 00 3d e0 65 6e 64 03 |..........=.end.|
00012190 14 c0 05 00 00 48 de d8 80 00 00 08 00 00 00 02 |.....H..........|
000121a0 58 2c 81 00 00 01 5a 01 10 62 65 67 69 6e 00 00 |X,....Z..begin..|
000121b0 00 63 00 00 00 09 00 00 00 01 00 00 00 00 00 00 |.c..............|
000121c0 3e 80 65 6e 64 00 00 00 00 00 00 00 00 00 00 00 |>.end...........|
此时可以发现，第五、第六、第七条数据的溢出页编号分别为 5 、6 、7，但第八条数据的溢出也的编号变成了 9，也就意味着第七条数据实际存储用掉了两个 page。
如果对其中任一条数据的 content 字段进行更新操作：
UPDATE testText SET content = REPEAT('0123456789abcdeF', 1000) WHERE id = 8
更新前后，数据的长度没有发生任何变化，只是将其中的 f 更新成了 F 。但此时再看数据存储方式：
00010150 00 01 56 01 10 62 65 67 69 6e 30 31 32 33 34 35 |..V..begin012345|
00010160 36 37 38 39 61 62 63 64 65 66 30 31 32 33 34 35 |6789abcdef012345|
*
000120e0 36 37 38 39 61 62 63 64 65 66 65 6e 64 03 14 c0 |6789abcdefend...|
000120f0 05 00 00 30 00 36 80 00 00 05 00 00 00 02 58 25 |...0.6........X%|
00012100 82 00 00 01 1b 01 10 62 65 67 69 6e 00 00 00 63 |.......begin...c|
00012110 00 00 00 05 00 00 00 01 00 00 00 00 00 00 1f a0 |................|
00012120 65 6e 64 03 14 c0 05 00 00 38 00 36 80 00 00 06 |end......8.6....|
00012130 00 00 00 02 58 26 81 00 00 01 58 01 10 62 65 67 |....X&....X..beg|
00012140 69 6e 00 00 00 63 00 00 00 06 00 00 00 01 00 00 |in...c..........|
00012150 00 00 00 00 3d 40 65 6e 64 03 14 c0 05 00 00 40 |....=@end......@|
00012160 00 36 80 00 00 07 00 00 00 02 58 2b 82 00 00 01 |.6........X+....|
00012170 0d 01 10 62 65 67 69 6e 00 00 00 63 00 00 00 07 |...begin...c....|
00012180 00 00 00 01 00 00 00 00 00 00 3d e0 65 6e 64 03 |..........=.end.|
00012190 14 c0 05 00 00 48 de d8 80 00 00 08 00 00 00 02 |.....H..........|
000121a0 58 31 02 00 00 01 7b 1a 78 62 65 67 69 6e 00 00 |X1....{.xbegin..|
000121b0 00 63 00 00 00 0b 00 00 00 01 00 00 00 00 00 00 |.c..............|
000121c0 3e 80 65 6e 64 00 00 00 00 00 00 00 00 00 00 00 |>.end...........|
发现，更新操作之前第八条记录的溢出页编号为 9，而此时其溢出页的编号变成了 11（0x0b）。继续往下看，发现旧的数据并没有删除，而是直接将更新后的内容存储到了新的溢出页中。
# 更新操作之前第八条记录的 content 字段的内容
000242b0 00 00 00 00 00 00 00 00 30 31 32 33 34 35 36 37 |........01234567|
000242c0 38 39 61 62 63 64 65 66 30 31 32 33 34 35 36 37 |89abcdef01234567|
*
00027ff0 38 39 61 62 63 64 65 66 48 56 bd 2f c6 c9 84 40 |89abcdefHV./...@|
00028000 24 af 6a 6c 00 00 00 0a 00 00 00 00 ff ff ff ff |$.jl............|
00028010 00 00 00 00 c6 c9 84 13 00 17 00 00 00 00 00 00 |................|
00028020 00 00 00 00 00 63 00 00 00 01 40 00 00 00 02 58 |.....c....@....X|
00028030 2c 30 31 32 33 34 35 36 37 38 39 61 62 63 64 65 |,0123456789abcde|
00028040 66 30 31 32 33 34 35 36 37 38 39 61 62 63 64 65 |f0123456789abcde|
*
00028170 66 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |f...............|
# 更新之后第八条字段的内容
0002c2b0 00 00 00 00 00 00 00 00 30 31 32 33 34 35 36 37 |........01234567|
0002c2c0 38 39 61 62 63 64 65 46 30 31 32 33 34 35 36 37 |89abcdeF01234567|
*
0002fff0 38 39 61 62 63 64 65 46 33 5b 50 7b c6 c9 82 bb |89abcdeF3[P{....|
00030000 b5 64 1f b2 00 00 00 0c 00 00 00 00 ff ff ff ff |.d..............|
00030010 00 00 00 00 c6 c9 82 bb 00 17 00 00 00 00 00 00 |................|
00030020 00 00 00 00 00 63 00 00 00 01 40 00 00 00 02 58 |.....c....@....X|
00030030 31 30 31 32 33 34 35 36 37 38 39 61 62 63 64 65 |10123456789abcde|
00030040 46 30 31 32 33 34 35 36 37 38 39 61 62 63 64 65 |F0123456789abcde|
*
00030170 46 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |F...............|
另外，查看 testText.ibd 文件的大小，发现更新操作之前文件的大小为 196K ，更新操作之后文件大小变成了 228K ，正好相差了两个 page 的大小。
# 更新之前
du -sh /usr/local/mysql/var/test/testText.ibd
196K /usr/local/mysql/var/test/testText.ibd
# 更新之后
du -sh /usr/local/mysql/var/test/testText.ibd
228K /usr/local/mysql/var/test/testText.ibd
以上测试内容针对的是 TEXT 类型，但对 VARCHAR 类型的测试结果也是一样。只不过将 content 字段定义为 VARCHAR(65535) 时，InnoDB 自动将其转换成了 MEDIUMTEXT 类型。
ALTER TABLE testText MODIFY `content` VARCHAR(65535) NOT NULL COMMENT '数据内容';
SHOW CREATE TABLE testText\G
*************************** 1. row ***************************
Table: testText
Create Table: CREATE TABLE `testText` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`begin` char(5) NOT NULL DEFAULT 'begin' COMMENT '开始标记',
`content` mediumtext NOT NULL COMMENT '数据内容',
`end` char(3) NOT NULL DEFAULT 'end' COMMENT '结束标记',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
总结
以上测试结果说明，可变长度类型的字段无论是在读、写、存储，都会对数据库性能造成一些不小的影响。
- 存储
当 content 字段的长度达到 8096 个字节时就需要存储在溢出页中，而当其长度达到 14400 个字节时就需要两个溢出页来存储。而一个 page 的大小为 16 K，很显然，这样造成了存储空间的浪费，同时对缓存也不友好，并且增大了数据文件的大小。
- 读
当数据存储到溢出页中时，在读取数据时就需要额外的 I/O 操作，并且随着数据长度的增加，可能会需要多次 I/O 才能将数据完整的读出。
- 写
测试过程中已经表明，如果可变长度字段存储在了溢出页中，那么此时对这些字段的更新并不会覆盖就数据，而是开辟新的溢出页来存储新数据。这样就造成了存储空间的浪费，增大了数据文件的大小。
