agent学习Day21——阶段3收官：数据库持久化、事务边界与端到端测试

阶段 3 里程碑验收通过。本文覆盖数据库迁移概念、索引设计原则、JD 分析接口自动存库、事务边界、N+1 问题与端到端测试，对新知识点做深入拆解。

一、数据库迁移概念 + 表设计原则

1.1 为什么需要"数据库迁移"

场景：项目里的 JdRecord 表已经跑了一周，突然需求来了——要加一个 jd_source 字段。打开模型文件加了一行 jd_source: Mapped[str | None]，push 代码。

同事拉下代码一跑——崩了。 因为他的数据库里根本没有 jd_source 这一列。

这就是"代码改了，表没改"的经典问题。迁移脚本就是解决这个的。

1.2 什么是迁移脚本

迁移脚本 = 数据库结构的 Git commit。它不是"改数据库"，而是描述怎么改数据库——一个可重复执行的程序。

类比：

一句话：任何人、任何环境，跑同一份迁移脚本，结果一样。

1.3 Alembic 工作流

Alembic 是 SQLAlchemy 官方的迁移工具，核心流程：

改了模型代码 → alembic autogenerate → 生成迁移脚本 → 审查脚本 → alembic upgrade head

自动生成不是黑盒，它产出一个 Python 文件，可以读、可以改：

# 概念示意：Alembic 迁移脚本结构
def upgrade():
    op.add_column('jd_record', sa.Column('jd_source', sa.String(200), nullable=True))

def downgrade():
    op.drop_column('jd_record', 'jd_source')

upgrade() 是"往前改"（加字段），downgrade() 是"往后撤"（删字段）。

阶段 3 只要求理解概念，不强制接入 Alembic。SQLite 先跑通功能闭环，等换 PostgreSQL 或多人协作时再接入。

1.4 索引——加不加？怎么判断

核心规则：索引加不加 = 看这个字段是不是 WHERE / ORDER BY / 范围查询的条件列。

以 JdRecord 表为例：

一句话：索引加速的是"找"（WHERE），不是"取"（SELECT）。大文本字段的搜索 ≠ 加普通索引。

二、JD 分析接口自动存库

2.1 WHY：分析完不存，等于白分析

之前的 /analyze 接口：

# 旧版：调 LLM → 返回结果 → 什么都没留下
async def analyze(req, llm_service):
    result = await llm_service.analyze_jd(req.jd_text)
    return result

收了 JD、调了 LLM、花了 token——结果丢进空气。第二天想不起来分析过哪个岗位，查不到历史。

这一步做的事：分析成功 → 自动存库 → 下次能查。

2.2 成功路径（完整链路）

用户 POST JD
  → Pydantic 校验请求体（非空 + 长度）
  → llm_service.analyze_jd()  调 LLM → 围栏清洗 → Pydantic 校验
  → 拿到 JdAnalysisResult（job_title / skills 等）
  → repo.create(session, jd_text, analysis_result, job_title, status="success")
  → db.commit()  ← 原子提交
  → return JdAnalysisResult

2.3 失败路径

用户 POST JD
  → llm_service.analyze_jd() 抛异常（RateLimitError / 坏JSON / 校验失败）
  → 不写库（不留 status="success" 的假记录）
  → 全局异常处理器捕获 → 返回 502/503 错误

2.4 事务处理——存库失败怎么办

分析成功但存库时 db.commit() 抛异常（比如磁盘满）怎么办？

# app/api/routes/jd.py — 路由层统一控制事务边界
repo = JdRecordRepository()
try:
    repo.create(session=db, jd_text=req.jd_text,
                analysis_result=result.model_dump(),
                job_title=result.job_title, status="success")
    db.commit()          # 尝试提交
except Exception:
    db.rollback()        # 回滚掉半写入的记录
    raise                # 异常继续向上抛，用户看到错误而非"假成功"

关键原则：

• 存库成功 → 才返回成功。存库失败 → 返回错误。
• 不能让用户收到"分析成功"但数据库里没有——这比直接报错更糟糕。
• rollback() 保证事务原子性：要么全写入、要么全不写。

2.5 为什么 Repository 不自己 commit

JdRecordRepository.create() 只执行 session.add()，不调用 session.commit()。

原因：一个业务操作可能跨多个 Repository 方法。

比如以后可能写"分析 JD → 存记录 → 存标签"，三步要原子化——要么全写、要么全滚。如果每个 Repository 方法自己 commit，步骤 1 提交了、步骤 2 炸了，步骤 1 撤不回来。

让路由层统一控制事务边界，才是正确的设计。

三、N+1 问题

3.1 什么是 N+1 问题

假设以后加了张 jd_tags 表，每条 JD 有 3-5 个标签。要查最近 20 条 JD 并显示每条的所有标签：

