优化Django ORM查询 | 头脑的思考

Django ORM（对象关系映射）是Django最强大的功能之一。它使我们能够使用Python代码而不是SQL与数据库进行交互。

它具有多个优点：

1.数据库引擎是从我们这里抽象出来的，因此可以轻松切换到另一个数据库系统。
2.它支持迁移：我们可以通过更新模型轻松地更改表，并且Django将自动生成更新数据库表所需的迁移脚本。
3.它支持事务：您可以在一个事务中对数据库进行多次更新，如果失败，则将其回滚到开始时的状态。

但这也有一些缺点：

1.由于它是基于SQL的抽象，因此晦涩难懂，因此我们无法确切知道将从我们的Python代码生成哪些SQL查询。
2.Django无法猜测何时需要使用相关表，因此在需要它们时不会为我们做JOIN。
3.ORM误导了我们所做的事情并不复杂，我们没有简单的方法知道访问对象中的属性可能会触发对数据库的查询，而这可能是用JOIN阻止的。

为了克服这些缺点，我们需要更加了解它，并了解幕后发生的事情。

找出幕后情况
首先，我们需要了解系统中正在发生的事情，正在运行的SQL查询以及使我们付出最大代价的事情。

这是检查SQL查询执行时的几种不同机制：

1. connection.queries
当debug = True时，可以访问通过打印connection.queries执行的查询。

>>> from django.db import connection
>>> Post.objects.all()
>>> connection.queries
[
   {
      'sql': 'SELECT "blogposts_post"."id", "blogposts_post"."title", '
             '"blogposts_post"."content", "blogposts_post"."blog_id", '
             '"blogposts_post"."published" FROM "blogposts_post" LIMIT 21',
      'time': '0.000'
   }
]

>>> from django.db import connection

>>> Post.objects.all()

>>> connection.queries

[

{

'sql': 'SELECT "blogposts_post"."id", "blogposts_post"."title", '

'"blogposts_post"."content", "blogposts_post"."blog_id", '

'"blogposts_post"."published" FROM "blogposts_post" LIMIT 21',

'time': '0.000'

}

]

connection.queries以字典的形式保存SQL查询的列表，其中包含SQL代码及其运行时间。

查询列表很容易被弄乱。为了解决这个问题，Django提供了一种清理它们的方法：

>>> from django.db import reset_queries
>>> reset_queries()

1 2	>>> from django.db import reset_queries >>> reset_queries()

2. shell_plus –print-sql
django-extensions项目很棒，并具有一些有用的功能。

shell_plus是其中之一。这是Django外壳，带有其他附加功能。如果使用–print-sql参数调用它，它将在您运行代码时执行的SQL查询打印出来。

我将在整个本文中使用shell_plus，以便您可以看到运行代码时正在执行的SQL查询。

这是一个输出示例的简单示例：

$ ./manage.py shell_plus --print-sql
>>> post = Post.objects.get(id=1)
SELECT "blogposts_post"."id",
     	"blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published"
 FROM "blogposts_post"
ORDER BY "blogposts_post"."id" ASC
LIMIT 1

$ ./manage.py shell_plus --print-sql

>>> post = Post.objects.get(id=1)

SELECT "blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published"

FROM "blogposts_post"

ORDER BY "blogposts_post"."id" ASC

LIMIT 1

3.django-silk
django-silk是一个配置文件工具。它拦截请求，记录已执行的SQL查询，并提供一种可视化它们的方法。

我们能够浏览请求，查看已执行的SQL查询的列表，并查看有关特定查询的详细信息，包括导致特定查询运行的代码行。

4. django-debug-toolbar
django-debug-toolbar在浏览器上添加了一个工具栏，在浏览Django项目时会向我们显示许多调试信息。使用它，就可以查看对请求执行的SQL查询的数量。还可以进一步检查这些查询，检查SQL代码并查看执行顺序以及每个查询花费了多少时间。

优化查询
介绍示例数据库模型
我们将使用以下数据库模型作为后面各节的示例：

class Blog(models.Model):
   name = models.CharField(max_length=250)
   url = models.URLField()

   def __str__(self):
       return self.name


class Author(models.Model):
   name = models.CharField(max_length=250)
   email = models.EmailField()

   def __str__(self):
       return self.name


class Post(models.Model):
   title = models.CharField(max_length=250)
   content = models.TextField()
   published = models.BooleanField(default=False)

   blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
   authors = models.ManyToManyField(Author, related_name="posts")

   def __str__(self):
       return self.title

