PostgreSQL 与 InnoDB 的存储路径

假设有一张再普通不过的订单表：主键是 id，用户经常按 status 查询，后台又不断更新订单状态。

CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  status     varchar(32) NOT NULL,
  buyer_id   bigint NOT NULL,
  total      decimal(12, 2) NOT NULL,
  note       varchar(500),
  created_at timestamp NOT NULL
);

CREATE INDEX orders_status_idx ON orders (status);

SELECT * FROM orders WHERE status = 'paid';

UPDATE orders SET note = 'checked' WHERE id = 9527;

但透过 SQL、优化器和执行器之后，两套数据库面对的物理世界完全不同。InnoDB 的表以聚簇索引为骨架，主键树的叶子就是行数据；PostgreSQL 的普通表是一份 heap，主键也只是另一个指向 heap 元组的索引。前者的二级索引把主键当作「地址」，后者的索引保存物理 TID。前者主要把旧版本放进 undo，后者主要把新旧版本都放在 heap 中。

这不是几个实现细节的偶然差异，而是一组互相咬合的设计选择：数据放在哪里，决定索引指向哪里；索引指向哪里，决定更新后哪些引用仍然有效；版本放在哪里，决定读路径如何判断可见性；旧版本如何回收，又决定系统会以什么方式膨胀和抖动。

所以我不准备从「谁的 B+ 树更快」开始，这个问题省略了太多相关因素。更有用的问题是：同一条记录在两套引擎里分别是什么，索引叶子最终交付的又是什么？ 一旦把这两个问题弄清楚，点查、范围扫描、覆盖索引、写放大乃至主键设计的差异都会自然出现。

这篇文章也是《PostgreSQL 如何选择索引》的续篇。前一篇从规划器视角解释了 Index Scan、Bitmap Heap Scan、Index Only Scan 和 Seq Scan 为什么会有不同成本；这里把同一个问题继续向下拆：一个 TID 在页里究竟指向什么，索引叶页和 heap page 如何连接，为什么 InnoDB 二级索引不保存物理页地址，以及一次 UPDATE 如何沿着这些引用扩散。读完本文再回看前一篇，规划器模型里的 random_page_cost、物理相关性和 Heap Fetches 就不再只是几个抽象参数。

一、B 树与 B+ 树：真正重要的不是那个加号

很多索引文章从二叉搜索树讲起：二叉树每个节点至多两个孩子，数据有序时可能退化；平衡树解决退化，但树高仍不适合磁盘；于是数据库改用一个节点拥有大量孩子的 B 树。这条叙事方向没错，却很容易让人只记住 O(log n)。

数据库索引的基本读写单位不是一个节点对象，而是一个页。页可能是 PostgreSQL 默认的 8 KB，也可能是 InnoDB 默认的 16 KB。一次缺页读取把整个页带进缓存，所以索引真正想做的是：让一个页容纳尽可能多的导航信息，以较低树高定位到目标叶页，并在叶页之间高效地继续扫描。

假设一个内部页扣除页头、槽目录等开销后，能容纳 500 个「分隔键 + 子页指针」。三层内部节点的理论覆盖量已经是 500³，也就是 1.25 亿个叶页方向。现实中键长、前缀压缩、页填充率和实现元数据会让数字变化，但结论很稳定：高扇出比树高的渐进复杂度更能解释数据库索引为什么有效。 根页和上层页又因为访问频繁而很容易常驻缓存，随机 I/O 往往发生在较低层。

1.1 教科书中的结构差异

经典定义下，B 树和 B+ 树可以这样区分：

这个区别带来两个直接结果。

第一，B+ 树内部页不放完整记录，可以把更多分隔键和子指针塞进同一页。扇出更大，树通常更矮，冷缓存下需要读取的层数更少。代价是即使搜索键恰好等于某个内部节点的分隔键，也要走到叶子才能拿到记录。

第二，所有数据项都位于同一层，叶页又按键序相连。找到 created_at >= T1 的第一个位置后，执行器可以沿叶页右链读到 T2，不用为每条记录重新从根搜索。这让范围查询、ORDER BY、前缀扫描和有序 LIMIT 成为同一种物理动作的不同表述。

1.2 为什么数据库用 B+ 树仍然不够严谨

工程实现的名字没有教科书那么统一。MySQL 官方文档把 InnoDB 的普通索引称为 B-tree，工程资料又普遍用 B+ tree 描述它；PostgreSQL 的访问方法也叫 B-tree，但叶子存放索引元组、内部页承担导航，并且页面存在左右兄弟链接。它们都表现出典型 B+ 树或 B-link tree 的特征。

因此，看到 USING btree 不能推导出「记录会散落在内部节点」。数据库文档里的 B-tree 往往是一个索引家族名称，而不是在参加数据结构术语考试。本文后面沿用各项目的官方名称：称 PostgreSQL B-tree 和 InnoDB B-tree；讨论叶层组织时，再明确指出它们具有 B+ 树式的结构。

PostgreSQL 的实现还接近 Lehman-Yao B-link tree：页面除了左右 sibling link，还用 high key 表示本页键空间上界。并发页分裂发生时，搜索者若发现目标超过 high key，可以沿右链继续，不必用一把全树锁冻结结构。这个细节提醒我们，数据库选择的从来不是抽象数据结构本身，而是一个能处理页分裂、崩溃恢复、并发访问和空间回收的数据结构协议。

1.3 叶链、预读与页缓存到底改善了什么

