MySQL InnoDB Storage engine table (2), mysqlinnodb

MySQL InnoDB Storage engine table (2), mysqlinnodb

This article continues the unfinished part of the previous article.

4. InnoDB Data Page Structure
Page is the minimum disk unit for the InnoDB Storage engine to manage databases. A page of B-tree Node stores the actual data of the row in the table. A Page consists of the following seven parts: File Header, Page Header, Infimun, Supremum Records, User Records, and Free Space) page Directory and File Trailer ). For example:

The sizes of File Header, Page Header, and File Trailer are fixed, which are 38, 56, and 8 bytes respectively. They are used to mark some information of the Page, such as checksum, the number of layers of B + tree indexes on the data page. User Records, Free Space, and Page Directory are the actual row record storage Space, so the size is dynamic. The following sections describe each part.
A. File Header
The File Header is used to record the Header information of some pages. It consists of eight parts and occupies 38 bytes in total. For example:

The specific types of FIL_PAGE_TYPE are as follows:

B. Page Header
The Page Header is used to record some status information of the data Page. It consists of 14 parts and occupies 56 bytes in total. For example:

C. Infimun and Supremum
In the InnoDB Storage engine, each data page has two virtual Row Records, which are used to limit the record boundary. Infimun records are smaller than any primary key on the page, and Supremum is larger than any possible value. All pages are created and will not be deleted under any circumstances. In the format of Compact and Redundant, the number of bytes occupied does not need to be the same. For example:

D. User Record and Free Space
User Record is the content that actually stores Row Records. Free Space refers to the Free Space, which is also a linked list data structure. After a piece of data is deleted, the Space will be added to the Free linked list.
E. Page Directory
Page Directory stores the relative location of records (the relative location of the Page rather than the offset). In some cases, these record pointers are called Slots or Directory Slots ). The slot of the InnoDB Storage engine is a sparse directory (spare directory), which may contain multiple records. The value of n_owned in the pseudo-record Infimum is always 1. The value range of n_owned in the record Supremum is [1, 8]. The value range of n_owned in other user records is [4, 8]. When a record is inserted or deleted, the slot must be split or balanced. In the Slots, records are stored according to the index key value. In this way, you can use binary search to quickly find the record pointer. Because the Page Directory in the InnoDB Storage engine is a sparse Directory, the binary search result is a rough result. Therefore, the InnoDB Storage engine must use next_record in the record header to continue searching for related records. The B + tree index itself cannot find a specific record, but only the page where the record is located. The database loads the Page into the memory, and then performs a binary search using Page Directory. The time used for Binary Search is very low, and the search in the memory is very fast. Therefore, the time used for this part of the search is usually ignored.
F. File Trailer
To check whether the page has been fully written to the disk (such as disk corruption during possible write operations or machine shutdown), the File Trailer section is set for the InnoDB Storage engine page. File Trailer has only one FIL_PAGE_END_LSN, which occupies 8 bytes. The first 4 bytes represent the checksum value of the page. The last 4 bytes are the same as FIL_PAGE_LSN in the File Header. Compare these two values with the FIL_PAGE_SPACE_OR_CHECKSUM and FIL_PAGE_LSN values in the File Header to see if they are consistent (the comparison of checksum needs to be compared through the checksum function of InnoDB, rather than a simple equivalent comparison ), to ensure the integrity of the page.
In the default configuration, the InnoDB Storage engine checks the integrity of a page every time it reads a page from the disk, that is, whether the page has upt. You can enable or disable the page integrity check by using the innodb_checksums parameter. MySQL 5.6.6 starts to add the innodb_checksum_algorithm parameter to set the algorithm of the checksum function. The default value is crc32, which can be set to innodb, crc32, none, strict_innodb, strict_crc32, and 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 features. For example, InnoDB 1.0.X provides a new page data structure to support table compression and completely store Off-page large-to-long character fields. These new page data structures are not compatible with previous versions. Therefore, the InnoDB Storage engine uses the Named File Formats mechanism to solve compatibility between different versions. Set the file format using the innodb_file_format parameter. The innodb_file_format_check parameter is used to check the currently supported file formats of the InnoDB Storage engine. The default value is ON.
6. View
A view is a named virtual table defined by an SQL query and can be used as a table. Unlike a persistent table, the data in the view is not physically stored. It is almost never used in actual development ~~
7. partitioned table
The partitioning process is to break down a table or index into smaller and more manageable parts. Currently, the MySQL database only supports horizontal partitions and does not support vertical partitions. It is almost never used in actual development ~~