mysql page header_浅析InnoDB Record Header及page overflow

最新推荐文章于 2023-04-04 21:02:50 发布

weixin_39984098

最新推荐文章于 2023-04-04 21:02:50 发布

阅读量347

点赞数

文章标签： mysql page header

本文链接：https://blog.csdn.net/weixin_39984098/article/details/113306052

版权

原标题：浅析InnoDB Record Header及page overflow

前言

土人有土办法，利用innodb_ruby观测验证innodb page header及overflow临界点。

这是一篇封存了两年的文章，最近拿出来重新整理发布。1、背景信息 1.1 InnoDB Diagrams项目

首先看一下Jeremy Cole、Davi Arnaut两位大神联合维护的牛逼项目：InnoDB Diagrams。

在这个项目中，详细介绍了InnoDB引擎数据结构、日志结构，以及InnoDB内部的运行机制。

为了配合这个项目，二位大神还开发了一个InnoDB数据文件解析工具 innodb_ruby

1.2 InnoDB Record Header

InnoDB Diagrams项目中有一张图介绍了InnoDB record header是如何构成的

关于这些头信息的解释可见文章 The physical structure of records in InnoDB，本文不赘述。

简言之，记住几条关键规则

一条记录的record header，至少是5字节

对record header影响最大的是变长列数量，及其是否允许为NULL的属性

关于变长列

每个变长列，如果列长度 <128 字节，则需要额外1字节

每个变长列，如果列长度 >=128 字节，则需要额外2字节

如果没有任何变长列，则无需这额外的1-2字节

变长类型为char/varchar/text/blob等

同学们可能会诧异，char为什么也当做变长类型了？这是因为，当字符集非latin1时，最大存储长度可能会超过255字节，例如 char(65) utf8mb4 最长就可以存储260字节，此时在record header中需要用2字节来表示其长度，因此也被当做变长类型了

关于列允许为NULL

每个列如果允许为NULL，则增加 1bit，不足8bit也需要额外1字节

例如只有2个列允许为NULL，只需要2bit来表示，但也需要占用1字节

P.S，在InnoDB的存储结构里，从tablespace到segment，再到extent、page，还是file层面，总有各种必要的header或trailer信息需要消耗额外的字节数，不像MyISAM那么简单。

1.3 innodb_ruby项目

上面提过，innodb_ruby工具可以帮助我们进一步理解InnoDB引擎数据结构、日志结构。

该项目用ruby语言开发(大神真是任性，选了这个比较冷门的开发语言)。

特别提醒，该项目已经多年未更新，有些数据类型发生了变化(例如最经典的5.6之后时间日期类型)，它解析的可能就不准确了，在我下面的实测案例中也证实了这点。因此，我还用到另外一个辅助工具 innblock。

1.4 innblock工具

由八怪开发，用于扫描和分析InnoDB page，详见 innblock | InnoDB page观察利器

2、定义不同数据类型时的record header消耗

根据上面的理论，我们接下来计算并验证当表里有各种不同的数据类型时，分别需要多少额外字节。

关于测试环境

MySQL版本：Percona Server 5.7.22-22

测试配套工具：innodb_ruby & innblock

特别提醒，测试表的字符集为utf8mb4。

经过计算和验证，最终可以得到以下几条结论：

每条记录的record header(下面简称RH)基础是5字节(简写成 RH=5)

每当表中多一个列允许为NULL，则额外增加1bit，且不足8bit时也需要消耗1字节(同理，不足16bit时需要消耗2字节)

每当表中多一个最大定义存储长度不超过255字节的变长列(char/varchar)时，额外增加1字节

每当表中多一个最大定义存储长度超过255字节的变长列(char/varchar/text/blob)时，额外增加2字节

由此我们可以推断出以下几种场景所需的record header大小(横屏观看，下同)

场景

表定义

行长度

(字节)

record header

(字节)

备注说明

id int not null

提醒：无显式主键

5(RH)

+6(TRX_ID)

+7(ROLL_PTR)

+6(ROW_ID)

+4(INT) = 28

id int

提醒：无显式主键，且未指定not null

6(允许null) + 6 +7 +6

= 28

id int not null

primary key(id)

提醒：id列是显式主键

5 + 6 + 7

= 22

id int not null,

c1 char(10),

primary key(id)

c1列只存储一个字符'a'

7(char+null)+

6 + 7

+ 4 + 10(c1)

= 34

id int not null,

c1 varchar(10),

primary key(id)

c1列只存储一个字符'a'

7(varchar+null) +

6 + 7

+ 4 + 1(c1)

= 25

更多的场景，详见下面这个汇总表格

3、案例测试验证过程

我们以场景18为例做测试。

测试表结构见下 (横屏观看，下同)

[root@yejr.me]> CREATE TABLE `t1` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`c1` varchar(10) DEFAULT NULL,

`c2` varchar(10) DEFAULT NULL,

`c3` varchar(10) DEFAULT NULL,

`c4` varchar(10) DEFAULT NULL,

`c5` varchar(10) DEFAULT NULL,

