数据库系统教程：从 ER 建模、关系代数、SQL 到事务、索引与恢复

这篇文章根据数据库系统课程资料整理，目标不是把课件逐页翻译，而是把数据库这门课真正的主线讲清楚：

现实业务 -> ER 建模 -> 关系模型 -> 关系代数 -> SQL 查询 -> 存储、索引、优化 -> 事务、并发、恢复

如果只背概念，数据库很容易学成一堆名词：entity、relationship、primary key、foreign key、selection、projection、join、normalization、B+ tree、ACID、serializability、logging。更好的学习方式是把它们看成同一个问题的不同层次：如何把现实世界中的数据，组织成可以被可靠查询、快速访问、正确更新、崩溃后还能恢复的系统。

原始资料：

• ER Model 课件
• Relational Model 课件
• Relational Algebra 课件
• SQL Basics 课件
• Integrity Constraints 课件
• Normalization 讲义
• File Organization and Indexing 讲义
• Query Processing 讲义
• Query Optimization 讲义
• Concurrency Control 讲义
• Recovery 讲义
• Course Project 资料包

1. 数据库到底在解决什么问题

数据库系统不是一个“更高级的 Excel”。Excel 更像是给人直接看的表格工具，而数据库系统是给应用程序长期使用的数据基础设施。它至少要同时解决四类问题：

1. 数据如何建模：现实世界里有学生、课程、选课、订单、商品、用户行为，系统要把这些对象和关系表达出来。
2. 数据如何查询：用户不是只想看整张表，而是想做筛选、连接、分组、排序、聚合。
3. 数据如何高效存储：数据大了以后，不能每次查询都从头扫到尾，需要文件组织、索引和查询优化。
4. 数据如何可靠更新：多个用户同时修改数据时不能互相覆盖；系统崩溃后，已经提交的修改不能丢，未完成的修改不能半吊子留在库里。

所以数据库系统可以理解成：

Data Model + Query Language + Storage Engine + Transaction System

前两部分偏逻辑，后两部分偏系统。初学时最重要的是先把逻辑层理解扎实，因为 SQL、索引、事务最终都是围绕“关系表里的数据如何被正确读写”展开的。

2. ER 建模：先从现实世界抽象出对象和关系

数据库设计的第一步不是直接建表，而是先问：现实世界里有哪些对象？它们之间有什么关系？这就是 ER Model，Entity-Relationship Model。

Entity 是可以被独立识别的对象。例如学生、课程、教师、部门、订单、商品。每个实体通常有一组属性，比如：

Student(student_id, name, major, year)

这里 student_id 通常会作为标识学生的 key。没有 key，数据库就很难区分两个同名学生。

Relationship 描述实体之间的联系。例如学生选课：

Enroll(Student, Course)

这不是学生实体本身，也不是课程实体本身，而是“学生和课程之间发生了一次选课关系”。如果选课还有成绩、学期、状态，那么这些属性应该放在关系上：

Enroll(student_id, course_id, semester, grade)

ER 建模最容易犯的错误，是把所有东西都塞到一个大表里。例如：

student_id, student_name, course_id, course_name, teacher_name, grade

这个表看似方便，但会产生重复和更新异常。如果一门课有 200 个学生，课程名和教师名就会重复 200 次。假如课程换老师，你要更新很多行，一旦漏更新就会出现数据不一致。

ER 建模的核心思想是：把对象本身和对象之间的关系拆开。例如：

Student(student_id, name, major)
Course(course_id, title, teacher_id)
Teacher(teacher_id, name)
Enrollment(student_id, course_id, semester, grade)

这种拆分不是为了让表变多，而是为了让每个事实只存一遍。学生姓名属于 Student，课程标题属于 Course，成绩属于 Enrollment。一个属性应该放在哪里，取决于它描述的事实属于哪个对象或关系。

3. 从 ER 到关系模型：把图变成表

关系模型的基本单位是 relation，也就是表。一个 relation 有 schema 和 instance：

Student(student_id, name, major)

这是 schema，描述表的结构。真正的一行行学生记录是 instance。

在关系模型里，有几个非常核心的约束。

