1 / 4概念
Relational Databases
关系型数据库把数据放进带约束的表,用 SQL 声明“要什么”,由查询优化器决定“怎么取”。判断标准不是“PostgreSQL 还是 MySQL”,而是数据完整性、事务边界、查询模式和运维能力。
1 概念
1.1 一句话定义
关系型数据库用关系(表)表示实体,用主键/外键表达身份与引用,用 SQL 做声明式查询,并用事务保证一组读写要么全部生效、要么全部不生效。
1.2 它解决什么问题
- 结构化数据的持久化与并发访问,不依赖应用进程内存。
- 用约束(主键、外键、唯一、CHECK)把业务规则落到存储层。
- 用事务隔离并发写,避免丢失更新、脏读和部分失败。
- 用索引和优化器支撑点查、范围查、连接和聚合,而不是全表扫描。
1.3 不属于本页的内容
| 主题 | 去哪 |
|---|---|
| 索引结构、执行计划与慢查询 | Indexing & Optimization |
| ACID、隔离级别与 MVCC | Transactions & Concurrency |
| 文档/键值/图数据库 | NoSQL Databases |
| 跨库分片与分布式事务 | Distributed Systems / 微服务页 |
| 批处理与湖仓 | Batch Processing / Lakehouse |
1.4 关键术语
| 术语 | 含义 | 容易混淆的点 |
|---|---|---|
| 关系 | 带 schema 的表,行是元组 | 不是 ER 图里的“关系” |
| 主键 | 唯一标识一行的列或列组合 | 不是“必须自增” |
| 外键 | 引用另一张表的主键 | 不是自动索引 |
| MVCC | 多版本并发控制,读不阻塞写 | 不是“没有锁” |
| WAL | 预写日志,先写日志再写数据页 | 不是 binlog 的同义词 |
| 优化器 | 根据统计信息选择执行计划 | 不是每次都能选对 |
2 机制
2.1 心智模型
一张表是“行 + 约束 + 索引”的组合;一次查询是“逻辑关系 → 物理访问路径”的翻译。SQL 只描述结果,优化器在扫描、索引、连接顺序之间做成本选择。
2.2 机制拆解
- 解析与重写:SQL → 语法树 → 逻辑计划,展开视图、检查权限。
- 优化:基于统计信息估算行数,比较顺序扫描、索引扫描和不同连接顺序的成本。
- 执行:从存储页读取元组,按执行器算子(Scan/Join/Agg/Sort)流水线处理。
- 提交:写 WAL → fsync(按配置)→ 更新可见性;崩溃后按 WAL 恢复。
2.3 完整枚举
| 对象 | 作用 | 关键属性 |
|---|---|---|
| 表 | 存储行 | 列类型、约束、分区 |
| 视图 | 保存查询 | 逻辑视图,不存数据 |
| 物化视图 | 保存结果 | 需要刷新,可建索引 |
| 索引 | 加速访问 | B-tree/Hash/GIN/BRIN 等 |
| 序列 | 生成单调值 | 自增主键、缓存、回绕 |
| 触发器 | 在写入前后执行逻辑 | 隐式副作用,难调试 |
| 函数/存储过程 | 在库内执行逻辑 | 语言、权限、可移植性 |
| schema | 命名空间 | 权限边界、迁移单位 |
PostgreSQL 与 MySQL 的关键差异:
| 维度 | PostgreSQL | MySQL(InnoDB) |
|---|---|---|
| MVCC | 行版本 + vacuum | undo log + 回滚段 |
| 默认隔离 | Read Committed | Repeatable Read |
| 复制 | 物理/逻辑复制、流复制 | binlog 主从、组复制 |
| JSON | JSONB + GIN 索引 | JSON + 生成列索引 |
| 扩展 | 扩展生态(PostGIS、pgvector) | 插件与存储引擎 |
| DDL 事务 | 支持事务性 DDL | 多数 DDL 隐式提交 |
| 大小写/标识符 | 默认折叠小写 | 依赖系统变量 |
2.4 关键数字与量级
| 指标 | 量级 | 说明 |
|---|---|---|
| 数据页 | 8 KB(PostgreSQL) | 页是 I/O 与缓存单位 |
| 索引高度 | 3–4 层 | 扇出大,点查通常 3–4 次 I/O |
| 连接开销 | 每连接 MB 级内存 | 连接池比“多开连接”更重要 |
| 复制延迟 | ms 到 s | 受网络、写入量和同步模式影响 |
| 事务长度 | 越短越好 | 长事务阻碍 vacuum/清理 |
3 实践
3.1 最小示例
-- 建表、约束、索引;订单属于用户,金额不能为负。
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
amount NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC);
-- 查最近 20 笔订单;EXPLAIN 看是否走索引。
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;
3.2 常用操作与参数
| 操作 | 命令 / SQL | 说明 |
|---|---|---|
| 连接 | psql "$DATABASE_URL" / mysql -h host -u user -p | 生产用连接池 |
| 查看表结构 | \d+ orders / SHOW CREATE TABLE orders | 确认索引与约束 |
| 执行计划 | EXPLAIN (ANALYZE, BUFFERS) | 关注实际行数、扫描方式、缓冲区 |
| 当前活动 | pg_stat_activity / SHOW PROCESSLIST | 查长事务、锁等待 |
| 备份 | pg_dump / mysqldump | 逻辑备份;物理备份另算 |
| 迁移 | 迁移工具 + 版本化 SQL | 先加列、回填、再切读 |
3.3 标准流程
- 先定实体、身份(主键)和引用(外键),再定索引。
- 用事务包住“必须一起成功”的写操作;事务尽量短。
- 用
EXPLAIN验证关键查询,而不是凭感觉建索引。 - 迁移采用 expand/contract:先兼容新旧结构,再切流量,最后删旧列。
- 上线前压测连接数、锁等待和复制延迟。
3.4 与相邻技术的组合
- 索引:关系模型提供语义,索引提供访问路径;没有索引的约束查询会退化为全表扫描。
- 事务:关系库用事务保证多行一致性;跨服务一致性要另找方案。
- 缓存:Redis 放在数据库前,必须定义失效与回源策略。
- 搜索:全文/向量检索通常外置,关系库保留事实数据。
3.5 选型维度
| 维度 | 取值 | 影响 |
|---|---|---|
| 负载 | OLTP / OLAP / 混合 | OLTP 重事务,OLAP 重扫描 |
| 一致性 | 强 / 最终 | 强一致通常选关系库 |
| 查询模式 | 点查 / 范围 / 连接 / 聚合 | 决定索引与是否分库 |
| 数据规模 | GB / TB / PB | 单机上限与分片策略 |
| 运维能力 | 自建 / 托管 | 备份、升级、故障切换成本 |
| 生态 | 扩展/工具/团队经验 | PostgreSQL 扩展多,MySQL 运维熟 |
3.6 决策规则
- 需要多表事务、约束和即席查询:关系库优先。
- 写入吞吐远超单机、且能接受最终一致:考虑分片或 NoSQL。
- 以分析扫描为主:列式仓库/湖仓,不要硬扛在 OLTP 库上。
- 需要 GIS/向量/全文等扩展:优先评估 PostgreSQL 扩展。
- 团队已有成熟 MySQL 运维体系:不要为了“新”迁移。
3.7 替代方案对比
| 方案 | 优势 | 代价 | 适用 |
|---|---|---|---|
| PostgreSQL | 扩展强、SQL 完整、事务性 DDL | 调优门槛较高 | 通用 OLTP、混合负载 |
| MySQL | 生态成熟、运维资料多 | 部分 DDL 隐式提交、扩展少 | Web 业务、读多写少 |
| 分布式 SQL | 水平扩展、强一致 | 复杂度与延迟上升 | 超大规模 OLTP |
| NoSQL | 灵活模型、水平扩展 | 事务/连接能力弱 | 特定访问模式 |
| 列式仓库 | 分析快、压缩好 | 不适合高频点写 | BI/报表 |
3.8 何时不该用
- 数据模型完全无结构、且查询模式频繁变化:文档库可能更合适。
- 单表写入成为瓶颈且无法分片:先评估归档、分区、批量写,再考虑换型。
- 以大规模分析为主:把 OLTP 库当仓库用会拖垮线上。
- 只需要缓存/会话:Redis 更合适,不要建表。
4 边界
4.1 典型故障
| 症状 | 根因 | 处理 |
|---|---|---|
| 查询突然变慢 | 统计信息过期、计划退化 | ANALYZE、检查执行计划、必要时固定计划 |
| 写入越来越慢 | 索引过多、WAL/刷盘瓶颈 | 精简索引、批量提交、检查 I/O |
| 锁等待/死锁 | 事务顺序不一致、长事务 | 统一加锁顺序、缩短事务、重试 |
| 表膨胀 | 大量更新/删除 + vacuum 跟不上 | 调 vacuum、分区、重建表 |
| 复制延迟 | 大事务、单线程回放、网络 | 拆事务、并行复制、监控 lag |
| 连接数耗尽 | 连接池配置不当/泄漏 | 连接池、超时、限制最大连接 |
| 主键回绕 | 序列/自增耗尽 | 用 BIGINT、监控使用率 |
4.2 反模式
| 做法 | 后果 | 替代 |
|---|---|---|
SELECT * | 多读列、破坏覆盖索引 | 只选需要的列 |
| N+1 查询 | 连接/往返放大 | JOIN 或批量查询 |
| 长事务里做网络调用 | 锁持有时间不可控 | 事务外调用,事务内只做 DB |
| 没有主键的表 | 复制/归档/去重困难 | 显式主键 |
| 每个列都建索引 | 写放大、优化器选择困难 | 按查询建复合索引 |
4.3 可观测信号
慢查询日志、pg_stat_statements、EXPLAIN (ANALYZE, BUFFERS)、锁等待视图、复制延迟、连接数、磁盘 I/O 与 WAL 生成速率。
4.4 安全 / 性能 / 一致性边界
- 持久性:
fsync/synchronous_commit决定崩溃时丢多少;不要为了吞吐默认关闭。 - 一致性:读写分离会引入复制延迟;强一致读要走主库。
- 权限:应用账号最小权限,迁移账号与运行账号分离。
- 容量:连接数、磁盘和 WAL 都有硬上限;先监控再扩容。