はじめに
今回はN+1問題についてです。これまでORMについてはAIが書いているのを全て受け入れてきたのですが、以前ある程度チェックしたはずのコードで「ここはN+1問題が発生する可能性がある」と指摘されることが度々ありました。
そのため、「これは自分で握っておかないとまずいな」と思い、基礎の習得が疎かになっていたSQLと併せて学習しました。ここにはその経過を記録します。
想定読者は私と同じくらいの理解度、具体的には「ORMは最低限書けるし読めるが、N+1問題を知らない」あるいは「N+1問題は知っているが、原理の話までは出来ない」という方になります。
この記事のゴールは、自分がど忘れしてしまっても、きちんと思い返せるようになっている、という点で設定します。
1.) N+1問題とは何か
ざっくりいうと、あるオブジェクトを取得する際に、余計なクエリが発生してしまう問題のことです。たとえば、以下のようなコードで発生する可能性があります。
想定するのは学習塾のシステムです。
-
ある生徒は必ずいずれかの組織に所属している
-
組織には複数の生徒が所属している
という状況だとします。つまりこんな感じのモデルがあるやつですね。
class Student(models.Model):
id = models.PositiveIntegerField()
name = models.CharField()
organization = models.ForeignKey("Organization", related_name="students")
class Organization(models.Model):
id = models.PositiveIntegerField()
name = models.CharField()
※本来であればidは自分で設定せずに、Django側で設定してくれるだとか、外部キーにはon_deleteが要るだとか、null, blankはどうなんだとか、いろいろありますが、いったん簡略化のため省略します。
ちなみに、related_nameは逆参照時、つまり「Student→Organization」ではなく、「Organization→Student」で辿りたいときの名前です。organization.studentsのように利用します。
ここで、システム管理者が、全ての生徒と、彼らの所属している組織を確認したいと思います。
すると、以下のようなコードが考えられます。
all_students = Student.objects.all()
for student in all_students:
print(student.organization.name, student.name)
Python的な思考回路だと全く正しい書き方ですし、実際に動作します。
ただ、ここで発生してしまうのがN+1問題です。
なぜ発生する?
ORMだけでなくて、DBの構造を想像すると少し理解しやすくなります。
たとえば、こんな感じです。
student
| id | name | organization_id |
|---|---|---|
| 1 | 山田 | 1 |
| 2 | 佐藤 | 1 |
organization
| id | name |
|---|---|
| 1 | A校 |
ポイントは、studentはorganizationそのものではなく、organization_idを保持していることです。
これは合理的な仕組みです。
というのも、今回のorganization程度であれば問題ないのですが、実際はもっと大量の属性を持っている場合があります。たとえば、組織のオーナーさん、作成日、加入日、所在地、電話番号etcetc...
これらは基本的にstudentにとっては不要なもので、全部持っているのは無駄です。仮に全部持っていたら、たとえばオーナーさんの名前が変わったら、全件データ検索して更新しにいかないといけなくなります。必要になったタイミングで取りにいくのはデータの肥大化や不整合を防ぐ上でも合理的だということですね。
遅延取得
必要になったら取りに行く。これを遅延取得と言います。具体的には、DjangoはORMが書かれた瞬間ではなく、実際に動作に必要になった瞬間にデータを取りに行きます。より具体的には、その属性にアクセスした際に、まだ中にデータが入っていない場合は、クエリを発行してデータを取りに行きます。
具体的に先程の例では、
all_students = Student.objects.all()
ここではなく、
for student in all_students:
ここです。ここで初めて、all_studentsが使われることが確定したのでクエリが発行されます。
具体的には以下のようなやつですね。
SELECT *
FROM student
ここでstudentテーブルのデータを全件取得します。ここで1回クエリが発生しています。結果として、
| id | name | organization_id |
|---|---|---|
| 1 | 山田 | 1 |
| 2 | 佐藤 | 1 |
これがall_studentsに入っています。
そして、次またクエリが発行されます。それがここ。
print(student.organization.name, student.name)
ここの、student.organizationでクエリが発行されます。
理由はシンプル。先ほど取得した生徒テーブルにorganization.nameが無いからですね。
もちろん取得はできます。organization.nameは持っていませんが、organization_idは持っています。なので、
SELECT organization.name
FROM organization
WHERE organization.id = 1;
SELECT organization.name
FROM organization
WHERE organization.id = 1;
という風に、山田さんの分と佐藤さんの分で、クエリを2回発行します。ぱっとみると、「えっ、同じなら共有すればよいのでは？」と感じますが、山田さんのインスタンスでキャッシュされていたとしても、佐藤さんのインスタンスにそれが自動で共有されているわけではないので、別途発行せざるを得ないのです。
こうすることで、
「生徒idが1の山田さんは、organization_id = 1だからA校」
「生徒idが2の佐藤さんは、organization_id = 1だからA校」
という風に、組織名が取得されます。そうすることで、
all_students = Student.objects.all()
for student in all_students:
print(student.organization.name, student.name)
は
A校 山田
A校 佐藤
と表示することができるのです。
この2件のデータを表示するのに、最初の全取得で1回、そこからのorganization_idを使った組織の取得で2回。合計3回の取得が発生しています。これがN+1問題の正体になります。
最初の1回でN件のStudentを取得し、そのN件それぞれについて未取得の関連オブジェクトへアクセスすると、追加でN回クエリが発行され、合計N+1回になります。
何が問題か
効率が悪いのが問題です。人間でたとえると、旅行の計画を立てる時に、飛行機の出発とゲートは調べているが、降りた後に乗るバスはまた別途調べ直す、みたいな感じです。あるいはお店を探す時に、いったん名前と営業日だけ調べておいて、電話番号を後でみるみたいなことだとも言えるかもしれません。
これは扱うデータのスケールが増えると、それに比例して問題になっていきます。10件だったら11回ですが、1万件だったら10001回、1000万件だったら……と、どんどんシステムとDBの処理能力を無駄に消費していってしまいます。
2.) N+1問題をどのように防止するか
これを見ると、「初めからとってきてよ…」と誰もが思います。でも、すでに述べた通り、初めから使うかわからないものを根こそぎ取ってくるのは無駄です。だからDjangoからすると、逆に「初めから教えてよ……」にするのが合理的なのです。
そして、その「初めから教えておく」の手段のうち、私が学んだのは以下になります。
2-1.) 解決策①：select_related（JOINで1回にまとめる）
では、どのように解決するのか。理屈としてはシンプルに、初めの段階で取得しておくになります。
Pythonのコードで書くと、
all_students = Student.objects.all().select_related("organization")
で、SQLで書くと、
SELECT student.id, student.name, organization.id, organization.name
FROM student
JOIN organization
ON student.organization_id = organization.id;
こんな感じになります。
ここで、先程の例を再掲します。
student
| id | name | organization_id |
|---|---|---|
| 1 | 山田 | 1 |
| 2 | 佐藤 | 1 |
organization
| id | name |
|---|---|
| 1 | A校 |
すると、student_organization_id = organization_id,
つまりstudentテーブルの組織IDと、organizationテーブルのIDが等しいものが、こんな風に結合されます。
| id | name | organization_id | organization.name |
|---|---|---|---|
| 1 | 山田 | 1 | A校 |
| 2 | 佐藤 | 1 | A校 |
このテーブルがあれば、
print(student.organization.name, student.name)
ここで、わざわざorganization.nameを取るためにクエリを走らせなくても良くなります。すでに用意されているからです。これがselect_relatedの働きです。事前指定しておけば、初めから効率よく取ってきてくれるわけですね。
2-2.) 解決策②：prefetch_related（1対多・多対多の場合）
次に紹介するのは、select_relatedだけでは対処が難しい場面です。それがずばり、参照元から参照先へ1:多、あるいは多:多の関係が成り立っている状況ですね。
ここでは、以下のようなデータが格納されている場合を想定してみます。
class Student(models.Model):
id = models.PositiveIntegerField()
name = models.CharField()
class LearningRecord(models.Model):
id = models.PositiveIntegerField()
name = models.CharField()
student = models.ForeignKey("Student", related_name="learning_records")
生徒1人につき、0件以上の学習履歴があるというイメージです。
データはこんな感じ。
student
| id | name |
|---|---|
| 1 | 山田 |
| 2 | 佐藤 |
learning_record
| id | name | student_id |
|---|---|---|
| 1 | 数学 | 1 |
| 2 | 英語 | 1 |
| 3 | 英語 | 2 |
| 4 | 物理 | 2 |
生徒それぞれが2つの学習履歴を持っている形です。
2-2-1.) そのまま取得する場合
ここで管理者として、生徒ごとに学習記録を出力したいと思います。以下のような形ですね。
all_students = Student.objects.all()
for student in all_students:
print(student.name)
learning_records = student.learning_records.all()
for record in learning_records:
print(record.name)
ここで、どこでクエリが発行されるかを確認してみましょう。まずはここ、
for student in all_students:
ここで全ての生徒を取得するためのクエリ
SELECT *
FROM student;
が発行されます。結果として、
| id | name |
|---|---|
| 1 | 山田 |
| 2 | 佐藤 |
が取得されます。次に、
for record in learning_records:
ここで、生徒に紐づく学習記録を取得するためのクエリ
SELECT *
FROM learning_record
WHERE student_id = 1;
SELECT *
FROM learning_record
WHERE student_id = 2;
と生徒ごとに発行されます。結果として、たとえば山田(student_id=1)さんのループのときには、
| id | name | student_id |
|---|---|---|
| 1 | 数学 | 1 |
| 2 | 英語 | 1 |
がlearning_recordsに入っていて、それが一つ一つ呼び出されます。
佐藤さん(student_id2)についても同様で、
| id | name | student_id |
|---|---|---|
| 3 | 英語 | 2 |
| 4 | 物理 | 2 |
がlearning_recordsに入っています。
最初の生徒の全情報で1回、2人の生徒それぞれで1回ずつで合計3回クエリが発行されています。
これもまたN+1問題です。
2-2-2.) select_relatedを使った場合
ここで、先程使ったselect_relatedを使ってみましょう。こんな感じになります。
all_students = Student.objects.all().select_related("learning_records")
遅延評価なので、ここだけではエラーを吐きませんが、実際に実行するタイミングになると、
django.core.exceptions.FieldError: Invalid field name(s) given in select_related: 'learning_records'.
という風にFieldErrorが発生します。select_relatedはあくまで、参照元から参照先が1件に確定する属性を先取りするために作られています。そのため、複数件の候補が存在しうるlearning_recordsでエラーを吐きます。
2-2-3.) prefetch_relatedを使った場合
このような時に登場するのがprefetch_relatedです。参照先が複数件である場合はこちらを使います。
all_students = Student.objects.all().prefetch_related("learning_records")
ここで内部で行われている動作が少し独特です。具体的にはSQLで取得したものをPython側で結合しています。今回の例であれば、
SELECT *
FROM student;
で、
| id | name |
|---|---|
| 1 | 山田 |
| 2 | 佐藤 |
が取得されます。さらに、
SELECT *
FROM learning_record
WHERE student_id IN (1, 2);
で、
| id | name | student_id |
|---|---|---|
| 1 | 数学 | 1 |
| 2 | 英語 | 1 |
| 3 | 英語 | 2 |
| 4 | 物理 | 2 |
を取得します。後はPythonの側で、studentテーブルのidと、learning_recordテーブルのstudent_idを比べながら紐づけしていきます。正確には異なりますが、イメージ的には以下のようなオブジェクトです。
all_students = [
{
"id": 1,
"name": "山田",
"learning_records": [
{
"id": 1,
"name": "数学",
"student_id": 1,
},
{
"id": 2,
"name": "英語",
"student_id": 1,
},
]
},
{
"id": 2,
"name": "佐藤",
"learning_records": [
{
"id": 3,
"name": "英語",
"student_id": 2,
},
{
"id": 4,
"name": "物理",
"student_id": 2,
},
]
},
]
このような感じのオブジェクトが作られるため、
learning_records = student.learning_records.all()
for record in learning_records:
print(record.name)
この場面でも、あらためてクエリが発行されることはなくなります。student.learning_records.all()がprefetchによって作られた取得済みキャッシュを利用するためですね。
2-2-4.) なぜJOINだけでは済まないのか
ここで自然な疑問が湧いてきます。それは「なぜPython側でわざわざ別途結合させているのか?」という疑問です。先程のselect_relatedで扱ったように、予めJOINして、それを取得すればよいのでは?というはもっともな疑問だと思います。
これの答えは、「1: 多のテーブルをJOINすると、結果セットの行数がどんどん増えていってしまうから」というものになります。
student
| id | name |
|---|---|
| 1 | 山田 |
| 2 | 佐藤 |
learning_record
| id | name | student_id |
|---|---|---|
| 1 | 数学 | 1 |
| 2 | 英語 | 1 |
| 3 | 英語 | 2 |
| 4 | 物理 | 2 |
これをstudent_idが一致するもの、つまり
SELECT student.id, student.name, learning_record.id, learning_record.name
FROM student
JOIN learning_record
ON student.id = learning_record.student_id;
として結合すると、
| student.id | student.name | learning_record.id | learning_record.name |
|---|---|---|---|
| 1 | 山田 | 1 | 数学 |
| 1 | 山田 | 2 | 英語 |
| 2 | 佐藤 | 3 | 英語 |
| 2 | 佐藤 | 4 | 物理 |
となります。このくらいであれば別に面倒には見えません。が、ここでさらに別のテーブルを結合させてみましょう。授業に来る曜日のデータが以下のようにあったとします。
day_of_week
| id | name | student_id |
|---|---|---|
| 1 | 月 | 1 |
| 2 | 金 | 1 |
| 3 | 水 | 2 |
| 4 | 火 | 2 |
SELECT student.id, student.name, learning_record.id, learning_record.name, learninig_record.name, day_of_week.id, day_of_weeek.name
FROM student
JOIN learning_record
ON student.id = learning_record.student_id
JOIN day_of_week
ON student.id = day_of_week.student_id;
と結合したテーブルにさらに結合します。出来上がるのは、
| student.id | student.name | learning_record.id | learning_record.name | day_of_week.id | day_of_week.name |
|---|---|---|---|---|---|
| 1 | 山田 | 1 | 数学 | 1 | 月 |
| 1 | 山田 | 1 | 数学 | 2 | 金 |
| 1 | 山田 | 2 | 英語 | 1 | 月 |
| 1 | 山田 | 2 | 英語 | 2 | 金 |
| 2 | 佐藤 | 3 | 英語 | 3 | 水 |
| 2 | 佐藤 | 3 | 英語 | 4 | 火 |
| 2 | 佐藤 | 4 | 物理 | 3 | 水 |
| 2 | 佐藤 | 4 | 物理 | 4 | 火 |
です。最初は一行だった山田さん、佐藤さんが、どんどん増殖しているのがわかるでしょうか。 たとえば山田さんについては、学習記録2件と曜日2件のすべての組み合わせが作られるため、2 × 2で4行になります。
私は初め勘違いしていたのですが、SQLによるJOINというのは、「いい感じの紐づけ」ではありません。
JOINは、条件に一致する行の組み合わせを列挙する処理です。
studentとlearning_recordの結合結果
| student.id | student.name | learning_record.id | learning_record.name |
|---|---|---|---|
| 1 | 山田 | 1 | 数学 |
| 1 | 山田 | 2 | 英語 |
今回のように、一人のStudentから独立した二つの1対多を同時にJOINすると、Studentごとに学習記録と曜日の直積的な組み合わせが生まれます。
結果セットが膨張してしまうのはJOINの性質上、より正確にはJOINだけを使う場合には避けられません。だから、Djangoでは、Pythonを用いてデータを紐づけているのです。
3.) select_relatedとprefetch_relatedの使い分け基準
ここまでの話をまとめておきましょう。
| 関係 | 使うメソッド | JOINするか | SQLの発行回数 |
|---|---|---|---|
| 参照先が1件に限定できる | select_related | する | 1回 |
| 参照先が1件に限定できない | prefetch_related | しない | 2回以上 |
実は、1件に限定できる場合でも、prefetch_relatedは使えます。ただ、一般的には上記のような使い分けが多いようです。
まとめ
正直なところ、ORMを書くという一点だけで見れば、普通にAIにお願いしても良いと思います。人間が一から書く必要はないのではないでしょうか。ただ、100％信用できるかというと、そうでもないとは感じます。
- 利用箇所やモデル構造がきちんとコンテキストに含まれているとは限らない
- 参照元のネット上の情報には、チュートリアルや解説的なものも多く、そこでは話を複雑化させるselect_related, prefetch_relatedは省かれる場合がある
- そもそもAI自体がどうしても確率的な挙動が避けられない
以上の理由から、やはりAIにだけ任せていると、100%N+1問題の発生を防ぐのは難しいのではないかと思います。
コンテキストなどで指示はしつつ、テストでクエリ発行回数を規定するものを書いたり、for文の中では人間側がとりわけ注意して見ておく、あたりが現実的な解決策かなと思います。
そしてその第一歩が、今回私が学んだORMとSQLの関係になるのでしょう。
次回はもう少し詳しい内容、具体的にはCOUNTがJOINで膨れ上がる問題だとか、一見select_relatedで十分そうだが、よくよく見るとprefetch_relatedしないとまずいものなどを見ていきたいと思います。