联合索引：为什么范围查询后索引就失效了？

一句话结论（30s）

联合索引的最左前缀是 B+ 树按多字段逐级排序的物理结果，不是 MySQL 的人为限制。关键设计在于：只有从最左列开始逐列匹配，B+ 树的有序性才成立；范围查询后的列之所以失效，是因为跨过范围边界后该列不再全局有序。权衡在于：多列索引能加速前缀等值/范围匹配，却无法单独服务跳过首列或范围后列的过滤，需要靠 ICP（减少回表）或覆盖索引补足。

核心原理（2min）

联合索引 idx(a, b, c) 在 B+ 树中先按 a 排序、a 相同按 b、b 相同按 c。查询必须从最左列 a 开始逐列匹配才能利用有序性定位，跳过 a 直接查 b 或 c 时它们在全局是无序的，所以走不了索引。WHERE a = 1 AND b > 10 AND c = 5 中，a=1 精确定位、b>10 走范围扫描，但跨 b 值后 c 不再全局有序，故 c=5 无法走索引定位、只能逐行判断。此时索引下推（ICP）把 c=5 的过滤下推到存储引擎层，扫描索引时先判断 c 是否等于 5，不满足就不回表，从而减少随机回表 IO——但它不能消除范围扫描本身，仍需遍历所有满足 a=1 AND b>10 的索引记录。

底层深入（5-10min）

联合索引的物理存储

联合索引 idx(a, b, c) 在 B+ 树中先按 a 排序，a 相同按 b 排序，b 相同按 c 排序：

(1, 1, 1)
(1, 2, 3)
(1, 5, 2)
(2, 1, 4)
(2, 3, 1)
(3, 1, 5)
...

这套”先按 a、再按 b、再按 c”的排序，落到 InnoDB 源码里就是 cmp_dtuple_rec_with_match_low()：比较一条索引元组 dtuple 与页内一条物理记录 rec 时，从字段 0 开始逐字段比较，任何一个字段不等就立刻返回：

int cmp_dtuple_rec_with_match_low(const dtuple_t *dtuple, const rec_t *rec,
                                  const dict_index_t *index,
                                  const ulint *offsets, ulint n_cmp,
                                  ulint *matched_fields) {
  ut_ad(dtuple_check_typed(dtuple));
  ut_ad(rec_offs_validate(rec, index, offsets));

  ut_ad(n_cmp > 0);
  ut_ad(*matched_fields == DISABLE_MIN_REC_FLAG_CHECK ||
        *matched_fields <= n_cmp);
  ut_ad(n_cmp <= dtuple_get_n_fields(dtuple));
  ut_ad(*matched_fields == DISABLE_MIN_REC_FLAG_CHECK ||
        *matched_fields <= rec_offs_n_fields(offsets));

  if (*matched_fields == 0) {
    ulint rec_info = rec_get_info_bits(rec, rec_offs_comp(offsets));
    ulint tup_info = dtuple_get_info_bits(dtuple);

    /* The leftmost node pointer record is defined as
    smaller than any other node pointer, independent of
    any ASC/DESC flags. It is an "infimum node pointer". */
    if (rec_info & REC_INFO_MIN_REC_FLAG) {
      return (!(tup_info & REC_INFO_MIN_REC_FLAG));
    } else if (tup_info & REC_INFO_MIN_REC_FLAG) {
      return (-1);
    }
  } else if (*matched_fields == DISABLE_MIN_REC_FLAG_CHECK) {
    /* Disable the left most node check. */
    *matched_fields = 0;
  }

  /* Compare fields in a loop. */
  for (auto i = *matched_fields; i < n_cmp; ++i) {
    const auto dtuple_field = dtuple_get_nth_field(dtuple, i);

    const auto dtuple_b_ptr =
        static_cast<const byte *>(dfield_get_data(dtuple_field));

    const auto type = dfield_get_type(dtuple_field);

    auto dtuple_f_len = dfield_get_len(dtuple_field);

    /* We should never compare against an externally
    stored field.  Only clustered index records can
    contain externally stored fields, and the first fields
    (primary key fields) should already differ. */
    ut_ad(!rec_offs_nth_extern(index, offsets, i));

    /* So does the field with default value */
    ut_ad(!rec_offs_nth_default(index, offsets, i));

    ulint rec_f_len;

    const auto rec_b_ptr =
        rec_get_nth_field(index, rec, offsets, i, &rec_f_len);

    ut_ad(!dfield_is_ext(dtuple_field));

    int ret{};

    if (dfield_is_multi_value(dtuple_field) &&
        (dtuple_f_len == UNIV_MULTI_VALUE_ARRAY_MARKER ||
         dtuple_f_len == UNIV_NO_INDEX_VALUE)) {
      /* If it's the value parsed from the array, or NULL, then
      the calculation can be done in a normal way in the else branch */
      ut_ad(index->is_multi_value());
      if (dtuple_f_len == UNIV_NO_INDEX_VALUE) {
        ret = 1;
      } else {
        multi_value_data *mv_data =
            static_cast<multi_value_data *>(dtuple_field->data);
        ret = mv_data->has(type, rec_b_ptr, rec_f_len) ? 0 : 1;
      }
    } else {
      /* For now, change buffering is only supported on
      indexes with ascending order on the columns. */
      ret = cmp_data(
          type->mtype, type->prtype,
          dict_index_is_ibuf(index) || index->get_field(i)->is_ascending,
          dtuple_b_ptr, dtuple_f_len, rec_b_ptr, rec_f_len);
    }

    if (ret) {
      *matched_fields = i;
      return (ret);
    }
  }

  /* If we ran out of fields, dtuple was equal to rec up to the common fields */
  *matched_fields = n_cmp;

  return (0);
}

