MySQL02丨沿着一条查询 SQL 理解 B+树、回表、MVCC 和慢 SQL

type

Post

status

Published

date

Dec 15, 2025

slug

mysql2

summary

一、查询首先要解决的问题：怎么快速找到数据？

假设有一张用户表：

现在执行：

MySQL 首先要解决的问题是：

怎么从大量数据里快速找到 id = 100 这一行？

最朴素的方式是全表扫描。

但如果表里有几百万、几千万行，全表扫描就太慢了。

所以数据库需要索引。

InnoDB 里最核心的索引结构，就是 B+树。

1. B+树真正解决的是磁盘 IO 问题

很多人提到 B+树，第一反应是：

B+树查询快，范围查询也快。

这句话没错，但还不够。

更准确地说，InnoDB 使用 B+树，是因为：

B+树非常适合磁盘页模型，可以用很少的磁盘 IO 定位大量数据。

InnoDB 的数据不是一行一行随便放在磁盘上的，而是按页管理的。

默认情况下，一个页大小是 16KB。

也就是说，InnoDB 读磁盘时，通常不是只读某一行，而是把一个 16KB 的页读进内存。

所以数据库索引最关心的不是抽象的 O(logN)，而是：

一次查询到底需要读取多少个数据页。

B+树是一棵多叉树。

一个非叶子节点里可以放很多索引键和页指针。

即使考虑页头、记录头、页目录等额外开销，一个 16KB 的页通常也能容纳很多索引项。

这就意味着：

B+树的单节点扇出很高，树高很低。

在几千万级数据量下，B+树通常三四层就够了。

B+树核心参数计算： InnoDB页大小默认16KB。假设一个bigint类型的主键占8字节，指针占6字节，一个索引条目共14字节。那么一个非叶子节点能存的索引条目数：16KB ÷ 14字节 ≈ 1170个。

高度为2的B+树能存的数据量：叶子节点数 × 每页记录数。每个叶子节点同样16KB，假设一行数据1KB，每页16条。那么总数据量 = 1170 × 16 ≈ 18720条。

高度为3的B+树能存的数据量：1170 × 1170 × 16 ≈ 2190万条。

三次磁盘IO就能查到任意一条数据，这就是B+树“矮”的真正含义。

树高低，就意味着一次查询需要访问的页更少。

B+树还有一个重要特点：

所有完整数据都集中在叶子节点，叶子节点之间通过链表连接。

这个设计非常适合范围查询。

比如：

InnoDB 可以先通过 B+树定位到 id = 1000 附近的叶子节点，然后沿着叶子节点链表往后扫描，直到超过 2000。

所以，B+树不只是能查单点数据，也适合范围扫描和有序遍历。

2. 为什么不是 B 树、红黑树、哈希表？

理解 B+树时，最好顺便看一下其他结构的问题。

数据结构	为什么不如 B+树适合 InnoDB 的通用索引场景
B 树	数据分散在各层节点中，不如 B+树集中在叶子节点适合范围扫描；非叶子节点携带数据也会影响节点扇出
红黑树	本质是二叉树，树高比 B+树高得多，不适合磁盘页模型下的大规模数据查询
哈希表	等值查询很快，但无序，不适合范围查询、排序和前缀匹配

B 树不是不能查，也不是一定很差。

只是相比 B+树，它的数据分布在各层节点里，不像 B+树那样把完整数据集中放在叶子节点。

对于数据库常见的范围查询来说，B+树的叶子链表更友好。

红黑树在内存里很好用，但它是二叉树。

数据量一大，树高就明显高于 B+树。

2000万条数据,红黑树高度约为24层,远高于B+树的三层。

在数据库里，树高一般意味着更多磁盘随机 IO。

哈希表适合这种查询：

但它不适合这种查询：

因为哈希表本身没有顺序。

而 B+树是有序结构，既能支持等值查询，也能支持范围查询和排序场景。

所以，InnoDB 更适合使用 B+树作为通用索引结构。

这一节的核心结论

B+树适合 InnoDB，是因为它适合磁盘页模型:

它通过高扇出降低树高，从而减少磁盘 IO；又通过叶子节点链表支持范围扫描和有序遍历。

二、找到索引后，为什么有时还要回表？

理解了 B+树之后，接下来要理解 InnoDB 的两个索引概念：

聚簇索引和二级索引。

它们决定了：

