第29章：【SQLAlchemy 中级篇综合实战】异步多租户订单服务

一、项目背景

“新客户要求系统支持 5000 个租户、峰值 QPS 2000、P99 < 200ms——但我们现在的同步单租户架构完全扛不住。”

星云订单中台面临商业化落地的关键节点：从单租户内部系统升级为多租户 SaaS 平台。新客户的 SLA 要求模型让团队感到压力——现有架构存在五个硬伤：

1. 同步阻塞：FastAPI 接口每个请求都等待同步 SQLAlchemy 查询完成——2000 QPS 下每请求只能用 0.5ms 的等待时间（含 CPU 时间），现有的同步数据库查询平均耗时 15ms，根本无法满足。
2. 租户隔离缺失：全部订单共用一个订单表，查询时靠手动 WHERE tenant_id——已有 3 次因忘记加过滤条件导致跨租户数据泄露的线上事故。
3. 连接池失控：默认 pool_size=5, max_overflow=10——30 个 API Worker 峰值要 450 个连接，经常打满 PostgreSQL 的 max_connections。
4. 无审计：订单状态变更无记录——客户投诉"订单状态被改了"时无从追溯。
5. N+1 泛滥：订单列表页包含用户信息、明细、商品分类——懒加载策略导致每页 20 条订单产生 200+ 条 SQL。

更糟糕的是——这五个问题之间相互耦合。异步改造需要重新设计 Session 管理；租户过滤需要与异步的 contextvars 配合；连接池参数需要在异步引擎下重新测试；审计事件在异步 flush 下的行为也需重新验证。

本章是中级篇的综合实战章节，将把第 17-28 章学到的所有技能整合为一个完整的异步多租户订单服务。验收标准：所有核心用例测试全绿、无 N+1 查询、连接池不泄漏、跨租户读取被正确隔离、Alembic 迁移可完整回滚。

二、项目设计

场景一：架构选型

周一下午，大师在白板上画出了新架构的整体设计。小胖拿着奶茶过来，看到白板上密密麻麻的连线有点晕。

小胖：“大师，这架构图上的线比我的外卖订单图还复杂——异步、多租户、审计、连接池……能不能一个一个来？”

大师：“不能。因为它们互相依赖。异步改了 Session 管理方式，租户过滤需要 contextvars 在异步环境中正确传递，连接池在异步引擎下的参数也不同。拆开来做的话，合起来会有大量集成 bug。”

小白：“那我们从哪一层开始？”

大师：“底线向上——先确定数据层，再往上搭业务层。”

# 线程安全的租户上下文（asyncio 兼容）
import contextvars
current_tenant: contextvars.ContextVar[str] = contextvars.ContextVar("tenant", default="")

# 异步引擎 + 连接池
engine = create_async_engine(
    DATABASE_URL,
    pool_size=5,           # 异步连接复用
    max_overflow=10,
    pool_pre_ping=True,
    pool_recycle=3600,
    echo_pool=False,
)
AsyncSessionFactory = async_sessionmaker(engine, expire_on_commit=False)

小胖：“技术映射：新架构 = 新建摩天大楼——地基（异步引擎）→ 管道（连接池）→ 门禁（租户过滤）→ 摄像（审计）→ 电梯（加载策略）——每层都依赖下层。”

小白：“异步模式下 Session 的生命周期应该怎么管理？是每个请求一个新 Session，还是可以用依赖注入复用？”

大师：“FastAPI 的 Depends 是一个天然的 Session 生命周期管理器。”

async def get_session():
    async with AsyncSessionFactory() as session:
        yield session
        # 请求结束后自动 close，连接归还池

@app.post("/orders")
async def create_order(session: AsyncSession = Depends(get_session)):
    # session 在请求处理期间有效
    pass

大师：“但要注意——Depends 的 yield 是在响应返回后执行的，只能做清理工作。如果想在 commit 后做 after_commit 通知，需要在中间件或事件中处理。”

场景二：租户过滤 + 异步上下文传递

小胖：“租户 ID 怎么从 HTTP header 传到数据库查询？用 ThreadLocal？”

