Go back

MySQL 索引下推（ICP）：让二级索引在引擎层就完成过滤

一句话结论（30s）

ICP（索引下推）的本质是把 WHERE 条件中能用索引列判断的部分下推到存储引擎层、在回表之前先过滤，因为回表是二级索引查询里最昂贵的随机 IO。关键设计是：引擎层每读到一条索引记录就调用 pushed_idx_cond->val_int() 校验，不满足直接跳过不回表。权衡在于：ICP 只减少回表次数、不减少索引扫描行数，且覆盖索引、等值精确匹配、主键查询等场景会自动跳过 ICP，其实际收益依赖 BufferPool 命中率。

核心原理（2min）

无 ICP 时，二级索引扫描会返回所有匹配部分索引条件的行，Server 层再逐一过滤，导致大量无效回表。ICP 把如 age > 20 这类索引列条件下推到引擎层：扫描 idx_user_age 时对每条记录先校验 age，只有满足条件的才回表取完整行，其余行直接跳过。源码层面由 handler::index_read_map 在读完索引后检查 pushed_idx_cond->val_int()，不满足即返回 HA_ERR_END_OF_FILE。它常与 MRR 配合：ICP 先过滤减少要回表的行、MRR 再按主键排序批量回表，把随机 IO 转成顺序 IO，叠加产生 1+1>2 的效果。

底层深入（5-10min）

一、ICP 解决什么问题？

在没有 ICP（Index Condition Pushdown）的时代，二级索引的查询流程如下：

1. Server 层：WHERE user_id = 100 AND age > 20 AND name LIKE 'A%'
2. 存储引擎：通过 idx_user_age 拿到所有 age > 20 的行
3. 存储引擎 → Server 层：返回所有匹配行（包括 WHERE 中其他条件未匹配的行）
4. Server 层：对返回的行逐一校验 name LIKE 'A%'
5. 返回最终结果

核心问题：所有符合部分索引条件的行都被回表取出，即使它们最终会被 Server 层过滤掉。回表（通过二级索引中的主键到聚簇索引中读取完整行数据）是随机 IO——这是 MySQL 最昂贵的操作之一。

ICP 的优化：将 WHERE 条件中 能使用索引列判断的部分 下推到存储引擎层：

1. Server 层：WHERE user_id = 100 AND age > 20 AND name LIKE 'A%'
2. Server 层 → 引擎层：ICP 条件只有 age > 20（索引列条件）
3. 引擎层：扫描 idx_user_age 时，对每条记录用 age > 20 校验
4. 只有 age > 20 为 true 的行才回表 → 取出完整行 → 返回 Server 层
5. Server 层：再对 name LIKE 'A%' 做最终过滤

减少的是回表次数，而非索引扫描次数。所有索引记录仍然被扫描（age > 20 在索引中），但不符合条件的行不再回表。

💭 想一想：ICP 为什么不省”扫描”、只省”回表”？——因为扫描发生在二级索引里、是顺序读、本身很便宜，而回表要随机跳到聚簇索引读完整行、是昂贵的那一步；ICP 能做的就是在引擎层”扫描到一条就顺手判断一下”，把不满足的行在回表前拦下。所以它省的是”无意义的随机 IO”，而不是”顺序扫描”。这也解释了它为什么依赖 BufferPool 命中率。

二、handler::index_read 调用路径

ICP 在 MySQL 源码中的核心链路：

// sql/handler.cc
int handler::index_read_map(uchar *buf, const uchar *key,
                            key_part_map keypart_map, enum ha_rkey_function find_flag) {
    // ...
    int error = index_read(buf, key, keypart_map, find_flag);
    if (!error && pushed_idx_cond) {  // ← ICP 条件存在
        error = pushed_idx_cond->val_int() ? 0 : HA_ERR_END_OF_FILE;
        // 索引条件不满足 → 跳过此行，不触发回表
    }
    return error;
}

pushed_idx_cond 是 Item 类型的指针（MySQL 内部表达式树节点），它包含被下推的 WHERE 条件子集。引擎层扫描索引时，每读到一个符合条件的记录就调用 pushed_idx_cond->val_int() 做校验。

引擎层真实落点：row_search_mvcc 的 row_search_idx_cond_check