class Blog(models.Model):

name = models.CharField(max_length=250)

url = models.URLField()

def __str__(self):

return self.name

class Author(models.Model):

name = models.CharField(max_length=250)

email = models.EmailField()

def __str__(self):

return self.name

class Post(models.Model):

title = models.CharField(max_length=250)

content = models.TextField()

published = models.BooleanField(default=False)

blog = models.ForeignKey(Blog, on_delete=models.CASCADE)

authors = models.ManyToManyField(Author, related_name="posts")

def __str__(self):

return self.title

使用缓存的外键ids
如果只需要访问ForeignKey字段的ID，则可以使用Django已通过<field_name> _id为我们缓存的ID。

让我们看一个例子：

>>> Post.objects.first().blog.id
SELECT "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published"
 FROM "blogposts_post"
ORDER BY "blogposts_post"."id" ASC
LIMIT 1

Execution time: 0.001668s [Database: default]
SELECT "blogposts_blog"."id",
      "blogposts_blog"."name",
      "blogposts_blog"."url"
 FROM "blogposts_blog"
WHERE "blogposts_blog"."id" = 1
LIMIT 21

Execution time: 0.000197s [Database: default]

>>> Post.objects.first().blog.id

SELECT "blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published"

FROM "blogposts_post"

ORDER BY "blogposts_post"."id" ASC

LIMIT 1

Execution time: 0.001668s [Database: default]

SELECT "blogposts_blog"."id",

"blogposts_blog"."name",

"blogposts_blog"."url"

FROM "blogposts_blog"

WHERE "blogposts_blog"."id" = 1

LIMIT 21

Execution time: 0.000197s [Database: default]

通过嵌套对象博客访问博客的ID会生成一个新的SQL查询，以获取整个博客对象。但是，由于我们不需要访问Blog对象的任何其他属性，因此可以通过执行以下操作完全避免执行以上查询：

>>> Post.objects.first().blog_id
SELECT "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published"
 FROM "blogposts_post"
ORDER BY "blogposts_post"."id" ASC
LIMIT 1

Execution time: 0.000165s [Database: default]

>>> Post.objects.first().blog_id

SELECT "blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published"

FROM "blogposts_post"

ORDER BY "blogposts_post"."id" ASC

LIMIT 1

Execution time: 0.000165s [Database: default]

让Django事先知道您需要什么
对外键使用select_related
Django无法预测何时需要从正在查询的模型中访问ForeignKey关系。 select_related实用程序允许我们确切地告诉Django我们想要哪些相关模型，以便它可以执行JOIN。

在我们的示例中，我们有一个Post模型。帖子属于特定博客。此关系通过从发到博客的ForeignKey在数据库上表示。

要访问特定的Post对象，我们可以执行以下操作：

>>> post = Post.objects.get(id=1)
SELECT "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published"
 FROM "blogposts_post"
ORDER BY "blogposts_post"."id" ASC
LIMIT 1

>>> post = Post.objects.get(id=1)

SELECT "blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published"

FROM "blogposts_post"

ORDER BY "blogposts_post"."id" ASC

LIMIT 1

如果我们想从Post中访问Blog对象，我们可以这样做：

>>> post.blog
SELECT "blogposts_blog"."id",
      "blogposts_blog"."name",
      "blogposts_blog"."url"
 FROM "blogposts_blog"
WHERE "blogposts_blog"."id" = 1
LIMIT 21

Execution time: 0.000602s [Database: default]
<Blog: Rocio's Blog>

>>> post.blog

SELECT "blogposts_blog"."id",

"blogposts_blog"."name",

"blogposts_blog"."url"

FROM "blogposts_blog"

WHERE "blogposts_blog"."id" = 1

LIMIT 21

Execution time: 0.000602s [Database: default]

<Blog: Rocio's Blog>

但是，此语句生成了一个新查询以从博客中获取信息。我们要避免这种情况。这是select_related使用的最好时机，我们可以将原始查询更新为：

>>> post = Post.objects.select_related("blog").get(id=1)
SELECT "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published",
      "blogposts_blog"."id",
      "blogposts_blog"."name",
      "blogposts_blog"."url"
 FROM "blogposts_post"
INNER JOIN "blogposts_blog"
   ON ("blogposts_post"."blog_id" = "blogposts_blog"."id")
