[Go] MySQL indexing principle

MySQL indexing principle

B + Tree Index is an implementation of the B + tree in the database, which is the most common and most frequently used index in the database. B + trees represent balance (balance), not binary (binary), since B + trees evolve from the earliest balanced binary tree. Before you speak the B + tree, you must first understand the binary search tree, the Balanced binary tree (avltree), and the Balanced Multipath lookup tree (b-tree), which are gradually optimized by the B + tree.

Binary search Tree

A binary tree has the following properties: The key value of the Zuozi is less than the key value of the root, and the key value of the right subtree is greater than the key value of the root.
As shown here is a binary search tree,

Find the nodes of the two-fork tree find a node with a depth of 1, a number of lookups of 1, a depth of 2, and a node with a depth of N of N, so its average number of lookups is (1+2+2+3+3+3)/2 = 6 times

Binary search trees can be arbitrarily constructed, as are the six numbers of 2,3,5,6,7,8, which can also be constructed as follows:

But the query efficiency of this binary tree is low. Therefore, if the query efficiency of binary tree is as high as possible, the binary tree needs to be balanced, which leads to a new definition-balanced binary tree, or AVL tree.

Balanced binary trees (AVL tree)

Balanced binary tree (AVL tree) The maximum height difference for the two subtree of any node is 1, under the condition that the binary search tree is met. The following two pictures, the left is the AVL tree, its two subtree of any node of the height difference <=1; the right is not the AVL tree, its root node Zuozi height is 3, and the right subtree height is 1;

If inserting or deleting nodes in the AVL tree may cause the AVL tree to lose its balance, this unbalanced two-fork tree can be summed up in four gestures: LL (left-left), RR (right-right), LR (left and right), RL (left). They are as follows:

These four types of unbalanced gestures have their own definitions:
Ll:leftleft, also known as "left left". When a node is inserted or deleted, the left child of the root node (leaving children) also has a non-empty node, causing the root node's Zuozi height to be higher than the right subtree height 2,avl the tree out of balance.

Rr:rightright, also known as "right-right". When you insert or delete a node, the right child of the root node right children is also a non-empty node, causing the right subtree of the root node to be at a height higher than the Zuozi height 2,avl the tree to lose balance.

Lr:leftright, also known as "around". When a node is inserted or deleted, the left child (right child) of the root node also has a non-empty node, causing the root node's Zuozi height to be higher than the right subtree height 2,avl the tree to lose balance.

Rl:rightleft, also known as "right-left". After inserting or deleting a node, the left child of the root node (right child) also has a non-empty node, which causes the right subtree height of the root node to be Zuozi higher than the height of the 2,avl tree to lose balance.

After the AVL tree loses its balance, it can be rotated to restore balance. The following are the corresponding rotation methods for the four cases of loss of balance.

LL's spin. If ll loses balance, the AVL tree can be restored by a single rotation. The steps are as follows:

1. Take the left child of the root node as the new root node.
2. The right child of the new root node is the left child of the original root node.
3. Use the original root node as the right child for the new root node.

ll rotate as follows:

RR rotation: RR loses balance in case the rotation method is rotated symmetrically with LL, step as follows:

1. Use the right child of the root node as the new root node.
2. The left child of the new root node is the right child of the original root node.
3. Leave the original root node as the left child of the new root node.

The RR rotates as follows:

LR Rotation: When LR is out of balance, it needs to be rotated two times, in the following steps:

1. RR rotation around the left child of the root node.
2. ll rotates around the root node.

The rotation of the LR is as follows:

RL Rotation: RL loses the balance of the case also need to do two rotation, rotation method and LR rotation symmetry, the steps are as follows:

1. Rotate the right child around the root node for LL.
2. RR rotation around the root node.

The RL rotation is as follows:

Balanced multi-Path lookup tree (b-tree)

B-tree is a balanced lookup tree designed for storage devices other than disks. So before you talk about B-tree, you should know about the disk.

When the system reads data from the disk into memory, it is based on the disk block (block), and the data in the same disk block is read out at once, rather than needing anything.

The InnoDB storage engine has the concept of pages (page), which is the smallest unit of its disk management. The default size of each page in the InnoDB storage engine is 16KB, the page size can be set to 4K, 8K, 16K by parameter innodb_page_size, and the page size can be viewed in MySQL by the following command:

like ‘innodb_page_size‘;

• 1

While the system disk block storage space is often not so large, so innodb each request disk space will be a number of address contiguous disk block to reach the size of the page 16KB. InnoDB when the disk data is read into the disk will be the basic unit of the page, when querying data if each piece of data in a page can help to locate the location of data records, this will reduce disk I/O times, improve query efficiency.

The data of the B-tree structure allows the system to efficiently find the disk block where the data resides. In order to describe B-tree, first define a record as a two-tuple [key, data], key for the record, the primary key value in the corresponding table, data is a row of records in addition to the primary key. For different records, the key value differs from the other.

