什么是索引下推

索引下推（Index Condition Pushdown，简称ICP）是一种数据库查询优化技术，它利用了数据库引擎中的索引和过滤条件，将部分过滤工作下推到存储引擎层面进行处理，从而减少不必要的数据读取和传输。

在传统的查询执行过程中，数据库引擎首先根据索引定位到符合过滤条件的数据行，并将这些行读取到内存中，然后再进行进一步的过滤操作。而索引下推则在这一步骤中尽可能地将过滤操作下推到存储引擎层面，避免将不符合条件的数据行读取到内存中。

具体实现方式可以是通过存储引擎提供的接口或者钩子函数，让存储引擎在读取索引页时就进行额外的过滤操作。

通过索引下推，数据库系统可以在存储引擎层面根据索引和过滤条件提前过滤掉不符合条件的数据，减少了需要传递给查询引擎的数据量和内存消耗。这样可以大大减少磁盘 I/O 和数据传输的开销，提升查询性能和整体系统效率。

需要注意的是，索引下推并不是对所有类型的查询都适用，它更适用于复杂查询条件、多列条件的查询中，能够有效地减少不必要的数据读取和传输。

介绍索引下推

索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是在 MySQL 5.6 针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB，本篇的内容只基于 INNODB。
在讲这个技术之前你得对mysql架构有一个简单的认识，见下图：

• MySQL 服务层：也就是 SERVER 层，用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等。

• MySQL 存储引擎层：按照 MySQL 服务层下发的请求，通过索引或者全表扫描等方式把数据上传到 MySQL 服务层。

• MySQL 索引扫描：根据指定索引过滤条件，遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。

• MySQL 索引过滤：通过索引扫描并且基于索引进行二次条件过滤后再回表。

实战数据准备

delete from user1;
drop table user1;

CREATE TABLE `user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) NOT NULL,
	`address` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `user1` (`name`, `age`, `address`) VALUES
('Alice', 40, 'address1'),
('Amy', 23, 'address2'),
('Tom', 18, 'address3'),
('Mike', 22, 'address4');

explain SELECT * FROM user1 WHERE name LIKE 'A%' and age = 23;

# 查看索引下推是否开启
select @@optimizer_switch
# 开启索引下推
set optimizer_switch="index_condition_pushdown=on";
# 关闭索引下推
set optimizer_switch="index_condition_pushdown=off";

索引下推实战

不使用索引下推实现

Explain SELECT * FROM user1 WHERE name LIKE 'A%' and age = 40;

使用索引下推实现

Explain SELECT * FROM user1 WHERE name LIKE 'A%' and age = 40;

索引下推的使用条件

• ICP目标是减少全行记录读取，从而减少IO 操作，只能用于非聚簇索引。聚簇索引本身包含的表数据，也就不存在下推一说。

• 只能用于range、 ref、 eq_ref、ref_or_null访问方法；

• where 条件中是用 and 而非 or 的时候。

• ICP适用于分区表。

• ICP不支持基于虚拟列上建立的索引，比如说函数索引

• ICP不支持引用子查询作为条件。

• ICP不支持存储函数作为条件，因为存储引擎无法调用存储函数