大师：“contextvars——它是 Python 3.7+ 的标准库，在 asyncio 中是 Task 级别的隔离，不会跨协程串数据。你可以在 FastAPI 中间件中从 X-Tenant-ID header 读取并设置。”

# 中间件
@app.middleware("http")
async def tenant_middleware(request: Request, call_next):
    tenant_id = request.headers.get("X-Tenant-ID", "")
    token = current_tenant.set(tenant_id)
    try:
        response = await call_next(request)
        return response
    finally:
        current_tenant.reset(token)

小白：“那 do_orm_execute 事件在异步 Session 中能正常工作吗？”

大师：“完全正常——事件系统对同步和异步是一致的。唯一区别是——异步引擎的事件处理器也应该是同步的（@event.listens_for 不需要 async def——它的触发点是在引擎内部同步调用链路中）。”

场景三：加载策略与性能目标

大师：“最后是加载策略——订单列表页（20 条订单）的 SQL 目标：不超过 4 条。”

# 订单列表页加载方案
stmt = (
    select(Order)
    .options(
        joinedload(Order.user),    # 多对一 → 1 条 JOIN 搞定
        selectinload(Order.items), # 一对多 → 1 条 IN 搞定
    )
    .order_by(Order.created_at.desc())
    .limit(20)
)
# SQL 数量：1 主查询 + 1 selectinload(items) = 2 条 SQL

小胖：“技术映射：异步架构 = 外卖骑手（asyncio）+ 专属餐柜（contextvars）+ 智能分单（连接池）+ 订单跟踪（审计）——缺一不可。”

三、项目实战

3.1 环境准备与项目骨架

# 依赖安装
pip install "sqlalchemy[asyncio]>=2.0" asyncpg alembic fastapi uvicorn pytest pytest-asyncio httpx

"""ch29_async_multitenant/main.py —— 异步多租户订单服务"""

# =============================================
# 项目目录结构
# =============================================
"""
async_multitenant/
├── app/
│   ├── __init__.py
│   ├── main.py              # FastAPI 应用入口
│   ├── config.py            # 配置管理
│   ├── db/
│   │   ├── __init__.py
│   │   ├── engine.py        # 异步引擎 + Session 工厂
│   │   ├── base.py          # ORM 声明基类
│   │   └── events.py        # 全局事件（审计、租户过滤）
│   ├── models/
│   │   ├── __init__.py
│   │   ├── mixins.py        # TenantMixin, SoftDeleteMixin
│   │   ├── order.py         # Order, OrderItem
│   │   └── audit.py         # AuditLog
│   ├── services/
│   │   ├── __init__.py
│   │   └── order_service.py # 下单业务逻辑
│   ├── api/
│   │   ├── __init__.py
│   │   └── orders.py        # 订单 API 路由
│   └── middleware/
│       ├── __init__.py
│       └── tenant.py        # 租户中间件
├── alembic/
│   └── versions/            # 迁移脚本
├── tests/
│   ├── conftest.py          # 测试 Fixture
│   ├── test_orders.py       # 订单核心用例
│   └── test_tenant_isolation.py # 租户隔离测试
└── requirements.txt
"""

3.2 数据层：异步引擎、连接池与基类

# ===== app/config.py =====
import os
from pydantic_settings import BaseSettings

class Settings(BaseSettings):
    database_url: str = "postgresql+asyncpg://username:password@localhost:5432/order_center"
    pool_size: int = 5
    max_overflow: int = 10
    pool_timeout: int = 30
    pool_recycle: int = 3600
    pool_pre_ping: bool = True
    echo_sql: bool = False
    slow_sql_threshold_ms: int = 300

    class Config:
        env_file = ".env"

settings = Settings()

# ===== app/db/engine.py =====
from sqlalchemy.ext.asyncio import (
    create_async_engine, AsyncSession, async_sessionmaker,
)
from app.config import settings

engine = create_async_engine(
    settings.database_url,
    pool_size=settings.pool_size,
    max_overflow=settings.max_overflow,
    pool_timeout=settings.pool_timeout,
    pool_recycle=settings.pool_recycle,
    pool_pre_ping=settings.pool_pre_ping,
    echo=settings.echo_sql,
)

AsyncSessionFactory = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
)

