matobaの学んだこと

とあるPythonエンジニアのブログ

djangoのSQL実行を最適化する(prefetch_related/select_related/Prefetch)

Djangoを使っていて、イケてないSQLがありました。 そもそも、僕はDjangoのprefetch_relatedとselect_relatedがよくわかっていませんでした。

今回は、それを調べたので説明を書きます。

prefetch_relatedとselect_relatedって?

両方ともDjagnoのForeignKeyに関係するORMのメソッド。

とりあえず使ってみます。

ちなみにDjangoのバージョンは 1.11 です。 Pythonのバージョンは 3.6 です。

準備

モデルを用意する。

今回用意するモデルはこういうモデル。

myapp/models.py

from django.db import models

class Blog(models.Model):
    title = models.CharField(max_length=10)
    content = models.CharField(max_length=40)

    def __str__(self):
        return self.title

class Tag(models.Model):
    name = models.CharField(max_length=10)

    def __str__(self):
        return self.name

class BlogTag(models.Model):
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
    tag = models.ForeignKey(Tag, on_delete=models.CASCADE)

    class Meta:
        unique_together = ('blog', 'tag',)

    def __str__(self):
        return "{}:<{}>".format(self.blog.title, self.tag.name)

タグがあって、ブログ記事にタグをつけて分類できるというモデルです。

データを用意する

今回は、django admin画面を用意して、テストデータを手で入れました。

myapp/admin.py

from django.contrib import admin
from myapp.models import Blog, Tag, BlogTag

class BlogTagAdmin(admin.ModelAdmin):
    list_display = ('blog', 'tag')

admin.site.register(Blog)
admin.site.register(Tag)
admin.site.register(BlogTag, BlogTagAdmin)

f:id:mtb_beta:20180413003159p:plain

SQLをログに出力する。

データの準備ができたので、実際にORMを叩いていきたいところです。

しかしその前に、ORMで実行されるSQLが見れるようにして起きましょう。

settings.pyに以下の設定を追加します。

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
        }
    },
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
            'handlers': ['console'],
        },
    }
}

これで、実際にSQLが表示されるようになりました。

>>> Tag.objects.filter(name='Python')
(0.001) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."name" = 'Python' LIMIT 21; args=('Python',)
<QuerySet [<Tag: Python>]>

試行

とりあえず、データを確認します。

>>> tag = Tag.objects.filter(name='Python')
>>> tag
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."name" = 'Python' LIMIT 21; args=('Python',)
<QuerySet [<Tag: Python>]>
>>> tag[0].id
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."name" = 'Python' LIMIT 1; args=('Python',)
1

Tagテーブルのid=1はPythonタグです。

次に、BlogTagテーブルから、tagのidが1のレコードを取り出してみます。