`c6` varchar(10) DEFAULT NULL,

`c7` varchar(10) DEFAULT NULL,

`c8` varchar(10) DEFAULT NULL,

`c9` varchar(10) DEFAULT NULL,

`c10` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

插入两条数据

[root@yejr.me]>insert into t1 values(1,'a','a','a','a','a','a','a','a','a','a');

[root@yejr.me]>insert into t1 values(2,'b','b','b','b','b','b','b','b','b','b');

用innblock工具查看数据结构：

[root@yejr.me]# innblock innodb/t2_varchar.ibd 3 16

...

-----Total used rows:4 used rows list(phy):

(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2

(2) SUPREMUM record offset:112 heapno:1 n_owned 3,delflag:N minflag:0 rectype:3

(3) normal record offset:137 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0

(4) normal record offset:181 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0

可以看到一条物理记录的长度是 181-137=44字节，分别由以下几部分组成：

record header：17字节

其中基础RH=5

共10个varchar列均允许为NULL因此需要额外10bit折算成2字节

另外每个varchar列最大定义存储长度10*4=40，没超过256字节，每个varchar列需要额外1个字节，共10字节

那么5+2+10=17字节

db_trx_id: 6字节

db_roll_ptr: 7字节

id列: 4字节

c1 ~ c10列：10字节

我们再用innodb_ruby工具验证一下：

[root@yejr.me]# innodb_space -s ibdata1 -T innodb/t1 -p 3 page-dump

...

records:

{:format=>:compact,

:offset=>137,

:header=>

{:next=>181,

:type=>:conventional,

:heap_number=>2,

:n_owned=>0,

:min_rec=>false,

:deleted=>false,

:nulls=>[],

:lengths=>

{"c1"=>1,

"c2"=>1,

"c3"=>1,

"c4"=>1,

"c5"=>1,

"c6"=>1,

"c7"=>1,

"c8"=>1,

"c9"=>1,

"c10"=>1},

:externs=>[],

:length=>17}, #所有record header总大小是17字节

:next=>181,

:type=>:clustered,

:key=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>1}],

:row=>

[{:name=>"c1", :type=>"VARCHAR(40)", :value=>"a"},

{:name=>"c2", :type=>"VARCHAR(40)", :value=>"a"},

{:name=>"c3", :type=>"VARCHAR(40)", :value=>"a"},

{:name=>"c4", :type=>"VARCHAR(40)", :value=>"a"},

{:name=>"c5", :type=>"VARCHAR(40)", :value=>"a"},

{:name=>"c6", :type=>"VARCHAR(40)", :value=>"a"},

{:name=>"c7", :type=>"VARCHAR(40)", :value=>"a"},

{:name=>"c8", :type=>"VARCHAR(40)", :value=>"a"},

{:name=>"c9", :type=>"VARCHAR(40)", :value=>"a"},

{:name=>"c10", :type=>"VARCHAR(40)", :value=>"a"}],

:sys=>

[{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>10518},

{:name=>"DB_ROLL_PTR",

:type=>"ROLL_PTR",

:value=>

{:is_insert=>true,

:rseg_id=>100,

:undo_log=>{:page=>491, :offset=>272}}}],

:length=>27, #row data总大小是27字节，加上RH的17字节，总共是44字节，和推测结果一致

:transaction_id=>10518,

:roll_pointer=>

{:is_insert=>true, :rseg_id=>100, :undo_log=>{:page=>491, :offset=>272}}}

... 4、什么时候发生page overflow

我们大概知道，一条记录由于有较多的变长列(varchar/text/blob类型)，当长度约为page size的一半时，就会发生overflow，会把最长的那个列存储在独立的page中，聚集索引中用20字节的指针指向那个page(dynamic row format时是这么做的，不同row format处理方式也不同)。文档中是这么说的

Variable-length columns are an exception to the rule that column values are stored in B-tree index nodes. Variable-length columns that are too long to fit on a B-tree page are stored on separately allocated disk pages called overflow pages. Such columns are referred to as off-page columns. The values of off-page columns are stored in singly-linked lists of overflow pages, with each such column having its own list of one or more overflow pages. Depending on column length, all or a prefix of variable-length column values are stored in the B-tree to avoid wasting storage and having to read a separate page.

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. Fixed-length fields greater than or equal to 768 bytes are encoded as variable-length fields. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

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.

我们来测试下，一条记录长度到底是多少时，会发生overflow。

测试表结构

[root@yejr.me]> CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`c1` text NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我们先进行反推，计算每条记录最长能存储多少字节：

默认的page size是16KB，也就是16384字节

FIL Header消耗38字节

Index Header消耗36字节

File Segment Header消耗20字节

Infimum & Supremum 两条虚拟记录共消耗26字节

FIL Trailer消耗8字节

由于此时page中最多只有两条物理记录，所以Page Directory消耗4字节

每条记录还需要额外消耗DB_TRX_ID(6B)、DB_ROLL_PTR(7B)共13字节

上述测试表的record header需要消耗7字节

