Efficiently traversing InnoDB B + Trees with the page directory -- slot, traversinginnodb

Efficiently traversing InnoDB B + Trees with the page directory -- slot, traversinginnodb

Efficientlytraversing InnoDB B + Trees with the page directory1, the purpose of the page directory

As described in the posts mentioned above, all records in INDEX pages are linked together in a singly-linked list inascending order. however, list traversal through a page with potentiallyseveral hundred records in it is very expensive: every record's key must becompared, and this needs to be done at each level of the B + Tree until therecord sought is found on a leaf page.

All records on the Index page are sequentially linked to a single-linked table. However, it takes a lot of money to search records in the form of a linked list on a page: the keys of each record must be compared. This action needs to be performed on the B + tree of all heights, knowing that records are found on the leaf node.

 

The page directory greatly optimizes thissearch by providing a fixed-width data structure with direct pointers to 1 ofevery 4-8 records, in order. thus, it can be used for a traditional binarysearch of the records in each page, starting at the mid-point of the directoryand progressively pruning the directory by half until only a single entryremains, and then linear-scanning from there. since the directory isinclutively an array, it can be traversed in either ascending or descendingorder, despite the records being linked in only ascending order.

Page directory optimizes queries by providing a fixed-size data structure (which points to one of 4-8 records. Therefore, the binary search method can be used on each page. According to the slot half-lookup, we know that there is only one entry left, and then we can use this entry to perform linear scanning in boiling water. Because directory is an efficient array, scanning can be performed in ascending or descending order, even if the records are only linked in ascending order.

2. The physical structure of the pagedirectory

The structure is actually very simple. thenumber of slots (the page directory length) is specified in the first field ofthe INDEX header of the page. the page directory always contains an entry forthe infimum and supremum system records (so the minimum size is 2 entries), andmay contain 0 or more additional entries, one for each 4-8 system records. arecord is said to "own" another record if it represents it in the pagedirectory. each entry in the page directory "owns" the records between theprevious entry in the directory, up to and including itself. the count ofrecords "owned" by each record is stored in the record header that precedeseach record.

The number of Slots is specified in the first field of the index header part of the page. Page directory contains at least infimum and supremum slots. Therefore, directory has at least two slots. One record, if other own records, is in this slot. Each slot manages records between itself and the records in the previous slot. The number of records owned exists in the record header of each record.

 

The page-directory-summary mode of innodb_spacecan be used to view the page directory contents, in this case for a completelyempty table (with the same schema as the 1 million row table used in A quickintroduction to innodb_ruby), showing the minimum possible page directory:

 

$ Innodb_space-f t_page_directory.ibd-p 3page-directory-summary

Slot offset type owned key

0 99 infimum 1

1 112 supremum 1

 

If we insert a single record, we can seethat it gets owned by the record with a greater key than itself that has anentry in the page directory. in this case, supremum will own the record (aspreviusly discussed, supremum represents a record higher than any possible keyin the page ):

 

$ Innodb_space-f t_page_directory.ibd-p 3page-directory-summary

Slot offset type owned key

0 99 infimum 1

1 112 supremum 2

 

The infimum record always owns only itself, since no record can have a lower key. the supremum record always owns itself, but has no minimum record ownership. each additional entry in the pagedirectory shown own a minimum of 4 records (itself plus 3 others) and amaximum of 8 records (itself plus 7 others ).

Infimum records are always only owned by own, because they are the minimum records. Supremum records are always owned by own. In addition to infimum and supremum slots, each slot manages at least four records (itself + 3 others) and manages up to eight records.

 

To restrict strate, each record with an entry inthe page directory (bolded) owns the records immediately prior to it in thesingly-linked list (K = Key, O = Number of Records Owned ):

 

3. Growth of the page directory

Once any page directory slot wowould exceed 8 records owned, the page directory is rebalanced to distribute the records into4-record groups. if we insert 6 additional records into the table, supremumwill now own a total of 8 records:

Once a slot manages more than eight records, the slot divides them into four records. If we insert 6 records, the supremum slot will have 8 records.

$ Innodb_space-f t_page_directory.ibd-p 3page-directory-summary

Slot offset type owned key

0 99 infimum 1

1 112 supremum 8

 

The next insert will cause are-organization:

Inserting a record will cause restructuring.

$ Innodb_space-f t_page_directory.ibd-p 3page-directory-summary

Slot offset type owned key

0 99 infimum 1

1 191 conventional 4

2 112 supremum 5

 

4. A logical view of the page directory

At a logical level, the page directory (andrecords) for a page with 24 records (with keys from 0 to 23) wocould look likethis:

Infimum is always only own, And the n_owned of the slot is 1

Supremum is always the last few records on the owns page. The number can be less than 4.

Other slots have at least 4 records and a maximum of 8 records.

Discharge in reverse order. Starting from 16376 bytes, that is, the starting position of FIL trailer.

 

Take note that:

 

Records are singly linked from infimum tosupremum through all 24 user records, as previusly discussed.

Approximately each 4th record is enteredinto the page directory, represented in the procedure both by bolding thatrecord and by noting its offset in the page directory array represented at thetop of the procedure.

The page directory is stored "backwards" inthe page, so is reversed in this validation compared to its ordering on disk.

Record is linked as a single-chain table

Http://blog.jcole.us/2013/01/