MySQL InnoDB 索引与事务

B+ 树为何做索引 · 聚簇/二级索引 · 联合索引最左前缀 · 索引下推 ICP · 索引失效 · MVCC（undo 版本链 + ReadView + 快照/当前读）· 隔离级别与幻读

想跳出 InnoDB 一家看数据模型与存储层的通用抽象（页 + 磁盘指针 + 缓冲池 + WAL、B+ 树 vs LSM、七大范式与向量索引），见 数据库范式与存储引擎。本篇是具体实现视角，那篇是范式与原理视角。

场景问题

打个比方（MVCC）：InnoDB 给每一行数据都留了一叠"历史版本快照"（undo 版本链）。事务一进门，先领一张门票（ReadView），票面上写死了"此刻哪些人的改动算数、哪些当它没发生过"。之后你的快照读永远只看你进门那一刻的世界——别人后来怎么改、怎么提交，都与你无关。正因如此，读不加锁、读写互不打架，并发一下就上去了。类比失效边界：在 RR 隔离级别下，这张门票在第一次快照读时生成、整个事务复用同一张（所以你全程看到同一个世界）；但 select ... for update、update 这类当前读会无视门票、直接读最新版本并加锁。这就是同一个事务里"普通 select 和 for update select 看到的数据竟然不一样"的诡异来源，也是幻读的经典坑——得靠间隙锁去堵。

高频追问链路，一步步深挖：

实现方案

为什么 B+ 树做索引

• 树高 ≈ 磁盘 IO 次数：InnoDB 一页 16KB。B+ 树非叶子节点只存 key + 指针，扇出极大——3 层就能索约 2000 万行，即热点几乎全在内存、一次查询 2~4 次 IO。
• 叶子节点串成有序双向链表：天然支持范围查询 / ORDER BY / 分页，扫描不用回中间节点。
• 对比：B 树每个节点都存数据 → 扇出小、树更高；红黑树是二叉、树高 log₂N 远大于 B+ 树、且节点分散不利磁盘顺序读；Hash 索引 O(1) 等值快，但不支持范围、排序、最左前缀，还有 rehash 抖动。

聚簇索引 vs 二级索引，回表与覆盖索引

• 聚簇索引（主键）：叶子节点直接存整行数据，表数据就是按主键组织的 B+ 树。没有主键时 InnoDB 用唯一非空索引，再没有就生成隐藏 rowid。
• 二级索引（辅助索引）：叶子节点存索引列 + 主键值。用二级索引查非索引列，需拿主键回表再查一次聚簇索引。
• 覆盖索引：查询列全在二级索引里（含主键）→ 无需回表，EXPLAIN 里 Extra: Using index。这是最常用的优化手段。

聚簇索引 (主键 id)              二级索引 (name)
        [ ...id... ]                [ ...name... ]
       /     |     \              /       |
   叶子: 整行数据           叶子: name + id(主键) --回表--> 聚簇索引取整行

联合索引与最左前缀

联合索引 (a, b, c) 按 a、再 b、再 c 排序。命中规则：

• 从最左列连续使用才走索引：a ✅ / a,b ✅ / a,b,c ✅ / b,c ❌（缺 a）
• 范围列之后的列失效：a=? AND b>? AND c=? → a、b 走索引，c 因为 b 是范围而无法用于索引定位（但 ICP 下 c 仍可在引擎层过滤）
• ORDER BY 也遵循最左前缀，否则 Using filesort

索引下推 ICP（Index Condition Pushdown，5.6+）

不满足最左前缀但涉及索引列的条件，下推到存储引擎层用二级索引先过滤，再决定是否回表：

-- 联合索引 (name, age)，查 name 前缀 + age
SELECT * FROM t WHERE name LIKE '张%' AND age = 20;

• 无 ICP：引擎按 name LIKE '张%' 取出所有匹配行的主键，逐条回表拿整行，再由 Server 层判 age=20 → 回表次数多。
• 有 ICP：引擎在二级索引上就用 age=20 过滤（age 也在索引里），只对同时满足的行回表 → 大幅减少回表。Extra: Using index condition。

索引失效常见写法

• 索引列上用函数/运算：WHERE YEAR(created)=2026、WHERE id+1=5
• 隐式类型转换：phone 是 varchar 却 WHERE phone=138...（数字），触发全表转换
• LIKE '%x' 前导通配符（'x%' 可走）
• 不满足最左前缀 / 范围列后续列
• OR 两侧有一侧无索引（可用 UNION 或改写）
• 优化器估算走索引不如全表扫（区分度低，如性别列）时主动放弃

MVCC：undo 版本链 + ReadView + 隐藏列

