深入对比 MySQL

带着 MySQL 的直觉读 PG，最大的风险不是看不懂，而是看懂了错的东西——把「表就是主键 B+tree」「长事务撑大 undo」「默认隔离是 RR」这些 InnoDB 的事实，悄悄套到 PG 上。这一章是整份教程的跨章辨析章：把前五章每个 PG 机制，都拉到 InnoDB 的对应实现旁边并排看。同样是「读不阻塞写」，两者代价相反；同样是 count(*)，两边都全扫但 PG 多一条出路；同样是默认隔离级别，失败模式南辕北辙。看清差异，前五章的模型才真正长进直觉里。

§1聚簇索引 vs 堆：主键决定一切，还是什么都不决定

InnoDB 的表就是主键 B+tree，行躺在主键里；PG 的表是无序堆，主键只是又一个指向 ctid 的索引。

InnoDB 怎么做：表即主键 B+tree

InnoDB 的表本身就是一棵以主键排序的 B+tree（聚簇索引 / clustered index，亦称索引组织表）。 这棵树的叶子节点直接存放整行数据——行不是独立躺在某个堆里，行就是主键 B+tree 的叶子内容。默认数据页 16KB。没有显式主键时，InnoDB 会挑第一个非空唯一索引、再不然用内部生成的 6 字节 row_id 当聚簇键。

这带来一个 MySQL 老手都熟的事实：二级索引（secondary index）的叶子节点存的是主键值，而不是行的物理指针。 于是一次非覆盖的二级索引查找要走两次 B+tree 下降——先在二级索引树里下降，找到匹配行的主键值；再拿这个主键值到聚簇索引树里第二次下降，才取到整行。这第二步就是 MySQL 语境里的回表。只有当查询要的列全在二级索引里（覆盖索引）时，才省掉第二次下降。

后果直接挂在主键的选择上。 用单调递增的主键（如 AUTO_INCREMENT），新行总是追加到 B+tree 最右侧的叶子页——顺序写、页填得满、几乎不分裂。换成随机的主键（如 UUID v4），插入点散布在整棵树中间，频繁触发页分裂：一个写满的中间页被迫一分为二，留下半空的页、制造碎片、降低页填充率。更隐蔽的代价是：因为每一个二级索引的叶子都存着主键值，主键越宽（UUID 16 字节起，文本主键更甚），所有二级索引就越胖、越占空间、缓存效率越低。

对照 PG：堆 + 平权独立索引

PG 这边，第 1 章 §tuple 立下的地基此刻全部生效：行存在一个无序的堆里，物理顺序与任何键都无关。每一个索引——包括主键索引——都是独立的结构，叶子节点存的是 ctid，即 (block, offset) 这个物理坐标。PG 里没有聚簇索引这个概念，没有哪个索引是「主」的、其余是「从」的：主键索引和任意一个普通 B-tree 索引在结构上完全平权，都通过 ctid 一跳定位到堆里的行。

所以主键的选择对 PG 的存储布局影响很小。用 UUID 当主键，PG 基本没有 InnoDB 那套惩罚：主键索引该分裂还是分裂（这是 B-tree 的固有行为），但它不决定行的物理位置，不会把整张表的数据布局搅乱，更不会因为主键宽就让其它索引一起变胖——因为 PG 的二级索引叶子存的是 ctid（定长 6 字节），不是主键值。代价是另一面：PG 的二级索引无法靠「叶子存主键」实现覆盖时的去回表优化（它本来就一跳到堆，没有「回表」这一说），并且任何索引都会随更新删除而碎片化。PG 的 CLUSTER 命令能按某个索引把堆重排成物理有序，但那只是一次性操作——重排完之后新的插入更新照样打乱顺序，PG 不会像 InnoDB 那样持续维护聚簇有序。

§2undo-log MVCC vs 堆内多版本：旧版本去了哪

两种 MVCC 都让读不阻塞写，但旧版本的去向相反——InnoDB 推进 undo log，PG 留在堆里等 VACUUM。

InnoDB 怎么做：原地更新 + undo 链回溯

InnoDB 是原地更新（in-place update）。 UPDATE 直接在聚簇索引的叶子上修改那一行的当前值；被覆盖掉的旧值并不丢弃，而是被推入 undo log（回滚段，rollback segment）。每一行在聚簇索引里都带两个隐藏字段：DB_TRX_ID（最后修改它的事务 id）和 DB_ROLL_PTR（指向该行在 undo log 里的上一个版本的回滚指针）。