WHERE "blogposts_post"."id" = 1
LIMIT 21

Execution time: 0.000150s [Database: default]

>>> post = Post.objects.select_related("blog").get(id=1)

SELECT "blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published",

"blogposts_blog"."id",

"blogposts_blog"."name",

"blogposts_blog"."url"

FROM "blogposts_post"

INNER JOIN "blogposts_blog"

ON ("blogposts_post"."blog_id" = "blogposts_blog"."id")

WHERE "blogposts_post"."id" = 1

LIMIT 21

Execution time: 0.000150s [Database: default]

现在请注意Django如何在上面的SQL查询上使用JOIN来为我们从博客表中获取属性。现在，当从Post内部访问Blog对象时，由于它已经被缓存了，因此不需要额外的查询：

>>> post.blog
<Blog: Rocio's Blog>

1 2	>>> post.blog <Blog: Rocio's Blog>

select_related也适用于查询集。我们可以为整个查询集预先选择博客对象。如果有50个帖子，而我们没有使用select_related来预先选择博客对象，则需要Django 50个查询来运行以下代码。与select_related只需一个：

>>> posts = Post.objects.select_related("blog").all()
>>> for post in posts:
       post.blog

SELECT "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published",
      "blogposts_blog"."id",
      "blogposts_blog"."name",
      "blogposts_blog"."url"
 FROM "blogposts_post"
INNER JOIN "blogposts_blog"
   ON ("blogposts_post"."blog_id" = "blogposts_blog"."id")

Execution time: 0.000224s [Database: default]

>>> posts = Post.objects.select_related("blog").all()

>>> for post in posts:

post.blog

SELECT "blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published",

"blogposts_blog"."id",

"blogposts_blog"."name",

"blogposts_blog"."url"

FROM "blogposts_post"

INNER JOIN "blogposts_blog"

ON ("blogposts_post"."blog_id" = "blogposts_blog"."id")

Execution time: 0.000224s [Database: default]

对ManyToMany字段使用prefetch_related
prefetch_related与select_related类似，但是它用于预选ManyToMany字段。 prefetch_related的工作原理有所不同，让我们通过示例进行了解。

假设我们要获取所有帖子，然后为每个帖子打印作者。我们可以执行以下操作：

>>> for post in Post.objects.all():
       post.authors.all()

SELECT "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published"
 FROM "blogposts_post"

Execution time: 0.000158s [Database: default]
<QuerySet []>
SELECT "blogposts_author"."id",
      "blogposts_author"."name",
      "blogposts_author"."email"
 FROM "blogposts_author"
INNER JOIN "blogposts_post_authors"
   ON ("blogposts_author"."id" = "blogposts_post_authors"."author_id")
WHERE "blogposts_post_authors"."post_id" = 1
LIMIT 21

Execution time: 0.000101s [Database: default]
<QuerySet []>
SELECT "blogposts_author"."id",
      "blogposts_author"."name",
      "blogposts_author"."email"
 FROM "blogposts_author"
INNER JOIN "blogposts_post_authors"
   ON ("blogposts_author"."id" = "blogposts_post_authors"."author_id")
WHERE "blogposts_post_authors"."post_id" = 2
LIMIT 21
Execution time: 0.001043s [Database: default]

Execution time: 0.000101s [Database: default]
<QuerySet []>
SELECT "blogposts_author"."id",
      "blogposts_author"."name",
      "blogposts_author"."email"
 FROM "blogposts_author"
INNER JOIN "blogposts_post_authors"
   ON ("blogposts_author"."id" = "blogposts_post_authors"."author_id")
WHERE "blogposts_post_authors"."post_id" = 3
LIMIT 21
Execution time: 0.001043s [Database: default]

>>> for post in Post.objects.all():

post.authors.all()

SELECT "blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published"

FROM "blogposts_post"

Execution time: 0.000158s [Database: default]

SELECT "blogposts_author"."id",

"blogposts_author"."name",

"blogposts_author"."email"

FROM "blogposts_author"

INNER JOIN "blogposts_post_authors"

ON ("blogposts_author"."id" = "blogposts_post_authors"."author_id")

WHERE "blogposts_post_authors"."post_id" = 1

LIMIT 21

Execution time: 0.000101s [Database: default]

SELECT "blogposts_author"."id",

"blogposts_author"."name",

"blogposts_author"."email"

FROM "blogposts_author"

INNER JOIN "blogposts_post_authors"

