MySql 索引下推

Index Condition Pushdown

• Index Condition Pushdown，即索引下推(ICP)，是针对使用索引查询策略的优化
• 不开启索引下推时，MySQL通过存储引擎遍历索引，定位数据返回给服务器，再由服务器进行Where条件的判断
• 开启索引下推，且某一部分的WHERE条件可以使用索引中的列来判断时，服务器会将这部分Where条件下推到存储引擎，由存储引擎使用索引判断
• 索引下推可以减少存储引擎访问数据表的次数，以及MySQL服务器访问存储引擎的次数

适用条件

1. 需要整表扫描的情况，如range, ref, eq_ref, ref_or_null，适用于InnoDB和MyISAM引擎（5.7版本后可以用于分区表查询）
2. 对于InnoDB只适用于二级索引，因为InnDB的聚簇索引会将整行数据读到InnDB的缓冲区，这样无法减少IO次数，失去了优化的意义
3. 引用子查询的条件不能下推
4. 调用存储过程的条件不能下推，存储引擎无法调用位于MySQL服务器中的存储过程
5. 触发条件不能下推

相关示例

此处翻译官方文档，假定有一张人员信息表，并定义联合索引（zipcode，lastname，firstname），此时运行如下查询

SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';

• 若没有使用索引下推，存储引擎会从索引中匹配zipcode='95054'的行返回至服务器，由服务器来判断lastname LIKE '%etrunia%'和address LIKE '%Main Street%'
• 若使用索引下推，存储引擎会在索引中判断是否符合lastname LIKE '%etrunia%'和address LIKE '%Main Street%'，仅返回匹配的行至服务器

相关配置

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

个人思考

可以将索引下推理解为，在遍历索引时携带该索引支持的列查询条件，在索引中完成数据过滤，这样可以减少查询的整体交互次数，提升性能