把叶页连接起来，不等于把所有叶页保证写在磁盘连续扇区上。文件可能碎片化，页可能分布在不同 extent，云盘和 SSD 也早已把「相邻逻辑块」等同于「相邻物理位置」的直觉打散。叶链真正提供的是稳定、单调的逻辑访问序列。

这条序列给缓存与预读留下了优化空间：

1. 执行器只需在树上定位一次范围起点，此后沿 sibling page 扫描。
2. 缓冲池观察到连续的索引页访问后，可以提前提交后续读取。InnoDB 具有线性与随机 read-ahead 机制；PostgreSQL 和操作系统也能从顺序访问、批量 I/O 中获益。
3. 每个叶页中的槽按键序处理，CPU 比较与内存访问更加集中，读入一页能消费多条候选记录。
4. 上层页反复复用，叶页则形成流式工作集，缓存替换更容易区分热导航页与一次性扫描页。

但预读只优化「索引页接下来读什么」。如果索引叶子只给出另一个位置，回表仍可能把访问序列打散。也正是在这里，InnoDB 聚簇索引和 PostgreSQL heap 的差异第一次真正影响性能。

二、两种表：主键树就是表，还是主键树只是一张地图

「索引即表」和「索引指向表」看起来只差一个介词，实际却定义了两套不同的存储拓扑。

2.1 InnoDB：每张表必须选出一棵聚簇索引

InnoDB 的表数据保存在聚簇索引中。聚簇索引内部页保存分隔键和子页指针，叶子记录包含主键以及该行其他列；过长的变长列可能把部分内容放到 overflow page，因此「完整行都在叶页」是逻辑描述，不代表所有字节一定内联。

聚簇键按以下顺序确定：

1. 表显式定义了 PRIMARY KEY，使用主键。
2. 没有主键，选择第一个所有列均为 NOT NULL 的 UNIQUE 索引。
3. 两者都没有，InnoDB 生成一个 6 字节隐藏 row ID，并以 GEN_CLUST_INDEX 组织表。

所以 InnoDB 表不是「有主键时才聚簇」。它总要有一棵承载行数据的聚簇树，只是这个键有时由引擎生成。显式定义短小、稳定的主键，能让这项选择保持在设计者控制之内。

按主键点查时，树搜索的终点就是行。按主键范围扫描时，目标行也按主键顺序聚集在相邻叶页中。对 WHERE id BETWEEN 10000 AND 11000，索引扫描与数据扫描是一件事，这是聚簇组织最直接的收益。

代价也同样直接：行只能按一套顺序聚簇。选择了主键，就无法让 created_at 或 buyer_id 同时决定行的物理组织；其他访问路径只能建立二级索引。随机主键插入还会把写入分散到聚簇树的不同叶页，增加页分裂、缓存压力和日志量。单调递增主键更接近最右叶追加，对局部性友好，却也可能让并发写集中在树的右端热点。所谓「自增主键一定更快」仍然是有负载前提的判断。

2.2 PostgreSQL：heap 与每一棵索引都是独立 relation

PostgreSQL 的普通表是 heap-organized table。这里的 heap 不是 JVM heap，也不是二叉堆，而是说行不按某个业务键维持全局顺序。表由固定大小的页组成；页内有 ItemId 数组，元组本体从页尾向前生长。插入时数据库会借助 Free Space Map（空闲空间映射，FSM）寻找有足够空间的页，因此「按插入顺序堆叠」只能作为入门近似：并发插入、空闲空间复用、更新和清理都会让物理顺序偏离提交顺序。

一个 TID（Tuple Identifier，元组标识符）由 (BlockNumber, OffsetNumber) 组成：块号找到 heap 页，槽号找到页内 ItemId。SQL 中可以通过系统列 ctid 观察它，例如 (1842,7)。元组本体在页内压缩整理时可以移动，只要 ItemId 槽位仍指向新位置，外部索引保存的 TID 就无需改变。这一层很小的间接引用，是页内整理和 HOT 链得以工作的关键。

主键在 PostgreSQL 中不会把 heap 改造成主键树。PRIMARY KEY 约束通常创建一棵唯一 B-tree：叶子索引元组保存键和 heap TID，找到键之后再去 heap 取行并检查 MVCC 可见性。二级 B-tree 的形态也一样，只是键不同。

这种分离意味着「主键查询天然不回表」不适用于 PostgreSQL。若查询需要 heap 中的列，主键索引和普通二级索引都要沿 TID 访问 heap。反过来，它也意味着 heap 不必为了维持主键顺序而在每次插入时找到聚簇树叶页；每个索引与表本体可以独立演化。

2.3 一张拓扑图看懂两条寻址路线

以 status = 'paid' AND id = 9527 对应的某行举例：

两边都可能出现「查索引后再访问别的页」，但第二跳的语义不同：

「PostgreSQL 只需一次定位」不能理解为「只需一次 I/O」。TID 消除了第二次树搜索，却仍可能读取一个不在缓存里的 heap 页；命中许多离散 TID 时，随机 heap 访问往往才是主要成本。InnoDB 的第二次搜索也不必然对应一次磁盘读取：聚簇树上层和目标叶页可能已经在 buffer pool 中。复杂度描述的是步骤，不是延迟测量。

2.4 再向下一层：两套引擎的一张 page 里分别有什么

到这里，聚簇索引和 heap 仍可能只是两个框。继续下钻到页内，会发现两套引擎都使用 page、slot 和 record，却把这些组件组合成了不同的引用关系。

