关于mysql: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';

集体思考

能够将索引下推了解为，在遍历索引时携带该索引反对的列查问条件，在索引中实现数据过滤，这样能够缩小查问的整体交互次数，晋升性能