索引条件下推优化

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

索引条件下推优化的适用性受以下条件限制：

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

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

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

ICP不支持在虚拟生成列上创建的辅助索引。InnoDB支持在虚拟生成列上创建辅助索引。

不能推送涉及子查询的条件。

不能推送涉及存储函数的条件。存储引擎无法调用存储函数。

不能推送触发器条件。

（MySQL 8.0.30 及更高版本：）不能将条件推送到引用系统变量的派生表中。

要了解这种优化是如何工作的，首先考虑当不使用索引条件下推时，索引扫描的过程：

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

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

使用索引条件下推时，扫描的过程如下：

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

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

如果条件满足，则使用索引元组定位并读取完整的表行。

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

当使用索引条件下推时，EXPLAIN 输出中的 Extra 列会显示 Using index condition。它不会显示 Using index，因为当需要读取完整的表行时，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' 的人员的完整表行。

使用索引条件下推，MySQL 在读取完整的表行之前检查 lastname LIKE '%etrunia%' 部分。这避免了读取与 zipcode 条件匹配但不满足 lastname 条件的索引元组对应的完整行。

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

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

索引条件下推优化

导航菜单

搜索