Shulou(Shulou.com)05/31 Report--

This article focuses on "how to understand MySQL index push-down", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "how to understand MySQL index push down"!

-mind map-

Table return operation

For the database, as long as the index is involved, it is impossible to go back to the table operation. Of course, this is also the basis of the top tone of what we are talking about today.

When it comes to returning to the table, we need to start with the index. Don't worry, it won't be a long speech. I'll just talk about the primary key index and the general index here, in order to give you an understanding of the table return operation. If you are familiar with the table return operation, you can skip this paragraph.

Here we only use the Innodb storage engine as the object of discussion.

Primary key index

The underlying data storage of the primary key index is implemented through the B+ tree. To put it simply, all nodes except leaf nodes store primary key values. The whole row of data is stored on the leaf node.

The general structure is shown in the following figure.

Non-primary key index

With the exception of primary key indexes, other indexes are called non-primary key indexes. Unlike primary key indexes, the leaf nodes of non-primary key indexes store the value of the primary key.

So let's go back to the original question, what is a table return operation?

When we look for a row of data on a non-primary key index, the way to find it is to search the non-primary key index tree first, get the corresponding primary key value, and then find the corresponding row data on the primary key index tree.

This operation is called a table return operation.

Well, here you should know what a table return operation is. To put it simply, get the corresponding primary key value on the non-primary key index tree, and then go back to the primary key index to find the corresponding row data.

The prerequisite for this is that the field you are looking for does not exist on the non-primary key index tree.

Low version operation

After talking about the back-to-table operation, let's return to the topic of this article-index push-down.

In fact, there is no index push-down feature before Mysql version 5.6, and this optimization item has been added since version 5.6. So before pulling out the index, it's better to review how it is handled without this feature.

Let's explain it with a real example.

Here is a user table user, which records the user's name, sex, height, age and other information. Id is the self-incrementing primary key in the table, and (name,sex) is the federated index. Here, 1 for male and 2 for female. Now we need to find information about all the men surnamed Wang.

SQL is easy to implement:

But how does it work?

According to the leftmost prefix principle of the joint index, when we find the first value that meets the condition on the non-primary key index tree, we use the primary key value recorded by the leaf node to go back to the primary key index tree to find the corresponding row data, and then compare whether it is the gender we are currently looking for.

The whole principle can be represented by the following diagram.

You see, in the lower version, every piece of data needs to be returned to the table, increasing the number of tree searches. If you encounter a large amount of data you are looking for, there must be a lack of performance.

High version operation

After talking about the low-version operation, let's go back to the topic of this article-index push-down.

Know the pain point, so how to solve it. It is very simple to return to the table only when the conditions are met. Combined with our example, when the gender sex = 1 is satisfied, then go back to the table to look it up. In this way, you might need to look up the table 4 times, but now it may only take 2 times.

So in essence, index push-down is to go back to the table only if it meets the conditions, judge the fields contained in the index first, and skip those that do not meet the conditions. Unnecessary table return operations are reduced.

Summary

Table return operation

When the field you are looking for is not on the non-primary key index tree, you need to get the corresponding row data on the primary key index through the primary key value of the leaf node, which is called back table operation.

Index push-down

Index push-down mainly reduces unnecessary table return operations. For the data found, first filter out those that do not meet the criteria, and then look for the rest on the primary key index tree.

At this point, I believe you have a deeper understanding of "how to understand MySQL index push-down". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.