「ループでクエリをN回打つのと、1回で取って手元で処理するの、どちらが速いか」— この議論、だいたい「round tripが少ない方が速い」で終わります。N+1問題の教訓としてはそれで正しいのですが、半分です。実際に手を動かすと、round tripの数だけ数えていると裏切られるケースに出会います。
例で考えます
注文明細が100万行あるとして、顧客ごとの購入合計が欲しい。やり方は3つあります。
-- (a) 手続き型: 顧客ごとにループしてN回クエリ (いわゆるN+1)
SELECT SUM(amount) FROM order_items WHERE customer_id = ?; -- を1万回
-- (b) 1回で全部取って、アプリ側で集計
SELECT customer_id, amount FROM order_items; -- 100万行を転送
-- (c) DB内で集計して、結果だけ受け取る
SELECT customer_id, SUM(amount) FROM order_items GROUP BY customer_id; -- 1万行を転送
私が保守の現場で山ほど見てきたのは(b)です。ガバッと取って、後で選別する — Railsのプロダクトを7年保守していた頃、このコードには本当に手を焼きました。round tripは1回ですから、N+1の教訓には反していません。でも(b)は100万行 — 数十MB — をDBからアプリへ運び、その全部をアプリのメモリに載せて、使うのは集計後のわずかな行だけです。(c)なら速くて、メモリも食わなくて、いいことずくめなのに、なんでこんなことしてるんだ、と何度憤慨したか分かりません。
ただ、吐露しますと — 私もかつては(b)を書く側でした。15年以上前の私は、とにかく取ってきてしまえば、アプリ側でどうにでもなるじゃん、と考えていました。だから(b)の言い分もわからなくはありません。開発者は慣れ親しんだ言語体系の中で処理した方が “わかりやすい” “扱いやすい” と感じますので、データを手元の List に持ってきてから、慣れた構文でfilterやsumを書く方が「考えなくて済」みます。SQL に仕事をさせるには、境界の向こう側の言語で考える必要があります。つまり(b)は、そういう意味で “怠慢”、または 境界を意識していない コードで、当時の私は、境界がコストだということ自体を知りませんでした。
数えるべきは「境界を越えるコスト」
つまりround tripの数は、本当のコストの代理変数のひとつにすぎません。本当に払っているのはDBとアプリの境界を越えるコストで、これは2つの項でできています。
境界コスト = 往復の回数 × レイテンシ + 運ぶ量 × 帯域あたりコスト
(a)の罪は第1項 (1万回のレイテンシ)、(b)の罪は第2項 (100万行の転送 — しかも運んだ分はそのままアプリのメモリに積まれ、GCの掃除代まで付いてきます)。N+1問題は第1項の教訓として有名になりましたが、第2項にも名前はあります — over-fetching。GraphQLがRESTを批判する文脈で広まった言葉ですけれども、N+1ほどDBの議論では流通していないので、同じくらい高くつくのに見過ごされがちです。「1回で取ってるから大丈夫」— 何を、どれだけ運んでいますか。
集計は、データのある場所でやる
(c)が良い理由は単純です。集計してから運べば、運ぶ量が減る。 それだけの話で、分散処理の世界が「処理をデータに寄せろ」と言い続けてきたのと同じことが、アプリとDBの間でも成り立っているだけです。
要するに、餅は餅屋です。集計や絞り込みは、DBが数十年かけて磨いてきた本業で、オプティマイザも実行計画もそのためにあります。
ただし「昔からそうだった」わけではありません。MySQLがsubqueryすら書けなかった時代 (4.0まで) があり、MyISAMの時代 (InnoDBがdefaultになる5.5=2010年まで) には長い集計がテーブルロックで書き込みを止めたので、重い処理をアプリへ逃がすのは運用上の自衛でもありました。加えて2000年代のweb開発、特にRails文化圏には「ロジックはアプリに置け、DBを賢くするな」という明確な流儀がありました — stored procedureを嫌い、外部キー制約すら嫌う時代です。私が「取ってきちゃえばどうにでもなる」と考えていたのは、ちょうどその残響の中でした。でもMySQLはとうの昔に一人前の餅屋になっていて、8.0では古典的な道具の最後の1つ (hash join) まで揃いました (2019)。逃がす理由は、もうありません。
結局、タイトルに戻るとこうなります。SQLの速さは、round tripの数だけでは決まりません。DBとアプリの境界を、何回、どれだけの量が越えるかです。round tripを数え終わったら、次は運ぶ量を数えてください。ガバッと取っているそのクエリ、何を、どれだけ運んでいますか。
(下書きはうちのAIが書き、文責は私にあります)