N+1問題という性能問題関連のワードは聞いた事があっても、自信を持って説明できない方もいらっしゃるのではないでしょうか。
N+1問題は、一覧を取得した後、各行に必要な関連データを個別に取りに行くことで、データ件数に応じてSQLの回数が増える問題です。
この記事では、注文一覧と顧客情報を例に、追加のSQLが発生する位置、JOINとまとめ取りによる修正、SQL回数を使った再発防止まで確認します。SELECTの基本が分かる方を対象に、ORMなどの用語は使う場面で補足します。
動画では、注文一覧からSQLが増える流れと、JOIN・まとめ取りによる直し方を図で確認できます。
1. 注文一覧で何が「N＋1」になるのか
画面には、注文番号・金額・顧客名を表示したいとします。注文と顧客は、次のように別のテーブルに保存されています。
orders（注文）
| id | amount | customer_id |
|---|---|---|
| 1 | 1200 | 101 |
| 2 | 2400 | 102 |
| 3 | 3600 | 103 |
customers（顧客）
| id | name |
|---|---|
| 101 | 顧客A |
| 102 | 顧客B |
| 103 | 顧客C |
まず、注文一覧を取得します。
SELECT id, amount, customer_id
FROM orders
ORDER BY id
LIMIT 100;
続いて、注文ごとに顧客名を検索すると、次のSQLが追加されます。
SELECT name FROM customers WHERE id = 101;
SELECT name FROM customers WHERE id = 102;
SELECT name FROM customers WHERE id = 103;
このデータなら、注文一覧の取得が1回、顧客名の取得が3回で、合計4回です。同じ構造で100件の注文を処理すれば、合計101回になります。追加取得の件数をNと表すため、N+1問題と呼ばれます。
ここでは、各注文が別の顧客を参照し、その顧客情報を初めて取得する例にしています。ORMのキャッシュなどで取得済みの顧客情報が再利用されると、実際の回数は変わります。見るべき点は、一覧の件数に応じて追加取得が増える構造です。
2. ORMでは、どの処理がSQLを増やすのか
ORM（Object-Relational Mapping）は、プログラムのオブジェクトとデータベースのテーブルを対応付ける仕組みです。注文オブジェクトから関連する顧客を参照できるため、次のようなコードで一覧を作れます。
以下は、特定のライブラリに依存しない疑似コードです。
orders = loadOrders(limit=100) // 注文一覧の取得
for order in orders:
customerName = order.customer.name
display(order.id, order.amount, customerName)
遅延読み込みは、関連データを実際に使う時点で取得する方式です。この方式で顧客情報が未取得なら、order.customer.name に触れたときに顧客を検索するSQLが発行されます。ループの各回で検索が必要になれば、N回の追加取得が発生します。
手書きSQLでも、ループ内で顧客を1件ずつ検索すれば同じ構造になります。コードを読むときは、ループの中でデータベースへ追加の問い合わせをしている箇所を探します。
3. SQLログから原因を確かめる
調査では、一覧画面を1回開いたときの処理に範囲を絞ります。
- その処理が発行したSQLを記録する。
- 同じ形のSELECTが繰り返されている箇所を探す。
- 変わっている値が、一覧の各行のIDなどに対応しているか確認する。
- 一覧件数を10件、100件に変えて、SQL回数の増え方を比較する。
今回の例をPython標準のSQLiteで実行し、取得結果とSELECT回数を比較した結果は次のとおりです。顧客は注文ごとに異なり、総件数を数えるSQLや認証処理は含めていません。
| 注文件数 | 顧客を個別取得 | JOINで取得 | 顧客をまとめ取り |
|---|---|---|---|
| 10件 | 11回 | 1回 | 2回 |
| 100件 | 101回 | 1回 | 2回 |
検証環境はSQLite 3.53.1です。3方式で、注文番号・金額・顧客名の結果が一致することも確認しました。これはSQL回数と結果の検証で、実際のWebアプリやORMを使った性能測定はしていません。
小さな開発用データでは応答が速くても、本番で件数が増えると遅延が表面化することがあります。
たとえば1リクエストあたり101回のSQLを発行する処理が100回実行されると、合計10,100回になります。これは回数の単純計算で、接続を同時に10,100本使うという意味ではありません。同時実行が重なると、環境によってはDB負荷や接続待ちが増え、タイムアウトにつながります。
本番の調査では、APM（Application Performance Monitoring：アプリケーションの性能監視）などで、リクエストとDB処理を対応付ける方法もあります。SQLログの値には個人情報が含まれることがあるため、値の伏せ方（マスキング）、記録対象、保存期間を決めておくことも重要です。
4. JOINとまとめ取りで修正する
JOINで注文と顧客を一緒に取得する
JOINは、関連するテーブルの行を結び付けて取得するSQLの機能です。注文と顧客を結び、画面で使う3項目を一度に取得します。
SELECT o.id, o.amount, c.name
FROM orders o
JOIN customers c
ON c.id = o.customer_id
ORDER BY o.id
LIMIT 100;
このSQLでは、注文一覧と顧客名を1回で取得できます。取得後は、返された顧客名をそのまま表示します。
例では、すべての注文に対応する顧客が存在する前提です。顧客が未設定の注文も表示する仕様なら、LEFT JOINを使い、顧客名がNULLになる場合の表示も決めます。
なお、このSQLのLIMIT 100は、1注文に対して顧客が最大1件対応する前提で使っています。注文明細のような複数件の関連をJOINする場合は、後述する行数の増加とページ分割に注意します。
顧客IDを集めて、まとめて取得する
まとめ取りでは、注文一覧を取得した後、必要な顧客だけを一括で取得します。
最初のSQLは注文一覧の取得です。
SELECT id, amount, customer_id
FROM orders
ORDER BY id
LIMIT 100;
取得した注文から顧客IDを集め、重複を除きます。上の3件の例なら、対象は101、102、103です。IN句は、指定した複数の値のいずれかに一致する行を選びます。
SELECT id, name
FROM customers
WHERE id IN (101, 102, 103);
取得した顧客を「顧客IDから顧客名を探せる辞書」に入れ、注文と組み合わせます。ここで再びDBへ問い合わせないようにします。
orders = loadOrders(limit=100)
customerIds = unique(orders.customer_id)
customers = loadCustomers(customerIds)
customerById = indexBy(customers, "id")
for order in orders:
customer = customerById[order.customer_id]
display(order.id, order.amount, customer.name)
これも疑似コードです。実装では、ID一覧が空なら顧客取得を省略し、SQLへ渡す値には利用するDBドライバーのパラメーター機能を使います。IN句の枠だけを必要数生成し、値は文字列へ直接連結せず別途渡します。
この例は、注文一覧が1回、顧客取得が1回の合計2回です。対象IDが多い場合は、DBやORMの制限に合わせて複数回に分けて取得します。たとえばSQLAlchemy 2.0のSelect IN読み込みも、IDを一定件数ごとに分けて取得します。
5. 取得回数と、取得するデータ量を確認する
取得方法を変えたら、SQL回数に加えて、返される行数や列数も確認します。
複数件の関連をJOINすると行が増える
1件の注文に明細が10件あれば、注文と明細のJOIN結果は10行になります。さらに、その注文に紐づく別の関連一覧として履歴が10件あり、両方を注文IDで同時にJOINすると、明細と履歴の組み合わせで100行になる場合があります。
Microsoftの公式資料にも、同じ親に紐づく二つの関連一覧をJOINすると、行の組み合わせが増える例が示されています。単一クエリと分割クエリの説明
この形のJOIN結果にそのまま件数制限をかけると、「注文100件」と「JOIN結果100行」が一致しません。先に対象ページの注文を確定して関連データをまとめ取りするなど、ページの単位を保てる取得方法にする必要があります。
画面で使う列に絞る
注文番号・金額・顧客名だけを表示するなら、備考全文や他の顧客情報まで取得する必要はありません。SELECTに必要な列を明示すると、転送するデータ量を減らせます。
関連データをまとめて取得することと、取得列を絞ることは組み合わせて使います。各行で顧客名を個別検索する構造が残っていれば、取得列を名前だけに絞っても、その追加取得は続きます。
複数回のSQLでは、データの見え方も決める
注文を取得してから顧客を取得するまでの間に、別の処理がデータを更新することがあります。整合性の要件が厳しい処理では、トランザクションの範囲と分離レベルを確認します。
トランザクションは複数の処理を一つの単位として扱う仕組み、分離レベルは同時に動く処理からデータがどう見えるかを決める設定です。複数のSQLから同じ時点のデータを見るには、それを保証するDBの機能や設定が必要です。
6. SQL回数のテストとレビューで再発を防ぐ
一覧に関連データを一つ追加しただけで、個別取得が再び発生することがあります。取得処理には、その画面で使う関連データを明示し、SQL回数を検証するテストを置きます。
以下はテストの考え方を示す疑似コードです。
for size in [10, 100]:
prepareOrdersWithDifferentCustomers(size)
resetSessionAndCaches()
result, count = measureSelectCount {
buildOrderListResponse(limit=size)
}
assert result.length == size
assert everyRowHasExpectedCustomerName(result)
assert count <= 2
この上限2回は、記事の注文・顧客だけを取得する例に合わせた値です。実際の画面で総件数のCOUNTや別の関連取得が必要なら、その分を含めて上限を決めます。バッチの分割がある場合は、その境界も含めて期待回数を設定します。
計測範囲には、画面表示やレスポンス生成のために関連データへ触れる処理まで含めます。一覧を取得する関数だけを測ると、その後の変換・表示処理で発生する追加SQLを見落とします。テストデータの登録処理は計測範囲から外し、事前に読み込んだ関連データがキャッシュに残って結果を隠さないようにします。
レビューと運用では、次の点を確認します。
| 確認箇所 | 確認する内容 |
|---|---|
| ループ | 関連データや関連件数の取得で、各回にSQLが出ていないか |
| 取得処理 | 画面で使う関連データをまとめて取得しているか |
| ページ分割 | 取得件数の上限があり、JOIN後もページの単位が保たれるか |
| テスト | 複数のデータ件数でSQL回数と結果の正しさを確認しているか |
| 負荷試験 | 本番相当のデータ量と同時アクセスで問題が出ないか |
| 監視 | リクエスト単位のSQL回数、DB負荷、接続待ち、応答時間を追えるか |
まとめ
N+1問題では、一覧取得の1回に加え、各行の関連データを取得するSQLが繰り返されます。注文一覧のような処理では、SQLログと件数を対応付けると発生箇所を探しやすくなります。
修正にはJOINやまとめ取りを使い、取得結果の行数・必要な列・ページ分割・データの整合性を確認します。修正後はSQL回数と表示内容をテストし、表示項目を追加した際の再発を検出できるようにします。