ON ("blogposts_author"."id" = "blogposts_post_authors"."author_id")

WHERE "blogposts_post_authors"."post_id" = 2

LIMIT 21

Execution time: 0.001043s [Database: default]

Execution time: 0.000101s [Database: default]

SELECT "blogposts_author"."id",

"blogposts_author"."name",

"blogposts_author"."email"

FROM "blogposts_author"

INNER JOIN "blogposts_post_authors"

ON ("blogposts_author"."id" = "blogposts_post_authors"."author_id")

WHERE "blogposts_post_authors"."post_id" = 3

LIMIT 21

Execution time: 0.001043s [Database: default]

请注意，上面的代码生成了4个查询，一个查询获取帖子，然后一个查询每个帖子以获取作者（总共3个帖子）。

这就是著名的N + 1问题。给定N个帖子，将执行N + 1个查询。在这种情况下，我们有3个帖子，可转换为4个查询。数量不多，但是随着我们创建新帖子，这很容易升级。对于50个帖子，此代码将生成51个查询。

为了避免这种情况，我们可以使用prefetch_related预选作者：

>>> for post in Post.objects.prefetch_related("authors").all():
       post.authors.all()

SELECT "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published"
 FROM "blogposts_post"

Execution time: 0.000158s [Database: default]
SELECT ("blogposts_post_authors"."post_id") AS "_prefetch_related_val_post_id",
      "blogposts_author"."id",
      "blogposts_author"."name",
      "blogposts_author"."email"
 FROM "blogposts_author"
INNER JOIN "blogposts_post_authors"
   ON ("blogposts_author"."id" = "blogposts_post_authors"."author_id")
WHERE "blogposts_post_authors"."post_id" IN (1, 2, 3)

Execution time: 0.001043s [Database: default]

>>> for post in Post.objects.prefetch_related("authors").all():

post.authors.all()

SELECT "blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published"

FROM "blogposts_post"

Execution time: 0.000158s [Database: default]

SELECT ("blogposts_post_authors"."post_id") AS "_prefetch_related_val_post_id",

"blogposts_author"."id",

"blogposts_author"."name",

"blogposts_author"."email"

FROM "blogposts_author"

INNER JOIN "blogposts_post_authors"

ON ("blogposts_author"."id" = "blogposts_post_authors"."author_id")

WHERE "blogposts_post_authors"."post_id" IN (1, 2, 3)

Execution time: 0.001043s [Database: default]

使用我们的更新代码，仅执行了2个查询。使用prefetch_related时，Django首先获取所有帖子，然后运行另一个SQL查询，以检索所有帖子的所有作者。

定制预取
在某些情况下，prefetch_related基本语法不足以阻止Django执行额外的查询。要进一步控制预取，可以使用Prefetch对象。

在我们的示例数据库中，有一个Post模型和一个Author模型。 Post模型通过ManyToMany字段与Author模型相关。假设我们想按作者逐一查看所有由该作者发表的帖子：

>>> authors = Author.objects.all()
>>> for author in authors:
       print(author.posts.filter(published=True))

SELECT "blogposts_author"."id",
      "blogposts_author"."name",
      "blogposts_author"."email"
 FROM "blogposts_author"

Execution time: 0.000251s [Database: default]
SELECT "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published"
 FROM "blogposts_post"
INNER JOIN "blogposts_post_authors"
   ON ("blogposts_post"."id" = "blogposts_post_authors"."post_id")
WHERE ("blogposts_post_authors"."author_id" = 1 AND "blogposts_post"."published" = 1)
LIMIT 21

Execution time: 0.000178s [Database: default]
<QuerySet [<Post: Optimizing Django ORM Queries>, <Post: Placeholder Post>, <Post: Placeholder Post 2>, <Post: Placeholder Post 3>, <Post: Placeholder Post 4>, <Post: Placeholder Post 6>, <Post: Placeholder Post 7>, <Post: Placeholder Post 8>, <Post: Placeholder Post 9>]>
SELECT "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published"
 FROM "blogposts_post"
INNER JOIN "blogposts_post_authors"
   ON ("blogposts_post"."id" = "blogposts_post_authors"."post_id")
WHERE ("blogposts_post_authors"."author_id" = 2 AND "blogposts_post"."published" = 1)
LIMIT 21

Execution time: 0.000081s [Database: default]
<QuerySet [<Post: Optimizing Django ORM Queries>]>

