可变长度字段在 InnoDB 中的存储方式

·  阅读 171

  InnoDB 中常用的可变长度的数据类型有 VARCHARTEXTBLOB 等,对于这些数据类型字段的存储方式,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_FORMATDYNAMIC 时,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;
复制代码

  其中,beginend 作为开始和结束的标记,来标记记录中 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 才能将数据完整的读出。

  测试过程中已经表明,如果可变长度字段存储在了溢出页中,那么此时对这些字段的更新并不会覆盖就数据,而是开辟新的溢出页来存储新数据。这样就造成了存储空间的浪费,增大了数据文件的大小。

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改