03.9 阶段 8:两库差异、迁移与架构综合(可选)
所属:全栈开发面试题库 / 学习路径 / 03 MySQL 与 PostgreSQL
本页目录
一、阶段定位
前面七个阶段把 MySQL 与 PostgreSQL 分别讲了一遍,本阶段是收尾与可选进阶:建立横向对照视角(不要把一个库的行为默认套到另一个库)、能回答选型问题、能把组件拼成完整架构。这是面试"你为什么选 MySQL/PG""如果让你设计电商库你怎么演进"这类综合题的准备。
判断自己是否卡在本阶段:
- 写 SQL 时不区分两库方言,把 PG 的
RETURNING或SERIAL写到 MySQL 里——这是方言差异不敏感。 - 选型时只会说"MySQL 常用""PG 功能强",列不出具体场景理由——这是选型没有依据。
- 架构图只会画一个圆写"数据库",说不出从单库到分片的演进路径与触发条件——这是架构综合能力不足。
- 听说过跨库事务,但答不上"本地事务管不到两个库"——这是事务边界理解不到位。
衔接关系:本阶段不引入新机制,而是把阶段 0–7 的组件(连接池、缓存、主从、分片、备份、监控)拼成一张完整架构图。时间有限可以跳过,但面试综合题前建议过一遍。详细的逐维度对照见 03.10 差异对照表,本页只讲"怎么用这张表做决策"。
二、阶段目标与投入
| 能力目标(可验证的行为) | 验证方式 |
|---|---|
| 能说出两库在类型、JSON、大小写、NULL 排序上的行为差异 | 对一段 SQL 指出跨库会出问题的点 |
| 能完成一次从 MySQL 到 PG 的类型映射 | 给一张表写出映射表 |
| 能针对业务场景给出选型建议(MySQL 或 PG)并给 3 条理由 2 条风险 | 对电商、时序、GIS、向量四个场景分别选型 |
| 能画出从单库到分库分表的演进路线与触发条件 | 标注每阶段瓶颈与下一步动作 |
| 能说清跨库事务为什么不能依赖本地事务 | 解释最终一致方案(消息/本地消息表) |
| 能指出把 MySQL 行为套到 PG 的常见坑 | 列 5 个跨库迁移事故 |
建议学时:
- 全职:3–5 天,约 12 小时。两库差异复盘 3 小时、迁移方言 3 小时、选型与架构 4 小时、跨库事务 2 小时。
- 在职:1.5 周,约 14 小时。本阶段是综合,不要求搭新环境。
学时分配:方言与类型差异 30%、迁移流程 20%、选型决策 25%、架构演进 15%、跨库事务 10%。
三、先修自检
| 先修项 | 合格标准 | 不合格时回到 |
|---|---|---|
| 两库基础 | 都能建库建表、写 CRUD | 03.1 阶段 0 |
| 索引与执行计划 | 能读 EXPLAIN | 03.3 阶段 2 |
| 事务与 MVCC | 知道 RR vs RC 默认级别差异 | 03.4 阶段 3 |
| 复制与高可用 | 知道 binlog vs WAL | 03.6 阶段 5 |
| 分库分表 | 知道分片与分布式 ID | 03.7 阶段 6 |
经验判断:本阶段最忌讳"背差异表"。要能说出"为什么这个差异会导致线上事故"——例如大小写折叠不同导致迁移后表找不到、默认隔离级别不同导致并发行为变化。
四、知识地图
| 模块 | 知识点 | 掌握程度 | 对应题库章节 |
|---|---|---|---|
| 方言与类型 | 数据类型映射、自动增量、RETURNING、标识符 | 能讲清原理 | 05.4 SQL Server |
| 行为差异 | 大小写、NULL 排序、默认隔离级别 | 能讲清原理 | 03.10 差异对照表 |
| 迁移 | 类型映射、工具、回归验证 | 会用 | 05.2 MySQL |
| 选型 | 事务、JSON、扩展、生态、团队 | 能讲清原理 | 11.2 故障排查与稳定性 |
| 向量/扩展 | pgvector、PostGIS、TimescaleDB | 了解 | 05.6 向量数据库与语义检索 |
| 跨库事务 | 本地事务边界、最终一致 | 能讲清原理 | 09.6 分布式事务 |
| 架构演进 | 单库→读写分离→分片→中间件 | 能讲清原理 | 09.1 高并发基础 |
进度管理建议:本阶段可选,但面试前用 03.10 差异表做一次自测。术语见 03.12 术语表,题库对照见 03.15 与本项目题库的对照。
五、分模块精讲
5.1 方言与类型映射
为什么需要它:迁移时最大的坑不是语法,而是"同样写法在两个库行为不同"。本模块列高频差异。
核心机制:
- 自动增量:MySQL
AUTO_INCREMENT;PGBIGSERIAL(或GENERATED ALWAYS AS IDENTITY);PG 用RETURNING id拿回生成 ID,MySQL 用LAST_INSERT_ID()。 - 类型映射:
TINYINT(1)(MySQL 布尔)→ PGboolean。DATETIME→ PGtimestamp(无时区)或timestamptz(建议)。DECIMAL与NUMERIC等价。- MySQL 没有原生数组、范围;PG 有
int[]、tsrange。
- 标识符:MySQL 表名大小写受
lower_case_table_names与文件系统影响;PG 未加引号折叠为小写,加引号区分大小写。 - 字符串比较:MySQL 默认 collation 不区分大小写;PG 默认区分大小写。
最小可运行示例:
-- MySQL
CREATE TABLE t (id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64));
INSERT INTO t (name) VALUES ('a');
SELECT LAST_INSERT_ID();
-- PG 等价
CREATE TABLE t (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(64));
INSERT INTO t (name) VALUES ('a') RETURNING id;
迁移映射示例:
TINYINT(1) → boolean
DATETIME → timestamptz
DECIMAL(12,2) → NUMERIC(12,2)
JSON → JSONB(推荐)
AUTO_INCREMENT → GENERATED ALWAYS AS IDENTITY
LIMIT n OFFSET m → 相同(PG 支持)
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 迁移后表名找不到 | PG 折叠小写、原表名大写加了引号 | 统一小写命名 |
| 唯一约束挡不住 Admin/admin | MySQL ci vs PG 敏感 | 业务层统一小写;或用 citext |
LAST_INSERT_ID() 报错 |
PG 不支持 | 改 RETURNING |
| 时区偏移 8 小时 | DATETIME 无时区 → timestamptz | 统一 timestamptz + UTC |
面试考法:"MySQL 与 PG 类型映射注意什么"。要点:布尔、时间、自增、标识符大小写。
5.2 行为差异:隔离级别、NULL 排序、JSON
为什么需要它:同样的 SQL 在两个库跑出不同结果,往往是这些默认行为差异。迁移回归测试必须覆盖。
核心机制:
- 默认隔离级别:MySQL InnoDB 默认 RR;PG 默认 RC。迁移后并发行为会变(PG 每条 SELECT 新 Read View,见阶段 3)。
- NULL 排序:MySQL NULL 升序排最前;PG 升序 NULL 默认排最后,可用
NULLS FIRST/LAST显式指定。 - JSON:MySQL 有 JSON 类型;PG 有 JSON(原样)与 JSONB(二进制,可 GIN 索引),半结构化查询优先 JSONB。
- 锁:MySQL 有间隙锁(RR 下),PG 没有间隙锁(用 MVCC + 显式锁)。
最小可运行示例:
-- PG:显式控制 NULL 位置
SELECT * FROM orders ORDER BY amount DESC NULLS LAST;
-- PG JSONB + GIN
CREATE TABLE doc (data JSONB);
CREATE INDEX idx ON doc USING GIN (data);
SELECT * FROM doc WHERE data @> '{"city":"北京"}';
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 迁移后分页结果顺序变了 | NULL 排序位置不同 | 加 NULLS LAST |
| 并发行为变化 | 默认 RR→RC | 回归测试;显式设隔离级别 |
| JSON 查询慢 | 用了 JSON 而非 JSONB | 改 JSONB + GIN |
面试考法:"两库默认隔离级别差异""NULL 排序差异"。要点:MySQL RR vs PG RC;MySQL NULL 在前 vs PG NULL 在后。
5.3 迁移流程与回归
为什么需要它:从 MySQL 迁到 PG(或反向)不是导个数据就行,schema、数据、SQL、应用配置都要改。本模块给出可执行流程。
核心机制:
- 评估:盘点表、数据量、SQL、存储过程、触发器。
- schema 转换:类型映射、自增改 IDENTITY、JSON 改 JSONB。
- 全量迁移:pg_dump/MyBatis 工具,或 DataX。
- 数据校验:行数、抽样 checksum、业务对账。
- 应用改造:方言 SQL、连接串、驱动、RETURNING。
- 双写/灰度:先读 PG,再写切流(阶段 7 的双写迁移思路)。
- 回归:重点测并发、排序、分页、大小写。
最小可运行示例:
迁移检查清单:
[ ] 表结构类型映射核对
[ ] 自增列改 IDENTITY / SERIAL
[ ] 存储过程/触发器重写(PL/pgSQL vs 存储过程)
[ ] 应用 SQL 方言(NOW()、LIMIT、字符串函数)
[ ] 唯一约束大小写行为
[ ] 时区与时间字段
[ ] 索引类型(GIN/BRIN 替代部分 MySQL 索引)
[ ] 全量数据 checksum
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 迁移后存储过程报错 | 语法不兼容 | 重写为 PL/pgSQL |
| 数据行数对不上 | 批量迁移漏数据 | 校验 + 补偿 |
| 上线后偶发排序错误 | NULL/大小写差异 | 加 NULLS LAST;统一小写 |
面试考法:"MySQL 迁 PG 要注意什么"。要点:类型、自增、方言、大小写、隔离级别、回归。
5.4 选型决策:什么时候用 MySQL,什么时候用 PG
为什么需要它:面试官问"你会怎么选",不能答"看团队熟哪个"。要给出基于场景的依据。
核心机制:
- MySQL 优势:生态成熟、国内招人容易、InnoDB 运维经验丰富、云厂商托管普及、简单 CRUD 性能稳。适合传统业务、电商、内容、已有 MySQL 团队。
- PG 优势:
- 复杂查询、CTE、窗口函数、优化器强。
- JSONB、数组、范围类型。
- 扩展生态:pgvector(向量/AI 检索)、PostGIS(地理)、TimescaleDB(时序)。
- 事务标准兼容好、MVCC 成熟。
- 选型建议:
- 纯业务 CRUD、团队熟悉 MySQL:选 MySQL。
- 复杂分析、半结构化、GIS、时序、AI 向量:选 PG。
- 国内互联网电商主库:MySQL 为主线更稳妥。
最小可运行示例:
场景选型:
电商订单/用户 CRUD → MySQL(生态、运维成熟)
复杂报表/多表分析 → PG(优化器、CTE/窗口)
地理位置(附近的店) → PG + PostGIS
时序指标(监控数据) → PG + TimescaleDB
AI 语义检索/向量 → PG + pgvector(与业务库同实例)
文档/半结构化灵活模型 → PG JSONB(比 MySQL JSON 强)
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 为了"功能强"选 PG 但团队不熟 | 运维成本 | 评估团队能力;先用托管 |
| 用 PG 做高并发简单 CRUD | 优势不匹配 | 评估吞吐;可能 MySQL 更省事 |
| 向量检索硬用 MySQL | 生态弱 | 用 pgvector 或专用向量库(阶段 12) |
面试考法:"MySQL vs PG 怎么选"。要点:CRUD/生态选 MySQL;复杂查询/JSON/GIS/向量/时序选 PG;结合团队与场景。
5.5 跨库事务边界与最终一致
为什么需要它:分库分表后,本地事务(BEGIN/COMMIT)只能管一个库。跨库的"扣 A 库余额、加 B 库积分"不能靠本地事务保证原子性。这是分布式事务的引子。
核心机制:
- 本地事务:只在单个数据库连接内有效。
- 跨库场景:订单库扣库存、账户库扣款、积分库加积分——三者跨库。
- 方案:
- 2PC/XA:强一致但性能差、耦合,少用。
- 最终一致:本地消息表 + 消息队列,业务先落本地消息,异步消费到下游,失败重试 + 对账。
- TCC/Saga:业务层补偿。
- 原则:能不跨库就不跨库(按业务域垂直拆分,一个事务落在一个库);必须跨库用最终一致。
最小可运行示例:
本地消息表模式:
1. 订单库本地事务:写订单 + 写消息表(待发送)
2. 定时任务扫消息表,发 MQ
3. 积分库消费消息,加积分,ACK
4. 失败重试;对账任务兜底
→ 不保证实时,但最终一致
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 跨库用本地事务 | 误以为跨连接有效 | 改最终一致 |
| 消息发了但下游没消费 | 无重试/对账 | 重试 + 对账 |
| 2PC 把性能拖垮 | 强一致代价 | 业务能接受最终一致就用 MQ |
面试考法:"跨库事务怎么办""为什么不用分布式事务"。要点:本地事务只管单库;用本地消息表 + MQ 最终一致;2PC 性能差。
5.6 架构演进综合
为什么需要它:把前面阶段的组件串成"一个业务从 0 到高并发"的演进路线,是面试"设计一个架构"题的骨架。
核心机制:
- 阶段 1 单库:业务初期,一个 MySQL/PG 实例 + 应用 + 缓存。
- 阶段 2 优化:加索引、读写分离(主从)、Redis 缓存挡读。
- 阶段 3 高可用:MHA/Patroni 自动切换、连接池、监控告警。
- 阶段 4 垂直拆分:按业务域拆库(用户、订单、商品),微服务。
- 阶段 5 水平分片:单表过大按 user_id 分片,引入 ShardingSphere/Vitess。
- 阶段 6 专用存储:时序用 TimescaleDB、GIS 用 PostGIS、向量用 pgvector/专用库、搜索用 ES。
- 每一步都有触发条件,不是一次到位。
最小可运行示例:
电商架构演进:
0–1 万 DAU: 单库 + Redis
1–10 万 DAU: 主从读写分离 + 缓存 + 慢查询优化
10–100 万 DAU: 高可用自动切换 + 垂直拆库(订单/用户/商品)
100 万+ DAU: 订单表水平分片 + 专用组件(ES 搜索、时序监控)
触发条件: 不是按 DAU,而是按慢查询、TPS、单表行数、运维成本
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 过早微服务/分片 | 业务还小 | 单库撑到瓶颈再拆 |
| 拆了之后跨域查询难 | 垂直拆分后跨库 JOIN | 应用层组装;冗余;CQRS |
| 组件堆砌 | 为技术而技术 | 每一步解决明确瓶颈 |
面试考法:"设计一个电商数据库架构""怎么演进"。要点:单库→读写分离→高可用→垂直拆→水平分片→专用存储;按瓶颈触发。
六、分层练习
6.1 复现型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 方言差异自测 | 对 10 条 SQL 标出两库差异点 | 大小写/NULL/自增/时区 | 1 小时 |
| 类型映射 | 给一张 MySQL 表写 PG DDL | 类型全部映射 | 1 小时 |
| 差异表自测 | 用 03.10 表自测 12 个维度 | 能说出每维度差异 | 1 小时 |
6.2 改造型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 修迁移 SQL | 给定含 NOW()/LIMIT/LAST_INSERT_ID() 的 SQL 改为 PG | 能在 PG 跑通 | 1.5 小时 |
| 修 NULL 排序 | 把依赖默认排序的查询加 NULLS LAST | 结果一致 | 0.5 小时 |
| 改最终一致 | 把一段跨库强一致代码改为本地消息表 | 有重试与对账 | 2 小时 |
6.3 设计型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 选型报告 | 对一个业务(含 JSON、向量、高并发 CRUD)给选型 | 3 理由 2 风险 | 2 小时 |
| 架构图 | 画出电商从单库到分片的演进 | 标注触发条件 | 2 小时 |
| 迁移方案 | 写一份 MySQL→PG 迁移清单 | 可执行 | 2 小时 |
七、排障表
| 现象 | 可能原因 | 排查步骤 | 解决办法 |
|---|---|---|---|
| 迁移后表找不到 | 大小写折叠/引号 | \dt 看实际表名 |
统一小写命名 |
| 唯一约束行为不同 | ci vs 敏感 | 试 Admin/admin | 业务统一小写或 citext |
| 时区数据偏 8 小时 | DATETIME vs timestamptz | 查字段类型 | 统一 timestamptz |
| 并发结果变了 | 默认 RR→RC | 压测并发 | 显式设隔离级别 |
| 跨库数据不一致 | 本地事务管不到 | 查消息表/对账 | 最终一致 + 对账 |
| JSON 查询慢 | 用 JSON 非 JSONB | \d doc 看类型 |
改 JSONB + GIN |
| 自增 ID 不返回 | LAST_INSERT_ID 不兼容 | 查应用代码 | 改 RETURNING |
| 排序结果不稳定 | NULL 位置不同 | 对比两库结果 | 加 NULLS LAST |
八、出口条件
完成本页后,整条 03 MySQL 与 PostgreSQL 路径即告一段落。
- [ ] 能列出两库在类型、大小写、NULL 排序、默认隔离级别的行为差异
- [ ] 能对一张 MySQL 表写出 PG 等价 DDL(含自增、时间、JSON)
- [ ] 能对电商、GIS、时序、向量四个场景给出选型建议
- [ ] 能画出从单库到分片的演进路线与每步触发条件
- [ ] 能解释跨库事务为什么不能用本地事务,给出最终一致方案
- [ ] 能说出至少 5 个"把 MySQL 行为套到 PG"的坑
自测题:
- MySQL 的
DATETIME迁到 PG 用什么类型?为什么?- 要点:
timestamptz(带时区),避免无时区导致的偏移。
- 要点:
- 两库默认隔离级别分别是什么?迁移后并发行为会怎么变?
- 要点:MySQL RR、PG RC;PG 下每条 SELECT 新 Read View,不可重复读会发生。
- 电商高并发 CRUD 与 AI 向量检索分别选什么?
- 要点:CRUD 选 MySQL(生态成熟);向量检索选 PG + pgvector 或专用向量库。
- 跨库扣余额加积分,为什么不能用本地事务?
- 要点:本地事务只在单连接有效;用本地消息表 + MQ 最终一致 + 对账。
- 迁移后表名找不到,最可能什么原因?
- 要点:PG 未加引号折叠为小写,原大写表名加了引号;统一小写。
- 架构演进中,什么时候该从读写分离走向分库分表?
- 要点:单表行数/单库 TPS/写瓶颈达到阈值,且读写分离与缓存已做过。
- 为什么说"不要把一个库的行为默认套到另一个库"?举一例。
- 要点:如 MySQL 的
TINYINT(1)是布尔、PG 是 SMALLINT;MySQL 默认大小写不敏感、PG 敏感,都会导致迁移事故。
- 要点:如 MySQL 的