8.2.1.5 索引条件下推式优化
索引条件下推(ICP)是针对 MySQL 使用索引从 table 中检索行的情况的优化。如果没有 ICP，则存储引擎将遍历索引以在基 table 中定位行，并将其返回给 MySQL 服务器，该 MySQL 服务器将评估这些行的WHERE条件。启用 ICP 后，如果只能使用索引中的列来评估WHERE条件的一部分，则 MySQL 服务器会将WHERE条件的这一部分下推到存储引擎。然后，存储引擎通过使用索引条目来评估推送的索引条件，并且只有在满足此条件的情况下，才从 table 中读取行。 ICP 可以减少存储引擎必须访问基 table 的次数以及 MySQL 服务器必须访问存储引擎的次数。
索引条件下推式优化的适用性取决于以下条件：
-
当需要访问完整 table 行时，ICP 用于range，ref，eq_ref和ref_or_null访问方法。
-
对于
InnoDBtable，ICP 仅用于辅助索引。 ICP 的目标是减少全行读取的次数，从而减少 I/O 操作。对于InnoDB聚集索引，完整的记录已被读取到InnoDB缓冲区中。在这种情况下使用 ICP 不会减少 I/O。 -
在虚拟生成的列上创建的二级索引不支持 ICP。
InnoDB支持虚拟生成的列上的二级索引。 -
引用子查询的条件不能下推。
-
涉及存储功能的条件不能下推。存储引擎无法调用存储的功能。
-
触发条件不能下推。 (有关触发条件的信息，请参阅第 8.2.2.3 节“使用 EXISTS 策略优化子查询”。)
要了解此优化的工作原理，请首先考虑在不使用“索引条件下推”的情况下如何进行索引扫描：
-
获取下一行，首先读取索引 Tuples，然后使用索引 Tuples 查找并读取整个 table 行。
-
测试适用于此 table 的
WHERE条件的一部分。根据测试结果接受或拒绝该行。
使用“索引条件下推”，扫描将像这样进行：
-
获取下一行的索引 Tuples(而不是整个 table 行)。
-
测试适用于此 table 的
WHERE条件的一部分，并且只能使用索引列进行检查。如果不满足条件，请转到下一行的索引 Tuples。 -
如果满足条件，请使用索引 Tuples 来定位和读取整个 table 行。
-
测试适用于此 table 的
WHERE条件的其余部分。根据测试结果接受或拒绝该行。
使用“索引条件下推”时，EXPLAIN的输出在Extra列中显示Using index condition。它不会显示Using index，因为在必须读取完整 table 行时，该命令不适用。
假设一个 table 包含有关人员及其地址的信息，并且该 table 的索引定义为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'的人检索完整的 table 行。
通过索引条件下推，MySQL 在读取整个 table 行之前检查lastname LIKE '%etrunia%'部分。这避免了读取与匹配zipcode条件但不匹配lastname条件的索引 Tuples 相对应的完整行。
默认情况下，索引条件下推处于启用状态。可以通过设置index_condition_pushdown标志来使用optimizer_switch系统变量进行控制：
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';
See 第 8.9.2 节“可切换的优化”.