读的时候，一个事务的一致性视图叫 read view。当它读到聚簇索引里的当前行、发现这一行的 DB_TRX_ID 对自己的 read view 不可见（即这行被一个「在该 read view 之后才开始、或此刻尚未提交」的事务改过）时，InnoDB 就顺着 DB_ROLL_PTR 进 undo log，沿 undo 链一步步回溯、重建出对这个 read view 可见的那个旧版本。换句话说，InnoDB 的旧版本是「按需从 undo 记录里现场重算」出来的，不是像 PG 那样以完整 tuple 形式躺在表里。

undo 由专门的 purge 线程回收：当某条 undo 记录所对应的旧版本，已经不再被任何活跃 read view 需要时，purge 才能把它清掉。这里埋着 InnoDB 长事务的核心代价——一个长时间运行、或开了事务却空闲不提交的事务，会一直钉住它的 read view，使得「它仍需回溯到的那些旧版本」对应的 undo 记录无法被 purge。后果是 history list length（待清理的旧版本链总长）持续增长、单行的 undo 版本链越拖越长，于是凡是要回溯老版本的读都被拖慢。但关键在于：这撑大的是 undo 表空间，表（聚簇索引）本身不膨胀——当前行始终是原地的那一份。

对照 PG：旧版本留在堆里成死元组

PG 的做法 第 2 章已经讲透：UPDATE 在堆里写一个新 tuple，旧 tuple 原地保留、被盖上 xmax 失效戳，待再无快照能看到它便成死元组。旧版本就是一份完整的、躺在堆里的 tuple——不需要「重算」，直接读。回收它的是 VACUUM，不是 purge 线程。

于是 PG 的长事务代价落在另一处：一个长事务持有的旧快照，会拦住 VACUUM——凡是「这个老快照仍能看到」的死元组，VACUUM 都不能回收（回收了老事务就读不到它该读的版本了）。死元组在堆里越积越多，表和它的所有索引一起膨胀（bloat）。这正是 InnoDB 与 PG 长事务症状的镜像：同一个「长事务拦住清理」的因，在 InnoDB 撑大 undo、在 PG 撑大关系文件。

§3三日志 vs 单 WAL：一套日志干几件事

MySQL 用 redo + undo + binlog 三套日志分担职责，PG 用一套 WAL 同时干崩溃恢复、物理复制、逻辑解码。

InnoDB/MySQL 怎么做：两层架构，三套日志

根因是架构。MySQL 是两层结构：上层是 server 层（解析、优化、执行、复制），下层是可插拔的存储引擎（InnoDB 是默认的那个）。 这道分层直接导致日志被切成三套，各管一摊：

• redo log（InnoDB 层）：物理日志，记录「对哪个页的哪个字节做了什么改动」，环形（固定大小、循环覆盖），唯一职责是崩溃恢复——重启后把已提交但未刷盘的页变更重放回来。
• undo log（InnoDB 层）：即 §2 的回滚段，服务于事务回滚与 MVCC 旧版本重建。
• binlog（server 层）：逻辑日志，记录「执行了什么 SQL / 行级数据怎么变」，有 statement / row / mixed 三种格式，是主从复制与时间点恢复（PITR）的数据源。因为它在 server 层，所以与具体存储引擎无关。

三套日志分属两层，引出一个出名的工程难题：提交一个事务，既要写 InnoDB 的 redo、又要写 server 层的 binlog，两者必须原子地一起成功或一起失败，否则主从就会不一致。InnoDB 用两阶段提交（redo prepare → 写 binlog → redo commit）保证这一点，并用 group commit 把多个并发事务的 fsync 批量合并，摊薄刷盘开销。

对照 PG：单一 WAL 一肩挑

PG 没有两层架构（存储与执行一体），也就没有「server 层日志 vs 引擎层日志」之分。第 3 章讲的那一套 WAL（Write-Ahead Log）就是全部，它同时承担 MySQL 要三套日志才分担得了的职责：

• 崩溃恢复——这是 WAL 的本职，对应 MySQL 的 redo。
• 物理流复制——备库直接接收并重放主库的 WAL 字节流，对应 MySQL 用 binlog 做的复制（但 PG 用的是物理 WAL，不是逻辑语句）。
• 逻辑解码（logical decoding）——PG 能把同一份物理 WAL 解码成逻辑变更流，喂给逻辑复制 / CDC，这部分对应 MySQL 的 row 格式 binlog。

至于「旧版本 / 回滚」，PG 不需要单独的 undo 日志——因为旧版本本就是堆里的死元组（§2），回滚靠的也是 MVCC 可见性，不靠回放 undo。所以 MySQL 的 redo + undo + binlog 三件事，在 PG 这里被收敛成「WAL + 堆内多版本」两件事，而其中复制与 CDC 又都从那一份 WAL 派生。

§4默认 RR vs RC：失败模式南辕北辙

InnoDB 默认 REPEATABLE READ 靠 gap 锁防幻读，PG 默认 READ COMMITTED;两者冲突时一个阻塞/死锁、一个直接中止重试。

