一、问题背景

事情是这样的。我们有个订单服务,技术栈是 FastAPI + SQLAlchemy + PostgreSQL + Redis,部署在 4C8G 的容器里,跑在 K8s 上。其中一个核心接口 GET /api/v1/orders/summary,负责返回某个用户最近 30 天的订单汇总,包括订单列表、每单的商品明细、以及统计信息(总金额、订单数、待付款数)。

这个接口在测试环境一直没人管,因为数据量小,测出来也就 100 多毫秒。但上线后随着用户量增长,P95 一路飙到 1.2s,高峰期数据库 CPU 直接干到 85%,接口超时告警天天响。运维第一反应是加副本,从 2 个 Pod 扩到 6 个,结果数据库更扛不住了——因为瓶颈根本不在应用层。

我先贴一下当时的压测环境,后面所有数据都基于这个环境:

  • Python 3.11.6
  • FastAPI 0.109.2
  • SQLAlchemy 2.0.25(async 模式,asyncpg 0.29.0)
  • PostgreSQL 15.4
  • Redis 7.2.3
  • uvicorn 0.27.0,--workers 4
  • 压测工具:wrk 4.2.0,wrk -t4 -c100 -d60s
  • 测试数据:单用户 30 天内约 120 个订单,每单平均 4 个商品明细

二、定位:先用 py-spy 看火焰图,别瞎猜

我的原则是:没有 profiling 数据的优化都是玄学。第一步不是改代码,是搞清楚时间花在哪。

应用层我用 py-spy,它最大的好处是可以在不重启、不改代码的情况下 attach 到运行中的进程,对线上服务很友好:

# 安装
pip install py-spy==0.3.14

# 找到 uvicorn worker 的 PID
ps aux | grep uvicorn

# 采样 60 秒,生成火焰图
py-spy record -o profile.svg --pid 12345 --duration 60 --rate 100

火焰图出来后,结论非常清晰:大约 70% 的采样落在 asyncpg 的 fetch 调用上,而且调用栈里同一个 select_order_items 的查询被反复执行。这就是典型的 N+1 查询——先查 120 个订单,然后对每个订单单独查一次商品明细,等于一次请求打了 121 次数据库。

数据库侧我用 PostgreSQL 自带的 pg_stat_statements 交叉验证:

-- 需要先 CREATE EXTENSION pg_stat_statements;
SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

结果排第一的就是那条商品明细查询,calls 高达 240 万次,mean_exec_time 3.8ms。单看 3.8ms 不吓人,但乘以 121 次就是 460ms,再加上网络往返和 ORM 序列化开销,1.2s 就凑出来了。

这里有个细节值得说:我用的是 SQLAlchemy 2.0 的 async 模式,selectinload 和 joinedload 都能解决 N+1,但两者行为不同。joinedload 会生成一个大 JOIN,数据量大的时候结果集膨胀严重;selectinload 是拆成两条查询,用 IN 批量拉取,更适合一对多场景。我选了 selectinload。

三、方案设计

定位清楚后,方案分三层,从下往上:

第一层:SQL 与索引。 把 N+1 干掉,改成批量查询;同时给高频查询条件补复合索引。我看了下原表,orders 表有 800 万行,order_items 有 3200 万行,只建了主键索引和 user_id 单列索引。查询条件是 user_id = ? AND created_at >= ? ORDER BY created_at DESC,单列索引在回表排序时效率很差,需要 (user_id, created_at) 复合索引。

第二层:ORM 查询重构。 用 selectinload 预加载明细,统计信息用一条聚合 SQL 一次算完,不要拉回 Python 里循环累加。

第三层:缓存。 订单汇总这种读多写少的数据,天然适合缓存。策略是 Redis 存 60 秒,key 用 order_summary:{user_id}:{date},写入订单时主动删 key(cache-aside + 主动失效)。60 秒是我实测出来的折中值——业务上能接受 1 分钟延迟,同时缓存命中率能到 90% 以上。

另外补一句:先做 SQL 优化再上缓存。很多人上来就加 Redis,结果缓存穿透时数据库直接被打穿。SQL 优化后单次请求从 460ms 的数据库耗时降到 20ms 以内,这时候再加缓存才是锦上添花。

四、核心实现

4.1 索引

-- 复合索引,覆盖 user_id 过滤 + created_at 排序
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC);

-- order_items 按 order_id 批量查,补一个索引
CREATE INDEX CONCURRENTLY idx_order_items_order_id
ON order_items (order_id);

注意用 CONCURRENTLY,800 万行的表直接 CREATE INDEX 会锁表,线上绝对不能这么干。代价是建索引慢一点(这张表跑了大概 4 分钟),但不影响读写。

4.2 ORM 查询重构

改造前的代码(简化版,问题一目了然):

# 改造前:N+1 重灾区
async def get_order_summary_bad(user_id: int, db: AsyncSession):
    result = await db.execute(
        select(Order).where(
            Order.user_id == user_id,
            Order.created_at >= datetime.now() - timedelta(days=30),
        ).order_by(Order.created_at.desc())
    )
    orders = result.scalars().all()

    total_amount = 0
    pending_count = 0
    for order in orders:
        # 每循环一次打一次数据库,120 个订单 = 120 次查询
        items = await db.execute(
            select(OrderItem).where(OrderItem.order_id == order.id)
        )
        order.items = items.scalars().all()
        total_amount += order.amount
        if order.status == "pending":
            pending_count += 1
    return {"orders": orders, "total": total_amount, "pending": pending_count}

改造后:

