blog.evesq.com

脳裏上の森

⚡

Django ORMで育ったエンジニアが生SQLを学び直す 【パフォーマンスチューニング編】

この記事は17分で読めます
DjangoSQLデータベースORM

はじめに

本記事は、Django ORMで育った私が生SQLを学び直すシリーズの4本目、最終回のパフォーマンスチューニング編です。シリーズは次の4本立てでした。

  • モデル定義編: models.pyの定義がどんなテーブルになるのか
  • モデル変更編: makemigrationsとmigrateの裏でどんなALTER TABLEが走るのか
  • クエリ編: filterやupdateの裏でどんなクエリが走っているのか
  • パフォーマンスチューニング編(本記事): N+1問題とselect_related・prefetch_related、集計の裏側

前回のクエリ編までで、テーブルを作り、変え、1本1本のクエリを読み書きできるようになりました。最後は速さの話をします。ORMの便利さの代償として名高いN+1問題は、SQLで見ると何が起きているのか。その対策のselect_relatedとprefetch_relatedは、それぞれどんなSQLを発行してN+1を潰すのか。そして集計のannotateやaggregate、F・Q・Case・Subqueryといった式オブジェクトが、SQLのどの構文に化けるのか。「遅いORMコード」の裏で走っているSQLを覗いて、原因のほうから理解するのがこの回のねらいです。

引き続き、モデルは前回までと同じサンプルモデル(User・Profile・Post・Tag・Comment)を使います。定義の全文とER図はモデル定義編を参照してください。手元では、ユーザー3人(alice・bob・carol)、記事6本、それにタグとコメントを紐付けた状態で確認しています。

今回使うのは、クエリ編の最後で予告したconnection.queriesです。実行されたSQLが1本ずつ入っているので、その本数を数えれば「このORMコードが何本のSQLを投げたか」がそのまま分かります1。N+1問題は、まさにこの本数の問題でした。以降、各実験の頭に「クエリ本数」を添えて進めます。

独自に調べた内容のため、誤りを含むかもしれません。気づいた点があれば指摘してもらえると大変助かります。

N+1問題をSQLで観察する

記事一覧で、各記事の著者名を出したいとします。何気なく書くと、こうなります。

>>> for p in Post.objects.all():
...     print(p.author.name)

一見ふつうのループですが、connection.queriesを数えると、記事6本に対してSQLが7本飛んでいました。

-- クエリ本数: 7本
SELECT ... FROM "blog_post"
SELECT ... FROM "blog_user" WHERE "blog_user"."id" = 1 LIMIT 21
SELECT ... FROM "blog_user" WHERE "blog_user"."id" = 1 LIMIT 21
SELECT ... FROM "blog_user" WHERE "blog_user"."id" = 1 LIMIT 21
SELECT ... FROM "blog_user" WHERE "blog_user"."id" = 2 LIMIT 21
SELECT ... FROM "blog_user" WHERE "blog_user"."id" = 2 LIMIT 21
SELECT ... FROM "blog_user" WHERE "blog_user"."id" = 3 LIMIT 21

1本目が記事の一覧を取るSELECTで、残り6本がp.authorへアクセスするたびに飛ぶ著者のSELECTです。Post.objects.all()の時点ではblog_userには触れていないので、著者が要るたび、記事1本ごとにWHERE id = ?のSELECTが1本ずつ発行されています。末尾のLIMIT 21は、クエリ編で見たgetのあれです。関連を1件辿るのも内部的にはgetなので、同じ形が出ています。

注目したいのは、id=1のaliceを引くSELECTが3回、そっくりそのまま繰り返されていることです。aliceの記事は3本あるので、記事ごとに毎回aliceを引き直しています。「さっき取った著者」を使い回してはくれません。これが、一覧の件数に比例してクエリが増えていく仕組みです。

一覧の1本を「1」、ループで増える分を「N」と数えて、この現象はN+1問題と呼ばれます。今回はN=6で7本でしたが、記事が100本並ぶ一覧なら101本、1000本なら1001本のSQLが飛びます。アプリとDBの往復を図にすると、問題の形がはっきりします。

