PostgreSQL与MySQL深度对比：存储引擎、查询优化与选型决策

存储引擎架构差异与适用场景

PostgreSQL和MySQL的底层存储引擎设计哲学截然不同，这直接影响了两者在不同场景下的性能表现。MySQL的InnoDB引擎采用聚簇索引（Clustered Index）结构，主键索引的叶子节点直接存储完整行数据，二级索引存储主键值，查询非主键列需要回表。PostgreSQL所有索引都是二级索引，表数据存储在堆表（Heap Table）中，索引指向数据的物理位置（CTID），不存在聚簇索引的回表问题。

这种架构差异导致的行为区别：InnoDB主键查询只需一次IO，二级索引查询需要两次IO（索引查主键+回表查数据）。PostgreSQL所有索引查询都是两次IO（索引查CTID+堆表取数据），但VACUUM清理后的堆表物理顺序与索引顺序可能更接近，实际IO次数取决于数据分布。写入方面，InnoDB的聚簇索引要求主键有序写入，随机主键会导致页分裂和碎片；PostgreSQL堆表追加写入，索引更新不影响数据页位置，写入吞吐在随机主键场景更稳定。

查询优化器与执行计划对比

PostgreSQL的查询优化器基于Cascades框架演进，支持更丰富的计划选择。MySQL 8.0的优化器相比5.7版本有大幅增强，加入了hash join、anti/semi join等现代优化，但在复杂查询的执行计划选择上仍不如PostgreSQL灵活。关键差异体现在以下几个方面。

多表连接方面，PostgreSQL支持geqo（遗传算法优化器）处理10+表连接的枚举空间问题，也能用动态规划精确求解。MySQL 8.0对超过61个表的连接使用贪心搜索，执行计划可能不是全局最优。子查询方面，PostgreSQL对子查询有更成熟的优化策略，支持将相关子查询转换为join，支持materialization和hashed subplan。CTE（WITH子句）方面，PostgreSQL支持MATERIALIZED/NOT MATERIALIZED提示控制CTE物化行为，MySQL 8.0的CTE总是物化，大结果集场景内存消耗大。

-- PostgreSQL: CTE物化控制
WITH user_stats AS MATERIALIZED (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
)
SELECT u.name, us.order_count
FROM users u
JOIN user_stats us ON u.id = us.user_id
WHERE us.order_count > 100;

-- MySQL 8.0: CTE总是物化，大结果集可能OOM
WITH user_stats AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
)
SELECT u.name, us.order_count
FROM users u
JOIN user_stats us ON u.id = us.user_id
WHERE us.order_count > 100;

窗口函数方面，两者都支持ROW_NUMBER、RANK、LEAD/LAG等标准窗口函数。PostgreSQL额外支持自定义聚合窗口函数和PARTITION BY中的表达式，MySQL的限制更多。EXPLAIN输出方面，PostgreSQL的EXPLAIN ANALYZE提供实际执行统计（真实行数、实际耗时），MySQL的EXPLAIN ANALYZE从8.0.18开始支持，格式可读性稍差。

JSON与半结构化数据处理能力

JSON/JSONB支持是PostgreSQL的强项，也是与MySQL拉开差距最明显的领域之一。PostgreSQL的JSONB类型采用二进制存储，支持GIN索引加速JSON字段内部查询，支持丰富的JSON操作符和函数。MySQL的JSON类型同样使用二进制存储，但索引支持需要通过生成列（Generated Column）间接实现。

-- PostgreSQL: 直接对JSONB字段建GIN索引
CREATE INDEX idx_events_data ON events USING GIN (event_data jsonb_path_ops);

-- JSONB内部字段查询走索引
SELECT * FROM events
WHERE event_data @> '{"type": "click"}';

SELECT * FROM events
WHERE event_data->>'user_id' = '12345'
  AND (event_data->>'amount')::numeric > 100;

-- MySQL: 需要生成列+普通索引
ALTER TABLE events
ADD COLUMN event_type VARCHAR(32)
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.type'))) STORED,
ADD INDEX idx_event_type (event_type);

PostgreSQL的JSONPATH支持（jsonb_path_query系列函数）允许在JSON内部执行类似XPath的查询，对嵌套JSON结构的数据分析极为方便。MySQL的JSON_TABLE函数可将JSON数组展开为关系表，两者各有侧重，但PostgreSQL在JSON数据的灵活查询能力上更胜一筹。

并发控制与MVCC实现差异

两者的MVCC（多版本并发控制）实现机制有本质区别。PostgreSQL在堆表中为每行数据附加xmin/xmax事务ID，通过快照隔离判断行可见性，UPDATE操作创建新版本行并标记旧版本为已死。死元组通过VACUUM回收，自动VACUUM的触发阈值由autovacuum_vacuum_scale_factor等参数控制。长事务会导致死元组无法回收，表膨胀风险需要监控。

MySQL InnoDB在聚簇索引记录中存储DB_TRX_ID事务ID和DB_ROLL_PTR回滚指针，旧版本数据存在Undo Log中，通过回滚指针构建行版本链。Purge线程异步清理Undo Log，长事务导致Undo Log膨胀而非表数据膨胀。这意味着PostgreSQL的表膨胀问题需要手动VACUUM或pg_repack修复，而MySQL的Undo膨胀在事务结束后能自动清理。

-- PostgreSQL: 监控表膨胀
SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) AS total_size,
       n_dead_tup, n_live_tup,
       ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- MySQL: 监控Undo Log大小
SHOW ENGINE INNODB STATUS\G
-- 查看History list length指标

迁移策略与选型决策框架

数据库选型不是简单的优劣对比，而是匹配业务特征的决策过程。以下框架可作为参考。业务特征偏向事务密集型、主键查询为主、读写比高、团队MySQL经验丰富，选MySQL。业务特征偏向复杂分析查询、JSON/半结构化数据多、地理空间数据处理、需要更强的SQL标准兼容性，选PostgreSQL。两者在高可用方案上都已成熟：MySQL有MHA、Orchestrator、MySQL InnoDB Cluster；PostgreSQL有Patroni、Stolon、PgPool-II。

从MySQL迁移到PostgreSQL，数据层面可用pgloader或AWS DMS自动迁移表结构和数据，但存储过程、触发器、自定义函数需要手动改写。应用层面的ORM兼容性较好，Hibernate/MyBatis等主流ORM对两种数据库都有方言支持，SQL方言差异是迁移成本的主要来源。建议在迁移前用pg_dump导出目标PostgreSQL的完整表结构，与源MySQL表逐一对比字段类型映射、索引策略、默认值差异，建立迁移检查清单。

PostgreSQL与MySQL的竞争推动了双方快速进步，MySQL 8.0补齐了窗口函数、CTE、JSON函数等短板，PostgreSQL 16/17在并行查询和逻辑复制的性能上持续优化。选型的关键不是哪个更强大，而是哪个与业务模型、团队技能、运维体系的匹配度更高。数据库是可以替换的基础设施，但在替换之前，先把当前选型用好。