MySQL performance optimization index pushdown

Index condition pushdown (ICP) is introduced in MySQL 5.6 and is used to optimize queries.

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

When using ICP, if there are judgment conditions for certain indexed columns, the MySQL server passes these judgment conditions to the storage engine, and then the storage engine determines whether the index meets the conditions passed by the MySQL server. 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 base table and the number of times the MySQL server receives data from the storage engine.

Start masturbating

Before we start, we need to prepare a user table (user), in which the main fields are: id, name, age, and address. Create a joint index (name, age).

Assume 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", the joint index (name, age) is used for query here, and the performance is definitely higher than that of full table scan.

The question is, what if there are other conditions? Assume 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 MySQL 5.6.

MySQL versions prior to 5.6

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

The age field will be ignored, and the query will be performed directly through name. Two results will be found in the (name, age) lesson tree, with ids 2 and 1 respectively. Then the obtained id values ​​will be used to query the table again and again. Therefore, this process requires returning to the table twice .

Mysql5.6 and later versions

Version 5.6 adds the index pushdown optimization, and the execution process is as follows:

InnoDB does not ignore the age field. Instead, it determines whether age is equal to 20 within the index and directly skips records that are not equal to 20. Therefore, only one record is matched in the (name, age) index tree. At this time, the id is used to query all the data in the primary key index tree. This process only requires one table return .

practice

Of course, the above analysis is only in principle. We can analyze it in practice. Therefore, Chen installed MySQL version 5.6 and parsed the above statement, as shown below:

According to the explain analysis result, we can see that the value of Extra is Using index condition, which means that index pushdown has been used.

Summarize

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. I will not explain the modification of the configuration file anymore. After all, why turn off such an excellent function?

set optimizer_switch='index_condition_pushdown=off';

This is the end of this article about MySQL performance optimization and index pushdown. For more relevant MySQL index pushdown content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!