Django prefetch_related优化查询,但仍然很慢 [英] Django prefetch_related optimize query but still very slow
问题描述
在具有5个m2m字段的模型上, prefetch_related
遇到一些严重的性能问题,并且我还预取了一些嵌套的m2m字段.
I'm experiencing some severe performances issues with prefetch_related
on a Model with 5 m2m fields and I'm pre-fetching also few nested m2m fields.
class TaskModelManager(models.Manager):
def get_queryset(self):
return super(TaskModelManager, self).get_queryset().exclude(internalStatus=2).prefetch_related("parent", "takes", "takes__flags", "assignedUser", "assignedUser__flags", "asset", "asset__flags", "status", "approvalWorkflow", "viewers", "requires", "linkedTasks", "activities")
class Task(models.Model):
uuid = models.UUIDField(primary_key=True, default=genOptimUUID, editable=False)
internalStatus = models.IntegerField(default=0)
parent = models.ForeignKey("self", blank=True, null=True, related_name="childs")
name = models.CharField(max_length=45)
taskType = models.ForeignKey("TaskType", null=True)
priority = models.IntegerField()
startDate = models.DateTimeField()
endDate = models.DateTimeField()
status = models.ForeignKey("ProgressionStatus")
assignedUser = models.ForeignKey("Asset", related_name="tasksAssigned")
asset = models.ForeignKey("Asset", related_name="tasksSubject")
viewers = models.ManyToManyField("Asset", blank=True, related_name="followedTasks")
step = models.ForeignKey("Step", blank=True, null=True, related_name="tasks")
approvalWorkflow = models.ForeignKey("ApprovalWorkflow")
linkedTasks = models.ManyToManyField("self", symmetrical=False, blank=True, related_name="linkedTo")
requires = models.ManyToManyField("self", symmetrical=False, blank=True, related_name="depends")
objects = TaskModelManager()
查询次数很好,数据库查询时间也很好,例如,如果我查询模型的700个对象,我有35个查询,平均查询时间为100〜200ms,但总请求时间约为8秒
The number of query is fine and the database query time is fine too, for exemple if I query 700 objects of my model i have 35 query and the average query time is 100~200ms but the total request time is approximately 8 seconds.
我已经进行了一些分析,并指出花费了80%以上的时间是在 prefetch_related_objects
调用上.
I've run some profiling and it pointed out that more than 80% of the time spent was on the prefetch_related_objects
call.
我正在使用 Django == 1.8.5
和 djangorestframework == 3.4.6
我愿意采取任何方式对此进行优化.预先感谢您的帮助.
I'm open to any way to optimize this. Thanks in advance for your help.
使用 select_related
Edit with select_related
:
我已经尝试过阿拉斯达尔提出的改进措施
I've tried the improvement proposed by Alasdair
class TaskModelManager(models.Manager):
def get_queryset(self):
return super(TaskModelManager, self).get_queryset().exclude(internalStatus=2).select_related("parent", "status", "approvalWorkflow", "step").prefetch_related("takes", "takes__flags", "assignedUser", "assignedUser__flags", "asset", "asset__flags", "viewers", "requires", "linkedTasks", "activities")
对于32个查询和150ms查询时间的请求,新结果仍然是8秒.
The new result is still 8 seconds for the request with 32 queries and 150ms of query time.
似乎四年前在Django问题跟踪器上打开了一个票证,并且该票证仍在打开:https://code.djangoproject.com/ticket/20577
It seems that a ticket was opened on Django issue tracker 4 years ago and is still open : https: //code.djangoproject.com/ticket/20577
推荐答案
我遇到了同样的问题.
在您链接的问题之后,我发现您可以改善 prefetch_related 使用
Prefetch
对象和 to_attr
自变量来实现code>性能.
Following the issue you linked i found that you can improve the prefetch_related
performance using Prefetch
object and to_attr
argument.
从提交中引入,该对象引入了 Prefetch
对象:
From the commit that introduces the Prefetch
object:
当Prefetch实例指定to_attr参数时,结果为存储在列表中而不是QuerySet中.这很幸运明显更快的结果.性能改进是由于我们节省了创建查询集的昂贵开销实例.
When a Prefetch instance specifies a to_attr argument, the result is stored in a list rather than a QuerySet. This has the fortunate consequence of being significantly faster. The preformance improvement is due to the fact that we save the costly creation of a QuerySet instance.
因此,只需调用以下命令,我就可以将我的代码从7秒显着提高到0.88秒:
So i significantly improved my code (from about 7 seconds to 0.88 seconds) simply by calling:
Foo.objects.filter(...).prefetch_related(Prefetch('bars', to_attr='bars_list'))
代替
Foo.objects.filter(...).prefetch_related('bars')
这篇关于Django prefetch_related优化查询,但仍然很慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!