4.4 索引条件下推优化

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

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

  • 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 支持虚拟生成的列上的二级索引。

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

  • 涉及存储功能的条件不能下推。存储引擎无法调用存储的功能。

  • 触发条件不能下推。

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

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

通过“索引条件下推”，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';
Previoussql优化Nextsql优化

Last updated