MySQL helps you understand index pushdown in seconds

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

1. The principle of index push-down 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索引下推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.
  

2. 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 the index, then you 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?

1. Not using ICP

Before MySQL 5.6 , the storage engine finds the primary key id (1, 4) of name like '张%' through the joint index, scans the table one by one, removes the clustered index to find the complete row record, and server layer filters the data according to 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.

2. Use ICP

After MySQL 5.6 , the storage engine finds name like '張% ' 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 |
+----+-------------+-------+------------+-------+---------------+-----------+----------+------+------+------+----------+-----------------------+

3. 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,數據和索引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.
  

Relevant 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";

This is the end of this article about understanding MySQL index pushdown in a few minutes. For more relevant MySQL index pushdown content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!