InnoDB 默认索引页是 16 KB。一个简化后的页包含文件页头、索引页头、infimum 与 supremum 两条系统记录、按键序组织的用户记录、空闲空间、page directory 和页尾校验信息。用户记录在页内通过 next-record 信息形成有序链，page directory 的 slot 则指向若干记录组，用于先做较粗的二分定位，再在组内沿记录链查找。infimum 和 supremum 是比任何主键值更小和更大的虚拟记录，它们本身不属于用户写入的数据。

如果这是聚簇索引叶页，record A 的业务载荷是该行各列以及 MVCC 隐藏字段；如果是二级索引叶页，载荷是二级键列与聚簇主键。内部页的记录承担导航，保存分隔键与子页号。同一套 page 框架，因为树的身份不同，record 的语义也不同。

PostgreSQL 的 heap page 与 B-tree page 共用 PageHeaderData + ItemId + item data + special space 这套通用页框架，但具体 item 由访问方法解释。heap page 的 item 是 HeapTuple，special space 为空；B-tree page 的 item 是 IndexTuple，special space 中的 opaque data 保存左右 sibling block、层级和页标志等 B-tree 私有信息。

PostgreSQL B-tree 的第 0 个 block 通常是 metapage，记录 root、fast root 及层级等元数据。普通树页分为内部页和叶页。内部索引元组承担 separator key 与 downlink 的职责；叶子索引元组保存键和 heap TID。除最右页外，页面还使用 high key 表示本页键空间上界。这里的 high key 与右兄弟链接共同服务于并发分裂下的正确搜索。

这个页内视角解释了两个经常被混用的「指针」：PostgreSQL B-tree 内部页的 downlink 指向另一张索引页，叶子项的 TID 指向heap page 的 ItemId；InnoDB 二级叶子的主键不是物理指针，而是一段需要重新送入聚簇树比较的逻辑键。把它们都叫「地址」会掩盖一次直接 block lookup 和一次树搜索的本质区别。

2.5 页分裂时，究竟有哪些引用必须跟着改

页分裂是检验引用设计最直观的场景。假设一个索引叶页已经放不下新记录，引擎分配新页，把一部分记录移动过去，更新 sibling link，并把新的分隔信息传播到父页；父页也满时，分裂可能向上递归，极端情况下产生新 root。

在 InnoDB 聚簇树中，行记录可能因分裂从 clustered page P 移到 Q。二级索引无需把「P 中第几个槽」改成「Q 中第几个槽」，因为它从未保存这个物理位置，只保存主键。下一次回表仍用主键从 root 搜索，自然走到 Q。这是二次搜索换来的结构隔离。

在 PostgreSQL 中，某棵 B-tree 的叶子分裂可能把 (key, TID) 索引元组移到新索引页，但 TID 的值不变，因为目标 HeapTuple 没动。父 downlink 和 sibling/high key 需要按 B-tree 协议修正，其他索引与 heap 不需要知道这次分裂。反过来，heap 插入只是在 FSM 找到的页中分配 tuple 与 ItemId；heap 不按键维持树形边界，因此不存在「heap 叶页分裂并向父页传播」这件事。

这也是为什么「PostgreSQL 的 ctid 遇到页分裂会失效」不准确：索引页分裂不改变 heap TID，heap 又没有这种树页分裂。真正使 ctid 变化的常见动作是创建新 tuple version 的 UPDATE，以及 CLUSTER、VACUUM FULL 等搬动或重写 heap 的操作。

2.6 行数据放不进叶页时，数据还会在叶页吗

「InnoDB 把整行放在聚簇叶子，PostgreSQL 把整行放在 heap」是恰当的逻辑模型，却不是绝对的物理现实。数据库页大小固定，note、JSON、二进制对象等变长值可能远大于一页；引擎必须把行内定位信息与大字段内容拆开。

PostgreSQL 的变长类型使用 varlena 表示。行过宽时，引擎会先尝试压缩允许压缩的值，仍然过大时把值切片存进关联的 TOAST 表，主 heap tuple 中留下一个外部值指针。TOAST 表自身也是一张内部 heap，并拥有用于按值标识和分片序号查找 chunk 的索引。于是一个看似普通的 SELECT note 可能经历「用户索引 → 主 heap tuple → TOAST 索引 → TOAST heap chunks」多层访问；只查询未被外置的窄列时，则不必读取大字段内容。

InnoDB 的具体行为取决于 row format。以常见的 DYNAMIC 为例，过长的变长列可以把内容放到 overflow page，聚簇记录保留指向外部内容的引用。聚簇叶子仍然是「这一行的权威入口」，却不保证 SELECT * 在命中聚簇叶页后已经取得全部字节。外部页是否在 buffer pool、值被分成多少页，都会影响宽行查询的尾延迟。

这个边界对覆盖索引也很重要。覆盖的价值通常来自「让少量窄列留在更紧凑的索引页中」，而不是复制整行。把大字段塞进 PostgreSQL INCLUDE 会受索引元组大小限制并显著降低扇出；InnoDB 的索引键长度也有上限，前缀索引只保存值的一部分，又未必能够完整返回原列。行外存储解决的是「如何容纳大值」，不是「如何降低大值查询的代价」。

