06 · 关系数据库

⏱ 42 分钟 ｜ ⭐ 「要存什么」比「用哪个数据库」重要一百倍

🎯 一句话

难点不在选型，在于「哪些东西必须在请求发生的那一刻就写下来」——错过了永远补不回来。 Postgres 还是 MySQL 五分钟能定；漏掉一列 token 数，三个月后你算不出这个功能挣不挣钱。

🧩 一、先把清单列全

⚠️ 右边那一列才是重点。 会话标题忘了存，明天加一列就有了；token 数当时没记，那条请求的成本永远是未知数——原始响应体早就不在了。

⭐ 判据：可推导的能晚点存，不可推导的必须当场存。 用量、成本、状态变更、审计，都是「事件过去就消失」的东西。

🧩 二、对话为什么是两张表，不是一张 JSON

最省事的做法是把消息塞进一个 JSON 列。能跑，但三件事会打脸：⚠️ 追加一条要整读、改、整写回，两个请求同时追加时后写的会整段覆盖先写的；算不了账（这个用户这月花了多少，没法直接 SUM）；查不了单条（「所有重试失败的 assistant 消息」在两张表里是一句 WHERE）。

-- 整段可以直接喂给 sqlite3（已实跑）；Postgres 只需把类型换成 uuid / timestamptz
CREATE TABLE conversations (
  id TEXT PRIMARY KEY, user_id TEXT NOT NULL, title TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL);      -- ⭐ 列表页按它倒序，不是 created_at

CREATE TABLE messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  conversation_id TEXT NOT NULL REFERENCES conversations(id),
  seq INTEGER NOT NULL,              -- ⭐ 会话内序号，别拿时间戳当顺序
  role TEXT NOT NULL, content TEXT NOT NULL,
  model TEXT,                        -- ⭐ 哪个模型生成的，按条记
  input_tokens INTEGER, output_tokens INTEGER,
  cost_usd REAL,                     -- ⭐ 当场算好，别指望以后回补
  created_at INTEGER NOT NULL,
  UNIQUE (conversation_id, seq));    -- ⭐ 重试写两遍时，这行救你

表的形状一个字不用动，换库只换类型名。有了这两张表，「这个用户这个月花了多少」就是一句 SUM(cost_usd) 加一个 JOIN。

⭐ 三个容易漏的设计：用 seq 而不是靠时间戳排序（同一秒插两条，created_at 可能相同，顺序就随机了）；model 记在消息上而不是会话上（用户中途换模型是常态，成本要按条算）；UNIQUE (conversation_id, seq)——重试导致同一条写两遍是一定会发生的事，让数据库挡掉比在代码里判断可靠。

💡 JSON 列适合装「你不打算查询的东西」：原始响应体、工具调用参数快照。判据是你会不会 WHERE 它、SUM 它，会就单独开列。

🧩 三、索引：哪两个查询会先慢

读请求高度集中在两条路径：打开侧边栏（这个用户的会话，按最近更新倒序取 20 条）、点开会话（这个会话的消息，按 seq 升序）。不建索引就是全表扫。

实测（SQLite 内存库塞 40 万行消息、随机分给 2 万个会话，查的就是上面那条「点开会话」路径——SELECT role, content FROM messages WHERE conversation_id=? ORDER BY seq，重复 200 次；你的数值会不同，但量级差异是结构性的）：

⭐ 索引要把 ORDER BY 的列也带上。只建 (conversation_id) 也能命中，但排序还得在内存里做——无索引那行计划里的 USE TEMP B-TREE FOR ORDER BY 就是它；两列一起建，数据库直接按顺序读出来，那句就没了。同理会话列表要的是 (user_id, updated_at DESC)。

💡 COVERING INDEX（连回表都省掉）是另一回事，别指望它：只有你要的列全在索引里时才会出现。上面这条查询要读 content，索引里没有，所以仍要回表，计划是 SEARCH … USING INDEX。把它换成 SELECT seq FROM …（只取索引列）计划才变成 SEARCH … USING COVERING INDEX，同样 200 次 0.0009 秒。⚠️ 别为了凑覆盖索引把 content 塞进索引——那等于把整张表再复制一遍。

⚠️ 看执行计划，别猜（SQLite EXPLAIN QUERY PLAN，Postgres EXPLAIN (ANALYZE, BUFFERS)），大表上出现 SCAN / Seq Scan 就是信号。也别把索引当免费——每个索引都让写入变慢、占空间，messages 这种写多的表两三个够了。