sequenceDiagram
    participant App as アプリ
    participant DB
    App->>DB: SELECT ... FROM blog_post(一覧)
    DB-->>App: 記事6件
    loop 記事1本ごとに(N回)
        App->>DB: SELECT ... FROM blog_user WHERE id = ?
        DB-->>App: 著者1件
    end

1本のSQLがどれだけ速くても、この往復そのものに固定のコスト(ネットワークの遅延やクエリの解析)がかかります。N+1問題が効いてくるのは、個々のクエリが遅いからではなく、軽いクエリを何度も往復してしまうからです。実際、手元の合計時間は数ミリ秒で、6本のSELECT自体は一瞬でした。それでもDBが別サーバーにいて1往復に数ミリ秒かかる本番環境では、この本数がそのまま遅延に化けます。

同じことは、件数を数えるときにも起きます。各記事のコメント数を出そうとしてp.comments.count()をループで呼ぶと、やはり7本でした。

-- クエリ本数: 7本
SELECT ... FROM "blog_post"
SELECT COUNT(*) AS "__count" FROM "blog_comment" WHERE "blog_comment"."post_id" = 1
SELECT COUNT(*) AS "__count" FROM "blog_comment" WHERE "blog_comment"."post_id" = 2
SELECT COUNT(*) AS "__count" FROM "blog_comment" WHERE "blog_comment"."post_id" = 3
...

こちらは記事ごとにCOUNTが1本ずつ飛んでいます。関連オブジェクトそのものを取るのも、関連の件数を数えるのも、ループの中で関連に触れれば同じN+1になる、というわけです。ではどう1本にまとめるのか。ここからが対策です。DjangoにはJOINで潰すselect_relatedと、別クエリで潰すprefetch_relatedの2つが用意されています。順に見ます。

さきほどのループに、select_related("author")を1つ足します。

>>> for p in Post.objects.select_related("author"):
...     print(p.author.name)
-- クエリ本数: 1本
SELECT "blog_post"."id", ..., "blog_post"."updated_at",
       "blog_user"."id", "blog_user"."name", "blog_user"."email"
FROM "blog_post" INNER JOIN "blog_user" ON ("blog_post"."author_id" = "blog_user"."id")

7本が1本になりました。出てきたのはJOINです。JOINそのものはクエリ編で「リレーションをまたぐfilter」として見ましたが、あのときと役割が違います。filterのJOINは、blog_user側の列で絞り込むための道でした。今回のJOINで肝心なのはSELECT句のほうです。よく見ると、blog_postの全列に続けてblog_userのid・name・emailまで並んでいます。記事とその著者を1つの行にくっつけて、まとめて1回で取ってきているのです。

JOINが2つのテーブルをどう1つの行にするか、結果の形を表で見ておきます。ON ("blog_post"."author_id" = "blog_user"."id")は「記事のauthor_idと一致するユーザーの行を横に繋げる」という指定で、結果はこうなります。

blog_post.idblog_post.titleblog_post.author_idblog_user.idblog_user.name
1DjangoのORM入門11alice
2SQLを学び直す11alice
4Python 3.14の新機能22bob

記事1行につき、その著者の列がぴったり横に付いた1行ができます。Djangoはこの1本のSELECT結果から、Postインスタンスと一緒にauthorのインスタンスまで組み立ててキャッシュしておきます。だからループでp.authorに触れても、もうDBには行きません。N+1のNが丸ごと消えた、という理屈です。

INNER JOINとLEFT OUTER JOIN

上のSQLはINNER JOINでした。INNER JOINは「両側で相手が見つかった行だけ」を残します。記事のauthorは必須(NOT NULL)なので、著者のいない記事は存在せず、INNERで取りこぼしはありません。

一方、相手がいないかもしれないリレーションだと、JOINの種類が変わります。ユーザーとプロフィールの関係で試します。プロフィールはcarolだけ持っていません。

>>> for u in User.objects.select_related("profile"):
...     ...
-- クエリ本数: 1本
SELECT "blog_user"."id", "blog_user"."name", "blog_user"."email",
       "blog_profile"."id", "blog_profile"."user_id", "blog_profile"."bio", "blog_profile"."links"
