Optimizer 与 Handler：ICP、MRR 与存储引擎边界

EXPLAIN 显示 Using index condition 时，部分 WHERE 谓词在 InnoDB 层 对二级索引记录求值，而非全部回表后在 Server 过滤——这是 ICP（Index Condition Pushdown）。Using mrr 则表示 MRR 批量收集主键再排序回表，把随机 IO 变成顺序 IO。二者都发生在 handler 接口 之下，优化器只选是否启用。

本文不展开 Server 层代价模型全文，只钉住 InnoDB 提供的能力与代价，对照 PG 执行器。版本：MySQL 8.0.36。

一、Handler 在架构中的位置

flowchart TD
  SQL[SQL 层] --> OPT[Optimizer]
  OPT --> H[handler 接口]
  H --> INN[ha_innobase]
  INN --> ROW[row0sel / row0upd]
  ROW --> BTR[B+Tree]
  ROW --> BUF[Buffer Pool]

handlerton 注册引擎能力（事务、索引类型、ICP 标志）。优化器通过 TABLE::file 调用 index_read、index_next、rnd_next 等。

源码：sql/handler.h、sql/handler.cc、storage/innobase/handler/ha_innodb.cc。

二、核心 handler 方法

ha_innobase::index_read 最终进入 row_search_mvcc（一致性读）或加锁读路径（第 7–9 篇）。

三、ICP：Index Condition Pushdown

优化器把 可用索引列无法完全覆盖 的谓词，以 ITEM 形式传给引擎；ha_innobase 在 二级索引记录 上调用 index_cond 回调。

-- 需本地验证
EXPLAIN SELECT * FROM t WHERE a > 10 AND b LIKE 'x%';
-- 索引 (a,c)，谓词含 b 时可能出现 Using index condition

收益：减少回表次数。代价：引擎内求值 CPU；某些函数不可下推。

源码：sql/opt_range.cc（计划）、storage/innobase/handler/ha_innodb.cc（innobase_index_cond）。

四、MRR 与 DS-MRR

Multi-Range Read：先扫二级索引收集主键到 buffer，按 PK 排序 后批量回表。

对 高选择性 范围查 + 宽表，MRR 显著降随机 IO；低选择性可能增加内存与排序开销。

五、覆盖索引与 EXPLAIN 解读

Using index：所需列均在同一二级索引中（第 11 篇）。Using where 在 Server 或引擎过滤。

EXPLAIN ANALYZE（8.0.18+）显示 实际行数与循环耗时——排查 ICP/MRR 是否生效的首选（需本地验证）。

六、代价边界

优化器用 统计信息（mysql.innodb_index_stats）估计行数；InnoDB 提供 info()、scan_time() 等粗粒度代价。 不准确统计 导致选错「索引+回表」vs「全表扫描」——与 PG 错误 n_distinct 同类。

七、实验（需本地验证）

SET optimizer_switch='mrr=on,mrr_cost_based=off';
SET optimizer_switch='index_condition_pushdown=on';

EXPLAIN ANALYZE SELECT id, col FROM wide WHERE secondary_key BETWEEN 1 AND 10000;
-- 对比 mrr on/off 的 actual time

八、工程坑点

以为 ICP 等于索引全覆盖。只能下推与索引列相关的条件组合。

关闭 MRR 后抱怨随机 IO。宽表二级索引范围查可显式测试 MRR。

8.0 隐式主键与覆盖索引设计。无显式 PK 时用隐藏 DB_ROW_ID，影响回表语义。

九、关键要点

1. handler 是优化器与 InnoDB 的唯一边界。
2. ICP 在索引记录上过滤，减少回表。
3. MRR 排序主键后批量回表，优化 IO 形态。
4. 覆盖索引 避免回表；与 ICP/MRR 正交。
5. 计划问题先 EXPLAIN ANALYZE，再下钻 handler 路径。

十一、PG 对照

PG 第 12 篇 在计划节点层下推；MySQL 在 存储引擎 API 层下推——读懂 ha_innobase::index_read 即可对齐直觉。

上一篇：主从复制机制

下一篇：Change Buffer 与 AHI

参考资料

源码（MySQL 8.0.36）

• storage/innobase/ — 引擎实现
• sql/binlog.cc, sql/handler.cc — Server 层交界

官方文档

• MySQL 8.0 Reference Manual, InnoDB / Replication / Backup

相关文章

• 系列索引
• PostgreSQL 内核系列