探究InnoDB数据页内部行的存储方式

实验数据

CREATE TABLE `ibd2_test` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

+----+-------+
| id | name  |
+----+-------+
|  1 | test1 | 
|  2 | test2 | 
|  3 | test3 | 
|  4 | test4 | 
|  5 | test5 | 
+----+-------+
5 rows in set (0.00 sec)

之后delete id为3的行，并继续插入4行数据，最终:

localhost.test>select * from ibd2_test;
+----+-------+
| id | name  |
+----+-------+
|  1 | test1 | 
|  2 | test2 | 
|  4 | test4 | 
|  5 | test5 | 
|  6 | test6 | 
|  7 | test7 | 
|  8 | test8 | 
|  9 | test9 | 
+----+-------+
8 rows in set (0.00 sec)

分析工具

自己python写的Innodb Extract

实验分析

首先回忆下MySQL源码中关于record格式的定义，文件rec0rem.c(77~104行)

/* PHYSICAL RECORD (NEW STYLE)
===========================

The physical record, which is the data type of all the records
found in index pages of the database, has the following format
(lower addresses and more significant bits inside a byte are below
represented on a higher text line):

| length of the last non-null variable-length field of data:
if the maximum length is 255, one byte; otherwise,
0xxxxxxx (one byte, length=0..127), or 1exxxxxxxxxxxxxx (two bytes,
length=128..16383, extern storage flag) |
...
| length of first variable-length field of data |
| SQL-null flags (1 bit per nullable field), padded to full bytes |
| 4 bits used to delete mark a record, and mark a predefined
minimum record in alphabetical order |
| 4 bits giving the number of records owned by this record
(this term is explained in page0page.h) |
| 13 bits giving the order number of this record in the
heap of the index page |
| 3 bits record type: 000=conventional, 001=node pointer (inside B-tree),
010=infimum, 011=supremum, 1xx=reserved |
| two bytes giving a relative pointer to the next record in the page |
ORIGIN of the record
| first field of data |
...
| last field of data |

画成图如下：

row_format

info bits的第三位表示该行是否已被删除，如果是则标记1，没有被删除则标记0，第四位表示该记录是否是预先被定义为最小的记录，如果是则标记为1
n_owned该记录拥有的记录数，指的是该记录所在页中page diectory所属slot中拥有的记录数
order索引堆中的顺序，伪记录首记录infimum这里为0，而伪记录最后一条记录spremum这里为1，也就是说真实记录从2开始。这里这个值代表的是物理记录的真实顺序，而非逻辑顺序，后续我们为此验证
record type表示记录的类型，数据行为0,节点指针值为1，伪记录首记录infimum值为2，伪记录最后一个记录supremum的值为3
next record offset下一条记录的相对offset，通过这个next record offset 我们可以遍历一个页中的所有记录。记录与记录之间通过链表的形式组织

深入剖析

step 1，我们首先看下原先删除Id为3的记录前:

[root@hebe211 ibd]#  python innodb_extract.py ibd2_test.ibd

infimum
row_id:000000000213,info_bits:0000,n_owned:0000,order:2(0000000000010),next offset:34(0000000000100010)
   1 test1 
row_id:000000000214,info_bits:0000,n_owned:0000,order:3(0000000000011),next offset:34(0000000000100010)
   2 test2 
row_id:000000000215,info_bits:0000,n_owned:0000,order:4(0000000000100),next offset:34(0000000000100010)
   3 test3 
row_id:000000000216,info_bits:0000,n_owned:0000,order:5(0000000000101),next offset:34(0000000000100010)
   4 test4 
row_id:000000000217,info_bits:0000,n_owned:0000,order:6(0000000000110),next offset:-150(1111111101101010)
   5 test5

首先,我们没有定义主键，所以系统会自动创建一个6字节的row_id作为隐藏主键，每一条记录record header的最后两个字节指向下一条记录row_id的起始offset，链表是按照聚簇索引组织起来的，也就说逻辑记录是按照聚簇索引的顺序链接起来。我们在看物理顺序是2->3->4->5->6,此时跟聚簇索引的顺序是完全一样的！(另外在我的工具中把伪记录的首记录infimum和尾记录supremum过滤了，这两条记录的order分别是0和1，这里不做详。)

step 2,我们将id为3(row_id为000000000215)的记录删除，再看变化

infimum
row_id:000000000213,info_bits:0000,n_owned:0000,order:2(0000000000010),next offset:34(0000000000100010)
   1 test1 
row_id:000000000214,info_bits:0000,n_owned:0000,order:3(0000000000011),next offset:68(0000000001000100)
   2 test2 
row_id:000000000216,info_bits:0000,n_owned:0000,order:5(0000000000101),next offset:34(0000000000100010)
   4 test4 
row_id:000000000217,info_bits:0000,n_owned:0000,order:6(0000000000110),next offset:-150(1111111101101010)
   5 test5

我们看到，row_id为000000000215的记录不见了，就是说在这个数据链表中被摘除了。此时记录的物理顺序也没有变:2->3->5->6，第二行row_id为000000000214的下一条记录的offset不再是34，而变成了68，指向的是row_id为000000000216的行。印证了前一句我说的id为3的记录是被从数据链表中'摘除'而不是删除。

step 3,我们继续插入4条数据之后再看

infimum
row_id:000000000213,info_bits:0000,n_owned:0000,order:2(0000000000010),next offset:34(0000000000100010)
   1 test1 
row_id:000000000214,info_bits:0000,n_owned:0000,order:3(0000000000011),next offset:68(0000000001000100)
   2 test2 
row_id:000000000216,info_bits:0000,n_owned:0000,order:5(0000000000101),next offset:34(0000000000100010)
   4 test4 
row_id:000000000217,info_bits:0000,n_owned:0100,order:6(0000000000110),next offset:-68(1111111110111100)
   5 test5 
row_id:000000000218,info_bits:0000,n_owned:0000,order:4(0000000000100),next offset:102(0000000001100110)
   6 test6 
row_id:000000000219,info_bits:0000,n_owned:0000,order:7(0000000000111),next offset:34(0000000000100010)
   7 test7 
row_id:00000000021a,info_bits:0000,n_owned:0000,order:8(0000000001000),next offset:34(0000000000100010)
   8 test8 
row_id:00000000021b,info_bits:0000,n_owned:0000,order:9(0000000001001),next offset:-252(1111111100000100)
   9 test9

此时数据链表中的物理顺序变为2->3->5->6->4->7->8->9,注意物理存储的顺序不再是根据聚簇索引顺序排序的顺序了！我们后插入的第一条row_id为000000000218的记录此时在堆中的排序变成4，同时row_id为000000000217的下一条记录的相对位置offset偏移量变成了负数(负数的存储方式以补码的形式存储)，并且-68就是刚刚被删除的row_id为000000000215的物理偏移量，那我们可以理解为被删除的空间重用了

step 4,我们再删除1条id为8(row_id00000000021a)的行

localhost.test>select * from ibd2_test;
+----+-------+
| id | name  |
+----+-------+
|  1 | test1 | 
|  2 | test2 | 
|  4 | test4 | 
|  5 | test5 | 
|  6 | test6 | 
|  7 | test7 | 
|  9 | test9 | 
+----+-------+

然后我们再观察，根据mysql源码里对于PAGE HEADER的定义：

/*          PAGE HEADER
            ===========

Index page header starts at the first offset left free by the FIL-module */

