MySQL 索引下推 ICP 的优化原理与适用条件

ICP 是什么

ICP（Index Condition Pushdown，索引条件下推）是 MySQL 5.6 版本引入的一种查询优化技术。它的核心思想是：将部分 WHERE 条件从服务层（Server Layer）下推到存储引擎层（Storage Engine Layer），在索引遍历过程中提前过滤不符合条件的记录，从而减少回表次数和 IO 开销。

传统模式下，即使索引能定位到范围，但索引字段后面的条件（非索引列或无法直接利用索引排序的条件）必须在回表后由服务层判断。ICP 改变了这一顺序，让存储引擎在索引扫描时直接利用这些条件进行过滤。

优化原理：对比无 ICP 的执行流程

假设一张表 t 有联合索引 (a, b)，执行查询：

SELECT * FROM t WHERE a > 10 AND b LIKE '%abc%';

a > 10 可以使用索引范围扫描，但 b LIKE '%abc%' 由于是左模糊，无法直接通过索引 B+Tree 快速定位。以下是无 ICP 和有 ICP 的两种执行路径。

无 ICP 模式（MySQL 5.6 之前）

1. 存储引擎：通过索引 (a, b) 定位到所有 a > 10 的索引记录（连续或非连续范围）。
2. 服务层：对每条索引记录，回表（访问聚簇索引）获取完整行数据。
3. 服务层：对每一行数据应用 b LIKE '%abc%' 过滤条件，丢弃不符合的行。
4. 返回结果：最终满足所有条件的行。

问题：即使大部分索引记录中的 b 不满足条件，仍需要回表读取完整行，产生大量不必要的随机 IO。

启用 ICP 模式

1. 存储引擎：通过索引 (a, b) 扫描 a > 10 的索引记录时，同时检查索引中 b 列的值（虽然 b 是左模糊，但索引中存储了 b 的完整值，可以使用 LIKE '%abc%' 进行字符串匹配）。
2. 存储引擎：只有在索引记录中 b 满足条件时，才进行回表获取完整行。
3. 服务层：此时无需再做 b 的过滤（但可能仍需处理其他未下推的条件），直接返回结果。

改进：回表次数大大减少，因为只有满足 b LIKE '%abc%' 的索引记录才会触发回表。数据库术语中，这种“用索引中的列值提前过滤”的操作就是条件下推。

流程图对比

无ICP:
  Index Scan (a > 10) -> 每条记录回表 -> Server过滤b条件
  ↑                               ↑
  大量回表                        IO浪费

有ICP:
  Index Scan (a > 10) + 索引内检查b条件 -> 仅满足b的记录回表 -> Server处理剩余条件
  ↑                                          ↑
  过滤在索引层完成                           IO减少

ICP 的适用条件

ICP 并非对所有查询都有效，需要同时满足以下条件：

1. 存储引擎支持

• InnoDB 和 MyISAM 支持 ICP。
• 其他引擎（如 Memory、NDB）不支持。
• 默认情况下 InnoDB 开启 ICP，但可通过 optimizer_switch 控制。

2. 使用索引进行数据访问

• 查询必须用到索引（可以是二级索引或聚簇索引）。
• 若为全表扫描（无索引），则不涉及 ICP。

3. 索引是二级索引（通常情况）

• 对于 二级索引，ICP 可以过滤索引列中未被索引排序直接利用的条件。
• 对于 聚簇索引（主键索引），ICP 也可生效，但回表本就是同一 B+Tree（InnoDB 聚簇索引的叶子节点包含完整行数据），此时 ICP 的作用是减少访问叶子节点的次数。不过实际中 ICP 更多用于二级索引。

4. 查询条件包含索引列上的“不能直接用于索引定位”的条件

典型场景：

• 范围条件后面的列：联合索引 (c1, c2, c3)，c1 = 1 AND c2 > 10 AND c3 LIKE '%x%'。c2 > 10 使 c2 使用范围，c3 无法利用索引排序，但索引中存有 c3 值，ICP 可将 c3 LIKE '%x%' 下推。
• 左模糊查询：col LIKE '%abc' 或 col LIKE '_abc'。
• 函数包裹的索引列：LENGTH(col) > 5（若 MySQL 版本支持函数索引，否则无法下推）。
• OR 条件：某些情况下 ICP 也可用于 OR 连接的多个索引条件（需满足索引合并条件）。

