An article to understand what is MySQL Index Pushdown (ICP)

1. Introduction

ICP (Index Condition Pushdown) is a query optimization strategy introduced in MySQL 5.6. It pushes the index condition check originally done by the server layer down to the storage engine layer to reduce the number of table returns and storage engine accesses, thereby improving query efficiency.

2. Principle

To understand how ICP works, let's first understand how MySQL queries without 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 as follows:

• Read index records (not complete row records);
• 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.
  

III. Practice

First create a table and insert records

CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT COMMENT "Primary key",
name varchar(32) COMMENT "Name",
city ​​varchar(32) COMMENT "city",
age int(11) COMMENT "age",
primary key(id),
key idx_name_city(name, city)
)engine=InnoDB default charset=utf8;

insert into user(name, city, age) values("ZhaoDa", "BeiJing", 20),("QianEr", "ShangHai", 21),("SunSan", "GuanZhou", 22), ("LiSi", "ShenZhen", 24), ("ZhouWu", "NingBo", 25), ("WuLiu", "HangZhou", 26), ("ZhengQi", "NanNing", 27), ("WangBa", "YinChuan", 28), ("LiSi", "TianJin", 29), ("ZhangSan", "NanJing", 30), ("CuiShi", "ZhengZhou", 65), ("LiSi", "KunMing", 29), ("LiSi", "ZhengZhou", 30);

Check the table records

mysql> select * from user;
+----+----------+-----------+------+
| id | name | city | age |
+----+----------+-----------+------+
| 1 | ZhaoDa | Beijing | 20 |
| 2 | QianEr | ShangHai | 21 |
| 3 | SunSan | GuanZhou | 22 |
| 4 | LiSi | ShenZhen | 24 |
| 5 | ZhouWu | NingBo | 25 |
| 6 | WuLiu | HangZhou | 26 |
| 7 | ZhengQi | NanNing | 27 |
| 8 | WangBa | YinChuan | 28 |
| 9 | LiSi | TianJin | 29 |
| 10 | ZhangSan | NanJing | 30 |
| 11 | CuiShi | ZhengZhou | 65 |
| 12 | LiSi | KunMing | 29 |
| 13 | LiSi | ZhengZhou | 30 |
+----+----------+-----------+------+
13 rows in set (0.00 sec)

Note that a joint index (name, city) is created in this table. Suppose we want to query the following statement:

select * from user where name="LiSi" and city like "%Z%" and age > 25;

3.1 Not using index pushdown

Without using index pushdown, according to the "leftmost match" principle of the joint index, only the name column can use the index. The city column cannot use the index because it is a fuzzy match. The execution process at this time is as follows:

1. The storage engine finds the record with the name value of LiSi based on the (name, city) joint index, a total of 4 records;
2. Then, based on the ID values ​​in these four records, the table is scanned one by one to retrieve complete row records from the clustered index and return these records to the server layer.
3. The server layer receives these records and filters them according to the conditions name="LiSi" and city like "%Z%" and age > 25, and finally leaves the record ("LiSi", "ZhengZhou", 30).
   

Let’s draw a picture:

Index condition pushdown is not used

3.2 Using Index Pushdown

When using index pushdown, the execution process is as follows:

• The storage engine finds 4 records with name='LiSi' based on the (name, city) joint index.
• Because the joint index contains the city column, the storage engine directly filters the joint index by city like "%Z%". After filtering, 2 records remain;
• According to the ID values ​​of the filtered records, the table is scanned one by one, complete row records are retrieved from the clustered index, and these records are returned to the server layer;
• The server layer filters the rows again based on the other condition of the WHERE statement (age > 25), and finally leaves only the record ("LiSi", "ZhengZhou", 30).
  

Let’s draw a picture:

Using index condition pushdown

In addition, you can also see from the execution plan that index pushdown is used (Using index condition is displayed in Extra)

mysql> explain select * from user where name="LiSi" and city like "%Z%" and age > 25;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+
| 1 | SIMPLE | user | NULL | ref | idx_name_city | idx_name_city | 99 | const | 4 | 7.69 | Using index condition; Using where |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

IV. Conditions of Use

• Can only be used for range, ref, eq_ref, 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);
  

Tip: 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 complete row records have been loaded into the cache, so index pushdown is meaningless.

• 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.
  

5. 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 is the end of this article about what is MySQL Index Pushdown (ICP). For more information about MySQL Index Pushdown (ICP), please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!