一条 SQL 通过索引找到记录后，能不能直接拿到完整数据。

1. 聚簇索引：叶子节点存完整行数据

InnoDB 表必须有一个聚簇索引。

一般情况下，主键就是聚簇索引。

如果没有主键，InnoDB 会选择第一个非空唯一索引。

如果还没有，就会生成一个隐藏的 row_id。

聚簇索引最重要的特点是：

叶子节点存放完整行数据。

比如：

因为 id 是主键，所以这条 SQL 会走主键索引，也就是聚簇索引。

查询路径大致是：

主键索引 B+树 → 找到 id = 100 的叶子节点 → 直接拿到完整行数据

这个过程不需要再查别的索引。

因为完整行数据已经在聚簇索引的叶子节点里了。

2. 二级索引：叶子节点存索引字段和主键值

除了聚簇索引，其他索引都可以理解为二级索引，也叫辅助索引。

比如：

idx_name 也是一棵 B+树。

但它的叶子节点不存完整行数据，而是存：

name 字段值 + 主键 id

现在执行：

查询过程大致是：

这个过程就叫回表。

简单来说：

回表就是先通过二级索引找到主键值，再根据主键回到聚簇索引里查完整行数据。

3. 为什么二级索引不直接存完整行？

因为那样太浪费。

假设一张表有 5 个二级索引。

如果每个二级索引的叶子节点都存完整行数据，那么同一行数据就会在多个索引里重复保存多份。

一旦这行数据更新，多个索引里的完整数据都要跟着更新。

这会带来很高的空间成本和写入维护成本。

所以 InnoDB 的设计是：

完整行数据只放在聚簇索引里；二级索引只保存索引字段和主键值。

这样做的结果是：

查询时有时需要回表，但整体存储和维护成本更低。

4. 为什么二级索引存主键值，而不是物理地址？

这个问题更关键。

既然二级索引最终要回到聚簇索引查完整行，为什么不直接存数据的物理地址？

看起来这样可以少查一次。

但问题是：

物理地址不稳定，主键值稳定。

InnoDB 的数据页可能发生页分裂、页合并、数据移动。

如果二级索引里存的是物理地址，那么数据页一旦变化，所有相关二级索引都可能需要更新。

维护成本会非常高。

而主键值是逻辑稳定的。

只要主键不变，二级索引就不需要关心这行数据在底层页里的物理位置怎么变化。

所以 InnoDB 选择：

二级索引 → 主键值 → 聚簇索引 → 完整行数据

这个设计牺牲了一部分查询场景下的回表成本，但换来了更稳定、更容易维护的索引结构。

5. 覆盖索引：减少或避免回表

回表不是一定会发生。

比如：

对于索引 idx_name(name) 来说，二级索引叶子节点里本来就有：

name + id

所以这条 SQL 需要的字段都能从索引里拿到。

这就是覆盖索引。

覆盖索引的核心是：

查询需要的字段基本都能从索引中取得，从而减少或避免回表。

如果查询改成：

而 idx_name 里没有 age，就通常需要回表。

如果这类查询很频繁，可以考虑建立联合索引：

这样在某些查询场景下，就可以通过索引直接拿到 name、age、id，减少回表成本。

这里补充一个严谨点：

覆盖索引主要强调“查询字段能从索引中取得”。但在 MVCC 可见性判断、删除标记等存储引擎内部细节上，某些场景仍可能需要额外确认。作为日常 SQL 优化理解，可以先把它理解为：覆盖索引能显著减少或避免回表。

这一节的核心结论

聚簇索引的叶子节点存完整行数据。

二级索引的叶子节点存二级索引字段和主键值。

所以，通过二级索引查询完整行时，通常要先查二级索引拿到主键，再根据主键回到聚簇索引查整行，这就是回表。

覆盖索引的价值，就是让查询字段尽量从索引里直接取得，减少回表成本。

三、联合索引为什么要从左往右用？

理解完单列索引和回表，再看联合索引就容易多了。

假设有一个联合索引：

很多人学联合索引时，会背一句话：

要遵守最左前缀原则。

但为什么要遵守？

是 MySQL 人为规定的吗？

不是。

最左前缀原则的本质是：

联合索引在 B+树里的排序规则，决定了它只能从左往右高效使用。

1. 联合索引不是三棵树，而是一棵树

联合索引 (a,b,c) 并不是给 a、b、c 各建一棵树。

