03 MySQL 与 PostgreSQL
本路径覆盖关系型数据库从 SQL 入门到高可用、分片、性能诊断的完整链条,主线同时对照 MySQL(InnoDB)与 PostgreSQL 的实现差异。数据库是后端所有路径的公共底座:无论 Java、Go、PHP 还是 AI 应用方向,接口服务最终都要落到持久化层,先把这一层的读写、事务、索引和故障排查走通,上层框架学习才有落地的锚点。
本篇目录
| 编号 | 章节 |
|---|---|
| 03.1 | 阶段 0:环境准备与关系模型基础 |
| 03.2 | 阶段 1:SQL 进阶与数据建模 |
| 03.3 | 阶段 2:索引与执行计划 |
| 03.4 | 阶段 3:事务、MVCC 与锁 |
| 03.5 | 阶段 4:日志、持久化与备份恢复 |
| 03.6 | 阶段 5:复制、高可用与读写分离 |
| 03.7 | 阶段 6:分库分表与容量评估 |
| 03.8 | 阶段 7:性能诊断、参数调优与运维 |
| 03.9 | 阶段 8:两库差异、迁移与架构综合(可选) |
| 03.10 | MySQL 与 PostgreSQL 差异对照表 |
| 03.11 | 知识点全清单 |
| 03.12 | 术语表 |
| 03.13 | 官方文档与权威资料索引 |
| 03.14 | 实战项目阶梯 |
| 03.15 | 与本项目题库的对照 |
| 03.16 | 常见误区与避坑 |
| 03.17 | 自测清单 |
1. 适用人群与先修要求
1.1 三类起点对号入座
| 人群 | 起点 | 先补什么 | 全职学习周期(每天 4–6 小时) | 在职学习周期(每天 2 小时) |
|---|---|---|---|---|
| 零基础转岗 | 不会写建表语句与多表查询,只接触过界面化数据库工具 | Linux 命令行基础、一门后端语言的变量与函数、HTTP 接口基本概念 | 3–4 个月 | 6–8 个月 |
| 有其他语言基础 | 会编程,但对关系模型、SQL 和事务没有系统概念 | 关系模型与范式、SQL 语法、数据类型选择,可跳过编程语言语法阶段 | 2–2.5 个月 | 4–5 个月 |
| 已工作补短板 | 会写业务 CRUD 和普通索引,但说不清执行计划、隔离级别与高可用流程 | 事务与 MVCC、锁与死锁排查、复制与高可用、慢查询诊断、参数调优 | 1–1.5 个月定向补强 | 2–3 个月 |
1.2 先修要求说明
- 不需要提前掌握任何一门数据库的内部源码,但必须能在本机或容器里启动 MySQL 与 PostgreSQL,并用命令行客户端连上执行 SQL。
- 阶段 0 之前只要求会使用终端、会看进程与网络连接;操作系统与网络的深入知识可在阶段 4、5 对照补充,不必先学完再开工。
- 本路径是后端所有路径的公共底座:缓存、消息队列、分布式事务、容器化与可观测性章节都会反复引用数据库行为;先完成阶段 1 与阶段 3,再进入对应后端框架路径会更顺畅。
- 两个库建议以一个为主线精读,另一个做差异对照。国内招聘场景 MySQL 出镜率更高,PostgreSQL 在复杂查询、JSON、扩展生态与 AI 向量检索场景占优;时间有限时先 MySQL 主线,阶段 8 再补 PostgreSQL 差异。
2. 技术全景与搭配关系
真实项目里数据库从不单独出现。下表列出生产环境中围绕 MySQL / PostgreSQL 的常见组件组合、适用场景、搭配原因与替代方案,作为后续分阶段学习的全景地图。
| 组合 | 构成 | 适用场景 | 为什么这么搭 | 替代方案 |
|---|---|---|---|---|
| 应用连接池 | 应用服务 + HikariCP(JVM)或 PgBouncer(PostgreSQL)+ 单库 | 高并发短连接业务 | 复用 TCP 连接与认证上下文,控制数据库连接数,避免每次请求重新握手拖垮数据库 | 应用内直连无池化;代理层统一池化 |
| ORM 与迁移工具 | MyBatis / JPA / Hibernate、GORM、Eloquent、SQLAlchemy + Flyway / Liquibase / Alembic | 业务系统常规迭代 | ORM 减少样板代码,迁移工具把 schema 变更纳入版本控制与 CI 流程 | 手写 JDBC 或原生客户端;纯 SQL 脚本手工执行 |
| 缓存层 | Redis 等缓存 + MySQL / PostgreSQL | 读多写少、热点数据访问 | 把高频读请求挡在缓存层,降低主库读压力,缩短接口响应 | 进程内本地缓存;NoSQL 直接承载热点数据 |
| 消息队列 | Kafka / RocketMQ / RabbitMQ + 数据库 | 异步解耦、削峰填谷、最终一致 | 写库前先落消息,消费端异步落库,隔离突发流量与下游故障 | 同步直写;定时任务批量补偿 |
| 读写分离中间件 | 主从拓扑 + 代理式(Pgpool-II、ShardingSphere-Proxy)或客户端式路由 | 读流量远大于写流量 | 按规则把读请求分发到从库,应用侧透明或半透明分流,提升读吞吐 | 客户端多数据源手工路由;单库加缓存兜底 |
| 监控体系 | Prometheus + 数据库 exporter + Grafana + 告警规则 | 生产环境常驻 | 采集连接数、慢查询、复制延迟、锁等待、缓存命中率等指标并设置告警 | 脚本定时巡检;商业云监控服务 |
| 备份系统 | 逻辑备份与物理备份 + 异地归档 + 定期恢复演练 | 生产数据保护 | 覆盖误操作、介质损坏、机房故障三类场景,PITR 保证可恢复到任意时间点 | 云厂商自动快照;单机定时 dump |
| 云厂商托管实例 | RDS 或托管 PostgreSQL + 应用 | 中小团队、快速上线 | 主备、备份、升级、监控由云平台兜底,团队聚焦业务逻辑 | 自建在虚拟机或容器集群上 |
搭配判断的核心只有三条:团队现有技术资产能否复用、业务瓶颈在 CPU 还是 IO、出了故障谁来兜底。数据库选型与组件取舍的面试题可对照 11 跨领域综合场景题 中的技术选型章节。