FROM "blog_user" LEFT OUTER JOIN "blog_profile" ON ("blog_user"."id" = "blog_profile"."user_id")

LEFT OUTER JOINになりました。これもクエリ編の__isnullの節で顔を出した部品で、左側(blog_user)の行は全部残し、相手のいないcarolの行はプロフィール側の列をNULLで埋めて繋ぎます。もしここでINNER JOINを使ってしまうと、プロフィールを持たないcarolがJOINの時点で消えてしまい、一覧から1人抜け落ちます。Djangoは、繋ぐ相手がNULLになりうるか(=リレーションのnull許容)を見て、INNERとLEFT OUTERを選び分けていました。

select_relatedが使えるのはFKとOneToOneまで

ここまでの例で、select_relatedの相手はどちらもForeignKeyかOneToOneでした。これは制約です。select_relatedは「JOINで横に繋いで1行にまとめる」戦略なので、繋いだ結果、記事の行数が増えてしまう関連には使えません。

思い出したいのが、クエリ編で見た「JOINすると行が増える」現象です。1人のユーザーが公開記事を2本持っていると、ユーザーとその記事をJOINした結果はユーザーの行が2行に増えました。ManyToManyや逆参照(1対多の「多」側)を横に繋ぐと、まさにこれが起きて、元のテーブルの行が水増しされてしまいます。だからそちら向きには、行を増やさない別の戦略が要ります。それがprefetch_relatedです。

各記事のコメント一覧を出す、というN+1が起きやすい形を、prefetch_relatedで書きます。

>>> for p in Post.objects.prefetch_related("comments"):
...     list(p.comments.all())
-- クエリ本数: 2本
SELECT ... FROM "blog_post"
SELECT ... FROM "blog_comment" WHERE "blog_comment"."post_id" IN (1, 2, 3, 4, 5, 6)

select_relatedと違い、SQLが1本ではなく2本でした。ただしN+1の7本ではなく、きっちり2本で止まっています。1本目でまず記事を全部取り、そこに登場した記事のid(1〜6)を集めて、2本目でWHERE post_id IN (...)とまとめて問い合わせる。取れたコメントを、DjangoがPython側で記事ごとに振り分けてキャッシュします。JOINで1つの表にするのではなく、関連を別クエリで一気に取ってきてアプリ側で結合する、という戦略です。

なぜJOINにしないかは、前の節のとおりです。記事とコメントをJOINすると、コメント3件を持つ記事は3行に増え、本文(content)のような大きな列が3回ぶん重複して転送されます。記事が多くコメントも多いと、この水増しが効いてきます。IN句で分けて取れば、記事とコメントをそれぞれ1回ずつしか運びません。

ManyToManyも同じ形です。タグで試すと、中間テーブルを経由する2本目になりました。

-- クエリ本数: 2本
SELECT ... FROM "blog_post"
SELECT ("blog_post_tags"."post_id") AS "_prefetch_related_val_post_id", "blog_tag"."id", "blog_tag"."name"
FROM "blog_tag" INNER JOIN "blog_post_tags" ON ("blog_tag"."id" = "blog_post_tags"."tag_id")
WHERE "blog_post_tags"."post_id" IN (1, 2, 3, 4, 5, 6)

2本目の中でJOINが使われていますが、これはタグ本体と中間テーブルを繋ぐためのもので、記事側とはIN句で分けたままです。モデル定義編で見た中間テーブルblog_post_tagsが、ここでも経由地として働いています。先頭の_prefetch_related_val_post_idは、取れたタグをどの記事に振り分けるかをDjangoが判断するために、post_idを一緒に持ち帰っている列です。

2つの対策を、SQLの形で対比しておきます。

select_relatedprefetch_related
SQLの本数1本(JOIN)2本(本体 + IN)
結合する場所DB(JOIN)Python側
使える関連ForeignKey / OneToOneManyToMany / 逆参照(1対多)も可
向いていない場面行が増える関連1対1で十分なとき(1本で済むのに2本になる)