async def get_async_session():
    """FastAPI Depends 使用的 Session 依赖注入"""
    async with AsyncSessionFactory() as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise

# ===== app/db/base.py =====
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from datetime import datetime
from sqlalchemy import func

class Base(DeclarativeBase):
    pass

class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(
        server_default=func.now()
    )
    updated_at: Mapped[datetime | None] = mapped_column(
        onupdate=func.now(), nullable=True
    )

3.3 模型层：Mixin、实体与审计日志

# ===== app/models/mixins.py =====
from sqlalchemy import String, DateTime, Index
from sqlalchemy.orm import Mapped, mapped_column
from datetime import datetime
from typing import Optional

class TenantMixin:
    """多租户 Mixin——所有需要租户隔离的表继承此 Mixin"""
    tenant_id: Mapped[str] = mapped_column(
        String(50), nullable=False, index=True
    )

class SoftDeleteMixin:
    """软删除 Mixin"""
    deleted_at: Mapped[datetime | None] = mapped_column(
        DateTime, nullable=True, default=None, index=True
    )

# ===== app/models/order.py =====
from sqlalchemy import String, Integer, Numeric, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.db.base import Base, TimestampMixin
from app.models.mixins import TenantMixin, SoftDeleteMixin
from typing import List
from datetime import datetime

class Order(Base, TimestampMixin, TenantMixin, SoftDeleteMixin):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    order_no: Mapped[str] = mapped_column(String(32), index=True)
    user_id: Mapped[int] = mapped_column(Integer, index=True)
    total_amount: Mapped[float] = mapped_column(Numeric(12, 2))
    status: Mapped[str] = mapped_column(String(20), default="pending")

    items: Mapped[List["OrderItem"]] = relationship(
        back_populates="order",
        cascade="all, delete-orphan",
        passive_deletes=True,
    )

    __table_args__ = (
        Index("idx_orders_tenant_status", "tenant_id", "status", "deleted_at"),
    )

class OrderItem(Base, TenantMixin, SoftDeleteMixin):
    __tablename__ = "order_items"

    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(
        ForeignKey("orders.id", ondelete="CASCADE"),
        index=True,
    )
    product_name: Mapped[str] = mapped_column(String(200))
    unit_price: Mapped[float] = mapped_column(Numeric(12, 2))
    quantity: Mapped[int] = mapped_column(Integer)

    order: Mapped["Order"] = relationship(back_populates="items")

# ===== app/models/audit.py =====
from sqlalchemy import String, Integer, DateTime, Text
from sqlalchemy.orm import Mapped, mapped_column
from app.db.base import Base
from app.models.mixins import TenantMixin
from datetime import datetime
from sqlalchemy import func

class AuditLog(Base, TenantMixin):
    __tablename__ = "audit_logs"

    id: Mapped[int] = mapped_column(primary_key=True)
    entity_type: Mapped[str] = mapped_column(String(50))
    entity_id: Mapped[int | None] = mapped_column(Integer, nullable=True)
    field_name: Mapped[str] = mapped_column(String(50))
    old_value: Mapped[str | None] = mapped_column(Text, nullable=True)
    new_value: Mapped[str | None] = mapped_column(Text, nullable=True)
    changed_at: Mapped[datetime] = mapped_column(
        DateTime, server_default=func.now()
    )

3.4 全局事件：租户过滤 + 审计 + 慢 SQL 检测

# ===== app/db/events.py =====
import time
import json
import logging
from sqlalchemy import event, inspect, select
from sqlalchemy.orm import with_loader_criteria
from sqlalchemy.ext.asyncio import AsyncSession, AsyncEngine

from app.models.mixins import TenantMixin, SoftDeleteMixin
from app.models.audit import AuditLog
from app.config import settings

# --- 租户上下文 ---
import contextvars
current_tenant: contextvars.ContextVar[str] = contextvars.ContextVar(
    "tenant_id", default=""
)

logger = logging.getLogger("sqlalchemy")

# =============================================
# 1. 租户全局过滤
# =============================================