每行有隐藏列 DB_TRX_ID（最近修改的事务 id）、DB_ROLL_PTR（回滚指针，指向 undo log 里的旧版本）。多次修改把旧版本串成 undo 版本链。

ReadView（一致性视图） 记录生成时刻的活跃事务快照，含：m_ids（活跃未提交事务集）、min_trx_id、max_trx_id、creator_trx_id。可见性判定沿版本链逐版本比对某版本的 DB_TRX_ID：

• 小于 min_trx_id → 已提交，可见
• 大于等于 max_trx_id → 视图生成后才开始的事务，不可见，沿 DB_ROLL_PTR 找更旧版本
• 落在区间内：在 m_ids 中 → 未提交不可见；不在 → 已提交可见

两种读：

• 快照读：普通 SELECT，读 ReadView 可见版本，不加锁，靠 MVCC
• 当前读：SELECT ... FOR UPDATE / LOCK IN SHARE MODE、UPDATE/DELETE/INSERT，读最新版本并加锁

隔离级别与幻读

InnoDB 在 RR 下如何压制幻读：

• 快照读：整个事务复用同一个 ReadView → 多次读结果一致，看不到别人新插入的行
• 当前读：靠 Next-Key Lock（记录锁 + 间隙锁） 锁住区间，阻止其他事务在间隙里 INSERT，从而挡住当前读维度的幻读
• 注意「快照读 + 当前读混用」仍可能观察到幻影，严格场景需显式当前读加锁

为什么这么做

• B+ 树对齐磁盘特性：数据库瓶颈是随机 IO，B+ 树把「一次比较尽量多排除」和「叶子顺序扫」同时做到，是磁盘存储引擎的最优解。
• MVCC 换来读写不互斥：快照读不加锁，读不阻塞写、写不阻塞读，把并发度拉满；一致性靠 ReadView 的可见性算法在读时“算”出来，而非靠锁“堵”出来。
• RR 做默认：MySQL 主从早期基于 binlog 复制，RR + 间隙锁能避免语句级复制下主从数据不一致，历史与安全性共同决定了默认级别。

为什么别的选择不行

• Hash 索引：等值 O(1) 很香，但范围、排序、LIKE 前缀、最左前缀全不支持，且冲突/rehash 抖动 → 只做 Memory 引擎或自适应哈希的补充，不做主力。
• 纯悲观锁（读也加锁）：并发一落千丈，读多写少场景吞吐塌方 → 用 MVCC 让绝大多数读走无锁快照。
• 靠应用层控制版本 / 乐观锁：能解决单点更新冲突，但无法提供事务级一致性视图与跨行范围保护 → 一致性读和幻读仍要引擎级 MVCC + 间隙锁兜底。

沉淀结论

面试速答清单：

• B+ 树矮胖 + 叶子有序链表：IO 少、支持范围排序；胜过 B 树/红黑树/Hash
• 聚簇索引存整行，二级索引存主键 → 回表；查询列全命中索引即覆盖索引免回表
• 联合索引最左前缀，范围列之后失效；ORDER BY 同理
• ICP 把索引条件下推引擎层过滤，减回表（Using index condition）
• 索引失效：函数/隐式转换/前导 %/OR/破坏最左前缀/区分度太低
• MVCC = undo 版本链 + ReadView 可见性判定；快照读不加锁、当前读加锁
• RC 每次快照读新建 ReadView，RR 首次生成后复用；RR 靠 MVCC + Next-Key Lock 压制幻读

记忆口诀

• 索引本质：B+树矮胖 / 叶子有序链表 / 树高≈IO次数
• 回表三件套：聚簇存整行 / 二级存主键 / 覆盖索引免回表
• 命中规则：最左前缀 / 范围列后失效 / ICP下推减回表
• MVCC：undo版本链 / ReadView可见性 / 快照读不加锁
• 隔离：RC每次新建视图 / RR首次复用 / Next-Key Lock堵幻读

内容来源

关键点整理自 lifei6671/interview-go（mysql/mysql-mvcc.md、mysql/mysql-index-b-plus.md、mysql/mysql-interview.md 及 mysql/0001-0002.md 索引下推/失效），结合 InnoDB 官方文档重写为五段式。请以官方文档为准。

自测：合上资料能说清楚吗？

1. 为什么 InnoDB 用 B+ 树做索引，而不用 B 树、红黑树或 Hash？请对比这几种结构。

2. 什么是回表？覆盖索引如何避免它？

3. 联合索引 (a,b,c) 在 a=? AND b>? AND c=? 下如何命中？

4. MVCC 如何判断某版本对当前事务可见？

5. RC 与 RR 的快照读有何区别？RR 又如何压制幻读？