>>> authors = Author.objects.all()

>>> for author in authors:

print(author.posts.filter(published=True))

SELECT "blogposts_author"."id",

"blogposts_author"."name",

"blogposts_author"."email"

FROM "blogposts_author"

Execution time: 0.000251s [Database: default]

SELECT "blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published"

FROM "blogposts_post"

INNER JOIN "blogposts_post_authors"

ON ("blogposts_post"."id" = "blogposts_post_authors"."post_id")

WHERE ("blogposts_post_authors"."author_id" = 1 AND "blogposts_post"."published" = 1)

LIMIT 21

Execution time: 0.000178s [Database: default]

SELECT "blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published"

FROM "blogposts_post"

INNER JOIN "blogposts_post_authors"

ON ("blogposts_post"."id" = "blogposts_post_authors"."post_id")

WHERE ("blogposts_post_authors"."author_id" = 2 AND "blogposts_post"."published" = 1)

LIMIT 21

Execution time: 0.000081s [Database: default]

如上所见，上面的代码生成了3个查询，其中1个用于获取作者，然后2个查询以获取每个作者的帖子。

如果我们使用prefetch_related怎么办？似乎下面就是要的答案：

>>> authors = Author.objects.prefetch_related("posts").all()
>>> for author in authors:
       print(author.posts.filter(published=True))

SELECT "blogposts_author"."id",
      "blogposts_author"."name",
      "blogposts_author"."email"
 FROM "blogposts_author"

Execution time: 0.000097s [Database: default]
SELECT ("blogposts_post_authors"."author_id") AS "_prefetch_related_val_author_id",
      "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published"
 FROM "blogposts_post"
INNER JOIN "blogposts_post_authors"
   ON ("blogposts_post"."id" = "blogposts_post_authors"."post_id")
WHERE "blogposts_post_authors"."author_id" IN (1, 2)

Execution time: 0.000190s [Database: default]
SELECT "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published"
 FROM "blogposts_post"
INNER JOIN "blogposts_post_authors"
   ON ("blogposts_post"."id" = "blogposts_post_authors"."post_id")
WHERE ("blogposts_post_authors"."author_id" = 1 AND "blogposts_post"."published" = 1)
LIMIT 21

Execution time: 0.000074s [Database: default]
<QuerySet [<Post: Optimizing Django ORM Queries>, <Post: Placeholder Post>,
<Post: Placeholder Post 2>, <Post: Placeholder Post 3>,
<Post: Placeholder Post 4>, <Post: Placeholder Post 6>,
<Post: Placeholder Post 7>, <Post: Placeholder Post 8>,
<Post: Placeholder Post 9>]>
SELECT "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published"
 FROM "blogposts_post"
INNER JOIN "blogposts_post_authors"
   ON ("blogposts_post"."id" = "blogposts_post_authors"."post_id")
WHERE ("blogposts_post_authors"."author_id" = 2 AND "blogposts_post"."published" = 1)
LIMIT 21

Execution time: 0.000070s [Database: default]
<QuerySet [<Post: Optimizing Django ORM Queries>]>

>>> authors = Author.objects.prefetch_related("posts").all()

>>> for author in authors:

print(author.posts.filter(published=True))

SELECT "blogposts_author"."id",

"blogposts_author"."name",

"blogposts_author"."email"

FROM "blogposts_author"

Execution time: 0.000097s [Database: default]

SELECT ("blogposts_post_authors"."author_id") AS "_prefetch_related_val_author_id",

"blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published"

FROM "blogposts_post"

INNER JOIN "blogposts_post_authors"

ON ("blogposts_post"."id" = "blogposts_post_authors"."post_id")

WHERE "blogposts_post_authors"."author_id" IN (1, 2)

Execution time: 0.000190s [Database: default]

SELECT "blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published"

FROM "blogposts_post"

INNER JOIN "blogposts_post_authors"

ON ("blogposts_post"."id" = "blogposts_post_authors"."post_id")

WHERE ("blogposts_post_authors"."author_id" = 1 AND "blogposts_post"."published" = 1)

LIMIT 21

Execution time: 0.000074s [Database: default]

<QuerySet [<Post: Optimizing Django ORM Queries>, <Post: Placeholder Post>,

<Post: Placeholder Post 2>, <Post: Placeholder Post 3>,

<Post: Placeholder Post 4>, <Post: Placeholder Post 6>,

<Post: Placeholder Post 7>, <Post: Placeholder Post 8>,

<Post: Placeholder Post 9>]>

