MySQL underlying index depth solution

Author: Wang Xinhua

Why do you need an index?
In one sentence: the appearance of indexes is actually to improve the efficiency of data query.

One, index common models

Models: hash tables, ordered arrays, and search trees

Hash table

• Hash table is a structure that stores data in key-value. We only need to enter the key to be searched, namely key, to find its corresponding value, namely Value. The idea of hashing is very simple. Put the value in the array, use a hash function to convert the key into a certain position, and then put the value in this position of the array.
  Time complexity: 0(1)
• Draw the key point: If the value of the index is duplicated, a hash collision will occur. Although the hash conflict can be resolved, the query efficiency will be reduced.
• Scenario: The structure of the hash table is suitable for the scenario where there is only equivalent query, not for the search range. Similar to redis Memcached and some other NoSQL engines.

Search tree

Before understanding the search tree, we need to know the binary tree, balanced binary tree, B tree, B+ tree

Recommend a tool that can clearly understand the principle of the tree: https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

Binary tree

Binary tree characteristics: The key value of the left subtree 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.

The following figure is a binary tree

The insertion order of the current binary tree is: 3 2 4 1 5

If we insert in the order of 1 2 3 4 5, the binary tree we get is as shown in the figure below

If it is this kind of binary tree query efficiency is too low. If you want the query efficiency of the binary tree to be 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 tree

Features of balanced binary tree:

• Satisfy the characteristics of the binary tree
• The maximum difference between the heights of the two subtrees of any node is 1

After the above two combinations is a balanced binary tree, as shown below

The order of inserting the tree is: 1 2 3 4 5 6 7 8 When the content is generated as shown in the figure, it is not the same as the completion of figure 2. It is obvious that no matter how the node data is inserted when the figure is generated by the tool Can satisfy the characteristics of a balanced binary tree 2.

The avl tree can also be satisfied after deleting a node, as shown in the following figure after deleting the 5 nodes in Figure 3.

Summarize the advantages of balanced binary trees

• a Good search performance (O(logn)), there is no extreme inefficient search.
• b can realize range search and data sorting.

It seems that the AVL tree is really good as the data structure for data search, but the AVL tree is not suitable for the index data structure of the Mysql database, because consider this question:

The bottleneck of database query data lies in disk IO. If we are using an AVL tree, we only store one piece of data per tree node. We can only retrieve the data on one node and load it into the memory at a time, such as query id=8. We need to perform disk IO three times for this data, which is time-consuming. Therefore, when designing database indexes, we need to first consider how to reduce the number of disk IOs as much as possible.

Disk IO has a feature, that is, the time it takes to read 1B data and 1KB data from disk is basically the same. Based on this idea, we can store as much data as possible on a tree node. IO loads more data into the memory, which is the design principle of B-tree, B+ tree.

B-tree

Features of B-tree:

• All key values are distributed throughout the tree
• The search may end at a non-leaf node
• The root node has at least 2 subtrees
• All leaf nodes are on the same layer
• The number of forks (the number of ways) is always 1 more than the number of keywords
• Node stores key and value

Demonstrate B-tree index split and merge

For example, when Max Degree (number of channels) is 3, we insert data 1, 2, and 3. When inserting 3, it should be in the first disk block, but if a node has three keywords, it means there are With 4 pointers, the child node will become 4 way, so this time it must be split. Put the middle data 2 up, and turn 1 and 3 into 2's child nodes.

If you delete the node, there will be a reverse merge operation. Note that this is split and merge, which is different from the left-hand and right-hand of the AVL tree. We continue to insert 4 and 5, and the B Tree will be split and merged again.

From this, we can also see that there will be a large number of index structural adjustments when updating the index. The splitting and merging of nodes are actually the splitting and merging of InnoDB pages.

B+ tree

B+ tree is an optimization work done on B tree

• Each node of the B+ tree can contain more node content. Why do you do this? First: reduce the height of the tree. Second: change the data range into multiple intervals. The more intervals, the faster the retrieval speed
• Non-leaf nodes store keys, and leaf nodes store keys and data
• The leaf node pointers are connected, and the sequential search speed is faster
  

What is the difference between B-tree and B+-tree?

First, a B-tree node stores the key and data, while the B+ tree stores the index (address), so a node in the B-tree cannot store a lot of data, but a node of the B+ tree can store many indexes, B+ tree The leaf nodes store all the data.

Second, the leaf nodes of the B+ tree are connected in a linked list in the data stage to facilitate range search.

Through the comparison of B-tree and B+ tree, we can see that B+ tree nodes store indexes. When the storage capacity of a single node is limited, a single node can also store a large number of indexes, which reduces the height of the entire B+ tree and reduces disk IO. Secondly, the leaf nodes of the B+ tree are where the real data is stored. The leaf nodes are connected by a linked list. The linked list itself is ordered, and it is more efficient when searching in the data range. Therefore, Mysql index uses B+ tree, which has very good performance in search efficiency and range search.

