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

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

什么是索引下推（ICP）

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

官方文档说明

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

中文翻译：

索引条件推迟优化（Index Condition Pushdown，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 支持对虚拟生成的列创建辅助索引。
不能将引用子查询的条件推送下去。
不能将引用存储函数的条件推送下去。存储引擎无法调用存储函数。
触发的条件不能被推送下去。关于触发条件的信息，请参见“使用 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节“可切换的优化”。

ICP的工作原理

ICP的核心在于，允许在使用索引扫描来检索行时，将WHERE子句中与索引列相关的部分条件“下推”到存储引擎层。如果这部分条件可以用于在索引层面排除行，那么就只有符合这些条件的记录会被传递到服务器层。

例如，考虑一个包含 name 和 age 列的表，并且对 name 列有索引。在没有ICP的情况下，查询 SELECT * FROM table WHERE name LIKE ‘A%’ AND age = 30 时，名字以 ‘A’ 开头的所有记录都会被检索出来，然后在服务器层筛选出 age 为30的记录。而启用ICP后，存储引擎将直接排除那些名字以 ‘A’ 开头，但年龄不是30岁的记录。

ICP带来的好处

ICP的主要优势在于减少数据传输量。在传统的操作中，所有匹配第一个条件的行都会从存储引擎传递到MySQL服务器层，哪怕这些行并不满足查询的其他条件。这不仅增加了I/O开销，还可能导致缓存浪费和CPU周期的不必要消耗。而有了ICP，只有更少、更相关的数据集会被传输和处理，从而极大地提高了查询的效率和性能。

如何确定是否使用了ICP

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

例如：

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

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

如何启用ICP

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

• 启用ICP：

SET optimizer_switch = 'index_condition_pushdown=on';

• 禁用ICP：

SET optimizer_switch = 'index_condition_pushdown=off';

最佳实践和使用注意事项

• 使用合适的索引：ICP最有效的前提是存在能够被利用的合适索引。
• 适当的查询设计：复杂查询中包含多个联合条件时，ICP可以极大地提高性能。
• 监控和调整：始终监控查询性能，如果发现ICP没有带来预期的性能提升，可以考虑通过调整查询或索引策略来优化。
• 版本兼容性：注意，ICP是MySQL 5.6及之后版本的特性。在旧版本中不可用。
• 共享存储引擎限制：ICP仅适用于InnoDB和MyISAM存储引擎。

在开发和设计数据库时，了解并合理运用ICP，对于优化查询性能、降低系统资源消耗具有重要意义。通过本文的解析，您现在应该对MySQL的索引下推优化有了更深刻的理解，可以开始在实践中应用这一强大的特性了。

总结

在不能使用索引场景下尽量使用索引在存储引擎查询过滤，从而减少回表次数，减少IO。
特点：

• 存在于辅助索引(二级索引)，因为聚集索引不需要回表
• 在正常无法走索引场景，尽量使用索引下推(需要优化器判断是否能走索引下推，跟存储引擎、数据量有关)
• 在存储引擎层使用索引条件过滤，减少查询到服务层的数据量