logo cmdragon's Blog

N+1查询：数据库性能的隐形杀手与终极拯救指南

avatar
cmdragon 大乘
  2025-05-06 00:16:30   2025-05-06 00:16:30   1.8k 字  7 分钟
image image

扫描二维码
关注或者微信搜一搜：编程智域 前端至全栈交流与成长

探索数千个预构建的 AI 应用，开启你的下一个伟大创意：https://tools.cmdragon.cn/

第一章：理解N+1查询问题本质

1.1 什么是N+1查询问题？

N+1查询是ORM使用过程中常见的性能陷阱。假设我们有一个博客系统，当查询作者列表时，如果每个作者关联了多篇文章，常规查询会先获取N个作者（1次查询），然后为每个作者单独执行文章查询（N次查询），总共产生N+1次数据库查询。

示例场景：

  • 数据库包含10位作者
  • 每位作者有5篇文章
  • 常规查询会产生1（作者）+10（文章）=11次查询

1.2 问题复现与性能影响

使用Tortoise-ORM创建数据模型：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# models.py
from tortoise.models import Model
from tortoise import fields


class Author(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=50)


class Article(Model):
    id = fields.IntField(pk=True)
    title = fields.CharField(max_length=100)
    content = fields.TextField()
    author = fields.ForeignKeyField('models.Author', related_name='articles')

问题查询代码示例：

1
2
3
4
5
6
7
8
9
10
async def get_authors_with_articles():
    authors = await Author.all()
    result = []
    for author in authors:
        articles = await author.articles.all()
        result.append({
            "author": author.name,
            "articles": [a.title for a in articles]
        })
    return result

使用EXPLAIN ANALYZE分析查询计划：

1
2
3
4
5
6
7
8
9
10
11
12
-- 主查询
EXPLAIN
ANALYZE
SELECT "id", "name"
FROM "author";

-- 单个作者的文章查询
EXPLAIN
ANALYZE
SELECT "id", "title", "content"
FROM "article"
WHERE "author_id" = 1;

第二章：prefetch_related异步预加载实战

2.1 预加载机制原理

Tortoise-ORM的prefetch_related使用SQL JOIN语句在单个查询中加载关联数据。对于1:N关系，它通过以下步骤实现：

  1. 执行主查询获取所有作者
  2. 收集作者ID列表
  3. 执行关联查询获取所有相关文章
  4. 在内存中进行数据关联映射

2.2 优化后的实现方案

完整FastAPI示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
# main.py
from fastapi import FastAPI
from tortoise.contrib.fastapi import register_tortoise
from pydantic import BaseModel

app = FastAPI()


# Pydantic模型
class ArticleOut(BaseModel):
    title: str


class AuthorOut(BaseModel):
    id: int
    name: str
    articles: list[ArticleOut]

    class Config:
        orm_mode = True


# 数据库配置
DB_CONFIG = {
    "connections": {"default": "postgres://user:pass@localhost/blogdb"},
    "apps": {
        "models": {
            "models": ["models"],
            "default_connection": "default",
        }
    }
}


# 路由端点
@app.get("/authors", response_model=list[AuthorOut])
async def get_authors():
    authors = await Author.all().prefetch_related("articles")
    return [
        AuthorOut.from_orm(author)
        for author in authors
    ]


# 初始化ORM
register_tortoise(
    app,
    config=DB_CONFIG,
    generate_schemas=True,
    add_exception_handlers=True,
)

2.3 执行计划对比分析

优化后的SQL查询示例：

1
2
3
4
5
6
7
8
9
EXPLAIN
ANALYZE
SELECT a.id,
       a.name,
       ar.id,
       ar.title,
       ar.content
FROM author a
         LEFT JOIN article ar ON a.id = ar.author_id;

性能对比指标：

指标优化前 (N=10)优化后
查询次数112
平均响应时间 (ms)32045
网络往返次数112
内存占用 (KB)850650

第三章：进阶优化与最佳实践

3.1 多层预加载技巧

处理多级关联关系：

1
2
3
4
await Author.all().prefetch_related(
    "articles__comments",  # 文章关联的评论
    "profile"  # 作者个人资料
)

