基本介绍

索引下推（Index Condition Pushdown, ICP），是MySQL5.6 引入的优化技术，核心思想是：将查询条件中索引可过滤的部分从 Server 层 “下推” 到存储引擎层（如 InnoDB）提前处理，从而减少回表次数和磁盘 I/O。

为什么需要索引下推？

未引入ICP（x）

（1）存储引擎先根据索引（需符合最左前缀等原则）查找数据，返回所有满足索引条件的记录；

（2）Server 层对所有返回的记录进行二次过滤，剔除不满足条件的记录（回表次数较多）；

例如，表 users 有复合索引 idx(age, city)`,

-- a. 由于age是范围查询，因此复合索引只能利用到 age 部分。存储引擎innodb通过索引找到所有 age >   20 的记录，并返回给Server层。
-- b. 返回 Server 层后，再过滤 city = 'Shanghai' 和 name LIKE '%John%'
SELECT * FROM users 
WHERE age > 20 
AND city = 'Shanghai' 
AND name LIKE '%John%';

引入ICP（√）

1. 存储引擎在索引遍历时，直接使用索引中包含的字段，提前过滤掉不满足 WHERE 子句的部分条件。
2. 仅将过滤后的记录返回给 Server 层，减少回表次数和传输的数据量。（充分利用索引减少后续回表次数）

例如，表 users 有复合索引 ``idx(age, city),

-- a. 由于age是范围查询，因此复合索引只能利用到 age 部分。但条件中存在索引列 age, city，存储引擎在索引中直接过滤 age > 20 AND city = 'Shanghai'的记录，仅返回符合索引条件的记录。
-- b. 返回 Server 层后，再由 Server 层过滤 name。
SELECT * FROM users 
WHERE age > 20 
AND city = 'Shanghai' 
AND name LIKE '%John%';

如何指导sql优化

适用场景

可以看出，索引下推适用的场景：

• 查询需要回表（非覆盖索引，如上述例子）。
• WHERE 子句包含索引列和非索引列的条件（非索引列后续需要回表，如上述例子 name）。
• 索引是复合索引，且 WHERE 条件包含索引的前缀列和非前缀列（如上述 name 和 age）。

sql优化

Explain 分析sql语句时，发现extra_info中包含Using index condition ，表明使用到了索引下推优化查询。

总结：
(1) 设计索引时，优先将高频过滤条件加入联合索引；
(2) 编写sql时，充分利用联合索引条件，在存储引擎层过滤掉不符合条件的记录，减少回表次数；
(3) 结合 Explain 执行计划验证 ICP 是否生效，针对性优化索引和查询条件;