优化Django中Brand-Categories多对多关系更新性能的问询

嘿，面对22万条Product、4000+Brand的数据量，原代码跑2小时太正常了——核心问题是循环里频繁触发数据库查询，导致了严重的N+1性能问题，而且你用的prefetch_related没真正发挥作用，咱们一步步来优化。

先拆解现有代码的性能瓶颈 #

1. 多次重复查询：循环里的products.filter(category=category).exists()和products.filter(brand=brand).distinct('category')，每一次循环都会发起新的SQL查询，4000+Brand乘以每个Brand下的Category数量，查询量直接爆炸。
2. 预取未被有效利用：虽然加了prefetch_related('categories')，但后续的判断逻辑还是直接查数据库，预取的Category数据只用来遍历，没帮你减少查询次数。
3. distinct操作耗时：在大表上频繁执行distinct('category')会带来额外的数据库计算开销。

优化思路：用数据库聚合+批量操作替代Python循环 #

核心是把大部分计算逻辑交给数据库，减少Python和数据库之间的交互次数，最后用批量操作一次性更新多对多关系，具体步骤如下：

步骤1：一次性获取所有Brand的有效Category（即有Product关联的Category） #

用Django的聚合查询，直接从数据库拿到每个Brand对应的有效Category集合：

from django.db.models import Count
from collections import defaultdict

# 从Product表聚合出每个Brand关联的Category（仅保留有Product的）
brand_valid_categories = (
    Product.objects.values('brand__brand_name', 'category__category_name')
    .annotate(product_count=Count('id'))
    .filter(product_count__gt=0)
    .values_list('brand__brand_name', 'category__category_name')
)

# 转换成字典：key=brand_name，value=该Brand的有效Category名称集合
brand_to_valid_cats = defaultdict(set)
for brand_name, cat_name in brand_valid_categories:
    brand_to_valid_cats[brand_name].add(cat_name)

步骤2：获取所有Brand当前的Category关联 #

用prefetch_related一次性拉取所有Brand和它们的Category，避免多次查询：

# 预取所有Brand的Category，转成字典
brand_current_cats = defaultdict(set)
for brand in Brand.objects.prefetch_related('categories'):
    brand_current_cats[brand.brand_name] = {cat.category_name for cat in brand.categories.all()}

步骤3：提前构建名称到ID的映射 #

避免在循环里频繁查询数据库获取ID：

# Category名称→ID的映射
cat_name_to_id = {cat.category_name: cat.id for cat in Category.objects.all()}
# Brand名称→ID的映射
brand_name_to_id = {brand.brand_name: brand.id for brand in Brand.objects.all()}

步骤4：计算需要添加/移除的关联 #

对比当前关联和有效关联，找出差异：

to_remove = []  # 格式：[(brand_id, category_id), ...]
to_add = []     # 格式：[{'brand_id': xxx, 'category_id': xxx}, ...]

for brand_name, current_cats in brand_current_cats.items():
    valid_cats = brand_to_valid_cats.get(brand_name, set())
    brand_id = brand_name_to_id[brand_name]
    
    # 找出需要移除的关联：当前有但无Product的Category
    for cat_name in current_cats - valid_cats:
        cat_id = cat_name_to_id[cat_name]
        to_remove.append((brand_id, cat_id))
    
    # 找出需要添加的关联：有Product但当前未关联的Category
    for cat_name in valid_cats - current_cats:
        cat_id = cat_name_to_id[cat_name]
        to_add.append({'brand_id': brand_id, 'category_id': cat_id})

步骤5：批量更新多对多关系 #

用批量操作替代逐个add/remove，大幅减少SQL查询次数：

from django.db import connections, transaction

with transaction.atomic():  # 保证操作原子性，出错时回滚
    # 批量删除无效关联
    intermediate_table = Brand.categories.through._meta.db_table  # 获取多对多中间表名
    if to_remove:
        with connections['default'].cursor() as cursor:
            # 构造批量删除的SQL，用IN子句一次性处理
            placeholders = ','.join(['(%s, %s)'] * len(to_remove))
            flat_remove = [item for pair in to_remove for item in pair]
            cursor.execute(
                f"DELETE FROM {intermediate_table} WHERE (brand_id, category_id) IN ({placeholders})",
                flat_remove
            )
    
    # 批量添加新关联
    if to_add:
        Brand.categories.through.objects.bulk_create(
            [Brand.categories.through(**item) for item in to_add],
            batch_size=1000  # 根据数据库性能调整，比如MySQL建议1000-2000
        )

为什么原来的prefetch_related没生效？ #

你虽然加了prefetch_related('categories')，但后续的核心判断逻辑（比如products.filter(...)）都是直接查询数据库，没有利用预取的内存数据。预取的Category只是用来遍历，并没有减少你和数据库的交互次数，所以没起到优化作用。

额外优化小贴士 #

1. 尽量用values/values_list：减少Django模型实例的创建，节省内存和初始化时间。
2. 调整batch_size：bulk_create的batch_size不要太大，避免数据库超时，根据你的数据库配置调整。
3. 避开业务高峰执行：批量操作会占用数据库资源，建议在低峰期运行。

内容的提问来源于stack exchange，提问作者Chiefir