Understanding MySQL index pushdown in five minutes

If you hear terms like "MySQL 5.6" and "index optimization" during an interview, you should immediately understand that this question is about "index pushdown".

What is index pushdown?

Index Condition Pushdown (ICP) is a new feature of MySQL 5.6. It can reduce the number of table queries and improve query efficiency.

The principle of index pushdown optimization

Let's first take a brief look at the general architecture of MySQL:

The MySQL service layer is responsible for SQL syntax parsing, generating execution plans, etc., and calling the storage engine layer to perform data storage and retrieval.

The push-down of index push actually means that some of the tasks that are the responsibility of the upper layer (service layer) are handed over to the lower layer (engine layer) for processing.

Let's take a closer look at the MySQL query without using ICP:

• The storage engine reads the index record;
• Locate and read the complete row record based on the primary key value in the index;
• The storage engine passes the record to the server layer to check whether the record meets the WHERE condition.

When using ICP, the query process is:

• The storage engine reads the index record (not the complete row record);
• Determine whether the WHERE condition can be checked using the columns in the index. If the condition is not met, process the next row of index records.
• If the conditions are met, use the primary key in the index to locate and read the complete row record (this is called table return);
• The storage engine passes the record to the server layer, which checks whether the record satisfies the rest of the WHERE condition.

Specific practice of index pushdown

The theory is rather abstract, so let’s put it into practice.

Use a user table tuser and create a joint index (name, age) in the table.

If there is a requirement now: retrieve all users in the table whose first name is Zhang and whose age is 10 years old. Then, the SQL statement is written like this:

select * from tuser where name like '张%' and age=10;

If you understand the leftmost matching principle of indexes, you will know that this statement can only be used when searching the index tree, and the id of the first record that meets the conditions is 1.

So what are the next steps?

No ICP used

Before MySQL 5.6, the storage engine finds the primary key ID (1, 4) of name likelike '張%' through the joint index, scans the table one by one, removes the clustered index to find the complete row record, and the server layer age=10進行篩選.

Let's look at the schematic diagram:

It can be seen that the table needs to be returned twice, which wastes the other field age of our joint index.

Using ICP

After MySQL 5.6, the storage engine finds name likelike '張% ' based on the (name, age) combined index. Since the combined index contains the age column, the storage engine directly filters the combined index according to age=10 . Scan the table one by one according to the filtered data.

Let's look at the schematic diagram:

You can see that the table was only returned once.

In addition, we can also look at the execution plan and see Using index condition in Extra column, which means that index pushdown is used.

+----+-------------+-------+------------+-------+---------------+-----------+----------+------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+----------+------+------+------+----------+-----------------------+
| 1 | SIMPLE | tuser | NULL | range | na_index | na_index | 102 | NULL | 2 | 25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+----------+------+------+------+----------+-----------------------+

Index pushdown usage conditions

• Can only be used with range , ref , eq_ref , and ref_or_null access methods;
• Can only be used for InnoDB and MyISAM storage engines and their partitioned tables;
• For the InnoDB storage engine, index pushdown only applies to secondary indexes (also called auxiliary indexes);

The purpose of index pushdown is to reduce the number of table returns, that is, to reduce IO operations. For InnoDB's clustered index, the data and index are together, and there is no such thing as table return.

• Conditions that reference subqueries cannot be pushed down;
• Conditions that reference stored functions cannot be pushed down because the storage engine cannot call stored functions.

Related system parameters

Index condition pushdown is enabled by default, and you can use the system parameter optimizer_switch to control whether it is enabled.

View the default status:

mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

Toggle state:

set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";

Summarize

This article ends here. I hope it can be helpful to you. I also hope that you can pay more attention to more content on 123WORDPRESS.COM!