🧩 四、Schema 演进：迁移不是可选项 ⭐⭐

AI 产品的字段是天天加的：今天加 reasoning_tokens，明天加 cache_read_tokens。所以第一天就得回答：线上那个库怎么知道自己该长什么样？ 答案是把每次结构变更写成一个带名字的脚本，把「跑过哪些」记在数据库自己里：

import sqlite3
MIGRATIONS = [("001_init", "CREATE TABLE m (id INTEGER PRIMARY KEY, body TEXT)"),
              ("002_add_cost", "ALTER TABLE m ADD COLUMN cost_usd REAL")]  # ⭐ 只往末尾追加
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE IF NOT EXISTS schema_migrations (name TEXT PRIMARY KEY)")
for _ in range(2):                   # ⭐ 连跑两遍：第二遍什么也不会发生，这就是幂等
    done = {r[0] for r in db.execute("SELECT name FROM schema_migrations")}
    for name, sql in MIGRATIONS:
        if name in done: continue
        db.execute(sql)                                                  # 改结构
        db.execute("INSERT INTO schema_migrations VALUES (?)", (name,))  # ⭐ 同一步记账
        db.commit(); print("applied", name)
print([r[1] for r in db.execute("PRAGMA table_info(m)")])

Alembic 之类的成品就是这个形状：有序脚本 + 一张记录表 + 幂等（部署脚本每次启动都调它，不幂等第二次就报错）。⚠️ 跑过的迁移永远不能回头改——别的库早就按旧版本执行完了。

💀 不用迁移的下场：有人本地手改了一列，没写进任何脚本。三个月后新同事按 README 建库，报 column does not exist；线上能跑只是因为那一台机器的库被手改过——此时谁也说不出线上库的真实结构。

⚠️ 上线中的表怎么加字段而不锁死

以下是 Postgres 行为（🗓️ 未实跑，需要真实 Postgres；细节随大版本变，动手前查你那版文档）：

⭐ 通用规则：迁移和发布要能各自单独回滚，做法就是让每一步都同时兼容新旧两版代码——这是五步法存在的全部理由。

🧩 五、连接池：并发一上来就崩的那个配置

Postgres 每个连接是一个进程，几百个就能把内存吃光，所以应用侧要复用固定数量的连接。以 SQLAlchemy 异步引擎为例（🗓️ 未实跑，需要 Postgres + asyncpg），五个旋钮值得认识：pool_size 常驻连接数；max_overflow 峰值临时再开几个；⭐ pool_timeout 等不到就报错（无限等 = 请求全挂住，比报错难查得多）；pool_recycle 定期回收，躲开中间层的空闲断连；⭐ pool_pre_ping 借出前 ping 一下，别拿到已经死掉的连接。

⭐ pool_size 是一道除法，不是拍脑袋：数据库能承受的连接总数（比如 100）÷ 应用进程数（4 worker × 2 台 = 8）÷ 还有谁在连同一个库（后台任务、定时脚本、你的 psql）→ 每进程 ~10 个。

⚠️ 两个真实翻车点：忘了乘进程数（单机测试 pool_size=20 很舒服，上线开 8 个 worker 就是 160 个连接，数据库直接拒连）；以及 ⭐ 池小不是错，池小 + 占着不放才是错——真正的容量瓶颈几乎总是持有时长。

🧩 六、事务边界：⚠️ 别把 LLM 调用包在事务里

这是 AI 应用最常见也最贵的数据库错误。 写法非常自然：开事务 → 写用户消息 → 调 LLM → 写回复 → 提交，「要么都成功要么都失败，多干净」。问题是中间那步要三秒到三十秒，这整段时间里一个连接被占着什么也没干：

import asyncio, time
POOL, N, LLM, DB = 5, 40, 1.0, 0.005     # 真实世界一次 LLM 调用常是 3–30 秒

async def bad(sem):
    async with sem:                                # ⚠️ 连接从头占到尾
        await asyncio.sleep(DB + LLM + DB)

async def good(sem):
    async with sem: await asyncio.sleep(DB)        # ⭐ 读完立刻还回去
    await asyncio.sleep(LLM)                       # ⭐ LLM 调用在池外
    async with sem: await asyncio.sleep(DB)        # ⭐ 写的时候再借一次

async def main():
    for name, fn in (("包在池里", bad), ("池外调用", good)):
        sem = asyncio.Semaphore(POOL); t = time.perf_counter()   # 信号量当连接池
        await asyncio.gather(*(fn(sem) for _ in range(N)))
        print(f"{name}: {N} 个请求耗时 {time.perf_counter()-t:.2f}s")