>>> BlogTag.objects.filter(tag=1)
(0.001) SELECT "myapp_blogtag"."id", "myapp_blogtag"."blog_id", "myapp_blogtag"."tag_id" FROM "myapp_blogtag" WHERE "myapp_blogtag"."tag_id" = 1 LIMIT 21; args=(1,)
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."id" = 1; args=(1,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 1; args=(1,)
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."id" = 3; args=(3,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 1; args=(1,)
<QuerySet [<BlogTag: Pythonの記事:<Python>>, <BlogTag: Djangoの記事:<Python>>]>
>>

一つ目のSQLがBlogTagレコードのを探していて、あとの4つは文字列を出力するために検索してるようですね。

これをselect_relatedで取り出すと次のようになります。

>>> BlogTag.objects.select_related('tag').filter(tag=1)
(0.000) SELECT "myapp_blogtag"."id", "myapp_blogtag"."blog_id", "myapp_blogtag"."tag_id", "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_blogtag" INNER JOIN "myapp_tag" ON ("myapp_blogtag"."tag_id" = "myapp_tag"."id") WHERE "myapp_blogtag"."tag_id" = 1 LIMIT 21; args=(1,)
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."id" = 1; args=(1,)
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."id" = 3; args=(3,)
<QuerySet [<BlogTag: Pythonの記事:<Python>>, <BlogTag: Djangoの記事:<Python>>]>

要するに何が起きたの?というと、select_relatedで指定したテーブル(Tagテーブル)をINNER_JOINしたSQLを実行してくれます。

こうすることで、BlogTagレコードの各レコードにひもづくTagレコードを後から検索する必要がなくなります。

当然ですが、複数指定することもできます。

>>> BlogTag.objects.select_related('tag').select_related('blog').filter(tag=1)
(0.000) SELECT "myapp_blogtag"."id", "myapp_blogtag"."blog_id", "myapp_blogtag"."tag_id", "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content", "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_blogtag" INNER JOIN "myapp_tag" ON ("myapp_blogtag"."tag_id" = "myapp_tag"."id") INNER JOIN "myapp_blog" ON ("myapp_blogtag"."blog_id" = "myapp_blog"."id") WHERE "myapp_blogtag"."tag_id" = 1 LIMIT 21; args=(1,)
<QuerySet [<BlogTag: Pythonの記事:<Python>>, <BlogTag: Djangoの記事:<Python>>]>

ただし、今は、BlogTagから辿っているからいいのですが、TagテーブルやBlogテーブルからselect_relatedすることはできません。

>>> Blog.objects.select_related('blogtag')
Traceback (most recent call last):
....
django.core.exceptions.FieldError: Invalid field name(s) given in select_related: 'blogtag'. Choices are: (none)

まあ、当たり前なんですが。

そういう時にprefetch_relatedを使えます。

こんな風に使えます。

例えば、もともと、こういうDBアクセスがあったとします。

>>> for blog in Blog.objects.filter(title__contains='記事'):
...     BlogTag.objects.filter(blog=blog)
...
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."title" LIKE '%記事%' ESCAPE '\'; args=('%記事%',)
(0.000) SELECT "myapp_blogtag"."id", "myapp_blogtag"."blog_id", "myapp_blogtag"."tag_id" FROM "myapp_blogtag" WHERE "myapp_blogtag"."blog_id" = 1 LIMIT 21; args=(1,)
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."id" = 1; args=(1,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 1; args=(1,)
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."id" = 1; args=(1,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 4; args=(4,)
<QuerySet [<BlogTag: Pythonの記事:<Python>>, <BlogTag: Pythonの記事:<ソフトウェア>>]>
(0.000) SELECT "myapp_blogtag"."id", "myapp_blogtag"."blog_id", "myapp_blogtag"."tag_id" FROM "myapp_blogtag" WHERE "myapp_blogtag"."blog_id" = 2 LIMIT 21; args=(2,)
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."id" = 2; args=(2,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 2; args=(2,)
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."id" = 2; args=(2,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 4; args=(4,)
<QuerySet [<BlogTag: Rubyの記事:<Ruby>>, <BlogTag: Rubyの記事:<ソフトウェア>>]>
(0.000) SELECT "myapp_blogtag"."id", "myapp_blogtag"."blog_id", "myapp_blogtag"."tag_id" FROM "myapp_blogtag" WHERE "myapp_blogtag"."blog_id" = 3 LIMIT 21; args=(3,)
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."id" = 3; args=(3,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 1; args=(1,)
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."id" = 3; args=(3,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 3; args=(3,)
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."id" = 3; args=(3,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 4; args=(4,)
<QuerySet [<BlogTag: Djangoの記事:<Python>>, <BlogTag: Djangoの記事:<Django>>, <BlogTag: Djangoの記事:<ソフトウェア>>]>

これは以下の観点で、効率的ではないですね。

  • Blogレコードに対応するBlogTagのレコードを参照する際に、個別にSQL実行している。
  • BlogTagレコードに対応するBlogレコードやTagレコードを参照する際に、個別にSQL実行している。

prefetch_relatedを使うことで、SQL実行の数を削減することができます。

>>> for blog in Blog.objects.filter(title__contains='記事').prefetch_related('blogtag_set'):
...     blog.blogtag_set.all()
...
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."title" LIKE '%記事%' ESCAPE '\'; args=('%記事%',)
(0.000) SELECT "myapp_blogtag"."id", "myapp_blogtag"."blog_id", "myapp_blogtag"."tag_id" FROM "myapp_blogtag" WHERE "myapp_blogtag"."blog_id" IN (1, 2, 3); args=(1, 2, 3)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 1; args=(1,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 4; args=(4,)
<QuerySet [<BlogTag: Pythonの記事:<Python>>, <BlogTag: Pythonの記事:<ソフトウェア>>]>
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 2; args=(2,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 4; args=(4,)
<QuerySet [<BlogTag: Rubyの記事:<Ruby>>, <BlogTag: Rubyの記事:<ソフトウェア>>]>
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 1; args=(1,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 3; args=(3,)
(0.000) SELECT "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_tag" WHERE "myapp_tag"."id" = 4; args=(4,)
<QuerySet [<BlogTag: Djangoの記事:<Python>>, <BlogTag: Djangoの記事:<Django>>, <BlogTag: Djangoの記事:<ソフトウェア>>]>

forループが始まる前に、where inでまとめてBlogTagのレコードを取得できています。

それでもまだTagテーブルは個別にSQLを実行してしまっています。

select_relatedとprefetch_relatedと合わせて、使ってみます。

>>> for blog in Blog.objects.filter(title__contains='記事').prefetch_related('blogtag_set'):
...     blog.blogtag_set.all().select_related('tag')
...
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."title" LIKE '%記事%' ESCAPE '\'; args=('%記事%',)
(0.000) SELECT "myapp_blogtag"."id", "myapp_blogtag"."blog_id", "myapp_blogtag"."tag_id" FROM "myapp_blogtag" WHERE "myapp_blogtag"."blog_id" IN (1, 2, 3); args=(1, 2, 3)
(0.000) SELECT "myapp_blogtag"."id", "myapp_blogtag"."blog_id", "myapp_blogtag"."tag_id", "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_blogtag" INNER JOIN "myapp_tag" ON ("myapp_blogtag"."tag_id" = "myapp_tag"."id") WHERE "myapp_blogtag"."blog_id" = 1 LIMIT 21; args=(1,)
<QuerySet [<BlogTag: Pythonの記事:<Python>>, <BlogTag: Pythonの記事:<ソフトウェア>>]>
(0.000) SELECT "myapp_blogtag"."id", "myapp_blogtag"."blog_id", "myapp_blogtag"."tag_id", "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_blogtag" INNER JOIN "myapp_tag" ON ("myapp_blogtag"."tag_id" = "myapp_tag"."id") WHERE "myapp_blogtag"."blog_id" = 2 LIMIT 21; args=(2,)
<QuerySet [<BlogTag: Rubyの記事:<Ruby>>, <BlogTag: Rubyの記事:<ソフトウェア>>]>
(0.000) SELECT "myapp_blogtag"."id", "myapp_blogtag"."blog_id", "myapp_blogtag"."tag_id", "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_blogtag" INNER JOIN "myapp_tag" ON ("myapp_blogtag"."tag_id" = "myapp_tag"."id") WHERE "myapp_blogtag"."blog_id" = 3 LIMIT 21; args=(3,)
<QuerySet [<BlogTag: Djangoの記事:<Python>>, <BlogTag: Djangoの記事:<Django>>, <BlogTag: Djangoの記事:<ソフトウェア>>]>

少しマシになりました。

でももう少しSQLの数が減らないものかと思います。

例えば、prefetch_relatedする際に、select_relatedできないの?と思ったりします。

こんなイメージです。

>>> for blog in Blog.objects.filter(title__contains='記事').prefetch_related('blogtag_set').select_related('tag'):
...     blog.blogtag_set.all()
...
Traceback (most recent call last):
...
django.core.exceptions.FieldError: Invalid field name(s) given in select_related: 'tag'. Choices are: (none)

とはいえ、これはできません。

そういう時は、Prefetchオブジェクトを使います。

Prefetchオブジェクト

こんな風に使えます。

>>> from django.db.models import Prefetch
>>> for blog in Blog.objects.filter(title__contains='記事').prefetch_related(Prefetch('blogtag_set', queryset=BlogTag.objects.select_related('tag'))):
...     blog.blogtag_set.all()
...
(0.000) SELECT "myapp_blog"."id", "myapp_blog"."title", "myapp_blog"."content" FROM "myapp_blog" WHERE "myapp_blog"."title" LIKE '%記事%' ESCAPE '\'; args=('%記事%',)
(0.000) SELECT "myapp_blogtag"."id", "myapp_blogtag"."blog_id", "myapp_blogtag"."tag_id", "myapp_tag"."id", "myapp_tag"."name" FROM "myapp_blogtag" INNER JOIN "myapp_tag" ON ("myapp_blogtag"."tag_id" = "myapp_tag"."id") WHERE "myapp_blogtag"."blog_id" IN (1, 2, 3); args=(1, 2, 3)
<QuerySet [<BlogTag: Pythonの記事:<Python>>, <BlogTag: Pythonの記事:<ソフトウェア>>]>
<QuerySet [<BlogTag: Rubyの記事:<Ruby>>, <BlogTag: Rubyの記事:<ソフトウェア>>]>
<QuerySet [<BlogTag: Djangoの記事:<Python>>, <BlogTag: Djangoの記事:<Django>>, <BlogTag: Djangoの記事:<ソフトウェア>>]>

実行されるSQLは二つになりました。

これで、だいぶスッキリしました。

終わり

今回は、DjangoのORMのprefetch_related/select_related/Prefetchの使い方を説明しました。

データ量が少なかったりアクセス数が少ないサービスのリリース初期は、気にならないようなクエリもデータが増えてくると苦しくなってくると思います。

そういう時は、SQLの最適化を検討しましょう。

最後に、アンケートに答えてもらえると励みになります。