526互联

什么是MySQL里的索引下推？什么时候会触发索引下推？

发布时间 2023-08-16 10:01:25作者: 夏威夷8080
索引下推（Index Condition Pushdown，简称 ICP）是MySQL优化器的一个特性，从MySQL 5.6开始引入。它允许MySQL优化器更早地在索引搜索过程中应用WHERE子句中的条件，而不是在检索到行后再进行过滤。这种优化可以大大减少存储引擎必须检查的行数。

不使用索引下推之前，执行流程大致如下：

  1. 优化器使用非聚簇索引来查找满足条件的行。
  2. 使用索引找到数据的位置并从表中检索完整的行。
  3. 将完整的行数据与WHERE子句中的其他条件进行比较，看是否满足条件。

当启用索引下推时，执行流程变为：

  1. 优化器使用非聚簇索引来查找满足条件的行。
  2. 在使用索引时，立即应用WHERE子句中的其他条件。如果索引条目不满足条件，那么完整的行数据就不会被检索。
  3. 只有满足所有条件的数据才会从表中检索完整的行。

这意味着，当条件可以在索引级别过滤时，存储引擎不必检索和返回那些不满足WHERE子句条件的行，从而提高了查询的性能。

例如，假设你有一个包含用户信息的表，并且你经常根据年龄和姓名进行查询。如果你有一个包含年龄和姓名的复合索引，那么在执行查询时，索引下推可以使得只有那些满足特定年龄和姓名条件的行才会被完整地从表中检索。

需要注意的是，不是所有的查询都会从索引下推中受益。只有当部分WHERE子句条件可以在索引层面进行过滤时，这种优化才是有效的。

 

什么时候会触发索引下推？

索引下推（Index Condition Pushdown，简称 ICP）在满足特定条件时被MySQL优化器触发。以下是触发ICP的一些常见情况和考虑因素：

  1. 适用版本：首先，你需要使用的MySQL版本应该支持ICP。这个特性从MySQL 5.6版本开始引入。

  2. 配置设置：ICP默认是启用的，但确保optimizer_switch设置中的index_condition_pushdown为ON。可以使用以下命令查看：

    SHOW VARIABLES LIKE 'optimizer_switch';

  3. 查询类型：只有使用了非聚簇索引的查询才可能触发ICP。这是因为在聚簇索引中，索引与数据行是紧密耦合的，所以没有额外的检索步骤。

  4. WHERE 子句的结构：当查询的WHERE子句中的一部分可以使用索引进行过滤，而另一部分则不能时，ICP最有可能被触发。在这种情况下，可以使用索引的部分首先进行过滤，然后应用余下的条件。

    例如，考虑一个包含first_name和last_name的复合索引。以下查询可能会触发ICP：

    SELECT * FROM users WHERE first_name = 'John' AND last_name LIKE 'Smi%';
    在这个例子中，first_name的等值比较可以直接用索引进行过滤，而last_name的LIKE操作可能会触发ICP，这样只有满足first_name条件的索引条目才会进一步检查last_name。

  5. 存储引擎：不是所有的存储引擎都支持ICP。例如，InnoDB和MEMORY存储引擎支持ICP，而MyISAM则不支持。

  6. EXPLAIN输出：当怀疑ICP可能对查询有益时，可以使用EXPLAIN命令来查看查询的执行计划。如果ICP被使用，你会在Extra列看到“Using index condition”。

总之，索引下推ICP是MySQL优化器的一个高级特性，它在特定情境下可以显著提高查询性能。不过，不是所有的查询都会从中受益，所以最好时常使用EXPLAIN来检查查询的执行计划并确定ICP是否被触发。