# 概念示意：N+1 问题的诞生现场
records = session.query(JdRecord).limit(20).all()  # 1 条 SQL
for rec in records:
    tags = rec.tags  # 每条又触发 1 条 SQL（懒加载）

第一行 1 条 SQL 拿了 20 条记录。然后 for 循环里每访问一次 rec.tags，SQLAlchemy 就懒加载一次关联数据——20 条记录 = 20 次额外查询。

本来 1-2 条 SQL 搞定的事，变成了 1+20=21 条。100 条记录就是 101 条，1000 条直接炸。

根本原因：SQLAlchemy 默认是懒加载（lazy="select"），访问关联属性时才去查库。在循环里逐一访问 = 逐一查。

3.2 怎么解决：急性加载（Eager Loading）

selectinload()——先查主表，再一次性捞所有关联数据：

# 概念示意：selectinload 用 IN 子查询批量加载
from sqlalchemy.orm import selectinload

records = session.query(JdRecord) \
    .options(selectinload(JdRecord.tags)) \
    .limit(20).all()

# 总共 2 条 SQL，跟 N 多大无关：
#   SELECT * FROM jd_records LIMIT 20
#   SELECT * FROM jd_tags WHERE jd_id IN (主表所有 id)

selectinload 用 IN 子查询一次性拿回所有关联行，SQLAlchemy 在内存里把标签"挂"到对应记录上。始终 2 条 SQL。

joinedload()——用 JOIN 一次带回：

# 概念示意：joinedload 用 JOIN 一次查回
from sqlalchemy.orm import joinedload

records = session.query(JdRecord) \
    .options(joinedload(JdRecord.tags)) \
    .limit(20).all()

# 1 条 SQL：SELECT * FROM jd_records LEFT JOIN jd_tags ON ...

但 JOIN 会产生笛卡尔积膨胀——如果每条 JD 有 5 个标签，20 条就返回 100 行，SQLAlchemy 再去重整理。数据量大时比 selectinload 慢。

3.3 选型指南

当前项目是单表，没关联查询，所以不会踩 N+1。但以后加了外键关系，脑子里要有这根弦。

四、端到端测试

4.1 什么是端到端测试

E2E（End-to-End）= 模拟真实用户操作流程，从入口走到出口，每一步都通过 HTTP 走真链路。

单接口测试 vs 端到端测试：

4.2 E2E 流程

1. Mock LLM（不让测试真正调 OpenAI）
2. POST /analyze × 3（3 段不同 JD）→ 3 条记录存库
3. GET /records → 验证列表有 3 条
4. 从列表响应里取真实的 record_id → GET /records/{id} → 验证详情字段匹配
5. GET /records?status=success → 验证过滤生效
6. GET /records/999 → 验证 404

核心原则：每一步都通过 HTTP，不直接碰数据库。record_id 从响应里取，不硬编码。

4.3 Mock LLM 的技巧——按关键词返回不同结果

# tests/conftest.py 或 tests/test_e2e.py — FakeLlmService
class FakeLlmService:
    def __init__(self):
        self._results = {
            "Python": JdAnalysisResult(job_title="Python后端工程师", ...),
            "AI":     JdAnalysisResult(job_title="AI算法工程师", ...),
            "全栈":   JdAnalysisResult(job_title="全栈工程师", ...),
        }

    async def analyze_jd(self, jd_text: str) -> JdAnalysisResult:
        for keyword, result in self._results.items():
            if keyword in jd_text:
                return result
        return next(iter(self._results.values()))  # 兜底

三段 JD 各有辨识词（Python / AI / 全栈），FakeLlmService 按命中词返回不同结果。为什么必须不同？

因为后面验证详情时才能区分"查回来的到底是哪一条"——如果三段 JD 全部返回同一个 job_title，断言 detail["job_title"] == "XXX" 碰巧对了也不代表链路正确。

4.4 dependency_overrides 双重注入

conftest 的 override_get_db（autouse）已经把 get_db 替换为测试 session。现在再加 mock_llm fixture 替换 get_llm_service：

# tests/conftest.py — 双重依赖注入覆盖
@pytest.fixture
def mock_llm():
    service = FakeLlmService()
    app.dependency_overrides[get_llm_service] = lambda: service
    yield service
    app.dependency_overrides.pop(get_llm_service, None)  # 测试完清理

两个 key 不同（get_db vs get_llm_service），不冲突。yield 后 pop 清理，跟 conftest 的模式一致。

五、今日核心概念速记表

阶段 3 核心能力：SQL → ORM → Repository → Session 注入 → 测试隔离 → 索引设计 → 事务边界 → 自动存库 → E2E 验证。pytest 24 passed，里程碑验收通过。下阶段：LLM 能力深入 + 向量检索 + RAG。