数据库设计与事务

学习目标：能为小型业务设计表、约束、索引和事务，并用查询计划验证性能假设。

1. 从业务规则到表结构

先写清实体与约束，再选数据库。以笔记服务为例：用户拥有笔记，标题不能为空，每条笔记有创建和更新时间。

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE notes (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner_id BIGINT NOT NULL REFERENCES users(id),
    title TEXT NOT NULL CHECK (length(trim(title)) > 0),
    body TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX notes_owner_created_idx ON notes(owner_id, created_at DESC, id DESC);

这段语法以 PostgreSQL 为例，其他数据库在自增、时间类型和函数上有所不同。应用层校验提升体验，数据库约束保证所有写入路径遵守底线。

2. 索引与查询

<span style="color:#1E90FF;font-weight:700;">索引（index）</span>加速特定查询，也占存储并增加写入成本。先确定查询条件和排序，再设计复合索引；不要给每一列都建索引。用 EXPLAIN/EXPLAIN ANALYZE 看实际计划，结合数据量和选择性判断，不能只凭 SQL 长短猜快慢。

分页时要固定排序。高页码的 OFFSET 可能成本较高，可按 (created_at, id) 使用游标分页；更新中的数据可能改变结果，客户端要接受分页视图不是全局快照。

3. 事务与并发

<mark style="background-color:#FFFF99;color:#1F2937;padding:0.08em 0.28em;border-radius:0.25em;">事务边界</mark>应对应一个业务不变量。例如转账的扣款与入账需同成同败。事务失败要回滚；并发下还需考虑唯一约束、锁或乐观版本号，不能只靠“先查再写”防重复。

BEGIN → 检查与写入 → COMMIT
                    └─ 任一步失败 → ROLLBACK

不同隔离级别对脏读、不可重复读、幻读的处理不同，具体还要看数据库实现。先能描述业务允许什么并发结果，再选择隔离策略。

4. 迁移与 ORM

表结构变更要有版本化迁移。大表加列、回填和创建索引可能影响线上读写，采用“先兼容旧版 → 回填 → 切换读取 → 清理旧字段”的步骤。ORM 能减少样板代码，但不能代替 SQL 理解；留意 N+1 查询、连接池耗尽和隐式事务。

<div style="border-left:4px solid #DC143C;padding:0.55em 0.8em;margin:0.8em 0;color:#DC143C;"><strong>易错点：</strong>不要把用户输入直接拼进 SQL。参数化查询防注入，逐资源授权则防止读到别人的数据；两者解决的问题不同。</div>

自测

只建 owner_id 单列索引，是否一定最适合按 created_at DESC 列表查询？不一定，应看组合条件与查询计划。
“先查询不存在，再插入”能否独立保证并发唯一？不能，应有数据库唯一约束。

5. 用真实查询反推索引

以下查询只读某个用户的笔记，并以时间和 ID 确保稳定排序。索引 (owner_id, created_at DESC, id DESC) 与过滤和排序方向匹配；是否真能获益仍需用实际数据量与查询计划验证。

SELECT id, title, created_at
FROM notes
WHERE owner_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 20;

翻页可记录上一页最后一条的 (created_at, id)，下一页增加行值比较条件 AND (created_at, id) < ($2, $3)。若允许修改创建时间，游标稳定性会受影响；一般创建时间应不可变。索引不等于权限：每个列表、详情、修改和删除查询都要基于当前认证用户限定 owner_id。

6. 乐观锁保护编辑冲突

给表增加 version BIGINT NOT NULL DEFAULT 1。客户端读取笔记时同时得到版本号，更新时提交旧版本；只有版本仍匹配才更新并自增。若影响行数为 0，要区分“不存在、无权限、版本冲突”的 API 策略。

UPDATE notes
SET title = $3, version = version + 1, updated_at = now()
WHERE id = $1 AND owner_id = $2 AND version = $4;

这个条件把检查与修改放在同一条语句里，避免先读后写的竞态。数据库迁移新增非空列时，要考虑大表回填和滚动发布期间旧应用的写入行为；开发环境能一次完成的 SQL 未必适合生产大表。

7. 事务实验

用两个数据库会话同时修改同一用户的两条记录，观察锁等待和最终结果；再给某一步故意制造唯一约束冲突，确认前面的写入是否回滚。记录所用数据库、隔离级别与 SQL，不要把在 SQLite 看到的行为直接套到 PostgreSQL 或 MySQL。

01_数据库设计与事务