In summary, the mysql innodb index has chosen the B+ tree.

Thinking problem

1. All the data of the poems stored in the leaf nodes of the B+ tree. If 10 million data are stored, the linked list is too large, will it affect the performance?

You can use the data page, the following is a key analysis

2. How many rows of data can a B+ tree in InnoDB store?

Neither the root node nor the branch node of B+Tree stores data, only the leaf nodes store data. The searched keyword will not be returned directly, but the leaf node of the last layer will be reached. For example, when we search for id=3, although it hits directly in the first layer, all the data is on the leaf node, so I will continue to search down to the leaf node.
For example: assuming that a record is 1K, a leaf node (one page) can store 16 records. How many pointers can a non-leaf node store?
Assume that the index field is of type bigint and the length is 8 bytes. The pointer size is set to 6 bytes in the InnoDB source code, so a total of 14 bytes. Non-leaf nodes (one page) can store 1024*16 / 14 = 1170 such units (key value + pointer), which means that there are 1170 pointers.
When the tree depth is 2, there are 1170^2 leaf nodes, and the data that can be stored is:
1170 * 1170 * 16 = 21,902,400 20 million
When searching for data, one page search represents one IO, that is to say, for a table of about 20 million, querying data requires up to 3 disk accesses. Therefore, the depth of the B+ tree in InnoDB is generally 1-3 levels, which can satisfy tens of millions of data storage.

Two, innodb index analysis

In InnoDB, tables are stored in the form of indexes according to the order of the primary key. The tables in this storage method are called index-organized tables.

In the above, we analyzed from different dimensions that InnoDB finally uses the B+ tree index model, so the data is stored in the B+ tree.

Each index corresponds to a B+ tree in InnoDB. Suppose, we have a table with a primary key column of ID, a field k in the table, and a normal index on k.

The table creation statement for this table is:

create table user(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;

The (ID,k) values in the first to fifth rows of the table are (100,1), (200,2), (300,3), (500,5) and (600,6), two trees The example schematic diagram is as follows.

The picture above is: main health index

The picture above is: ordinary index

According to the above index structure description, let's discuss a question: What is the difference between a query based on a primary key index and a normal index?

If the statement is select * from T where ID=500 , that is, the primary key query method, you only need to search the ID B+ tree;

If the sentence is select * from T where k=5 , that is, the common index query method, you need to search the k index tree first, get the value of ID 500, and then search the ID index tree again. This process is called returning to the table. In other words, queries based on non-primary key indexes need to scan one more index tree. Therefore, we should try to use primary key queries in our applications.

In order to maintain the orderliness of the index, the B+ tree needs to do necessary maintenance when inserting new values. Take the above figure as an example, if you insert a new row ID value of 700, you only need to insert a new record after the record in R5. As shown

If the newly inserted ID value is 400, it is relatively troublesome. You need to move the following data logically to vacate the position, as shown in the figure.

To make matters worse, if the data page where R5 is located is full, according to the algorithm of the B+ tree, a new data page needs to be applied at this time, and then some data is moved. This process is called page splitting. In this case, performance will naturally be affected.

In addition to performance, the page splitting operation also affects the utilization of data pages. The data that was originally placed on one page is now divided into two pages, and the overall space utilization is reduced by about 50%. Of course, where there is division, there is merger.

When the utilization rate of two adjacent pages is very low due to data deletion, the data pages will be merged. The process of merging can be regarded as the inverse process of the splitting process.

Three, innodb data page

In the above, we mentioned the concept of data page, data page, it is the basic unit of MySQL management storage space, the size of a page is generally 16KB, and we know that the record is actually stored in the page, if the record is occupied Too much space may also cause row overflow, which will cause a record to be stored in multiple pages.

The essence of a page is a 16KB storage space. InnoDB divides pages into different types for different purposes. The page used to store records is also called data page. Let’s take a look at the page length used to store records. What kind of. The 16KB storage space represented by the data page can be divided into multiple parts. Different parts have different functions. Each part is shown in the figure:

As can be seen from the figure, the storage space of an InnoDB data page is divided into 7 parts, and each part can be divided into several small parts.

Next, use the data page to analyze the innodb index data.

We have used the primary key index as an example. The default size of each page is 16k. When the user data area of our first page is full, we will apply for a new page, which is the second page. Then, when new data is inserted, it will be in the second Store our user data in the page, such as R4 in the figure. The data in the retrieval page that involves this kind of data is similar to a linked list, and the page is also a linked list. When the amount of data is large, the performance is actually relatively poor. , This time we need to introduce the concept of page directory.

When there is a page directory, the retrieval performance will be improved a lot, but if there are many pages, the performance will also have drawbacks. What should we do at this time? At this time, we need to add a linked list of the page directory at the upper level.