← 课程 设计数据库
0 / 5 单元
English
第 4 课

如何设计一个始终正确的数据库

数据库是应用存放信息的地方。如果设计得不好，数据就会自相矛盾、丢失，或者很难快速找到。在这一课里，你会故意弄坏一个设计糟糕的数据库，然后把它修好。

关系型数据库设计首先关乎完整性，其次才是性能：通过规范化消除异常，显式地建模基数关系，按访问路径建索引，并用事务在故障和并发下维持不变式。

第 1 单元

从一张大表到规范的多张表

一家小店把所有订单都记在一张大表里。看起来没问题，但同样的信息被一遍遍地复制（橙色单元格）。用下面的按钮看看会出什么问题，然后把它拆成多张表。

一张存在重复事实的非规范化单表。触发三种经典异常（更新、删除、插入），然后规范化到 3NF，再重复同样的操作。

关于客户依赖于客户 关于商品依赖于商品 关于订单本身依赖于订单主键
表结构（PostgreSQL）
CREATE TABLE customers (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   text NOT NULL,
  email  text NOT NULL UNIQUE
);

CREATE TABLE products (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   text NOT NULL,
  price  numeric(10,2) NOT NULL CHECK (price >= 0)
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  product_id  bigint NOT NULL REFERENCES products(id),
  unit_price  numeric(10,2) NOT NULL  -- 下单时的价格，有意为之
);
一句话概括：每个事实只存在一个地方。客户放进 customers 表，商品放进 products 表。订单只是通过编号指向它们（customer_id、product_id）。这些指针叫作外键，而每一行自己独有的编号叫作主键。
范式的实际运用
  • 1NF：值是原子的，没有重复组（不能出现 products = "Mouse, Keyboard"）。
  • 2NF / 3NF：每个非主键列都依赖于主键、依赖于完整的主键、并且只依赖于主键。email 依赖的是客户而不是订单，所以要移出去。
  • 有意的反规范化：orders.unit_price 故意复制了价格。它记录的是一个历史事实（实际收取的金额），而不是当前价格。
  • 只有在有明确“真相来源”的前提下，才为读性能做反规范化（物化视图、缓存，或由触发器维护的列）。
第 2 单元

关系：一个还是多个？

数据库里的东西是相互关联的。关键问题永远是：“多少个这个对应多少个那个？”选出答案，图会显示这些表是如何连接的。

选择基数，图会显示外键放在哪里，以及由哪些约束来保证。进阶模式会加入自引用关系。

DDL
（作答后显示表结构）
第 3 单元

索引：快速找到东西

一张有一百万用户的表。没有索引时，数据库必须查看每一行。索引就像电话簿上的字母标签：几乎能直接跳到正确的位置。

顺序扫描对堆是 O(n) 的。B-tree 索引查找只需要 O(log n) 次页读取，而且扇出可达数百，所以即使是非常大的表也只有 3–4 层。索引的代价是写放大和存储空间。

没有索引

从上到下读取每一行。Seq Scan on users · Filter: email = … 每个方块代表 10,000 行。
已读行数：0

email 上有索引

顺着字母标签一路找到正确的那一页。Index Scan using users_email_idx · B-tree，3 层。
已读页数：0
点击“开始查找”，两种查找会同时开始。

这次查找能用上索引吗？

复合索引：哪些条件能用上它？

email 上有一个索引。逐个判断下面的查找。

CREATE INDEX ON people (last_name, first_name); 可以把它想成先按姓排序，同一个姓里面再按名排序。

权衡：每个索引都会让查找更快，但每次插入或更新时也必须同时更新索引，而且索引会占用空间。只为应用中真正经常执行的查找添加索引。
经验法则：为外键以及高频的 WHERE / ORDER BY 路径建索引。在复合索引中，等值条件的列放在范围条件的列之前。在合适的地方使用部分索引（WHERE deleted_at IS NULL）和覆盖索引（INCLUDE）。用 EXPLAIN (ANALYZE, BUFFERS) 检查执行计划，并删掉没有被任何查询用到的索引。
第 4 单元

事务：要么全做，要么全不做

Alice 给 Bob 转 $100。这是两次修改：先从 Alice 那里扣 $100，再给 Bob 加 $100。如果服务器在这两步之间崩溃了怎么办？

一笔转账包含两次写入，它们必须维持一个不变式：总余额保持不变。在两次写入之间注入一次崩溃，分别对比有事务和没有事务的情况。

Alice
$500
Bob
$200
先关闭事务试一次崩溃，再打开事务试一次。
A要么全做，要么全不做：每一步都发生，或者都不发生。
C规则始终成立，比如“钱不会凭空产生或消失”。
I两个人同时操作也不会互相干扰。
D一旦提示“完成”，即使断电也不会丢。
A原子性：undo 日志 / MVCC；失败时 ROLLBACK。
C一致性：提交时约束、外键、CHECK 均成立。
I隔离性：选择的隔离级别在异常与并发之间取舍。
D持久性：COMMIT 返回之前 WAL 已经 fsync。

并发：同时发生两笔取款

两个会话在同一时刻各从 Alice 的 $500 中取出 $100。正确结果是 $300。

第 5 单元

自我检测