查看“索引条件下推优化”的源代码

索引条件下推（ICP）是一种针对MySQL使用索引检索行的情况的优化。在没有ICP的情况下，存储引擎遍历索引以定位基表中的行，并将其返回给MySQL服务器，服务器在对这些行进行WHERE条件评估。启用ICP并且如果WHERE条件的一部分可以仅使用索引列进行评估，MySQL服务器会将WHERE条件的这部分推送到存储引擎中。存储引擎然后通过使用索引条目来评估推送的索引条件，只有在满足条件时才从表中读取行。ICP可以减少存储引擎必须访问基表的次数，以及MySQL服务器必须访问存储引擎的次数。


索引条件下推优化的适用性受以下条件限制：

*当需要访问完整的表行时，ICP可用于范围、ref、eq_ref和ref_or_null访问方法。

*ICP可用于InnoDB和MyISAM表，包括分区的InnoDB和MyISAM表。

*对于InnoDB表，ICP仅用于辅助索引。ICP的目标是减少全行读取的次数，从而减少I/O操作。对于InnoDB聚簇索引，完整的记录已经被读入InnoDB缓冲区。在这种情况下使用ICP不会减少I/O。

*ICP不支持在虚拟生成列上创建的辅助索引。InnoDB支持在虚拟生成列上创建辅助索引。

*不能推送涉及子查询的条件。

*不能推送涉及存储函数的条件。存储引擎无法调用存储函数。

*不能推送触发器条件。

*（MySQL 8.0.30 及更高版本：）不能将条件推送到引用系统变量的派生表中。



要了解这种优化是如何工作的，首先考虑当不使用索引条件下推时，索引扫描的过程：

#获取下一行，首先通过读取索引元组，然后使用索引元组定位并读取完整的表行。

#测试适用于该表的 WHERE 条件的部分。根据测试结果接受或拒绝该行。


使用索引条件下推时，扫描的过程如下：

#获取下一行的索引元组（但不获取完整的表行）。

#测试适用于该表并且可以仅通过索引列进行检查的 WHERE 条件的部分。如果条件不满足，则继续处理下一行的索引元组。

#如果条件满足，则使用索引元组定位并读取完整的表行。

#测试适用于该表的其余 WHERE 条件的部分。根据测试结果接受或拒绝该行。



当使用索引条件下推时，EXPLAIN 输出中的 Extra 列会显示 Using index condition。它不会显示 Using index，因为当需要读取完整的表行时，Using index 不适用。


假设一个表包含有关人员及其地址的信息，并且该表具有一个索引，定义为 INDEX (zipcode, lastname, firstname)。如果我们知道一个人的 zipcode 值，但不确定姓氏，我们可以这样搜索：

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


MySQL 可以使用索引扫描具有 zipcode='95054' 的人员。第二部分 (lastname LIKE '%etrunia%') 无法用于限制需要扫描的行数，因此如果没有索引条件下推，这个查询必须检索所有具有 zipcode='95054' 的人员的完整表行。


使用索引条件下推，MySQL 在读取完整的表行之前检查 lastname LIKE '%etrunia%' 部分。这避免了读取与 zipcode 条件匹配但不满足 lastname 条件的索引元组对应的完整行。


索引条件下推在默认情况下是启用的。可以通过 optimizer_switch 系统变量来控制它，设置 index_condition_pushdown 标志：
 SET optimizer_switch = 'index_condition_pushdown=off';
 SET optimizer_switch = 'index_condition_pushdown=on';