Django prefetch相关的序列化方法中使用了太多的查询,在以下情况下,如何使用预取来减少查询?

y53ybaqx  于 2023-08-08  发布在  Go
关注(0)|答案(1)|浏览(83)

通过调查为什么一些api需要太多的时间来响应,我发现这是因为数据库查询随着查询集的增加而增加。在Django中,select related和prefetch related将有助于减少这个问题。在这里,我试图解决这样一个问题,我在串行化器中使用了预取相关,但仍然进行了太多的数据库查询。
models.py

class Order(models.Model):
    orderid = models.AutoField(primary_key=True)
    order_number = models.CharField(max_length=20, unique=True)
    #....

class SubOrder(models.Model):
    suborderid = models.AutoField(primary_key=True)
    orderid = models.ForeignKey(
        Order, on_delete=models.CASCADE, related_name="orderitems", db_column="orderid"
    )
    order_number = models.CharField(max_length=50)
    product_quantity = models.PositiveIntegerField()
    #....

字符串
views.py

class GetOrderDetailsByStore(mixins.ListModelMixin, generics.GenericAPIView):

  
    def get(self, request, order_number=None, *args, **kwargs):

        start = time.time()
        logger.info("started %s", start)
        store_id = self.kwargs["storeid"]
        status_list = [SubOrder.Suborder_Status.PAYMENT_INITIATED,
                       SubOrder.Suborder_Status.PAYMENT_FAILED,
                       SubOrder.Suborder_Status.PAYMENT_PENDING,
                       SubOrder.Suborder_Status.ORDER_INITIATED]
        
        order_items = Order.objects.filter(Q(storeid=store_id) &~Q(order_status__in=status_list)).order_by(
                "-created_date"
            )
        order_items_eager_loading = AllOrderSellerSerializer.setup_eager_loading(
                order_items)
        serializer = AllOrderSellerSerializer(order_items_eager_loading, many=True).data

        end = time.time()
        diff = end - start
        logger.info("ended %s", end)
        logger.info("time taken %s", diff)
        return Response({"message": "success", "data": serializer}, status=status.HTTP_200_OK)


serializer.py

class AllOrderSellerSerializer(serializers.ModelSerializer):

    orderitems = AllOrderItemsSellerSerializer(many=True)
    store_reference = serializers.CharField(source="store_reference.store_reference")
    user_reference = serializers.CharField(source="user_reference.user_reference")
    number_of_items = serializers.SerializerMethodField("get_number_of_items")

    class Meta:
        model = Order
        fields = ["order_number", "order_request_number", "store_reference",
                  "user_reference","number_of_items", "total_amount", "orderitems"]

    @staticmethod
    def get_number_of_items(obj):
        list_of_pdt_quantity_of_order = [order_item.product_quantity for order_item in obj.orderitems.filter(orderid=obj.orderid)]
        total_ordered_products_quantity = sum(list_of_pdt_quantity_of_order)
        return total_ordered_products_quantity
        # return obj.orderitems.count()

    @staticmethod
    def setup_eager_loading(queryset):
        """ Perform necessary eager loading of data. """
        queryset = queryset.prefetch_related('orderitems')
        queryset = queryset.select_related('user_reference', 'store_reference')
        return queryset


这里number_of_items字段取prodcut_quantity的总和。但是在这里,db查询会随着订单的增加而增加。如果这个“number_of_items”被移除,对于任何数量的订单,这只需要2个查询,但是由于这个字段,查询变得越来越多,API需要很多时间来响应。使用预取是否与错误的方式相关,或者是否有任何方法可以解决这么多的数据库查询?


的数据

rn0zuynd

rn0zuynd1#

prefetch_related不会减少SQL查询的数量,此行为已记录:
select_related的工作方式是创建一个SQL连接,并在SELECT语句中包含相关对象的字段。[...]
另一方面,prefetch_related为每个关系执行单独的查找,并在Python中执行“连接”。这允许它预取多对多和多对一对象,这是使用select_related无法完成的,除了select_related支持的外键和一对一关系。

相关问题