4.2 数据库建模实战——主键、外键、索引、事务，一次讲透模块四：后端 API 与数据管理

模块四：后端 API 与数据管理 | 第02讲：数据库建模实战——主键、外键、索引、事务，一次讲透

本讲定位：把 VibeNote 的数据从「想当然的字段列表」升级为「可演进的关系模型」，并能在本机或云上跑起 PostgreSQL。
项目锚点：VibeNote 智能笔记（PostgreSQL；后续用 Drizzle 映射）。
阅读线索：课程内容《5.3 数据库建模从入门到可用：主键外键索引事务实战》；仓库内可参考 course/part3-fullstack/14-database-basics.md（原参考路径中的 06-data-persistence-database.md 未收录，本节以课程英文稿与 PostgreSQL 通用实践补足）。

一、开篇：关系型数据库到底解决了什么问题？

上一讲我们把 API 当作「前台与厨房之间的服务员」。数据库则是「冷库与货架」：负责长期保存、并发下的一致性、可查询的结构。

如果你用 JSON 文件或单机内存存笔记，会遇到三类经典事故：

并发写入互相覆盖：两个请求同时保存，后写覆盖先写，用户以为已保存的内容消失。
关联关系无法约束：笔记挂了不存在的 userId，或删除用户后笔记变成孤儿数据。
查询与规模：全文检索、按标签过滤、按时间排序——没有索引与结构化查询，最终你会手写一堆脆弱逻辑。

关系型数据库用表、主键、外键、索引、事务把这些问题收敛到成熟机制里。你要做的，是把业务语言翻译成可验证的 schema。

二、核心概念速通：主键、外键、索引、事务

2.1 主键（Primary Key）

主键是每一行记录的唯一身份证。常见策略：

策略	优点	缺点	VibeNote 建议
自增整数（SERIAL/BIGSERIAL）	紧凑、索引友好	易预测、分布式合并麻烦	内部关联可用
UUID（gen_random_uuid()）	合并与暴露 ID 更安全	占用略大	对外 API 暴露的 id
雪花/ULID	可排序、分布式友好	实现成本高	中大型团队

原则：主键稳定、非空、唯一；不要在业务上把「邮箱」当主键——用户会改邮箱。

2.2 外键（Foreign Key）

外键声明「这一列引用另一张表的主键」。数据库在插入/删除时帮你维护引用完整性。

VibeNote 中：notes.user_id 引用 users.id；note_tags 连接 notes 与 tags。

2.3 索引（Index）

索引是用空间换时间的查找加速结构。像书的目录：没有目录也能读，但查找慢。

滥用索引会拖慢写入，因为每次插入/更新都可能维护索引树。只为查询路径建索引。

2.4 事务（Transaction）

事务把多条语句包成「要么全成功，要么全失败」的原子单元，并提供隔离级别控制并发下的可见性。

典型场景：创建笔记并写入多标签——中间失败则应回滚，避免出现「有笔记无标签」或「标签挂上了一半」。

flowchart TB
    subgraph ACID["事务要守住什么"]
        A[Atomicity 原子性]
        C[Consistency 一致性]
        I[Isolation 隔离性]
        D[Durability 持久性]
    end
    A --> C
    C --> I
    I --> D

三、VibeNote 领域建模：从用户故事到表

3.1 用户故事（MVP）

用户可以注册/登录（用户实体）。
用户可以创建、编辑、删除笔记（笔记实体，归属用户）。
笔记可以打多个标签；标签可复用（多对多）。
笔记支持标题、Markdown 正文、软删除可选、时间戳。

3.2 ER 关系（逻辑）

