MySQL index drop down

Hello, I’m Dabin

Today I will share with you the MySQL index push down.

What is index push down

Index Condition Pushdown (ICP) is also called Index Condition Pushdown.

Index push-down is a new feature added to MySQL5.6 to optimize queries for data.

Prior to MySQL5.6, when queries were made using non-primary key indexes, the storage engine queried the data through the indexes and then returned the results to the MySQL Server layer to determine whether the conditions were met.

In MySQL5.6 and above, you can use the index push-down feature. If there are index columns as the criteria, the MySQL server will pass the criteria to the storage engine, and the storage engine will filter out the indexes that meet the criteria. That is, at the storage engine layer, the indexes that do not meet the criteria will be filtered out according to the criteria, and then query the results back to the table. Return the result to MySQL Server.

It can be seen that with index push-down optimization, under certain conditions, the storage engine layer will filter data before the query back to the table, which can reduce the number of query back to the table.

For example

User_info = name, level, weapon (weapon); weapon (weapon);

Select * from user where the first word of the user name is “large” and level is 1;

SELECT * FROM user_info WHERE name LIKE"Big %"AND level = 1;
Copy the code

So how does this SQL actually execute?

The following points for analysis.

Take a look at previous versions of MySQL5.6.

Earlier versions of MySQL5.6 did not have index push-downs, which are executed as follows:

MySQL > select * from table where id = 1; MySQL > select * from table where ID = 1; MySQL > select * from table where ID = 1; Check the level field in MySQL Server layer. The whole process needs to return to the table twice.

Then take a look at the execution of MySQL5.6 and above, as shown below.

Compared with the version before 5.6, the index push-down optimization is added. During index traversal, the index fields in the index are judged first and the index entries that do not meet the conditions are filtered out, that is, the index entries are judged whether level is equal to 1. If level is not equal to 1, the index entries are skipped. Therefore, only one record is matched in the index tree (name, level), and then the table is queried with the corresponding ID (id=1), and the whole process is returned to the table once.

You can use Explain to see if index push-downs are used, and when the value of the Extra column is Using index condition, index push-downs are used.

conclusion

As you can see from the above example, using index push-down can effectively reduce the number of times the table is returned in some scenarios, thus improving query efficiency.

Code word is not easy, if you think it is helpful, you can click a “like” to encourage!