Analysis of MySQL InnoDB line format

InnoDB, like most line databases, records stored in row format, which provides two formats: compact and redundant

Compact

The compact format is introduced in MySQL5.0, a new line format designed to efficiently store data, as shown in the following schematic:

1 variable length list of field lengths. Placed in reverse order of columns, when the column length is less than 255 bytes, in 1 bytes, if greater than 255 bytes, in 2 bytes, up to 2 bytes (This also explains how the maximum length of the varchar in InnoDB is 65535, because 2 bytes are 16 digits, 2 is 16.1). Note that for fixed-length fields, such as Char, there is no need to record their length.

2) NULL flag bit. Indicates whether the column in the row is null,1 to indicate null.

3) record header information. Fixed occupies 5 bytes (40 bits), each representing the meaning shown in the following table:

4 finally is the data that is actually stored in each column of the row, note: null does not occupy storage in this section.

5 In addition, there are two hidden parts, the transaction ID and rollback pointer, respectively, size 6 bytes and 7 bytes, if the table does not define a primary key, each row will automatically increase 6 bytes of rowid.

Redundant

Redundant is the row record storage format for MySQL5.0 before InnoDB, and its schematic diagram is as follows:

1 field length offset list. The same column is placed in reverse order, when the column length is less than 255 bytes, in 1 bytes, if greater than 255 bytes, in 2 bytes.

2) record header information. Fixed occupies 6 bytes (48 bits), each meaning is shown in the following table:

3 The last part is the actual storage of individual column data.

4 and the compact, there are two hidden parts, respectively, the transaction ID and rollback pointer, the size of 6 bytes and 7 bytes, if the table does not define a primary key, each row will automatically increase 6 bytes of rowid.

The biggest difference between the redundant and the compact is the processing of null values for char types, which redundant actually store null values, occupy the char type fixed-length space, and the compact does not occupy space, which is one of the reasons why the compact can be more recorded. Note: For the varchar type, no null value is stored in either the compact or the redundant.

Compressed and dynamic

InnoDB Plugin introduces a new file format called Barracuda (the previous compact and redundant known as Antelope), with two permissions for record formats compressed and dynamic. This is not a detailed description of them, interested in children's shoes can be studied under their own.