AI Full-Stack Architect

Appearance

预计时间

2 周

学习目标 ​

  • 表设计：从 ER 图到建表 SQL
  • 索引设计：理解 B+Tree，掌握联合索引
  • SQL 优化：EXPLAIN 分析、慢查询排查
  • 事务与锁：ACID、隔离级别、死锁排查
  • 多租户架构：三种隔离方案对比

一、表设计 ​

1.1 从需求到表 ​

拿到需求后，不要上来就写 CREATE TABLE。先画 ER 图，理清实体和关系。

以 AI 知识库系统为例：

text
实体列表：
  User         — 用户
  Organization — 组织（租户）
  Document     — 文档
  Knowledge    — 知识片段（chunk）
  Conversation — 会话
  Message      — 消息
  ApiKey       — API 密钥

关系：
  User M:1 Organization     （一个组织多个用户）
  Organization 1:M Document （一个组织多个文档）
  Document 1:M Knowledge    （一个文档多个知识片段）
  User 1:M Conversation     （一个用户多个会话）
  Conversation 1:M Message  （一个会话多条消息）
  Organization 1:M ApiKey   （一个组织多个 API 密钥）

1.2 建表 SQL ​

sql
-- 组织（租户）
CREATE TABLE organizations (
  id          UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  slug        VARCHAR(100) NOT NULL UNIQUE,  -- URL 友好标识
  plan        VARCHAR(20)  NOT NULL DEFAULT 'free',  -- free | pro | enterprise
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- 用户
CREATE TABLE users (
  id              UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  organization_id UUID NOT NULL REFERENCES organizations(id),
  email           VARCHAR(255) NOT NULL,
  password_hash   VARCHAR(255) NOT NULL,
  role            VARCHAR(20) NOT NULL DEFAULT 'member',  -- admin | member
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 文档
CREATE TABLE documents (
  id              UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  organization_id UUID NOT NULL REFERENCES organizations(id),
  uploaded_by     UUID NOT NULL REFERENCES users(id),
  title           VARCHAR(500) NOT NULL,
  file_type       VARCHAR(20) NOT NULL,  -- pdf | md | txt | docx
  file_size       BIGINT NOT NULL,
  status          VARCHAR(20) NOT NULL DEFAULT 'processing',  -- processing | ready | failed
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_documents_org ON documents(organization_id, status);

-- 知识片段（chunk）
CREATE TABLE knowledge_chunks (
  id          UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  content     TEXT NOT NULL,
  chunk_index INT NOT NULL,           -- 第几个 chunk
  token_count INT NOT NULL,           -- Token 数量
  embedding   vector(1536),           -- pgvector 扩展：向量字段
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_knowledge_doc ON knowledge_chunks(document_id);

-- 会话
CREATE TABLE conversations (
  id         UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id    UUID NOT NULL REFERENCES users(id),
  title      VARCHAR(500),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_conversations_user ON conversations(user_id, created_at DESC);

-- 消息
CREATE TABLE messages (
  id              UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  role            VARCHAR(20) NOT NULL,  -- user | assistant | system
  content         TEXT NOT NULL,
  token_count     INT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_messages_conv ON messages(conversation_id, created_at);

1.3 范式 vs 反范式 ​

第三范式 (3NF)反范式
做法消除冗余，数据只存一份适度冗余，用空间换查询速度
优点数据一致性好，更新简单查询快，少 JOIN
缺点多表 JOIN 慢更新要改多处
适用场景OLTP（频繁写入）OLAP（频繁复杂查询）

实战建议

先按 3NF 设计，哪里慢再反范式。 不要过早优化。大多数时候，加对索引就足够快了。

二、索引设计 ​

2.1 B+Tree 是怎么工作的？ ​

把索引想象成一本书的目录：

  • 没有索引 → 从第一页翻到最后一页（全表扫描）
  • 有索引 → 查目录，直接翻到对应页码

B+Tree 的结构：

text
                 [50 | 100]          ← 根节点（范围指针）
                /    |     \
         [10|30]  [60|80]  [120|150]  ← 中间节点
         /  |  \   /  |  \   /  |  \
       叶子节点（存真实数据或指针）

关键特性：

  • 所有叶子节点在同一层 → 查询时间复杂度 O(log n)
  • 叶子节点之间用链表相连 → 范围查询快（BETWEEN、>, <）

2.2 什么时候建索引？ ​

sql
-- 频繁作为 WHERE 条件的列
CREATE INDEX idx_status ON documents(status);

-- 频繁 JOIN 的外键
CREATE INDEX idx_documents_org ON documents(organization_id);

-- 排序和分页字段
CREATE INDEX idx_created ON documents(created_at DESC);

-- 覆盖多个查询条件的联合索引
CREATE INDEX idx_org_status ON documents(organization_id, status);

2.3 联合索引 —— 最左前缀原则 ​

这是面试和实际工作中最高频的知识点。

sql
-- 联合索引
CREATE INDEX idx_a_b_c ON orders(a, b, c);

只有以下查询能用上这个索引：

sql
✅ WHERE a = 1                -- 匹配最左列 a
✅ WHERE a = 1 AND b = 2      -- 匹配 a, b
✅ WHERE a = 1 AND b = 2 AND c = 3  -- 全部匹配
✅ WHERE a = 1 AND c = 3      -- 匹配 a（c 不能跳过 b）

❌ WHERE b = 2                -- 跳过 a，不行
❌ WHERE c = 3                -- 跳过 a 和 b，不行
❌ WHERE a = 1 OR b = 2       -- OR 导致索引失效

类比记忆

联合索引像一个多层目录：国家 → 省份 → 城市。你能快速找到"中国→广东→深圳"，但没法跳过"国家"直接找"深圳"。

2.4 用 EXPLAIN 看索引效果 ​

sql
EXPLAIN ANALYZE
SELECT * FROM documents
WHERE organization_id = 'abc' AND status = 'ready';

关注这三个字段：

字段好坏
typeconst, eq_ref, ref, rangeALL（全表扫描）
key有索引名NULL（没用索引）
rows越小越好接近总行数

2.5 索引不是越多越好 ​

text
索引的代价：
  INSERT 每条数据 → 更新所有索引 → 写入变慢
  索引本身占磁盘空间
  优化器选错索引比没索引更糟

原则：为查询建索引，不为建而建。

三、SQL 优化 ​

3.1 常见优化模式 ​

sql
-- ❌ SELECT * 返回不需要的列
SELECT * FROM documents WHERE status = 'ready';

-- ✅ 只取需要的列
SELECT id, title, file_type FROM documents WHERE status = 'ready';

-- ❌ 函数包装索引列，索引失效
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- ✅ 保持索引列"裸用"（或建表达式索引）
CREATE INDEX idx_users_email_lower ON users((LOWER(email)));

-- ❌ 大 offset 分页
SELECT * FROM messages ORDER BY created_at LIMIT 20 OFFSET 100000;

-- ✅ 游标分页（记住上一页最后一条）
SELECT * FROM messages
WHERE created_at < '2024-01-01 00:00:00'
ORDER BY created_at DESC LIMIT 20;

-- ❌ 隐式类型转换
SELECT * FROM users WHERE id = '123';  -- id 是 int, '123' 是 string

-- ✅ 类型匹配
SELECT * FROM users WHERE id = 123;

3.2 慢查询排查流程 ​

text
1. 开慢查询日志
   SET slow_query_log = ON;
   SET long_query_time = 1;  -- 超过 1 秒就记录

2. EXPLAIN 分析执行计划
   看 type、key、rows、Extra

3. 优化手段优先级
   加索引 > 改写查询 > 拆表 > 加缓存 > 换数据库

四、事务 ​

4.1 ACID 到底是什么？ ​

字母含义一句话解释
Atomicity原子性要么全做，要么全不做
Consistency一致性钱不会凭空消失（业务规则）
Isolation隔离性并发事务互不干扰
Durability持久性提交了就不会丢（写入磁盘）

4.2 隔离级别 ​

sql
-- 查看当前级别
SHOW TRANSACTION ISOLATION LEVEL;

-- 设置级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
隔离级别脏读不可重复读幻读性能
READ UNCOMMITTED⚠️ 会⚠️ 会⚠️ 会最快
READ COMMITTED✅ 不会⚠️ 会⚠️ 会快
REPEATABLE READ (PG 默认)✅ 不会✅ 不会✅ 不会中等
SERIALIZABLE✅ 不会✅ 不会✅ 不会最慢

PG 的 REPEATABLE READ 和 MySQL 不一样

PostgreSQL 的 REPEATABLE READ 已经通过 Snapshot Isolation 解决了幻读，不需要到 SERIALIZABLE。MySQL 的 REPEATABLE READ 可能还有幻读。

4.3 脏读 / 不可重复读 / 幻读 ​

text
脏读 (Dirty Read)：
  事务 A 改了数据但没提交 → 事务 B 读到了这个未提交的数据
  → A 回滚了 → B 读到一个不存在的值

不可重复读 (Non-Repeatable Read)：
  事务 A 第一次读 age=25 → 事务 B 把 age 改成了 26 并提交
  → 事务 A 第二次读 age=26 → 同一事务内两次读到不同的值

幻读 (Phantom Read)：
  事务 A 查询 status='ready' 得到 5 条 → 事务 B 插入一条 status='ready' 并提交
  → 事务 A 再次查询得到 6 条 → 数据"凭空多出来了"

4.4 实践中怎么选？ ​

text
默认用 READ COMMITTED —— 90% 场景够用，性能好
需要一致性保证时用 REPEATABLE READ —— 如转账、库存扣减

五、锁 ​

5.1 行锁 vs 表锁 ​

sql
-- 行锁（只锁命中行）
SELECT * FROM users WHERE id = 'abc' FOR UPDATE;

-- 表锁（锁整张表）
LOCK TABLE users IN EXCLUSIVE MODE;
行锁表锁
粒度单行整表
并发高低
开销大（每行一个锁）小
适用OLTPDDL、全表更新

5.2 死锁 ​

text
死锁条件（四个同时满足）：
  1. 互斥 — 资源不能共享
  2. 持有并等待 — 拿着 A 等 B
  3. 不可抢占 — 不能抢别人的锁
  4. 循环等待 — A 等 B，B 等 A → 闭环

示例：
  事务1: UPDATE users SET age=25 WHERE id=1;  -- 锁 id=1
         UPDATE users SET age=26 WHERE id=2;  -- 等 id=2（被事务2 锁着）

  事务2: UPDATE users SET age=27 WHERE id=2;  -- 锁 id=2
         UPDATE users SET age=28 WHERE id=1;  -- 等 id=1（被事务1 锁着）

  → 死锁！PG 会自动检测并回滚其中一个事务。

避免死锁：按固定顺序访问资源、缩短事务、使用低隔离级别。

六、SaaS 多租户模型 ​

三种主流方案：

text
方案 A：一租户一数据库（Database per Tenant）
  org_001_db / org_002_db / org_003_db ...

  优点：最强隔离、备份恢复独立
  缺点：连接池管理复杂、成本高
  适用：金融、医疗等强合规需求

方案 B：共享数据库 + 共享表（Shared Table）
  所有租户数据在同一张表，用 tenant_id 区分

  优点：简单、成本低
  缺点：隔离最弱、查询必须带 tenant_id
  适用：早期 MVP、中小 SaaS

方案 C：共享数据库 + Schema 隔离（Schema per Tenant）
  同一数据库，不同 schema：tenant_001.users / tenant_002.users

  优点：隔离较好、资源共享、成本适中
  缺点：Schema 管理复杂、跨租户查询困难
  适用：有一定隔离需求、成本敏感

推荐

起步选方案 B（最简单），用 Prisma 的 Row-Level Security 或中间件自动注入 tenant_id。等有了付费大客户再考虑方案 A。

实践 ​

不用等到看完所有内容，现在就动手：

  1. 复制上面的建表 SQL 到你的 PostgreSQL
  2. 插入一些测试数据
  3. 跑几个 EXPLAIN 看看索引效果
  4. 试着开两个事务测试隔离级别