Django多对多表过滤产生过多SQL查询，如何优化？

问题场景 #

使用django_filters对Department模型进行过滤时，出现大量重复SQL查询，导致性能损耗。

代码实现 #

models.py #

class Branch(models.Model):
    name = models.CharField(max_length=100)

    class Meta:
        verbose_name_plural = "Branches"

    def __str__(self):
        return self.name


class Department(models.Model):
    name = models.CharField(max_length=100)
    branch = models.ManyToManyField(Branch, related_name="department")

    def __str__(self):
        return self.name

views.py #

class DepartmentListAPIView(generics.ListAPIView):
    queryset = Department.objects.all()
    serializer_class = DepartmentSerializer
    filter_backends = (filters.DjangoFilterBackend,)
    filterset_class = DepartmentFilter

serializers.py #

class DepartmentSerializer(serializers.ModelSerializer):
    class Meta:
        model = Department
        fields = "__all__"

filters.py #

class DepartmentFilter(filters.FilterSet):
    branch = filters.CharFilter(field_name="branch__id", lookup_expr="in")

    class Meta:
        model = Department
        fields = "__all__"

问题截图 #

解决方案 #

1. 预加载多对多关联数据 #

Django多对多关系默认是懒加载，每访问一个Department的branch字段都会触发一次新查询，也就是典型的N+1问题。修改视图中的queryset，用prefetch_related一次性预加载所有关联的Branch数据：

class DepartmentListAPIView(generics.ListAPIView):
    # 预加载多对多关联的branch数据，合并查询
    queryset = Department.objects.all().prefetch_related('branch')
    serializer_class = DepartmentSerializer
    filter_backends = (filters.DjangoFilterBackend,)
    filterset_class = DepartmentFilter

2. 优化FilterSet过滤字段定义 #

原CharFilter配合in查询不够适配多对多场景，改用ModelMultipleChoiceFilter更严谨，还能避免类型转换问题：

class DepartmentFilter(filters.FilterSet):
    # 用ModelMultipleChoiceFilter适配多对多ID过滤
    branch = filters.ModelMultipleChoiceFilter(queryset=Branch.objects.all(), field_name="branch__id")

    class Meta:
        model = Department
        fields = "__all__"

3. 验证优化效果 #

修改后重新请求接口，通过Django Toolbar可以看到SQL查询次数大幅减少——原来的N次Branch查询会合并为1次批量查询。

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