The b-tree of an M-order has the following characteristics:
1. Each node has a maximum of M children.
2. In addition to the root and leaf nodes, each of the other nodes has at least ceil (M/2) children.
3. If the root node is not a leaf node, there are at least 2 children
4. All leaf nodes are on the same layer and do not contain other keyword information
5. Each non-terminal node contains n keyword information (p0,p1,... Pn, K1,... kn)
6. Number of keywords N satisfied: ceil (M/2)-1 <= n <= m-1
7. Ki (i=1,... n) is the keyword, and the keyword is sorted in ascending order.
8. Pi (I=1,... N) is a pointer to a sub-root node. All node keywords for the subtree pointed to by P (i-1) are smaller than Ki, but are greater than K (i-1)

Each node in the B-tree can contain a large number of keyword information and branches, as shown in a 3-step b-tree:

Each node occupies disk space on a disk block, a node has two ascending sorted keywords and three pointers to the subtree nodes, and the pointer stores the address of the disk block where the child nodes reside. The two keywords are divided into three range fields that correspond to the range fields of the data of the subtree pointed to by three pointers. In the root node example, the data range for the subtree pointed to by the keyword 17 and the 35,P1 pointer is less than the 17,P2 pointer to the subtree that the 17~35,P3 pointer points to is greater than 35.

Simulate the process of finding keyword 29:

1. Disk Block 1 is found based on the root node and is read into memory. "Disk I/O operation 1th Time"
2. Compare the keyword 29 in the interval (17,35), locate the disk block 1 of the pointer P2.
3. Locate disk block 3 According to the P2 pointer and read into memory. "Disk I/O operation 2nd time"
4. Compare the keyword 29 in the interval (26,30), locate the disk block 3 of the pointer P2.
5. Locate disk Block 8 According to the P2 pointer and read into memory. "Disk I/O operation 3rd Time"
6. Find the Keyword 29 in the list of keywords in disk block 8.

Analyzing the above procedure, it is found that 3 disk I/O operations and 3 memory lookup operations are required. Because the in-memory keyword is an ordered table structure, the dichotomy method can be used to find efficiencies. The 3 disk I/O operations are the determining factor that affects the overall B-tree lookup efficiency. B-tree reduces the number of nodes relative to Avltree, making the data for each disk I/O to memory play a role, thus improving query efficiency.

B+tree

B+tree is an optimization based on b-tree, making it more suitable for implementing the external storage index structure, InnoDB storage engine is to implement its index structure with B+tree.

From the B-tree structure diagram in the previous section, you can see that each node contains not only the key value of the data, but also the database value. and each page storage space is limited, if the data is large, it will cause each node (that is, a page) can store the number of keys is very small, when the amount of data stored is very large, it will also cause b-tree depth, increase the number of disk I/O at the time of query, thus affecting query efficiency. In B+tree, all data record nodes are stored in the same layer of leaf nodes according to the order of key values, instead of only the key value information is stored on the leaf node, which can greatly increase the number of key values stored by each node and reduce the height of b+tree.

There are several different b+tree relative to B-tree:

1. Non-leaf nodes store only key value information.
2. All leaf nodes have a chain pointer between them.
3. The data records are stored in the leaf node.

The B-tree in the previous section is optimized, since B+tree's non-leaf nodes store only key-value information, assuming that each disk block can store 4 key values and pointer information, it becomes b+tree after its structure as shown:

Usually there are two head pointers on the B+tree, one pointing to the root node and the other to the smallest leaf node, and all leaf nodes (i.e. data nodes) are a chain ring structure. Therefore, you can perform two kinds of lookup operations on B+tree: One is the range lookup and the paging lookup for the primary key, and the other is a random lookup starting from the root node.

Perhaps the above example only 22 data records, do not see the advantages of B+tree, the following is a calculation:

The size of the page in the InnoDB storage engine is 16KB, the primary key type of the general table is int (takes 4 bytes) or bigint (takes up 8 bytes), the pointer type is generally 4 or 8 bytes, that is, a page (a node in b+tree) probably stores 16kb/(8b+8b) =1k a key value (because it is an estimate, for the convenience of calculation, here the value of K is 〖10〗^3). This means that a b+tree index with a depth of 3 can maintain 10^3 * 10^3 * 10^3 = 1 billion records.

In fact, each node may not be filled, so in the database, the height of the b+tree is generally in the layers. MySQL's InnoDB storage engine is designed with the root node resident in memory, which means that a maximum of one disk I/O operation is required to find a row record for a key value.

The B+tree indexes in the database can be divided into clustered indexes (clustered index) and secondary indexes (secondary index). The B+tree example diagram above is a clustered index in the database, and the leaf nodes in the b+tree of the clustered index hold the row record data for the entire table. The difference between a secondary index and a clustered index is that the leaf node of the secondary index does not contain all the data for the row record, but rather the clustered index key, the primary key, that stores the corresponding row data. When querying data through a secondary index, the InnoDB storage engine traverses the secondary index to find the primary key, and then finds the complete row record data in the clustered index through the primary key.

[Go] MySQL indexing principle