def register_tenant_filter():
    """在 AsyncSession 上注册 do_orm_execute 事件，注入租户/软删除过滤"""
    @event.listens_for(AsyncSession, "do_orm_execute")
    def apply_filters(execute_state):
        if not execute_state.is_select:
            return

        tid = current_tenant.get()
        skip_tenant = execute_state.execution_options.get("skip_tenant_filter", False)
        include_deleted = execute_state.execution_options.get("include_deleted", False)

        filters = []
        if tid and not skip_tenant:
            filters.append(
                with_loader_criteria(
                    TenantMixin,
                    lambda cls: cls.tenant_id == tid,
                    include_aliases=True,
                )
            )
        if not include_deleted:
            filters.append(
                with_loader_criteria(
                    SoftDeleteMixin,
                    lambda cls: cls.deleted_at == None,
                    include_aliases=True,
                )
            )
        if filters:
            execute_state.statement = execute_state.statement.options(*filters)

# =============================================
# 2. 审计日志
# =============================================

def register_audit():
    """在 Session 上注册 before_flush 事件，自动记录字段变更"""
    @event.listens_for(AsyncSession, "before_flush")
    def audit_on_flush(session, flush_context, instances):
        for obj in session.dirty:
            if isinstance(obj, AuditLog):
                continue  # 不审计审计表自身
            insp = inspect(obj)
            for attr in insp.attrs:
                if not hasattr(attr, "history"):
                    continue
                hist = attr.history
                if not hist.has_changes():
                    continue
                old_val = str(hist.deleted[0]) if hist.deleted else None
                new_val = str(hist.added[0]) if hist.added else None
                audit = AuditLog(
                    entity_type=type(obj).__name__,
                    entity_id=getattr(obj, "id", None),
                    field_name=attr.key,
                    old_value=old_val,
                    new_value=new_val,
                    tenant_id=getattr(obj, "tenant_id", ""),
                )
                session.add(audit)

# =============================================
# 3. 慢 SQL 检测 + 结构化日志
# =============================================

def register_slow_sql_logging(target_engine: AsyncEngine):
    """在引擎上注册事件，记录慢 SQL 和 EXPLAIN 计划"""
    @event.listens_for(target_engine.sync_engine, "before_cursor_execute")
    def before_query(conn, cursor, statement, parameters, context, executemany):
        conn.info["query_start"] = time.monotonic()

    @event.listens_for(target_engine.sync_engine, "after_cursor_execute")
    def after_query(conn, cursor, statement, parameters, context, executemany):
        start = conn.info.pop("query_start", None)
        if start is None:
            return
        elapsed_ms = (time.monotonic() - start) * 1000
        if elapsed_ms > settings.slow_sql_threshold_ms:
            log_data = {
                "event": "slow_sql",
                "elapsed_ms": round(elapsed_ms, 2),
                "statement": statement[:300],
                "params": str(parameters)[:200] if parameters else "",
                "tenant_id": current_tenant.get(""),
            }
            logger.warning(json.dumps(log_data, ensure_ascii=False))

3.5 业务服务层：下单逻辑

# ===== app/services/order_service.py =====
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import joinedload, selectinload
from app.models.order import Order, OrderItem
from typing import List
import uuid