asyncio.run(main())

实测输出：包在池里: 8.15s / 池外调用: 1.15s。池只有 5、并发 40，请求被迫排成 8 批，每批空等一次完整的 LLM 调用。池大小一个字没改，只是把外部调用挪出了持有区间。

⭐ 判据：事务里只放数据库自己的操作，而且必须是快操作。 任何网络调用（LLM、对象存储、第三方 API）都在事务之外。

那「一半成功了怎么办」？不是拉长事务，是换个模型：用户消息先单独提交；LLM 失败就给会话记一个 failed 状态，让用户看得见、能重试；真正需要原子性的只有最后一小段——写 assistant 消息 + 记账 + 更新 updated_at，一个短事务搞定。

⚠️ 同源的坑：别在 SELECT ... FOR UPDATE 之后调外部服务——那不只占着连接，是攥着一把行锁，别的请求会卡死在那些行上。

🌐 换个栈怎么对应

⭐ 执行计划三家都是同一句 EXPLAIN ANALYZE —— 这是数据库的能力，跟语言无关。 ⭐ 三个生态的迁移工具都长成「有序脚本 + 一张记录表」，连接池都有个「最大连接数」旋钮。换栈要重学的只有语法。（🗓️ 包名会变，用前先查。）

🔗 这一章连到哪里

✅ 检查点

1. 七类必存数据里，哪三类「事后补不回来」？为什么？
2. 为什么把整段对话塞进一个 JSON 列，会在并发追加时出错？
3. messages 为什么要有 seq 列，而不是按 created_at 排序？
4. 索引把 ORDER BY 的列一起带上好在哪？实测那两个数字是多少？
5. 迁移器为什么必须幂等？「跑过哪些」记在哪里？
6. 线上要删掉一列，为什么不能直接 DROP COLUMN？五步法是哪五步？
7. pool_size 怎么算？单机测好的值搬上线为什么会炸？
8. 把 LLM 调用包在连接里，实测（池 5 / 并发 40 / LLM 1 秒）是多少对多少？

🛑 可以停在这里

⚡ 走神救援

难点不在选型，在哪些东西必须在请求发生那一刻写下来。七类必存数据里，用量与成本、任务状态、审计日志补不回来——原始响应体早没了。判据：可推导的能晚存，不可推导的必须当场存。 对话拆 conversations + messages 两张表不塞 JSON：JSON 追加要整读整写、并发时后写的整段覆盖先写的，还没法 SUM(cost_usd)。三个细节——用 seq 排序（同一秒时间戳会撞）、model 按条记（中途换模型是常态）、UNIQUE (conversation_id, seq) 挡掉重试的重复写。 索引覆盖两条热路径：(user_id, updated_at DESC) 和 (conversation_id, seq)，把 ORDER BY 的列一起带上才不用二次排序。实测（SQLite 内存库 40 万行，查的是「点开会话」那条路径）200 次查询：无索引 3.802 秒（SCAN + USE TEMP B-TREE FOR ORDER BY），有索引 0.0019 秒（SEARCH … USING INDEX；⚠️ 它要读 content 所以仍要回表，别指望覆盖索引——只取索引列才覆盖，0.0009 秒）。看执行计划别猜，也别乱加索引——写入会变慢。 字段天天加，所以迁移必须有：有序脚本 + schema_migrations 记录表 + 幂等，十几行就能写，Alembic 只是成品；跑过的迁移永远不回头改。线上加字段的真风险是锁排队：ALTER 要一瞬排他锁，前面卡着长查询它就排队，后来的读写又排在它后面，整张表卡住，所以要 SET lock_timeout 抢不到就重试。删列改名走扩展—收缩五步：加新列 → 双写 → 回填 → 切读 → 几天后删。 连接池大小是道除法：数据库总连接数 ÷ 进程数 ÷ 其他连它的人；⚠️ 单机 pool_size=20 × 8 worker = 160 连接是最常见的上线事故。但瓶颈几乎总是持有时长：把 LLM 调用包在池里实测 8.15 秒（池 5、并发 40、LLM 1 秒），只把它挪出持有区间就是 1.15 秒。事务里只放数据库自己的快操作，网络调用一律在外；更别在 SELECT ... FOR UPDATE 之后调外部服务，那是攥着行锁在等。原子性靠拆：用户消息先单独提交，失败记 failed 状态，最后写回复 + 记账 + 更新时间用一个短事务。

下一节 👉 06b-列表接口与分页.md