3.2 选择性字段加载

优化查询字段选择：

1
2
3
await Author.all().prefetch_related(
    articles=Article.all().only("title", "created_at")
)

3.3 分页与预加载结合

分页查询优化方案：

1
2
3
4
5
6
7
from tortoise.functions import Count


async def get_paginated_authors(page: int, size: int):
    return await Author.all().prefetch_related("articles")
        .offset((page - 1) * size).limit(size)
        .annotate(articles_count=Count('articles'))

课后Quiz

  1. 当处理M:N关系时，应该使用哪个预加载方法？
    A) select_related
    B) prefetch_related
    C) both
    D) none

    答案：B
    M:N关系需要使用prefetch_related，因为select_related仅适用于ForeignKey和OneToOne关系

  2. 以下哪种情况最适合使用prefetch_related？
    A) 查询单个对象及其关联的10条记录
    B) 列表页需要显示主对象及其关联的统计数量
    C) 需要实时更新的高频写入操作
    D) 需要关联5层以上的深度查询

    答案：B
    当需要批量处理关联数据时，prefetch_related能显著减少查询次数

常见报错解决方案

报错1：TortoiseORMError: Relation does not exist

  • 原因：模型未正确注册或字段名拼写错误
  • 解决：
    1. 检查register_tortoise的models配置
    2. 验证关联字段的related_name拼写
    3. 执行数据库迁移命令

报错2：OperationalError: connection closed

  • 原因：异步连接未正确关闭
  • 解决：
    1
    2
    3
    4
    5
    6
    # 在请求处理完成后手动关闭连接
    @app.middleware("http")
    async def close_connection(request, call_next):
        response = await call_next(request)
        await connections.close_all()
        return response

报错3：ValidationError: field required (type=value_error.missing)

  • 原因：Pydantic模型与ORM模型字段不匹配
  • 解决：
    1. 检查from_orm方法是否正确使用
    2. 验证response_model的字段定义
    3. 确保启用orm_mode配置

环境配置与运行

安装依赖：

1
pip install fastapi uvicorn tortoise-orm[asyncpg] pydantic

启动服务：

1
uvicorn main:app --reload --port 8000

测试端点：

1
curl http://localhost:8000/authors

余下文章内容请点击跳转至 个人博客页面 或者 扫码关注或者微信搜一搜：编程智域 前端至全栈交流与成长，阅读完整的文章：

往期文章归档：

N+1查询：数据库性能的隐形杀手与终极拯救指南
posts/bd59ee70c62e/
作者
cmdragon
发布于
2025-05-06 00:16
许可
  1. 1. 第一章：理解N+1查询问题本质
    1. 1.1. 1.1 什么是N+1查询问题？
    2. 1.2. 1.2 问题复现与性能影响
  2. 2. 第二章：prefetch_related异步预加载实战
    1. 2.1. 2.1 预加载机制原理
    2. 2.2. 2.2 优化后的实现方案
    3. 2.3. 2.3 执行计划对比分析
  3. 3. 第三章：进阶优化与最佳实践
    1. 3.1. 3.1 多层预加载技巧
    2. 3.2. 3.2 选择性字段加载
    3. 3.3. 3.3 分页与预加载结合
  4. 4. 课后Quiz
  5. 5. 常见报错解决方案
  6. 6. 环境配置与运行
    1. 6.1. 往期文章归档：
  1. 1. 第一章：理解N+1查询问题本质
    1. 1.1. 1.1 什么是N+1查询问题？
    2. 1.2. 1.2 问题复现与性能影响
  2. 2. 第二章：prefetch_related异步预加载实战
    1. 2.1. 2.1 预加载机制原理
    2. 2.2. 2.2 优化后的实现方案
    3. 2.3. 2.3 执行计划对比分析
  3. 3. 第三章：进阶优化与最佳实践
    1. 3.1. 3.1 多层预加载技巧
    2. 3.2. 3.2 选择性字段加载
    3. 3.3. 3.3 分页与预加载结合
  4. 4. 课后Quiz
  5. 5. 常见报错解决方案
  6. 6. 环境配置与运行
    1. 6.1. 往期文章归档：