526互联

MySQL索引下推(官方翻译)

发布时间 2023-05-08 23:00:33作者: JaxYoun

索引条件下推优化

原文链接：https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

索引条件下推 (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 策略优化子查询”。）

  • （MySQL 8.0.30 及更高版本? 无法将条件下推到包含对系统变量的引用的派生表。

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

  1. 获取下一行，首先通过读取索引元组，然后使用索引元组定位并读取全表行。
  2. WHERE测试适用于该表的条件 部分。根据测试结果接受或拒绝该行。

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

  1. 获取下一行的索引元组（但不是完整的表行）。
  2. WHERE测试适用于此表的条件 部分，并且可以仅使用索引列进行检查。如果不满足条件，则继续处理下一行的索引元组。
  3. 如果满足条件，则使用索引元组定位并读取全表行。
  4. WHERE 测试适用于此表的条件 的其余部分。根据测试结果接受或拒绝该行。

EXPLAIN使用索引条件下推时，输出显示 Using index condition在列中 。Extra它不显示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'。

使用 Index Condition Pushdown，MySQL lastname LIKE '%etrunia%'在读取完整的 table 行之前检查部分。zipcode这样可以避免读取符合条件但不符合 条件的索引元组对应的完整行 lastname。

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

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