1. 概念定位

• MySQL：Oracle 旗下，默认"单进程多线程 + 可插拔存储引擎"，以易用、高并发读为主。
• PostgreSQL：社区开源，默认"多进程单引擎（Heap）+ 插件化扩展"，以功能完整、SQL 标准兼容为主。

---

2. 来源血统

• MySQL：1995 年瑞典公司 → Sun → Oracle
• PG：1986 年 UC Berkeley Post-Ingres → 全球社区持续 30+ 年

---

3. 宏观架构

维度	MySQL	PostgreSQL
连接模型	线程池/每连接线程	每连接独立进程
层次	4 层：Client → Connection(连接层) → Service(服务层) → Storage-Engine(存储引擎层)	5 层：Client → Connection(连接层) → SQL(查询引擎) → Storage-Mgr(存储管理层) → Disk(物理存储层)
存储引擎	多引擎（InnoDB、MyISAM…）可换	基本只有 Heap，不可换（12 起有 API，仍仅 Heap 成熟）

PG 为什么把存储引擎层拆分成 2 层？

• MySQL 的存储引擎把"缓存逻辑 + 文件落地"捆在一起，所以"换存储 = 换引擎"
• PostgreSQL 把这两件事拆成 Storage-Manager（逻辑） vs Physical-Storage（物理） 两层，于是不换引擎，也能换磁盘，这就是拆层的根本原因

层级	负责什么	不关心什么
Storage-Manager 层（逻辑）	1. 8 KB 页在内存 Buffer 里的哈希表 2. 脏页队列、刷盘策略 3. WAL 生成、Checkpoint、Vacuum、可见性图	磁盘文件叫啥、分片多大、是否压缩
Physical-Storage 层（物理）	1. 把"页号→文件名/偏移"翻译出来 2. 真正 read()/write()/pwrite() 3. 支持"一个表超 1 GB 自动分片"、Direct I/O、加密页	MVCC、并发、日志、锁

---

4. 存储引擎实现

• MySQL-InnoDB：聚簇 B+ 树，主键即数据；二级索引叶子存主键值，回表需再扫 PK。
• PG-Heap：非聚簇，数据=无序堆页；任何索引（含 PK）叶子都只存 <键, TID>，必回表（Index-Only Scan 除外）

---

5. 索引结构

• MySQL：InnoDB 用 B+ 树（聚簇）；支持 Hash、Full-text、R-tree（GIS）但非默认
• PostgreSQL：默认 B-link-tree（非聚簇），叶子带右链表；支持 Hash、GiST、GIN、SP-GiST、BRIN、Bloom 等多访问方法

---

6. 执行计划节点速查

场景	MySQL 节点	PostgreSQL 节点	备注
全表扫	ALL	Seq Scan	都是最底层
普通索引回表	range/ref/eq_ref	Index Scan	均需二次回表
覆盖索引	using index 附注	Index Only Scan	PG 有 VM bitmap 判断可见性
多条件合并	index_merge	Bitmap Index/Heap Scan	PG 用位图批量回表，MySQL 逐条
唯一等值	const/eq_ref	Unique Scan	都只需读 1 行
物理行号	无	TID Scan	PG 独有，ctid 直接定位页/槽

---

7. 成本可见性

• MySQL：EXPLAIN FORMAT=JSON 给 cost=*，但无启动成本、无行宽、无缓存命中统计
• PostgreSQL：EXPLAIN (ANALYZE,BUFFERS)，输出启动/总成本、行宽、shared hit/read、实际时间，调优粒度更细

---

8. 写语句执行计划

• MySQL：EXPLAIN 默认是静态计划，不会真正执行， 加参数如 EXPLAIN ANALYZE UPDATE 会真改数据，需手动包事务回滚
• PostgreSQL：同上，默认也是静态计划，加参数 EXPLAIN (ANALYZE) 包裹任何 DML 会真正执行，update 需要回滚

---

9. 并发与锁（MVCC）

