Documentation Home
MySQL 5.7 参考手册
MySQL 8.0 参考手册  / 第8章优化  / 8.2 优化SQL语句  / 8.2.1 优化 SELECT 语句  /  8.2.1.5 索引条件下推优化

8.2.1.5 索引条件下推优化

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

Index Condition Pushdown 优化的适用性取决于以下条件：

  • 当需要访问完整的表行时 ， ICP 用于 range、 ref、 eq_ref和 访问方法。ref_or_null

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

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

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

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

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

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

要了解此优化的工作原理，首先考虑在未使用索引条件下推时索引扫描如何进行：

  1. 获取下一行，首先通过读取索引元组，然后使用索引元组定位并读取全表行。

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

使用索引条件下推，扫描会像这样进行：

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

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

  3. 如果满足条件，则使用索引元组定位并读取全表行。

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

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.

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: 

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'.

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.

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

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

请参阅第 8.9.2 节，“可切换优化”。