PostgreSQL 与 MySQL 索引和事务实现有什么区别？

学完这篇，你能说清 PostgreSQL 和 MySQL 在索引存储结构、MVCC 实现、默认隔离级别上的实质差别，并知道在自己的项目里该按哪套思路设计主键、写迁移脚本和排查膨胀／死锁问题。

下面用 PostgreSQL 16 和 MySQL 8.0 InnoDB 做对照，每一步都给可直接执行的 SQL。

第一步：建同样的表，先看清索引背后存的是什么

这一步的目的是让你亲眼看到「索引里到底放了什么」，这是后面所有差异的根源。分别在两个库里建表：

-- MySQL 8.0
CREATE TABLE article (
  id BIGINT PRIMARY KEY,
  title VARCHAR(200),
  created_at DATETIME
) ENGINE=InnoDB;

-- PostgreSQL 16
CREATE TABLE article (
  id BIGINT PRIMARY KEY,
  title VARCHAR(200),
  created_at TIMESTAMPTZ
);

MySQL 里 PRIMARY KEY 就是聚簇索引，整行数据直接挂在主键 B+ 树的叶子节点上，二级索引 idx_title 的叶子存的是主键值，查其他列要「回表」。执行 EXPLAIN SELECT * FROM article WHERE title='x';，如果 Extra 列显示 Using index，说明用上了覆盖索引，没回表。

PostgreSQL 里主键只是一棵独立的 B-tree，叶子存的是行位置 ctid（页号 + 行内偏移），表本身是无序的堆表。执行 SELECT ctid, xmin, xmax FROM article; 就能看到每行的物理位置和事务号。

注意：MySQL 里用随机 UUID 做主键会造成 B+ 树页分裂和写入抖动，这个坑在 PG 里几乎不存在（堆表无序），但 PG 因此也没有「按主键顺序扫描天然快」这回事，需要 CLUSTER 手动重排一次。

第二步：对照索引类型清单，确认你的查询能不能走索引

PG 默认建 B-tree，还支持 USING hash / gin / gist / brin / spgist，并且支持部分索引：

CREATE INDEX idx_article_recent ON article(created_at) WHERE created_at > '2025-01-01';
CREATE INDEX idx_article_tags ON article USING gin(tags);

MySQL 8.0 InnoDB 只有 B+Tree、全文索引和空间索引，USING HASH 只对 Memory／NDB 引擎有效，也不支持部分索引，只能用「生成列 + 普通索引」模拟。

注意：如果一张时序大表按月分区、查询总带时间范围，PG 的 BRIN 索引体积可能只有 B-tree 的百分之一；迁移到 MySQL 时这类索引没有等价物，得改成联合索引或分区裁剪来顶。

第三步：更新一行，看 MVCC 各自怎么实现

PG 的 UPDATE 是「写新版本」：旧元组留在页里标记为死元组，等 autovacuum 回收。所以频繁更新的表容易膨胀，用这条查死元组：

SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='article';

MySQL InnoDB 是「就地更新 + undo log 保存旧版本」，回滚和一致性读都靠 undo 版本链，用 SHOW ENGINE INNODB STATUS\G 看 History list length，它持续增长就说明有长事务压着 purge。

注意：PG 里一个忘记提交的长事务会挡住 vacuum，几小时就能让表翻几倍；MySQL 里长事务则让 undo 表空间疯涨。两边的解法一样——监控并杀掉超长事务，PG 用 pg_stat_activity 的 state='idle in transaction' 筛，MySQL 用 information_schema.innodb_trx。

第四步：对比默认隔离级别和幻读的处理方式

查当前级别：PG 执行 SHOW transaction_isolation;（默认 read committed）；MySQL 执行 SELECT @@transaction_isolation;（默认 REPEATABLE READ）。

关键差别在「怎么防幻读」：MySQL 的 RR 用 next-key lock（记录锁 + 间隙锁）把范围锁住，代价是并发插入容易撞死锁，用 SELECT * FROM performance_schema.data_locks; 能看到锁住的间隙；PG 的 RR 本质是快照隔离，读不加锁，但两个事务改同一行时后提交的会报 could not serialize access due to concurrent update，需要应用层重试。

注意：MySQL 在 RC 级别下没有间隙锁，死锁会少很多，但 binlog 必须用 ROW 格式才能保证主从一致；PG 没有这个约束，改隔离级别只需 SET transaction_isolation = 'repeatable read';。

第五步：验证 DDL 能不能放进事务里回滚

这一步决定你迁移脚本怎么写。PG 支持事务性 DDL：

BEGIN;
ALTER TABLE article ADD COLUMN tags TEXT[];
ROLLBACK;  -- 列真的没了

同样的语句在 MySQL 会隐式提交，ROLLBACK 回滚不掉（8.0 的原子 DDL 只保证单条 DDL 自身不半途而废）。

注意：给大表加索引，PG 用 CREATE INDEX CONCURRENTLY（不能放在事务块里），MySQL 用 ALTER TABLE ... ADD INDEX ..., ALGORITHM=INPLACE, LOCK=NONE，两者都不能简单粗暴地直接跑在业务高峰期。

小结

• 索引结构：MySQL 主键即聚簇索引，二级索引存主键要回表；PG 索引统一存 ctid，表是无序堆表，主键选择压力小。
• 索引能力：PG 有 GIN／BRIN／部分索引，MySQL 以 B+Tree 为主，复杂场景要用生成列或分区绕。
• MVCC：PG 靠多版本元组 + vacuum，怕长事务导致表膨胀；MySQL 靠 undo 版本链 + purge，怕长事务导致 undo 膨胀。
• 隔离级别：MySQL 默认 RR 用间隙锁防幻读、易死锁；PG 默认 RC，RR 是快照隔离，冲突时报错让应用重试。
• 事务边界：PG 的 DDL 可回滚，MySQL 的 DDL 隐式提交，迁移脚本的写法必须区别对待。