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访问方法。

• ICP 可用于InnoDB和MyISAMtable，包括分区的InnoDB和MyISAMtable。

• 对于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 节“可切换的优化”.