循环变量 i 从 *matched_fields 走到 n_cmp，n_cmp 就是 tuple 里参与比较的前缀字段数；每一轮用 cmp_data 比较第 i 个字段，一旦 ret != 0 立即返回，并把 *matched_fields 记成 i。这意味着联合索引的比较是”字典序”式的逐字段比较——a 不同就停在 a，a 相同才继续比 b，b 相同才比 c。这就是最左前缀的物理根源：跳过 a 直接对 b、c 做比较，全局记录根本不会按 b、c 有序排列，逐字段比较自然无从谈起。

最左前缀原则

思考：为什么说”最左前缀”是物理结果，而不是 MySQL 故意定的规矩？——因为联合索引在 B+ 树里就是按”字段 0、再字段 1、再字段 2”逐级排序落盘的：a 相同才比 b，b 相同才比 c。跳过 a 直接看 b 或 c，全局记录根本不按它们有序，B+ 树的分层二分定位无从下手——这不是”规则禁止”，是”数据根本没排成那样”。

只有从索引的最左列开始逐列匹配，B+ 树的有序性才起作用：

-- ✅ 走索引：a 是最左列，范围在索引中有序
WHERE a = 1

-- ✅ 走索引：a 精确 → b 在 a=1 的范围内有序
WHERE a = 1 AND b > 10

-- ❌ 不走索引：跳过了 a，b 在全局中无序
WHERE b = 10

-- ❌ 不走索引：跳过了 a，c 在全局中无序
WHERE c = 5

B+ 树的查找入口是 btr_cur_search_to_nth_level()：从根节点出发，逐层下降到叶子。每一层它都调用 page_cur_search_with_match() 在当前页内做二分定位，再根据命中的 node pointer 下到子节点：

  } else if (height == 0 && btr_search_enabled &&
             !dict_index_is_spatial(index)) {
    /* The adaptive hash index is only used when searching
    for leaf pages (height==0), but not in r-trees.
    We only need the byte prefix comparison for the purpose
    of updating the adaptive hash index. */
    page_cur_search_with_match_bytes(block, index, tuple, page_mode, &up_match,
                                     &up_bytes, &low_match, &low_bytes,
                                     page_cursor);
  } else {
    /* Search for complete index fields. */
    up_bytes = low_bytes = 0;
    page_cur_search_with_match(block, index, tuple, page_mode, &up_match,
                               &low_match, page_cursor,
                               need_path ? cursor->rtr_info : nullptr);
  }