typedef byte        page_header_t;

#define PAGE_HEADER FSEG_PAGE_DATA  /* index page header starts at this
                offset */
/*-----------------------------*/
#define PAGE_N_DIR_SLOTS 0  /* number of slots in page directory */
#define PAGE_HEAP_TOP    2  /* pointer to record heap top */
#define PAGE_N_HEAP  4  /* number of records in the heap,
                bit 15=flag: new-style compact page format */
#define PAGE_FREE    6  /* pointer to start of page free record list */
#define PAGE_GARBAGE     8  /* number of bytes in deleted records */

PAGE_FREE和PAGE_GARBAGE分别定义可重用空间的指针和可重用空间的大小,我们打开debug信息，再看下物理行的变化

[root@hebe211 ibd]#  python innodb_extract.py ibd_test.ibd
PAGE_FREE pointer offset 330,PAGE_GARBAGE size 34
now row begin offset 99
infimum
now row begin offset 126
row_id:000000000213,info_bits:0000,n_owned:0000,order:2(0000000000010),next offset:34(0000000000100010)
   1 test1 
now row begin offset 160
row_id:000000000214,info_bits:0000,n_owned:0000,order:3(0000000000011),next offset:68(0000000001000100)
   2 test2 
now row begin offset 228
row_id:000000000216,info_bits:0000,n_owned:0000,order:5(0000000000101),next offset:34(0000000000100010)
   4 test4 
now row begin offset 262
row_id:000000000217,info_bits:0000,n_owned:0100,order:6(0000000000110),next offset:-68(1111111110111100)
   5 test5 
