MySQL index with B + Tree

MySQL index with B + Tree B + Tree

The MySQL Innodb storage engine uses B + trees to organize the index. Before introducing the B + Tree, first recognize what is a B-tree, B-tree is a balanced binary tree, unlike the general two-fork search tree, the balance binary tree first satisfies the definition of binary search tree (the key of the left subtree is smaller than the root key, the right subtree key is greater than the key), and the second must meet any node of the two subtree height difference is 1 The maintenance of the B-tree requires that the nodes be inserted and updated with 1 or more left and right spins to meet the equilibrium conditions. Whether the binary lookup tree is balanced directly affects the number of times the lookup needs to be compared.

The B + tree differs from the normal two-fork tree in that its nodes consist of multiple keywords and pointers to the subtree, the number of pointers to the subtree equals the number of keywords plus 1, the range of the keywords in these subtrees is qualified by its parent node, and the real data part is stored in the leaf node. MySQL in the page data structure is these leaf nodes, each leaf node corresponds to a page, and the page data structure has Page_prev and page_next two pointers, so these leaf node 22 is also connected to each other.

Insert operation for B + Tree

Because the B + tree needs to be balanced after insertion, the insert operation involves splitting the page. The Index page refers to a non-leaf node and the leaf page refers to the leaf nodes. The insert operation is divided into the following three scenarios:

1. When both the Index page and leaf page are dissatisfied, the record is inserted directly into the leaf node.
2. When the Index page is not satisfied, the Leaf page is full, the node is placed in the corresponding page, the middle node as the basis, the Page is split, and then put the middle node in the Index Page, the split left and right records are placed on both sides of the middle node.
3. When the index page and leaf page are full, split the leaf page, and then split the index page, the method for splitting the index page is the same as the method for splitting the leaf page.

It is important to note that in order to reduce the splitting of a page in a possible way, the B + tree provides a rotation operation similar to a binary balance tree. Rotation occurs when the Leaf Page is full, but its left and right sibling nodes are not full.

B + Tree Delete operation

The B + Tree uses a fill factor to control the tree change, that is, the number of intermediate node keywords and the proportional relationship between the number of key words in the leaf node and the maximum value. Here is an example of a fill factor of 50%. Less than the fill factor is less than half the total capacity. The delete operation can be divided into the following three scenarios:

1. When the number of the leaf page keyword and the index page node keyword are not less than the fill factor, the record is deleted directly from Leaf node, and if the node is the index page node, the index page node is replaced with its right node.
2. When the number of leaf page keys is less than the index page is not less than the fill factor, the leaf page node is merged with its sibling node, and the index page node is updated.
3. When the number of Leaf page keys and the number of Index page node keywords are less than the fill factor, the index page node needs to be merged on the basis of Scenario 2.

Index

The index is implemented in MySQL using B + trees, and the B + trees formed between different indexes are different.

Clustered index

A clustered index constructs a B + tree based on the primary key, and the leaf node holds the row records for the corresponding page.

Secondary index (nonclustered index)

A secondary index is a B + tree constructed using a non-primary key, and the leaf node holds the corresponding key value and the corresponding clustered index key. Search by secondary index is generally level two, the first level to find the key value corresponding to the clustered index key, the second level is based on the clustered index key to find Row Records.

Federated Index

A federated index is an index of multiple columns on a table so that the index node and Page node of a B + tree are constructed with multiple keys.

Index overrides

In the case of a federated index to search for Row records, assuming that the columns of the required row records are exactly included in the federated Index, the results can be obtained directly from the federated index, eliminating the need to search from the clustered index, which greatly reduces IO because it does not contain records of the entire row.

If the index is not used

When querying all the columns of a row that satisfies a condition, MySQL does not use a secondary index, but instead uses the clustered index directly. The reason is that even if a secondary index is used, it is necessary to find the clustered index through the directory in the leaf node to get the complete information, then get it directly from the clustered index.

MySQL index with B + Tree