選び方は「相手が1件に定まるならselect_related(JOINで1本)、相手が複数になりうるならprefetch_related(INで2本)」と押さえておけば、まず外しません。どちらも、クエリの本数をNから定数に畳むための道具でした。

集計はGROUP BYで理解する

N+1の次は集計です。件数や合計、平均といった集計は、SQLではCOUNTやSUMといった集計関数と、GROUP BYという構文で表現されます。Django側のaggregateとannotateが、それぞれどちらに対応するのかを見ていきます。

aggregateとCount・Sum・Avg・Min・Max

まずaggregateです。全記事の件数と平均閲覧数を、一度に求めてみます。

>>> Post.objects.aggregate(Count("id"), Avg("view_count"))
{'id__count': 6, 'view_count__avg': 128.33...}
-- クエリ本数: 1本
SELECT COUNT("blog_post"."id") AS "id__count", AVG("blog_post"."view_count") AS "view_count__avg"
FROM "blog_post"

CountやAvgが、そのままSQLの集計関数COUNT・AVGになりました。集計関数は、たくさんの行を受け取って1つの値に畳み込む関数です。COUNTは行数、SUMは合計、AVGは平均、MINとMAXは最小・最大。このSELECTにはWHEREやGROUP BYが付かないので、テーブル全体という1つのかたまりに対して集計関数が効き、結果は1行(dict1つ)だけ返ります。絞り込んでから集計したいなら、いつものfilterを前に挟むだけです。

>>> Post.objects.filter(status="published").aggregate(Sum("view_count"), Min("view_count"), Max("view_count"))
SELECT SUM("blog_post"."view_count") AS "view_count__sum", MIN(...) AS "view_count__min", MAX(...) AS "view_count__max"
FROM "blog_post" WHERE "blog_post"."status" = 'published'

filterが組み立てるWHERE句は、クエリ編で見たものと同じ部品が、そのまま集計のSELECTにも刺さっています。「公開記事だけを対象に合計・最小・最大」が1本のSQLで求まりました。

ちなみに、件数だけならおなじみのcount()があります。

>>> Post.objects.count()
SELECT COUNT(*) AS "__count" FROM "blog_post"

aggregate(Count("id"))とほぼ同じで、COUNT(*)になります。件数を知りたいだけならこちらが素直です。

annotateとGROUP BY

aggregateがテーブル全体を1つの値に畳むのに対して、annotateは「行ごとに集計列を足す」操作です。各ユーザーの投稿数を求めてみます。

>>> User.objects.annotate(post_count=Count("posts"))
-- クエリ本数: 1本
SELECT "blog_user"."id", "blog_user"."name", "blog_user"."email", COUNT("blog_post"."id") AS "post_count"
FROM "blog_user" LEFT OUTER JOIN "blog_post" ON ("blog_user"."id" = "blog_post"."author_id")
GROUP BY "blog_user"."id"

新しく出てきたのがGROUP BYです。GROUP BYは、指定した列が同じ値の行を1つのグループにまとめ、集計関数をグループごとに効かせる構文です。ここではGROUP BY "blog_user"."id"なので、ユーザーIDごとに記事をまとめ、そのグループの中でCOUNTを数えています。動きを表で追ってみます。まずJOINでユーザーと記事を繋ぐと、次のようになります。

blog_user.idblog_post.id
1 (alice)1
1 (alice)2
1 (alice)3
2 (bob)4
2 (bob)5
3 (carol)6

これをuser.idでまとめると、alice=3行・bob=2行・carol=1行のグループになり、各グループの行数をCOUNTした値がpost_countになります。集計関数は、テーブル全体ではなくこのグループ単位に効く、というのがaggregateとの違いでした。JOINがLEFT OUTERなのは、記事が0本のユーザーもCOUNT=0として残すためです(INNERだと0本のユーザーが消えます)。

もうひとつ、クエリ編のvaluesで張った伏線をここで回収します。valuesとannotateを組み合わせると、GROUP BYの対象が変わります。ステータスごとの記事数を数えてみます。

