MySQL 索引下推（Index Condition Pushdown，ICP）

索引下推是 MySQL 5.6 引入的一项查询优化技术，它允许在存储引擎层执行部分 WHERE 条件的过滤，而不是将所有数据行返回到 Server 层后再进行过滤。

核心原理

在传统的查询处理中：

1. 存储引擎使用索引定位数据
2. 将完整的数据行返回给 Server 层
3. Server 层应用 WHERE 子句中的条件进行过滤

使用 ICP 后：

1. 存储引擎使用索引定位数据
2. 在存储引擎层就直接使用索引中的列信息进行部分条件过滤
3. 只将满足条件的数据行返回给 Server 层

工作原理示例

假设有表 users和组合索引 (age, city)：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_age_city (age, city)
);

-- 查询语句
SELECT * FROM users 
WHERE age = 25 
  AND city LIKE 'New%';

没有 ICP 的情况：

1. 存储引擎使用索引找到所有 age = 25的记录
2. 将所有满足 age = 25的记录返回到 Server 层
3. Server 层再过滤 city LIKE 'New%'

有 ICP 的情况：

1. 存储引擎使用索引找到所有 age = 25的记录
2. 在存储引擎层直接检查 city LIKE 'New%'（因为 city 在索引中）
3. 只将同时满足两个条件的记录返回到 Server 层

使用条件

1. 必须使用二级索引（非主键索引）
2. 只适用于需要回表查询的场景
3. 条件必须涉及索引中的列
4. 支持的范围：
   • range、ref、eq_ref、ref_or_null访问方法
   • InnoDB 和 MyISAM 引擎
   • 虚拟列（MySQL 5.7+）

优势

1. 减少回表次数：提前在存储引擎层过滤，减少不必要的数据行读取
2. 降低 I/O 开销：减少从存储引擎到 Server 层的数据传输
3. 提高查询性能：特别是当索引的选择性不高时，效果更明显

限制

1. 不适用于主键索引（聚簇索引）
2. 覆盖索引不需要 ICP：如果查询只使用索引就能完成，不需要回表
3. 子查询、存储函数条件不支持
4. 触发条件删除的表不支持

检查是否使用 ICP

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE age = 25 AND city LIKE 'New%';

-- 在 Extra 列中看到 "Using index condition" 表示使用了 ICP

控制 ICP

-- 查看 ICP 是否启用
SHOW VARIABLES LIKE 'optimizer_switch';

-- 临时开启/关闭 ICP
SET optimizer_switch = 'index_condition_pushdown=on';  -- 开启
SET optimizer_switch = 'index_condition_pushdown=off'; -- 关闭

实际应用场景

1. 组合索引的部分列查询：特别是第一个条件选择性不高，第二个条件能过滤很多数据时
2. LIKE 前缀匹配：当 LIKE 的条件列在索引中时
3. 范围查询后的等值查询：如 WHERE age > 20 AND city = 'Beijing'

示例对比

-- 假设有 10000 条 age=25 的记录，其中只有 100 条 city 以 'New' 开头

-- 无 ICP：存储引擎读取 10000 行，Server 层过滤后剩 100 行
-- 有 ICP：存储引擎自己过滤，只读取 100 行到 Server 层
-- 性能差异：回表次数从 10000 次减少到 100 次

索引下推是 MySQL 优化器的一个重要优化手段，能有效减少不必要的数据访问，提升查询性能，特别是在组合索引和需要回表的查询场景中效果显著。