8.2.1.5索引条件下推优化 ICP

8.2.1.5 Index Condition Pushdown Optimization 8.2.1.5索引条件下推优化

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

Index Condition Pushdown (ICP)是对 MySQL 使用索引从表中检索行的一种优化。在没有 ICP 的情况下，存储引擎遍历索引来定位基表中的行，并将它们返回给 MySQL 服务器，后者将评估这些行的 WHERE 条件。如果启用了 ICP，并且只使用索引中的列就可以评估 WHERE 条件的某些部分，则 MySQL 服务器会将 WHERE 条件的这一部分推送到存储引擎。然后，存储引擎使用索引条目评估推入索引条件，并且只有在满足该条件时，才从表中读取行。ICP 可以减少存储引擎必须访问基表的次数和 MySQL 服务器必须访问存储引擎的次数。

Applicability of the Index Condition Pushdown optimization is subject to these conditions:

索引条件下推优化的适用性取决于以下条件:

• ICP is used for the range, ref, eq_ref, and ref_or_null access methods when there is a need to access full table rows.

  当需要访问整个表行时，ICP 用于 range, ref, eq_ref 和 ref_or_null 访问方法。

• ICP can be used for InnoDB and MyISAM tables, including partitioned InnoDB and MyISAM tables.

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

• For InnoDB tables, ICP is used only for secondary indexes. The goal of ICP is to reduce the number of full-row reads and thereby reduce I/O operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce I/O.

  对于 InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少全行读操作的数量，从而减少 i/o 操作。对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。在这种情况下使用 ICP 并不会降低 i/o。

• ICP is not supported with secondary indexes created on virtual generated columns. InnoDB supports secondary indexes on virtual generated columns.

  不支持在虚拟生成列上创建二级索引，InnoDB 支持在虚拟生成列上创建二级索引。

• Conditions that refer to subqueries cannot be pushed down.

  不能下推引用子查询的条件。

• Conditions that refer to stored functions cannot be pushed down. Storage engines cannot invoke stored functions.

  引用已存储函数的条件不能下推。存储引擎不能调用已存储函数。

• Triggered conditions cannot be pushed down. (For information about triggered conditions, see Section 8.2.2.3, “Optimizing Subqueries with the EXISTS Strategy”.)

  触发条件不能下推(有关触发条件的信息，请参阅第8.2.2.3节“使用 EXISTS 策略优化子查询”)

To understand how this optimization works, first consider how an index scan proceeds when Index Condition Pushdown is not used:

为了理解这种优化是如何工作的，首先考虑当不使用 Index Condition Pushdown 时，索引扫描是如何进行的:

1. Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.

   获取下一行，首先读取索引元组，然后使用索引元组定位和读取整个表行。

2. Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

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

Using Index Condition Pushdown, the scan proceeds like this instead:

使用下推索引条件，扫描过程如下所示:

1. Get the next row's index tuple (but not the full table row).

   获取下一行的索引元组(但不是整个表行)。

2. Test the part of the WHERE condition that applies to this table and can be checked using only index columns. If the condition is not satisfied, proceed to the index tuple for the next row.

   测试应用于此表的 WHERE 条件的一部分，可以只使用索引列进行检查。如果条件不满足，则转到下一行的索引元组。

3. If the condition is satisfied, use the index tuple to locate and read the full table row.

   如果条件满足，则使用索引元组来定位和读取整个表行。

4. Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

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

EXPLAIN output shows Using index condition in the Extra column when Index Condition Pushdown is used. It does not show Using index because that does not apply when full table rows must be read.

当使用“索引条件下推”时，在Extra列中显示“ Using index condition”。它不显示 Using index，因为当必须读取整个表行时，这不适用。

Suppose that a table contains information about people and their addresses and that the table has an index defined as INDEX (zipcode, lastname, firstname). If we know a person's zipcode value but are not sure about the last name, we can search like this:

假设一个表包含有关人员及其地址的信息，并且该表有一个定义为 INDEX (邮政编码、姓氏、名)的索引。如果我们知道一个人的邮政编码值，但不确定他的姓氏，我们可以这样搜索:

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

MySQL can use the index to scan through people with zipcode='95054'. The second part (lastname LIKE '%etrunia%') cannot be used to limit the number of rows that must be scanned, so without Index Condition Pushdown, this query must retrieve full table rows for all people who have zipcode='95054'.

可以使用该索引通过 zipcode  =’95054’ 的人进行扫描。第二部分(lastname LIKE’% etrunia%’)不能用于限制必须扫描的行数，因此如果没有 Index Condition Pushdown，此查询必须为所有具有 zipcode =’95054’的人检索完整的表行。

With Index Condition Pushdown, MySQL checks the lastname LIKE '%etrunia%' part before reading the full table row. This avoids reading full rows corresponding to index tuples that match the zipcode condition but not the lastname condition.

使用“索引条件下推”，MySQL 在读取整个表行之前检查“lastname LIKE '%etrunia%' ”部分。这样就避免了读取与索引元组对应的完整行，这些索引元组与邮编条件匹配，但与 lastname 条件不匹配。

Index Condition Pushdown is enabled by default. It can be controlled with the optimizer_switch system variable by setting the index_condition_pushdown flag:

索引条件下推是默认启用的。它可以通过设置 index_condition_pushdown 标志来控制 optimizer_switch 系统变量:

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

See Section 8.9.2, “Switchable Optimizations”.

参见8.9.2节，“可切换优化”。