MySQL InnoDB Storage Engine table (ii)

This is a continuation of the last part of the story to continue to say.

4.InnoDB data page structure
page is the smallest disk unit of the INNODB Storage engine management database. A page of page type B-tree node holds the actual data for the rows in the table. The page consists of the following seven sections: File header (header), page header (header), Infimun and supremum Records, User Records (row record), free space, page  Directory (page catalog) and file Trailer (footer). Such as:

The size of the file Header, Page header, and file trailer is fixed, 38, 56, and 8 bytes, respectively, to mark some information about the page, such as checksum, the number of layers in the B + Tree index of the data page, and so on. The parts of User Records, free space, and page directory record storage space for actual rows, so the size is dynamic. The sections below are described separately
A.file Header
The File header is used to record the header information of some pages, which consists of 8 parts and occupies 38 bytes. Such as:

The specific types of Fil_page_type are as follows:

B.page Header
The page header is used to record some state information of the data page, which consists of 14 parts and occupies 56 bytes. Such as:

C.infimun and Supremum
In the InnoDB storage engine, each data page has two virtual row records that are used to limit the boundaries of a record. A Infimun record is a value that is smaller than any primary key in the page, and supremum is larger than any value that is likely to be large. Are created at the time of page creation and are not deleted under any circumstances. In compact and redundant row formats, the number of bytes consumed does not want to be the same. Such as:

D.user Record and free Space
The User record is the actual storage of the row Records. Free space, which is also a linked list data structure, is added to the free list when a piece of data is deleted.
E.page Directory
In page directory, the relative position of the record (which is the relative position of the page, not the offset) is stored, and sometimes these record pointers are called Slots (slots) or directory Slots (directory slots). The slot in the InnoDB storage engine is a sparse directory (spare directory), which may contain multiple records in a slot. The n_owned value of the pseudo-record infimum is always 1, the n_owned value of the record supremum is [1,8], and the other user records the value range of n_owned [4,8]. A maintenance operation that splits or balances a slot is required when the record is inserted or deleted. The records in slots are stored according to the index key value, which can be used to find a pointer to the record quickly. Because page directory is a sparse directory in the InnoDB storage engine, the result of a binary lookup is a rough result, so the InnoDB storage engine must continue to find related records through the Next_record in the record header. The B + Tree index cannot find a specific record in itself and can find just the page where the record is located. The database loads the page into memory and then makes a binary lookup through page directory. Binary lookups have a low time complexity, while in-memory lookups are fast, so the time it takes to find this part is usually ignored.
F.file Trailer
To check whether the page has been fully written to the disk (such as possible disk corruption during writing, machine shutdown, and so on), the InnoDB Storage Engine page has the file Trailer section set. File trailer has only one fil_page_end_lsn section, which takes up 8 bytes. The first 4 bytes represent the checksum value of the page, and the last 4 bytes are the same as the FIL_PAGE_LSN in the file header. Compare these two values to the Fil_page_space_or_checksum and FIL_PAGE_LSN values in the file header to see if they are consistent (the checksum comparison needs to be compared by InnoDB's checksum function. is not a simple equivalent comparison) to ensure the integrity of the page.
In the default configuration, the InnoDB storage engine detects the integrity of the page every time a page is read from disk, that is, whether the page has corrupt. The user can turn on or off the page integrity check by using the parameter innodb_checksums. MySQL 5.6. Version 6 starts with the new parameter innodb_checksum_algorithm to set the algorithm for the checksum function. The default is CRC32, you can set the values are: InnoDB, CRC32, none, Strict_innodb, Strict_crc32, Strict_none.
5.Named File formats mechanism
With the development of the InnoDB storage engine, new page data structures are sometimes used to support new feature features. such as the InnoDB 1.0.X release provides a new page data structure to support table compression functionality, full overflow (Off page) large variable long character type field storage. These new page data structures are incompatible with previous versions of the page, so the innodb storage engine resolves compatibility between versions by named the File formats mechanism. Set the file format with the parameter Innodb_file_format. The parameter innodb_file_format_check is adopted to detect the support degree of the current InnoDB storage engine file format, which is on by default.
6. View
A view is a named virtual table that is defined by an SQL query and can be used as a table, unlike a persisted table, where the data in the view is not physically stored. In the actual development of almost no use, ignored ~ ~
7. Partition Table
Partitioning is the process of decomposing a table or index into multiple smaller, more manageable parts. Currently, the MySQL database only supports horizontal partitioning and does not support vertical partitioning. In the actual development of almost no use, ignored ~ ~

MySQL InnoDB Storage Engine table (ii)