使用索引和更好的SQL优化循环中的查询集

Question

我有一个观点，返回一些有关电子邮件列表增长的统计数字。涉及的模型有：

型号.py

class Contact(models.Model):
    email_list = models.ForeignKey(EmailList, related_name='contacts')
    customer = models.ForeignKey('Customer', related_name='contacts')
    status = models.CharField(max_length=8)
    create_date = models.DateTimeField(auto_now_add=True)


class EmailList(models.Model):
    customers = models.ManyToManyField('Customer',
        related_name='lists',
        through='Contact')


class Customer(models.Model):
    is_unsubscribed = models.BooleanField(default=False, db_index=True)
    unsubscribe_date = models.DateTimeField(null=True, blank=True, db_index=True)

在视图中，我所做的是迭代所有EmailLists对象并获取一些度量：以下方式：

视图.py

class ListHealthView(View):
    def get(self, request, *args, **kwargs):
        start_date, end_date = get_dates_from_querystring(request)

        data = []
        for email_list in EmailList.objects.all():
            # historic data up to start_date
            past_contacts = email_list.contacts.filter(
                status='active',
                create_date__lt=start_date).count()
            past_unsubscribes = email_list.customers.filter(
                is_unsubscribed=True,
                unsubscribe_date__lt=start_date,
                contacts__status='active').count()
            past_deleted = email_list.contacts.filter(
                status='deleted',
                modify_date__lt=start_date).count()
            # data for the given timeframe
            new_contacts = email_list.contacts.filter(
                status='active',
                create_date__range=(start_date, end_date)).count()
            new_unsubscribes = email_list.customers.filter(
                is_unsubscribed=True,
                unsubscribe_date__range=(start_date, end_date),
                contacts__status='active').count()
            new_deleted = email_list.contacts.filter(
                status='deleted',
                modify_date__range=(start_date, end_date)).count()

            data.append({
                'new_contacts': new_contacts,
                'new_unsubscribes': new_unsubscribes,
                'new_deleted': new_deleted,
                'past_contacts': past_contacts,
                'past_unsubscribes': past_unsubscribes,
                'past_deleted': past_deleted,
            })
        return Response({'data': data})

现在这可以正常工作，但是随着我的数据库开始增长，这个视图的响应时间超过1s，偶尔会导致数据库中长时间运行的查询。我认为最明显的改进是索引EmailList.customers，但我认为可能需要一个复合索引？还有，有没有更好的方法？也许是用骨料？你知道吗

编辑

在@bdoubleu回答之后，我尝试了以下方法：

data = (
    EmailList.objects.annotate(
        past_contacts=Count(Subquery(
            Contact.objects.values('id').filter(
                email_list=F('pk'),
                status='active',
                create_date__lt=start_date)
        )),
        past_deleted=Count(Subquery(
            Contact.objects.values('id').filter(
                email_list=F('pk'),
                status='deleted',
                modify_date__lt=start_date)
        )),
    )
    .values(
        'past_contacts', 'past_deleted',
    )
)

我不得不改用F而不是OuterRef，因为我意识到我的模型EmailList已经id = HashidAutoField(primary_key=True, salt='...')导致了ProgrammingError: more than one row returned by a subquery used as an expression，但我不能完全确定。你知道吗

现在查询工作了，但遗憾的是所有计数都返回为0

Answer 1

同样，您的代码为每个EmailList实例生成6个查询。对于100个实例，至少需要600个查询，这会减慢速度。你知道吗

您可以使用^{}表达式和.values()进行优化。你知道吗

from django.db.models import Count, OuterRef, Subquery

data = (
    EmailList.objects
    .annotate(
        past_contacts=Count(Subquery(
            Contact.objects.filter(
                email_list=OuterRef('pk'),
                status='active',
                create_date__lt=start_date
            ).values('id')
        )),
        past_unsubscribes=...,
        past_deleted=...,
        new_contacts=...,
        new_unsubscribes=...,
        new_deleted=...,
    )
    .values(
        'past_contacts', 'past_unsubscribes',
        'past_deleted', 'new_contacts',
        'new_unsubscribes', 'new_deleted',
    )
)

更新：对于Django的旧版本，您的子查询可能需要如下所示

customers = (
    Customer.objects
    .annotate(
        template_count=Subquery(
            CustomerTemplate.objects
            .filter(customer=OuterRef('pk'))
            .values('customer')
            .annotate(count=Count('*')).values('count')
        )
    ).values('name', 'template_count')
)