数据库原理与 SQL：从关系模型到事务隔离
0. 元信息
- 主题路径：
docs/topics/db-and-sql/README.md
- 主分类：计算机基础
- 辅助分类：Web 与后端
- 适合对象：会用 Linux 命令行，写过基础程序
- 建议周期：6～10 周，每周 8～12 小时
- 前置知识：Linux 开发环境
- 最终目标：能设计关系模式，写可靠 SQL，读查询计划，调优索引并解释事务异常
1. 学习路线
关系与约束 → SELECT 与表达式 → JOIN/聚合/窗口 → 表设计与范式 → B+Tree 与索引 → EXPLAIN ANALYZE → ACID/锁/MVCC → 隔离级别 → 综合调优
每一步都配可运行 SQL。PostgreSQL 是主环境，MySQL 用来做差异对照。
2. 阶段周数分配
| 阶段 | 6 周方案 | 10 周方案 | 产出 |
|---|
| 1. 关系模型 | 0.5 | 1 | 键、约束与关系代数笔记 |
| 2. SQL 基础 | 0.75 | 1 | 查询练习 |
| 3. JOIN/聚合/窗口 | 0.75 | 1.5 | 分析查询 |
| 4. 表设计 | 0.75 | 1.5 | ERD 与迁移脚本 |
| 5. 索引 | 0.75 | 1.5 | 索引实验 |
| 6. 查询计划 | 0.75 | 1.5 | EXPLAIN 报告 |
| 7. 事务与隔离 | 1 | 1.5 | 双会话实验 |
| 8. 综合项目 | 0.75 | 0.5 | 数据库设计与性能报告 |
3. 九阶段表
| 阶段 | 核心知识 | 实践产出 | 可观察学会标准 |
|---|
| 1 | relation、tuple、attribute、candidate key、constraint | 订单关系定义 | 能区分逻辑模型与物理表 |
| 2 | DDL、DML、NULL、三值逻辑、子查询 | 30 条 SQL | 能预测 NULL 参与比较的结果 |
| 3 | INNER/OUTER JOIN、GROUP BY、CTE、窗口函数 | 销售报表 | 能避免重复计数和错误连接 |
| 4 | FD、1NF/2NF/3NF/BCNF、反范式 | ERD、DDL | 能从函数依赖推导分解 |
| 5 | B+Tree、Hash、GiST、复合/覆盖/部分索引 | 索引基准 | 能解释列顺序和选择性 |
| 6 | planner、统计信息、scan/join/sort | 计划对比 | 能读 estimated/actual rows 与 buffers |
| 7 | ACID、WAL、锁、MVCC | 双会话脚本 | 能复现脏读、不可重复读或写偏差 |
| 8 | 隔离、死锁、重试、短事务 | 并发测试 | 能从等待图解释死锁 |
| 9 | schema、query、index、transaction 综合 | 性能报告 | 能以基线、改动、结果证明优化 |
4. 第一周任务
Day 1 运行约定：PostgreSQL 16+；用 psql -v ON_ERROR_STOP=1 执行脚本。每次记录 SELECT version();、DDL、测试数据和输出。
| 日 | 任务 | 当天交付 |
|---|
| Day 1 | 建库，练 CREATE TABLE、PK、FK、CHECK | schema.sql |
| Day 2 | SELECT、过滤、排序、NULL | queries-basic.sql |
| Day 3 | INNER/LEFT JOIN | joins.sql |
| Day 4 | 聚合与 HAVING | 销售汇总 |
| Day 5 | CTE、子查询、窗口函数 | 排名与移动平均 |
| Day 6 | EXPLAIN (ANALYZE, BUFFERS) | 两份计划 |
| Day 7 | 步骤 A：做订单最小库和 5 条查询；步骤 B：补空表、NULL、重复键、孤儿外键四类测试 | 可重跑脚本与实际输出 |
5. 阶段通用验收
- 脚本能在空库重复执行；
- 能用自己的话解释语义和代价；
- 保存 schema、数据、SQL、计划与耗时；
- 测空表、NULL、重复值、并发失败；
- 至少三组规模不同的数据；
- 同时看正确性与 P95 延迟；
- 改动后重跑相同基准。
6. 最终验收
- 独立完成含 PK、FK、UNIQUE、CHECK 的业务 schema；
- 完成至少 40 条 SQL，含 8 条 JOIN、5 条窗口函数、5 条事务实验；
- 为 5 条查询读
EXPLAIN ANALYZE，提出并验证索引；
- 复现一次死锁和一次隔离异常，写出修复与重试策略；
- 用 15 分钟讲清关系模型、索引、planner、MVCC 的关系。
7. 综合项目
首选：小型电商数据库。交付 ERD、DDL、种子数据、查询集、索引实验、并发实验与性能报告。查询覆盖商品搜索、库存扣减、订单统计和用户排行。库存扣减必须用事务并测试并发。
备选：图书借阅系统 / SaaS 计费系统。
项目必须包含需求、数据约束、迁移脚本、可重跑测试、查询计划、事务边界、异常路径、README 和 notes/retrospective.md。
8. 推荐开源资料
9. 学习资料汇聚（v0.3 自包含）
本节由本计划生成。资料用作导航。结论要回到 SQL 输出、查询计划或官方文档。
9.1 背景与动机
关系模型由 Edgar F. Codd 在 1970 年提出。它用 relation 与谓词逻辑隔离数据的逻辑含义和物理存放。SQL 后来成为主流接口，但它包含 bag semantics、NULL 和三值逻辑，不能简单等同于关系代数。
数据库负责持久化、并发、恢复和高效访问。应用中的重复数据、慢查询、丢失更新与死锁，常落在 schema、query、index、transaction 四个边界。我们学它们是为了能用证据改系统。
9.2 概念地图
flowchart LR
RM[关系模型] --> SQL[SQL]
RM --> FD[函数依赖与范式]
SQL --> Algebra[关系代数]
SQL --> Plan[查询计划]
FD --> Schema[Schema 与约束]
Schema --> Stats[统计信息]
Index[B+Tree / Hash / GiST] --> Plan
Stats --> Plan
Plan --> Exec[执行器]
Tx[事务] --> Lock[锁]
Tx --> MVCC[MVCC]
WAL[WAL / binlog] --> Recovery[恢复与复制]
Lock --> Isolation[隔离级别]
MVCC --> Isolation
Exec --> Tx
关系模型约束数据含义。SQL 被 planner 改写成执行计划。索引和统计信息影响访问路径。事务借助锁、MVCC 与 WAL 保证并发和恢复。
9.3 基础知识讲解
9.3.1 经典论文
| 资料 | 贡献 | 读法 |
|---|
| Codd, A Relational Model of Data for Large Shared Data Banks | 关系模型 | 精读 data independence、normal form |
| Selinger et al., Access Path Selection in a Relational Database Management System | System R 成本优化器 | 跟 EXPLAIN 对照 join order 与 cost |
| Gray et al., Granularity of Locks and Degrees of Consistency | 锁模式与隔离 | 画兼容矩阵与异常 |
| Kung & Robinson, On Optimistic Methods for Concurrency Control | 乐观并发控制 | 对比 2PL 与 OCC |
| Stonebraker et al., The Design of the POSTGRES Storage System | PostgreSQL 架构源流 | 对照现代 PostgreSQL 文档 |
9.3.2 经典书籍
| 书 | 侧重 | 用法 |
|---|
| Silberschatz, Korth & Sudarshan, Database System Concepts | 数据库教材全景 | 主线读关系、索引、优化、事务 |
| C. J. Date, Database Design and Relational Theory | 关系理论与设计 | 精读键、依赖、规范化 |
| C. J. Date, An Introduction to Database Systems | 关系模型与 DBMS | 查概念边界 |
| Joe Celko, SQL for Smarties | 高级 SQL 模式 | 做集合、树、窗口查询 |
| Kleppmann, Designing Data-Intensive Applications | 分布式数据系统 | 学完单机事务后再读 |
9.3.3 优秀博客与文档
9.3.4 核心人物
| 人物 | 影响 | 建议材料 |
|---|
| Edgar F. Codd | 关系模型 | 1970 论文 |
| C. J. Date | 关系理论与数据库教育 | Database Design and Relational Theory |
| Jim Gray | 事务、锁、系统研究 | 事务论文与 Transaction Processing |
| Michael Stonebraker | Ingres、Postgres、数据库系统 | POSTGRES 论文与访谈 |
| Pat Selinger | 成本查询优化 | System R optimizer 论文 |
| Andy Pavlo | 现代数据库系统教学与研究 | CMU 15-445 课程与 Database Group 资料 |
9.3.5 开发与学习方法
| 方法 | 动作 | 何时用 |
|---|
| Query-first | 先写预期结果，再写 SQL | 防止只追求“能跑” |
| Plan-first tuning | 保存基线计划，再改一处 | 慢查询调优 |
| Data-shape testing | 改数据量、选择性、倾斜、NULL | 检查计划稳定性 |
| Two-session experiment | 两个 psql 会话控制交错 | 锁、隔离、死锁 |
| Constraint-first schema | 把业务不变量写成约束 | 表设计与迁移 |
| Differential test | PostgreSQL/MySQL 对跑 | 识别标准 SQL 与实现差异 |
| Course-lab loop | CMU 15-445 / Berkeley CS186 讲义配实验 | 补系统实现视角 |
9.4 经典问题与经典案例
| 问题 | 为什么重要 | 最简答案或证据 |
|---|
NOT IN 遇到 NULL 返回空 | 三值逻辑常制造线上 bug | 用 NOT EXISTS 并明确 NULL 语义 |
| LEFT JOIN 后在 WHERE 过滤右表 | 会意外变成 INNER JOIN | 过滤条件放 ON，或显式保留 NULL |
| JOIN 后聚合翻倍 | 多对多连接扩大行数 | 先按目标粒度聚合，再连接 |
| 有索引仍 Seq Scan | 小表、低选择性、统计误差都可能合理 | 看 actual rows、buffers 与总代价 |
| 复合索引顺序怎么选 | 决定可用前缀与排序能力 | 由查询谓词、范围条件、排序和选择性共同决定 |
EXPLAIN ANALYZE 是否安全 | 它会真实执行语句 | 写操作放事务中并 ROLLBACK |
| 隔离级别高就没异常吗 | Snapshot Isolation 仍可能写偏差 | 明确不变量，必要时 SERIALIZABLE 或显式锁 |
| 死锁怎么处理 | 数据库只能中止一个事务 | 固定加锁顺序，缩短事务并重试 |
| 范式越高越好吗 | 读路径与约束成本有权衡 | 先保证正确，再用测量证明反范式收益 |
| WAL 与 binlog 一样吗 | 日志层级和用途不同 | PostgreSQL WAL 记录物理变化；MySQL binlog 面向复制/恢复，InnoDB 另有 redo/undo |
9.5 学习难点
概念难点
| 难点 | 为什么会卡 | 突破路径 |
|---|
| SQL 与关系代数 | SQL 有重复、NULL、顺序扩展 | 对同一查询写代数树，再观察重复与 NULL |
| 逻辑 schema 与物理访问 | 表定义看不出 heap/index 路径 | 用 EXPLAIN (ANALYZE, BUFFERS) 连起来 |
| MVCC 可见性 | 同一行有多个版本 | 画 transaction id、snapshot、tuple version 时间线 |
思维难点
| 难点 | 为什么会卡 | 突破路径 |
|---|
| 集合式思维 | 容易逐行循环 | 先写结果关系的列、键和谓词 |
| 成本而非规则 | “索引一定快”是错的 | 用数据规模与选择性做成对实验 |
| 并发交错 | 单会话看不到异常 | 写事务 T1/T2 的逐步 schedule |
工程难点
| 难点 | 为什么会卡 | 突破路径 |
|---|
| 生产调优 | 缓存、参数、数据倾斜会干扰 | 保存 SQL、参数、计划、buffers、数据规模 |
| 在线迁移 | DDL 可能锁表或重写表 | 查目标版本文档，在副本和压测数据演练 |
| 跨数据库差异 | SQL 相似，默认隔离和 optimizer 不同 | 记录版本并做 differential test |
9.6 技术标准与接口
9.6.1 Entity
| 名称 | 主流版本 | 发布组织 | 状态 | 许可证 / 可访问性 |
|---|
| PostgreSQL | 16～18 | PostgreSQL Global Development Group | 活跃 | PostgreSQL License；文档公开 |
| MySQL | 8.4 LTS / 9.x Innovation | Oracle | 活跃 | GPLv2 Community / 商业版；文档公开 |
| SQLite | 3.x | SQLite Consortium | 活跃 | Public Domain；文档公开 |
| Oracle Database | 19c / 23ai | Oracle | 活跃 | 商业许可；文档公开 |
| SQL Server | 2022 / 2025 | Microsoft | 活跃 | 商业/Developer；文档公开 |
| SQL 标准 | ISO/IEC 9075 | ISO/IEC JTC 1/SC 32 | 持续修订 | 标准正文通常收费 |
9.6.2 Scope
SQL 标准定义数据定义、查询、更新、约束与事务语义。具体数据库实现存储、索引、优化器、锁、MVCC、恢复和复制。SQL 标准不规定 PostgreSQL VACUUM、MySQL InnoDB redo 或 Oracle optimizer 的全部行为。
9.6.3 Structure
必须掌握：DDL/DML、数据类型、PK/FK/UNIQUE/CHECK、SELECT、JOIN、GROUP BY、窗口函数、事务控制、隔离级别。实现接口包括 PostgreSQL system catalogs、EXPLAIN、ANALYZE、VACUUM、锁视图和统计视图。
9.6.4 Ecosystem
主流关系数据库是 PostgreSQL、MySQL、SQLite、Oracle、SQL Server。驱动遵循 DB-API、JDBC、ODBC 等接口。迁移工具、连接池、ORM 和 CDC 位于 DBMS 外。标准 SQL 与产品方言要明确区分。
9.6.5 Depth Tiers
| 层级 | 能力 | 可观察标准 |
|---|
| L0 | 知道存在 | 知道表、SQL、索引、事务分别做什么 |
| L1 | 看得懂示例 | 能读 DDL、SELECT 和简单计划 |
| L2 | 能正确调用 | 能写 JOIN、约束、索引和事务 |
| L3 | 能解释与排错 | 能定位错误结果、坏计划、锁等待与隔离异常 |
| L4 | 能设计与扩展 | 能实现优化器、存储引擎或并发控制组件 |
本计划要求达到 L3。
9.6.6 Source
10. 常见误区
- 把 SQL 当逐行执行的脚本；
- 认为结果没有
ORDER BY 也有稳定顺序；
- 用
= NULL 判断空值；
- 不声明业务约束，只靠应用校验；
- 认为索引越多越好；
- 只看 EXPLAIN cost，不跑
ANALYZE 和 buffers；
- 在生产直接对写语句跑
EXPLAIN ANALYZE；
- 长事务里等待用户或远程 API；
- 把默认隔离级别当成业务正确性的证明；
- 捕获死锁错误却不做完整事务重试；
- 用小而均匀的测试数据推断生产计划；
- 为了去 JOIN 盲目反范式；
- 混淆 WAL、redo、undo 与 binlog。
11. 所有知识点分类（统一规则）
- 编程语言
- 数据结构与算法
- 计算机基础
- 工程技术
- Web 与后端
- 前端与客户端
- 数据与人工智能
- 项目与职业能力
- 安全与可靠性
本计划归属：计算机基础 主 + Web 与后端 辅。