from sqlalchemy.orm import selectinload
from sqlalchemy import select, func

async def get_order_summary(user_id: int, db: AsyncSession):
    since = datetime.now() - timedelta(days=30)

    # 一条查询拉订单 + selectinload 自动批量拉明细(共 2 条 SQL)
    stmt = (
        select(Order)
        .options(selectinload(Order.items))
        .where(Order.user_id == user_id, Order.created_at >= since)
        .order_by(Order.created_at.desc())
        .limit(200)  # 防御性上限,防止极端用户拖垮接口
    )
    orders = (await db.execute(stmt)).scalars().all()

    # 统计用聚合 SQL 一次算完,不拉数据回 Python
    stat_stmt = select(
        func.coalesce(func.sum(Order.amount), 0).label("total"),
        func.count(Order.id).label("count"),
        func.count(Order.id).filter(Order.status == "pending").label("pending"),
    ).where(Order.user_id == user_id, Order.created_at >= since)
    stat = (await db.execute(stat_stmt)).one()

    return {
        "orders": orders,
        "total": float(stat.total),
        "count": stat.count,
        "pending": stat.pending,
    }

这里提一句 func.count(...).filter(...),这是 PostgreSQL 的 FILTER 子句,SQLAlchemy 2.0 原生支持,比 SUM(CASE WHEN ...) 写法干净得多,执行计划也更优。

4.3 缓存层

import json
from redis.asyncio import Redis

redis = Redis(host="redis.internal", port=6379, decode_responses=True)
CACHE_TTL = 60

async def get_order_summary_cached(user_id: int, db: AsyncSession):
    cache_key = f"order_summary:{user_id}:{datetime.now():%Y%m%d}"

    cached = await redis.get(cache_key)
    if cached:
        return json.loads(cached)

    data = await get_order_summary(user_id, db)
    # 序列化时排除 ORM 内部字段,这里用 Pydantic 模型 dump 更稳妥
    payload = OrderSummarySchema.model_validate(data).model_dump(mode="json")
    await redis.set(cache_key, json.dumps(payload), ex=CACHE_TTL)
    return payload

写路径的失效逻辑放在订单创建/状态变更的地方,await redis.delete(f"order_summary:{user_id}:*") 用 SCAN 匹配删除,别用 KEYS(会阻塞 Redis 主线程)。

五、踩坑与优化

坑 1:selectinload 的 IN 查询参数上限。 订单数超过 1000 时,asyncpg 生成的 IN (...) 参数过多会报错。我加了 limit(200) 兜底,业务上也合理——没有用户需要一次看 30 天里 200 单以上的汇总。

坑 2:缓存序列化把 ORM 对象直接 json.dumps。 一开始直接 dump ORM 对象,报 Object of type Order is not JSON serializable。后来统一走 Pydantic schema 的 model_dump(mode="json"),顺便把响应格式也规范了。

坑 3:索引建完没生效。 建完复合索引后 EXPLAIN ANALYZE 一看还是走 Seq Scan。原因是统计信息没更新,跑了一次 ANALYZE orders; 后优化器才选对索引。这个坑很常见,建完索引记得 ANALYZE。

坑 4:uvicorn worker 数和数据库连接池不匹配。 4 个 worker,每个 SQLAlchemy 连接池 pool_size=10, max_overflow=20,理论最大连接 120,但 PostgreSQL max_connections 只有 100,高峰期直接报 too many clients。最后把 pool_size 降到 5,max_overflow 降到 10,单 worker 最大 15 连接,4 个 worker 共 60,留足余量。

坑 5:缓存雪崩。 60 秒 TTL 是固定值,同一批用户可能同时过期。给 TTL 加了 ±10 秒的随机抖动:ex=CACHE_TTL + random.randint(-10, 10)。

六、效果数据

优化分三阶段压测,每阶段都是 wrk -t4 -c100 -d60s,同一台压测机、同一份数据:

阶段 P50 P95 P99 QPS DB CPU
优化前 680ms 1210ms 1850ms 45 85%
干掉 N+1 + 索引 95ms 180ms 260ms 310 38%
加 Redis 缓存 22ms 78ms 130ms 620 22%

几个观察:

  1. 索引 + N+1 修复贡献了最大的一跳,P95 从 1210ms 降到 180ms,降幅 85%。这一步没加任何缓存,纯靠 SQL 层。
  2. 缓存把 QPS 又翻了一倍,从 310 到 620,DB CPU 从 38% 降到 22%。缓存命中率实测 91.3%(Redis INFO stats 里的 keyspace_hits / (hits + misses))。
  3. 缓存加完后,我又把 Pod 从 6 个缩回 3 个,资源反而更宽裕了。之前扩容纯粹是在给 N+1 查询买单。

七、总结

这次调优最大的体会是:扩容不是性能优化的替代品。数据库被 N+1 查询打满的时候,加应用副本只会让数据库死得更快。正确的顺序永远是:profiling 定位 → SQL 和索引 → 代码逻辑 → 缓存 → 最后才考虑横向扩容。

另外分享两个我觉得最值钱的经验:

  • py-spy 火焰图 + pg_stat_statements 这两件套,基本能覆盖 90% 的 API 性能问题,而且都能在线上低开销使用,值得每个后端同学备着。
  • 优化要有基线数据。我每个阶段都留了压测记录,这样既能证明优化有效,也能在后续回归时快速发现性能退化。

如果你们也有类似的接口,建议先别急着上缓存,跑一遍 pg_stat_statements,说不定一半的问题都在那几条 mean_exec_time 排前面的 SQL 里。