[MySQL] InnoDB row format profiling _ MySQL

[MySQL] InnoDB row format analysis bitsCN.com

[MySQL] InnoDB row format analysis

Like most row-based databases, InnoDB stores records in the row format. It provides two formats: Compact and Redundant.

Compact
The Compact format is introduced only when MySQL5.0 is used. it is a new row format and is designed to efficiently store data, as shown below:



1) length list of variable-length fields. Placed in reverse order of the column. if the column length is less than 255 bytes, it is expressed as 1 byte. if the column length is greater than 255 bytes, it is expressed as 2 bytes, the maximum length of varchar in InnoDB is 2 bytes (this also explains why the maximum length of varchar is 65535, because 2 bytes are 16 bits, and the power of 2 is-1 ). Note that you do not need to record the length of a fixed-length field (such as char.
2) NULL flag. Indicates whether the column in the row is NULL, and 1 indicates NULL.
3) record header information. It occupies 5 bytes (40 bits). The meaning of each representative is shown in the following table:



4) the data in each column of the row is actually stored. note: NULL does not occupy storage in this part.
5) There are two hidden parts: transaction ID and rollback pointer, which are 6 bytes and 7 bytes respectively. if the table does not have a primary key defined, each row also automatically adds 6 bytes of ROWID.

Redundant
Redundant is the row record storage format of InnoDB before MySQL5.0, which is as follows:



1) field length offset list. It is also placed in reverse order of the column. when the column length is less than 255 bytes, it is expressed in 1 byte. if the column length is greater than 255 bytes, it is expressed in 2 bytes.
2) record header information. It occupies 6 bytes (48 bits). The meanings of each character are shown in the following table:



3) the last part is the actual storage of column data.
4) like Compact, there are two hidden parts that store the transaction ID and rollback pointer respectively. The size is 6 bytes and 7 bytes respectively. if the table does not have a primary key defined, each row also automatically adds 6 bytes of ROWID.

The biggest difference between Redundant and Compact is the processing of NULL values of the Char type. Redundant actually stores NULL values, occupying a fixed length space of the Char type, while Compact does not occupy space, this is one of the reasons Compact can keep more records. Note: For the varchar type, no NULL value is stored for both Compact and Redundant.

Compressed and Dynamic
The InnoDB Plugin introduces a new file format called Barracuda (the earlier Compact and Redundant are called Antelope). The record formats with two permissions are Compressed and Dynamic. I will not detail them here. if you are interested, you can study them on your own.

BitsCN.com