• MySQL-InnoDB：行锁+MVCC，索引与数据同一聚簇树，主键更新可能隐式锁整页。 MVCC（隐藏字段、readView、锁）
• PostgreSQL：堆页与索引分离，更新只写新堆元组+索引新条目，旧版本通过 VACUUM 回收，读写不阻塞扫描。 MVCC（隐藏字段、快照比对 xmin/xmax 判断是否可见、原地保留旧版本（更新=插入新版本+给旧版本打删除标））

MySQL 的隐藏字段

• DB_TRX_ID：最后一次插入/更新该行的事务 ID
• DB_ROLL_PTR：回滚指针，指向 undo log 中该行的前一个版本，形成版本链
• DB_ROW_ID：单调递增行号；当表没有主键时，InnoDB 用它作为聚簇索引键

MySQL 的快照字段（ReadView）

• m_low_limit_id：创建快照时 InnoDB 将分配的下一个事务 ID（≥它的都视为"未来"）
• m_up_limit_id：创建快照时最小活跃事务 ID（<它的都视为"已提交"）
• m_creator_trx_id：创建本 ReadView 的事务自身 XID
• m_ids：当时仍在运行的活跃事务 ID 列表（位于 low~up 之间）

PG 的隐藏字段

• xmin：插入它的事务 XID
• xmax：删除/更新它的事务 XID（未删除时为 0）
• cid：同一事务内命令序号，用于区分"语句级"还是"事务级"快照
• ctid：物理位置（页号, 行号），定位用

PG 的快照字段

• xmin：当时最小活跃 XID（比它老的全部已提交，可见）
• xmax：下一个将分配 XID（大于等于它的全未提交，不可见）
• xip_list：当前真正还在跑的 XID 数组（在 xmin~xmax 之间但还没提交）

---

10. 日志对比

场景	MySQL (InnoDB)	PostgreSQL
旧版本放在哪	undo 段（独立表空间）	原堆页（xmax 标记）
崩溃恢复靠什么	redo log (ib_logfile*)	WAL (pg_wal/xxxxx)
主从复制靠什么	binlog (row/mixed)	同一份 WAL（物理流复制）或逻辑解码插件
提交刷盘参数	innodb_flush_log_at_trx_commit=1	fsync=on
长事务后果	undo 段膨胀	堆页膨胀（dead tuple），需 autovacuum

MySQL 日志	作用	PostgreSQL 对应日志	文件名/开关	是否持久化
undo log	1. 事务回滚 2. MVCC 读旧版本	无需独立 undo log	—	—
redo log	崩溃恢复，保证已提交事务不丢	WAL (redo)	pg_wal/ 目录	是
binlog	主从复制、时间点恢复	WAL (redo) + 逻辑解码 slot	pg_wal/ + pgoutput 插件	是

---

11. PGSQL 是如何解决不同隔离级别下脏读、不可重复读、幻读？

一、隔离级别与现象对照（PG 内部只实现 3 种）

隔离级别	脏读	不可重复读	幻读	实现机制
读未提交	✗（实际=读已提交）	✔ 可能	✔ 可能	语句级快照
读已提交（默认）	✗ 不可能	✔ 可能	✔ 可能	语句级快照
可重复读	✗ 不可能	✗ 不可能	✗ 不可能	事务级快照
可串行化	✗ 不可能	✗ 不可能	✗ 不可能	事务级快照 + 谓词锁 (SSI)

PG 的 RR 级别天然不会出现幻读，因为整个事务使用同一快照；若并发插入新行，写操作会在提交时做"可串行化冲突检测"，直接回滚后发起事务，而不是靠间隙锁挡住。

二、具体怎么解决三种读问题

1. 脏读

• 任何级别都不可能读到别事务未提交的数据——PG 的 MVCC 规则决定：只要元组的 xmin 尚未提交，就对别人不可见。

2. 不可重复读

