Efficiently traversing InnoDB b+trees with the page Directory--slot

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 is linked together in a singly-linked list Inascendi Ng 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 being done at each level of the B+tree until Therecord sought are found on a leaf page.

All records in the Index page page are concatenated in the form of a single-linked list increment. However, it is expensive to retrieve records in a linked list on a single page: Each key must be compared, and this action needs to be performed on a B + Tree of all heights, knowing that a record is found at the leaf node.

The page directory greatly optimizes Thissearch by providing a FIXED-WIDTH data structure with direct pointers to 1 ofever Y 4-8 Records, in order. Thus, it can used for a traditional binarysearch of the records on each page, starting at the mid-point of the Director Yand progressively pruning the directory by half until is a single entryremains, and then linear-scanning from there. Since the directory iseffectively An array, it can is traversed in either ascending or Descendingorder, despite the record s being linked in only ascending order.

Page Directory optimizes queries by providing a fixed-size data structure that points to one of 4-8 records. So you can use a two-fork lookup method on each page. Based on the slot binary lookup, know that only one entry is left and then a linear scan from this entry is boiled. Because directory is an efficient array, it can be scanned in ascending or descending order, even if the records are simply 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 a 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 was stored in the record header, that Precedeseach record.

The number of slots is specified in the first field of the Index header section of the page. Page directory contains at least Infimum and supremum slots. Therefore, the directory has a minimum of 2 slots. A record if own other records, expressed in this slot. Records between each slot management itself and the records in the previous slot. The number of records owned exists in the record header section of each recording.

The page-directory-summary mode of Innodb_spacecan is used to view the page directory contents, in this case for a complet Elyempty table (with the same schema as the 1 million row table used in A quickintroduction to Innodb_ruby), showing the M Inimum Possible page directory:

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

Slot Offset type owned key

0 infimum 1

1 supremum 1

If We insert a single record, we can seethat it gets owned by the record with a greater key than itself that have anentry I n the page directory. In this case, supremum'll own the record (aspreviously 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 infimum 1

1 supremum 2

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

Infimum records always only own themselves, because it is the smallest record. Supremum records always own themselves. In addition to the slots for infimum and supremum, each slot will manage at least 4 records (itself+3others) and manage up to 8.

To illustrate, each record with a entry inthe page directory (bolded) owns the records immediately prior to it in thesing ly-linked list (K = Key, O = number of Records owned):

3. Growth of the page directory

Once any page directory slots would exceed 8records 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 is managed with more than 8 records, the slot is divided into 4 records as a group. If we insert 6 Records, thesupremum slot will have 8 Records .

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

Slot Offset type owned key

0 infimum 1

1 supremum 8

The next insert would cause are-organization:

Inserting a record can cause a reorganization

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

Slot Offset type owned key

0 infimum 1

1 191 Conventional 4

2 supremum 5

4. A logical view of the page directory

At a logical level, the page directory (andrecords) for a page with the records (with keys from 0 to +) would look Likethi S

Infimum always only own himself, the slot of the n_owned=1

Supremum always owns the last few records in a page, the number can be less than 4.

The other slots have at least 4 records with a maximum of 8.

Reverse emission. Starting at 16,376 bytes, which is the starting position of the FIL trailer.

Take note:

Records is singly linked from infimum tosupremum through all user Records, as previously discussed.

Approximately each 4th record was enteredinto the page directory, represented in the illustration both by bolding Thatrecor D and by noting their offset in the page directory array represented at thetop of the illustration.

The page directory is a stored "backwards" inthe page, so are reversed in this illustration compared to their ordering on disk.

The record is a single linked list form link

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

Efficiently traversing InnoDB b+trees with the page Directory--slot