page_cur_search_with_match() 在页内先对目录槽做二分查找，每取一条中间记录 mid_rec 就调用 tuple->compare(mid_rec, ...) 按字段顺序比较：

void page_cur_search_with_match(const buf_block_t *block,
                                const dict_index_t *index,
                                const dtuple_t *tuple, page_cur_mode_t mode,
                                ulint *iup_matched_fields,

                                ulint *ilow_matched_fields,

                                page_cur_t *cursor, rtr_info_t *rtr_info) {
  ...
  /* Perform binary search until the lower and upper limit directory
  slots come to the distance 1 of each other */

  while (up - low > 1) {
    mid = (low + up) / 2;
    slot = page_dir_get_nth_slot(page, mid);
    mid_rec = page_dir_slot_get_rec(slot);

    cur_matched_fields = std::min(low_matched_fields, up_matched_fields);

    auto offsets = get_mid_rec_offsets();

    cmp = tuple->compare(mid_rec, index, offsets, &cur_matched_fields);

    if (cmp > 0) {
    low_slot_match:
      low = mid;
      low_matched_fields = cur_matched_fields;

    } else if (cmp) {
#ifdef PAGE_CUR_LE_OR_EXTENDS
      if (mode == PAGE_CUR_LE_OR_EXTENDS &&
          page_cur_rec_field_extends(tuple, mid_rec, offsets,
                                     cur_matched_fields, index)) {
        goto low_slot_match;
      }
#endif /* PAGE_CUR_LE_OR_EXTENDS */
    up_slot_match:
      up = mid;
      up_matched_fields = cur_matched_fields;

    } else if (mode == PAGE_CUR_G || mode == PAGE_CUR_LE
#ifdef PAGE_CUR_LE_OR_EXTENDS
               || mode == PAGE_CUR_LE_OR_EXTENDS
#endif /* PAGE_CUR_LE_OR_EXTENDS */
    ) {
      goto low_slot_match;
    } else {
      goto up_slot_match;
    }
  }

这里的 tuple->compare(...) 最终调用上一节的 cmp_dtuple_rec_with_match_low()，tuple 的 n_fields_cmp 决定了参与比较的前缀列数。整棵树的有序性正是建立在”页内按字段 0、1、2 逐级排序”之上——所以只有把最左列 a 放进比较范围，二分定位才有意义；跳过 a 只看 b，页内记录并不按 b 有序，二分查找直接失效。

范围查询的”断裂”效应

WHERE a = 1 AND b > 10 AND c = 5

思考：a=1 AND b>10 AND c=5 里，a、b 都能用索引，凭什么 c 就”失效”了？——关键在”全局有序”四个字：在某个固定 b 值内部，c 确实按大小排好了；但 B+ 树遍历要跨过很多个 b 值，一旦跨值，c 的排序就被”重置打乱”，整体看 c 并不有序，自然无法二分定位，只能逐行判断。

在 a=1, b>10 的范围内，行按 b 排序。b=11 时 c 有序，b=12 时 c 也有序——但跨 b 值后 c 不再是全局有序的。B+ 树的遍历是按 b 递增的，在跨 b 的边界处，c 的值会”跳跃”。因此 c = 5 无法走索引定位，只能逐行判断。

这就是”范围查询后的列索引失效”的数学本质——跨值后该列不再全局有序。

索引下推（ICP — Index Condition Pushdown）

ICP 把 c = 5 的过滤推到了存储引擎层。在扫描索引时（尚未回表），InnoDB 先检查 c 是否等于 5——不等于则直接跳过这一行，不回表。

无 ICP: 索引扫描 → 每行都回表读完整行 → 在 Server 层判断 c=5 → 多数回表是浪费
有 ICP: 索引扫描 → 在索引中先判断 c=5 → 只有 c=5 的行才回表

EXPLAIN 的 Extra 列出现 Using index condition 表示启用了 ICP。ICP 减少回表次数，但不能消除范围扫描本身——仍需遍历满足 a=1 AND b>10 的所有索引记录。

思考：ICP 既然能减少回表，为什么不能把范围扫描也”省掉”？——因为 ICP 只是”过滤的搬运工”，不是”排序的还原剂”：c=5 在跨 b 后已经全局无序，索引遍历必须老老实实扫完所有满足 a=1 AND b>10 的记录，ICP 只能在”要不要回表”这一步做减法，无法改变”必须遍历这些记录”这个前提。

源码佐证：ICP 在回表前先过滤（row_search_idx_cond_check）

row_search_mvcc() 是 InnoDB 行扫描的主循环。每读到一条二级索引记录，先调用 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 的回表分支。只有 ICP_MATCH 才会进入回表逻辑（row_sel_get_clust_rec_for_mysql）取完整行。这就是”引擎层先过滤”的落点：把 c=5 的判断放在二级索引扫描阶段，拦下不满足条件的行，避免随机回表 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() 计算布尔值。三个返回值分别对应”命中→继续回表 / 不命中→跳过 / 越界→终止扫描”，其中 MONITOR_ICP_* 是统计 ICP 命中与跳过次数的埋点。

五种经典索引失效场景

总结

联合索引的最左前缀是 B+ 树多字段排序的物理结果，不是 MySQL 的人为限制。理解 B+ 树中跨字段的顺序性，就能准确判断每个查询到底能用到几列索引。

章末提问

1. 为什么联合索引必须满足”最左前缀”？是 MySQL 的硬性限制吗？ 结论先行：不是硬性限制，而是 B+ 树按多字段逐级排序的物理结果。因为联合索引的键在树里先按 a、再按 b、再按 c 排序，只有从 a 开始比较，“有序”这一前提才成立；跳过 a 直接查 b 或 c，它们在全局是乱序的，二分定位无从谈起。

2. WHERE a = 1 AND b > 10 AND c = 5，到底哪几列用到了索引？ 结论先行：a 做精确等值定位、b 做范围扫描，c 用不上索引只能逐行过滤。因为 a=1 能精确定位到区间起点，b>10 在 a=1 的范围内有序、可范围扫描；而跨过不同 b 值后 c 不再全局有序，无法走索引，只能依赖 ICP 在引擎层过滤、减少回表。

3. 为什么范围查询之后的列一定会”失效”？ 结论先行：因为跨范围边界后，后续列不再全局有序。因为 B+ 树遍历按 b 递增，b 每变一个值，c 的排列就重新开始一轮，整体看 c 是”分段有序、全局无序”；索引定位依赖全局有序，故只能逐行判断。

4. ICP（索引下推）到底优化了什么？不能优化什么？ 结论先行：优化了”回表次数”，不能优化”扫描范围”。因为 ICP 把 c=5 的过滤下推到存储引擎，扫描二级索引时先判断、不满足就不回表，省的是随机回表 IO；但 c 已无法参与索引定位，仍需遍历所有满足 a=1 AND b>10 的索引记录，扫描的行数一分不少。

5. 函数、隐式类型转换、前导通配符为什么都会让索引失效？ 结论先行：因为它们都破坏了”索引列有序”这一可用前提。因为索引存的是列的原始值按序排列，func(col)、隐式转换（本质是函数）、LIKE '%abc' 都让”查询条件”和”索引里存的值”对不上、或无法利用前缀有序性，优化器无法用索引做定位，只能放弃。