• RC：每条语句重新拿快照，别事务提交后的新版本立即可见 → 允许不可重复读
• RR / Serializable：事务启动瞬间生成全局快照，之后无论读多少次，都只看快照范围内的旧版本 → 天然避免不可重复读。

3. 幻读

• RC：语句级快照，后续语句能看到别事务新插入并提交的行 → 允许幻读
• RR：同一快照，后续查询看不到新插入行；若本事务随后想更新那些"幻影行"，PG 会在提交时检测"串行化异常"——如果发现要改的行在快照里不可见但已被别人改，则回滚本事务（抛 ERROR: could not serialize access due to concurrent update）。
• Serializable：在 RR 基础上再加谓词锁 (SSI)，对查询范围加"逻辑锁"，冲突检测更严格，百分百无幻读。

---

三、锁机制总结（没有 next-key lock）

锁类型	存在级别	作用
行级排他锁	所有级别写操作	阻止 concurrent update 同一行
谓词锁 (SSI)	Serializable	阻止并发事务在查询范围内插入/更新，冲突即回滚
页级/表级锁	显式 Lock 或系统内部升级	日常 DML 不出现

---

12. pgsql 是如何保证 ACID？

一、原子性（Atomicity）

1. 做法

• 所有变更先写 WAL（Write-Ahead Logging），再改内存缓冲页；
• 提交时只做 WAL 刷盘（fsync=on），内存页异步写；
• 若中途崩溃，重启后重放 WAL 把已经记录但未 flush 到数据页的修改重新做一遍，已经部分写入的页不会对外可见（因为快照规则只看已提交事务）。

2. 回滚阶段

• 事务主动 ROLLBACK 或报错 → 把当前事务产生的所有 WAL 记录标记为 ABORT，恢复时跳过它们即可；
• 旧版本原地保留（xmax 填本事务 XID），无需额外 undo 文件。

二、一致性（Consistency）

1. 数据库层一致性

• 所有约束（主键、唯一、外键、检查、触发器）在语句执行期即刻校验；任何失败立即抛错，事务进入 aborted 状态，后续 SQL 都被拒绝，直到 ROLLBACK。

2. 业务层一致性

• MVCC 快照保证事务看到稳定的静态视图（RR/SR 级别），不会出现"半更新"的中间状态。

三、隔离性（Isolation）

1. 默认隔离级别 读已提交（RC）

• 每条语句重新拿快照，只能读到已提交版本；
• 写冲突：若两个事务改同一行，后者等待前者提交/回滚后获得行级排他锁再继续。

2. 可重复读（RR）

• 事务启动时生成全局快照，整个事务用同一张快照 → 天然避免不可重复读、幻读；
• 写冲突检测：提交时发现"要改的行在快照里不可见但已被别人改" → 串行化失败，强制回滚（ERROR: could not serialize access）。

3. 可串行化（Serializable）

• 在 RR 基础上再加 SSI 谓词锁（Serializable Snapshot Isolation），对查询范围加"逻辑锁"；
• 任何并发事务若在此范围内插入/更新，冲突即回滚，百分百无幻读。

四、持久性（Durability）

1. WAL 刷盘策略

• fsync=on（默认）：每次 commit 都调用 fsync()，保证 WAL 落盘才返回客户端成功；
• synchronous_commit 可细调：
  • on → 等本地刷盘（最强）
  • remote_write → 等备库收到 WAL（同步复制）
  • off → 延迟写（最快，但可能丢≤1 事务）。

2. 崩溃恢复流程

• 从最近一次 checkpoint 记录点开始，顺序重放其后所有 WAL 段；
• 已经提交的事务重做（redo），未提交或 abort 标记的跳过 → 数据库重启后处于一致且持久的状态。

---

五、锁与日志分工速记

目标	靠日志	靠锁
原子性	WAL 先写后刷，崩溃重放	—
一致性	约束/触发器校验失败→回滚	—
隔离性	MVCC 快照挡住读异常	行级锁挡写-写冲突；SSI 谓词锁挡范围冲突
持久性	WAL 刷盘即持久	—