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

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 跨领域综合场景题 中的技术选型章节。