什么是索引下推

索引下推（Index Condition Pushdown, ICP）是 MySQL 5.6 引入的一种优化技术，旨在提高使用索引的查询性能。它的主要作用是将一些查询条件下推到索引扫描阶段，从而减少回表的次数，提升查询效率。

背景

在没有索引下推优化时，MySQL使用索引进行查询时，通常会按照索引条件查找到满足条件的记录，然后回表（即访问表的行数据）进行其他非索引条件的过滤。这会导致大量不必要的回表操作，尤其是在表较大、索引覆盖的字段较少时，性能损耗较为明显。

索引下推的工作原理

索引下推的核心思想是，在使用索引进行扫描时，将一部分查询条件在索引层面过滤掉，减少不必要的回表操作。

具体过程：

1. 索引扫描阶段：当执行索引扫描时，首先根据索引列来匹配查询条件。如果存在能够通过索引直接判断的条件（如范围查询、相等条件等），则在扫描过程中直接应用这些条件。

2. 下推过滤：对于那些可以通过索引值进行判断的其他查询条件（即使不是索引最左前缀的部分），MySQL也会尽量在索引扫描阶段过滤掉这些不符合条件的行，而不是先回表再过滤。

3. 减少回表：只对那些完全符合条件的行进行回表操作（从主键或聚簇索引中取出整行数据）。这样可以大幅减少回表次数，尤其在数据量大的情况下，性能提升明显。

举例说明

假设有一个表 employees，其索引为 (name, age, department)，并有如下查询：

SELECT * FROM employees WHERE name LIKE 'J%' AND age > 30 AND department = 'HR';

1. 没有索引下推的情况：

• MySQL 使用索引 name 进行扫描，找到 name LIKE 'J%' 的所有匹配项。
• 对每个匹配到的索引行，执行回表操作，获取 age 和 department 字段的数据。
• 回表后，MySQL 再对每一行数据进行条件 age > 30 AND department = 'HR' 的过滤。

即使有些 age 或 department 不满足条件，MySQL 仍然要回表后再过滤，这会导致大量的回表操作，浪费时间和资源。

2. 启用索引下推的情况：

• MySQL 在索引扫描时，先根据 name LIKE 'J%' 的条件找到匹配的索引行。
• 在索引层面，MySQL 会进一步检查索引中的 age 和 department 字段，先过滤掉 age ≤ 30 或 department ≠ 'HR' 的行。
• 只有完全符合条件的索引行才会进行回表，获取完整的行数据并返回。

这样就减少了不必要的回表操作，从而提升了查询性能。

索引下推的适用场景

• 索引下推最适合复合索引的场景，特别是当查询条件中部分列是索引前缀中的列，而其他条件列也是索引的一部分时。
• 它对减少回表次数尤其有效，当表很大且只需要检索少量数据时，效果更加显著。

适用的存储引擎

• InnoDB：索引下推主要在 InnoDB 存储引擎中得到了广泛应用。在使用 InnoDB 时，通过复合索引优化查询时，索引下推能够有效提升性能。
• MyISAM：也支持索引下推，但使用频率较少，因为 MyISAM 通常适用场景较窄。

总结

索引下推通过将部分过滤条件下推到索引扫描阶段进行处理，减少了不必要的回表操作，从而提高查询效率。它特别适用于复合索引，能够减少不必要的全表扫描或回表操作，是 MySQL 查询优化的一项重要技术。