はじめに
前回は基本的なN+1問題とSQLの構成についてお話しました。結論として、
| 関係 | 使うメソッド | JOINするか | SQLの発行回数 |
|---|---|---|---|
| 参照先が1件に限定できる | select_related | する | 1回 |
| 参照先が1件に限定できない | prefetch_related | しない | 2回以上 |
という使い分けをお話しました。比較的シンプルな例であれば、これで概ね問題ないのですが、場合によってはもう少し細かく考えなければならない場合もあります。今回は自分が実際にやらかした間違いを元に、①関連が多段になった場合のselect_related / prefetch_relatedの使い分け、②JOINによって Count() が水増しされる問題、の2つをまとめていきたいと思います。
1.) 今回使用するモデルとデータ
前回使ったのと似たようなデータです。前回と同じく、
- 組織と教室、生徒がいる
- ある教室はいずれか1つの組織に所属する
- ある生徒はいずれか1つの組織に所属する
という条件です。モデルの定義は以下のようになります。
class Organization(models.Model):
id = models.PositiveIntegerField()
name = models.CharField(max_length=100)
class Student(models.Model):
id = models.PositiveIntegerField()
name = models.CharField(max_length=100)
organization = models.ForeignKey(
"Organization",
related_name="students",
on_delete=models.CASCADE
)
class Classroom(models.Model):
id = models.PositiveIntegerField()
name = models.CharField()
organization = models.ForeignKey(
"Organization",
related_name="classrooms",
on_delete=models.CASCADE
)
なお、今回もon_deleteだとか、idだとか、その辺の細かい話は、単純化のために触れないこととします。
また、SQLを今回もたくさん書きますが、あくまで概念的なもので、これまた単純化されている件にご注意下さい。
ここで、DBには以下のようなデータが入っていると仮定します。
organization
| id | name |
|---|---|
| 1 | A校 |
| 2 | B校 |
student
| id | name | organization_id |
|---|---|---|
| 1 | 山田 | 1 |
| 2 | 佐藤 | 1 |
| 3 | 木村 | 1 |
| 4 | 鈴木 | 2 |
classroom
| id | name | organization_id |
|---|---|---|
| 1 | A校・数学教室 | 1 |
| 2 | A校・英語教室 | 1 |
| 3 | B校・数学教室 | 2 |
2.) 一見select_related()だけでよさそうなケース
では、ここからは具体的な例を考えていきます。複数の生徒ごとに、どのような組織に属していて、どのような教室に所属しているか、その一覧を確認できる画面を作成したいと思います。
簡易的には以下のようなコードです。
students = Student.objects.select_related("organization")
for student in students:
print(student.name)
print(student.organization.name)
for classroom in student.organization.classrooms.all():
print(classroom.name)
ポイントは当然select_relatedです。生徒から見れば、組織は1つに限定されます。であれば、
参照先が1件に限定できるようであればselect_related
に従うと、organizationにはselect_relatedで問題ないように見えます。
2-1.) select_related()で解決できている部分
実際、確かに効果はあります。
students = Student.objects.select_related("organization")
この部分は、
for student in students:
ここで実際に、以下のようなクエリが発行されます。
SELECT student.id,
student.name,
student.organization_id,
organization.id,
organization.name
FROM student
JOIN organization
ON student.organization_id = organization.id;
student側の組織IDと一致するIDがJOINされて、以下のような結果セットが得られます。
| id | name | student.organization_id | organization.id | organization.name |
|---|---|---|---|---|
| 1 | 山田 | 1 | 1 | A校 |
| 2 | 佐藤 | 1 | 1 | A校 |
| 3 | 木村 | 1 | 1 | A校 |
| 4 | 鈴木 | 2 | 2 | B校 |
そのため、
print(student.organization.name)
ここで追加のクエリが発行されるのは防止することができました。
2-2.) それでも残るN+1問題
ただ、それで全部解決した？というとそうでもなく、というのも、
for classroom in student.organization.classrooms.all():
print(classroom.name)
この部分があるからですね。organizationでとどまらず、classroomsまで取得しています。先程studentでは
SELECT student.id,
student.name,
student.organization_id,
organization.id,
organization.name
FROM student
JOIN organization
ON student.organization_id = organization.id;
というクエリで、
| id | name | student.organization_id | organization.id | organization.name |
|---|---|---|---|---|
| 1 | 山田 | 1 | 1 | A校 |
| 2 | 佐藤 | 1 | 1 | A校 |
| 3 | 木村 | 1 | 1 | A校 |
| 4 | 鈴木 | 2 | 2 | B校 |
という結果セットが発生していました。しかし、ここには教室が含まれていません。そのため、新たにクエリを発行して教室を取得する必要があります。結果として、
for classroom in student.organization.classrooms.all():
print(classroom.name)
の部分で、
山田さん(A校)
SELECT classroom.id,
classroom.name,
classroom.organization_id
FROM classroom
WHERE classroom.organization_id = 1;
佐藤さん(A校)
SELECT classroom.id,
classroom.name,
classroom.organization_id
FROM classroom
WHERE classroom.organization_id = 1;
木村さん(A校)
SELECT classroom.id,
classroom.name,
classroom.organization_id
FROM classroom
WHERE classroom.organization_id = 1;
鈴木さん(B校)
SELECT classroom.id,
classroom.name,
classroom.organization_id
FROM classroom
WHERE classroom.organization_id = 2;
という形で、ループ1回につき、クエリ発行が1回発生します。N+1問題ですね。幸いにして、organization.idとして1,2は取得済みなので、取得自体はできますが、典型的なN＋1問題ですね。
3.) 多段リレーションをprefetch_related()する
では、これを防止するためにはどうすればよいのか、ということですが、結論としては以下のようになります。
students = (
Student.objects
.select_related("organization")
.prefetch_related("organization__classrooms")
)
Student→OrganizationはFKによる単一参照なので、select_relatedで取得し、classroomはorganizationに対して1:Mなのでprefetch_relatedで取得します。
3-1.) 発行されるSQL
このORMでは2回クエリが発行されます。SQLは以下のような感じになります。
まずは1回目、select_relatedまでですね。これ自体は先ほどと同じです。
-- 1回目：StudentとOrganization
SELECT student.id,
student.name,
student.organization_id,
organization.id,
organization.name
FROM student
JOIN organization
ON student.organization_id = organization.id;
結果セットは
| id | name | student.organization_id | organization.id | organization.name |
|---|---|---|---|---|
| 1 | 山田 | 1 | 1 | A校 |
| 2 | 佐藤 | 1 | 1 | A校 |
| 3 | 木村 | 1 | 1 | A校 |
| 4 | 鈴木 | 2 | 2 | B校 |
です。
ここからさらに、organizationとそこに紐づいた教室をまとめて取得します。
発行されるSQLは以下のようになります。
-- 2回目：関連するClassroomをまとめて取得
SELECT id,
name,
organization_id
FROM classroom
WHERE organization_id in (1, 2);
1,2は既にDjango側で取得済みの組織IDを使っている形です。
取得される結果セットは以下のようになります。
| id | name | organization_id |
|---|---|---|
| 1 | A校・数学教室 | 1 |
| 2 | A校・英語教室 | 1 |
| 3 | B校・数学教室 | 2 |
3-2.) Python側での対応付け
ここからDjango側でorganization_idを使い、内部で以下のような構造のオブジェクトを作る形で紐づけしていきます。
organizations = {
1: {
"name": "A校",
"classrooms": [
"A校・数学教室",
"A校・英語教室",
],
},
2: {
"name": "B校",
"classrooms": [
"B校・数学教室",
],
},
}
これが内部に保持されるため、
for classroom in student.organization.classrooms.all():
print(classroom.name)
のallの部分で追加のクエリが発行されなくなります。
3-3.) select_related()かprefetch_related()かは、最初の一段だけでは決まらない
この例からわかることは、selectなのかprefetchなのかも正確に判断するためには、構造を一段一段別個に把握する必要があるということです。表にすると、以下みたいな感じになります。
| アクセス経路 | 関連先の件数 | 基本的な取得方法 |
|---|---|---|
student.organization |
最大1件 | select_related() |
organization.classrooms |
複数件 | prefetch_related() |
student.organization.classrooms |
途中は1件、末端は複数件 | 両方を組み合わせる |
Student→OrganizationはFKによる単一参照なのでselect。Organization→Classroomは1件だと限定できないので、prefetch。
このように、自分がどのような形でデータを利用するかきちんと把握したうえで、Student→Organization→Classroomのような辿り方を理解して、適切に設計する必要があります。
AIにおまかせするのも悪くはないのですが、結構綿密に指定しないと、見えている範囲だけ、与えている範囲だけで探索を完結させてしまい、先の先までは辿ってくれない場合があります。そのため、一度自分でチェックするのがやはり安全だと思います。
4.) もう一つの問題：JOINによるCount()の水増し
ここからはまた別のやらかしについて記述します。
まずは例として、組織ごとにその組織に所属している生徒数、および教室数を同時に取得したいという場合を考えます。データは1章と同じものをそのまま使います。
これに対して、以下のような集計を行いたいとします。
| organization | student_count | classroom_count |
|---|---|---|
| A校 | 3 | 2 |
| B校 | 1 | 1 |
4-1.) annotateとは
ここで利用するのがannotateです。名前の通り、既存のデータに加えて、一時的に別の列を追加する働きがあります。まずはシンプルに行きましょう。教室のことは一旦忘れて、組織ごとの生徒数をカウントしてみたいと思います。annotateは以下のように、「追加したい列名=値」という引数を与えて利用します。
from django.db.models import Count
organizations = Organization.objects.annotate(
student_count=Count("students")
)
こうすることで、以下のようなSQLが発行されます。
SELECT organization.id,
organization.name,
Count(student.id) as student_count
FROM Organization
LEFT JOIN Student
ON organization.id = student.organization_id
GROUP BY organization.id,
organization.name;
これにより、まずOrganizationとStudentがJOINされて、以下のようなテーブルができます。
| organization.id | organization.name | student.id | student.name | student.organization_id |
|---|---|---|---|---|
| 1 | A校 | 1 | 山田 | 1 |
| 1 | A校 | 2 | 佐藤 | 1 |
| 1 | A校 | 3 | 木村 | 1 |
| 2 | B校 | 4 | 鈴木 | 2 |
次に、organization.id,organization.nameを基準としてグループ分けが実行されます。
このとき、Studentの情報はCountで圧縮されて、行数だけカウントされ以下のような結果セットができます。
| organization.id | organization.name | student_count |
|---|---|---|
| 1 | A校 | 3 |
| 2 | B校 | 1 |
組織ごとの生徒数を把握したいという需要をしっかりと満たしていますね。
4-2.) annotate()で二つのCount()を追加する
基本を抑えたところで、次は「組織ごとの生徒数と教室数を把握したい」という本来の機能に戻りたいと思います。
まずは先ほどと同様に、
organizations = Organization.objects.annotate(
student_count=Count("students"),
classroom_count=Count("classrooms"),
)
と素直にannotateの中に追加してみます。発行されるSQLは、
SELECT organization.id,
organization.name,
Count(student.id) as student_count,
Count(classroom.id) as classroom_count
FROM Organization
LEFT JOIN Student
ON organization.id = student.organization_id
LEFT JOIN Classroom
ON organization.id = classroom.organization_id
GROUP BY organization.id,
organization.name;
です。一見問題なさそうですが、実はこれ、上手く働きません。理由は前回の記事で取り扱ったJOINによる行の増殖が発生するからです。
まずは
FROM Organization
LEFT JOIN Student
ON organization.id = student.organization_id
ここですが、これは先程と同じです。以下のようなテーブルが出てきます。
| organization.id | organization.name | student.id | student.name | student.organization_id |
|---|---|---|---|---|
| 1 | A校 | 1 | 山田 | 1 |
| 1 | A校 | 2 | 佐藤 | 1 |
| 1 | A校 | 3 | 木村 | 1 |
| 2 | B校 | 4 | 鈴木 | 2 |
そしてさらにここ、
LEFT JOIN Classroom
ON organization.id = classroom.organization_id
ここで、行が増殖します。SQLは、
| id | name | organization_id |
|---|---|---|
| 1 | A校・数学教室 | 1 |
| 2 | A校・英語教室 | 1 |
| 3 | B校・数学教室 | 2 |
のorganization_idをorganization.idと比較させて、一致するところに片っ端から結合していきます。つまり、
| organization.id | organization.name | student.id | student.name | student.organization_id | classroom.id | classroom.name | classroom.organization_id |
|---|---|---|---|---|---|---|---|
| 1 | A校 | 1 | 山田 | 1 | 1 | A校・数学教室 | 1 |
| 1 | A校 | 1 | 山田 | 1 | 2 | A校・英語教室 | 1 |
| 1 | A校 | 2 | 佐藤 | 1 | 1 | A校・数学教室 | 1 |
| 1 | A校 | 2 | 佐藤 | 1 | 2 | A校・英語教室 | 1 |
| 1 | A校 | 3 | 木村 | 1 | 1 | A校・数学教室 | 1 |
| 1 | A校 | 3 | 木村 | 1 | 2 | A校・英語教室 | 1 |
| 2 | B校 | 4 | 鈴木 | 2 | 3 | B校・数学教室 | 2 |
という感じになります。organization_idが1の生徒が3人いて、organization_idが1の教室が2つあるので、3×2=6。そこにB校の1教室が1通りあるので、
最後に、先程と同様に、organization.id,organization.nameを基準としてグループ分けと集計が実行されますので、
| organization.id | organization.name | student_count | classroom_count |
|---|---|---|---|
| 1 | A校 | 6 | 6 |
| 2 | B校 | 1 | 1 |
となってしまいました。A校の人数と教室数が多すぎますね。
以上のように、Countはそれぞれの元テーブルを見ていい感じに数えてくれているのではなく、JOIN後の結果を見てから数えているだけです。そのため、データ同士の関係性によっては、値がどんどん大きくなってしまうことがあります。
4-3.) distinctを用いた重複の防止
では、どのように防止すればよいのか、というと、
organizations = Organization.objects.annotate(
student_count=Count("students", distinct=True),
classroom_count=Count("classrooms", distinct=True),
)
という風に、Countへdistinctを追加すればOKです。このときSQLは、
SELECT organization.id,
organization.name,
Count(DISTINCT student.id) as student_count,
Count(DISTINCT classroom.id) as classroom_count
FROM Organization
LEFT JOIN Student
ON organization.id = student.organization_id
LEFT JOIN Classroom
ON organization.id = classroom.organization_id
GROUP BY organization.id,
organization.name;
という風に変わります。テーブルはそのままなのですが、その結果の集計で、DISTINCTを使ってくれるので、重複を１件としてカウントしてくれるようになります。より具体的には、
Count(DISTINCT student.id) as student_count,
は組織ごとに重複を潰していきます。今回の例では、A校は
| student.id |
|---|
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
の重複を潰して、
| student.id |
|---|
| 1 |
| 2 |
| 3 |
から3件となり、Bは
| student.id |
|---|
| 4 |
でそのまま1件。
そして、
Count(DISTINCT classroom.id) as classroom_count
でA校は、
| classroom.id |
|---|
| 1 |
| 2 |
| 1 |
| 2 |
| 1 |
| 2 |
の重複を潰して、
| classroom.id |
|---|
| 1 |
| 2 |
で2件となります。Bは
| classroom.id |
|---|
| 3 |
で1件。
結果として、
| organization.id | organization.name | student_count | classroom_count |
|---|---|---|---|
| 1 | A校 | 3 | 2 |
| 2 | B校 | 1 | 1 |
となります。A校の重複が消えて、正しく数えられています。
5.) まとめ: やはりクエリを意識するのが重要
ここまで、私が実務で引っ掛かったきたやらかしを見てきましたが、非常に月並みながら、やはりデータ構造とクエリを同時に頭の片隅で意識するのが重要だと思いました。
繰り返しになりますが、データ構造しかり、その場での利用方法しかり、AIが毎回背景から利用先まで広く意識してくれるとは限りません。実際、あまり関係ない別の作業をしているときに、まさに今回のような問題に遭遇することが未だによくあります。
かといって、たとえば毎回読んでくれる指示、CLAUDE.mdなどに、「毎回ORMを書く時は参照されている場所と、データ構造の関係を必ず調べるように」という指示を出すのは少し重く感じます。
そもそも現実の業務ありきな以上、そこの把握を完全にお任せするのは厳しいと思います。
そのため最近は、ORMがちょっと複雑目だと感じたら、shellを起動して、実際にどのようなクエリが発生しているかを確認することがあります。Djangoでは、ORMの後ろに.queryと付け加えてprintすると、それがどのようなSQLを発行するのかを見せてくれるのです。
>>> from accounts.models import Organization
>>> from django.db.models import Count
>>> organizations = Organization.objects.annotate(
... student_count = Count("students"),
... classroom_count = Count("classrooms")
... )
>>> print(organizations.query)
SELECT "accounts_organization"."id",
"accounts_organization"."name",
COUNT("accounts_student"."baseuser_ptr_id") AS "student_count", COUNT("accounts_classroom"."id") AS "classroom_count"
FROM "accounts_organization"
LEFT OUTER JOIN "accounts_student"
ON ("accounts_organization"."id" = "accounts_student"."organization_id")
LEFT OUTER JOIN "accounts_classroom"
ON ("accounts_organization"."id" = "accounts_classroom"."organization_id")
GROUP BY "accounts_organization"."id"
>>> unique_organizations = Organization.objects.annotate(
... student_count = Count("students", distinct=True),
... classroom_count = Count("classrooms", distinct=True)
... )
>>> print(unique_organizations.query)
SELECT "accounts_organization"."id",
"accounts_organization"."name",
COUNT(DISTINCT "accounts_student"."baseuser_ptr_id") AS "student_count", COUNT(DISTINCT "accounts_classroom"."id") AS "classroom_count"
FROM "accounts_organization"
LEFT OUTER JOIN "accounts_student"
ON ("accounts_organization"."id" = "accounts_student"."organization_id")
LEFT OUTER JOIN "accounts_classroom"
ON ("accounts_organization"."id" = "accounts_classroom"."organization_id")
GROUP BY "accounts_organization"."id"
多少整形していますが、これは実際にshellから出力されたものです。内部ではモデル名の前にaccountsというアプリケーション名がついていることが見て取れますね。
なお、baseuser_ptr_idという見慣れない表記や、GROUP BYの挙動など、いろいろお話したい部分もあるといえばあるのですが、あんまり本筋には関係内ので一旦スルーします。
また、テストで仕様を明文化するのも1つだと思います。今回の例で言えば、
class StudentQueryTest(TestCase):
def test_student_list_does_not_cause_n_plus_one(self):
# ここかsetupで組織や教室などを作成する
students = (
Student.objects
.select_related("organization")
.prefetch_related("organization__classrooms")
)
with self.assertNumQueries(2):
for student in students:
_ = student.organization.name
for classroom in student.organization.classrooms.all():
_ = classroom.name
でN+1を確認し、
class OrganizationCountTest(TestCase):
def test_counts_students_and_classrooms_without_join_duplication(self):
# 同様にデータの作成
organizations = (
Organization.objects
.annotate(
student_count=Count("students", distinct=True),
classroom_count=Count("classrooms", distinct=True),
)
.order_by("id")
)
a = organizations[0]
b = organizations[1]
self.assertEqual(a.student_count, 3)
self.assertEqual(a.classroom_count, 2)
self.assertEqual(b.student_count, 1)
self.assertEqual(b.classroom_count, 1)
で行が増殖していないかを数えます。
この辺は割とAIにまかせていいかなと思います。一から任せるとちょっと不安ですが、仕様を指定した上でなら、私が人力で制作するより遥かに早く処理してくれます。
今回は以上になります。何かありましたら、コメント欄にて指摘・ご質問いただけると幸いです。次回もよろしくお願いします。