页内还有另一层常被忽略的开销：行头与空值信息。PostgreSQL HeapTuple 除用户列外还带事务标识、命令与标志位、t_ctid、可选 null bitmap 和对齐填充；InnoDB 聚簇记录也带记录头、NULL 信息、变长字段长度以及 MVCC 隐藏列。逻辑上同样的五个字段，在两套引擎里不会自然占用相同字节。列顺序、数据类型、NULL 分布和 row format 都会改变「一页能放多少行」，进而改变缓存密度、树扇出、顺序扫描页数和 HOT 成功率。

因此，底层比较不能停在 8 KB 对 16 KB。页更大可能提高一次读取的空间利用率和树扇出，也可能放大随机读、脏页刷写与并发争用的粒度；页更小可以让缓存选择更细，却可能增加元数据比例和树层级。两套引擎围绕各自默认页大小设计了 WAL/redo、缓冲管理和记录格式，脱离整条路径比较单个数字没有选型意义。

三、二级索引：地址选择产生的第一轮连锁反应

二级索引必须回答一个基础问题：找到二级键后，用什么把它和行连起来？InnoDB 选择主键，PostgreSQL 选择物理 TID。两者各自避免了一类维护成本，也各自把成本推向了另一处。

3.1 InnoDB：二级索引叶子为什么保存主键

对 INDEX(status)，InnoDB 的叶子项可以简化为 (status, primary_key)：

它没有保存聚簇叶页的物理地址。原因是物理地址会随着聚簇树页分裂、页合并和记录重组而变化；若每个二级索引都保存物理地址，那么聚簇页的一次结构调整可能要求批量修正多个二级索引。保存逻辑主键增加了查询的一次树搜索，却把聚簇树的物理变化隔离在自身内部。

这是一种典型的间接层：用稳定标识换取定位成本。 它对存储空间的影响不能忽略。假设主键是 BIGINT，每个二级索引项至少携带这 8 字节主键内容及记录开销；若主键换成长字符串或宽复合键，这份值会进入每一棵二级索引。二级索引越多、行数越大，宽主键的乘数效应越明显：索引文件变大、每页条目减少、树高和缓存工作集可能增加，插入与 redo 量也随之增长。

因此，「InnoDB 主键应尽量短」不是审美偏好，而是二级索引存储机制下的必然选择。一个 36 字符文本 UUID 和一个 8 字节整数，即使业务唯一性相同，在多个二级索引上引发的空间膨胀与局部性损耗，绝非同一个数量级。二进制 UUID 能减少宽度，时间有序 UUID 能改善插入局部性，但是否值得采用仍要综合分布式生成、暴露顺序、迁移复杂度和真实查询模式。

还有一个容易漏掉的收益：二级索引本来就携带主键，所以查询若只需要二级键和主键，可能天然被覆盖：

SELECT id, status
FROM orders
WHERE status = 'paid';

这时通常无需为了取得 id 回到聚簇树。EXPLAIN 中可能出现 Using index。但 MVCC 会给这个结论加上条件，后文再展开。

3.2 PostgreSQL：TID 很直接，但它指向的是行版本

PostgreSQL B-tree 叶子通常保存 (index key, heap TID)。不论它来自主键、唯一约束还是普通索引，寻址模型基本相同。TID 是定长且直接的：拿到 (1842,7)，执行器就知道访问哪个 heap block，不需要再按主键比较一棵树。

但它定位的是某一个 heap tuple version，不是抽象的「第 9527 号订单」。PostgreSQL 的 UPDATE 通常在 heap 中写入新版本，旧版本的 t_ctid 可指向新版本，新版本也拥有新的物理 TID。非 HOT 更新会为新版本写入新的索引项；旧索引项在安全之前仍可能被旧快照需要，之后再由清理过程回收。

需要特别澄清三个常见误解：

• PostgreSQL heap 本身没有按键维护的树结构，所以普通 heap 页不会因为插入键而发生「页分裂」。索引页会分裂，但索引页分裂不改变 heap TID。
• 普通 VACUUM 通常不会为了压紧文件而把仍然存活的元组搬到其他 block。它清理不可见版本、回收页内空间，因此不会普遍让所有活 TID 失效。
• UPDATE、CLUSTER、VACUUM FULL、某些表重写操作会改变 ctid。所以应用不应把 ctid 当作跨事务长期保存的业务主键。

换句话说，TID 的不稳定性主要来自行版本和表重写，不是「只要页面整理或索引分裂就失效」。页内 tuple 本体甚至可以移动，只要槽号对应的 ItemId 保持稳定，TID 仍然有效。

3.3 多行命中时，回表顺序比树高更重要

假设 status = 'paid' 返回 10 万行。InnoDB 二级叶页按 (status, primary_key) 排序，同一 status 下主键也有序，因此回聚簇索引时至少具有一定主键单调性。是否仍然离散，要看这些主键在聚簇叶页的分布以及 buffer pool 命中情况。

PostgreSQL 的 TID 则跟随 heap 物理位置。普通 Index Scan 按索引键序取出 TID，heap block 可能在 900 → 12 → 701 → 12 之间跳转。规划器可选择 Bitmap Index Scan + Bitmap Heap Scan：先收集候选 TID，按 heap block 组织，再以较友好的物理顺序读取 heap。代价是需要构建位图、可能因内存限制变成 lossy bitmap，并失去原索引可提供的输出顺序。

这说明「二级索引找到记录的复杂度是 O(log n)」对性能判断帮助有限。树搜索可能只占很小一部分；返回行数、heap 或聚簇页的相关性、缓存状态和可见性检查才决定总成本。数据库优化器选择顺序扫描，并不意味着它忘了索引存在，而可能是它判断随机回表比顺着表读一次更贵。

