MySQL Internal-InnoDB Storage engine (row structure)

MySQL Internal-InnoDB Storage engine (row structure)

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 saved as [1, 3 (1 + 2), 7 (1 + 2 + 4)], and the list is in reverse order, the information of the dump Field Start Offsets 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 (Origin) is given, InnoDB follows the steps below to obtain records:

-- 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 Borland's TDUMP 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 Bytes
00 00 00 00 04 21 System Column #1
00 00 00 00 09 2A System Column #2
80 00 00 00 2D 00 84 System Column #3
50 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 Bytes
00 00 00 00 04 22 System Column #1
00 00 00 00 09 2B System Column #2
80 00 00 00 2D 00 84 System Column #3
51 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 Bytes
00 00 00 00 04 23 System Column #1
00 00 00 00 09 2C System Column #2
80 00 00 00 2D 00 84 System Column #3
52 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: [, 17, 15,13, 0c, 06]

-- "Extra Bytes"

Refer to "First Row" and "Extra Bytes" as [00 00 78 0D 02 BF]. Refer to "extra bytes" to read and skip the First 21-bit read (n_fields) and retrieve 10 bits, reads 7 bits [00001101] of the last three bytes of the third byte [000] And 0D [0000110] of the fourth byte. the resulting 6 is the number of fields (excluding Extra Bytes). The fourth byte 0D [00001101] Last bit: 1 indicates byte_offs_flag indicating that the offset is 1 byte, the final byte 02 BF points to the record of the next Row Second Row (System Column #1) (02BF is the address on the 0D42BF page). The next record points to System Column #1, 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.

MySQL InnoDB table-Basic BTree Data Structure

Optimization of the count (*) function in the InnoDB Storage engine of MySQL

MySQL InnoDB Storage engine lock mechanism Experiment

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration

This article permanently updates the link address: