[!info] related notes

• 上层入口：Backend Engineering MOC
• 学习路线：关系型数据库学习路线、BodySense 数据库工程深化课程 MOC
• 关系总览：数据库事务、并发与恢复总览
• 工程衔接：Caching MOC、Distributed Systems MOC、Go 数据访问 MOC
• 产品：MySQL、PostgreSQL、SQLite

数据库 MOC

知识总览

filters:
  and:
    - 'file.ext == "md"'
    - 'file.inFolder("z")'
    - 'this.file.links.contains(file)'
    - 'file.path != this.file.path'
formulas:
  knowledge: 'file.asLink(title)'
properties:
  formula.knowledge:
    displayName: 知识
  note.description:
    displayName: Description
  note.mastery:
    displayName: Mastery
views:
  - type: table
    name: 知识总览
    order:
      - formula.knowledge
      - note.description
      - note.mastery
    columnSize:
      formula.knowledge: 280
      note.description: 760
      note.mastery: 56

[!abstract] 学习目标 从关系模型和 SQL 开始，继续理解数据怎样按 Page 存储、索引怎样减少 I/O、优化器怎样选择计划、事务怎样控制并发、日志怎样支持恢复。最终能从数据建模、正确性与执行机制三个层次解释“为什么这样设计、为什么快、为什么锁、为什么能恢复”。

一张图看主干

Relational Model
→ Schema / Constraint / Cardinality / Normalization
→ SQL / JOIN / GROUP BY / Aggregate
→ ORM / Loading Strategy / N+1
→ Schema Migration
→ Page / Buffer Pool / Index
→ Query Optimizer / Execution Plan
→ Transaction / Isolation / MVCC / Lock
→ WAL / Undo / Redo / Recovery

推荐阅读顺序

1. 关系数据库与基础结构

• [[z/database|数据库]]、DBMS、关系型数据库
• Schema、Table、Row、Column、Data Type
• SQL、查询

先建立一个核心直觉：Schema 不只是“字段清单”，还描述数据库允许出现哪些持久化状态。

2. 关系建模与约束

[[primary-key|主键]]
→ [[foreign-key|外键]]
→ [[constraint|约束]]
→ [[one-to-many-relationship|一对多关系]]
→ [[many-to-many-relationship|多对多关系与连接表]]
→ [[composite-unique-constraint|联合唯一约束]]

综合入口：

• 主键、外键与约束
• ER 模型
• 数据库规范化

建模时重点回答：

这一行是谁？
它引用谁？
关系基数是多少？
什么组合必须唯一？
什么状态必须由数据库拒绝？

3. SQL 查询、JOIN 与聚合

原子入口：

• SQL JOIN
• SQL GROUP BY
• SQL 聚合函数
• SQL JOIN 中 ON 与 WHERE 的过滤边界
• SQL HAVING 和 WHERE 的区别

综合入口：

• SQL JOIN、GROUP BY 与聚合查询
• SQL 子查询
• SQL 分页

最短查询心智模型：

FROM / JOIN
→ ON
→ WHERE
→ GROUP BY
→ Aggregate
→ HAVING
→ SELECT / ORDER BY

这是逻辑语义模型；真实物理执行由优化器决定。

4. ORM 与数据访问

• 对象关系映射（ORM）
• ORM 的 Eager Loading 与 Lazy Loading
• N+1 Query Problem
• 数据库连接池
• Go 数据访问 MOC
• Prisma 完整指南

ORM 的学习重点不是背 API，而是始终能回答：

这段 ORM 代码会生成什么 SQL？
会发多少次查询？
关系数据何时加载？
数据库约束是否真的存在？

5. Schema 演进

• 数据库 Schema Migration
• 数据库 Schema Migration 与发布兼容性
• 数据库迁移设计（golang-migrate）

核心边界：

Model / ORM Schema
→ 当前状态

Migration
→ 状态变化历史

已有数据后的 Schema 变化还必须考虑 backfill、约束收紧、回滚的数据边界与新旧代码兼容窗口。

6. 存储与索引

[[database-pages-and-buffer-pool|Page 与 Buffer Pool]]
→ [[b-plus-tree-database-index|B+Tree 数据库索引]]
→ [[clustered-and-secondary-indexes|聚簇索引与二级索引]]
→ [[composite-and-covering-database-indexes|联合索引与覆盖索引]]
→ [[database-index-selectivity-and-cost|选择性、基数与代价]]

这一层应能解释回表、最左前缀、范围扫描、覆盖索引和“有索引却不走”的原因。

7. 查询执行与优化

• SQL 查询执行流程
• EXPLAIN / EXPLAIN ANALYZE

SQL → Parse / Bind → Optimize → Plan → Execute
→ estimated rows vs actual rows → I/O / latency

重点从“SQL 看起来对”推进到“数据库实际怎么执行”。

8. 事务、并发与恢复

先读 事务、并发与恢复总览，再拆开：

• 事务、ACID
• 隔离级别
• MVCC、数据库锁
• WAL、Undo Log、Redo Log、Binlog

最短主线：

Transaction defines boundary
→ Isolation defines visibility
→ MVCC serves snapshot reads
→ Locks coordinate conflicts
→ Logs support rollback / durability / recovery

9. 产品差异与实践

• MySQL、PostgreSQL 与 SQLite 的差异
• 数据类型差异
• 事务与并发差异
• 数据库测试策略

认证数据本身也是数据库建模的一种应用，可继续看：

• Server-side Session 鉴权

数据库与相邻系统的边界

Database      → business facts / constraints / transaction
Redis         → derived cache / transient state / optional session store
Elasticsearch → derived search index
Kafka         → event stream / integration log
MinIO         → binary object bytes

当数据进入缓存、搜索、消息或对象存储后，跨系统一致性必须显式设计。

面试最短主线

1. PK / FK / UNIQUE / CHECK 分别守什么不变量；
2. 1:N 和 N:M 怎么落表，连接表属性放哪里；
3. INNER JOIN、LEFT JOIN、ON vs WHERE；
4. GROUP BY、COUNT(*) vs COUNT(column)、WHERE vs HAVING；
5. ORM 的 Eager/Lazy 与 N+1；
6. Migration 为什么不能等价于改 Model；
7. 为什么索引常用 B+Tree、如何看 EXPLAIN；
8. ACID、隔离级别、MVCC 与锁；
9. Undo / Redo / WAL / Binlog 的职责。

可验证练习

• 建 users + assessments，用 FK/NOT NULL/CHECK 阻止非法状态。
• 建 Student N:M Course 的 Enrollment，并用联合唯一约束防止重复关系。
• 写一条 LEFT JOIN + GROUP BY，正确统计没有子记录的父实体为 0。
• 构造 LEFT JOIN 条件分别放 ON / WHERE，对比结果差异。
• 制造一个 N+1，再用 preload / batch query 消除。
• 为已有表安全新增最终 NOT NULL 字段并完成 backfill。
• 构造 10 万级数据，对比全表扫描与索引扫描。
• 用 EXPLAIN ANALYZE 比较估算行数和实际行数。
• 用两个连接复现不可重复读、锁等待或死锁。

相邻地图

• Backend Engineering MOC
• Caching MOC
• Distributed Systems MOC