now row begin offset 194
row_id:000000000218,info_bits:0000,n_owned:0000,order:4(0000000000100),next offset:102(0000000001100110)
   6 test6 
now row begin offset 296
row_id:000000000219,info_bits:0000,n_owned:0000,order:7(0000000000111),next offset:68(0000000001000100)
   7 test7 
now row begin offset 364
row_id:00000000021b,info_bits:0000,n_owned:0000,order:9(0000000001001),next offset:-252(1111111100000100)
   9 test9

此时row_id为000000000219的下一行指向了row_id00000000021b,相对offset从34变为了68，跳过了刚才删除的row_id为00000000021a的行。此时在看PAGE_FREE指向的offset为330，PAGE_GARBAGE大小34个字节，等于row_id000000000219起始offset 296 + 34(刚才删除行的size)，也就是说刚才从数据链表被摘下的行被放入了可重用空间链表里去了，这个指针永远指向最新的被删除的行，如果有数据插入，这个可重用空间被重用，那么这行就从可重用空间链表里摘除，同时放入数据链表中

step 5 为了印证上面的想法，我们继续删除id为1(row_id为000000000213)的行

localhost.test>select * from ibd2_test;
+----+-------+
| id | name  |
+----+-------+
|  2 | test2 | 
|  4 | test4 | 
|  5 | test5 | 
|  6 | test6 | 
|  7 | test7 | 
|  9 | test9 | 
+----+-------+
6 rows in set (0.00 sec)

我们在看下可重用空间指针内容的变化

[root@hebe211 ibd]#  python innodb_extract.py ibd2_test.ibd
PAGE_FREE pointer offset 126,PAGE_GARBAGE size 68
now row begin offset 99
infimum
now row begin offset 160
row_id:000000000214,info_bits:0000,n_owned:0000,order:3(0000000000011),next offset:68(0000000001000100)
   2 test2 
now row begin offset 228
row_id:000000000216,info_bits:0000,n_owned:0000,order:5(0000000000101),next offset:34(0000000000100010)
   4 test4 
now row begin offset 262
row_id:000000000217,info_bits:0000,n_owned:0000,order:6(0000000000110),next offset:-68(1111111110111100)
   5 test5 
now row begin offset 194
row_id:000000000218,info_bits:0000,n_owned:0000,order:4(0000000000100),next offset:102(0000000001100110)
   6 test6 
now row begin offset 296
row_id:000000000219,info_bits:0000,n_owned:0000,order:7(0000000000111),next offset:68(0000000001000100)
   7 test7 
now row begin offset 364
row_id:00000000021b,info_bits:0000,n_owned:0000,order:9(0000000001001),next offset:-252(1111111100000100)
   9 test9

删除id为1的行之后，此时PAGE_FREE指针指向了位置为126的位置，此时可重用空间的大小变成了68字节。而此时伪记录的首记录infimum的下一条记录的指针指向了row_id为000000000214的行，而不再是row_id 000000000213的行，offset变为68，跳过了被删除的行。此时，我们看下，PAGE_FREE指向的offset为126，正是被删除的行(row_id为000000000213，offset为126)的起始位置，而可重用空间的大小从34字节变成了64字节。说明PAGE_FREE指针指向的是最新的被删除的行，而有新数据插入的时候，也是重用最后删除的行的空间，符合“后入先出”规律，类似于栈。

step 6,我们最后插入一条数据，看是否会重用row_id000000000213的行的空间，如果是的话，变验证了上面的想法

localhost.test>select * from ibd2_test;
+----+-------+
| id | name  |
+----+-------+
|  2 | test2 | 
|  4 | test4 | 
|  5 | test5 | 
|  6 | test6 | 
|  7 | test7 | 
|  9 | test9 | 
|  3 | testa | 
+----+-------+
7 rows in set (0.00 sec)


[root@hebe211 ibd]#  python innodb_extract.py ibd2_test.ibd
PAGE_FREE pointer offset 330,PAGE_GARBAGE size 34
now row begin offset 99
infimum
now row begin offset 160
row_id:000000000214,info_bits:0000,n_owned:0000,order:3(0000000000011),next offset:68(0000000001000100)
   2 test2 
now row begin offset 228
row_id:000000000216,info_bits:0000,n_owned:0000,order:5(0000000000101),next offset:34(0000000000100010)
   4 test4 