class OrderService:
    @staticmethod
    async def create_order(
        session: AsyncSession,
        user_id: int,
        items: List[dict],
        tenant_id: str,
    ) -> Order:
        total = sum(item["unit_price"] * item["quantity"] for item in items)
        order = Order(
            order_no=f"ORD-{uuid.uuid4().hex[:8].upper()}",
            user_id=user_id,
            total_amount=total,
            tenant_id=tenant_id,
        )
        session.add(order)
        await session.flush()  # 获取 order.id

        for item_data in items:
            item = OrderItem(
                order_id=order.id,
                product_name=item_data["product_name"],
                unit_price=item_data["unit_price"],
                quantity=item_data["quantity"],
                tenant_id=tenant_id,
            )
            session.add(item)

        await session.commit()
        return order

    @staticmethod
    async def list_orders(
        session: AsyncSession,
        user_id: int = None,
        status: str = None,
        page: int = 1,
        page_size: int = 20,
    ) -> List[Order]:
        stmt = select(Order).options(
            joinedload(Order.user) if hasattr(Order, "user") else None,
            selectinload(Order.items),
        ).order_by(Order.id.desc())

        conditions = []
        if user_id:
            conditions.append(Order.user_id == user_id)
        if status:
            conditions.append(Order.status == status)

        # 使用 selectinload 加载一对多关系
        stmt = stmt.options(
            selectinload(Order.items),
        )
        for cond in conditions:
            stmt = stmt.where(cond)

        stmt = stmt.limit(page_size).offset((page - 1) * page_size)
        result = await session.execute(stmt)
        return result.unique().scalars().all()

    @staticmethod
    async def update_status(
        session: AsyncSession,
        order_id: int,
        new_status: str,
    ) -> Order:
        order = await session.get(Order, order_id)
        if order is None:
            raise ValueError(f"订单 {order_id} 不存在")
        old_status = order.status
        order.status = new_status
        # before_flush 事件自动记录 audit log
        await session.commit()
        return order

    @staticmethod
    async def soft_delete_order(
        session: AsyncSession,
        order_id: int,
    ) -> None:
        """软删除：标记 deleted_at 而非物理删除"""
        from datetime import datetime
        order = await session.get(Order, order_id)
        if order is None:
            raise ValueError(f"订单 {order_id} 不存在")
        order.deleted_at = datetime.now()
        session.add(order)
        await session.commit()

3.6 API 路由层：FastAPI 端点

# ===== app/api/orders.py =====
from fastapi import APIRouter, Depends, HTTPException, Header
from sqlalchemy.ext.asyncio import AsyncSession
from pydantic import BaseModel, Field
from typing import List, Optional
from app.services.order_service import OrderService
from app.db.events import current_tenant

router = APIRouter(prefix="/api/orders", tags=["orders"])

class OrderItemCreate(BaseModel):
    product_name: str
    unit_price: float
    quantity: int

class OrderCreateRequest(BaseModel):
    user_id: int
    items: List[OrderItemCreate]

class OrderItemResponse(BaseModel):
    product_name: str
    unit_price: float
    quantity: int

class OrderResponse(BaseModel):
    id: int
    order_no: str
    user_id: int
    total_amount: float
    status: str
    items: List[OrderItemResponse] = []

    class Config:
        from_attributes = True

@router.post("", response_model=OrderResponse, status_code=201)
async def create_order(
    req: OrderCreateRequest,
    session: AsyncSession = Depends(get_async_session),
    x_tenant_id: str = Header(..., alias="X-Tenant-ID"),
):
    token = current_tenant.set(x_tenant_id)
    try:
        order = await OrderService.create_order(
            session, req.user_id,
            [i.model_dump() for i in req.items],
            x_tenant_id,
        )
        return order
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))
    finally:
        current_tenant.reset(token)

@router.get("", response_model=List[OrderResponse])
async def list_orders(
    user_id: Optional[int] = None,
    status: Optional[str] = None,
    page: int = 1,
    page_size: int = 20,
    session: AsyncSession = Depends(get_async_session),
    x_tenant_id: str = Header(..., alias="X-Tenant-ID"),
):
    token = current_tenant.set(x_tenant_id)
    try:
        orders = await OrderService.list_orders(
            session, user_id=user_id, status=status,
            page=page, page_size=page_size,
        )
        return orders
    finally:
        current_tenant.reset(token)

@router.put("/{order_id}/status", response_model=OrderResponse)
async def update_order_status(
    order_id: int,
    new_status: str,
    session: AsyncSession = Depends(get_async_session),
    x_tenant_id: str = Header(..., alias="X-Tenant-ID"),
):
    token = current_tenant.set(x_tenant_id)
    try:
        order = await OrderService.update_status(session, order_id, new_status)
        return order
    except ValueError as e:
        raise HTTPException(status_code=404, detail=str(e))
    finally:
        current_tenant.reset(token)

@router.delete("/{order_id}", status_code=204)
async def delete_order(
    order_id: int,
    session: AsyncSession = Depends(get_async_session),
    x_tenant_id: str = Header(..., alias="X-Tenant-ID"),
):
    token = current_tenant.set(x_tenant_id)
    try:
        await OrderService.soft_delete_order(session, order_id)
    except ValueError as e:
        raise HTTPException(status_code=404, detail=str(e))
    finally:
        current_tenant.reset(token)