>>> Post.objects.values("status").annotate(n=Count("id"))
SELECT "blog_post"."status" AS "status", COUNT("blog_post"."id") AS "n"
FROM "blog_post" GROUP BY 1

今度はGROUP BY 1、つまりSELECTの1番目の列(status)でまとめています。annotateの前に置いたvalues(“status”)が、そのままGROUP BYの単位を決めているのです。ここがハマりどころで、valuesに何を並べるかでグループの粒度が変わります。さきほどのユーザーの例がGROUP BY user.idだったのに対し、valuesを挟むとその列でグルーピングされる。「行ごとの集計はannotate、その集計の単位はGROUP BY(valuesで指定できる)」と対応づけておくと、狙った粒度で数えられます。

exists()とcount()の使い分け

集計に関連して、実務でよく迷うexists()とcount()の違いもSQLで見ておきます。「公開記事が1本でもあるか」を知りたいとき、両方書けてしまいます。

>>> Post.objects.filter(status="published").exists()
SELECT 1 AS "a" FROM "blog_post" WHERE "blog_post"."status" = 'published' LIMIT 1
>>> Post.objects.filter(status="published").count()
SELECT COUNT(*) AS "__count" FROM "blog_post" WHERE "blog_post"."status" = 'published'

exists()はSELECT 1 ... LIMIT 1、つまり「条件に合う行が1つ見つかった時点で打ち切り」です。存在確認なら1件見つければ十分なので、DBは最初の1件を見つけしだい仕事をやめられます。対してcount()はCOUNT(*)で、条件に合う行を全部数え切ります。「あるかどうかだけ知りたいのに、全部数える」のが無駄だ、というのがSQLの仕事量として見えます。

同じ理由で、if queryset:という書き方にも注意が要ります。QuerySetを真偽判定すると、Djangoは全件をSELECTしてリストに読み込んでから、空かどうかを見ます。

-- if qs: で走るSQL(全カラム・全件)
SELECT "blog_post"."id", ..., "blog_post"."updated_at" FROM "blog_post" WHERE "blog_post"."status" = 'published'

存在を知りたいだけなのに、全記事の全カラムを運んでいます。if queryset.exists():と書けば、上のLIMIT 1で済みます。「あるか?」にはexists()、と決めておくのが安全でした。

EXPLAINで実行計画を読む

ここまでは「何本SQLが飛ぶか」の話でした。最後に、1本のSQLがDBの中でどう処理されるかを覗きます。使うのはEXPLAINです。SQLの頭にEXPLAINを付けると、DBがそのクエリをどう実行するつもりか(実行計画)を教えてくれます。DjangoではQuerySetのexplain()で見られます。

モデル変更編で、published_atにインデックスを張りました(db_index=True)。あれが本当に使われているのかを、ここで確かめます。ただし、手元の6行のような小さなテーブルだと、DBは「全部読んだほうが速い」と判断してインデックスを使いません。そこで、published_atを散らした記事を1万行ほど投入した状態(実験後にロールバック)で、少数だけがヒットする条件で絞ってみます。

>>> print(Post.objects.filter(published_at__gte=recent).explain())
Index Scan using blog_post_published_at_9524a659 on blog_post  (cost=0.29..9.37 rows=31 width=74)
  Index Cond: (published_at >= '2026-07-08 02:20:55+00'::timestamp with time zone)

Index Scanと出ました。使われているインデックスの名前blog_post_published_at_9524a659は、モデル変更編でmigrateしたときにDjangoが作ったものです。あのときCREATE INDEXで作られた索引が、今こうしてクエリの高速化に効いている、というのが伏線の回収です。行の頭にあるcost=0.29..9.37は、DBが見積もったコストの目安で、小さいほど軽い見込みです。

比べるために、インデックスを張っていないview_countで、同じくらいの件数を絞ってみます。

>>> print(Post.objects.filter(view_count__lt=30).explain())
Seq Scan on blog_post  (cost=0.00..268.07 rows=32 width=74)
  Filter: (view_count < 30)

