如何减少含SerializerMethodField的Serializer中SQL查询次数？

当前你的SiteSensorSerializer中，issue字段通过SerializerMethodField实现，每个Sensor对象都会单独触发一次SQL查询来判断是否存在未结束的关联Event，这引发了严重的N+1查询问题。下面给出两种优化方案，优先推荐第一种。

现有代码回顾 #

序列化器代码 #

class SiteSensorSerializer(serializers.ModelSerializer):
    issue = serializers.SerializerMethodField()

    class Meta:
        model = Sensor
        fields = ('id', 'label', 'view', 'issue',)

    def get_issue(self, obj):
        return (
            Event.objects.filter(sensor=obj, date_end__isnull=True)
            .order_by('-date_start')
            .exists()
        )


class SiteDeviceSerializer(serializers.ModelSerializer):
    label = DeviceLabelSerializer()
    sensors = SiteSensorSerializer(many=True, read_only=True)
    class Meta:
        model = Device
        fields = ('id', 'name', 'label', 'sensors')

ViewSet代码 #

class SiteViewSet(viewsets.ReadOnlyModelViewSet):
    permission_classes = (permissions.IsAuthenticated,)
    serializer_class = SiteSerializer
    filter_backends = [
        DjangoFilterBackend,
        filters.SearchFilter,
        filters.OrderingFilter,
    ]
    filter_fields = [
        "name",
    ]
    search_fields = [
        "name"
    ]
    ordering = ["name"]

    def get_queryset(self):
        if self.request.user.is_superuser:
            return (
                Site.objects.all()
                .prefetch_related("devices")
                .prefetch_related("devices__label")
                .prefetch_related("devices__sensors")
            )
        else:
            return (
                Site.objects.filter(devices__users=self.request.user)
                .prefetch_related(
                    Prefetch(
                        "devices",
                        Device.objects.filter(users=self.request.user).select_related(
                            "label"
                        ),
                    )
                )
                .distinct()
                .prefetch_related(
                    Prefetch(
                        "devices__sensors",
                        Sensor.objects.filter(device__users=self.request.user),
                    )
                )
                .distinct()
            )

优化方案一：用数据库注解（annotate）预计算字段 #

这种方法直接在数据库层面完成issue状态的判断，一次性返回所有Sensor的结果，彻底避免N+1查询。

步骤1：修改ViewSet的查询集 #

导入Exists和OuterRef，在预取devices__sensors时添加注解：

from django.db.models import Exists, OuterRef

class SiteViewSet(viewsets.ReadOnlyModelViewSet):
    # ... 其他代码保持不变

    def get_queryset(self):
        if self.request.user.is_superuser:
            return (
                Site.objects.all()
                .prefetch_related("devices")
                .prefetch_related("devices__label")
                .prefetch_related(
                    Prefetch(
                        "devices__sensors",
                        queryset=Sensor.objects.annotate(
                            has_active_issue=Exists(
                                Event.objects.filter(
                                    sensor=OuterRef('pk'),
                                    date_end__isnull=True
                                )
                            )
                        )
                    )
                )
            )
        else:
            return (
                Site.objects.filter(devices__users=self.request.user)
                .prefetch_related(
                    Prefetch(
                        "devices",
                        Device.objects.filter(users=self.request.user).select_related(
                            "label"
                        ),
                    )
                )
                .distinct()
                .prefetch_related(
                    Prefetch(
                        "devices__sensors",
                        Sensor.objects.filter(device__users=self.request.user).annotate(
                            has_active_issue=Exists(
                                Event.objects.filter(
                                    sensor=OuterRef('pk'),
                                    date_end__isnull=True
                                )
                            )
                        ),
                    )
                )
                .distinct()
            )

步骤2：修改序列化器 #

移除SerializerMethodField和get_issue方法，直接使用注解生成的字段：

class SiteSensorSerializer(serializers.ModelSerializer):
    issue = serializers.BooleanField(source='has_active_issue', read_only=True)

    class Meta:
        model = Sensor
        fields = ('id', 'label', 'view', 'issue',)

优化方案二：预取关联的Event对象（备选） #

如果不想修改数据库查询逻辑，可以通过Prefetch预取每个Sensor的未结束Event，再在序列化器中判断是否存在：

步骤1：修改ViewSet的查询集 #

添加针对Event的预取：

from django.db.models import Prefetch

class SiteViewSet(viewsets.ReadOnlyModelViewSet):
    # ... 其他代码保持不变

    def get_queryset(self):
        active_events_prefetch = Prefetch(
            'event_set',
            queryset=Event.objects.filter(date_end__isnull=True).order_by('-date_start'),
            to_attr='active_events'
        )

        if self.request.user.is_superuser:
            return (
                Site.objects.all()
                .prefetch_related("devices")
                .prefetch_related("devices__label")
                .prefetch_related(
                    Prefetch(
                        "devices__sensors",
                        queryset=Sensor.objects.prefetch_related(active_events_prefetch)
                    )
                )
            )
        else:
            return (
                Site.objects.filter(devices__users=self.request.user)
                .prefetch_related(
                    Prefetch(
                        "devices",
                        Device.objects.filter(users=self.request.user).select_related(
                            "label"
                        ),
                    )
                )
                .distinct()
                .prefetch_related(
                    Prefetch(
                        "devices__sensors",
                        Sensor.objects.filter(device__users=self.request.user).prefetch_related(active_events_prefetch),
                    )
                )
                .distinct()
            )

步骤2：修改序列化器的get_issue方法 #

class SiteSensorSerializer(serializers.ModelSerializer):
    issue = serializers.SerializerMethodField()

    class Meta:
        model = Sensor
        fields = ('id', 'label', 'view', 'issue',)

    def get_issue(self, obj):
        return len(obj.active_events) > 0

这种方法会预取所有符合条件的Event对象，数据量相对更大，因此优先推荐方案一。

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