PageStructure

Ref: https://developpaper.com/mysql-operation-principle-data-page/

According to MySQL technology insider: InnoDB storage engine (Second Edition)Nuggets brochure “how MySQL works: understanding MySQL from the root”A summary of the two books
Article referenceTaobao database kernel group monthly report
The figures in this article are taken from inside MySQL Technology: InnoDB storage engine (Second Edition)

summary

In the previous article introducing the table, I briefly introducedData page, the text about the data page is not clear at all, so a separate article is opened to introduce the data page.
In the final analysis, all operations on the database are operations on the index page. The common page types include data page, undo page, system page, transaction data page, insert buffer bitmap page, etc. our article only introduces the data page.

page structure

The data page consists of seven parts: data page file header, data page header, maximum and minimum records, user records, free space, data directory and data page tail. The data page is simply divided into two parts. One part stores the data records, which are connected by the recorded pointer according to the size of the records, and the other part stores the directory of the data page.

File header

File header is mainly used to store table space related information. Its components are as follows:

• FIL PAGE SPACE OR Chksum: mainly used to store the checksum of data pages
• FIL PAGE Offset: this corresponds to the page number of the data page, and each table space starts from 0

• FIL PAGE ARCH LOG NO OR SPACE_ ID: the current version is used to store spaceid

  Data page header

  Page header is mainly used to store meta information of data pages. Its components are as follows:
  

• PAGE N DIR_ Slots: used to store the number of data page entries. A new initial data page will have two records, pointing to the maximum and minimum records respectively. In a non empty data page, the first record always points to the minimum record and the last one always points to the maximum record

• PAGE HEAP Top: refers to the starting address of the free space in the data page, because there is page Free record exists, less than page HEAP_ The top address can also be reused
• PAGE N Heap: the number of records in the current data page space. Normally, normal records and deleted records are recorded. Therefore, this number will not decrease, but will increase. The page of a new initial data page N The default value of heap is 2, which is the maximum and minimum records
• PAGE Free: records deleted will enter the deletion linked list. If records are inserted, they can be allocated from this space first. If this space is not enough, they can be allocated on page HEAP_ Allocation in top space
• PAGE_ Direction: the direction of the last record insertion, from left to right, in order to accelerate the subsequent insertion speed
• PAGE N RECs: the number of records on this page, excluding the maximum and minimum two records

  Maximum and minimum records

  There are two virtual row records in each data page to limit the boundary of records. Infimum record is the smallest record on the data page, and the records of all users are larger than it. Superfimum record is the largest record on the data page, and the records of all users are smaller than it, that is, they are the boundary of the maximum and minimum values on the data page.

  User record

  All data records inserted by the user are stored here. Each data record has a pointer to the next record, but there is no pointer to the previous record. Records are sorted according to the primary key order, that is, users can traverse from the smallest data record to the largest data record, including all normal data records and deleted mark records, but they will not access the records marked for deletion.

  Free space

  After a data record is deleted, the space will be added to the free linked list. From page HEAP The free space between the top and the last data directory is reset to 0. When users insert records, they will first find the deleted record space. If there is no suitable location, they will be allocated from the free space, and page will be incremented after inserting records HEAP TOP、PAGE N RECs has two values.

  Page directory(used for record location)

  It is highly recommended to look at the page directory in the data page structure of the booklet
  The page directory stores the relative positions of records. These record pointers are sometimes called slots. Not every record has a slot. The slot in the InnoDB storage engine is a sparse directory, which means that a slot has multiple records. Since the page directory in the InnoDB storage engine is a sparse directory, the result of binary search is only a rough result, so the InnoDB storage engine must pass therecorder headerMediumnext_recordTo continue to find relevant records.
  It should be noted that the B + tree index cannot find a specific record, but only the page of the record can be found. The database loads the page into memory, and then performs binary search through the page directory. Because the binary search time complexity is very low and the search is performed in memory, the search time in this part can be ignored.

  End of file information (file tracker)

  This part of the information is at the last position of the data page, only 8 bytes. In order to detect whether the page is completely refreshed to the disk, there is only oneFIL_PAGE_END_LSNPart, the first 4 bytes represent the of the pagechecksumValue, last 4 bytes andfile headerMediumFIL_PAGE_LSNSame value.