今度はSeq Scanです。Seq Scanは「テーブルを先頭から全行読んで、条件に合う行だけ拾う」という総当たりの読み方です。costの右の数字を見比べると、Index Scanが9.37だったのに対しSeq Scanは268.07。インデックスのある列で絞ると索引をたどって目的の行だけ読み、ない列で絞ると全行を読んで振るい落とす、という差がコストにそのまま出ています。この2つを見分けられると、「遅いクエリにインデックスが効いていない」状況を実行計画から読み取れます。

さらにexplain(analyze=True)を付けると、見積もりだけでなく実際に実行して実測値まで見せてくれます。

Index Scan using blog_post_published_at_9524a659 on blog_post  (cost=0.29..9.37 rows=31 width=74) (actual time=0.003..0.007 rows=32 loops=1)
  Index Cond: (published_at >= '...')
Planning Time: 0.022 ms
Execution Time: 0.011 ms

actual timeやExecution Timeが、実際にかかった時間です。本番で「なぜかこのクエリだけ遅い」というとき、まずEXPLAIN ANALYZEにかけて、Seq Scanになっていないか・想定した件数で済んでいるかを見るのが調査の入口になります。ORMのexplain()から同じことができる、と知っておくと便利です。

式を組み立てるオブジェクトたち

ここからは、より込み入ったSQLを組み立てるための道具です。filterやexcludeだけでは書けなかった条件や更新が、F・Q・Case・Subqueryといったオブジェクトで表現できます。それぞれSQLのどの構文になるのかを確かめます。

F: カラム同士の比較・更新

ふつう、閲覧数を1増やすには「取ってきて、+1して、保存」と書きたくなります。しかしそれだと、取得と保存のあいだに別の処理が同じ行を触ると、片方の更新が消える競合(レースコンディション)を招きます。Fを使うと、この計算をDBの中で完結させられます。

>>> Post.objects.filter(id=1).update(view_count=F("view_count") + 1)
-- クエリ本数: 1本
UPDATE "blog_post" SET "view_count" = ("blog_post"."view_count" + 1) WHERE "blog_post"."id" = 1

F("view_count")が、SQLの中の"blog_post"."view_count"という列参照になりました。値をPython側に持ってこず、SET view_count = view_count + 1とDBに計算させています。現在値を読んでから書くのではなく、DBが1つのUPDATEの中で読み書きするので、さきほどの競合が起きません。在庫や残高、カウンタのような「今の値を元に増減する」更新では、この書き方が安全です。

Fは更新だけでなく、列同士の比較にも使えます。「公開後に更新された記事」を、updated_atとpublished_atの比較で探します。

>>> Post.objects.filter(updated_at__gt=F("published_at"))
SELECT ... FROM "blog_post" WHERE "blog_post"."updated_at" > ("blog_post"."published_at")

WHEREの右辺が、定数ではなく別の列になっています。ふつうのfilterはview_count__gt=100のように列と定数を比べますが、Fを渡すと2つの列同士を比べるWHEREが書けます。

Q: OR条件と複雑なWHERE

クエリ編で見たとおり、filterに条件を並べるとANDで繋がれます。ORを書きたいときに使うのがQです。

>>> Post.objects.filter(Q(status="published") | Q(view_count__gte=200))
SELECT ... FROM "blog_post" WHERE ("blog_post"."status" = 'published' OR "blog_post"."view_count" >= 200)

|がSQLのORになりました。Qを使わないfilterではANDしか書けなかったのが、Qを|で繋ぐと初めてWHEREにORが現れます。同じように~Q(...)はNOTです。

>>> Post.objects.filter(~Q(status="draft"))
SELECT ... FROM "blog_post" WHERE NOT ("blog_post"."status" = 'draft')

これはクエリ編のexcludeと同じ形のWHEREで、実際exclude(status="draft")とfilter(~Q(status="draft"))は同じSQLになります。Qとふつうのキーワード引数は混ぜられて、そのときQ側がOR、キーワード側がANDで結ばれます。

>>> Post.objects.filter(Q(view_count__lt=50) | Q(view_count__gte=300), status="published")
SELECT ... FROM "blog_post"
WHERE (("blog_post"."view_count" < 50 OR "blog_post"."view_count" >= 300) AND "blog_post"."status" = 'published')

