使用预取相关的查询速度比没有？ [英] query slower with prefetch related than without?

问题描述

这是我的模型的伪代码

Offer
    match = FK(Match)

Odds
    offer = FK(Offer, related_name='odds')

test1 = "[o.odds.all() for o in Offer.objects.filter(match__id=123)]"
test2 = "[o.odds.all() for o in Offer.objects.filter(match__id=123).prefetch_related('odds')]"
timeit.timeit(test1, number=1000)
>>> 3.078
timeit.timeit(test2, number=1000)
>>> 11.794

我以为在获得赔率时，显示它们根本就没有问题 - 那么它会变成因为预取它们实际上较慢。我做错了事情，或者根本不明白使用 prefetch_related ？

I thought that when prefetching the odds, displaying them would be no problem at all - then it turns out that it's actually slower to prefetch them. Am I doing something wrong, or do I simply not understand the use of prefetch_related?

谢谢

编辑

我将查询 o.odds。 latest（）并仅使用该信息，我只是试图优化我的查询。我很困难地围着 select_related 和 prefetch_related 。

I am going to query for o.odds.latest() and take use of that information only, and I'm simply trying to optimize my query. I'm just having a hard time wrapping my head around select_related and prefetch_related.

推荐答案

如果您检查Django执行的查询，您会看到 prefetch_related（）导致额外的使用 WHERE IN（ids）子句进行查询。这些值然后在Python中映射回原始对象，从而避免额外的数据库加载。

If you examine the queries Django executes, you'll see that prefetch_related() leads to an additional query with a WHERE IN (ids) clause. Those values are then mapped back to the original object in Python, preventing additional database load.

调用 prefetch_related（）无限制的 all（）对整个数据库的QuerySets可能非常昂贵，特别是如果你的表很大。但是，如果您正在使用较小的数据集，因为您的原始QuerySet除了 filter（key = value）之外， prefetch_related（）将减少N次数据库到数据库的1次访问。这可能会导致性能提升。

Calling prefetch_related() on unrestrained all() QuerySets against your entire database can be very costly, especially if your tables are large. However, if you are working with smaller sets of data thanks to a meaningful filter(key=value) addition to your original QuerySet, prefetch_related() will reduce N trips to the database down to 1 trip to the database. This can lead to a drastic performance increase.

作为附注 - 不要运行 prefetch_related（odds）除非你需要这些数据。您的测试没有使用额外的信息，但它仍然在额外的工作来获取它。没有免费午餐：）

And as a side note -- don't run prefetch_related("odds") unless you need that data. Your test isn't making use of the extra information, but it's still doing the extra work to fetch it. There's no free lunch :)

这篇关于使用预取相关的查询速度比没有？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！