MySQL的索引条件下推（Index Condition Pushdown，ICP）是一种优化技术，用于提升查询性能，尤其是在带有范围条件或复杂过滤条件的查询中。它的主要作用是在存储引擎层级过滤数据，而不是将所有数据返回给MySQL服务器层再进行过滤，从而减少了数据量的传输和处理。
1：工作原理
在没有ICP的情况下，MySQL会将索引范围扫描的数据返回给服务器层，然后在服务器层进行非索引字段的过滤。而有了ICP，MySQL在存储引擎中应用了更多的过滤条件，从而减少传递给服务器层的数据量。
工作流程：
- MySQL首先在存储引擎中进行索引范围扫描，选取符合索引条件的行。
- 在存储引擎中应用额外的过滤条件（ICP条件），筛选符合条件的行。
- 将过滤后的结果返回给服务器层
2：使用条件
ICP主要适用于复合索引查询中，尤其是在存在WHERE条件时。查询涉及的字段既包含索引字段，也包含非索引字段时，ICP会更加有效。索引条件下推可以应用在SELECT、UPDATE和DELETE等带有WHERE条件的查询中
3： 优势
- 减少服务器层过滤：通过在存储引擎中进行更多的过滤操作，减少了服务器层的数据过滤压力。
- 降低I/O操作：减少了需要传输的数据量，进而减少了磁盘I/O和网络I/O。
- 提升查询性能：尤其在范围查询中，ICP能够显著减少查询时间。
4： 案例
我们有一个user表。
mysql> show create table user;
+——-+———————————————-+
| Table | Create Table |
+——-+———————————————-+
| user | CREATE TABLE user (
id int NOT NULL AUTO_INCREMENT,
user_id int NOT NULL DEFAULT ‘0’ COMMENT ‘ID’,
user_name varchar(16) NOT NULL DEFAULT ”,
zip_code char(6) NOT NULL DEFAULT ‘0’,
address varchar(64) NOT NULL DEFAULT ”,
create_time int NOT NULL DEFAULT ‘0’,
PRIMARY KEY (id),
UNIQUE KEY idx_user_id (user_id),
KEY idx_zip_code_user_name (zip_code,user_name)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |
+——————————————————–+
1 row in set (0.01 sec)我们写一个存储过程，往这个表里面插入100万条数据。
mysql>
DELIMITER $$ CREATE PROCEDURE insert_user(in num_limit int,in rand_limit int) BEGIN DECLARE i int default 1; DELIMITER $$ CREATE PROCEDURE insert_user(in num_limit int) BEGIN DECLARE i int default 1; DECLARE user_id int default 0; DECLARE user_name varchar(16) default ''; DECLARE zip_code char(6) default ''; DECLARE address varchar(64) default ''; DECLARE time int default 0; WHILE i<=num_limit do set user_id = i+1; set user_name = substring(MD5(RAND()),1,6); set zip_code = FLOOR(rand()*1000); set address = substring(MD5(RAND()),1,6); set time=unix_timestamp(); INSERT into user values (null,user_id,user_name,zip_code,address,time); set i = i + 1; END WHILE; END; $$
mysql> call insert_user(1000000);
Query OK, 1 row affected (3 min 36.46 sec)mysql> select count(1) from user;
+———-+
| count(1) |
+———-+
| 1000000 |
+———-+
1 row in set (0.04 sec)看一下索引下推条件有没有开启:
mysql> SELECT @@optimizer_switch;
+—————————————————————————————–+
| @@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,
use_invisible_indexes=off,
skip_scan=on,hash_join=on,
subquery_to_derived=off,
prefer_ordering_index=on,
hypergraph_optimizer=off,
derived_condition_pushdown=on |
+—————————————————————————————–+
1 row in set (0.00 sec)我们主要看到index_condition_pushdown=on，那就说明开启索引条件下推。
下面我们执行一个sql:
mysql> explain select * from user where zip_code=’263′ and user_name like ‘%a%’;
+—-+————-+——-+————+——+————————+————————+———+——-+——+———-+———————–+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+————————+————————+———+——-+——+———-+———————–+
| 1 | SIMPLE | user | NULL | ref | idx_zip_code_user_name | idx_zip_code_user_name | 18 | const | 1091 | 11.11 | Using index condition |
+—-+————-+——-+————+——+————————+————————+———+——-+——+———-+———————–+
1 row in set, 1 warning (0.00 sec)当使用索引条件下推时，EXPLAIN 输出在Extra列显示Using index condition。 它不显示Using index，因为当必须读取完整的表行时，这并不适用。
MySQL可以使用索引扫描 zipcode=’263′ 的人。 第二部分（user_name LIKE ‘%a%’）不能用于限制必须扫描的行数，
如果没有索引条件下推，这个查询必须检索所有 zipcode=’263′ 的人的完整表行，也就是需要回表，然后去比对是否满足user_name LIKE ‘%a%’条件。
有索引条件下推，MySQL 在读取完整表行会在idx_zip_code_user_name这个索引里面检查条件user_name LIKE ‘%a%’ 部分。 如果user_name不匹配，那么就不需要去回表。
参考链接：
https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html