它只有一棵 B+树。

这棵树的排序规则是：

先按 a 排序； a 相同，再按 b 排序； b 相同，再按 c 排序。

可以理解成字典排序。

比如这些索引项：

(1, 1, 1) (1, 1, 2) (1, 2, 1) (1, 2, 3) (2, 1, 1) (2, 2, 1)

首先按 a 排。

只有 a 相同时，b 才是有序的。

只有 a 和 b 都相同时，c 才是有序的。

所以，联合索引的有序性是从左到右建立的。

这就是最左前缀原则的来源。

2. 几个 SQL 例子

假设索引是：

第一种：

这个查询可以比较完整地利用联合索引。

因为它从最左边的 a 开始，并且 a、b、c 都是等值条件。

第二种：

这个查询可以利用 a = 1 定位一段索引范围。

但它跳过了 b。

在联合索引 (a,b,c) 中，只有当 a 和 b 都确定时，c 才能体现出连续有序性。

现在 b 没有条件，所以 c = 3 通常不能继续用于缩小索引扫描范围。

但不要简单说：

c 完全没用。

如果 MySQL 使用索引下推，也就是 ICP，c = 3 仍然可能在存储引擎层参与过滤，减少回表次数。

所以更严谨的说法是：

a = 1 可以用于定位索引范围； c = 3 不能跳过 b 继续缩小扫描范围；但 c = 3 可能通过索引下推参与过滤。

第三种：

这个查询没有最左边的 a。

而联合索引是先按 a 排序的。

没有 a，就很难在这棵 B+树上直接定位到 b = 2 的连续范围。

所以常规情况下，这个查询无法高效利用 (a,b,c) 做索引定位。

如果这类查询很多，更适合建：

第四种：

这个 SQL 中：

a = 1 可以用于等值定位； b > 2 可以用于范围扫描； c = 3 通常不能继续用于缩小扫描范围。

原因是，一旦 b 变成范围条件，后面的 c 在整个扫描范围内不再具备连续有序性。

但同样要注意：

c 不能继续用于缩小扫描范围，不代表它一定完全没用。它仍然可能通过索引下推参与过滤。

第五种：

这个查询里，a 已经固定。

在 a = 1 的数据范围内，联合索引天然按照 b,c 排序。

所以它有机会利用索引顺序完成排序，减少额外的 filesort。

3. “用到索引”要分两层理解

联合索引最容易混淆的地方，是一句话：

这个字段有没有用到索引？

这个说法太粗了。

更好的问法应该是：

它能不能用于缩小索引扫描范围？它能不能在索引扫描过程中参与过滤？

比如：

对于索引 (a,b,c) 来说：

a = 1 可以缩小索引扫描范围； c = 3 不能跳过 b 继续缩小扫描范围；但 c = 3 可能通过 ICP 在索引层参与过滤。

能区分这两层，才算真正理解联合索引。

这一节的核心结论

最左前缀原则是联合索引排序方式决定的。

联合索引 (a,b,c) 是先按 a 排，a 相同再按 b 排，a、b 都相同再按 c 排。

所以只有从左到右连续匹配时，才能高效利用索引缩小扫描范围。

跳过中间字段，或者在某个字段上进入范围扫描后，后面的字段通常不能继续缩小扫描范围，但仍可能参与过滤。

四、查到数据后，当前事务能不能看到它？

索引解决的是：

怎么快速找到数据。

但在事务场景下，找到数据还不够。

InnoDB 还要判断：

这行数据对当前事务是否可见？

比如：

事务 A 正在读取 user 表；事务 B 修改了某一行并提交；事务 A 再次读取时，到底应该看到旧值还是新值？

这个问题就涉及 MVCC。

1. 先区分快照读和当前读

讲 MVCC 前，必须先区分两个概念：

快照读：普通 SELECT 当前读：SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT

普通查询一般是快照读：

它读取的是某个一致性视图里的数据版本。

当前读读取的是最新数据，并且通常会加锁：

为什么要先区分它们？

因为 InnoDB 在处理事务一致性时，不是只靠一种机制。

更准确地说：

快照读主要依赖 MVCC；当前读主要依赖锁机制。

如果不区分快照读和当前读，就很容易分不清楚 RR、MVCC、幻读、Next-Key Lock 。

2. MVCC 的本质：一行数据有多个版本

MVCC，全称 Multi-Version Concurrency Control，多版本并发控制。

