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

This article mainly explains the "Mysql index push down what is the role", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "what is the role of Mysql index push" bar!

Guide reading

Index push-down (index condition pushdown), referred to as ICP, is introduced on the version of Mysql5.6 to optimize queries.

In the case of not using ICP, when using non-primary key index (also known as ordinary index or secondary index) for query, 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.

In the case of using ICP, if there are some conditions for judging the indexed column, the MySQL server passes this part of the judgment condition to the storage engine, and then the storage engine determines whether the index meets the conditions passed by the MySQL server, and only when the index meets the conditions will the data be retrieved and returned to the MySQL server.

Push-down optimization under index conditions can reduce the number of times the storage engine queries the underlying tables, and it can also reduce the number of times that the MySQL server receives data from the storage engine.

[recommended study: mysql video tutorial]

Start playing.

Before you begin, prepare a user table (user). The main fields are: id, name, age, address. Create a federated index (name,age).

Suppose there is a requirement to match all users whose first name is Chen, and the sql statement is as follows:

SELECT * from user where name like 'Chen%'

According to the principle of "best left prefix", a federated index (name,age) is used to query, 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 for a user whose first word of name is Chen and the age is 20 years old. The sql statement is as follows:

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

How should this sql statement be executed? The previous and later versions of Mysql5.6 are analyzed below.

Previous versions of Mysql5.6

In versions prior to 5.6, there is no index push down for this optimization, so the process is as follows:

The age field is ignored and queried directly through name. Two results are found on the tree of (name,age). The id is 2 and the result is 1, and then the id value is returned to the table again and again, so the process needs to return to the table twice.

Mysql5.6 and later version

Version 5.6 adds the optimization of index push-down, as shown in the following figure:

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 one record is matched in the index tree (name,age). At this time, take the id to the primary key index tree to query all the data. This process only needs to return to the table once.

Practice

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

According to the explain parsing result, you can see that the value of Extra is Using index condition, indicating that index push-down has been used.

Thank you for your reading, the above is the "Mysql index push down what is the role of" the content, after the study of this article, I believe you have a deeper understanding of the Mysql index push down what the role of this problem, the specific use of the need for you to practice and verify. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.