同样是 B+ 树，为什么 PostgreSQL 永远没有“2000万行性能断崖”？

qq_37221867

557人浏览 · 2026-01-07 18:07:20

qq_37221867 · 2026-01-07 18:07:20 发布

这是一个非常懂内核的问题。你已经精准地指出了 MySQL (InnoDB) 的痛点：因为它是 索引组织表 (Index-Organized Table, IOT)，数据就在 B+ 树的叶子上，导致行记录越大，叶子节点能存的条目越少，树很容易变高。

那么 PostgreSQL (PG) 是怎么设计的？它是否避免了这个问题？

结论是：PostgreSQL 从根本的存储架构上就避免了“因数据行过大导致索引树变高”的问题，但它用另一种 I/O 代价作为交换。

PostgreSQL 采用的是 堆表 (Heap Table) 存储模式。

以下是深度的架构对比与解析：

1. 核心差异：堆表 (Heap) vs 索引组织表 (IOT)

这是理解两者的关键：

MySQL (InnoDB):
- 数据即索引： 主键索引（Clustered Index）的叶子节点存放的是整行数据。
- 瓶颈来源： 如果你的行有 1KB，一个 16KB 的页只能存 15 行左右。数据量一多，叶子节点分裂极快，树迅速变高（3层 -> 4层），导致 I/O 增加。
PostgreSQL:
- 数据与索引分离： 数据存储在 “堆 (Heap)” 文件中，这是无序的乱序存储。
- 索引只存指针： 无论是主键索引还是二级索引，在 PG 眼里都是地位平等的。它们在 B+ 树叶子节点里只存储 (Key, TID)。
  - Key: 索引键值（比如 ID）。
  - TID (Tuple ID): 指向堆文件中数据的物理指针（Block号 + 偏移量）。
- 设计结果： 因为索引叶子节点不存储真实数据行 (Payload)，只存一个极小的指针（TID 只有 6 字节），所以 PG 的 B+ 树节点密度极高（Fan-out 非常大）。

2. PostgreSQL 如何“避免”树高问题？

回到你的场景：2000 万行，每行 1KB。

在 MySQL 中：
叶子节点被 1KB 的数据撑满了，扇出（Fan-out）很小，导致树被迫向上生长，变成 4 层。

在 PostgreSQL 中：

尽管 PG 默认页大小是 8KB（比 MySQL 小一半），但它的 B+ 树叶子节点里没有 1KB 的数据。
它只需要存 ID (比如8字节) + TID (6字节) ≈ 14字节。
一个 8KB 的索引页可以存几百甚至上千个索引条目。
结果： 哪怕是 2000 万甚至 1 亿行数据，PG 的索引树高度通常能稳定保持在 2-3 层，极少会变成 4 层。

所以，PG 完美避免了“数据膨胀导致索引树变高”的问题。

3. 代价是什么？“回表”是必然的

世界上没有免费的午餐。PG 虽然让索引树变矮了，但它改变了 I/O 的结构。

MySQL (PK 查询): 查 B+ 树 (3次 I/O) -> 拿到数据 (数据就在树里)。总共 3 次 I/O。
PostgreSQL (PK 查询): 查 B+ 树 (2-3次 I/O) -> 拿到 TID -> 去堆文件里读数据页 (1次随机 I/O)。总共 3-4 次 I/O。

你看出了问题吗？
虽然 PG 树矮（比如 2 层），但它必须多一次去 Heap 表读取数据的 I/O（除非由 Index-Only Scan 优化，后面讲）。
所以，在主键查询这个单一场景下，MySQL 因为少了一次“跳跃”去读堆文件，通常比 PG 稍微快一点点（如果树高没崩的话）。

但是！在二级索引（非主键）查询场景下，PG 完胜：

MySQL: 二级索引树 -> 拿到主键 ID -> 回表（再跑一遍主键 B+ 树） -> 拿到数据。这是“两棵树”的开销。
PostgreSQL: 二级索引树 -> 拿到 TID -> 直接指哪打哪（去堆表读数据）。没有“第二棵树”。

4. PG 针对大数据的独门绝技

为了应对 2000 万行以上的大表查询，PG 除了传统的 B+ 树，还有两个 MySQL 没有的设计理念来降低 I/O：

A. BRIN 索引 (Block Range Index) —— 真正的“大表神器”

如果你的 2000 万行数据是按时间或 ID 顺序插入的，PG 提供了一种叫 BRIN 的索引。

原理： 它不存每行的指针，它只存摘要。它记录：“第 1 到 100 个数据页，最小 ID 是 1，最大 ID 是 10000”。
大小： 对于 2000 万行表，B+ 树索引可能要几百 MB，而 BRIN 索引可能只有几十 KB。
效果： 它可以完全常驻内存。查询时，它能帮你直接跳过 99% 的无关数据块。在时序数据和日志场景下，这是 MySQL 无法比拟的优势。

B. Index-Only Scan (仅索引扫描) 与 Visibility Map

你可能会问：“PG 每次都要去堆表回表，岂不是很慢？”
PG 引入了 Visibility Map (可见性映射)。

原理： 如果查询需要的列都在索引里（覆盖索引），且 PG 知道这个页面没有脏数据（对所有事务可见），它就可以直接从索引返回数据，不再去读堆表。
这就达到了和 MySQL 覆盖索引一样的性能效果。

C. TOAST (超大字段存储技术)

如果你的“1KB 每行”里包含了一个大的 Text/JSON 字段。

MySQL: 可能会把大字段的一部分存在 B+ 树页里，导致页分裂更严重。
PG: 会自动把大字段抽取出来，存到一张专门的 TOAST 表 里，原表只留一个指针。
结果： 主表的页密度更高，扫描更快。只有当你真的 select 那个大字段时，才会去读 TOAST 表。

总结

PostgreSQL 在面对“2000万行”临界点时，设计理念与 MySQL 完全不同：

避免树高增加： PG 采用 Heap + Index 分离 架构。索引树只存指针，极其轻量，树高几乎不会随数据行大小（1KB）而变化。不管你一行有 100 字节还是 5KB，PG 的索引树高度都很稳。
I/O 模型差异： PG 将“树高带来的 I/O”转化为了“必然的堆表随机 I/O”。
- 在二级索引查询上，PG 效率极高（无二次树查找）。
- 在主键查询上，两者打平或 MySQL 略优。
大表专用工具： PG 拥有 BRIN 索引 和 TOAST 技术，这使得它在处理海量数据（特别是日志、时序、大文本）时，比 MySQL 的 B+ 树更不容易遇到性能“断崖”。