SELECT "blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published"

FROM "blogposts_post"

INNER JOIN "blogposts_post_authors"

ON ("blogposts_post"."id" = "blogposts_post_authors"."post_id")

WHERE ("blogposts_post_authors"."author_id" = 2 AND "blogposts_post"."published" = 1)

LIMIT 21

Execution time: 0.000070s [Database: default]

刚刚发生了什么？我们使用prefetch_related减少了查询数量，实际上将其增加了1。

发生这种情况是因为我们正在过滤发布= True的帖子。 Django无法使用我们缓存的帖子，因为查询时它们没有被过滤。为了避免这种情况的发生，我们可以使用Prefetch对象自定义查询集：

>>> authors = Author.objects.prefetch_related(
       Prefetch(
          "posts",
          queryset=Post.objects.filter(published=True),
          to_attr="published_posts",
       )
    )
>>> for author in authors:
      print(author.published_posts)

SELECT "blogposts_author"."id",
      "blogposts_author"."name",
      "blogposts_author"."email"
 FROM "blogposts_author"

Execution time: 0.000129s [Database: default]
SELECT ("blogposts_post_authors"."author_id") AS "_prefetch_related_val_author_id",
      "blogposts_post"."id",
      "blogposts_post"."title",
      "blogposts_post"."content",
      "blogposts_post"."blog_id",
      "blogposts_post"."published"
 FROM "blogposts_post"
INNER JOIN "blogposts_post_authors"
   ON ("blogposts_post"."id" = "blogposts_post_authors"."post_id")
WHERE ("blogposts_post"."published" = 1 AND "blogposts_post_authors"."author_id" IN (1, 2))

Execution time: 0.000089s [Database: default]
[<Post: Optimizing Django ORM Queries>, <Post: Placeholder Post>,
<Post: Placeholder Post 2>, <Post: Placeholder Post 3>,
<Post: Placeholder Post 4>, <Post: Placeholder Post 6>,
<Post: Placeholder Post 7>, <Post: Placeholder Post 8>,
<Post: Placeholder Post 9>]
[<Post: Optimizing Django ORM Queries>]

>>> authors = Author.objects.prefetch_related(

Prefetch(

"posts",

queryset=Post.objects.filter(published=True),

to_attr="published_posts",

)

>>> for author in authors:

print(author.published_posts)

SELECT "blogposts_author"."id",

"blogposts_author"."name",

"blogposts_author"."email"

FROM "blogposts_author"

Execution time: 0.000129s [Database: default]

SELECT ("blogposts_post_authors"."author_id") AS "_prefetch_related_val_author_id",

"blogposts_post"."id",

"blogposts_post"."title",

"blogposts_post"."content",

"blogposts_post"."blog_id",

"blogposts_post"."published"

FROM "blogposts_post"

INNER JOIN "blogposts_post_authors"

ON ("blogposts_post"."id" = "blogposts_post_authors"."post_id")

WHERE ("blogposts_post"."published" = 1 AND "blogposts_post_authors"."author_id" IN (1, 2))

Execution time: 0.000089s [Database: default]

[<Post: Optimizing Django ORM Queries>, <Post: Placeholder Post>,

<Post: Placeholder Post 2>, <Post: Placeholder Post 3>,

<Post: Placeholder Post 4>, <Post: Placeholder Post 6>,

<Post: Placeholder Post 7>, <Post: Placeholder Post 8>,

<Post: Placeholder Post 9>]

[<Post: Optimizing Django ORM Queries>]

我们使用Prefetch对象告诉Django：

使用特定的查询集来检索帖子-通过queryset参数。
通过to_attr参数将过滤后的帖子存储在新属性（published_posts）中。
执行author.published_posts时，将不运行查询，因为所有内容均已缓存。无论我们系统上的作者人数如何，该操作将始终执行2个SQL查询。

总结
在使用Django ORM时，考虑幕后情况非常重要。

您在此博客文章上学到的概念将帮助您编写更多优化的查询，并在检查代码时查找可能的优化。但是请注意，您应该始终测量优化前后的查询时间，以确保优化工作正常。有时更少的查询并不一定意味着更少的时间，JOIN可能也很昂贵。

原文：http://schegel.net/posts/optimizing-django-orm-queries/

2024年四月
M	T	W	T	F	S	S
« Jun
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30