四、MVCC 与更新：旧版本究竟放在哪里

表的组织方式决定读取从哪里开始，MVCC（多版本并发控制）又决定读取要沿着什么历史才能找到「我的快照看得见的版本」。InnoDB 和 PostgreSQL 都允许读事务在写入发生时继续看到一致视图，但两者保存历史版本的位置几乎相反。

4.1 InnoDB：当前聚簇记录加 undo 版本链

InnoDB 聚簇记录带有隐藏系统字段，其中 DB_TRX_ID 表示最后插入或更新该记录的事务，DB_ROLL_PTR 指向 rollback segment 中的 undo log record。更新发生时，引擎修改当前聚簇记录，并把重建旧值所需的信息写入 undo。旧快照若发现当前记录太新，便沿 roll pointer 到 undo 中构造它应该看到的历史版本。

这就是常说的「in-place update + undo」，但 in-place 不能理解成永远只改原字节且不触碰其他结构：

• 更新非索引列时，聚簇记录可以在当前位置更新，同时产生 undo、redo，并把数据页变脏；记录变长时还可能涉及页空间调整。
• 更新二级索引列时，旧的二级索引记录会被 delete-mark，再插入新的二级索引记录，因为二级树的有序位置改变了。
• 更新主键相当于改变聚簇键，会带来删除旧聚簇记录、插入新聚簇记录的效果；由于每个二级索引记录也保存主键，影响会扩散到二级索引。
• 删除行先做 delete-mark，等没有活跃 Read View 再需要相应历史后，由 purge 真正移除记录与索引项。

redo 与 undo 也解决不同问题。Redo log 让已提交的数据页修改在崩溃后可以重放，核心目标是持久性与崩溃恢复；undo 支持事务回滚，并为一致性读提供旧版本。一次更新可能同时写数据页、redo、undo 和若干索引页，「原地更新」并不等于写入只有一份。

长事务会阻止 purge 丢弃仍可能被旧 Read View 使用的 update undo。结果可能是 history list 变长、undo tablespace 增长，旧版本读取需要走更长链条，delete-marked 记录迟迟不能回收。InnoDB 没有 PostgreSQL 风格的 heap dead tuple，并不等于它没有垃圾回收问题；它把主要积压表现放在 undo 与 purge 体系中。

4.2 PostgreSQL：新版本写进 heap，旧版本也留在 heap

PostgreSQL 的 heap tuple header 记录 xmin、xmax、t_ctid 等事务与版本信息。一次普通 UPDATE 的直观过程是：把旧版本标记为被更新，在 heap 中插入新版本，并让旧版本的 t_ctid 指向新版本。不同快照根据事务状态与 xmin/xmax 判断哪个版本可见。

PostgreSQL 不需要一份与 InnoDB update undo 对等的文件来重建旧行，因为旧行版本本身就在 heap。事务回滚时也不必把整个数据页还原成更新前的逻辑状态：未提交事务创建的版本不会被其他事务判定为已提交，之后再由清理机制回收。WAL 负责持久化物理变化和崩溃 redo，但 WAL 不是 MVCC 旧版本存储，也不是通用 undo。

这种设计的收益是版本可见性与 heap 元组直接结合，写入新版本也有利于避免在读者仍需要旧内容时覆盖它。代价是更新会持续消耗 heap 空间。更关键的是，索引项指向具体 TID：如果新版本落在另一个 TID，而索引仍只指向旧版本，执行器怎样找到它？朴素答案是为新版本向每一棵普通索引插入新项，即使被更新的只是一个未建立索引的 note 列。

这会产生明显写放大：一张表有 8 棵索引，一次非索引列更新也可能写一个新 heap tuple 和 8 个新索引 tuple，并生成相应 WAL；旧版本和旧索引项稍后还要清理。索引不只是读优化，它也是每次非 HOT 更新都要偿还的写债务。

4.3 HOT：用「同页版本链」绕过索引维护

HOT（Heap-Only Tuple，堆内元组）正是为上述问题设计的优化。当更新没有修改任何被表索引引用的列（PostgreSQL 18 文档对 BRIN 等 summarizing index 有例外说明），并且旧版本所在 heap page 有足够空间容纳新版本时，新版本可以留在同一页，普通索引不需要新增条目。

HOT 的两个条件必须同时成立：

1. 没有修改普通索引引用的列。索引表达式涉及的底层列也算「被引用」，不能只看列名是否直接出现在索引定义中。
2. 原 heap page 留有足够空间。新版本若只能放到另一页，就无法让原 TID 通过受控的页内链条安全找到它。

这也解释了 fillfactor 的取舍。把表的 fillfactor 从默认值调低，会在页中预留更多空间，提高后续 HOT 更新留在原页的概率，减少索引写与 WAL；代价是初始表更大、顺序扫描要读更多页、缓存密度下降。对频繁更新非索引列的表，空出来的空间可能很划算；对几乎只追加和读取的表，预留空间可能只是浪费。参数不能脱离更新模式单独调优。

PostgreSQL 还能在页内做 pruning，移除已经不可能被任何事务看到的 HOT 中间版本，把根 ItemId 变成 redirect。部分工作可在普通访问页面时完成，不必全部等到 VACUUM。但跨页的死元组、索引清理、冻结和可见性映射维护仍需要 VACUUM 体系。

可以从统计视图观察 HOT 是否真的发生：

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       n_tup_newpage_upd,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_pct
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY n_tup_upd DESC;

