MVCC导致索引覆盖失效场景

MVCC（多版本并发控制）确实会导致索引覆盖失效，从而触发回表。

很多开发者认为只要 SQL 的字段都在联合索引里，就一定能走“索引覆盖（Using index）”，但在高并发写场景下，这并不绝对。

其根本原因在于 MySQL InnoDB 存储引擎的二级索引（Secondary Index）结构设计。

---

核心原因：二级索引“不完整”

在 InnoDB 中，MVCC 的实现依赖于数据行中隐藏的两个字段：

1. DB_TRX_ID：最近修改该行数据的事务 ID。
2. DB_ROLL_PTR：回滚指针，指向 Undo Log。

关键点来了：

这两列隐藏字段只存在于聚簇索引（主键索引）的叶子节点中。普通的二级索引（非主键索引）是不存储 DB_TRX_ID 的

这意味着：当你扫描二级索引时，MySQL 无法仅凭二级索引中的数据判断这条记录“对于当前事务是否可见”。

---

失效的具体过程

假设你有一个表 users，索引是 idx_name (name)。

SQL：SELECT name FROM users WHERE name = ‘Alice’;（理论上应该走索引覆盖）

1. 正常情况（无并发修改）

InnoDB 在二级索引页（Page）头部维护了一个 PAGE_MAX_TRX_ID 属性，记录了修改过该页面的最大事务 ID。

• 如果 PAGE_MAX_TRX_ID < 当前事务的 Read View 最小活跃事务 ID min_trx_id。
• 说明该页面的所有数据都是“旧”的，肯定对当前事务可见。
• 结果： 放心使用索引覆盖，不需要回表。

2. 并发修改情况（MVCC 导致失效）

如果在你查询的同时，有其他事务（哪怕是刚提交的）修改了 name='Alice' 所在的二级索引页（比如插入了 ‘Bob’，但他俩在一个 Page 上）：

• 该 Page 的 PAGE_MAX_TRX_ID 变大，变得“太新了”（大于当前事务 Read View）。
• 此时，MySQL 扫描到 ‘Alice’ 这条索引记录时，心里会发慌：“这页被新事务动过，虽然 ‘Alice’ 看着没变，但我没法确定这条索引记录是不是最新版本，或者是不是被删了。”
• 结果： 哪怕你需要的数据 name 就在手边，InnoDB 也不敢直接返回。它必须拿着主键 ID，回表到聚簇索引中，找到包含 DB_TRX_ID 的完整行，做一次 MVCC 可见性判断。
• 结论：索引覆盖失效。

---

这种失效的特点

1. 隐蔽性强： EXPLAIN 有时可能还会显示 Using index（因为执行计划是预测的），但实际执行时产生了大量的随机 I/O 回表。
2. 瞬时性： 一旦那些活跃的事务提交，且相关的数据页不再频繁变动，后续的查询又会恢复成正常的索引覆盖。
3. 性能抖动： 这解释了为什么在数据更新频繁的业务高峰期，某些原本极快的“纯读”查询（Top SQL）耗时会偶尔飙升。

对比：PostgreSQL 的解决方案

为了解决这个问题，PostgreSQL 引入了 Visibility Map（可见性映射表）。

它用一个位图标记哪些 Page 是“全可见”的。如果位图显示该页所有元组都可见，就直接走 Index Only Scan；否则才回表查 MVCC 版本。MySQL 目前主要依赖 PAGE_MAX_TRX_ID 做类似的判断，但粒度较粗（Page 级）。

总结

MVCC 机制决定了二级索引并不总是“可信”的。 当数据页处于“脏”或“由于并发写入导致版本过新”的状态时，为了保证数据一致性（Repeatable Read），MySQL 必须放弃索引覆盖，强制回表“验明正身”。