Innodb_file_per_table defaults to 1. InnoDB engine generates an IBD file for each InnoDB engine data table in the MySQL data folder. The files corresponding to the tables in the database are located in this folder).
If innodb_FILe_per_TABLE is set to 1, each tablespace corresponds to a file, that is, each tablespace has an independent tablespace. Each tablespace contains a number of segments, and each segment contains a number of extents. The element that constitutes extents is page.
All pages in one tablespace are the same size. Set by innodb_page_size. Default value: 16 KB.
An extent consists of several pages. If the page size is less than 16 KB, an extent is 1 MB. If the page size is 32 KB, the extent size is 2 MB. If the page size is 64 KB, the extent size is 4 MB.
The number of segments in the TABLESPACE is twice the number of indexes in the tablespace. Each index allocates two segments, one for non-leaf nodes of B+Tree and the other for leaf nodes of B+Tree. Since all data in a B+Tree is stored in leaf nodes, storing all leaf nodes in a single segment can make data read as sequential I/O as possible.
Segments grow by extents. InnoDB can assign up to four extents to a segment at a time, again to ensure data continuity.
___________ line structure
Page stores rows (records) in a data table. By InnoDB convention, at least two records should be stored in a page. The record storage structure is shown in the figure above. A record consists of three parts:
Field Start OffsetsIs a list that stores the starting position of the next field of each field in reverse order.
Suppose there are three fields in a table. The first Field is 1 in length, the second Field is 2 in length, and the third Field is 4 in length. Then the information stored in the Field Start Offsets is [07, 03, 01].
In addition, the starting position of a record defaults to the position pointed to by Zero Point in the figure above, rather than the starting position of the Field Start Offsets. [07,03,01] immediate position relative to Zero Point.
Extra BytesIt is 6 bytes long and records important information about the current line. Among them:
☞ DELETED_flag Indicates whether the current record has been deleted. The length is one bit.
☞ N_fields Specifies the number of fields in the current line. The length is 10 bits.
☞ 1byte_offS_flag Field Start Length of each item in the list of Offsets. The length is 1 bit. If the value marked as is 1, the length of the entries in the list is 1 byte (at this point, the total length of the current record cannot exceed 127 bytes); otherwise, it is 2 bytes.
☞ Next Record Is a 16-bit pointer to the next record.
Field ContentsStores the specific values of these fields.
Structure of page 2.
The data rows are stored in the Page structure, which needs to record some additional information in addition to the data records.
☘Fil Header stores some information about the current page. One of the most important pieces of information is the checksum of the current page. This value is also stored in Fil Trailer. In this way, when page data is written from memory to disk, header data is written to disk first, and Fil Trailer data is written to disk last. At this point, if the checksum in Fil Trailer is found to be different from that in Fil Header, there is an exception in the process of writing the name data and it needs to be written again. In addition, the Fil Header has two Pointers to the previous and next page adjacent to the current page.
The ☘Page Header stores information related to the data in the current Page. Include:
PAGE_N_DIR_SLOTSPage DirectoryPart ofslotThe initial value is 2PAGE_HEAP_TOPA pointer to the first record (manually written record)PAGE_N_HEAPThe currentpageThe total number of records in, including those marked for deletion, with an initial value of 2PAGE_FREEPoint to theFree SpacePointer to the starting position of the sectionPAGE_GARBAGEThe amount of space occupied by records marked for deletionPAGE_LAST_INSERTPoint to the most recently inserted record (Zero PointLocation)PAGE_DIRECTIONThe direction in which the data is insertedPAGE_LEFT、PAGE_RIGHT、PAGE_NO_DIRECTIONPAGE_N_DIRECTIONThe number of consecutive records inserted in one directionPAGE_N_RECSThe currentpageThe number of valid records inPAGE_MAX_TRX_IDAct in the presentpageMaximum transaction ID on the record contained in thePAGE_LEVELThe currentpageHeight of the node in B+TreePAGE_INDEX_IDThe currentpageID of the index at which it is locatedPAGE_BTR_SEG_LEAFOf a leaf node in a B+TreepageIn thesegment的headerPAGE_BTR_SEG_TOPNon-leaf node in B+TreepageIn thesegment的header
The information stored in the last two items is used to assign new pages to the segment.
☘ Infimum and Supremum represent the lower and upper bounds of the index, respectively. In this way, the database engine does not go beyond the start and end of the index when retrieving data by index. InnoDB sets Infimum and Supremum records at the root of the index when it is created. These records are never deleted.
The ☘ User Records section Records data inserted by users. InnoDB does not store data records strictly in index order, because this involves a lot of order changes. To ensure performance, InnoDB inserts data into Free Space or into places marked as deleted records.
Records marked for deletion form a linked list
If there is contiguous space in the records marked for deletion that is larger than the space required for newly inserted data, the space occupied by the records marked for deletion is reused.
The Page Header records the Free Space Header pointer. When new data is inserted, the Free Space Header pointer will be offset correspondingly until there is not enough Free Space.
☘ Page Directory stores a certain number of slots. The specific number of slots is recorded in the PAGE_N_DIR_SLOT of the Page Header, which is similar to the Directory of a book.
InnoDB does not provide one-to-one mapping of slots to data records. In InnoDB, records in pages are stored in slots, with 4 to 8 records in each slot, ideally 6 records in each slot. In special cases, the first slot in each page contains only one record, that is, Infimum, and the second slot may contain 1 to 8 records.
The newly created ‘page’ has only two records, ‘Infimum’ and ‘Supremum’. There are only two ‘slots’ in the’ Page Directory ‘, including ‘Infimum’ and ‘Supremum’. When fewer than eight records are inserted into the ‘page’, they are assigned to the second ‘slot’. As data continues to be inserted, the second ‘slot’ is split, with each newly split ‘slot’ containing four records.
Records in a page are assigned to slots in indexed order.
Note that each slot refers to the last record in the current slot. The N_owned field in the record’s Extra Bytes also records the number of records contained in the current slot.
3. Page merger
In InnoDB, row records are stored in primary key index order in leaf pages of the primary key index tree. InnoDB sets a property MERGE_THRESHOLD for each index. The default value is 50%. When a page has less than 50% effective space usage, InnoDB tries to merge it with neighboring pages to optimize space usage.
Normally, when Records are inserted into an InnoDB table, they are written sequentially to the User Records space in the page. When there is no space in the page, it will continue to write to a new page.
When a record is deleted, the space occupied by the deleted record is not reclaimed. Instead, the deleted_flag in the Extra Bytes of the record is marked as 1. When the space occupied by deleted records in a page reaches a certain value (the value set by MERGE_THRESHOLD), InnoDB looks at adjacent pages to see if there is an opportunity to optimize the space utilization, and if so, merges the pages.
When 50% of the second page is deleted, InnoDB looks at adjacent pages to see if they have a chance to optimize space utilization. If the third page is also only half used, an attempt is made to merge the second and third pages.
After the merge, the second page stores data that originally existed in the third page, while the third page currently has no data.
Similarly, updating a table using update may cause a merge of pages. Index_page_merge_successful in the Innodb_metrics table in information_SCHEMA records the number of page merges.
Page _____ the split
In practice, every record in a data table is not guaranteed to be of equal length, so the space in the page will not be exactly 100% filled. Even if the space in the page is 100% used, updating the records in the page can cause the updated record to require more space to store data than the old record.
In the first case, if InnoDB inserts a record with ID 16 into the table and the record needs more storage space than the remaining space in the second page, InnoDB creates a new page. Then determine the split position in the second page (MERGE_THRESHOLD), move the data records after the split position to the newly created page, and finally re-establish the association between pages.
For the second case, InnoDB will do the same split page operation if a record with ID 16 is updated and the updated record needs more storage space.
Page splitting often causes page order dislocation, and even causes the newly split page to be in a different extent from the adjacent page. The index_PAGe_Splits from Innodb_Metrics in information_SCHEMA will be recorded.
If you want to restore a split page, there are two methods:
- Split off from the new
pageDrop until its space utilization becomes lowerMERGE_THRESHOLD - On the table
optimizeoperation
In addition, InnoDB places an exclusive lock on the index during page merges and splits, making data inaccessible during the process.
References
Dev.mysql.com/doc/interna… Dev.mysql.com/doc/interna… Dev.mysql.com/doc/refman/… www.percona.com/blog/2017/0… Mp.weixin.qq.com/s?__biz=MzI…