n_tup_hot_upd 高不自动代表表健康；仍要结合 dead tuple、表与索引尺寸、autovacuum 频率和业务延迟判断。统计是结果，不是目标函数。

4.4 VACUUM 与 purge 都是代价

普通 VACUUM 会识别不再被任何快照需要的 dead tuple，使其空间可供同一张表未来复用，清理不再需要的索引项，维护 Visibility Map，并承担事务 ID 冻结等工作。它通常不会把文件尾部以外的空洞归还操作系统，也不会把所有活元组重排得紧密。VACUUM FULL 能通过重写表收缩文件，却需要更强锁并改变物理 TID；它不是日常垃圾回收的同义词。

InnoDB purge 则根据没有活跃 Read View 再需要的 undo 历史，清除 delete-marked 记录并回收相关历史。它也可能因长事务或写入速度过高而落后。两者的表象不同，但共同规律是：MVCC 把前台读写冲突转移成了后台版本回收，而最老的活跃快照决定哪些历史还不能删除。

因此，把「PostgreSQL 需要 VACUUM、InnoDB 不需要」作为选型结论并不成立。准确说法是：PostgreSQL 把行版本放在 heap，VACUUM 的存在感更强；InnoDB 把历史主要放进 undo，并由 purge 清理。运维要观察的队列和故障形态不同，版本保留的成本没有消失。

五、把写放大拆开看，而不是只数一次 UPDATE

「哪个数据库更新更快」没有统一答案，因为 UPDATE 可能改变主键、二级索引列、非索引定长列或大对象；页可能有空间，也可能分裂；缓存可能命中，也可能需要随机读取；日志还可能包含整页镜像或 doublewrite。更有用的办法是拆分写入对象。

5.1 更新一个未索引列

执行下面的 SQL：

UPDATE orders SET note = 'checked' WHERE id = 9527;

InnoDB 大致需要定位聚簇记录，写 update undo，修改聚簇记录，产生 redo，并把相关页标脏。只要主键和二级索引键都没变，通常不需要为每棵二级索引插入新记录。旧快照从 undo 重建旧 note。

PostgreSQL 会写新 heap tuple，并修改旧 tuple 的版本元数据，产生 WAL。如果满足 HOT 条件，普通索引不变，旧索引 TID 通过同页 HOT chain 找到新版本；不满足 HOT 时，需要为所有普通索引创建指向新 TID 的条目。新 tuple 太大、原页空间不足，或者任意普通索引引用了 note，都会降低 HOT 机会。

因此，在「宽表、多索引、高频更新非索引列」这个特定场景里，InnoDB 当前记录 + undo 的布局通常更容易避免二级索引写放大；PostgreSQL 则高度依赖 HOT 与页内空间。可这不是无条件性能排名：undo 页、redo、锁竞争、purge 积压以及缓存局部性仍会决定 InnoDB 的实际成本，PostgreSQL 的追加式新版本也可能在并发和顺序写入上表现良好。

5.2 更新一个二级索引列

UPDATE orders SET status = 'shipped' WHERE id = 9527;

有序索引不可能在键从 paid 变成 shipped 后继续把记录留在原逻辑位置。两套引擎都必须维护 status 索引：

• InnoDB 将旧二级记录 delete-mark，并在 shipped 的键范围插入新记录；聚簇记录与 undo/redo 也要更新。
• PostgreSQL 无法使用普通 HOT，因为被索引列改变；新 heap tuple 获得新 TID，并为每棵普通索引创建新索引项，而不只是 status 这一棵。

第二点常让从 InnoDB 经验出发的工程师意外。PostgreSQL 索引项绑定具体 tuple version；一次更新若不是 HOT，新版本需要让所有访问路径都能直接找到自己。近年来 PostgreSQL 在 B-tree 重复版本清理、去重等方面持续优化，但这不改变基本版本寻址模型。

5.3 更新主键

主键应稳定在两套系统里都不是一句泛泛的建模原则，而是直接的物理建议。

InnoDB 主键是聚簇键。修改它会改变行在聚簇树中的位置，并且二级索引叶子都保存主键，影响从表本体扩散到所有二级索引。PostgreSQL 主键只是唯一索引，但主键列被索引，更新必然失去 HOT 条件；新 heap 版本和所有普通索引都可能产生新项。两边的原因不同，结论相同：不要把频繁变化的业务属性塞进主键。

5.4 日志、页分裂与后台写也属于写放大

只统计逻辑记录数量仍然会低估成本。完整的写放大至少包含四层：

PostgreSQL 在检查点之后首次修改某数据页时，默认可能把 full-page image 写入 WAL，以防 torn page；检查点过于频繁会让热页反复产生整页镜像。InnoDB 使用 redo 保证恢复，并以 doublewrite buffer 防护不完整页写入。两套系统都在用额外顺序写或冗余页写换取崩溃安全。

随机插入会让 B-tree 中间叶页变满并分裂。InnoDB 聚簇树的分裂同时牵涉承载行数据的页；PostgreSQL heap 不按主键分裂，但它的主键与二级 B-tree 仍会分裂。顺序键能把大部分插入集中到右端，减少随机分裂；它又可能形成热点。更高 fillfactor 提高读密度，却减少更新与插入余量。每一项优化都只是在空间、局部性、并发和后台工作之间重新分配成本。

六、覆盖索引与 Index Only Scan：名字相似，证明路径不同

