Detailed analysis of InnoDB indexes in MySQL

Detailed analysis of InnoDB indexes in MySQL

Abstract:

This article introduces the InnoDB Index-related knowledge of MySQL, from various trees to the indexing principle to the details of storage.

InnoDB is the default storage engine of MySQL (MyISAM before MySQL5.5.5 ). For the purpose of efficient learning, this article mainly introduces InnoDB and compares a small number of MyISAM instances.

This article was summarized during my learning process. The content mainly comes from books and blogs (which will be provided by references). I have added some of my own understandings during the process, the description is not accurate.

1. Various TREE STRUCTURES

I didn't plan to start with the Binary Search Tree, because there are too many related articles on the Internet, but considering the clear illustration, it is helpful to understand the problem and to ensure the integrity of the article, finally, this part is added.

Let's take a look at several tree structures:

1. Search for a binary tree: each node has two subnodes. Increasing the data size will inevitably lead to a rapid increase in height. Obviously, this is not suitable as the infrastructure for storing large amounts of data.

2 B: a B-level m-tree is a balanced m-path search tree. The most important property is that the number of keywords contained by each non-root node j satisfies the following requirements: Running m/2 running-1

3 B + tree: an m-Level B tree is a balanced m-path search tree. The most important property is that the number of keywords contained by each non-root node j satisfies the following requirements: Running m/2 running-1

4 B * tree: an m-Level B tree is a balanced m-path search tree. The two most important properties are: 1. Each non-root node contains the number of keywords. j satisfies the following requirements: Limit m2/3 limit-1.

The B/B +/B * trees have similar operations, such as searching, inserting, and deleting nodes. Here, we only focus on the insertion of nodes, and only analyze the insertion operations when the current node is full, because this action is slightly complicated and can fully reflect the differences between several trees. In contrast, the retrieval node is easier to implement, and the deletion of a node only needs to complete the process opposite to the insertion. (deleting a node in an actual application is not a complete Inverse Operation of insertion, it is often used only to delete data and keep the space for subsequent use ).

First, let's look at the split of Tree B. The red value is the newly inserted node. Every time a node is full, it needs to be split (split is a recursive process, refer to the insert 7 below, resulting in two layers of split ), because the non-leaf node of Tree B also saves the key value, the value after the node split is full will be distributed in three places: 1 original node, 2 parent node of the original node, 3. Create a new sibling node of the original node (refer to the insertion process in step 5 and Step 7 ). Splitting may increase the height of the tree (refer to the Insert Process of 3, 7), or may not affect the height of the tree (refer to the Insert Process of 5, 6 ).

Split of B + tree: When a node is full, allocate a new node, copy 1/2 of the data from the original node to the new node, and add a pointer to the new node in the parent node; the split of the B + tree only affects the original node and the parent node, but does not affect the sibling node. Therefore, it does not need to point to the sibling node.

B * tree split: When a node is full, if its next sibling node is not full, move part of the data to the sibling node, and then insert a keyword into the original node, finally, modify the keywords of the sibling node in the parent node (because the keyword range of the sibling node has changed ). If the brothers are full, add a new node between the original node and the sibling node, copy 1/3 of the data each to the new node, and add a pointer to the new node at the parent node. We can see that the split of the B * tree is very clever, because the B * tree should be 2/3 full after the split. If the B + tree method is used, simply dividing a full node into two parts will make each node only 1/2 full, which does not meet the requirements of the B * tree. Therefore, the B * tree adopts the policy of inserting a sibling node after the current node is full (this is why B * tree needs to add a sibling linked list to a non-leaf node ), until the sibling nodes are full and then pulled together to join the members, and each of them contributes 1/3 to set up a new node. The result is that the three nodes are just 2/3 full, it is a pleasure to meet the requirements of the B * tree.

The B + tree is suitable for serving as the basic structure of the database, because of the memory of the Computer-two-layer storage structure of the mechanical hard disk. The memory can achieve fast Random Access (random access gives any address and requires that the data stored in this address be returned), but the capacity is small. The Random Access to the hard disk must go through mechanical operations (one head is moved and two disks are rotated). The access efficiency is several orders of magnitude lower than the memory, but the hard disk capacity is large. The typical database capacity is much larger than the available memory size, which determines that it is possible to retrieve a piece of data in the B + tree through several disk I/O operations. As shown in: Generally, the downward reading of a node may be a disk I/O operation, but non-leaf nodes usually load the memory in the initial stage to speed up access. To speed up horizontal traversal between nodes, the blue CPU computing/memory read in the figure may be optimized to a binary search tree (the page directory mechanism in InnoDB) in the real database ).

The B + tree in the real database should be very flat. You can verify how flat the B + tree is in InnoDB by inserting enough data into the table in sequence. We CREATE a test table with only simple fields through the CREATE statement, as shown in, and then add data to fill the table. Through the statistical data (for the source, see References 1), we can analyze several intuitive conclusions, which display the scale of the B + tree in the database at a macro level.

MySQL InnoDB Storage engine lock mechanism Experiment

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration

1. Each leaf node stores 468 rows of data, and each non-leaf node stores about 1200 key values. This is a balanced 1200-way search tree!

2 For a 22.1G capacity table, only the B + tree with a height of 3 can be stored. This capacity can meet the needs of many applications. If the height is increased to 4, the storage capacity of the B + tree will immediately increase to 25.9T!

3 For a 22.1G capacity table, the height of the B + tree is 3. If you want to load all non-leaf nodes to the memory, you only need less than 18.8M of memory. (How can we draw this conclusion? For a tree with a height of 2, 1203 leaf nodes only need 18.8M space, while 22.1G is 3 from the height of the good table and 1204 non-leaf nodes. At the same time, we assume that the size of the leaf node is greater than that of the non-leaf node, because the leaf node stores row data, rather than the leaf node only has keys and a small amount of data .), With only such a small amount of memory, you can ensure that only one disk IO operation is required to retrieve the required data, and the efficiency is very high.

For more details, please continue to read the highlights on the next page:

1 2 3 4 Next Page