它的核心思想是：

一行数据被修改后，旧版本不会立刻消失，而是通过 undo log 保留下来。事务读取数据时，不一定读取最新版本，而是根据可见性规则找到自己应该看到的版本。

InnoDB 每行记录背后有一些隐藏字段，其中两个很关键：

DB_TRX_ID：最后修改这行记录的事务 ID DB_ROLL_PTR：回滚指针，指向 undo log 中的旧版本

一行数据被多次修改后，会形成一条版本链：

事务读取这行数据时，会先看当前版本。

如果当前版本对它不可见，就顺着版本链往前找，直到找到一个可见版本。

这就是 MVCC 的基本工作方式。

它的价值是：

让普通读操作不必总是加锁，从而提高读写并发能力。

3. Read View：一份可见性判断规则

Read View 可以理解成：

当前事务执行快照读时生成的一份“可见性判断规则”。

可以先把它理解成记录了三类信息：

哪些事务在 Read View 创建时还活跃；哪些事务 ID 对应的数据版本肯定已经提交；哪些事务 ID 是 Read View 创建之后才出现的。

判断一条记录版本是否可见时，主要看这条记录是由哪个事务修改的。

简单理解：

如果这个版本在 Read View 创建前已经提交，通常可见；如果这个版本是 Read View 创建后才产生的，不可见；如果修改这个版本的事务在 Read View 创建时还没提交，不可见；如果是当前事务自己修改的数据，通常可见。

所以，MVCC 不是简单地“读旧版本”。

更准确地说是：

根据 Read View 的可见性规则，在版本链里找到当前事务应该看到的那个版本。

4. RC 和 RR 的区别：Read View 创建时机不同

MySQL 常见的两个隔离级别是：

Read Committed：读已提交，简称 RC Repeatable Read：可重复读，简称 RR

它们都可以使用 MVCC。

核心区别在于：

Read View 的创建时机不同。

在 RC 隔离级别下：

每次普通 SELECT 都会生成一个新的 Read View。

所以在同一个事务里，如果第一次 SELECT 之后，其他事务提交了修改，那么第二次 SELECT 可能看到新数据。

这就是 RC 下可能出现不可重复读的原因。

在 RR 隔离级别下：

事务第一次执行普通快照读时生成 Read View，后续快照读复用这个 Read View。

注意，不是 BEGIN 的时候立刻生成 Read View。

而是第一次执行普通快照读时才生成。

所以在 RR 下，同一个事务内多次普通 SELECT 看到的是同一份一致性视图。

这就是可重复读的核心原因。

5. RR 怎么处理幻读？

InnoDB 的 RR 隔离级别下，普通快照读通过 MVCC 避免幻读；当前读通过 Next-Key Lock 避免幻读。

普通快照读，比如：

在 RR 下，第一次查询生成 Read View，后续查询复用同一个 Read View。

即使其他事务插入了新的 age > 18 的记录并提交，当前事务后续普通 SELECT 也看不到这条新记录。

所以在快照读层面，不会出现幻读。

但当前读不同。

比如：

当前读读取的是最新数据，并且会加锁。

为了防止其他事务在 age > 18 这个范围里插入新记录，InnoDB 会使用 Next-Key Lock。

Next-Key Lock 可以简单理解为：

记录锁 + 间隙锁

它不仅锁住已经存在的记录，还锁住记录之间的间隙，防止其他事务往这个范围里插入新数据。

所以 InnoDB 在 RR 下处理幻读，要分场景看：

普通快照读：靠 MVCC；当前读：靠 Next-Key Lock。

这一节的核心结论

索引帮 MySQL 找到数据，但事务隔离决定当前事务能不能看到这份数据。

InnoDB 通过 undo log 版本链保存数据的多个历史版本，再通过 Read View 判断哪个版本对当前事务可见。

RC 和 RR 的核心区别是 Read View 创建时机不同：

RC：每次快照读都生成新的 Read View； RR：第一次快照读生成 Read View，后续复用。

五、SQL 变慢时，沿着执行路径反着查

前面讲的 B+树、回表、联合索引、MVCC，真正有用的地方在于：

当一条 SQL 变慢时，你知道应该沿着哪里排查。

如果一条 SQL 慢了，本质上可以反过来问：

它是不是没选对访问路径？是不是扫描了太多索引页？是不是回表太多？是不是排序或临时表成本太高？是不是被锁等待或事务影响？