InnoDB 怎么做：默认 RR + next-key 锁

InnoDB 默认隔离级别是 REPEATABLE READ（RR）。 在 RR 下，普通的一致性读用的是事务首次读时建立的快照（与 PG 的快照思路相通）；但 InnoDB 还要在 RR 这一级防住幻读，靠的是next-key 锁——它是记录锁（record lock，锁住索引上某条已存在的记录）+ 间隙锁（gap lock，锁住索引记录之间那段不存在的区间）的组合。锁住「区间」意味着别的事务无法往这段间隙里插入新行，于是同一个范围查询两次读到的行集合不变——幻读被堵死。

这里要分清两种读：普通 SELECT 是一致性快照读，不加锁；而 SELECT ... FOR UPDATE / FOR SHARE 是加锁读（locking read），会沿扫描路径取 next-key 锁。间隙锁的副作用是出名的：两个事务各自锁住相邻间隙、再都想往里插，就互相等成死锁；或者一个事务的范围加锁读，会阻塞另一个事务对该范围的插入，哪怕它们插的根本不是同一行。

对照 PG：默认 RC，RR 是快照隔离，SERIALIZABLE 是 SSI

PG 的三件事都和 InnoDB 不同（第 2 章 §isolation 已铺垫）：

• 默认是 READ COMMITTED（RC），不是 RR。每条语句开始时取一个新快照，能看到此前已提交的最新数据。
• PG 的 REPEATABLE READ 是真正的快照隔离（snapshot isolation）：整个事务用一个固定快照，没有间隙锁。它不靠锁住区间防幻读，而是当两个事务的写产生冲突时，直接让其中一个抛出 40001 serialization failure（序列化失败）、回滚——由应用捕获并重试。
• PG 的 SERIALIZABLE 是 SSI（Serializable Snapshot Isolation）：在快照隔离之上追踪事务间的读写依赖，用谓词锁（predicate lock，即 SIReadLock，一种只做冲突检测、不阻塞别人的「软」锁）发现破坏可串行性的环，再中止其中一个事务。它仍然不阻塞，只在检测到危险时中止。

§5优化器与 join：计划空间谁更大

两者都是基于代价的优化器，但 PG 有三种 join、更丰富的可用索引类型，计划空间更大。

MySQL 怎么做：从 nested-loop 一路补齐

MySQL 的 join 算法有一条清晰的演进线：

• 历史上只有 nested-loop join 及其变体 block-nested-loop（BNL，块嵌套循环）——把外表成批读进 join buffer 再扫内表，缓解纯逐行嵌套的低效。
• hash join 自 MySQL 8.0.18（2019-10）引入；到 8.0.20（2020-04）移除了 BNL，由 hash join 全面替代——包括非等值连接、外连接、半连接等场景。
• MySQL 没有 merge join（sort-merge join）这种算法。
• 优化器升级：9.7（2026-04）把 Hypergraph 优化器带进了 Community 版，扩展了连接顺序的搜索能力。

对照 PG：三种 join + 更多索引扫描方式

PG 规划器的 join 工具箱（第 5 章 §joins）一直是三种齐备：nested loop、hash join、merge join——后者是 MySQL 至今没有的。加上 PG 独有的 bitmap index scan（把多个索引的命中结果按位图合并后再回堆，详见 第 4 章），以及规划器能利用的索引类型远多于 InnoDB（B-tree / Hash / GIN / GiST / SP-GiST / BRIN，见下一节），PG 优化器的计划空间明显更大——同一条查询，可枚举的扫描方式与连接策略组合更多。两者都是基于代价（cost-based）的优化器，差别在可选算子的丰富度，而非选择范式。

§6索引类型与事务型 DDL：能力面的差距

PG 可被规划器利用的索引类型远多于 InnoDB，且 DDL 完全事务化;MySQL 8.0 的原子 DDL 不等于可回滚。

索引类型：InnoDB 的清单 vs PG 的清单

InnoDB 能用的索引类型：B+tree（绝大多数索引）、FULLTEXT（全文）、R-tree（空间索引，用于 GEOMETRY）；此外 8.0 起支持用生成列做表达式索引（不能直接对表达式建索引，要先建 generated column）、invisible 索引（对优化器隐藏、用于灰度下线索引）、降序索引（descending index，真正按降序存储）。

PG 能用、且规划器能直接利用的索引类型：B-tree、Hash、GIN（倒排，适合数组 / jsonb / 全文）、GiST（通用平衡树，适合几何 / 范围 / 最近邻）、SP-GiST（空间划分）、BRIN（块范围索引，适合超大且物理有序的表）；外加部分索引（partial index，带 WHERE 条件只索引子集）、表达式索引（直接对表达式建，无需先造生成列）、覆盖索引的 INCLUDE 子句（把非键列附在叶子上做覆盖）。第 4 章展开了这些类型各自的命中场景。结论：可被规划器利用的索引类型，PG 远多于 InnoDB——这也是上一节「PG 计划空间更大」的一个来源。

