罗罗编程学习记录
本页内容 展开查看

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;PG BIGSERIAL(或 GENERATED ALWAYS AS IDENTITY);PG 用 RETURNING id 拿回生成 ID,MySQL 用 LAST_INSERT_ID()。
  • 类型映射:
    • TINYINT(1)(MySQL 布尔)→ PG boolean。
    • DATETIME → PG timestamp(无时区)或 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、应用配置都要改。本模块给出可执行流程。

核心机制:

  1. 评估:盘点表、数据量、SQL、存储过程、触发器。
  2. schema 转换:类型映射、自增改 IDENTITY、JSON 改 JSONB。
  3. 全量迁移:pg_dump/MyBatis 工具,或 DataX。
  4. 数据校验:行数、抽样 checksum、业务对账。
  5. 应用改造:方言 SQL、连接串、驱动、RETURNING。
  6. 双写/灰度:先读 PG,再写切流(阶段 7 的双写迁移思路)。
  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"的坑

自测题:

  1. MySQL 的 DATETIME 迁到 PG 用什么类型?为什么?
    • 要点:timestamptz(带时区),避免无时区导致的偏移。
  2. 两库默认隔离级别分别是什么?迁移后并发行为会怎么变?
    • 要点:MySQL RR、PG RC;PG 下每条 SELECT 新 Read View,不可重复读会发生。
  3. 电商高并发 CRUD 与 AI 向量检索分别选什么?
    • 要点:CRUD 选 MySQL(生态成熟);向量检索选 PG + pgvector 或专用向量库。
  4. 跨库扣余额加积分,为什么不能用本地事务?
    • 要点:本地事务只在单连接有效;用本地消息表 + MQ 最终一致 + 对账。
  5. 迁移后表名找不到,最可能什么原因?
    • 要点:PG 未加引号折叠为小写,原大写表名加了引号;统一小写。
  6. 架构演进中,什么时候该从读写分离走向分库分表?
    • 要点:单表行数/单库 TPS/写瓶颈达到阈值,且读写分离与缓存已做过。
  7. 为什么说"不要把一个库的行为默认套到另一个库"?举一例。
    • 要点:如 MySQL 的 TINYINT(1) 是布尔、PG 是 SMALLINT;MySQL 默认大小写不敏感、PG 敏感,都会导致迁移事故。