Server 层把条件通过 pushed_idx_cond 传给引擎后，真正的过滤发生在 InnoDB 的 row_search_mvcc() 主循环里。每读到一条二级索引记录，先调用 row_search_idx_cond_check()，不命中就直接跳到下一条、不触发回表：

  /* Check if the record matches the index condition. */
  switch (row_search_idx_cond_check(buf, prebuilt, rec, offsets)) {
    case ICP_NO_MATCH:
      prebuilt->try_unlock(true);
      goto next_rec;
    case ICP_OUT_OF_RANGE:
      err = DB_RECORD_NOT_FOUND;
      prebuilt->try_unlock(true);
      goto idx_cond_failed;
    case ICP_MATCH:
      break;
  }

  /* Get the clustered index record if needed, if we did not do the
  search using the clustered index. */

  if (index != clust_index && prebuilt->need_to_access_clustered) {

ICP_NO_MATCH 直接 goto next_rec 跳过本行，绕开了下面 need_to_access_clustered 的分支——那才是真正回表（row_sel_get_clust_rec_for_mysql）的地方。所以”引擎层先过滤”的准确含义是：在二级索引扫描阶段拦下不满足条件的行，避免为它们做随机 IO 回表。

row_search_idx_cond_check() 内部把索引记录中参与条件判断的列转成 MySQL 格式后交给 innobase_index_cond() 求值：

static ICP_RESULT row_search_idx_cond_check(
    byte *mysql_rec,          /*!< out: record
                              in MySQL format (invalid unless
                              prebuilt->idx_cond == true and
                              we return ICP_MATCH) */
    row_prebuilt_t *prebuilt, /*!< in/out: prebuilt struct
                              for the table handle */
    const rec_t *rec,         /*!< in: InnoDB record */
    const ulint *offsets)     /*!< in: rec_get_offsets() */
{
  ICP_RESULT result;
  ulint i;

  ut_ad(rec_offs_validate(rec, prebuilt->index, offsets));

  if (!prebuilt->idx_cond) {
    return (ICP_MATCH);
  }

  MONITOR_INC(MONITOR_ICP_ATTEMPTS);

  /* Convert to MySQL format those fields that are needed for
  evaluating the index condition. */

  if (prebuilt->blob_heap != nullptr) {
    mem_heap_empty(prebuilt->blob_heap);
  }

  for (i = 0; i < prebuilt->idx_cond_n_cols; i++) {
    const mysql_row_templ_t *templ = &prebuilt->mysql_template[i];

    /* Skip virtual columns */
    if (templ->is_virtual) {
      continue;
    }

    if (!row_sel_store_mysql_field(
            mysql_rec, prebuilt, rec, prebuilt->index, prebuilt->index, offsets,
            templ->icp_rec_field_no, templ, ULINT_UNDEFINED, nullptr,
            prebuilt->blob_heap)) {
      return (ICP_NO_MATCH);
    }
  }

  /* We assume that the index conditions on
  case-insensitive columns are case-insensitive. The
  case of such columns may be wrong in a secondary
  index, if the case of the column has been updated in
  the past, or a record has been deleted and a record
  inserted in a different case. */
  result = innobase_index_cond(prebuilt->m_mysql_handler);
  switch (result) {
    case ICP_MATCH:
      /* Convert the remaining fields to MySQL format.
      If this is a secondary index record, we must defer
      this until we have fetched the clustered index record. */
      if (!prebuilt->need_to_access_clustered ||
          prebuilt->index->is_clustered()) {
        if (!row_sel_store_mysql_rec(mysql_rec, prebuilt, rec, nullptr, false,
                                     prebuilt->index, prebuilt->index, offsets,
                                     false, nullptr, prebuilt->blob_heap)) {
          ut_ad(prebuilt->index->is_clustered());
          return (ICP_NO_MATCH);
        }
      }
      MONITOR_INC(MONITOR_ICP_MATCH);
      return (result);
    case ICP_NO_MATCH:
      MONITOR_INC(MONITOR_ICP_NO_MATCH);
      return (result);
    case ICP_OUT_OF_RANGE:
      MONITOR_INC(MONITOR_ICP_OUT_OF_RANGE);
      const auto record_buffer = row_sel_get_record_buffer(prebuilt);
      if (record_buffer) {
        record_buffer->set_out_of_range(true);
      }
      return (result);
  }

  ut_error;
}

prebuilt->idx_cond 为空时直接 ICP_MATCH 放行；否则把 idx_cond_n_cols 个索引列转成 MySQL 格式，再交给 innobase_index_cond() 计算布尔值。三个返回值中 ICP_NO_MATCH 统计并跳过本行、ICP_OUT_OF_RANGE 表示已越过条件范围可提前结束扫描、ICP_MATCH 才继续回表。

引擎层支持的 ICP 条件操作

操作支持说明
= < > <= >= <>✅比较操作符
LIKE 'abc%'✅前缀匹配（索引可使用）
BETWEEN a AND b✅范围比较
IN (1,2,3)✅等同多个等值
IS NULL✅判空
col1 = col2✅同表列间比较
LIKE '%abc'❌后缀模糊匹配（无法用索引）
函数表达式（UPPER(col)）❌需要 Server 层计算
子查询条件❌跨表或依赖外部查询
OR 条件⚠️ 部分取决于具体条件是否能下推

三、EXPLAIN Extra 字段的区分

3.1 Using index condition

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;
-- key: idx_user_id
-- Extra: Using index condition

含义：status = 1 这个条件虽然不在 idx_user_id 中，但被下推到引擎层做过滤。引擎层扫描 idx_user_id 时，对每条记录检查 status = 1（注意：status 不在索引中，但可以在回表前判断——等等，这怎么做到？）。

更正：status 如果不在索引中，引擎层需要先回表拿到 status 值，这已经不是 ICP 了。实际上 Using index condition 出现在索引中包含该列但查询条件不能 100% 用索引的场景：

-- idx_user_status = (user_id, status)
EXPLAIN SELECT * FROM orders WHERE user_id > 100 AND status = 1;
-- key: idx_user_status
-- Extra: Using index condition
-- 原因：user_id > 100 是范围条件，只用到了 user_id 列
--   status 在索引中但不在 key_len 内，被下推到引擎层过滤

💭 想一想：前面说”ICP 只能下推索引里的列”，这里 status 又”不在 key_len 内”却能下推，矛盾吗？——不矛盾。status 虽然没参与这次 B+Tree 定位（key_len 只覆盖 user_id），但它仍”存在”于 idx_user_status 这条索引记录里；引擎层扫描时能直接读到 status 的值、就地判断，不必先回表。ICP 的前提是”列在索引记录里可读”，而不是”列参与了本次定位”。

3.2 Using where

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND name LIKE '%abc';
-- key: idx_user_id
-- Extra: Using where

含义：name LIKE '%abc' 无法下推（后缀模糊匹配，索引无法利用），只能在 Server 层过滤。引擎层返回 user_id=100 的所有行，Server 层逐个检查 name LIKE '%abc'。

3.3 三种 Extra 对比

Extra含义WHERE 过滤位置回表影响
Using index conditionICP 生效引擎层（索引扫描时）减少回表
Using whereServer 层过滤Server 层（返回行后）全部回表
Using index覆盖索引引擎层 + 索引包含所有列无回表
Using where; Using indexServer 层过滤 + 覆盖索引Server 层无回表但需过滤

四、ICP 不生效的场景

4.1 覆盖索引查询自动跳过 ICP

当查询是覆盖索引时，Server 层直接使用索引中的数据，不需要回表。此时 ICP 没有减少回表的意义——因为根本没有回表操作。MySQL 优化器会直接跳过 ICP。

-- 覆盖索引：idx_userid_name_status = (user_id, name, status)
SELECT name, status FROM orders WHERE user_id = 100 AND name LIKE 'A%' AND status = 1;
-- Extra: Using where; Using index
-- 不显示 Using index condition，因为覆盖索引没有回表

4.2 非范围查询的前缀索引

当等值条件精确匹配索引前缀时，引擎层通过 B+Tree 直接定位到匹配的记录，不需要逐条过滤。此时 ICP 也没有额外价值：

-- idx_userid_name = (user_id, name)
SELECT * FROM orders WHERE user_id = 100 AND name = 'Alice';
-- 两个条件都是等值，完全利用索引精确定位
-- Extra 中不会出现 Using index condition

4.3 主键查询

主键查询（WHERE id = X）直接定位聚簇索引行，不存在回表概念。ICP 不适用。

4.4 全文索引

全文索引（FULLTEXT）使用独立的倒排索引结构，不支持 ICP。

五、定量分析：ICP 减少了多少次回表？

-- 一张表 orders(1,000,000 行)
-- idx_user_age = (user_id, age)
-- 查询：user_id >= 100 AND age > 50
-- 假设：user_id >= 100 匹配 600,000 行，其中只有 200,000 行 age > 50

-- 无 ICP：
--   索引扫描 600,000 行 → 回表 600,000 次 → Server 过滤掉 400,000 行
--   → 回表 600,000 次，Server 过滤 400,000 行

-- 有 ICP：
--   索引扫描 600,000 行 → age > 50 过滤掉 400,000 行 → 回表 200,000 次
--   → 回表次数减少 66.7%

但注意：ICP 不减少索引扫描的行数。符合 user_id >= 100 的 600,000 条索引记录仍然被扫描。ICP 只减少了回表——然而在 BufferPool 命中的情况下，回表的代价远低于索引扫描的代价（回表是一次随机 IO/内存查找，索引扫描是顺序读取）。所以 ICP 的实际收益取决于 BufferPool 命中率——如果索引和聚簇索引都在内存中，ICP 的收益主要来自减少 CPU 层面的行过滤开销。

💭 想一想：为什么 ICP 的收益要”看 BufferPool 脸色”？——因为 ICP 省的是”随机回表”；当聚簇索引页全在内存里时，回表不过是一次内存查找、几乎不贵，省下的随机 IO 价值就缩水了，剩下只有”少算几行过滤”的 CPU 收益。反之磁盘回表昂贵时，ICP 少回表一次就是实打实的 IO 节省——所以”省随机 IO”才是 ICP 的主战场。

六、ICP 与 MRR 的协同

ICP（Index Condition Pushdown）常与 MRR（Multi-Range Read）配合使用：

-- ICP 先减少需要回表的行
-- MRR 再将需要回表的行按主键排序，批量回表
-- 协同效果：先过滤 + 再排序 + 再批量回表 = 最大化减少随机 IO
EXPLAIN SELECT * FROM orders 
WHERE user_id > 100 AND age > 50 
ORDER BY id;
-- Extra: Using index condition; Using MRR

MRR 的排序将随机 IO（按二级索引顺序回表）转化为顺序 IO（按主键顺序回表），ICP 减少了需要回表的行数——两者叠加产生 1+1>2 的效果。

开启 MRR

SET optimizer_switch = 'mrr=on,mrr_cost_based=off';
-- mrr_cost_based=off：强制使用 MRR（而不是让优化器基于成本决定）

章末提问

追问 1：ICP 到底减少了什么、没减少什么？

回答思路：结论先行——只减少”回表次数”，不减少”索引扫描行数”。因为扫描发生在二级索引里是顺序读、本身便宜，回表是随机跳到聚簇索引读完整行、最贵；ICP 在引擎层扫描时对每条索引记录用下推条件判断，不满足就直接跳过、不回表。索引记录照样全扫一遍，只是把无意义的随机回表拦了下来。

追问 2：为什么覆盖索引会自动跳过 ICP？

回答思路：结论先行——因为覆盖索引根本没有回表，ICP 失去了优化的对象。因为 ICP 的价值是”在回表之前先过滤、减少回表”；覆盖索引查询所需列都在索引里、不需要回表，也就没有”无意义回表”可省，优化器直接跳过 ICP，EXPLAIN 里显示 Using where; Using index 而不是 Using index condition。

追问 3：ICP 和 MRR 是怎么配合的？为什么说 1+1>2？

回答思路：结论先行——ICP 先过滤减少要回表的行，MRR 再把剩下的行按主键排序批量顺序回表。因为 ICP 把回表行数砍掉一截，MRR 又把这批回表从”随二级索引顺序的随机 IO”变成”按主键顺序的顺序 IO”；一个减量、一个提质，叠加起来比各自单独用更省随机 IO，所以是 1+1>2。

Share this post on:
Share this post via WhatsAppShare this post on FacebookShare this post on XShare this post via TelegramShare this post on PinterestShare this post via email
Previous Post
MySQL 自适应哈希索引（AHI）：BufferPool 内的自动加速器
Next Post
MySQL并行回放MTS