我一直在努力解决一个问题，我相信每个人在某个时候都会遇到这个问题。我现在有一个15万个产品的小数据库(在我写这篇文章的时候越来越多。）
我正在使用drf作为api，并且一直在为我拥有很多产品的类别性能而挣扎。
i、 我有一个类别叫连衣裙 34633 产品。我的数据库是如何设计的，我在它下面有几个关系。
产品有类别、属性、颜色、尺寸、相关产品 M2M 查询
计数查询 809.83 微软

SELECT COUNT(*) 
FROM (
  SELECT DISTINCT `catalog_products`.`id` AS Col1 
  FROM `catalog_products` 
  INNER JOIN `catalog_products_category` ON (`catalog_products`.`id` =
                           `catalog_products_category`.`products_id`) 
  WHERE (`catalog_products`.`deleted` = 0 
     AND `catalog_products`.`in_stock` = 1 
     AND `catalog_products_category`.`categories_id` = 183)
) subquery

结果查询 2139.52 微软

SELECT DISTINCT `catalog_products`.`id`, `catalog_products`.`sku`,
  `catalog_products`.`title`, `catalog_products`.`old_price`,
  `catalog_products`.`price`, `catalog_products`.`sale`,
  `catalog_products`.`original_categories`,
  `catalog_products`.`original_conv_color`, `catalog_products`.`original_sizes` 
FROM `catalog_products` 
INNER JOIN `catalog_products_category` ON (`catalog_products`.`id` =
                         `catalog_products_category`.`products_id`) 
WHERE (`catalog_products`.`deleted` = 0 
  AND `catalog_products`.`in_stock` = 1 
  AND `catalog_products_category`.`categories_id` = 183) 
ORDER BY `catalog_products`.`title` ASC LIMIT 48

正如你所看到的，对于一个查询来说，时间太多了，但是当我应用过滤器时，这里有一个棘手的部分，即我选择了颜色过滤器，并且大小时间开始减少。
应用筛选器的查询
计数查询 264.63 微软

SELECT COUNT(*) FROM (
  SELECT DISTINCT `catalog_products`.`id` AS Col1 
  FROM `catalog_products` 
  INNER JOIN `catalog_products_color` ON (`catalog_products`.`id` =
                           `catalog_products_color`.`products_id`) 
  INNER JOIN `catalog_products_category` ON (`catalog_products`.`id` =
                           `catalog_products_category`.`products_id`) 
  INNER JOIN `catalog_sizethrough` ON (`catalog_products`.`id` =
                            `catalog_sizethrough`.`product_id`) 
  WHERE (`catalog_products`.`deleted` = 0 
    AND `catalog_products`.`in_stock` = 1 
    AND `catalog_products_color`.`color_id` = 1 
    AND `catalog_products_category`.`categories_id` = 183 
    AND `catalog_sizethrough`.`size_id` IN (262) 
    AND `catalog_sizethrough`.`stock` = 1)
) subquery

结果查询 351.43 微软

SELECT DISTINCT `catalog_products`.`id`, `catalog_products`.`sku`,
  `catalog_products`.`title`, `catalog_products`.`old_price`,
  `catalog_products`.`price`, `catalog_products`.`sale`,
  `catalog_products`.`original_categories`,
  `catalog_products`.`original_conv_color`,
  `catalog_products`.`original_sizes` 
FROM `catalog_products` 
INNER JOIN `catalog_products_color` ON (`catalog_products`.`id` =
                         `catalog_products_color`.`products_id`) 
INNER JOIN `catalog_products_category` ON (`catalog_products`.`id` =
                         `catalog_products_category`.`products_id`) 
INNER JOIN `catalog_sizethrough` ON (`catalog_products`.`id` =
                          `catalog_sizethrough`.`product_id`) 
WHERE (`catalog_products`.`deleted` = 0 
  AND `catalog_products`.`in_stock` = 1 
  AND `catalog_products_color`.`color_id` = 1 
  AND `catalog_products_category`.`categories_id` = 183 
  AND `catalog_sizethrough`.`size_id` IN (262) 
  AND `catalog_sizethrough`.`stock` = 1) 
ORDER BY `catalog_products`.`title` ASC 
LIMIT 48

我已经尝试了这么多的事情来解决这个问题，但无法得到修复我需要提高我的页面加载速度，但由于查询需要更长的时间，它不太适合用户。我已经使用了渴望加载，所以它不会再帮助改善，除非你有任何补充。
代码
序列化程序

class ProductsListSerializer(serializers.ModelSerializer):

images = ImagesSerializer(many=True, source='get_first_two_images')
related_color = serializers.SerializerMethodField()

def get_related_color(self, obj):
    return obj.related_color.count()

class Meta:

    fields = (
        'id',
        'sku',
        "title",
        "old_price",
        "price",
        "sale",
        "images",
        "original_categories",
        "related_color",
        "original_conv_color",
        "original_sizes",
    )
    model = Products

@staticmethod
def setup_eager_loading(queryset):
    queryset = queryset.only('id', 'sku', 'title', 'old_price', 'price', 'sale', 'original_categories', 'original_conv_color', 'original_sizes').prefetch_related('images', 'related_color')

    return queryset

看法

class ProductsViewSet(viewsets.ReadOnlyModelViewSet):
queryset = Products.objects.all()
permission_classes = [DjangoModelPermissionsOrAnonReadOnly]
filter_backends = (filters.SearchFilter, DjangoFilterBackend, filters.OrderingFilter, CustomFilter, SizeFilter)
filter_fields = ('slug', 'code', 'sku', 'color', 'attributes', 'category', 'original_color')
min_max_fields = ('price', 'sale')
search_fields = ('title', 'original_color', 'original_categories', 'original_conv_color', 'original_sizes')
ordering_fields = ('sale', 'price', 'created_at')
pagination_class = StandardResultsSetPagination

def get_queryset(self):
    if self.action == 'list':
        queryset = self.get_serializer_class().setup_eager_loading(self.queryset.filter(deleted=0,in_stock=1))
        return queryset
    return self.queryset

def get_serializer_class(self):
    if self.action == 'list':
        return ProductsListSerializer
    if self.action == 'retrieve':
        return ProductsSerializer
    return ProductsSerializer