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

探究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个链表:逻辑记录，物理记录，删除记录

逻辑记录的排序是根据聚簇索引的顺序排序的，物理记录的顺序是行在堆中的顺序。当放生数据被删除之后又插入数据空间被重用的时候，物理记录的顺序与逻辑记录的顺序不再一致

删除一条记录时同时从逻辑记录链表里摘除，加入删除链表，删除链表指针总是指向最新被删除的记录的空间。当空间被重用，栈顶指向的空间从删除链表中移除，加入到逻辑记录链表

删除数据之后，如果该行记录还在删除链表里存在，理论来讲数据是可以恢复的。但是如果空间被重用了，数据将不可恢复