项目 A：数据库设计

S5-002必学进阶约 90 分钟JDK 21项目实战 · 项目 A：测试管理平台

1. 本节定位

《项目 A：需求分析与范围定义》确定了需求与三个实体；本节把它们变成三张表，并回答三个设计问题：

text

① 「当前可借几本」是实时算，还是冗余一个字段？   → 冗余，但要保证同步 ✓
② 业务规则靠应用层保证，还是靠数据库约束？        → 两个都要，数据库是最后防线 ✓✓
③ 索引建哪几个？为什么？                        → 按"最高频的查询"来定 ✓

★ 本节的 SQL 全部可运行（labs/project-a/src/main/resources/db/schema.sql）✓

2. 学习目标

能把实体转成表，并说明每个字段的用途 ★★★；
能说清"冗余字段"的取舍（性能 vs 一致性） ★★★；
知道业务规则要用数据库约束兜底 ★★★；
能根据查询反推索引 ★★★；
知道种子数据要幂等、且要重置自增序列 ★★★；
知道同一份 DDL 在不同库上的可移植性问题 ★★。

3. 前置知识检查

知道索引与执行计划吗？（《执行计划与慢查询定位》）✓
知道事务与隔离级别吗？（《Linux 常用命令与部署》）✓

4. 为什么需要这个知识

先看一个真实的设计分歧 ✗：

text

问题：怎么知道「这本书现在还能借几本」？

方案 A：每次查的时候数一下
    SELECT COUNT(*) FROM borrow_record WHERE book_id = ? AND status = 'BORROWED'
    → 准确，但**每本书都要一次 COUNT** ✗（列表页 20 本书 = 20 次）

方案 B：在 book 上冗余一个 available_copies 字段
    → 列表页零成本 ✓
    → 但**借/还时必须同时更新它**，否则数据对不上 ✗✗

★ 本项目选方案 B，并用"同一事务 + 原子 UPDATE"保证同步 ✓✓

5. 核心概念

5.1 三张表与字段设计 ★★★

sql

CREATE TABLE book (
    id               BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    isbn             VARCHAR(20)  NOT NULL,
    title            VARCHAR(200) NOT NULL,
    author           VARCHAR(100) NOT NULL,
    category         VARCHAR(50),
    total_copies     INT          NOT NULL DEFAULT 0,
    available_copies INT          NOT NULL DEFAULT 0,      -- ★ 冗余字段
    created_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_book_copies CHECK (available_copies >= 0 AND available_copies <= total_copies)
);

★ 关键字段说明 ✓：

字段	为什么要有
`total_copies` / `available_copies` ★★★	总量与可借量分开 —— 否则"总共几本"会丢 ✓
`isbn`	图书的天然唯一标识（建唯一索引 ✓）
`created_at`	几乎每张表都该有 —— 排查问题时"什么时候建的"永远是第一个问题 ✓✓

★ 另外两张表 ✓：

sql

