MySQL Internal-innodb Storage engine (row structure)

three components of the InnoDB row store (description: F character = number of columns)

Names (name)	Sizes (size)
Field Start Offsets	(f*1) or (f*2) bytes
Extra Bytes	6 bytes
Field Contents	Depends on the content

1:field START Offsets

The position offset Information list for each field in the actual data store row (entry, the actual storage is not just the column, but also the additional information), which is calculated from the origin (origin) relative position and the next field. The offset information for each field in the row saved by the list is reversed, which means that the first field information in the row is at the end of the list.

For example: Suppose there are three columns, the length of the first column is 1 bytes, the second is 2 bytes, the third is 4 bytes, in this case, the offset information for saving three columns is [1,3 (1+2), 7 (1+2+4)], the list is reversed, the dump field Start offsets information should be [07,03,01].

There are two kinds of special complex situations:

1: The offset number may be one or two bytes, the maximum allowable length of a byte is 127, the highest bit is used to save the null, the "Extra Bytes" section describes whether the offset is one byte or two bytes.

2: The offset may have a flag message, the remaining byte space contains two segments, which refers to the specific content. (This may not be in the same page, refer to the following analysis)

When the offset is one byte:

1 bit = NULL

7 bit, actual offset information

When the offset is two bytes:

1 bit = NULL

1 bit = 0 content in the same page, = 1 content on different pages

bits = actual offset, 0 ~ 16383

2:extra BYTES

Extra Bytes is 6 bytes

Name	Size	Description
Info_bits:	??	??
()	1 bit	Not used
()	1 bit	Not used
Deleted_flag	1 bit	1: Delete flag bit (deleted)
Min_rec_flag	1 bit	1: Pre-defined minimum record
n_owned	4 bits	Number of records owned
Heap_no	Bits	Data page sequence number of the index in the heap block
N_fields	Ten bits	Number of fields in record 1 to 1023
1byte_offs_flag	1 bit	1:field Start offsets is a byte, otherwise two bytes
Next + Bits	+ Bits	Pointer to the next record (System Column #1)
Total	Bits	??

Total bit,6 bytes

If you need to read this stored record through bytes, the most critical need to read the Byte_offs_flag bit information in extra Bytes, you need to know that 1 means that the offset information is one byte, 0 means two bytes

If a relative origin (origin) is given,InnoDB获取记录开始遵循如下步骤：

--X = N_fields, this number equals the number defined in the field Start offsets list

--if Byte_offs_flag = 0,x = X * 2, each offset is represented by two bytes

--X = x + 6, fixed size extra bytes is 6 bytes

--the current position of the start position of the record minus X

(Refer to field CONTENTS)

3:field CONTENTS

The field Contents section includes all of the recorded data, which are stored sequentially in our predefined way.

The field and field do not have any marks, and the end of the record does not have any flags.

Instance:

--Create a table

CREATE TABLE T    (FIELD1 varchar (3), FIELD2 varchar (3), FIELD3 varchar (3))    Type=innodb;

What you need to know is that each row in the InnoDB table has 6 fields, not 3, because InnoDB automatically complements 3 columns ("System columns") before the stored content, which is the row ID (the row ID, which has no primary key defined). Transaction ID (transaction ID), Roll back pointer (rollback pointer).

--Add three data to the table

INSERT into T values (' PP ', ' pp ', ' pp ') and insert into t values (' Q ', ' Q ', ' Q '); INSERT into t values (' R ', null, NULL);

Run tool (Borland ' s tdump) to view binary transaction file information ( \mysql\data\ibdata1 )

Address Values in hexadecimal	Values in ASCII
0D4280: 00 00 2D 00 84 4F 4F 4F 4F 4F 4F 4F 4F 4F 19 17	..-..OOOOOOOOO..
0D4290: 15 13 0C 06 00 00 78 0D 02 BF 00 00 00 00 04 21	......x........!
0D42A0: 00 00 00 00 09 2A 80 00 00 00 2D 00 84 50 50 50	.....*....-..PPP
0D42B0: 50 50 50 16 15 14 13 0C 06 00 00 80 0D 02 E1 00	PPP.............
0D42C0: 00 00 00 04 22 00 00 00 00 09 2B 80 00 00 00 2D	....".....+....-
0D42D0: 00 84 51 51 51 94 94 14 13 0C 06 00 00 88 0D 00	..QQQ...........
0D42E0: 74 00 00 00 00 04 23 00 00 00 00 09 2C 80 00 00	t.....#.....,...
0D42F0: 00 2D 00 84 52 00 00 00 00 00 00 00 00 00 00 00	.-..R...........

Do some formatting, add tags:

0C Field Start Offsets/* First Row */00 xx 0D BF Extra Bytes00 (xx) System Column #100 00 2A system Column #280 (2D), System column #350 Field1 ' pp ', Field2 ' pp ', Field3 ' pp ' 16 15  0C Field Start offsets/Second Row */00 xx 0D E1 Extra Bytes00 (00) System Column #100 00 00  2B system column #280 xx 2D xx system column #351 Field1 ' q ' Field2 ' q ' Wuyi Field3 ' Q ' 94 94 (0C) Field Start Offsets/* Third Row */00 (0D) 2C system Bytes00, Extra Colu, XX/xx MN #280 xx 2D xx System Column #352 Field1 ' R '

--"Field Start offsets"

Referring to first Row, the 7 fields starting from extra bytes, size 6, 6, 7, 2, 2, 2, offset information points to the beginning of the next field, the number under 16 notation, 0c (6+6), 13 (6+6+7), 15 (6+6+7+2), 1 7 (6+6+7+2+2), 19 (6+6+7+2+2+2), reverse field Start offsets values are: [19,17,15,13,0c,06]

--"Extra Bytes"

Refer to first Row,extra bytes for [00 00 78 0D 02 BF]，参照EXTRA BYTES读取跳过头21 bit读(n_fields),取10个bit，读取第三个字节最后三个个bit [000]和第四个字节0D[00001101]的7个bit [0000110]，得出的6即为字段的数量（除去Extra Bytes），第四个字节0D[00001101]最后bit：1表示byte_offs_flag说明偏移量为1字节，最后的第5,6字节02 BF，指向下一行Second Row（System Column #1）的记录（02BF为0D42BF页内地址），下一记录指向了System Column #1，读取过程遵循EXTRA BYTES末的规则。

--the representation of a null column

Reference third row,field2 and FIELD3 are null because Byte_offs_flag is 1, so in field Start offsets [94 94 0C 06] Each read 1 bytes can represent the offset information of the field, The highest bit of this byte is a null token, and 14 13 represents the FIELD1 value of 1 bytes [52] of ' R ', 94 14 represents the FIELD2 value of 0 bytes null (94 the highest bit is 1 for null, the remaining 7 bits are 14), and 94 94 represents a 0-byte FIELD3 value of NULL.

MySQL Internal-innodb Storage engine (row structure)