罗罗编程学习记录

03.10 MySQL 与 PostgreSQL 差异对照表

所属:全栈开发面试题库 / 学习路径 / 03 MySQL 与 PostgreSQL

能力维度 MySQL 怎么做 PostgreSQL 怎么做 迁移或选型注意点
基础数据类型 提供 JSON 列类型,无原生数组类型,无范围类型 提供 JSON 与 JSONB 两类类型,原生数组(int[]、text[])与范围类型(int4range、tsrange 等) 迁移含数组与区间约束的模型时,MySQL 通常需要拆成关联表或字符串编码,先核对类型映射
JSON 能力 JSON 列配合 JSON 函数与虚拟列上索引使用 JSONB 支持 GIN 索引与更完整的操作符,更新时重写为二进制形式 文档型半结构化查询与嵌套字段检索场景优先评估 JSONB;跨库迁移需重写 JSON 操作符与索引
窗口函数与 CTE 8.0 版本起支持窗口函数与 WITH 子句 较早支持窗口函数与 CTE,新版本下 CTE 可被优化器内联 书写时避免依赖某一库对内联与否的假设,复杂 CTE 迁移后核对执行计划
分区表 支持 RANGE、LIST、HASH、KEY 分区,分区键与外键限制较多 声明式 RANGE、LIST、HASH 分区,支持多级分区与 ATTACH、DETACH 分区表 分区裁剪效果依赖分区键出现在查询条件中;迁移分区方案要重新验证约束与外键行为
并行查询 并行能力覆盖部分扫描与连接场景,版本间差异较大 查询并行覆盖顺序扫描、连接与聚合,配置项较细 多核大查询场景压测对比两库并行收益;不要把单库的并行配置结论直接搬到另一库
全文检索 内置 FULLTEXT 索引,中文场景配合 ngram 分词器 基于 tsvector 与 tsquery,可配置词典,模糊匹配可借助 pg_trgm 中文分词与排序相关性要求不同,检索方案迁移后需要重建索引并核对召回结果
复制方式 binlog 主从复制、GTID、半同步、组复制 MGR 物理流复制基于 WAL,逻辑复制基于 publication 与 subscription 跨版本、跨平台或部分表订阅场景,PostgreSQL 逻辑复制粒度更细;MySQL 复制链路以 binlog 为核心
默认隔离级别与 MVCC InnoDB 默认 REPEATABLE READ,版本链存于 undo log,可见性由 Read View 判断 默认 READ COMMITTED,元组带 xmin、xmax 版本号,可见性由事务快照判断,旧版本依赖 VACUUM 回收 迁移后先确认目标库默认级别;长事务在两库都会阻碍版本回收,PG 额外表现为表膨胀
锁模型 表锁、行锁、意向锁、MDL,间隙锁与 Next-Key Lock 防止范围幻读 表级锁、行级锁与咨询锁 advisory lock,MVCC 下读不阻塞写 涉及范围加锁与互斥任务的代码需要分别验证锁行为;咨询锁是 PG 特有的应用层手段
扩展生态 扩展主要通过存储引擎、插件与第三方工具接入 扩展生态成熟,代表有 pgvector、TimescaleDB、PostGIS、pg_trgm 向量检索、时序、地理空间等需求在 PG 侧通常有现成扩展,MySQL 侧往往需要额外组件
大小写与标识符规则 表名大小写敏感性受 lower_case_table_names 与底层文件系统影响,字符串比较取决于排序规则 未加引号的标识符折叠为小写,加引号保留原写法;字符串比较默认区分大小写 迁移时统一标识符命名风格,避免引号与排序规则差异导致对象找不到或重复值漏检
NULL 与排序语义 NULL 在排序结果中的位置依库与配置表现,NULL 之间不相等 升序时 NULL 默认排在最后,可用 NULLS FIRST、NULLS LAST 显式指定 排序分页与唯一约束场景必须核对 NULL 行为;跨库迁移后回归验证含空值的排序结果