MySQL Internal, mysqlinternal

MySQL Internal, mysqlinternal
Three parts of InnoDB Row Storage (Description: Number of columns in the F branch table)

Name)	Size)
Field Start Offsets	(F * 1) or (F * 2) bytes
Extra Bytes	6 bytes
Field Contents	Depends on Content

 

 

 

 

1: FIELD START OFFSETS

The position offset information list of each field (entry, which is not only a column but also additional information) in the actual data storage line. This position is determined by the Origin) the relative position and the next field are calculated. The offset information of each field in the row saved in this list is in reverse order, that is, the first field information in the row is at the end of this list.

For example, suppose there are three columns. The first column is 1 byte, the second column is 2 bytes, and the third column is 4 bytes. In this case, the offset information of the three columns is [1, 3 (1 + 2), 7 (1 + 2 + 4)], and the list is in reverse order. The dump Field Start Offsets information should be [07,03,01].

There are two special complexities:

1: The offset number may be one or two bytes. A single byte can contain up to 127 characters, and the highest bit is used to save whether it is NULL, the "Extra Bytes" section shows whether the offset is one or two Bytes.

2: The offset may have a flag information, and the remaining byte space contains two segments, which refer to the specific content. (These contents may not be on the same page. Refer to the analysis below)

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 in different pages

14 bits = actual offset, 0 ~ 16383

 

2: EXTRA BYTES

Extra Bytes is 6 Bytes

Name	Size	Description
Info_bits:	??	??
()	1 bit	Unused
()	1 bit	Unused
Deleted_flag	1 bit	1: delete a flag (Deleted)
Min_rec_flag	1 bit	1: predefined minimum record
N_owned	4 bits	Number of records owned
Heap_no	13 bits	Serial number of the data page indexed in the heap Block
N_fields	10 bits	Number of fields in the record: 1 to 1023
1byte_offs_flag	1 bit	1: Field Start Offsets is a byte; otherwise, it is two bytes.
Next 16 bits	16 bits	Pointer to the next record (System Column #1)
TOTAL	48 bits	??

 

 

 

 

 

 

 

 

 

A total of 48 bits, 6 bytes

If you need to read the stored records in Bytes, the most important thing is to read the byte_offs_flag bit information in Extra Bytes. You need to know that 1 indicates that the offset information is one byte, and 0 indicates two Bytes.

If a relative Origin is given ),To obtain records from InnoDB, follow these steps:

-- X = n_fields. This number is equal to 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. The 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 contains all the recorded data. These fields are stored in sequence in a predefined manner.

Fields and fields are not marked, and the end of the record is not marked.

Instance:

-- Create a table

CREATE TABLE T    (FIELD1 VARCHAR(3), FIELD2 VARCHAR(3), FIELD3 VARCHAR(3))    Type=InnoDB;

You need to know that each row in the InnoDB table contains six fields, not three, because InnoDB automatically adds three columns ("system columns") before the stored content "), these columns are respectively row ID (the table has no primary key defined), transaction ID (transaction ID), and rollback pointer ).

-- Add three data entries to the table

INSERT INTO T VALUES ('PP', 'PP', 'PP');INSERT INTO T VALUES ('Q', 'Q', 'Q');INSERT INTO T VALUES ('R', NULL, NULL);

Run the Borland's TDUMP tool to view the 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...........

 

 

 

 

 

 

 

 

 

 

 

 

 

Perform the following format processing and add the tag:

19 17 15 13 0C 06 Field Start Offsets /* First Row */00 00 78 0D 02 BF Extra Bytes00 00 00 00 04 21 System Column #100 00 00 00 09 2A System Column #280 00 00 00 2D 00 84 System Column #350 50 Field1 'PP'50 50 Field2 'PP'50 50 Field3 'PP'16 15 14 13 0C 06 Field Start Offsets /* Second Row */00 00 80 0D 02 E1 Extra Bytes00 00 00 00 04 22 System Column #100 00 00 00 09 2B System Column #280 00 00 00 2D 00 84 System Column #351 Field1 'Q'51 Field2 'Q'51 Field3 'Q'94 94 14 13 0C 06 Field Start Offsets /* Third Row */00 00 88 0D 00 74 Extra Bytes00 00 00 00 04 23 System Column #100 00 00 00 09 2C System Column #280 00 00 00 2D 00 84 System Column #352 Field1 'R'

-- "Field Start Offsets"

For details, refer to the First Row. The size of the seven fields starting with Extra Bytes is 6, 6, 7, 2, 2, and 2 respectively. The offset information points to the starting position of the next field, in hexadecimal notation, the numbers 06, 0c (6 + 6), 13 (6 + 6 + 7), 15 (6 + 6 + 7 + 2 ), 17 (6 + 6 + 7 + 2 + 2), 19 (6 + 6 + 7 + 2 + 2 + 2), Field Start Offsets in reverse order are :[19,17,15,13,0c,06]

-- "Extra Bytes"

Refer to the First Row. The Extra Bytes parameter is [00 00 78 0D 02 BF], refer to extra bytes read to skip the header 21 bit read (n_fields), get 10 bits, read the third byte the last three individual bits [000] and7 bits of the fourth byte 0D [00001101] [0000110], the resulting 6 is the number of fields (excluding Extra Bytes), and the fourth byte 0D [00001101] the last bit: 1 indicates byte_offs_flag indicating that the offset is 1 byte, And the last 5 to 6 bytes02 BF, pointing to the record of the next Row Second Row (System Column #1 (02BF isAddress on the 0D42BF page), The next record points to System Column #1, and the read process follows the rules at the end of extra bytes.

-- Expression of the NULL Column

If Third Row, FIELD2, and FIELD3 are NULL, because byte_offs_flag is 1, in Field Start Offsets, [94 94 14 13 0C 06] each time one byte is read, it indicates the offset of the Field. The highest bit of this byte is the NULL mark, 14 13 indicates the FIELD1 value of 1 byte [52] 'R', 94 14 indicates that the FIELD2 value of 0 byte is NULL (the highest bits of 94 is 1 indicates NULL, and the remaining 7 bits are 14 ), 94 94 indicates that the FIELD3 value of 0 bytes is NULL.