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 行为;跨库迁移后回归验证含空值的排序结果 |