##dynamic存储模式验证
数据预置
创建2张表，都包含一个长varchar字段，其中一个行总长度为8k，另一个总长度为60k，验证下2中不同表的存储方式
create table t1(id int primary key,col varchar(2047)) DEFAULT CHARSET=utf8mb4;
create table t2(id int,col varchar(15359)) DEFAULT CHARSET=utf8mb4;
各往每张表中插入一条数据
mysql> insert into t1 values (1,repeat('a',2047)),(2,repeat('a',2047)),(3,repeat('a',2047)),(4,repeat('a',2047)),(5,repeat('a',2047));
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values (1,repeat('a',15359)),(2,repeat('a',15359)),(3,repeat('a',15359)),(4,repeat('a',15359)),(5,repeat('a',15359));
Query OK, 1 row affected (0.01 sec)
查看页面信息
mysql> select table_name,page_number, page_type, number_records, data_size from information_schema.INNODB_BUFFER_PAGE where table_name like '%test%';
+-------------+-------------+-----------+----------------+-----------+
| table_name | page_number | page_type | number_records | data_size |
+-------------+-------------+-----------+----------------+-----------+
| `test`.`t2` | 3 | INDEX | 5 | 255 |
| `test`.`t3` | 3 | INDEX | 1 | 2078 |
| `test`.`t1` | 3 | INDEX | 5 | 10360 |
+-------------+-------------+-----------+----------------+-----------+
3 rows in set (0.11 sec)
都只有一个页存了5条数据，看到data_size的大小，t2表占用非常小，t2表是如何存储的，对ibd文件进行解析得到如下结果：
[ltdb1@db02 analysisIBD]$ python py_innodb_page_info.py ../data/data/test/t1.ibd -v
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 6:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1
[ltdb1@db02 analysisIBD]$ python py_innodb_page_info.py ../data/data/test/t2.ibd -v
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0000>
page offset 00000004, page type <Uncompressed BLOB Page>
page offset 00000005, page type <Uncompressed BLOB Page>
page offset 00000006, page type <Uncompressed BLOB Page>
page offset 00000007, page type <Uncompressed BLOB Page>
page offset 00000008, page type <Uncompressed BLOB Page>
Total number of page: 9:
Insert Buffer Bitmap: 1
Uncompressed BLOB Page: 5
File Space Header: 1
B-tree Node: 1
File Segment inode: 1
ibd文件解析
可以发现t1表只有一个页存数据，而t2表除了一个b+tree页之外还有5个Uncompressed BLOB Page。可见对t2表而言，varchar数据并非存在b+tree上，而是存在溢出页上，为印证判断，对ibd文件进行详细解析。
t1表ibd文件解析
[ltdb1@db02 test]$ hexdump t1.ibd
#page 0
00000000 47 f2 49 ee 00 00 00 00 00 00 00 00 00 00 00 00
00000010 00 00 00 15 a0 60 73 c9 00 08 00 00 00 00 00 00
00000020 00 00 00 00 00 84 00 00 00 84 00 00 00 00 00 00
00000030 00 06 00 00 00 40 00 00 00 21 00 00 00 04 00 00
00000040 00 00 ff ff ff ff 00 00 ff ff ff ff 00 00 00 00
00000050 00 01 00 00 00 00 00 9e 00 00 00 00 00 9e 00 00
00000060 00 00 ff ff ff ff 00 00 ff ff ff ff 00 00 00 00
00000070 00 00 00 00 00 03 00 00 00 00 ff ff ff ff 00 00
00000080 ff ff ff ff 00 00 00 00 00 01 00 00 00 02 00 26
00000090 00 00 00 02 00 26 00 00 00 00 00 00 00 00 ff ff
000000a0 ff ff 00 00 ff ff ff ff 00 00 00 00 00 02 aa ff
000000b0 ff ff ff ff ff ff ff ff ff ff ff ff ff ff 00 00
000000c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
*
00003ff0 00 00 00 00 00 00 00 00 47 f2 49 ee a0 60 73 c9
#page 1
00004000 de e7 72 90 00 00 00 01 00 00 00 00 00 00 00 00
00004010 00 00 00 15 a0 60 66 93 00 05 00 00 00 00 00 00
00004020 00 00 00 00 00 84 00 00 00 00 00 00 00 00 00 00
00004030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
*
00007ff0 00 00 00 00 00 00 00 00 de e7 72 90 a0 60 66 93
#page 2
00008000 18 64 7f a7 00 00 00 02 00 00 00 00 00 00 00 00
00008010 00 00 00 15 a0 60 73 c9 00 03 00 00 00 00 00 00
00008020 00 00 00 00 00 84 ff ff ff ff 00 00 ff ff ff ff
00008030 00 00 00 00 00 00 00 00 00 01 00 00 00 00 00 00
00008040 00 00 ff ff ff ff 00 00 ff ff ff ff 00 00 00 00
*
00008060 00 00 ff ff ff ff 00 00 ff ff ff ff 00 00 05 d6
00008070 69 d2 00 00 00 03 ff ff ff ff ff ff ff ff ff ff
00008080 ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff
*
000080f0 ff ff 00 00 00 00 00 00 00 02 00 00 00 00 00 00
00008100 00 00 ff ff ff ff 00 00 ff ff ff ff 00 00 00 00
*
00008120 00 00 ff ff ff ff 00 00 ff ff ff ff 00 00 05 d6
00008130 69 d2 ff ff ff ff ff ff ff ff ff ff ff ff ff ff
00008140 ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff
*
000081b0 ff ff 00 00 00 00 00 00 00 00 00 00 00 00 00 00
000081c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
*
0000bff0 00 00 00 00 00 00 00 00 18 64 7f a7 a0 60 73 c9
#page 3
0000c000 75 6b 33 6f 00 00 00 03 ff ff ff ff ff ff ff ff
#页标：00 00 00 03(3)
#前一个页：ff ff ff ff（只有一个页）
#后一个页：ff ff ff ff（只有一个页）
0000c010 00 00 00 15 a0 60 ab ef 45 bf 00 00 00 00 00 00
#LSN:00 00 00 15 a0 60 ab ef
#页面类型：45 bf(B+树叶节点)
0000c020 00 00 00 00 00 84
#space id：00 00 00 84
#页头，占用38个字节
00 02 28 f0 80 07 00 00 00 00
#槽位数：00 02(2)
#空闲开始位置的偏移量:28 f0(第28f行的第0个位置)，c00+28f=E8F,即第0000e120行的第4个字节开始就没有数据
#记录数：8007-8002(为什么初始值为2，因为有2个伪记录行，supremum和infinimum)=5
#可重用空间首地址和删除记录字节数：00 00 00 00，无删除记录
0000c030 20 e0 00 02 00 04 00 05 00 00 00 00 00 00 00 00
#页最后插入位置的偏移量：20 e0，c000+20e0=e0e0，最后一条数据插入位置
#插入方向：00 02，向右
#连续插入的记录数：00 04，插入5条记录，值为4
#行记录数：00 05（5条）
0000c040 00 00 00 00 00 00 00 00 00 cc 00 00 00 84 00 00
#层级0表示叶子节点00 00
#index id：00 00 00 00 00 00 00 cc
0000c050 00 02 00 f2 00 00 00 84 00 00 00 02 00 32
#page header：56字节
01 00
0000c060 02 00 1d 69 6e 66 69 6d 75 6d 00 06 00 0b 00 00
#recprd header:01 00 02 00 1d,下个位置的偏移量，00 1d，位置为0x0063(固定)+0x001d=0x0080
#infimum：69 6e 66 69 6d 75 6d 00
0000c070 73 75 70 72 65 6d 75 6d ff 87 00 00 00 10 08 18
#recprd header:06 00 0b 00 00
#supremum:73 75 70 72 65 6d 75 6d
#ff 87，变长字段长度，8(7ff),为啥有个8不理解
#00，null标志位
#record header：00 00 18 08 18，下条记录为c078+818=c890，下一条数据开始位置
0000c080 80 00 00 01 00 00 08 18 d2 b1 f8 00 00 02 a4 01
#数据开始位置:80 00 00 01,有主键则为主键为1(if data start with 8,无符号型)，否则为6位的rowid
#事务id:00 00 08 18 d2 b1
#roll pointer：f8 00 00 02 a4 01 10
0000c090 10
61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
0000c0a0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61*
#非主键值：c890-c0a0+15=2032+15=2047a
0000c890 ff 87 00 00 00 18 08 18
#单条数据长度为c898-c080=2072，总数据长度2072*5=10360，对应上文的查询结果
#长度计算：header(8)+int(4)+thread(6)+roll_pointer(7)+varchar(2047)=2072
80 00 00 02 00 00 08 18
0000c8a0 d2 b2 f9 00 00 02 a1 01 10 61 61 61 61 61 61 61
0000c8b0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
*
0000d0a0 61 61 61 61 61 61 61 61 ff 87 00 00 00 20 08 18
0000d0b0 80 00 00 03 00 00 08 18 d2 b7 fc 00 00 01 1c 01
0000d0c0 10 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
0000d0d0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
*
0000d8c0 ff 87 00 00 00 28 08 18 80 00 00 04 00 00 08 18
0000d8d0 d2 b8 fd 00 00 02 a0 01 10 61 61 61 61 61 61 61
0000d8e0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
*
0000e0d0 61 61 61 61 61 61 61 61 ff 87 00 00 00 30 df 90
0000e0e0 80 00 00 05 00 00 08 18 d2 b9 fe 00 00 02 a8 01
0000e0f0 10 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
0000e100 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61#*
0000e8f0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
#over
*
0000fff0 00 00 00 00 00 70 00 63 75 6b 33 6f a0 60 ab ef
#PageDictionary共有2个槽位，从fff8开始往前，即0063(最初行相对位置)和0070两个(最后一行的相对位置)，即infimum和supremum位置
#checksum：75 6b 33 6f，与文件头一致
#a0 60 ab ef与lsn后四个值相等
#page 4，5
00010000 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
*
00018000
t2表ibd文件解析
0000c070 73 75 70 72 65 6d 75 6d 14 c0 00 00 00 10 00 33 |supremum.......3|
0000c080 00 00 00 00 02 0b 00 00 08 18 d2 c5 a5 00 00 02 |................|
0000c090 aa 01 10 80 00 00 01 00 00 00 82 00 00 00 04 00 |................|
0000c0a0 00 00 26 00 00 00 00 00 00 3b ff 14 c0 00 00 00 |..&......;......|
#record header：14 c0 00 00 00 10 00 33(下一条数据偏移量0033[51],可作为单条记录总长度)
#00 00 00 00 02 0b六位rowid
#事务id:00 00 08 18 d2 c5
#roll pointer：a5 00 00 02 aa 01 10
#int值：80 00 00 01
#varchar地址：00 00 00 82 00 00 00 04 00 00 00 26 00 00 00 00 00 00 3b ff，00 00 00 04(页标)
现在再来看上面查询的data_size，用如上结果来计算一下：
| tbn | records | rh | rp | trxid | rowid | int | varchar | single | total |
|---|---|---|---|---|---|---|---|---|---|
| t1 | 5 | 8 | 7 | 6 | 0 | 4 | 2047 | 2072 | 10360 |
| t2 | 5 | 8 | 7 | 6 | 6 | 4 | 20 | 51 | 255 |
可以看到varchar只占到了20个字节，详细ibd分析如下，对应上篇中对行格式为 时的描述，变长列为完全页外存储，聚集索引记录包含一个20字节的指针指向溢出页，印证了结果。
附表1：页面类型对照表
| 名称 | 十六进制 | 解释 |
|---|---|---|
| FIL_PAGE_INDEX | 0x45BF | B+树叶节点 |
| FIL_PAGE_UNDO_LOGO | 0x0002 | UNDO LOG页 |
| FIL_PAGE_INODE | 0x0003 | 索引节点 |
| FIL_PAGE_IBUF_FREE_LIST | 0x0004 | Insert Buffer空闲列表 |
| FIL_PAGE_TYPE_ALLOCATED | 0x0000 | 该页的最新分配 |
| FIL_PAGE_IBUF_BITMAP | 0x0005 | Insert Buffer位图 |
| FIL_PAGE_TYPE_SYS | 0x0006 | 系统页 |
| FIL_PAGE_TYPE_TRX_SYS | 0x0007 | 事务系统数据 |
| FIL_PAGE_TYPE_FSP_HDR | 0x0008 | FILE SPACE HEADER |
| FIL_PAGE_TYPE_XDES | 0x0009 | 扩展描述页 |
| FIL_PAGE_TYPE_BLOB | 0x000A | BLOB页 |