覆盖查询的基本想法很朴素：如果过滤、连接和返回所需的列都已经在索引记录中，就别去访问表记录。这个优化对 heap 随机访问尤其有价值，但 MVCC 让「列齐了」不等于「答案已经可以返回」。

6.1 InnoDB：二级索引覆盖时可以跳过聚簇树

考虑联合索引：

CREATE INDEX orders_status_created_idx
ON orders (status, created_at);

SELECT id, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at
LIMIT 100;

InnoDB 二级索引记录包含显式索引列以及主键，所以 id 即使没写在索引定义里，也通常已经存在于叶子记录。查询可沿 (status, created_at, id) 的叶层读取并返回，避免对每个候选主键再次搜索聚簇树。这既减少树查找，也保留满足 ORDER BY 的顺序。

但 InnoDB 的二级索引记录没有聚簇记录中的 DB_TRX_ID 与 DB_ROLL_PTR。官方文档指出，当二级索引记录被 delete-mark，或二级索引页被更新事务影响而需要进一步判断时，一致性读可能仍要访问聚簇记录，检查事务 ID，并在必要时从 undo 构造正确版本。此时即使列在二级索引里，MVCC 也可能阻止纯粹的 covering path。

因此 Using index 描述计划具备覆盖条件，不能理解为任何快照、任何并发状态下绝无聚簇访问。实际仍需结合 handler 计数、buffer pool I/O 和事务历史来做性能诊断。

6.2 PostgreSQL：Index Only Scan 还要问 Visibility Map

PostgreSQL 可以把非搜索列作为 payload 放进 B-tree：

CREATE INDEX orders_status_created_idx
ON orders (status, created_at)
INCLUDE (id);

SELECT id, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at
LIMIT 100;

INCLUDE 列不参与树的搜索排序和唯一性语义，只用于携带查询所需数据。即使所有返回列都在索引中，PostgreSQL 仍要回答目标 tuple 对当前快照是否可见，而普通索引项不存完整 heap 可见性信息。

Visibility Map（可见性映射，VM）为每个 heap page 保存 all-visible 与 all-frozen 等摘要位。Index Only Scan 找到索引项后，先检查对应 heap page 的 all-visible bit：

VM 比 heap 小得多，通常容易留在缓存中。对于很少更新、autovacuum 能及时标记 all-visible 的历史数据，Index Only Scan 可以显著减少 heap I/O。对于持续更新的热点表，修改会清除相应可见性位，执行计划虽然仍叫 Index Only Scan，运行统计里却可能出现大量 Heap Fetches。

所以 PostgreSQL 覆盖索引的收益取决于两组条件：索引访问方法能否返回所需列，以及足够多的 heap page 是否 all-visible。把宽列盲目塞进 INCLUDE 会扩大索引、降低扇出、增加非 HOT 更新概率和 WAL；如果表一直很热，最后仍频繁访问 heap，就可能同时支付「宽索引」和「回表」两份成本。

6.3 主键扫描的「覆盖」含义也不同

InnoDB 的聚簇索引叶子已经是行，所以 SELECT * WHERE id = ? 不需要另一份表结构。PostgreSQL 的主键 B-tree 通常只含主键与 TID；SELECT * 必须访问 heap。若只查询主键本身，PostgreSQL 仍要借助 VM 证明可见性才可能真正不读 heap。

这会让 InnoDB 在主键点查和主键范围读取上具有非常自然的局部性。但如果查询经常按另一个维度批量读取整行，二级索引到聚簇树的回表可能成为瓶颈；PostgreSQL heap 也会随机回表，只是它用 TID 直接定位，并可能借助 bitmap 按 heap block 重排访问。没有一种组织能同时让所有维度都物理聚簇。

七、四条 SQL 的完整物理路径

理解了机制，再回头看看查询，差异会更具体。下面省略解析、优化、锁与网络，只看主要存储访问。

这四条 SQL 已经足以解释很多现象：

• 为什么 InnoDB 的宽随机主键会拖累所有二级索引
• 为什么 PostgreSQL 加一棵看似无关的索引可能降低 HOT 比例
• 为什么 PostgreSQL 的 Index Only Scan 仍有 Heap Fetches
• 为什么长事务会背离系统的设计初衷，却在监控指标上表现出截然不同的症状

7.1 主键点查

SELECT * WHERE id = 9527

InnoDB： 从聚簇树根页沿分隔键走到叶页；叶子记录含行的各列，处理 MVCC 后返回。若当前版本对 Read View 太新，沿 DB_ROLL_PTR 到 undo 重建旧版本。

PostgreSQL： 从主键 B-tree 根页走到叶页，得到 TID；访问对应 heap block 和槽，按 tuple header 与事务状态判断可见性。索引若指向旧版本，执行器按相应版本规则处理；HOT 情况可在页内链上找到合适版本。

冷缓存下，PostgreSQL 理论上多一个 heap 页访问；热缓存下，这可能只是一次内存页查找。InnoDB 也可能因 undo 链或溢出列访问额外页面。不要把结构上的一跳/两跳机械换算成固定 I/O 次数。

7.2 二级索引点查

SELECT * WHERE status = 'paid' AND ...

InnoDB： 搜索 status 二级树，取得主键；再搜索聚簇树，取得整行并判断可见性。命中多个候选时重复该过程，除非查询被二级索引覆盖或其他优化减少回表。

PostgreSQL： 搜索 status B-tree，取得 TID；按 TID 访问 heap 并判断可见性。多行时可能选择普通 Index Scan、Bitmap Heap Scan 或干脆 Seq Scan，取决于选择率、物理相关性、排序需求和成本估算。

