python - 使用索引和更好的 SQL 在循环中优化 QuerySet
问题描述
我有一个返回一些关于电子邮件列表增长的统计数据的视图。涉及的模型有:
模型.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})
现在这工作正常,但随着 My DB 开始增长,此视图的响应时间超过 1 秒,并且偶尔会导致数据库中长时间运行查询。我认为最明显的改进是索引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
解决方案
正如您的代码为每个EmailList
实例生成 6 个查询一样。对于 100 个实例,至少 600 个查询会减慢速度。
您可以使用SubQuery()
表达式和进行优化.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')
)
推荐阅读
- google-sheets - 如何在 Google 表格中基于彼此更新 2 个单元格
- git - Flyway 与 Git 集成并连接到 Snowflake
- amazon-web-services - 安装 yum-cron?或者运行 yum makecache fast error AWS EC2
- mysql - MariaDB 命令行在一段时间后停止响应
- c# - 如何使用 Windows 和/或 PowerShell 使用的接口和/或方法创建类库?
- angular - 在 Angular 中使用查询选择器
- python - Python在特定区间内选择随机数
- matlab - 在matalb中计算二维汉堡方程的能量场
- python - 我遇到 IndexError: list index out of range 错误,试图从 xml 文件中获取文本
- java - Firebase RecyclerView 在获取用户数据时崩溃