3.7 FastAPI 应用入口

# ===== app/main.py =====
from fastapi import FastAPI, Request
from app.db.engine import engine, AsyncSessionFactory
from app.db.events import (
    register_tenant_filter, register_audit,
    register_slow_sql_logging,
)
from app.api.orders import router as orders_router
from contextlib import asynccontextmanager

@asynccontextmanager
async def lifespan(app: FastAPI):
    # 启动时注册事件
    register_tenant_filter()
    register_audit()
    register_slow_sql_logging(engine)
    yield
    # 关闭时清理
    await engine.dispose()

app = FastAPI(title="异步多租户订单服务", lifespan=lifespan)

# 中间件：从 Header 提取租户信息
@app.middleware("http")
async def trace_request(request: Request, call_next):
    import uuid
    request.state.trace_id = str(uuid.uuid4())
    response = await call_next(request)
    response.headers["X-Trace-ID"] = request.state.trace_id
    return response

app.include_router(orders_router)

@app.get("/health")
async def health():
    return {"status": "ok"}

3.8 Alembic 迁移配置

# ===== alembic/env.py（简化版） =====
"""
from logging.config import fileConfig
from sqlalchemy import engine_from_config, pool
from alembic import context

config = context.config
target_metadata = Base.metadata

def run_migrations_offline():
    url = config.get_main_option("sqlalchemy.url")
    context.configure(url=url, target_metadata=target_metadata, literal_binds=True)
    with context.begin_transaction():
        context.run_migrations()

def run_migrations_online():
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata)
        with context.begin_transaction():
            context.run_migrations()
"""

# 和运维上线窗口的任务书：
ROLLOUT_PLAN = """
1. 低峰期 1:00 AM 先升级预发布环境
2. 执行 alembic upgrade head（先加 nullable 列）
3. 回填数据（data migration）
4. 执行 alembic upgrade head（加 NOT NULL 约束 + 删旧列）
5. 观察监控 15 分钟，无误后依次灰度推广到生产
6. 准备回滚脚本：alembic downgrade -1（每步都记录降级 revision）
"""

3.9 测试：租户隔离 + N+1 零容忍 + 审计验证

# ===== tests/conftest.py =====
import pytest
import pytest_asyncio
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from app.db.base import Base
from app.db.events import register_tenant_filter, register_audit, current_tenant

@pytest_asyncio.fixture
async def async_engine():
    engine = create_async_engine(
        "sqlite+aiosqlite:///:memory:",
        echo=False,
    )
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
    yield engine
    await engine.dispose()

@pytest_asyncio.fixture
async def async_session(async_engine):
    register_tenant_filter()
    register_audit()
    Factory = async_sessionmaker(async_engine, class_=AsyncSession, expire_on_commit=False)
    async with Factory() as session:
        yield session

# ===== tests/test_orders.py =====
import pytest
from sqlalchemy import select, func, event
from app.services.order_service import OrderService

@pytest.mark.asyncio
async def test_create_order_with_tenant(async_session):
    """测试下单 + 租户隔离"""
    current_tenant.set("tenant-a")
    order = await OrderService.create_order(
        async_session,
        user_id=1001,
        items=[{"product_name": "键盘", "unit_price": 299, "quantity": 2}],
        tenant_id="tenant-a",
    )
    assert order.id is not None
    assert order.tenant_id == "tenant-a"
    assert order.total_amount == 598
    assert len(order.items) == 1

@pytest.mark.asyncio
async def test_tenant_isolation(async_session):
    """测试租户 A 看不到租户 B 的数据"""
    # 创建租户 A 的数据
    current_tenant.set("ta")
    await OrderService.create_order(
        async_session, user_id=1,
        items=[{"product_name": "A商品", "unit_price": 10, "quantity": 1}],
        tenant_id="ta",
    )

    # 切换到租户 B
    current_tenant.set("tb")
    orders_tb = await OrderService.list_orders(async_session, page=1, page_size=10)
    assert len(orders_tb) == 0, "租户 B 不应看到租户 A 的订单"

    # 租户 A 应能看到自己的订单
    current_tenant.set("ta")
    orders_ta = await OrderService.list_orders(async_session, page=1, page_size=10)
    assert len(orders_ta) == 1, "租户 A 应能看到自己的订单"

