SQL索引下推（Index Condition Pushdown）优化原理解析

1. 问题背景：联合索引查询的局限性

假设有一张用户表 users，包含以下字段：

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

查询需求：查找年龄大于20岁且城市以"北京"开头的用户：

SELECT * FROM users WHERE age > 20 AND city LIKE '北京%';

未使用索引下推时的执行过程：

1. 索引扫描：沿联合索引 idx_age_city 找到所有 age > 20 的记录（索引能快速定位年龄范围）。
2. 回表操作：对每一条符合条件的索引记录，立即回主键索引（聚簇索引）读取完整数据行。
3. 筛选城市：在服务器层对读取到的数据行应用 city LIKE '北京%' 条件过滤。

缺陷：即使某些记录最终因城市条件不满足被过滤，仍会触发回表操作，造成不必要的磁盘I/O。

2. 索引下推的核心思想

索引下推（ICP） 将本应在服务器层处理的筛选条件下推至存储引擎层执行：

• 存储引擎在索引扫描阶段直接应用 city LIKE '北京%' 条件过滤。
• 仅对同时满足 age > 20 和 city LIKE '北京%' 的索引记录执行回表操作。

3. 索引下推的工作流程（以MySQL的InnoDB为例）

启用ICP后的执行步骤：

1. 索引扫描：存储引擎从 idx_age_city 索引中定位 age > 20 的第一条记录。
2. 条件下推过滤：检查该索引记录是否满足 city LIKE '北京%'：
   • 若满足，则执行回表操作，读取完整数据行并返回给服务器层。
   • 若不满足，直接跳过该索引记录，继续扫描下一条。
3. 服务器层过滤：服务器层对返回的数据行进行最终校验（如校验其他非索引条件）。

对比效果：

• 未启用ICP：回表次数 = 所有 age > 20 的记录数。
• 启用ICP：回表次数 = 同时满足 age > 20 和 city LIKE '北京%' 的记录数。

4. 索引下推的适用场景与限制

适用条件：

1. 查询涉及联合索引的前缀列（如 age）作为范围查询，后续列（如 city）作为过滤条件。
2. 条件下推列必须是索引列（如 city 是联合索引的一部分）。
3. 支持的条件类型：=, >, <, LIKE '前缀%' 等（不支持 LIKE '%后缀'）。

限制情况：

• 子查询或函数调用（如 city LIKE CONCAT('北', '%')）可能无法下推。
• 全文索引或空间索引不适用ICP。
• 当需要访问完整数据行才能判断条件时（如查询包含非索引列），无法下推。

5. 实际案例验证

查看是否启用ICP：
通过执行计划（Explain）观察 Extra 字段：

EXPLAIN SELECT * FROM users WHERE age > 20 AND city LIKE '北京%';

• 未启用ICP：Extra 显示 Using where（仅服务器层过滤）。
• 启用ICP：Extra 显示 Using index condition（条件下推至存储引擎）。

性能对比：
假设表中有10万条记录，其中 age > 20 的记录有5万条，最终满足城市条件的仅1000条：

• 无ICP：回表5万次。
• 有ICP：回表仅1000次，I/O开销降低约98%。

6. 总结与优化建议

• 优势：显著减少回表次数，降低CPU和磁盘I/O压力，尤其适用于联合索引的范围查询+过滤场景。
• 配置：MySQL 5.6+ 默认启用ICP，可通过 optimizer_switch='index_condition_pushdown=on' 控制。
• 设计建议：合理设计联合索引顺序，将范围查询列放在最后，或利用ICP特性将过滤列加入索引。