来自django foreignkey字段的许多查询 [英] Lots of queries from django foreignkey fields

问题描述

我一直在Django流口水，整个编码内部网站的记录时间，但现在我注意到，在我的ForeignKeys的模型中，这是非常低效的。

I've been drooling over Django all day while coding up an internal website in record time, but now I'm noticing that something is very inefficient with my ForeignKeys in the model.

我有一个有6个ForeignKey的模型，它们基本上是查找表。当我查询所有对象并在模板中显示时，它每个项目运行大约10个查询。这里有一些代码，应该更好地解释：

I have a model which has 6 ForeignKeys, which are basically lookup tables. When I query all objects and display them in a template, it's running about 10 queries per item. Here's some code, which ought to explain it better:

class Website(models.Model):
    domain_name = models.CharField(max_length=100)
    registrant = models.ForeignKey('Registrant')
    account = models.ForeignKey('Account')
    registrar = models.ForeignKey('Registrar')
    server = models.ForeignKey('Server', related_name='server')
    host = models.ForeignKey('Host')
    target_server = models.ForeignKey('Server', related_name='target')

class Registrant(models.Model):
    name = models.CharField(max_length=100)

...和5个更简单的表。有155个网站记录，在我使用的视图中：

...and 5 more simple tables. There are 155 Website records, and in the view I'm using:

Website.objects.all()

最终执行1544个查询。在模板中，我正在使用所有外部字段，如：

It ends up executing 1544 queries. In the template, I'm using all of the foreign fields, as in:

<span class="value">Registrant:</span> <a href="/filter/registrant/{{ website.registrant.id }}">{{ website.registrant.name }}</a><br />

所以我知道这将会运行很多查询...但似乎这是过度的。这是正常吗？我应该不这样做吗？

So I know it's going to run a lot of queries...but it seems like this is excessive. Is this normal? Should I not be doing it this way?

我对Django很新，所以希望我只是做一些愚蠢的事情。这绝对是一个非常惊人的框架。

I'm pretty new to Django, so hopefully I'm just doing something stupid. It's definitely a pretty amazing framework.

推荐答案

你应该使用 select_related function ，例如

Website.objects.select_related()

，以便它会自动执行加入并按照所有当执行查询而不是在使用它们时按需加载它们。 Django懒惰地从数据库中加载数据，所以默认情况下你会得到以下行为：

so that it will automatically do a join and follow all of those foreign keys when the query is performed instead of loading them on demand as they are used. Django loads data from the database lazily, so by default you get the following behavior

# one database query
website = Website.objects.get(id=123)

# first time account is referenced, so another query
print website.account.username 

# account has already been loaded, so no new query
print website.account.email_address

# first time registrar is referenced, so another query
print website.registrar.name

等等。如果使用选定的相关，则在幕后执行连接，并且所有这些外键都将自动跟踪并加载到第一个查询中，因此只执行一个数据库查询。所以在上面的例子中，你会得到

and so on. If you use selected related, then a join is performed behind the scenes and all of these foreign keys are automatically followed and loaded on the first query, so only one database query is performed. So in the above example, you'd get

# one database query with a join and all foreign keys followed
website = Website.objects.select_related().get(id=123)

# no additional query is needed because the data is already loaded
print website.account.username
print website.account.email_address
print website.registrar.name

这篇关于来自django foreignkey字段的许多查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！