@pytest.mark.asyncio
async def test_no_n_plus_one(async_session):
    """测试订单列表页无 N+1 问题（SQL 数量 ≤ 2）"""
    current_tenant.set("ta")

    # 创建 5 条订单，每条 2 个明细
    for i in range(5):
        await OrderService.create_order(
            async_session, user_id=1,
            items=[
                {"product_name": f"商品{i}-A", "unit_price": 10, "quantity": 1},
                {"product_name": f"商品{i}-B", "unit_price": 20, "quantity": 1},
            ],
            tenant_id="ta",
        )

    sql_count = 0

    @event.listens_for(async_session.sync_session.bind, "before_cursor_execute")
    def count_sql(conn, cursor, statement, parameters, context, executemany):
        nonlocal sql_count
        if statement.strip().upper().startswith("SELECT"):
            sql_count += 1

    orders = await OrderService.list_orders(async_session, page=1, page_size=20)
    assert len(orders) == 5
    # 预期 SQL 数量：1 主查询 + 1 selectinload(items) = 2 条
    assert sql_count <= 3, f"SQL 数量 {sql_count} 超过预期 (≤2)"

    event.remove(async_session.sync_session.bind, "before_cursor_execute", count_sql)

@pytest.mark.asyncio
async def test_audit_log_on_status_change(async_session):
    """测试状态变更自动记录审计日志"""
    current_tenant.set("ta")
    order = await OrderService.create_order(
        async_session, user_id=1,
        items=[{"product_name": "X", "unit_price": 10, "quantity": 1}],
        tenant_id="ta",
    )

    # 变更状态
    await OrderService.update_status(async_session, order.id, "paid")

    # 验证审计日志
    from app.models.audit import AuditLog
    stmt = select(AuditLog).where(
        AuditLog.entity_type == "Order",
        AuditLog.entity_id == order.id,
        AuditLog.field_name == "status",
    ).execution_options(skip_tenant_filter=True, include_deleted=True)
    result = await async_session.execute(stmt)
    logs = result.scalars().all()
    assert len(logs) >= 1, "状态变更应产生审计日志"
    assert logs[0].old_value == "pending"
    assert logs[0].new_value == "paid"

@pytest.mark.asyncio
async def test_soft_delete_hides_order(async_session):
    """测试软删除后查询不到"""
    current_tenant.set("ta")
    order = await OrderService.create_order(
        async_session, user_id=1,
        items=[{"product_name": "Y", "unit_price": 100, "quantity": 1}],
        tenant_id="ta",
    )
    order_id = order.id

    await OrderService.soft_delete_order(async_session, order_id)

    orders = await OrderService.list_orders(async_session)
    assert len(orders) == 0, "软删除后默认查询不应看到已删除订单"

    # 包括已删除的查询
    orders_all = await OrderService.list_orders(
        async_session.execution_options(include_deleted=True),
    )

3.10 Alembic 迁移脚本示例

# ===== alembic/versions/0001_initial.py =====
"""
revision = '0001'
down_revision = None

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table('orders',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('order_no', sa.String(32), nullable=False),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('total_amount', sa.Numeric(12,2), nullable=False),
        sa.Column('status', sa.String(20), server_default='pending'),
        sa.Column('tenant_id', sa.String(50), nullable=False),
        sa.Column('deleted_at', sa.DateTime(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
        sa.Column('updated_at', sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('idx_orders_tenant_status', 'orders',
        ['tenant_id', 'status', 'deleted_at'])

def downgrade():
    op.drop_index('idx_orders_tenant_status')
    op.drop_table('orders')
"""

可能遇到的坑及解决方法

1. asyncio + contextvars 的线程安全问题

• 现象：使用 current_tenant.set() 但在 HTTP 中间件传递时偶尔串数据。
• 根因：contextvars 在 asyncio 中是 Task 级隔离，但如果在中间件中创建新 Task（如 asyncio.create_task），子 Task 会继承父 Task 的上下文——需要显式 contextvars.ContextVar.set()。
• 解决：中间件只处理当前请求，不创建子 Task。如需异步任务，使用 Celery/ARQ 显式传递 tenant_id。

