Django Admin中prefetch_related无效，仍产生大量SQL查询如何解决？

问题根源分析 #

1. 重复定义多对多关系：ObjectList和SectionList双向都定义了ManyToManyField，生成两张独立中间表，冗余且干扰预取逻辑的关联。
2. 多对多选项未预取关联：编辑ObjectList时，Admin渲染sections字段的选项列表会单独查询所有SectionList实例，每个实例的__str__调用clean_sections.name，未预取时触发N+1查询。
3. 空关联未处理：SectionList的clean_sections允许为null，直接调用self.clean_sections.name存在报错风险，也可能间接影响查询优化。

具体修复步骤 #

1. 清理重复的多对多关系 #

修改models.py，删除SectionList中重复的object字段，利用反向关联访问即可：

class ObjectList(models.Model):
    uuid = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=True)
    name = models.CharField(max_length=250)
    # 仅保留单侧多对多定义，通过related_name实现反向访问
    sections = models.ManyToManyField('SectionList', related_name='object_sections', blank=True)


class CleanSections(models.Model):
    uuid = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=True)
    name = models.CharField(max_length=120)

    def __str__(self):
        return self.name


class SectionList(models.Model):
    uuid = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=True)
    # 删除重复的object字段，反向访问用：section.object_sections.all()
    clean_sections = models.ForeignKey(CleanSections, to_field='uuid', on_delete=models.CASCADE, null=True,
                                       related_name='sections_clean_name')

    def __str__(self):
        # 处理clean_sections为null的情况
        return self.clean_sections.name if self.clean_sections else "未设置分类"

2. 优化Admin中多对多字段的查询集 #

修改admin.py，同时优化ObjectList查询集和sections选项的预取逻辑：

from django.contrib import admin
from .models import ObjectList, SectionList

class ObjectListAdmin(admin.ModelAdmin):
    list_display = ('uuid', 'name')
    list_filter = ('name',)
    search_fields = ('uuid', 'name')
    ordering = ('uuid', 'name')
    fields = ('uuid', 'name', 'sections')

    def get_queryset(self, request):
        # 预取ObjectList关联的sections及对应的clean_sections
        return super().get_queryset(request).prefetch_related('sections__clean_sections')

    def formfield_for_manytomany(self, db_field, request, **kwargs):
        if db_field.name == 'sections':
            # 为sections选项列表预取clean_sections，避免N+1查询
            kwargs['queryset'] = SectionList.objects.select_related('clean_sections')
        return super().formfield_for_manytomany(db_field, request, **kwargs)

admin.site.register(ObjectList, ObjectListAdmin)

3. 可选：列表页展示关联字段（如需要） #

如果要在list_display中展示关联的sections信息，自定义方法即可，已有的预取逻辑会自动生效：

class ObjectListAdmin(admin.ModelAdmin):
    # ... 其他配置不变
    list_display = ('uuid', 'name', 'get_sections')

    def get_sections(self, obj):
        return ", ".join([str(section) for section in obj.sections.all()])
    get_sections.short_description = "关联分类"

效果验证 #

修改后，列表页、详情页、编辑页的SQL查询次数会从200+降至固定的3-4次，彻底解决N+1查询问题。

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