MySQL 溢出字段常规更新（full update）实验
发布于:2024-4-19
最后更新: 2024-4-22
次查看
MySQL
URL
date
slug
status
tags
summary
type

背景

继前一篇文章做的一些实验，发现在这条路上有点停不下来了。毕竟纸上得来终觉浅，当你的理论知识在实验中得到证实或者通过实验验证理论中比较模糊的点，都是一件非常爽的事情。
今天的实验主要是大字段的full update。full update指全量更新，MySQL 8.0开始支持大对象的部分更新（partial update），不过在用户侧能使用的api上还只支持json字段的几个函数（如json_set() 和 json_replace()）。
这里先介绍个工具——inno_space，本文我们会使用它来分析ibd文件。最开始是想使用innodb_ruby的，但是它不支持MySQL 8.0。关于inno_space的相关信息可以查看http://mysql.taobao.org/monthly/2021/11/02/和它的github仓库

前置知识点

MySQL 8.0对于Uncompressed LOB的结构改造

阅读这篇文章之前你需要掌握一些知识点，以便于你更好的理解。MySQL 8.0为了支持json类型字段的部分更新（partial update），对非压缩的溢出页（Uncompressed LOB）的结构做了改造。改造之前，对于需要多个溢出页保存的字段，它们之间会形成一个单向链表，且每个页面都是一种同类型FIL_PAGE_TYPE_BLOB。这种结构最大的问题就是无法根据offset快速获取到对应数据，必须一页一页的搜索，这在溢出页数量越大时性能影响也越大。
notion image
而改造之后，增加了三种新类型：
  • FIL_PAGE_TYPE_LOB_FIRST（溢出页首页）
  • FIL_PAGE_TYPE_LOB_INDEX（溢出页索引）
  • FIL_PAGE_TYPE_LOB_DATA（溢出页数据）。
如下图所示，溢出页之间不再有指针，而是由Index Entry来统一管理。FIL_PAGE_TYPE_LOB_FIRST页上预留了10个Index Entry 的位置。超过10个溢出页那就需要分配额外的FIL_PAGE_TYPE_LOB_INDEX页来存放。
FIL_PAGE_TYPE_LOB_FIRST上除了有Index Entry之外，还保存有实际溢出列的数据，所以第一个Index Entry一般是指向本页面的数据offset。可以看到，FIL_PAGE_TYPE_LOB_FIRST的命名单纯是从它所处的位置出发，并非功能，因为它很“综合”，既有索引又有数据。
notion image
通过这样的结构改造，那么给定一个偏移量，就很容易定位到具体的溢出页，提升了性能。改造之后，首页的数据容量为15680，而数据页的容量为16327。
关于上面的知识点，你感兴趣的话，可以在官网找到更详细的描述，里面还有官方做的性能测试

全量更新的流程

溢出页字段的全量更新（full update）的流程如下图所示：
notion image
大致步骤为：
  1. copy原溢出页
  1. 更新copy出的溢出页
  1. 更新聚簇索引里的溢出页指针指向新溢出页的首页
  1. 写入undo log并更新回滚指针

实验