2. 异步引擎的 connect 与 begin 混用导致死锁

• 现象：在已打开的异步连接上又调用 session.begin() ——因等待已有锁而超时。
• 解决：使用 session.commit() 实现事务，不混用引擎级 connect 和 Session 级事务。

3. do_orm_execute 事件中使用了 await

• 现象：在事件处理函数中写了 await session.execute(...)，事件处理器不支持异步。
• 解决：do_orm_execute 事件处理器必须是同步函数。如果需要异步逻辑，使用同步方式在当前 session 中添加对象（session.add(log)）。

四、项目总结

架构全景图

┌────────────────────────────────────────────────────┐
│                   FastAPI 应用层                    │
│  ┌──────────┐  ┌──────────┐  ┌───────────────┐   │
│  │ 中间件    │  │ API 路由 │  │ Service 层     │   │
│  │ tenant_id │  │ /orders  │  │ OrderService   │   │
│  │ trace_id  │  │          │  │ .create/list/  │   │
│  └──────────┘  └──────────┘  └───────────────┘   │
├────────────────────────────────────────────────────┤
│               SQLAlchemy ORM 层                     │
│  ┌──────────────┐  ┌──────────┐  ┌────────────┐  │
│  │ AsyncSession │  │ 事件系统 │  │ 加载策略   │  │
│  │ + Depends    │  │+租户过滤 │  │+selectin   │  │
│  │ + contextvars│  │+审计日志 │  │+joinedload │  │
│  └──────────────┘  └──────────┘  └────────────┘  │
├────────────────────────────────────────────────────┤
│               SQLAlchemy Core 层                    │
│  ┌──────────────┐  ┌──────────┐  ┌────────────┐  │
│  │ AsyncEngine  │  │ 连接池   │  │ 慢SQL检测  │  │
│  │ + asyncpg    │  │+pre_ping │  │+EXPLAIN    │  │
│  └──────────────┘  └──────────┘  └────────────┘  │
├────────────────────────────────────────────────────┤
│               PostgreSQL 16                        │
│  orders │ order_items │ audit_logs │ alembic_version│
└────────────────────────────────────────────────────┘

技术点回顾与验收清单

注意事项

1. expire_on_commit=False 在异步模式中强烈推荐——否则 commit 后访问对象属性会触发隐式延迟加载（另一次异步查询）。
2. SQLite :memory: 在异步测试中需要 aiosqlite 驱动——pip install aiosqlite。
3. do_orm_execute 的过滤对 session.get() 不生效——需要统一使用 select(Model).where(...) 替代 session.get()。

思考题

1. 当前架构中，审计日志的写入是在 before_flush 事件中同步完成的——这意味着如果审计日志写入失败，订单操作也会回滚。如果产品需求是"审计日志失败不应影响下单"，你应该如何修改事件逻辑？尝试在 before_flush 中捕获审计写入的异常。

2. 如果未来需要支持租户的"父子层级"——如集团 A 下有 3 个子租户，查询集团 A 时需要看到所有子租户的订单。with_loader_criteria 中的过滤条件如何扩展为 tenant_id IN (list_of_child_tenants)？这种 list 每次查询时都要重新获取，如何在 do_orm_execute 中动态注入？

参考答案参见附录 E。

延伸阅读与资源

NumPy 从入门到生产落地：全链路实战指南（科学计算/向量化）
Redis 8 实战精讲：从 CRUD 到源码，构建高可用缓存系统
Redis 实战修炼与原理进阶
Python 3实战精进：从脚本到高并发订单引擎
python入门：Rquests从菜鸟脚本到企业级SDK的网络实战圣经
Milvus向量数据库实战修炼：从 0 到 1精通向量检索与生产落地
MongoDB 实战进阶与内核修炼
后端工程师的 AI 转型第一课：Ollama 与私有化大模型实战
10倍开发者的 Dify 魔法书：从零构建全栈 AI 应用
后端工程师转型AI第一课-Ollama 与私有化大模型实战
大型语言模型(LLM) vLLM 高性能推理落地实战
Agent开发之LlamaIndex 实战修炼与源码进阶
大语言模型Transformers 实战修炼与源码剖析