CREATE TABLE reader (
    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    card_no    VARCHAR(20) NOT NULL,      -- 借书时用的卡号（业务主键）
    name       VARCHAR(50) NOT NULL,
    status     VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',   -- ACTIVE / SUSPENDED
    created_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE borrow_record (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    book_id     BIGINT    NOT NULL,
    reader_id   BIGINT    NOT NULL,
    borrow_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    due_at      TIMESTAMP NOT NULL,        -- ★ 应还日（借出时就定好）
    return_at   TIMESTAMP,                 -- 为 NULL 表示还没还
    status      VARCHAR(20) NOT NULL DEFAULT 'BORROWED',
    renew_count INT       NOT NULL DEFAULT 0,
    CONSTRAINT fk_borrow_book   FOREIGN KEY (book_id)   REFERENCES book (id),
    CONSTRAINT fk_borrow_reader FOREIGN KEY (reader_id) REFERENCES reader (id)
);

★★ 一个重要的设计判断 ✓✓：

borrow_record 是"事实表" —— 借还是业务里最核心的动作，必须留痕 ✓
如果只在 book 上记"当前被谁借了"，就： · 查不到历史（谁、什么时候借的）✗ · 查不到超期（没有 due_at）✗ · 还书后信息就没了 ✗✗

5.2 冗余字段的取舍 ★★★

★ 本项目用 available_copies 冗余，付出的代价是三条纪律 ✓✓：

#	纪律	做法
①	借/还必须在同一事务里更新库存	`@Transactional` ✓
②	扣减必须是原子的	`UPDATE ... WHERE available_copies > 0` ✓✓
③	归还必须幂等	`UPDATE ... WHERE status = 'BORROWED'` ✓✓

★★ 第 ② 条是最关键的 ✓✓：

sql

-- ✓ 判断与扣减是一条语句（原子）
UPDATE book SET available_copies = available_copies - 1
WHERE id = ? AND available_copies > 0;

-- ✗ 先查再改（并发下会超借）
SELECT available_copies FROM book WHERE id = ?;   -- 两个线程都读到 1
UPDATE book SET available_copies = 0 WHERE id = ?;

★ 项目的并发测试直接验证了这一点 ✓✓： 10 个线程抢 1 本，恰好 1 个成功（《项目 A：测试方案与分层测试》会展示完整测试）

★★ 判断标准 ✓✓："这个字段被读的频率，远高于被写的频率吗？" ✓ （图书列表每页都读库存，而借还是低频操作 → 适合冗余 ✓✓）

5.3 用数据库约束兜底业务规则 ★★★

★ 本项目用了三类约束 ✓：

约束	挡住什么
`CHECK (available_copies >= 0 AND <= total_copies)` ★★★	库存变负、或超过总量（超借的核心防线）
`UNIQUE (isbn)` ★★★	同一本书重复建目
`UNIQUE (card_no)` ★★★	读者卡号重复
外键 ★★	借阅记录指向不存在的书/读者

★★★ 为什么"应用层校验"不够 ✓✓：

应用层会写错、会被绕过（批量任务、手工改库、新接口漏写校验）✗
并发下应用层校验本来就不可靠（check-then-act）✗✗
约束是"最后一道防线" —— 它不需要任何一个调用方配合 ✓✓

★ 本项目的测试直接验证了约束在工作 ✓：

java

@Test
void databaseConstraintBlocksNegativeStock() {
    // ★ 证明"最后一道防线"真的生效
    assertThrows(Exception.class,
        () -> jdbc.update("UPDATE book SET available_copies = -1 WHERE id = 4"));
}

5.4 索引：按"最高频的查询"反推 ★★★

★ 本项目建了五个索引，核心是两个 ✓✓：

索引	服务哪个查询	为什么高频
`idx_borrow_reader_status (reader_id, status)` ★★★	"某读者当前借了几本 / 借了哪些"	借书前必查（规则校验）✓✓
`idx_borrow_due_status (due_at, status)` ★★★	"哪些记录超期了"	定时任务与统计必查 ✓✓
`uk_book_isbn (isbn)`	按 ISBN 查重	入库时查 ✓
`idx_book_title (title)`	按书名搜	列表页搜索 ✓
`uk_reader_card_no (card_no)`	按卡号找读者	每次借书都查 ✓✓

★★ 一条方法论 ✓✓：

不要"给所有字段都建索引"，也不要"凭感觉建" ✓
做法：把系统的高频查询列出来（通常不超过 5 条），为每一条建一个索引 ✓✓
然后用执行计划验证（《执行计划与慢查询定位》）✓

★★ 关于"部分唯一索引"的一个取舍 ✗✓：

sql

-- 想要的效果：同一读者对同一本书，只能有一条"未还"记录
CREATE UNIQUE INDEX uk_active_borrow ON borrow_record (reader_id, book_id)
    WHERE return_at IS NULL;      -- ← PostgreSQL 支持，H2 兼容性差 ✗

本项目没用这个约束，改为应用层校验 + 测试覆盖（hasActiveBorrow）✓
理由：演示环境用 H2，要保证同一份脚本两边都能跑 ✓
这是真实项目里常见的取舍：强约束 vs 可移植性 ✓✓

5.5 种子数据：幂等与序列重置 ★★★

★ 两条必须做对 ✓✓：

#	要点	不做会怎样
①	幂等（可重复执行）	第二次启动报主键冲突 ✗✗
②	重置自增序列	手工指定 id 后，后续 INSERT 从 1 开始 → 撞主键 ✗✗

sql

-- ① 幂等：先删子表（有外键）再删父表
DELETE FROM borrow_record;
DELETE FROM book;
DELETE FROM reader;

-- 显式 id，方便借阅记录引用
INSERT INTO book (id, isbn, ...) VALUES (1, '9787111213826', ...);

-- ② 重置序列（否则下一条 INSERT 会用到 id=1，撞主键）
ALTER TABLE book ALTER COLUMN id RESTART WITH 100;

★★ 一条重要的工程纪律 ✓✓：

表结构（schema）与演示数据（data）必须分开 ✗✓
· schema.sql：生产环境也要跑
· data.sql：只在开发/演示环境跑 —— 生产环境灌演示数据是灾难 ✓✓
本项目用 spring.sql.init.schema-locations 与 data-locations 分开配置 ✓

5.6 可移植性：同一份 DDL 跑在两种库上 ★★

★ 本项目用 H2（演示）+ PostgreSQL（可选） ✓：

写法	H2	PostgreSQL
`BIGINT GENERATED BY DEFAULT AS IDENTITY` ✓	✓	✓（10+）
`AUTO_INCREMENT` ✗	✓	✗（那是 MySQL）
`SERIAL` ✗	✗	✓
`TIMESTAMPADD(DAY, 30, ...)` ✗	✓	✗
`MERGE ... KEY(...)` ✗	✓	✗

★★ 结论 ✓✓：跨库时优先用标准 SQL； 时间计算尽量放到 Java 里（本项目就是这么做的 ✓ —— dueAt 由 LocalDateTime.plusDays() 算 ✓）

6. 递进示例

6.1 最小示例：一条带约束的建表

sql

CREATE TABLE book (
    ...
    CONSTRAINT chk_book_copies CHECK (available_copies >= 0 AND available_copies <= total_copies)
);

6.2 业务示例：冗余字段的同步（借书）

java

@Transactional
public BorrowRecord borrow(long bookId, String cardNo) {
    // ... 校验 ...
    if (!bookRepository.tryBorrowOne(bookId)) {       // ★ 原子扣减
        throw new BusinessException("NO_AVAILABLE_COPY", "...");
    }
    long id = borrowRepository.insert(bookId, reader.id(), now, now.plusDays(rules.getBorrowDays()));
    return borrowRepository.findById(id).orElseThrow();
}

6.3 反例：只改记录不改库存

java

// ✗ 只插借阅记录，忘了扣库存 → "借出 5 次，但库存还是 5"
borrowRepository.insert(...);
// 正确：扣库存和插记录必须在同一个事务里 ✓✓

7. 完整可运行代码与实测

代码：labs/project-a/src/main/resources/db/schema.sql 与 data.sql

查看效果：

powershell

cd labs
.\mvnw.cmd -pl project-a spring-boot:run
# 打开 http://localhost:18080/h2-console（JDBC URL: jdbc:h2:mem:library）→ 直接查三张表

★ 本节的设计在测试里的验证 ✓：

设计	验证它的测试
冗余字段同步（借/还）	`BorrowFlowIntegrationTest.fullLifecycle` ✓
`CHECK` 约束	`databaseConstraintBlocksNegativeStock` ✓✓
唯一索引	`uniqueIsbnBlocksDuplicates` ✓
原子扣减（并发）	`BorrowConcurrencyTest.concurrentBorrowNeverOverBorrows` ✓✓✓

8. Debug 观察点

手工把 available_copies 改成 -1：看 CHECK 是否拦住 ✓✓；
删掉 idx_borrow_reader_status：用执行计划看借书查询是否变全表扫描 ✓✓；
不重置自增序列就插入数据：观察主键冲突 ✗✗；
把 data.sql 灌到"生产"库（假设有数据）：体会为什么要分开 ✗✗；
把借书改成"先查再改"：跑并发测试，看它是否失败 ★★★。

9. 常见错误

#	错误	说明
1	冗余字段但不保证同步 ✗✗	数据对不上（最典型的一致性 bug）✓✓
2	先查再改（check-then-act） ✗✗	并发超借 ✓✓✓
3	只靠应用层校验，不建约束 ✗✗	批量任务/手工改库会绕过 ✓✓
4	给所有字段建索引 ✗	拖慢写入、占空间 ✓
5	种子数据不幂等 ✗✗	第二次启动失败 ✓
6	忘了重置自增序列 ✗✗	插入撞主键（很隐蔽）✓✓
7	演示数据与表结构混在一个文件 ✗	生产环境灌演示数据 ✗✗

10. 动手实验

目标：亲手验证"约束"与"原子扣减"这两道防线。

步骤：

打开 H2 控制台，手工执行 UPDATE book SET available_copies = -1，看报错 ✓✓；
把 tryBorrowOne 改成"先查再改"，跑并发测试，观察它失败 ★★★；
给 borrow_record 加一个你自己的索引，用执行计划验证是否命中 ✓；
写一份 data.sql，保证连续执行两次都不报错 ✓✓；
把 DDL 拿到 PostgreSQL 上跑一遍，记录哪些语句不兼容 ★★。

验收方式：能说出"你的表里哪个约束在保护什么、哪个索引在服务哪个查询"。

扩展任务：加一张 reservation（预约）表：

字段：读者、图书、预约时间、通知状态、失效时间 ✓；
索引：按"哪本书有哪些未处理的预约"来建 ✓✓；
约束：同一读者对同一本书不能重复预约 ✓；
说明：它的"失效"是靠定时任务扫，还是靠查询时过滤（提示：各有适用场景）★★

11. 练习题

判断：只要有应用层校验，就不需要数据库约束。（对 / 错）
填空：冗余字段的两个条件是「读的频率远 ______ 写的频率」和「必须有 ______ 的同步机制」。
简答：为什么"先查再改"在并发下会超借？
（判断）

sql

UPDATE borrow_record SET status = 'RETURNED', return_at = ?
WHERE id = ? AND status = 'BORROWED'

这条 SQL 的 AND status = 'BORROWED' 有什么用？ 5. （设计）给"会议室预约"设计表：要支持查询某时段的可用会议室。

参考答案（要点）

错。应用层会写错/被绕过，而且并发下本来就不可靠 ✓✓★
高于；原子（或"事务内"）✓
两个线程都读到"还有 1 本"，都判断通过，都执行扣减 ✗ —— 读与写之间有窗口，而并发会让窗口被同时穿过 ✓✓★
这就是幂等保护 ✓✓ —— 第二次执行时 status 已经是 RETURNED，影响 0 行，于是"重复归还不会把库存加两次" ✓（呼应《消息可靠性、幂等与顺序》的幂等）
要点：meeting_room（会议室）+ reservation（预约，含起止时间）+ user ✓； 关键索引：(room_id, start_time, end_time) 或按时间段查可用的索引 ✓✓； 难点是"时段重叠"的查询与约束（可用 CHECK (end_time > start_time) + 应用层判重叠，或用排他约束排除重叠 —— PostgreSQL 支持 EXCLUDE，MySQL 不支持）★★

12. 掌握标准

[ ] 能把实体转成表，并说清每个字段 ★★★；
[ ] 能说清冗余字段的取舍与三条纪律 ★★★；
[ ] 知道业务规则要用数据库约束兜底 ★★★；
[ ] 能按高频查询反推索引 ★★★；
[ ] 知道种子数据要幂等 + 重置序列 ★★★；
[ ] 知道跨库 DDL 的常见不兼容点 ★★；
[ ] 知道"部分唯一索引"这类约束的可移植性取舍 ★★。

13. 扩展资源

14. 下一步学习

《项目 A：后端分层与接口设计》：把表变成可调用的接口。

前置知识

S3-013 数据库范式与表关联设计

后续知识

暂无

进度只保存在本机浏览器，可随时清除

外部学习资源

面试题库索引

项目与练手索引

工具与软件索引

项目 A：数据库设计

1. 本节定位

2. 学习目标

3. 前置知识检查

4. 为什么需要这个知识

5. 核心概念

5.1 三张表与字段设计 ★★★

5.2 冗余字段的取舍 ★★★

5.3 用数据库约束兜底业务规则 ★★★

5.4 索引：按"最高频的查询"反推 ★★★

5.5 种子数据：幂等与序列重置 ★★★

5.6 可移植性：同一份 DDL 跑在两种库上 ★★

6. 递进示例

6.1 最小示例：一条带约束的建表

6.2 业务示例：冗余字段的同步（借书）

6.3 反例：只改记录不改库存

7. 完整可运行代码与实测

8. Debug 观察点

9. 常见错误

10. 动手实验

11. 练习题

12. 掌握标准

13. 扩展资源

14. 下一步学习

前置知识

后续知识

项目 A：数据库设计 ​

1. 本节定位 ​

2. 学习目标 ​

3. 前置知识检查 ​

4. 为什么需要这个知识 ​

5. 核心概念 ​

5.1 三张表与字段设计 ★★★ ​

5.2 冗余字段的取舍 ★★★ ​

5.3 用数据库约束兜底业务规则 ★★★ ​

5.4 索引：按"最高频的查询"反推 ★★★ ​

5.5 种子数据：幂等与序列重置 ★★★ ​

5.6 可移植性：同一份 DDL 跑在两种库上 ★★ ​

6. 递进示例 ​

6.1 最小示例：一条带约束的建表 ​

6.2 业务示例：冗余字段的同步（借书） ​

6.3 反例：只改记录不改库存 ​

7. 完整可运行代码与实测 ​

8. Debug 观察点 ​

9. 常见错误 ​

10. 动手实验 ​

11. 练习题 ​

12. 掌握标准 ​

13. 扩展资源 ​

14. 下一步学习 ​

前置知识

后续知识

项目 A：数据库设计

1. 本节定位

2. 学习目标

3. 前置知识检查

4. 为什么需要这个知识

5. 核心概念

5.1 三张表与字段设计 ★★★

5.2 冗余字段的取舍 ★★★

5.3 用数据库约束兜底业务规则 ★★★

5.4 索引：按"最高频的查询"反推 ★★★

5.5 种子数据：幂等与序列重置 ★★★

5.6 可移植性：同一份 DDL 跑在两种库上 ★★

6. 递进示例

6.1 最小示例：一条带约束的建表

6.2 业务示例：冗余字段的同步（借书）

6.3 反例：只改记录不改库存

7. 完整可运行代码与实测

8. Debug 观察点

9. 常见错误

10. 动手实验

11. 练习题

12. 掌握标准

13. 扩展资源

14. 下一步学习