erDiagram
    USERS ||--o{ NOTES : owns
    NOTES ||--o{ NOTE_TAGS : has
    TAGS ||--o{ NOTE_TAGS : tagged

    USERS {
        uuid id PK
        text email UK
        text name
        timestamptz created_at
    }

    NOTES {
        uuid id PK
        uuid user_id FK
        text title
        text content_md
        timestamptz created_at
        timestamptz updated_at
    }

    TAGS {
        uuid id PK
        uuid user_id FK
        text name UK_per_user
    }

    NOTE_TAGS {
        uuid note_id FK
        uuid tag_id FK
    }

说明：tags 按 user_id 做作用域隔离，避免用户 A 看到用户 B 的私有标签名冲突；若你做「全局公共标签库」，需另开话题（搜索、审核、重名合并）。

四、PostgreSQL 落地：Docker 与 Supabase 两条路线

4.1 路线 A：Docker 本机开发（可复现）

docker-compose.yml 示例（仅开发）：

# docker-compose.yml
services:
  postgres:
    image: postgres:16
    restart: unless-stopped
    environment:
      POSTGRES_USER: vibenote
      POSTGRES_PASSWORD: vibenote_dev_password
      POSTGRES_DB: vibenote
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata: {}

启动：

docker compose up -d

连接串写入 .env.local（勿提交 Git）：

DATABASE_URL="postgresql://vibenote:vibenote_dev_password@localhost:5432/vibenote"

4.2 路线 B：Supabase / Neon 托管（接近生产）

在控制台创建项目 → 复制 DATABASE_URL（通常要求 sslmode=require）。优点是免运维、备份与监控开箱；注意开发/生产分库。

五、可执行 DDL：最小可用 Schema + 索引 + 外键

下面 SQL 可在 psql、Supabase SQL Editor 或 Drizzle 迁移中执行。为教学清晰，使用 gen_random_uuid() 与 timestamptz。

-- 启用 UUID（部分托管环境已默认）
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE notes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content_md TEXT NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE tags (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, name)
);

CREATE TABLE note_tags (
  note_id UUID NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
  tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (note_id, tag_id)
);

-- 查询：某用户的笔记列表按更新时间倒序（高频）
CREATE INDEX notes_user_updated_idx ON notes (user_id, updated_at DESC);

-- 查询：按标题关键词（简单版走 ILIKE 时可配合 pg_trgm，后续演进）
CREATE INDEX notes_title_search_idx ON notes (user_id, title);

-- 标签侧查笔记：通过 note_tags 反查
CREATE INDEX note_tags_tag_idx ON note_tags (tag_id);
CREATE INDEX note_tags_note_idx ON note_tags (note_id);

5.1 `ON DELETE CASCADE` 的取舍

CASCADE：删除用户 → 笔记、标签、关联一并删除。适合强私有数据。
RESTRICT：防止误删，先清理子表。
SET NULL：若子表允许空引用（本示例不适用 user_id）。

产品决策必须写进 PRD：账号注销是否物理删除？ 法规场景可能需要「软删除 + 留存策略」。

5.2 触发器维护 `updated_at`（可选）

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notes_set_updated_at
BEFORE UPDATE ON notes
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

若 PostgreSQL 版本较旧，EXECUTE FUNCTION 需改为 EXECUTE PROCEDURE——让 AI 按你的版本生成。

六、事务示例：创建笔记并绑定标签

BEGIN;

-- 假设 :user_id, :title, :content 已在外层绑定
WITH new_note AS (
  INSERT INTO notes (user_id, title, content_md)
  VALUES (:user_id, :title, :content)
  RETURNING id
),
ins_tag AS (
  INSERT INTO tags (user_id, name)
  VALUES (:user_id, :tag_name)
  ON CONFLICT (user_id, name) DO UPDATE SET name = EXCLUDED.name
  RETURNING id
)
INSERT INTO note_tags (note_id, tag_id)
SELECT new_note.id, ins_tag.id FROM new_note, ins_tag;

COMMIT;

教学要点：多表写入同一业务动作 → 默认放在一个事务。应用层（Next.js Route Handler）里，等价于 db.transaction(async (tx) => { ... })（下一讲 Drizzle 展开）。

七、索引什么时候加？一张检查表

WHERE / JOIN / ORDER BY 出现的列组合是否稳定？稳定则考虑复合索引。
选择性是否足够？极低选择性（如布尔）单独索引收益小。
写多读少的表要谨慎加索引。
用 EXPLAIN (ANALYZE, BUFFERS) 看真实计划，而不是凭感觉。

flowchart TD
    Q[慢查询] --> M[确认 SQL 与数据量]
    M --> E[EXPLAIN ANALYZE]
    E -->|Seq Scan 过大| I[加索引/改条件]
    E -->|索引未用上| R[检查类型隐式转换/函数包裹列]
    I --> V[回归写入性能]
    R --> V

八、把建模与 API 串起来（承接上一讲）

当你完成 DDL，就可以把第01讲的内存数组替换为真实查询：

GET /api/notes：WHERE user_id = ? ORDER BY updated_at DESC LIMIT ?
POST /api/notes：插入 notes + 可选写入 tags/note_tags 事务。

没有正确模型，API 只是随机字符串拼接器；模型稳定后，AI 生成 CRUD 会高度可复用。

九、思考题

为什么「标签」更适合多对多中间表，而不是在 notes 里用逗号分隔字符串？举三个工程向的理由。
若你要支持「团队协作笔记」，ER 图会如何变化？哪些外键与唯一约束必须调整？
什么时候你会把 content_md 拆到 TOAST/大字段策略里关心性能？MVP 阶段要不要提前优化？

十、本节小结

关系型数据库用 主键/外键/索引/事务把数据一致性与查询性能变成可执行规则。
VibeNote MVP 推荐 users / notes / tags / note_tags，并明确 ON DELETE 策略。
Docker 与 Supabase 都是合理起点，连接串永远不要进前端仓库。
索引来自真实查询路径，用 EXPLAIN 验证，而不是「先堆一堆」。

十一、下一讲预告

第03讲：ORM 与 Drizzle——让 AI 写出可维护的数据访问层
我们将把本讲 DDL 翻译为 Drizzle Schema，配置 drizzle-kit 迁移流程，完成类型安全的 CRUD 与多对多写入；并对比 Drizzle 与 Prisma 在「AI 友好度、类型推断、迁移工作流」上的差异，给你一套可复制的提示词与目录结构。

4.2 数据库建模实战——主键、外键、索引、事务，一次讲透