Analyze what is Mysql index pushdown? Does it help optimization?

Mysql performance optimization: What is index pushdown?

Introduction

Index condition pushdown (index condition pushdown), referred to as ICP, was launched in the Mysql5.6 version and is used to optimize queries.

Without using ICP, when querying using a non-primary key index (also called a normal index or a secondary index), the storage engine retrieves the data through the index, and then returns it to the MySQL server, and the server then determines Whether the data meets the conditions.

When using ICP, if there are judgment conditions for certain indexed columns, the MySQL server will pass this part of the judgment conditions to the storage engine, and then the storage engine will judge whether the index meets the judgment conditions passed by the MySQL server. Conditions, only when the index meets the conditions will the data be retrieved and returned to the MySQL server.

Index condition pushdown optimization can reduce the number of times the storage engine queries the underlying table, and can also reduce the number of times the MySQL server receives data from the storage engine.

[Recommended learning: mysql video tutorial]

开游

Prepare a user table before starting (user), the main fields are: id, name, age, address. Create a joint index (name, age).

Suppose there is a requirement to match all users whose first name is Chen. The SQL statement is as follows:

SELECT * from user where  name like '陈%'

According to the principle of "best left prefix", a joint index is used here ( name, age) is queried, and the performance is definitely higher than that of a full table scan.

The question is, what if there are other conditions? Suppose there is another requirement to match users whose first name is Chen and whose age is 20 years old. The SQL statement at this time is as follows:

SELECT * from user where  name like '陈%' and age=20

How should this SQL statement be executed? The following is an analysis of versions before and after Mysql5.6.

Versions before Mysql5.6

Versions before 5.6 do not have the optimization of index pushdown, so the execution process is as follows:

will ignore the age field and query directly through name. Two results are found on the tree of (name, age). The ids are 2 and 1 respectively, and then take the obtained The id value is returned to the table for query again and again, so this process requires returning to the table twice.

Mysql5.6 and later versions

The 5.6 version adds the optimization of index pushdown. The execution process is as follows:

InnoDB does not ignore the age field, but determines whether age is equal to 20 within the index. Records that are not equal to 20 are skipped directly, so only matching is done in the (name, age) index tree. When a record is reached, take this ID and go back to the table in the primary key index tree to query all the data. This process only needs to go back to the table once.

Practice

Of course, the above analysis is only in principle, we can analyze it in practice, so Chen installed the Mysql5.6 version of Mysql and analyzed the above Statement, as shown below:

# According to the explain parsing results, it can be seen that the value of Extra is Using index condition, which means that index pushdown has been used.

Summary

The optimization of index pushdown on non-primary key indexes can effectively reduce the number of table returns and greatly improve query efficiency.

To turn off index pushdown, you can use the following command. The modification of the configuration file will not be described anymore. After all, why should such an excellent function be turned off:

set optimizer_switch='index_condition_pushdown=off';

The above is the detailed content of Analyze what is Mysql index pushdown? Does it help optimization?. For more information, please follow other related articles on the PHP Chinese website!