(A OR B) AND Cという括弧付きのWHEREが組み上がりました。「閲覧数が極端(50未満か300以上)で、かつ公開済み」という条件です。Qは、ANDだけでは表せない入り組んだWHEREを、括弧の構造ごと組み立てるための道具でした。

Case・When: SQLのCASE式

行の値によって表示を出し分けたい、というとき使うのがCaseとWhenです。閲覧数で記事に人気度ラベルを付けてみます。

>>> Post.objects.annotate(
...     popularity=Case(
...         When(view_count__gte=200, then=Value("人気")),
...         When(view_count__gte=100, then=Value("普通")),
...         default=Value("低調"),
...     )
... )
SELECT "blog_post"."title", "blog_post"."view_count",
       CASE WHEN "blog_post"."view_count" >= 200 THEN '人気'
            WHEN "blog_post"."view_count" >= 100 THEN '普通'
            ELSE '低調' END AS "popularity"
FROM "blog_post"

Case/Whenが、SQLのCASE式になりました。CASE WHEN 条件 THEN 値 ... ELSE 値 ENDは、行ごとに条件を上から順に見て、最初に当てはまった値を返すSQLの条件分岐です。クエリ編のbulk_updateでちらっと出てきたCASEを、今度は自分で組み立てた形です。上から評価されるので、>= 200の枝を>= 100より前に書く順序が効いてきます。

この条件分岐は、集計と組み合わせると強力です。「各ユーザーの公開記事だけを数える」条件付きの集計を書いてみます。

>>> User.objects.annotate(published_count=Count("posts", filter=Q(posts__status="published")))
SELECT "blog_user"."id", "blog_user"."name", "blog_user"."email",
       COUNT("blog_post"."id") FILTER (WHERE "blog_post"."status" = 'published') AS "published_count"
FROM "blog_user" LEFT OUTER JOIN "blog_post" ON ("blog_user"."id" = "blog_post"."author_id")
GROUP BY "blog_user"."id"

Countにfilter=を渡すと、COUNT(...) FILTER (WHERE ...)という構文になりました。これは「グループの中で、条件に合う行だけを数える」というSQL標準の書き方です。annotateのGROUP BYで全記事をユーザーごとにまとめつつ、そのうち公開済みだけをCOUNTしています。ステータス別に何本ずつ、といった集計をユーザー単位で一度に出せます。

SubqueryとOuterRef: 相関サブクエリ

最後は、一覧に「各ユーザーの最新記事タイトル」を添えたい、という題材です。ユーザーごとに記事を1本選ぶので、これまでの集計関数では表せません。使うのがSubqueryとOuterRefです。

>>> latest = Post.objects.filter(author=OuterRef("pk")).order_by("-published_at")
>>> User.objects.annotate(latest_title=Subquery(latest.values("title")[:1]))
SELECT "blog_user"."name",
       (SELECT U0."title" FROM "blog_post" U0
        WHERE U0."author_id" = ("blog_user"."id")
        ORDER BY U0."published_at" DESC LIMIT 1) AS "latest_title"
FROM "blog_user"

SELECT句の中に、括弧でくくられた別のSELECTが入りました。これがサブクエリ(クエリの中のクエリ)です。注目はWHERE U0."author_id" = ("blog_user"."id")の部分で、内側のSELECTが外側のblog_userの列を参照しています。OuterRef("pk")が、この「外側の行のid」への参照になっていました。

外側の1行ごとに、その行の値(ユーザーid)を使って内側のSELECTが動く、こういうサブクエリを相関サブクエリと呼びます。ユーザーaliceの行を作るときは内側がauthor_id = 1で最新記事を1本引き、bobの行ではauthor_id = 2で引く。行ごとに内側のクエリが連動する、という形です。.values("title")[:1]が内側のSELECT title ... LIMIT 1に対応しています。

annotateのGROUP BYでも似たことはできますが、相関サブクエリが向くのは「グループごとに1件を選んで、その行の別の列(ここではtitle)がほしい」ような、単純な集計関数では表しにくい場面です。SELECTの中にSELECTを埋め込める、と引き出しに入れておくと、複雑な一覧を1本のSQLで組めます。