5. 条件必须涉及索引列

• 如果过滤条件引用的是非索引列，则无法下推（因为索引中没有该列的值）。
• 例如联合索引 (a, b)，WHERE a > 10 AND c = 1，c 不在索引中，无法下推。

6. 存储引擎层能够计算该条件

• 存储引擎只能处理简单的比较、LIKE、IN 等操作符。无法处理子查询、用户变量、存储函数等复杂表达式。

确认 ICP 是否启用

查看 optimizer_switch

SHOW VARIABLES LIKE 'optimizer_switch';

输出中应有 index_condition_pushdown=on（默认开启）。

通过 EXPLAIN 观察

使用 EXPLAIN FORMAT=TRADITIONAL 或 EXPLAIN FORMAT=TREE。在传统格式中，Extra 列出现 Using index condition 表示使用了 ICP。

示例：

EXPLAIN SELECT * FROM employees WHERE first_name = 'John' AND last_name LIKE '%Smith%';

输出：

Using index condition 表示使用了 ICP。

若为 Using where; Using index condition，表示部分条件由 ICP 过滤，剩余条件仍需服务层处理。

实际对比：启用与关闭 ICP 的性能差异

准备实验（MySQL 8.0，InnoDB 引擎）：

-- 创建测试表
CREATE TABLE test_icp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  a INT NOT NULL,
  b VARCHAR(255) NOT NULL,
  INDEX idx_a_b (a, b)
);

-- 插入100万条数据，a 在 1~1000 范围内均匀分布，b 随机字符串
-- 略过具体插入脚本，假设数据已就绪

-- 查询：a=500 且 b 包含 'abc'
SELECT * FROM test_icp WHERE a = 500 AND b LIKE '%abc%';

关闭 ICP（临时）：

SET optimizer_switch = 'index_condition_pushdown=off';
-- 执行查询并记录时间

开启 ICP（默认）：

SET optimizer_switch = 'index_condition_pushdown=on';
-- 执行查询并记录时间

在百万级数据中，ICP 开启时，回表次数仅等于满足 b LIKE '%abc%' 的行数（假设几十行），而关闭 ICP 时，需要回表所有 a=500 的行（约1000行），性能差异可达数十倍。

ICP 的优势与限制

优势

• 减少回表次数：是 ICP 最直接的效果，尤其当二级索引中过滤性强的条件位于索引后部时。
• 降低 IO 压力：随机 IO 变为更少的回表，磁盘带宽和 CPU 资源消耗减少。
• 提升查询吞吐：在并发场景下，回表减少意味着 InnoDB 的缓冲池命中率可能提高。

限制

• 仅适用于单表访问：多表 JOIN 时，ICP 下推的条件仅作用于当前表的索引访问，不会跨表。
• 不支持分区表过滤：分区裁剪发生在存储引擎之前，ICP 不参与分区选择。
• 部分复杂表达式无法下推：如 col + 1 > 10（除非有函数索引）、子查询、用户变量等。
• 对聚簇索引效果有限：聚簇索引回表本就是读取叶子节点，虽然能减少叶子节点扫描量，但不如二级索引明显。
• 索引条件下推不等于索引覆盖：ICP 只是减少回表次数，但最终仍需要回表获取未索引的列；如果查询的列都在索引中（覆盖索引），则根本不需要回表，此时 ICP 无额外收益。

何时应关注 ICP 的关闭？

虽然 ICP 默认开启且通常有益，但在极少数情况下可以关闭它来改变执行计划：

• 强制使用 MRR（Multi-Range Read）：有些版本中 ICP 与 MRR 互斥，关闭 ICP 可让优化器选择 MRR，可能对某些范围查询更优。
• 调试与测试：对比执行计划时，关闭 ICP 可验证是否存在因 ICP 导致的错误过滤（极少见，如字符集转换不一致）。

总结

索引下推 ICP 是 MySQL 在索引扫描阶段提前过滤记录的优化技术，通过将一部分 WHERE 条件“推”给存储引擎执行，显著减少回表次数。它的生效需要满足：存储引擎支持、使用二级索引、条件涉及索引列且无法直接用于索引排序。通过 EXPLAIN 的 Using index condition 可以确认是否启用。在实际调优中，应优先保证索引设计合理，再结合 ICP 自然降低 IO 开销，无需手动干预。