索引下推（Index Condition Pushdown，简称ICP）是MySQL优化非聚簇索引查询效率的核心机制，本质是“将部分过滤逻辑从MySQL服务器层，下推到存储引擎层执行”，以减少“回表”次数，降低IO开销。

一、核心作用：减少无效回表

非聚簇索引的叶子节点仅存储“索引列+主键”，当查询条件包含非索引列时，默认流程是：

1.存储引擎通过非聚簇索引，找到所有满足“索引列条件”的主键；
2.用这些主键逐一“回表”查聚簇索引，获取完整数据行；
3.把数据行返回给服务器层，再过滤“非索引列条件”。

而开启ICP后，流程优化为：

• 存储引擎在遍历非聚簇索引时，同时判断“非索引列条件”；
• 只对满足所有条件的记录，才用主键去“回表”；
• 直接过滤掉不满足非索引列条件的记录，减少回表次数。

二、适用场景

ICP仅对非聚簇索引生效，且需满足以下条件：

• 查询类型为 SELECT（不支持UPDATE/DELETE）；
• 过滤条件（WHERE 子句）中，包含“非索引列的条件”（若全是索引列，无需下推，直接用索引过滤即可）；
• 索引类型为二级索引（非聚簇索引），聚簇索引无需回表，故ICP对其无意义。

三、示例理解

1.进行数据的准备

#1.1 创建表
CREATE TABLE user_order (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键（聚簇索引）
    user_id INT NOT NULL,               -- 用户ID
    order_date DATE NOT NULL,           -- 下单日期
    order_amount DECIMAL(10,2) NOT NULL -- 订单金额
);

#1.2 插入测试数据
INSERT INTO user_order (user_id, order_date, order_amount) 
VALUES 
(101, '2024-01-05', 299.00),
(101, '2024-03-12', 599.00),  -- 满足 user_id=101 且 order_date>2024-02-01
(102, '2024-02-20', 199.00),
(101, '2023-12-30', 89.00);   -- user_id=101 但 order_date<2024-02-01	

#1.3 建立联合索引（关键）
#创建 (user_id, order_date) 联合索引（ICP 依赖辅助索引中的“非索引前缀字段”过滤）：
CREATE INDEX idx_user_order ON user_order (user_id, order_date);

2. 触发 ICP 的查询案例

需求：查询 user_id=101 且 order_date>=‘2024-02-01’ 的订单（需用到联合索引的两个字段，且第二个字段 order_date 是过滤条件）。

#2.1 执行查询
EXPLAIN SELECT id, order_amount FROM user_order WHERE user_id = 101 AND order_date >= '2024-02-01';

EXPLAIN SELECT * FROM user_order WHERE user_id = 101 AND order_date like  '2024-02-01%';

3. 对比：有 ICP vs 无 ICP 的执行逻辑

通过 EXPLAIN 查看执行计划（关注 Extra 列是否显示 Using index condition，表示触发 ICP）：

3.1 有 ICP（MySQL 5.6+ 默认开启）

执行步骤：

1. 存储引擎遍历 idx_user_order 索引，先定位 user_id=101 的所有索引条目（共 3 条：(101,‘2024-01-05’)、(101,‘2024-03-12’)、(101,‘2023-12-30’)）。

2. 存储引擎直接过滤：检查每条索引条目中的 order_date 是否 >= ‘2024-02-01’，只保留 1 条符合条件的索引条目 (101,‘2024-03-12’)。

3. 用保留的索引条目（含主键 id）回表查询 order_amount，返回给服务器层。

4. 服务器层直接返回结果（无需二次过滤）。

执行计划特征：EXPLAIN 结果中 Extra 列显示 Using index condition。

3.2 无 ICP（模拟关闭 ICP 的情况）

关闭 ICP 命令（测试用，用完可恢复）：

SET optimizer_switch = 'index_condition_pushdown=off';

执行步骤：

1. 存储引擎遍历 idx_user_order 索引，定位 user_id=101 的所有 3 条索引条目，不做过滤，直接将这 3 条索引条目（含主键 id）返回给服务器层。
2. 服务器层接收 3 条数据后，过滤 order_date >= ‘2024-02-01’ 的条件，只保留 1 条符合条件的数据。
3. 用保留的主键 id 回表查询 order_amount，返回结果。

执行计划特征：EXPLAIN 结果中 Extra 列无 Using index condition，且会多一步服务器层过滤。

4. 关键结论

•	ICP 的核心价值：减少“存储引擎 → 服务器层”的数据传输量（案例中从 3 条减少到 1 条），尤其当符合“索引前缀条件”的数据量很大时，优化效果更明显。

•	触发条件：必须使用辅助索引（非聚簇索引），且过滤条件包含“索引非前缀字段”（如联合索引 (a,b) 中的 b）。

•	查看是否触发：执行 EXPLAIN 看 Extra 列是否有 Using index condition。

四、关键说明

控制开关：MySQL 5.6及以上默认开启ICP，可通过

SET optimizer_switch = 'index_condition_pushdown=on/off'; 手动开启/关闭。

局限性：若非索引列条件依赖函数（如 DATE_FORMAT(create_time, ‘%Y’) = ‘2024’），或查询使用 DISTINCT/GROUP BY，ICP可能不生效。