了解完上面的知识点，下面开始正式实验，我们先创建一张包含大字段的测试表并构造一条占据一个溢出页的测试数据
CREATE TABLE `off_page` (
  `id` int NOT NULL,
  `name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert off_page(id, name) values(1, repeat('a',15680));

更新1次

然后我们来对它做一次更新，让它占据两个溢出页
update off_page set name = concat(name, repeat('a',16327)) where id = 1;
我们通过inno_space观察一下更新前后的ibd文件
notion image
更新前只有1个溢出页（1个首页），更新后变成了3个溢出页（2个首页 + 1个数据页）。不过此时Page5已经失效了（BLOB FLAGS = 1）
notion image
Page6是从Page5复制出来的，而Page7则是新创建的用于保存此次更新的数据页。可以看到Page6里的BLOB FLAGS = 0
[root@dev-middleware0003 inno_space]# ./inno -f off_page_3pages_2.ibd -p 6
File path off_page_3pages_2.ibd path, page num 6
=========================6's block==========================
FIL Header:
CheckSum: 2453923152
Page number: 6
Previous Page: 0
Next Page: 4294967295
Page LSN: 97156241
Page Type: 24
Flush LSN: 0

BLOB First Page:
BLOB FLAGS: 0
BLOB LOB VERSION: 0
BLOB LAST_TRX_ID: 15470
BLOB LAST_UNDO_NO: 0
BLOB DATA_LEN: 15680
BLOB TRX_ID: 15470
结合前面知识点里关于full update的示意图，这一块应该比较容易理解。

更新9次

我们继续更新，期望每执行一次都增加一个溢出页，前9次执行都符合预期。我们来看看第9次执行完成时的页类型分布。
[root@dev-middleware0003 inno_space]# ./inno -f off_page_11pages_2.ibd -c list-page-type
File path off_page_11pages_2.ibd path, page num 0
==========================Space Header==========================
Space ID: 54
Highest Page number: 25
Free limit Page Number: 64
FREE_FRAG page number: 15
Next Seg ID: 5
==========================space page type==========================
File size 409600
start		end		count		type
0		0		1		FSP HDR
1		1		1		INSERT BUFFER BITMAP
2		2		1		INDEX NODE PAGE
3		3		1		SDI INDEX PAGE
4		4		1		INDEX PAGE
5		6		2		FIRST PAGE OF UNCOMPRESSED BLOB PAGE
7		23	17	DATA PAGE OF UNCOMPRESSED BLOB PAGE
24	24	0		FRESHLY ALLOCATED PAGE
总共有19个溢出页，下面我们用表格来分析一下这19个溢出页是怎么形成的，关键点有2个：
  1. 每次更新都要从原溢出页复制出新页，有失效的页可以优先复用，所以可以看到交替更新每一次都可以复用前一次失效的页
  1. 看起来同类型的页有优先复用的策略？（这个只是我的猜想，待考证）
行为
溢出页使用情况
插入
5
第1次更新
6、7
第2次更新
5、8、9
第3次更新
6、7、10、11
第4次更新
5、8、9、12、13
第5次更新
6、7、10、11、14、15
第6次更新
5、8、9、12、13、16、17
第7次更新
6、7、10、11、14、15、18、19
第8次更新
5、8、9、12、13、16、17、20、21
第9次更新
6、7、10、11、14、15、18、19、22、23
最终还留下来了5、8、9、12、13、16、17、20、21这9个失效页。这些就是所谓的碎片，但是在information_schemas.tables表的data_free字段并看不出来。
mysql> select table_name, format_bytes(data_length) DATA_SIZE, (data_length/1024/16 - 1), data_free from information_schema.TABLES where table_name = 'off_page';
+------------+------------+---------------------------+-----------+
| TABLE_NAME | DATA_SIZE  | (data_length/1024/16 - 1) | DATA_FREE |
+------------+------------+---------------------------+-----------+
| off_page   | 176.00 KiB | 10.00000000               |         0 |
+------------+------------+---------------------------+-----------+
1 row in set (0.01 sec)
实际表（ibd）文件大小是400k，总共25页，其中9个是失效页。我们通过
optimize table off_page;
重新整理表后，文件缩小到272k，减少了8页，比预期少了1页，我们再用inno_space来分析一下
[root@dev-middleware0003 inno_space]# ./inno -f off_page_11pages_2_after_optimize.ibd -c list-page-type
File path off_page_11pages_2_after_optimize.ibd path, page num 0
==========================Space Header==========================
Space ID: 55
Highest Page number: 17
Free limit Page Number: 64
FREE_FRAG page number: 15
Next Seg ID: 5
==========================space page type==========================
File size 278528
start		end		count		type
0		0		1		FSP HDR
1		1		1		INSERT BUFFER BITMAP
2		2		1		INDEX NODE PAGE
3		3		1		SDI INDEX PAGE
4		5		2		INDEX PAGE
6		6		1		FIRST PAGE OF UNCOMPRESSED BLOB PAGE
7		15	9		DATA PAGE OF UNCOMPRESSED BLOB PAGE
16	16	0		FRESHLY ALLOCATED PAGE
我们发现溢出页的减少其实是符合预期的（19 -> 10减少了9页)，但是INDEX PAGE的数量竟然从1变成了2，导致整体页面只减少了8个。optimize table之后，为什么会增加一页INDEX PAGE呢？按理说索引页的数量应该和记录数相关，表里始终就只有一条记录，我百思不得其解。Google了一圈也没找到相关资料。并且试了一下，插入数据是可以复用第5页的，那么第5页的这个INDEX PAGE应该是失效页，但是从INDEX PAGE的结构里好像也没找到有标志状态的字段。
💡
问了一下Notion AI，说可能是在optimize table时会预留一些空间以备后续的数据插入和索引构建。当然不能完全确信~

更新10次

我们继续更新，这一次的结果和之前不同，一下子增加了2个页。
mysql> select table_name, format_bytes(data_length) DATA_SIZE, (data_length/1024/16 - 1), data_free from information_schema.TABLES where table_name = 'off_page';

+------------+------------+---------------------------+-----------+
| TABLE_NAME | DATA_SIZE  | (data_length/1024/16 - 1) | DATA_FREE |
+------------+------------+---------------------------+-----------+
| off_page   | 176.00 KiB | 10.00000000               |         0 |
+------------+------------+---------------------------+-----------+
1 row in set (0.00 sec)

mysql> update off_page set name = concat(name, repeat('a',16327)) where id = 1;

Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> ANALYZE table off_page;

+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.off_page | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.04 sec)

mysql> select table_name, format_bytes(data_length) DATA_SIZE, (data_length/1024/16 - 1), data_free from information_schema.TABLES where table_name = 'off_page';

+------------+------------+---------------------------+-----------+
| TABLE_NAME | DATA_SIZE  | (data_length/1024/16 - 1) | DATA_FREE |
+------------+------------+---------------------------+-----------+
| off_page   | 208.00 KiB | 12.00000000               |         0 |
+------------+------------+---------------------------+-----------+
1 row in set (0.00 sec)
💡
这里查出来的data_length字段只包含生效的页面的大小，被标记为失效的页面并不统计在里面，这里最后减1减的是数据索引页（Index Page），所以这里的第三列统计的是除聚簇索引外的生效页的数量
我们继续用inno_space来分析一下页的类型，看看这次多出来的1个页是什么？
[root@dev-middleware0003 inno_space]# ./inno -f off_page_13pages_2.ibd -c list-page-type
File path off_page_13pages_2.ibd path, page num 0
==========================Space Header==========================
Space ID: 72
Highest Page number: 28
Free limit Page Number: 64
FREE_FRAG page number: 17
Next Seg ID: 5
==========================space page type==========================
File size 458752
start		end		count		type
0		0		1		FSP HDR
1		1		1		INSERT BUFFER BITMAP
2		2		1		INDEX NODE PAGE
3		3		1		SDI INDEX PAGE
4		4		1		INDEX PAGE
5		6		2		FIRST PAGE OF UNCOMPRESSED BLOB PAGE
7		25	19	DATA PAGE OF UNCOMPRESSED BLOB PAGE
26	26	1		INDEX PAGE OF UNCOMPRESSED BLOB PAGE
27	27	0		FRESHLY ALLOCATED PAGE
原来是多了1个溢出页索引页（FIL_PAGE_TYPE_LOB_INDEX），因为溢出页首页里只预留了10个索引位，超过10个之后就需要分配额外的索引页来存放。这也印证了我们前面说的知识点。

更新15次

我们继续更新，发现更新15次之后，又出现了一个诡异的现象。这一次除聚簇索引外的生效页面数量直接从16增长到了80。
mysql> select table_name, format_bytes(data_length) DATA_SIZE, (data_length/1024/16 - 1), data_free from information_schema.TABLES where table_name = 'off_page';
+------------+------------+---------------------------+-----------+
| TABLE_NAME | DATA_SIZE  | (data_length/1024/16 - 1) | DATA_FREE |
+------------+------------+---------------------------+-----------+
| off_page   | 272.00 KiB | 16.00000000               |         0 |
+------------+------------+---------------------------+-----------+
1 row in set (0.01 sec)
 
mysql> update off_page set name = concat(name, repeat('a',16327)) where id = 1;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> ANALYZE table off_page;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.off_page | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.02 sec)
 
mysql> select table_name, format_bytes(data_length) DATA_SIZE, (data_length/1024/16 - 1), data_free from information_schema.TABLES where table_name = 'off_page';
+------------+-----------+---------------------------+-----------+
| TABLE_NAME | DATA_SIZE | (data_length/1024/16 - 1) | DATA_FREE |
+------------+-----------+---------------------------+-----------+
| off_page   | 1.27 MiB  | 80.00000000               |         0 |
+------------+-----------+---------------------------+-----------+
1 row in set (0.00 sec)
我们先来看看更新后的页面分布：
[root@dev-middleware0003 inno_space]# ./inno -f off_page_81pages.ibd -c list-page-type
File path off_page_81pages.ibd path, page num 0
==========================Space Header==========================
Space ID: 53
Highest Page number: 128
Free limit Page Number: 128
FREE_FRAG page number: 21
Next Seg ID: 5
==========================space page type==========================
File size 2097152
start		end		count		type
0		0		1		FSP HDR
1		1		1		INSERT BUFFER BITMAP
2		2		1		INDEX NODE PAGE
3		3		1		SDI INDEX PAGE
4		4		1		INDEX PAGE
5		6		2		FIRST PAGE OF UNCOMPRESSED BLOB PAGE
7		25	19	DATA PAGE OF UNCOMPRESSED BLOB PAGE
26	26	1		INDEX PAGE OF UNCOMPRESSED BLOB PAGE
27	27	1		DATA PAGE OF UNCOMPRESSED BLOB PAGE
28	28	1		INDEX PAGE OF UNCOMPRESSED BLOB PAGE
29	36	8		DATA PAGE OF UNCOMPRESSED BLOB PAGE
37	63	27	FRESHLY ALLOCATED PAGE
64	64	1		DATA PAGE OF UNCOMPRESSED BLOB PAGE
65	127	62	FRESHLY ALLOCATED PAGE
再用我们之前的表格分析一下：
行为
溢出页使用情况
溢出索引页使用情况
插入
5
ㅤ
第1次更新
6、7
ㅤ
第2次更新
5、8、9
ㅤ
第3次更新
6、7、10、11
ㅤ
第4次更新
5、8、9、12、13
ㅤ
第5次更新
6、7、10、11、14、15
ㅤ
第6次更新
5、8、9、12、13、16、17
ㅤ
第7次更新
6、7、10、11、14、15、18、19
ㅤ
第8次更新
5、8、9、12、13、16、17、20、21
ㅤ
第9次更新
6、7、10、11、14、15、18、19、22、23
ㅤ
第10次更新
5、8、9、12、13、16、17、20、21、24、25
26
第11次更新
6、7、10、11、14、15、18、19、22、23、26、27
28
第12次更新
5、8、9、12、13、16、17、20、21、24、25、28、29
26
第13次更新
6、7、10、11、14、15、18、19、22、23、26、27、30、31
28
第14次更新
5、8、9、12、13、16、17、20、21、24、25、28、29、32、33
26
第15次更新
6、7、10、11、14、15、18、19、22、23、26、27、30、31、34、35
28
通过上面2份信息我们可以得出：
  1. 溢出索引页也会失效，也可以复用，所以更新过程中始终有2个页面在交替复用
  1. 第15次更新后的溢出数据页的数量是31个，包含
    1. 16个生效的溢出数据页
    2. 15个失效的溢出数据页
我们再来看看更新之前的页面分布：
[root@dev-middleware0003 inno_space]# ./inno -f off_page_17pages_2.ibd -c list-page-type
File path off_page_17pages_2.ibd path, page num 0
==========================Space Header==========================
Space ID: 74
Highest Page number: 37
Free limit Page Number: 64
FREE_FRAG page number: 21
Next Seg ID: 5
==========================space page type==========================
File size 606208
start		end		count		type
0		0		1		FSP HDR
1		1		1		INSERT BUFFER BITMAP
2		2		1		INDEX NODE PAGE
3		3		1		SDI INDEX PAGE
4		4		1		INDEX PAGE
5		6		2		FIRST PAGE OF UNCOMPRESSED BLOB PAGE
7		25	19	DATA PAGE OF UNCOMPRESSED BLOB PAGE
26	26	1		INDEX PAGE OF UNCOMPRESSED BLOB PAGE
27	27	1		DATA PAGE OF UNCOMPRESSED BLOB PAGE
28	28	1		INDEX PAGE OF UNCOMPRESSED BLOB PAGE
29	35	7		DATA PAGE OF UNCOMPRESSED BLOB PAGE
36	36	0		FRESHLY ALLOCATED PAGE
可以看到更新之前是37个页面，其中数据占据了32个页面，还有一个是FRESHLY ALLOCATED PAGE。而InnoDB分配页面的机制是刚开始是从碎片区开始分配，分配32个页面之后，开始以区（Extent）为单位，一次性分配连续的64个页面，可以利用顺序IO提升性能。看起来应该是这个机制起到了作用。
但是我测试下来，这个值是33个页面，而并非32个页面。不知道问题出在哪里。

溢出页首页的结构

这里我们顺势来分析一下溢出页首页的结构，整体结构如下图所示：
notion image

Index List 和 Index Free Nodes

我们先看Index List和Index Free Nodes这两个区域的数据：
更新9次，刚好10个溢出页时
Index List
00 00 00 0a       len表示index entry的数量（10）
00 00 00 06 00 60 first element（Page6（本页），offset=96）
00 00 00 06 02 7c last element（Page6（本页），offset=636，刚好等于96 + 9 * 60，也就是本页的第10个entry，也就是预留的最后一个entry）

Index Free Nodes
00 00 00 00       len（此时没有多余的空间了）
ff ff ff ff 00 00 first element
ff ff ff ff 00 00 last element
更新10次，11个溢出页时
Index List
00 00 00 0b       len表示index entry的数量（11）
00 00 00 05 00 60 first element（指向Page6（本页），offset=96）
00 00 00 1a 00 27 last element（指向Page26，offset=39，前38个字节为FIL Header，第39个字节是version）

Index Free Nodes
00 00 01 0f       len(此时应该是新索引页内的剩余空间)
00 00 00 1a 00 63 first element（指向Page26，offset=99，刚好等于39 + 60）
00 00 00 1a 3f ab last element（指向Page26，offset=16299，剩余空间已经放不下一个Index Entry）
看起来在创建Index Entry时，会根据Index Free Nodes里有没有剩余空间来决定是要新分配页还是在已有页上创建。创建完之后再维护到Index List里。

Index Entry

我们再来看看Index Entry，每个Index Entry占据60个字节，具体结构如下
notion image
它在实际保存数据之前的600个字节。我们只分析最后两个Index Entry，可以看到对应的OFFSET_PAGE_NO是0x17=23 和0x16=22，跟我们前面表格里分析的生效页一致。
00 00 00 06 02 40                                   OFFSET_PREV
ff ff ff ff 00 00                                   OFFSET_NEXT
00 00 00 00 ff ff ff ff 00 00 ff ff ff ff 00 00     OFFSET_VERSIONS
00 00 00 00 3c 35                                   OFFSET_TRXID
00 00 00 00 3c 35                                   OFFSET_TRXID_MODIFIER
00 00 00 00                                         OFFSET_TRX_UNDO_NO
00 00 00 00                                         OFFSET_TRX_UNDO_NO_MODIFIER
00 00 00 17                                         OFFSET_PAGE_NO
3f c7 00 00                                         OFFSET_DATA_LEN
00 00 00 01                                         OFFSET_LOB_VERSION

00 00 00 06 02 04                                   OFFSET_PREV
00 00 00 06 02 7c                                   OFFSET_NEXT
00 00 00 00 ff ff ff ff 00 00 ff ff ff ff 00 00     OFFSET_VERSIONS
00 00 00 00 3c 35                                   OFFSET_TRXID
00 00 00 00 3c 35                                   OFFSET_TRXID_MODIFIER
00 00 00 00                                         OFFSET_TRX_UNDO_NO
00 00 00 00                                         OFFSET_TRX_UNDO_NO_MODIFIER
00 00 00 16                                         OFFSET_PAGE_NO
3f c7 00 00                                         OFFSET_DATA_LEN
00 00 00 01                                         OFFSET_LOB_VERSION

参考

  1. Innodb BLOB
  1. MySQL的analyze table和optimize table对表进行定期优化
  1. Externally Stored Fields in InnoDB
  1. MySQL 8.0: InnoDB Introduces LOB Index For Faster Updates
  1. MySQL 8.0: MVCC of Large Objects in InnoDB
  1. MySQL · 引擎特性 · 庖丁解InnoDB之UNDO LOG
  1. MySQL8.0 新特性：Partial Update of LOB Column
 
相关文章
2024-04-07
用几个实验验证char、varchar和text的底层存储
2024-03-14
tomcat-jdbc连接池对于socketTimeout超时断连的处理BUG
2023-12-14
如何解析离线binlog文件
2023-10-08
mysql流式查询下的性能隐患
2023-10-08
mysql流式查询中的一个坑
2023-05-03
刷一张亿级表带来的思考
HTTP缓存机制解析用几个实验验证char、varchar和text的底层存储