最后可算得每条记录中，text列最多不可超过(16384-38-36-20-26-8-4)/2-13-7-4=8102字节

经过实测，text列最多可存储8101字节，一旦超过该值，就会发生overflow

如果不预设表中各个列数据类型的话，当一条记录超过8125字节(含可变长度的record header，以及DB_TRX_ID、DB_ROLL_PTR的13字节在内)时，就会发生overflow

插入两条测试数据

[root@yejr.me]> insert into t1 select 1,repeat('a',8102);

[root@yejr.me]> insert into t1 select 2,repeat('a',8101);

用innblock工具解析

[root@yejr.me]# innblock innodb/t1.ibd 3 16

...

-----Total used rows:4 used rows list(phy):

(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2

(2) SUPREMUM record offset:112 heapno:1 n_owned 3,delflag:N minflag:0 rectype:3

(3) normal record offset:127 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0

(4) normal record offset:171 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0

...

注意到第一条记录的长度是 171-127=44字节，可见的确是做overflow处理了。

再用innodb_ruby工具解析整个page

[root@yejr.me]# innodb_space -s ibdata1 -T innodb/t1 -p 3 page-dump

...

records:

{:format=>:compact,

:offset=>127,

:header=>

{:next=>171,

...

:key=>[{:name=>"id", :type=>"INT", :value=>1}],

:row=>

[{:name=>"c1",

:type=>"BLOB",

:value=>"",

:extern=>{:space_id=>214, :page_number=>4, :offset=>38, :length=>8102}}],

...

{:format=>:compact,

:offset=>171,

:header=>

{:next=>112,

:type=>:conventional,

...

:key=>[{:name=>"id", :type=>"INT", :value=>2}],

:row=>

[{:name=>"c1",

:type=>"BLOB",

:value=>

"aaaaaaa...a"}],

:sys=>

[{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>10619},

{:name=>"DB_ROLL_PTR",

:type=>"ROLL_PTR",

:value=>

{:is_insert=>true, :rseg_id=>36, :undo_log=>{:page=>466, :offset=>272}}}],

:length=>8118,

:transaction_id=>10619,

:roll_pointer=>

{:is_insert=>true, :rseg_id=>36, :undo_log=>{:page=>466, :offset=>272}}}

...

从page dump的结果能看到，第一条记录溢出存储在另一个page(pageno=4)，溢出的字节数是8102，也就是全部放在独立的page里存储了，聚集索引中只保留了20字节的指针。

我们继续测试当表里有两个text列的情况

[root@yejr.me]> CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`c1` text NOT NULL,

`c2` text NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

[root@yejr.me]> insert into t1 select 1,repeat('a',100),repeat('a',8001);

[root@yejr.me]> insert into t1 select 2,repeat('a',100),repeat('a',8000);

用innblock工具解析

[root@yejr.me]# innblock innodb/t1.ibd 3 16

...

-----Total used rows:4 used rows list(phy):

(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2

(2) SUPREMUM record offset:112 heapno:1 n_owned 3,delflag:N minflag:0 rectype:3

(3) normal record offset:128 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0

(4) normal record offset:273 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0

...

注意到第一条记录的长度是 273-128=145字节，可见的确是做overflow处理了。

再用innodb_ruby工具解析整个page

[root@yejr.me]# innodb_space -s ibdata1 -T innodb/t1 -p 3 page-dump

...

{:format=>:compact,

:offset=>128,

:header=>

{:next=>273,

...

:key=>[{:name=>"id", :type=>"INT", :value=>1}],

:row=>

[{:name=>"c1",

:type=>"BLOB",

:value=>

"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},

{:name=>"c2",

:type=>"BLOB",

:value=>"",

:extern=>{:space_id=>215, :page_number=>4, :offset=>38, :length=>8001}}],

...

{:format=>:compact,

:offset=>273,

:header=>

{:next=>112,

...

:key=>[{:name=>"id", :type=>"INT", :value=>2}],

:row=>

[{:name=>"c1",

:type=>"BLOB",

:value=>

"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},

{:name=>"c2",

:type=>"BLOB",

:value=>

"a..."}],

:sys=>

[{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>10686},

{:name=>"DB_ROLL_PTR",

:type=>"ROLL_PTR",

:value=>

{:is_insert=>true, :rseg_id=>78, :undo_log=>{:page=>478, :offset=>272}}}],

:length=>8117,

:transaction_id=>10686,

:roll_pointer=>

{:is_insert=>true, :rseg_id=>78, :undo_log=>{:page=>478, :offset=>272}}}

从page dump的结果能看到，第一条记录溢出存储在另一个page(pageno=4)，溢出的字节数是8001，也是全部放在独立的page里存储了，聚集索引中只保留了20字节的指针。

好吧，测试案例就介绍到这里，更多的场景请自行测试。

P.S，如果想偷懒不自己测试，也可以看看我的 InnoDB Record Header消耗测试过程实录(文末提供链接)。

P.P.S，我不是源码级MySQL内核开发者，水平有限，文中难免有误之处，还请多指教。

Enjoy MySQL :)