索引下推（Index Condition Pushdown, ICP）是 MySQL 5.6 引入的一种查询优化技术，旨在减少回表次数，提高查询效率。它通过 将 WHERE 条件部分下推到存储引擎层执行，避免无效的数据行回表。

---

一、为什么需要索引下推？

传统执行流程（无索引下推）

• 执行 WHERE 条件的过滤是在 Server 层 完成的。
• 存储引擎通过索引找到匹配的行主键，然后回表获取完整数据，返回给 Server。
• Server 再应用 WHERE 条件过滤不符合的记录。

问题：

• 如果 WHERE 条件中有索引列之外的列，存储引擎无法判断，需要回表再过滤。
• 回表次数多，性能低下。

---

二、索引下推的优化原理

ICP 原理

• 当查询使用 二级索引 且 WHERE 条件中包含该索引的列时，MySQL 可以将 部分 WHERE 条件下推到存储引擎层。
• 存储引擎在索引遍历时，提前过滤不符合条件的记录，减少回表次数。

关键点：

• ICP 仅对 二级索引 生效（聚簇索引不需要回表）。
• ICP 针对 范围查询（range）、ref 类型的扫描 效果显著。
• 启用 ICP 的前提是 条件涉及索引列。

---

三、索引下推的执行流程

无 ICP（MySQL 5.5 及之前）

1. 存储引擎根据索引找到匹配的索引键。
2. 对每一行执行回表，取出完整数据。
3. Server 层应用 WHERE 条件过滤。

有 ICP（MySQL 5.6+）

1. 存储引擎根据索引找到索引键。
2. 在存储引擎层对索引列条件进行过滤。
3. 仅回表符合条件的行，减少回表次数。

---

四、ICP 的生效条件

• 表必须使用 InnoDB 或 MyISAM。
• 使用 二级索引。
• WHERE 条件包含 索引列，且不是全部条件都能由索引覆盖。
• 不能是 唯一索引的等值查询（因为这时不需要下推）。
• 查询中不能禁用 ICP（optimizer_switch 控制）。

检查是否启用 ICP：

SHOW VARIABLES LIKE 'optimizer_switch';

ICP 默认开启，可以通过以下方式关闭：

SET optimizer_switch='index_condition_pushdown=off';

---

五、ICP 示例

假设有一张用户表 users：

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

示例 SQL

EXPLAIN SELECT * FROM users WHERE age > 20 AND city = 'Beijing';

分析：

• 索引 idx_age_city (age, city) 可以用于 age 的范围查询。

• city 也在索引里，但不是范围条件的第一列。

• 无 ICP 时：

  • 存储引擎找到 age > 20 的所有行 → 回表 → Server 层再判断 city。

• 有 ICP 时：

  • 存储引擎找到 age > 20 后，在存储引擎内判断 city = ‘Beijing’，减少无效回表。

EXPLAIN 输出：

Extra: Using index condition

表示启用了索引下推。

---

六、ICP 的性能优势

• 减少回表次数：过滤更多不符合条件的数据。
• 降低 I/O 消耗：减少数据页访问。
• 提高查询速度：特别是在范围查询时效果显著。

性能对比（假设 100 万行数据）：

• 无 ICP：扫描 10000 行索引，回表 10000 次。
• 有 ICP：扫描 10000 行索引，在存储引擎过滤掉 8000 行，只回表 2000 次。

---

七、ICP 的限制

• 仅适用于 二级索引。
• 仅能下推索引列相关条件，不能下推涉及非索引列的条件。
• 对于 覆盖索引（无需回表），ICP 无意义。
• 不能下推 OR 条件（部分版本优化）。

---

八、面试高频问答

Q1：什么是索引下推？

• 一种 MySQL 优化技术，将部分 WHERE 条件下推到存储引擎，在索引遍历时提前过滤，减少回表次数。

Q2：索引下推能优化什么类型的查询？

• 使用二级索引，且 WHERE 条件中涉及该索引的列，尤其是范围查询。

Q3：EXPLAIN 如何判断启用了 ICP？

• Extra 列显示 Using index condition。

Q4：ICP 适用于主键查询吗？

• 不适用，因为主键查询本身是聚簇索引，存储引擎可以直接取整行数据。

Q5：ICP 与覆盖索引的关系？

• 覆盖索引无需回表，所以 ICP 不会生效。

---

九、总结

特性	描述
优化点	减少回表次数，提高性能
适用范围	二级索引，WHERE 条件涉及索引列
EXPLAIN 标识	Using index condition
关键机制	将条件下推到存储引擎进行提前过滤

关键结论：

• ICP 适用于二级索引的范围查询或复合条件。
• 覆盖索引场景不会启用 ICP。
• MySQL 5.6+ 默认启用 ICP。

---