7.3 主键范围

WHERE id BETWEEN 10000 AND 11000

InnoDB： 定位聚簇叶层起点，沿叶页链连续读取行。只要页布局与缓存正常，这正是聚簇组织最擅长的模式。

PostgreSQL： 主键 B-tree 叶项连续，但对应 heap TID 不保证按 block 连续。若查询需要整行，索引范围扫描可能带来离散 heap 访问；当 heap 曾按主键执行 CLUSTER 且后续写入尚未严重失序时，物理相关性会改善，但 PostgreSQL 不会自动永久维持这种顺序。

7.4 高频更新

UPDATE ... SET note = ? WHERE id = ?

InnoDB： 主键定位当前聚簇记录，写 undo/redo 并更新记录；二级键未变时通常不改二级索引。旧快照从 undo 读历史。

PostgreSQL： 主键索引定位旧 TID，写新 heap version；若 note 不被任何普通索引引用且原页有空间，走 HOT，不新增普通索引项；否则写所有普通索引的新条目。后续 pruning/VACUUM 回收旧版本。

八、如何把底层差异转化为索引设计

理解内部结构的意义是什么？它能从根本上改变你的数据建模逻辑与性能诊断视角。

8.1 InnoDB：先把主键当作全表物理 API

设计 InnoDB 主键时，应该先回答四个问题：

1. 是否稳定？ 更新主键会移动聚簇记录，并改写所有二级索引中的主键副本。
2. 是否足够短？ 主键宽度会乘上二级索引条目数量。
3. 插入分布如何？ 随机键分散写入、增加页分裂与缓存工作集；单调键提高局部性，也可能制造右端热点。
4. 最重要的范围查询是否按主键？ 聚簇顺序只有一个，应给最值得的数据访问模式，而不是只满足「每表必须有 ID」的习惯。

业务上必须使用自然宽键时，可以保留唯一约束，再用短代理键作聚簇主键；也可以直接接受宽键以减少一次业务键到代理键的间接层。前者节省所有二级索引空间，后者让业务身份更直接。谁更好取决于二级索引数量、连接模式、写入量与数据生命周期，不能只凭范式或 ORM 默认值决定。

8.2 PostgreSQL：索引数量会进入 HOT 的隐性成本函数

PostgreSQL 中，新增索引不只增加插入、删除和被索引列更新的成本。只要一次更新不能走 HOT，新 tuple version 通常要进入所有普通索引。因此应审查重复索引、低价值单列索引、过宽 INCLUDE 和很少使用的表达式索引。

对于频繁更新表，需要注意：

• 被频繁修改的列是否出现在任何索引键、表达式或 INCLUDE 中；
• n_tup_hot_upd / n_tup_upd 比例以及 n_tup_newpage_upd；
• 页是否因行宽与 fillfactor 很难留出 HOT 空间；
• n_dead_tup、autovacuum 执行频率、长事务与复制槽是否拖住清理；
• 索引实际扫描次数能否证明它值得持续支付写成本。

降低 fillfactor 不应是首要决策，先删掉无价值索引、缩短事务、确认 autovacuum 跟得上，再用代表性负载验证预留页空间是否换来了足够 HOT 收益。

8.3 覆盖索引只覆盖具体查询，不覆盖所有性能问题

覆盖索引最适合返回列少、访问频繁、选择性合适且读收益明确的查询。它不是把 SELECT * 所有列复制进索引的理由。

对 InnoDB，要记得二级索引已经隐式携带主键；重复把同样主键列塞进定义，未必增加覆盖能力。对 PostgreSQL，要把 all-visible 比例纳入收益估算：高度易变表即使列齐全，也可能频繁 heap fetch。两边都要考虑宽索引降低扇出、扩大缓存工作集并增加写放大。

最可靠的方法仍是用真实数据分布和缓存条件运行 EXPLAIN ANALYZE，同时检查实际 I/O、页命中、返回行数和后台回收状态。只看优化器给出的 Using index 或 Index Only Scan 名字不够。

九、总结：没有谁消灭了成本，只是把成本放在不同位置

下面这张表不是胜负表，而是一份需要长期支付的账单

回到开头的两条 SQL：相同的 SELECT 在 InnoDB 中可能先取得主键再搜索聚簇树，在 PostgreSQL 中则取得 TID 后访问 heap；相同的非索引列 UPDATE，InnoDB 主要维护聚簇当前记录与 undo，PostgreSQL 则写一个新 heap 版本，并努力用 HOT 避免触碰所有索引。

这两套设计都没有凭空消灭随机 I/O、版本历史或垃圾回收。InnoDB 用主键这一层稳定间接引用，隔离聚簇页的物理变化，代价是二级索引膨胀和二次搜索；PostgreSQL 用直接 TID 缩短寻址，把行版本放在 heap，代价是 TID 随版本变化，以及 HOT、VACUUM 和 Visibility Map 共同承担后续维护。

系统设计里很少有零成本的抽象。真正可靠的判断，始终来自同一条因果链：记录放在哪里，引用如何保持有效，历史由谁保存，垃圾何时回收，最后哪些页真的被读写。 看懂这条链，才算看懂 SQL 背后的数据库。

如果你还想问，如何做数据库选型？可能会回答你，看那个数据的成本更贴近工作负载，但更可能回答说，如果在 MySQL 上没有积累，选择 PostgreSQL 不会让你失望。