What is index pushdown?

takeaway

The article was published in the wechat public number [code ape technology column] mp.weixin.qq.com/s/0-4LZwNUr…
Index condition pushdown (ICP) is introduced on Mysql5.6 to optimize queries.
In the absence of ICP, when a query is performed using a non-primary key index (also known as a normal index or secondary index), the storage engine retries data from the index and returns it to the MySQL server, which then determines whether the data meets the criteria.
In the case of using ICP, if there is some indexed column judgment conditions, MySQL server pass this part of the judgment condition to the storage engine, and then by the storage engine through judging index meets the requirements for the MySQL server transfer only when the index accords with a condition the data retrieved is returned to the MySQL server.
Push optimization based on index conditions can reduce the number of times the storage engine queries the base table, and also reduce the number of times the MySQL server receives data from the storage engine.

Before you start, prepare a user table (user), where the main fields are ID, name, age, address. Create a federated index (name, age).
Suppose you have a requirement to match all users whose first name is Chen. The SQL statement is as follows:

SELECT * from user where  name like Chen '%'
Copy the code

In accordance with the “best left prefix” principle, the query is performed using the federated index (name, age), which is definitely better than the full table scan.
So the question is, what if there are other conditions? Select * from user where name = Chen and age = 20; select * from user where age = 20;

SELECT * from user where  name like Chen '%' and age=20
Copy the code

How should this SQL statement be executed? The following looks at versions before and after Mysql5.6.

The age field will be ignored and the query will be conducted directly by name. In the tree of (name,age), two results will be found, with id 2 and 1 respectively. Then, the obtained ID value will be returned to the table again and again, so this process needs to return to the table twice.

In version 5.6, the index pushdown optimization was added. The process is shown below:

InnoDB does not ignore the age field, but checks whether the age is equal to 20 inside the index. If the age is not equal to 20, InnoDB will skip it directly. Therefore, InnoDB only matches one record in the index tree (name,age), and then uses this ID to query all data in the primary key index tree.

Of course, the above analysis is only in principle, we can actual analysis, so Chen installed Mysql5.6 version of Mysql, parse the above statement, as follows:

According to the explain parsing result, it can be seen that the value of Extra is Using index condition, indicating that index pushdown has been used.

The optimization of index pushdown on non-primary key indexes can effectively reduce the number of back tables and greatly improve the query efficiency.
You can disable index pushdown by using the following command. Configuration file modifications are not covered, after all, why disable such a great feature:

set optimizer_switch='index_condition_pushdown=off';
Copy the code