now row begin offset 262
row_id:000000000217,info_bits:0000,n_owned:0000,order:6(0000000000110),next offset:-68(1111111110111100)
   5 test5 
now row begin offset 194
row_id:000000000218,info_bits:0000,n_owned:0000,order:4(0000000000100),next offset:102(0000000001100110)
   6 test6 
now row begin offset 296
row_id:000000000219,info_bits:0000,n_owned:0000,order:7(0000000000111),next offset:68(0000000001000100)
   7 test7 
now row begin offset 364
row_id:00000000021b,info_bits:0000,n_owned:0000,order:9(0000000001001),next offset:-238(1111111100010010)
   9 test9 
now row begin offset 126
row_id:00000000021c,info_bits:0000,n_owned:0000,order:2(0000000000010),next offset:-14(1111111111110010)
   3 testa

我们看到插入id=3(row_id00000000021c)的行之后，PAGE_FREE指向的offset从126变回了330，可重用空间大小也变成了34字节，最新删除的行的空间从删除链中摘除，同时我们看到新插入的行order为2，也就是之前的删除的id=1(row_id000000000213)占用的空间,空间此处被新插入数据重用。

step5 到step6删除链表的变化总结如图：

reuse_list

最后，我们打开debug信息，分析一下现在删除链表存储的内容

[root@hebe211 ibd]#  python innodb_extract.py ibd2_test.ibd

PAGE_FREE pointer offset 330,PAGE_GARBAGE size 34
row_id:00000000021a,info_bits:0010,n_owned:0000,order:8(0000000001000),next offset:0(0000000000000000)

now row begin offset 99
infimum
now row begin offset 160
row_id:000000000214,info_bits:0000,n_owned:0000,order:3(0000000000011),next offset:68(0000000001000100)
   2 test2 
now row begin offset 228
row_id:000000000216,info_bits:0000,n_owned:0000,order:5(0000000000101),next offset:34(0000000000100010)
   4 test4 
now row begin offset 262
row_id:000000000217,info_bits:0000,n_owned:0000,order:6(0000000000110),next offset:-68(1111111110111100)
   5 test5 
now row begin offset 194
row_id:000000000218,info_bits:0000,n_owned:0000,order:4(0000000000100),next offset:102(0000000001100110)
   6 test6 
now row begin offset 296
row_id:000000000219,info_bits:0000,n_owned:0000,order:7(0000000000111),next offset:68(0000000001000100)
   7 test7 
now row begin offset 364
row_id:00000000021b,info_bits:0000,n_owned:0000,order:9(0000000001001),next offset:-238(1111111100010010)
   9 test9 
now row begin offset 126
row_id:00000000021c,info_bits:0000,n_owned:0000,order:2(0000000000010),next offset:-14(1111111111110010)
   3 testa

row_id:00000000021a,info_bits:0010,n_owned:0000,order:8(0000000001000),next offset:0(0000000000000000) now row begin offset 99

row_id00000000021a就是之前删除的Id=8的记录
==重点是这个info_bits:0010,第三位是deleted标志位，为1说明该行记录已被删除==
因为删除链只有这一条数据，所以next offset指向的下一条记录offset为0

总结

通过以上record header结合物理存储格式，我们看到有3个链表:逻辑记录，物理记录，删除记录

逻辑记录的排序是根据聚簇索引的顺序排序的，物理记录的顺序是行在堆中的顺序。当放生数据被删除之后又插入数据空间被重用的时候，物理记录的顺序与逻辑记录的顺序不再一致
删除一条记录时同时从逻辑记录链表里摘除，加入删除链表，删除链表指针总是指向最新被删除的记录的空间。当空间被重用，栈顶指向的空间从删除链表中移除，加入到逻辑记录链表
删除数据之后，如果该行记录还在删除链表里存在，理论来讲数据是可以恢复的。但是如果空间被重用了，数据将不可恢复

最后编辑于：2017.12.04 05:08:15

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 213,711评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,079评论 3赞 387
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 159,194评论 0赞 349
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,089评论 1赞 286
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,197评论 6赞 385
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,306评论 1赞 292
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,338评论 3赞 412
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,119评论 0赞 269
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,541评论 1赞 306
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,846评论 2赞 328
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,014评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,694评论 4赞 337
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,322评论 3赞 318
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,026评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,257评论 1赞 267
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,863评论 2赞 365
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,895评论 2赞 351

探究InnoDB数据页内部行的存储方式

探究InnoDB数据页内部行的存储方式

实验数据

分析工具

实验分析

深入剖析

总结

推荐阅读更多精彩内容