Primary key 用来唯一标识一行。例如：

CREATE TABLE Student (
  student_id CHAR(8) PRIMARY KEY,
  name VARCHAR(50),
  major VARCHAR(30)
);

它表达的不是“这个字段看起来重要”，而是一个逻辑承诺：

For any two rows r1 and r2 in Student:
if r1.student_id = r2.student_id, then r1 and r2 refer to the same student.

也就是说，如果两行的 student_id 一样，那它们就应该是同一个学生。

Foreign key 用来保证引用关系存在。例如选课表里的 student_id 必须真的出现在 Student 表里：

CREATE TABLE Enrollment (
  student_id CHAR(8),
  course_id CHAR(8),
  semester VARCHAR(20),
  grade VARCHAR(2),
  PRIMARY KEY (student_id, course_id, semester),
  FOREIGN KEY (student_id) REFERENCES Student(student_id),
  FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

这解决的是 referential integrity。如果没有外键，就可能出现一个不存在的学生选了一门课，或者选课记录指向一门不存在的课程。

从 ER 图到关系表，常见规则如下：

1. 强实体通常变成一张表。
2. 实体的简单属性变成表的列。
3. 一对多关系通常把“一”的主键放到“多”的表里作为外键。
4. 多对多关系通常单独变成一张关系表。
5. 弱实体需要依赖拥有者实体的主键来形成自己的主键。

多对多关系最值得注意。学生和课程是多对多，因为一个学生可以选多门课，一门课也可以被多个学生选。所以不能把 course_id 直接放进 Student，也不能把 student_id 直接放进 Course，而要创建 Enrollment。

4. 关系代数：SQL 背后的数学骨架

关系代数是数据库查询语言的理论基础。SQL 看起来像英文句子，但它背后可以理解成一组关系操作。

最基础的操作有五类：

1. Selection：选择行，记作 $\sigma$
2. Projection：选择列，记作 $\pi$
3. Union / Difference：集合并、差
4. Cartesian Product：笛卡尔积，记作 $\times$
5. Join：连接，通常由笛卡尔积加条件筛选得到

例如，从 Student 表里找所有 CS 专业学生：

sigma_{major = 'CS'}(Student)

对应 SQL：

SELECT *
FROM Student
WHERE major = 'CS';

如果只要学生姓名：

pi_name(sigma_{major = 'CS'}(Student))

对应 SQL：

SELECT name
FROM Student
WHERE major = 'CS';

连接是数据库查询里最重要的操作之一。假设要查每个选课学生的名字和课程名：

Student
  join Student.student_id = Enrollment.student_id Enrollment
  join Enrollment.course_id = Course.course_id Course

对应 SQL：

SELECT s.name, c.title, e.grade
FROM Student AS s
JOIN Enrollment AS e
  ON s.student_id = e.student_id
JOIN Course AS c
  ON e.course_id = c.course_id;

理解关系代数的好处是，你会知道 SQL 不是魔法。每个查询大致都可以拆成：

filter rows -> join tables -> project columns -> aggregate or sort

查询优化器做的很多事情，本质上就是重排这些操作，让结果不变但代价更低。

5. SQL：声明式语言，不是逐行执行脚本

SQL 的重要特点是 declarative，也就是声明式。你告诉数据库“我想要什么结果”，而不是详细命令它“先扫哪张表，再怎么循环”。

例如：

SELECT major, COUNT(*) AS n_students
FROM Student
GROUP BY major
HAVING COUNT(*) >= 10
ORDER BY n_students DESC;

这条 SQL 表达的是：

1. 从 Student 表取数据。
2. 按 major 分组。
3. 每组统计人数。
4. 只保留人数不少于 10 的专业。
5. 按人数从多到少排序。

初学 SQL 时，要特别区分 WHERE 和 HAVING：

• WHERE 在分组之前过滤原始行。
• HAVING 在分组之后过滤聚合结果。

所以：

WHERE grade = 'A'

表示只看成绩为 A 的行；而：

HAVING COUNT(*) > 20

表示只保留聚合后数量超过 20 的组。

再看一个带子查询的例子：找选课数超过平均水平的学生。

SELECT student_id, COUNT(*) AS n_courses
FROM Enrollment
GROUP BY student_id
HAVING COUNT(*) > (
  SELECT AVG(course_count)
  FROM (
    SELECT COUNT(*) AS course_count
    FROM Enrollment
    GROUP BY student_id
  ) AS t
);

这类查询的思路是先构造一个中间结果：

每个学生 -> 选课数量

再对这个中间结果求平均，最后筛选超过平均值的学生。写复杂 SQL 时，不要试图一口气写完，应该先在脑子里画出中间表。

6. Integrity Constraints：数据库里的“规则边界”

Integrity Constraints，也就是完整性约束，解决的是“哪些数据状态是合法的”。数据库不是只负责存储，还要负责拒绝明显不合法的数据。

常见约束包括：

1. Domain constraint：字段取值类型和范围必须合法。
2. Key constraint：主键不能重复。
3. Entity integrity：主键不能为 NULL。
4. Referential integrity：外键必须引用存在的行。
5. Check constraint：业务规则约束，例如余额不能小于 0。

例如：

CREATE TABLE Account (
  account_id INT PRIMARY KEY,
  client_id INT NOT NULL,
  balance DECIMAL(12, 2) CHECK (balance >= 0)
);

这里 CHECK (balance >= 0) 的意义是：数据库层面不允许账户余额为负。即使应用程序写错了 SQL，数据库也能挡住非法状态。

这点在事务里也很重要。ACID 里的 Consistency，不是说数据库自动知道所有业务逻辑，而是说：如果事务开始前数据库满足约束，事务结束后也应该满足约束。约束定义得越清楚，数据库越能帮你守住底线。

7. Normalization：为什么要范式化

范式化的目标是减少冗余和异常。最经典的问题来自 functional dependency，函数依赖。

如果在一个关系 $R$ 中，属性集 $X$ 的值可以唯一决定属性集 $Y$ 的值，就记作：

X -> Y

例如：

course_id -> course_title, teacher_id
teacher_id -> teacher_name

如果你把学生选课、课程信息、教师信息全部放在一张表里：

Enrollment(student_id, course_id, course_title, teacher_id, teacher_name, grade)

那么 course_title 会随着每个选课学生重复，teacher_name 也会重复。由此产生三类异常：

1. Update anomaly：教师姓名改了，需要更新很多行。
2. Insert anomaly：还没人选的新课程很难插入，因为缺少 student_id。
3. Delete anomaly：最后一个学生退课时，课程信息也可能被删掉。

范式化的基本操作是 decomposition，也就是拆表：

Course(course_id, course_title, teacher_id)
Teacher(teacher_id, teacher_name)
Enrollment(student_id, course_id, grade)

一个好的 decomposition 应该满足两个要求：

1. Lossless join：拆开后再 join 回来，不会丢信息，也不会产生假行。
2. Dependency preservation：原来的重要函数依赖仍然容易检查。

直觉上，范式化就是让每张表只表达一类事实。课程事实放 Course，教师事实放 Teacher，选课事实放 Enrollment。这样数据更干净，也更容易维护。

8. 文件组织和索引：为什么数据库查询可以很快

逻辑层讲完后，进入系统层：表最终要存到磁盘上。磁盘访问比内存慢很多，所以数据库性能很大程度取决于如何减少磁盘 I/O。

最朴素的查询方式是 full table scan：

SELECT *
FROM Student
WHERE student_id = 'S1234567';

如果没有索引，数据库可能要把 Student 表从头扫到尾。假设表有 100 万行，这显然很慢。

索引的目的就是用额外的数据结构换查询速度。最常见的是 B+ tree index。它像一本书的目录：你不需要逐页翻书，而是先通过目录定位到大概位置。

B+ tree 的几个关键点：

1. 数据项按 key 有序。
2. 内部节点只负责导航。
3. 叶子节点存放指向真实记录的指针。
4. 叶子节点通常串起来，方便范围查询。

所以等值查询：

WHERE student_id = 'S1234567'

可以快速从根节点走到叶子节点；范围查询：

WHERE student_id BETWEEN 'S1000' AND 'S2000'

也可以从第一个叶子节点顺着链表向后扫。

但索引不是越多越好。索引会带来维护成本。每次插入、删除、更新 key 时，索引也要更新。所以经验上：

• 经常出现在 WHERE、JOIN、ORDER BY 中的列适合建索引。
• 高选择性的列更适合建索引，比如 student_id。
• 低选择性的列不一定适合，比如 gender 只有少数几个取值。
• 写入很频繁的表不能盲目堆索引。

数据库性能调优的第一步，往往不是换模型，而是看查询路径：它是在扫全表，还是在用索引？

9. Query Processing and Optimization：SQL 是怎样被执行的

SQL 写出来后，数据库不会按你写的文本顺序机械执行。一般会经历：

SQL -> Parse -> Logical Plan -> Physical Plan -> Execution

逻辑计划描述“做什么”，物理计划描述“怎么做”。例如 join 在逻辑上都是连接，但物理上可能有不同算法：

1. Nested Loop Join：外层每一行去内层找匹配。
2. Sort-Merge Join：先按 join key 排序，再线性合并。
3. Hash Join：对一张表建立 hash table，再用另一张表探测。

不同算法适合不同场景。小表 join 大表时，nested loop 加索引可能很好；大表等值连接时，hash join 往往更合适；如果数据已经有序，sort-merge join 可能有优势。

查询优化的核心是 cost estimation。数据库会估计不同计划的代价，比如：

I/O cost + CPU cost + memory cost

一个经典优化是 selection pushdown。假设查询：

SELECT s.name, e.grade
FROM Student s
JOIN Enrollment e ON s.student_id = e.student_id
WHERE s.major = 'CS';

逻辑上可以先 join 再 filter，也可以先筛出 CS 学生再 join。通常后者更快，因为参与 join 的行更少：

sigma_{major = 'CS'}(Student join Enrollment)

可以优化为：

sigma_{major = 'CS'}(Student) join Enrollment

这就是关系代数为什么重要：它让数据库可以在保持结果等价的前提下改写查询。

10. 事务：把一组操作变成一个可靠单位

事务是数据库系统里最关键的概念之一。一个事务是一组逻辑上应该一起成功或一起失败的操作。

比如转账：

BEGIN;

UPDATE Account
SET balance = balance - 400
WHERE client_id = 7;

UPDATE Account
SET balance = balance + 400
WHERE client_id = 9;

COMMIT;

这两条更新必须作为一个整体。只扣钱不加钱是不合法的，只加钱不扣钱也不合法。如果中间崩溃，数据库必须知道该撤销还是该完成。

事务的目标通常用 ACID 描述：

1. Atomicity：原子性，要么全做，要么全不做。
2. Consistency：一致性，事务前后都满足完整性约束。
3. Isolation：隔离性，并发执行的结果要像某个串行顺序。
4. Durability：持久性，提交后的结果即使崩溃也不能丢。

把 ACID 对应到机制上：

Atomicity + Durability -> Logging and Recovery
Isolation              -> Concurrency Control
Consistency            -> Integrity Constraints + Correct Transaction Logic

这能帮你理解：事务不是一个单独功能，而是约束、并发控制、日志恢复共同组成的系统。

11. 并发控制：为什么两个正确操作一起跑会出错

考虑两个事务同时更新同一个账户：

T1: balance = balance + 100
T2: balance = balance + 500

初始余额是 1000，正确结果应该是 1600。但如果两个事务同时读到 1000：

T1 read 1000
T2 read 1000
T1 write 1100
T2 write 1500

最终余额可能是 1500，T1 的更新丢了。这叫 lost update。

隔离性的目标是 serializability：并发执行虽然操作交错，但结果必须等价于某个串行顺序。例如等价于先 T1 后 T2，或者先 T2 后 T1。

常见并发异常包括：

1. Lost update：两个事务写同一数据，一个覆盖另一个。
2. Dirty read：读到了另一个未提交事务的数据。
3. Unrepeatable read：同一事务内两次读取同一行，结果不同。
4. Phantom read：同一事务内两次范围查询，第二次多出或少了行。

最经典的控制方法是 locking。读写锁可以粗略理解为：

• Shared lock：读锁，多个事务可以同时读。
• Exclusive lock：写锁，一个事务写时别人不能读写冲突数据。

Two-Phase Locking，2PL，要求事务的加锁和解锁分为两个阶段：

1. Growing phase：只加锁，不释放锁。
2. Shrinking phase：只释放锁，不再加新锁。

2PL 可以保证 conflict serializability，但也可能带来 deadlock。比如 T1 拿着 A 等 B，T2 拿着 B 等 A。数据库需要检测死锁，或者用超时/等待图等机制处理。

12. 恢复：崩溃后数据库如何回到正确状态

数据库恢复解决的是 crash 后怎么办。系统崩溃时，内存里的修改可能还没写回磁盘；也可能某些未提交事务已经把部分数据写到了磁盘。这会破坏 Atomicity 和 Durability。

因此数据库通常使用 log。日志记录数据库发生过什么，典型记录包括：

<T1 start>
<T1, Account[7], old=1000, new=600>
<T1, Account[9], old=300, new=700>
<T1 commit>

Write-Ahead Logging，WAL，核心原则是：

在数据页写回磁盘之前，相关日志必须先写到稳定存储。

为什么？因为如果数据页已经写了，但日志没写，崩溃后系统就不知道这个修改来自哪个事务，也不知道该 undo 还是 redo。

恢复时常见两类操作：

1. Undo：撤销未提交事务的影响。
2. Redo：重做已经提交但可能没完全写入磁盘的影响。

直觉上：

• 没 commit 的事务不能留下痕迹，所以要 undo。
• 已 commit 的事务必须永久生效，所以要 redo。

这就是日志恢复和 ACID 的关系：

Atomicity  -> Undo uncommitted transactions
Durability -> Redo committed transactions

13. 把数据库知识串成一条学习路线

学数据库时，可以按下面的顺序复习：

1. 先会建模：能从业务描述画出实体、关系、属性、key。
2. 再会落表：能把 ER 图变成关系 schema，并正确设计主键外键。
3. 再会查询：能用关系代数理解 SQL，特别是 selection、projection、join、group by。
4. 再会约束和范式：知道为什么拆表，知道冗余会带来什么异常。
5. 再会性能：理解索引、join 算法、查询计划和优化。
6. 最后会可靠性：理解事务、并发控制、日志和恢复。

如果把数据库和数据科学联系起来，它的位置其实非常基础。Pandas、SparkSQL、数据仓库、特征工程、推荐系统、日志分析，背后都离不开表结构、查询、join、聚合和数据一致性。很多机器学习项目的上游问题，本质上都是数据库问题：

• 数据从哪里来？
• 字段语义是否稳定？
• join key 是否可靠？
• 标签是否泄漏？
• 训练集和线上数据是否来自同一套口径？
• 数据更新时是否会出现不一致？

所以数据库不是只属于后端开发的课程。对算法、数据挖掘、RAG/Agent 应用来说，数据库系统提供的是一套非常重要的底层思维：如何组织数据，如何查询数据，如何让数据在规模、并发和故障下仍然可信。

14. 一个最小实战练习

最后给自己留一个小练习：设计一个课程选课系统。

实体：

Student(student_id, name, major)
Course(course_id, title, credits)
Instructor(instructor_id, name, department)
Enrollment(student_id, course_id, semester, grade)

需要完成：

1. 为每张表设计 primary key。
2. 为 Enrollment 设计 foreign key。
3. 写 SQL 查询每个学生的选课数量。
4. 写 SQL 查询每门课的平均成绩。
5. 思考哪些列适合建索引。
6. 思考两个学生同时抢同一门课最后一个名额时，事务应该如何设计。

这个练习虽小，但已经覆盖数据库系统的核心链路：

Schema Design -> Constraints -> Query -> Index -> Transaction

真正学懂数据库，不是背出 ACID 或 B+ tree 的定义，而是看到一个数据问题时，能自然地问：这个事实应该存在什么表里？这个查询会怎么执行？这个更新是否安全？系统崩了以后还能不能恢复？