MySQL索引下推（Index Condition Pushdown, ICP）优化深入解析

数据库性能优化是现代软件开发中不可或缺的一环。在MySQL中，索引的使用往往是提高查询性能的关键。自5.6版本起，MySQL引入了一个强大的优化器功能，名为索引下推（Index Condition Pushdown, 简称ICP）。通过ICP，我们可以显著提升部分查询的效率，尤其是在使用索引过滤数据时。本文将详细介绍ICP的原理、作用以及应用场景。

1、什么是索引下推（ICP）

在MySQL中，ICP允许在存储引擎层过滤索引中的记录，而不是在服务器层进行。
传统上，即使使用了索引，MySQL也会将所有符合索引查找条件的记录从存储引擎传输到服务器层，然后在服务器层进行进一步的过滤。这种方法在处理包含多个条件并且索引不是很精确的查询时会非常低效。引入ICP后，可以在存储引擎层进行更多的过滤工作，从而减少了不必要的数据传输。

2、官方文档说明

MySQL 8.0官方文档说明：Index Condition Pushdown Optimization

中文翻译：

索引条件推迟优化（Index Condition Pushdown，ICP）是针对 MySQL 使用索引检索表中行的情况的一种优化。在没有使用 ICP 的情况下，存储引擎遍历索引以定位基表中的行，并将它们返回给 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 支持对虚拟生成的列创建辅助索引。
不能将引用子查询的条件推送下去。
不能将引用存储函数的条件推送下去。存储引擎无法调用存储函数。
触发的条件不能被推送下去。关于触发条件的信息，请参见“使用 EXISTS 策略优化子查询”。

了解这种优化如何工作，首先考虑当没有使用索引条件推迟时索引扫描的进行方式：

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

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

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

当使用索引条件推迟时，EXPLAIN 输出在 Extra 列中显示了 Using index condition。这是因为当必须读取完整的表行时，不会显示 Using index。

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

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%’ 部分。这避免了读取与邮政编码条件匹配但不满足姓氏条件的索引元组所对应的完整行。

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

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

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

3、如何确定是否使用了ICP

要确定MySQL是否对特定的查询使用了ICP，可以通过 EXPLAIN 语句查看查询执行计划。在输出结果中，如果在 Extra 列看到了 Using index condition，那么就意味着ICP被用于该查询。

例如：

EXPLAIN SELECT * FROM table WHERE name LIKE 'A%' AND age = 30;

查看结果中 Extra 列的内容是否包含 Using index condition。

4、如何启用ICP

在MySQL 5.6及以上版本中，ICP 默认是启用的。但是，如果需要手动启用或禁用ICP，可以通过设置系统变量 optimizer_switch 来控制：

• 启用ICP：

SET optimizer_switch = 'index_condition_pushdown=on';

• 禁用ICP：

SET optimizer_switch = 'index_condition_pushdown=off';

5、举例

表 swp_dcz 包括以下字段（id,name,money,attr），其中id是主键索引，name, money创建了组合索引 idx_nm。

• 例子1

等值查询使用索引一次性精准查询。

• 例子2

1、关闭索引下推，Extra打印为 Using where。

使用了 LIKE 操作符和一个通配符 %。在这种情况下，MySQL 可以使用 idx_nm 索引来快速定位到所有以 ‘小丁’ 开头的记录，但是由于 % 可以匹配任何字符序列（包括空序列），服务器可能需要在索引定位之后，再在数据行层面进行一次过滤。这种行层面的过滤检查是为了确保返回的行完全匹配 LIKE ‘小丁%’ 的模式。

说白了，就是根据索引查询到这个范围数据后，回表查询行数据到server层，还需要根据where条件过滤。

2、开启索引下推，Extra打印为 Using index condition。

就是把上面server层的判断逻辑下推到存储引擎层进行判断，过滤判断后再回表查询行数据，可以减少回表次数。

• 例子3

1、关闭索引下推，Extra打印为 Using where。

因为name是范围查询，所以money其实是不能走索引的，因为name非等值时查询到的数据money不是有序的。因此根据索引查询到 小丁% 的索引数据后会回表查询数据行，然后返回到server层进行money=10的过滤。
说白了，就是money索引未生效，是在server层进行过滤的。

2、开启索引下推，Extra打印为 Using index condition。

会把上面server层的过滤放到存储引擎层，即根据 小丁% 获取到叶子节点数据后会继续判断 money=10，然后把过滤后的数据进行回表，返回到server层。

3、开启索引下推，Extra打印为 Using where; Using index。

因为这里查询的字段是 id,name,money，都是可以从idx_nm索引树上面拿到的数据，不需要回表，因此没有使用索引下推的必要。根据 小丁%获取索引数据后直接返回到server层，然后进行过滤。Using where代表server层过滤，Using index代表索引覆盖。

• 例子4

强制索引，但是未生效，因为 %小丁 是无法走索引的，因此第一个name字段没有走索引，所以 type 是all，key 是null。name like ‘%小丁’ and money = 10; 都在server层进行过滤，因此 Extra 打印 Using where。

• 例子5

索引跳跃，虽然%小丁 无法走索引，但是 money可以通过索引跳跃走索引，因此 money = 10是在索引树上面查询，然后返回数据到server层进行 %小丁过滤。因此 Using index for skip scan 表示索引跳跃。
那么问题来了，为什么例子3没有走索引跳跃呢，因为例子3需要回表，而例子4不需要回表，这里应该是mysql 优化器觉得回表走索引跳跃性能不如全表扫描。

6、总结

索引下推是为了提升索引查询性能，把server层的过滤推送到存储引擎层，从而减少回表次数，减少IO。

触发条件：

• 辅助索引(二级索引)
• 需要回表(非索引覆盖)
• 非等值查询，例如范围查询