这就把慢 SQL 优化和前面的执行路径连起来了。

1. 索引失效不要只背场景

很多人背索引失效，会背这些：

对索引列使用函数；隐式类型转换； LIKE 前面有 %； OR 条件；不等于；不符合联合索引最左前缀。

这些结论有用，但最好理解本质。

索引失效大致可以分成两类。

第一类：

SQL 写法破坏了索引列的有序性，导致 B+树无法快速定位。

比如：

索引里存的是原始的 create_time，不是 DATE(create_time) 计算后的结果。

所以 MySQL 很难直接利用原始索引快速定位。

更好的写法是：

再比如：

B+树是从左到右有序的。

前面加了 %，MySQL 不知道从哪里开始定位，只能扫描大量数据。

如果业务允许，可以改成：

这样才有机会利用索引前缀。

第二类：

理论上能走索引，但优化器认为走索引成本更高，所以选择了其他访问路径。

比如：

或者某些低区分度字段，比如 gender、status。

如果大部分数据都满足条件，走索引后还要大量回表，可能不如直接全表扫描。

这不是索引坏了，而是优化器基于成本做出的选择。

2. 慢 SQL 排查链路

排查慢 SQL 时，可以按这条链路来：

第一步，先判断它是偶发慢，还是稳定慢。

如果是偶发慢，可能不是 SQL 本身的问题，而是：

锁等待；磁盘 IO 抖动； Buffer Pool 命中率下降；并发压力突然升高；大事务影响。

如果是稳定慢，再重点看慢查询日志和执行计划。

3. EXPLAIN 重点看什么？

执行：

重点看这些信息就够了：

type：访问方式 key：实际使用的索引 rows：预估扫描行数 filtered：过滤比例 Extra：额外信息

type 不用一开始背全。

先重点理解几个常见值：

type	大致含义
const	主键或唯一索引等值查询，最多匹配一行
ref	普通索引等值查询
range	范围查询
index	扫描整个索引树
ALL	全表扫描

一般来说，看到 ALL 要警惕。

但不能只看 type。

有时候 type = range，但扫描了几百万行，依然很慢。

有时候 type = ALL，但表很小，也未必是问题。

所以要结合：

type + key + rows + filtered + Extra + 实际耗时

一起判断。

如果 Extra 里出现：

Using filesort Using temporary

也要重点关注。

它们说明 SQL 可能发生了额外排序或临时表操作。

4. force index 为什么不能滥用？

有时候你明明建了索引，但 MySQL 没走。

这时很多人想直接写：

force index 不是不能用，但不能作为第一选择。

因为 MySQL 选择索引是基于统计信息和成本估算的。

今天你强制某个索引可能更快，但随着数据量和数据分布变化，未来可能反而更慢。

更合理的优化顺序是：

先理解 SQL 的访问路径；再看索引设计是否匹配；再看数据分布和统计信息是否准确；再考虑改写 SQL；最后才考虑 index hint。

慢 SQL 优化的核心是：

让 SQL 以尽可能低的成本完成数据访问。

有时候走索引是低成本。

有时候全表扫描反而是低成本。

最终要看数据量、选择性、回表成本、排序成本和执行计划。

这一节的核心结论

慢 SQL 排查不能只背“索引失效场景”。

更好的方式是沿着执行路径反向检查：

访问路径是否合理；扫描行数是否过大；是否发生大量回表；是否出现额外排序或临时表；是否存在锁等待或系统资源抖动。

索引优化的目的不是让 SQL “看起来走了索引”，而是让它以更低成本拿到结果。

总结：把零散概念放回一条 SQL 的路径里

B+树聚簇索引二级索引回表覆盖索引联合索引最左前缀 MVCC Read View Next-Key Lock EXPLAIN 慢 SQL 优化

它们不是孤立知识点。

如果沿着一条查询 SQL 的路径看，它们其实是连在一起的：

理解 MySQL，最重要的是抓住三条主线。

第一，数据怎么被组织和定位。

这对应 B+树、聚簇索引、二级索引、联合索引。

第二，查到数据后，当前事务能不能看到它。

这对应 undo log、版本链、Read View、RC、RR、Next-Key Lock。

第三，SQL 慢了以后，怎么判断慢在哪里。

这对应慢查询日志、EXPLAIN、索引选择、扫描行数、回表成本、排序成本和优化器成本估算。