"MySQL Series" InnoDB row record storage structure

Preface

When we usually insert data into a MySQL database table, the actual data is stored on the disk in the format of row records. In this article, we will come to understand the row record format of MySQL in detail. It is helpful to understand the format of row records. Later, we will understand how MySQL quickly locates travel records in the page, as well as MySQL's version control chain, transaction isolation level, etc. The row record format is the basis of many MySQL core knowledge.

InnoDB row record type

There are four types of row formats provided in MySQL: Compact , Redundant , Dynamic , Compressed .

You can specify the format of the row records when you create a table or modify a table
create table table name row_format=row format name
alter table table name row_format=row format name

Just know, don’t need to remember, basically can’t use

Compact row format

Among the four types of row formats, we mainly study the Compact format, and the row records of other formats are similar;

From the figure we can see that the row record is mainly composed of 4 parts: variable length field length, Null value list, row record header information and column real data

Variable length field length list

There are many variable-length data types in MySQL (varchar, text, etc.). MySQL needs to know the actual length of these data, so that the data of the corresponding column can be correctly extracted from the real data. Therefore, the variable-length field is composed of two parts:

• The length of the real data
• Real data bytes

The length of each variable-length field is either 1 byte or 2 bytes, which determines that the maximum number of bytes in each field is 65535;

• If the character type is gbk, each character occupies at most 2 bytes, and the maximum length cannot exceed 32766;
• If the character type is utf8, each character occupies at most 3 bytes, and the maximum length cannot exceed 21845.

So when to use 1 byte and when to use 2 bytes?

Three variables need to be defined here: w, m, l

1. If the character set used is utf8mb4 and the number of bytes occupied by each character is 4 bytes, then w=4; if the character type is utf8 and each character occupies at most 3 bytes, then w=3; so w Indicates the number of bytes occupied by each character in the character set
2. varchr(m), where m represents the length of the defined character
3. l Indicates the number of bytes occupied by the real data of the field

When m*w <= 255 ; indicates that the maximum length defined by the field will not exceed 1 byte, then the length of the field is represented by 1 byte

When m*w > 255 && l<=127 ; indicates that the length of the field definition may exceed 1 byte, but the current actual length is less than 127, which can be represented by 1 byte

When m*w > 255 && l>127 ; Use 2 bytes to indicate the length of the field

Thinking: Why is the value 127 compared to l?
When the variable-length field we define may be greater than 255 (that is, more than one byte), how can MySQL know the completed field length of the field in the currently read byte or half the field length of the field, in order to solve this problem , MySQL uses the first bit of 1 byte. When the first bit is 0, it means that the current length is 1 byte. When the first bit is 0, the current length is 2 bytes; because the first bit of 1 byte is occupied, the remaining 7 bits can represent The maximum value is 127

Variable-length fields will not be stored as the length of the Null column; secondly, the length of the variable-length field is not necessarily required in the row record. If the variable-length field is not defined in the row record or the variable-length field is Null, then it will not There is a variable length field length part

The number of bytes occupied by the variable length field is stored in reverse order

Null value list

Certain columns in a record may usually be allowed to be null, so the Compact row format manages these allowed to be null in a unified manner;

1. First, count which columns defined in the table are allowed to be null
2. If none of the fields in the table can be empty, then there is no null value list; if there are fields that allow null, then each field corresponds to a binary bit according to the order of the fields. When the binary bit is 1, it means the column The value is empty; when the binary bit is 0, it means that the column value is not empty
3. The list of Null values must have an integer number of bytes to represent, so use 0 to complement the bits that are not occupied

Header information

The header information mainly contains 6 fields, 5 of which are also frequently asked in interviews. For the convenience of memory, we correspond the 5 fields to the 5 fingers of the hand:

• n_owned (thumb): A data page will be divided into many groups, the last record of each group has this field as 1, and the other records have this field as 0, just like the big brother of all the records in the group; (corresponding to the thumb)
• deleted_flag (index finger): mark that the record is deleted; when the record is deleted, it will not be actually deleted, but will be marked with this field, and all deleted records will be connected using a linked list. Future articles will continue to talk about this field . (Imagine whether you usually pick your nose with your index finger)
• heap_no (middle finger): Indicates the relative position of the current record in the data page (MySQL uses this field to indicate the record position, which can correspond to the middle finger and cannot be described)

• record_type (ring finger): Indicates which type of record the current record belongs to. (The ring finger is used to carry a ring, which is related to classification. People can be divided into married and unmarried)

  1. 0 means normal record
  2. 1 means directory entry records, data records in non-leaf nodes in the index are all 1
  3. 2 means infrmum records, there will be at least two records in each data page, the smallest record of which is record_type=2
  4. 3 means Supremum records, there will be at least two records in each data page, the largest record of which is record_type=3
• next_record (little thumb): Store the relative position of the next record (when counting, the little thumb of the left hand should change to the right hand after counting, and the meaning type expressed by next_record)

The last field min_rec_flag: the minimum directory entry record of each non-leaf node in the B+ tree This field is 1; this field is less important than the other 5 fields and will not affect the understanding of the B+ tree index

Hidden column

In addition to user-defined data columns, MySQL will also generate 3 hidden columns for each row of records

• row_id: row ID, the unique identifier of the record; when the user defines the primary key field in the table, the user-defined primary key is preferred. If not, it will look up whether there is a unique index that is not defined as null, and if so, use the column as the primary key , If there is no MySQL, it will generate a row_id hidden column as the primary key
• trx_id: The ID of the transaction; this field is essential for achieving consistent views and transaction isolation levels, which will be explained in detail later
• roll_pointer: Rollback pointer, which points to the previous version number of the record. MySQL's MVCC is mainly implemented through this field.

Overflow column

All rows and records in MySQL will be stored in data pages. The size of each data page is 16KB, which is 16384 bytes; we mentioned earlier that the length of a variable-length field can be represented by two bytes, so The maximum length of a column can be 65535. When this extreme situation is encountered, a data page cannot store this record.

The compact row format handles this situation by recording part of the column data (768 bytes) at the real data, and other extra data will be stored in a new data page (overflow page), and then in the record Use 20 bytes to store the address of these data pages

Linked list used between overflow page and overflow page

Other line record formats:

Redundant : format before MySQL5.0, just ignore it

Dynamic , Compressed and Compact very similar, but there are some differences in the handling of overflow columns, they will only use 20 bytes in the real data column to store the address of the overflow page

Interview questions

• For fields defined by char(M), will the length of the field be recorded in the length list of variable-length fields?

Welcome everyone to leave a message in the comment area to discuss

---

Finally (point attention, don’t get lost)

There may be more or less deficiencies and mistakes in the article. If you have suggestions or comments, you are welcome to comment and exchange.

Finally, is not easy, please don’t use me , I hope my friends can follow , because these are all the sources of motivation for my sharing🙏

IDEA plugins commonly used by programmers: https://github.com/silently9527/Toolkit
Original link: https://silently9527.cn/?p=62