django - 优化 Django API 查询以减少数据库命中

Question

这只是基于我的信念，Django 有无限的可能性。我有一个 API，使用调试工具栏对其进行测试，每个请求最多有 13 次点击。

我想我可以做一些事情来将数据库命中率降低到 1。这是我的 API 列表视图：

class ProductsListAPIView(ListAPIView):
    queryset = Product.objects.active()
    serializer_class = ProductSerializer
    filter_backends = [SearchFilter, OrderingFilter]
    permission_classes = [HasAPIKey | AllowAny]
    search_fields = ['product_title', 'product_description',
                     'user__first_name', 'product_type', 'product_price', 'product_city']
    pagination_class = ProductPageNumberPagination


    def get_serializer_context(self, *args, **kwargs):
        context = super(ProductsListAPIView, self).get_serializer_context(*args, **kwargs)
        context['request'] = self.request
        coordinates = self.request.GET.get("coordinates")
        if not coordinates or len(coordinates) <= 4:
            coordinates = '0.0,0.0'
        context['coordinates'] = coordinates
        return context

    def get_queryset(self, *args, **kwargs):

        query = self.request.GET.get("search")
        lookups = ()

        if query and len(query) > 0:
            queryArray = query.split()
            for q in queryArray:
                lookups = (Q(product_title__icontains=q) |
                           Q(product_description__icontains=q) |
                           Q(product_type__icontains=q) |
                           Q(product_city__icontains=q) |
                           Q(user__first_name__icontains=q) |
                           Q(user__last_name__icontains=q) |
                           Q(product_address__icontains=query)
                           )
        # If user is loggedIn and discovery range / point and address is set
        # Returns products just in that range.
        uid = self.request.GET.get("uid")
        queryset_list = self.queryset
        if uid and len(uid) > 5:
            try:
                user = CustomUser.objects.get(uid=uid)
                from django.contrib.gis.measure import D
                if user is not None and user.point is not None and len(user.address) > 5:
                    if query and len(query) > 0:
                        print('USER RANGE and SEARCH QUERY!')
                        query_of_set = queryset_list.filter(
                            point__distance_lte=(user.point, D(km=user.discovery_range))).filter(lookups).distinct(
                        )

                    else:
                        print('USER RANGE WITHOUT SEARCH QUERY!')

                        query_of_set = queryset_list.filter(
                            point__distance_lte=(user.point, D(km=user.discovery_range)))

                    if query_of_set.count() < 3:
                        print('FEWER THAN EXPECTED NUMBER')
                        if query and len(query) > 0:
                            query_of_set = queryset_list.filter(lookups).distinct(
                            )
                        else:
                            query_of_set = queryset_list

                    queryset_list = query_of_set
            except Exception as e:

                queryset_list = queryset_list.filter(lookups).distinct(
                ) if query and len(query) > 0 else queryset_list
                print(f'USER ASSOCIATED QUERY EXCEPTION: {e}')
                logging.error(f'USER ASSOCIATED QUERY EXCEPTION: {e}')
        else:
            print('USER DETAILS NOT SET!, GIVE THEM A GENERAL VIEW')
            queryset_list = queryset_list.filter(lookups).distinct(
            ) if query and len(query) > 0 else queryset_list
        return queryset_list

有什么建议可以降低数据库的命中率，特别是对于 CustomUser 表？有什么方法可以在范围查询中进行产品查询，而无需先查询数据库中的用户对象。user = CustomUser.objects.get(uid=uid)?

还有过滤器后我的查询集的 count() 。我意识到，使用query_of_set.count() < 3添加了几个其他数据库查询。还有其他方法可以在这里检查查询集中的项目数吗？我试过len(query_of_set)了，但结果基本相同。

是的，我曾经prefetch_related()获得过产品照片、评论和投票，这些都会导致一击。我想没有其他出路了。

标签： django