Some excerpts from the booklet “How MySQL works”.

InnoDB record structure

Row format

  • InnoDB storage engine supports four row formats: Compact, Redundant, Dynamic, and Compressed

  • You can specify the row format through the ROW_FORMAT variable in the statement that creates or modifies the table

    CREATE TABLETable name (column information) ROW_FORMAT= Row format nameALTER TABLETable name ROW_FORMAT= Row format nameCopy the code

Compact row format

Redundant row format

Dynamic and Compressed formats

  • These two row formats are similar to the COMPACT row format, except that all bytes are stored in other pages, and only the addresses of other pages are stored where the real data is recorded

  • The Compressed row format uses a compression algorithm to compress the page

Line overflow

  • MySQL supports variable-length fields such as VARCHAR(M), VARBINARY(M), various TEXT types, and various BLOB types

  • In Compact and Redundant row formats, if the data of the variable length field is very large, only the first 768 bytes of the column and an address pointing to other pages are stored in the real data of the record, and the rest of the data is stored in the overflow page


InnoDB index page structure

File Header

Page Header

Infimum + Supremum

  • Infimum + Supremum are two virtual pseudo-records representing the minimum and maximum records in the page, each with a fixed 26 bytes

  • The records of Infimum + Supremum and user Records form a single linked list:

    • Specifies that the next record in Infimum is the user record with the smallest primary key on this page

    • The next record for the user with the largest primary key on this page is Supremum

Page Directory

  • Records are sequenced in a single linked list by primary key in the page. To quickly find records by primary key, InnoDB groups the records:

    • Initially, Infimum and Supremum fell into two groups

    • The N_OWNED attribute in the header of the last record for each group indicates how many records the group owns

    • The address offsets of the last record of each group are extracted separately and stored sequentially in the Page Directory. These address offsets in the Page Directory are called slots.

    • Each time a record is inserted, the slot in the Page Directory where the primary key is greater than and least different from the primary key of the current record is found, and the n_OWNED value of the record corresponding to that slot is increased by one until the number of records in the group is equal to eight

    • When the number of records in a group is equal to 8 and a record is inserted, the records in the group are split into two groups and a new slot is added in the Page Directory to record the new group

  • When searching for a record based on the primary key value, the previous group of the record’s group is found through the dichotomy method, and then the target record is found by traversing the single linked list

File Trailer

  • File Trailer is used to detect the integrity of the page. It consists of 8 bytes and can be divided into two parts:

    • The first four bytes represent the checksum of the page

    • The last four bytes represent the LSN of the log sequence where the page was last modified