まとめ

最終回は、速さの視点でORMとSQLを並べてきました。

N+1問題への対処は、突き詰めると2択でした。JOINで1本にまとめる(select_related)か、IN句でまとめて取って別クエリにする(prefetch_related)か。どちらを選ぶかは、辿る相手が1件に定まるか複数になりうるかで決まります。N本に膨らんでいたクエリを、定数本に畳むのが目的でした。

集計とその周辺は、SQLの構文にきれいに対応していました。今回の対応を一枚にまとめます。

ORM側SQL側
select_relatedJOIN(1本にまとめる)
prefetch_related本体SELECT + WHERE … IN(2本)
aggregate(Count・Sum・Avg・Min・Max)集計関数(GROUP BYなし・1行返す)
annotate集計列 + GROUP BY
values().annotate()GROUP BYの単位をvaluesで指定
exists() / count()SELECT 1 … LIMIT 1 / COUNT(*)
explain()EXPLAIN(Index Scan / Seq Scan)
F列参照(列同士の計算・比較)
Q(| / ~)WHEREのOR / NOT
Case・WhenCASE WHEN … END
Subquery・OuterRef相関サブクエリ

最後に、シリーズ全体を振り返ります。4本を通してやってきたのは、ORMのコードを1枚めくってSQLを見るという同じ作業でした。モデル定義のCREATE TABLE、マイグレーションのALTER TABLE、filterのSELECT、N+1の裏で走るSQL。どれも、めくればSQLが待っていました。そこで分かったのは、ORMが素朴な翻訳機ではなく、安全側・効率側の工夫を随所に埋めていることです。JOINで行が増える。OFFSETは読み飛ばす行も読む。N+1は往復の数で効いてくる。こうしたSQL側の性質は、ORMを使っていても消えませんでした。

冒頭のモデル定義編で、「SELECTやWHERE、JOINを知ってはいるが正確に説明できない」と書きました。4本を経て、その一つひとつをORMのコードと対応づけて読めるようになったと思います。ここで身についたSQLの見方は、Django以外でも効くはずです。PrismaやTypeORMのドキュメントを読むときも、結局は「このメソッドはどんなSELECTやJOINになるのか」を追うことになります。ORMの向こうにSQLが見えるようになったことがいちばんの収穫でした。

参考文献

IT入門書籍 スッキリシリーズ
わかる・たのしい・実は深い!
https://sukkiri.jp/books/sukkiri_sql4
初めてのSQL 第3版
SQL言語に初めて触れるプログラマを対象に、SQL言語の基本を解説します。データベースの歴史、概念などの基礎知識から、データベースの作成、クエリの基本、フィルタリング、トランザクションなどまで、実践に必要なポイントを過不足なくコンパクトにまとめ、理解を深められるよう練習問題を用意しています。改訂にあたり、「ビュー」「メタデータ」「解析関数」「大規模なデータベースの操作」「SQLとビッグデータ」の章が追加され、さらに充実しました。SQLを学び始めるのに最適の一冊です。
https://www.oreilly.co.jp/books/9784873119588/

脚注

  1. https://docs.djangoproject.com/en/6.0/faq/models/#how-can-i-see-the-raw-sql-queries-django-is-running ↩

新着記事

関連ネットワーク(beta)

ドラッグで移動 / Ctrl+ホイールでズーム
Django ORMで育ったエンジニアが生SQLを学び直す 【パフォーマンスチューニング編】Django ORMで育…Django ORMで育ったエンジニアが生SQLを学び直す【クエリ編】Django ORMで育…Django ORMで育ったエンジニアが生SQLを学び直す 【モデル定義編】Django ORMで育…Django ORMで育ったエンジニアが生SQLを学び直す【モデル変更編】Django ORMで育…Django transaction.atomicについてDjango trans…DjangoのTrigramSimilarityで類似度検索DjangoのTrigr…Django5.0から5.1へアップデートしたら、No such file or directoryになったDjango5.0から5…