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 :)
