MySQL 8.4 Release Notes
10.2.1.6 索引条件下推优化
索引条件下推（ICP）是 MySQL 对于使用索引检索表中的行的优化。没有 ICP，存储引擎将遍历索引以定位基表中的行，然后将这些行返回到 MySQL 服务器，该服务器对这些行评估WHERE条件。启用 ICP，并且如果可以使用索引列来评估WHERE条件的部分，那么 MySQL 服务器将推送这个部分的WHERE条件到存储引擎。存储引擎然后使用索引条目对推送的索引条件进行评估，如果满足这个条件，才从表中读取行。ICP 可以减少存储引擎访问基表的次数和 MySQL 服务器访问存储引擎的次数。
索引条件下推优化的适用性受以下条件限制：
-
ICP 用于
range、ref、eq_ref和ref_or_null访问方法，当需要访问完整表行时。 -
对于
InnoDB表格，ICP 只用于次要索引。ICP 的目标是减少全行读取的数量，从而减少I/O操作。对于InnoDB集群索引，完整记录已经被读入到InnoDB缓冲区中。在这种情况下使用 ICP 不会减少I/O。 -
ICP 不支持在虚拟生成列上创建的次要索引。
InnoDB支持在虚拟生成列上创建次要索引。 -
引用子查询的条件不能被推下。
-
引用存储函数的条件不能被推下。存储引擎无法调用存储函数。
-
触发器条件不能被推下。(关于触发器条件，请见第10.2.2.3节，“Optimizing Subqueries with the EXISTS Strategy”。)
-
条件不能被推下到包含系统变量引用的派生表。
要了解这个优化是如何工作的，首先考虑在不使用 Index Condition Pushdown 时索引扫描的过程：
-
获取下一行，首先读取索引元组，然后使用索引元组来定位和读取完整表格行。
-
对该表的WHERE条件进行测试，根据测试结果接受或拒绝该行。
使用索引条件下推，扫描过程如下：
-
获取下一行的索引元组（但不是完整的表行）
-
对该表的WHERE条件进行测试，可以只使用索引列来检查。如果条件不满足，继续处理下一个索引元组。
-
如果条件满足，使用索引元组来定位和读取完整的表行。
-
对该表的WHERE条件进行剩余测试，根据测试结果接受或拒绝该行。
EXPLAIN输出显示在Extra列中有Using index condition，当使用索引条件下推时。这不显示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';请参见第10.9.2节，“可切换优化”。