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 very expensive to retrieve records in the form of a linked list in a single page: Each record must have a more key, which 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.

You can therefore use a two-fork lookup method in each page. Based on slot binary, you know that there is only one entry left. Then from this entry the boiling water is linearly scanned. Because directory is an efficient array. can be scanned 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 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 includes at least Infimum and supremum slots.

Therefore, the directory has a minimum of 2 slots. A record assumes own other records, expressed in this slot. A record 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 are always only own themselves, as is the smallest record.

Supremum records always own themselves.

In addition to the slots in infimum and supremum, each slot will manage at least 4 records (itself+3others). Manage up to 8 of them.

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. Assuming 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, and the number can be less than 4.

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

Reverse emission. Start with 16,376 bytes. The start 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