事务型 DDL：可回滚 vs 隐式提交

PG 的 DDL 完全事务化。 CREATE TABLE / ALTER TABLE / DROP TABLE 等都能放进 BEGIN ... COMMIT 里，和普通 DML 一样——中途 ROLLBACK 就把这些 DDL 一并撤销，仿佛没发生过。多条 DDL 加几条数据修正可以打包成一个原子迁移事务，要么全成、要么全回滚。

MySQL 8.0 引入的是「原子 DDL（atomic DDL）」，但这不等于「事务型 DDL」。 原子 DDL 保证的是：单条 DDL 语句要么完整成功、要么完整失败（即使中途崩溃也不会留下半成品），它把数据字典、存储引擎操作和 binlog 写入捆成一个原子单元。但是：DDL 语句会触发隐式提交（implicit commit）——执行一条 DDL 会先把当前事务提交掉，因此 DDL 不能被包在多语句事务里，也无法 ROLLBACK。换言之，MySQL 的「原子」管的是单条语句的崩溃安全，不是「多条 DDL 一起回滚」。

§7迁移对照表与一个迁移示例

把前六节的机制差异，落到迁移时具体要改的语法与行为上。

下面这张表把「从 MySQL 迁到 PG」时最常撞上的差异逐条列出，每一条都能追溯到前面某一节的机制根源。

一个迁移示例：DDL 逐行映射

把上表里几条最常见的差异，凝成一段真实迁移 diff。下面两段建表语句左右对应，逐行说明 MySQL 写法在 PG 里换成什么。两段均未在本机执行，仅用于演示语法映射。

CREATE TABLE orders (
  id         BIGINT       NOT NULL AUTO_INCREMENT,
  user_id    BIGINT       NOT NULL,
  status     ENUM('new','paid','shipped') NOT NULL DEFAULT 'new',
  is_active  BOOLEAN      NOT NULL DEFAULT 1,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
                           ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_status (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- upsert
INSERT INTO orders (user_id, status) VALUES (42, 'paid')
  ON DUPLICATE KEY UPDATE status = VALUES(status);
                                          -- 未在本机执行

CREATE TYPE order_status AS ENUM ('new','paid','shipped');

CREATE TABLE orders (
  id         BIGINT       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    BIGINT       NOT NULL,
  status     order_status NOT NULL DEFAULT 'new',
  is_active  BOOLEAN      NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ  NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ  NOT NULL DEFAULT now(),
  CONSTRAINT uq_user_status UNIQUE (user_id, status)
);
-- updated_at 自动刷新需 BEFORE UPDATE 触发器（PG 无 ON UPDATE 语法）

-- upsert：必须指明冲突目标
INSERT INTO orders (user_id, status) VALUES (42, 'paid')
  ON CONFLICT (user_id, status) DO UPDATE
    SET status = EXCLUDED.status;
                                          -- 未在本机执行

逐行映射，把每处改动对回上表与机制：

• AUTO_INCREMENT → IDENTITYBIGINT AUTO_INCREMENT 换成 BIGINT GENERATED ALWAYS AS IDENTITY。两者都会留下空隙（回滚/崩溃跳号）;PG 底层用一个非事务 sequence，回滚不退号。
• ENUM 内联 → CREATE TYPEMySQL 把枚举写在列上;PG 先 CREATE TYPE order_status AS ENUM (...) 再把列声明成这个类型（也可退化成带 CHECK 的 text）。
• BOOLEAN DEFAULT 1 → trueMySQL 的 BOOLEAN 是 TINYINT(1) 别名、默认值写 1;PG 是真 boolean，默认值写 true。
• ON UPDATE CURRENT_TIMESTAMP → 触发器PG 没有列级 ON UPDATE 自动刷新语法，updated_at 的自动更新要靠一个 BEFORE UPDATE 触发器（此处仅以注释标出）。TIMESTAMP 一并换成带时区的 TIMESTAMPTZ。
• ON DUPLICATE KEY → ON CONFLICT (...)upsert 从 ON DUPLICATE KEY UPDATE 换成 ON CONFLICT (user_id, status) DO UPDATE——PG 必须显式指明冲突目标，且引用新值用 EXCLUDED.col（不是 MySQL 的 VALUES(col)）。
• 删掉 ENGINE/CHARSETENGINE=InnoDB DEFAULT CHARSET=utf8mb4 整段删除——PG 没有可插拔引擎（§3），编码在数据库/集群层定。