MySQL index push down, so simple!

Hello everyone, my name is Dabin~

Today, I will share with you MySQL index pushdown.

What is index pushdown

Index condition pushdown, also called index pushdown, the full English name Index Condition Pushdown , or ICP for short.

Index pushdown is a new feature added in MySQL 5.6 to optimize data query.

Before MySQL 5.6, when querying by using a non-primary key index, the storage engine queries data through the index, and then returns the result to the MySQL server layer. The server layer determines whether the condition is .

In MySQL 5.6 and above, the index pushdown feature can be used. When there is an indexed column as the judgment condition, the MySQL server will pass this part of the judgment condition to the storage engine, and then the storage engine will filter out the index item that the MySQL server's passing condition, that is, the storage engine layer filters according to the index condition Drop the index items that do not meet the conditions, and then return the table query to get the result, and return the result to the MySQL server.

It can be seen that has the optimization of index pushdown. Under certain conditions, the storage engine layer will filter the data before returning the table query, which can reduce the number of times the storage engine returns the table .

for example

Suppose there is a user information table user_info, there are three fields name, level, weapon (equipment), establish a joint index (name, level) , the initial data of the user_info table is as follows:

id	name	level	weapon
1	Dabin	1	keyboard
2	Genie	2	Yuanhong
3	Wei Zhuang	3	shark tooth
4	sledgehammer	4	hammer

If you need to match users whose name is the first character "big" and level is 1, the SQL statement is as follows:

SELECT * FROM user_info WHERE name LIKE "大%" AND level = 1;

So how exactly will this SQL be executed?

The following situations are analyzed.

Let's take a look at the version before MySQL 5.6.

As mentioned earlier, there is no index pushdown in versions before MySQL 5.6. The execution process is as follows:

The query condition name LIKE is not an equivalent match. According to the leftmost matching principle , only name is used in the (name, level) index tree to match, two records (id 1 and 4) are found, and the ids of the two records are obtained respectively. Return the table query, and then return the result to the MySQL server, and judge the level field at the MySQL server layer. The whole process of needs to return to .

Then look at the execution process of MySQL 5.6 and above, as shown below.

Compared with the previous version 5.6, there are more optimizations for index pushdown. During the index traversal process, the field in the index is judged first, and the index items that do not meet the conditions are filtered out. is to judge whether the level is equal to 1 , if level is not 1, skip it directly. Therefore, only one record is matched in the (name, level) index tree, and then the id (id=1) corresponding to this record is returned to the table to query all the data, and the whole process of is returned to the table once to .

You can use explain to check whether index pushdown is used. When the value of the Extra column is Using index condition , it means that index pushdown is used.

Summarize

As can be seen from the above example, the use of index can effectively reduce the number of returns to the table in some , thereby improving query efficiency.

It is not easy to code words, if you think it is helpful to you, you can it and to encourage it!