アプリから使う

実物: 接続

第 0 章の SHOW PROCESSLIST で見た 1 行が接続 1 本で、サーバーは接続ごとにスレッドを 1 本持つ。CONNECTION_ID() がその番号だ。 「実行するたびに変わることがある」のは、このラボが数本の接続を使い回しているからで、これをコネクションプールと呼ぶ。 接続を作るには TCP の握手と認証で数ミリ秒〜数十ミリ秒かかるので、アプリはリクエストごとに接続を作らず、作っておいた接続を借りて返す。

// 擬似コード
pool = createPool(接続先, 最大 10 本)
rows = pool.query("SELECT * FROM customers WHERE id = ?", [42])

「最大 10 本」はこのアプリの 1 プロセスが同時に使う上限。アプリのサーバーが 20 台なら DB には最大 200 本の接続が来る。 DB 側にも上限(max_connections、既定 151)があり、超えると接続を拒否される。この数は第二部 10 章で実測する。

驚き: 入力を混ぜると SQL が変わる

顧客名で検索する機能を、入力を文字列連結で SQL に埋め込んで作ったとする。

sql = "SELECT * FROM customers WHERE name = '" + input + "'"

入力が ' OR '1'='1 だったとき、組み立てられる SQL を実行する。行数を予測してから。

10 行、全員が返るはずだ。入力の ' が文字列を閉じ、その後ろが SQL の一部として解釈された。'1'='1' は常に真なので全行が残る。 ログイン処理がこの形なら、パスワードを知らなくても全員でログインできる。これを SQL インジェクションと呼ぶ。

入力に '; DELETE FROM orders; -- と書かれれば、複数文を許す設定では本当に注文が消える(第 5 章の WHERE なし DELETE が、外から実行できる)。

// 正しい: プレースホルダ
pool.query("SELECT * FROM customers WHERE name = ?", [input])

? の場所に値を別便で送る書き方(プレースホルダ、プリペアドステートメント)を使うと、入力は「値」として扱われ、SQL の構造を変えられない。 入力に ' があっても、それは名前の一部として比較される。ORM を使っていれば通常は自動でこうなるが、生の SQL を書く機能(raw)で文字列連結すれば同じ穴が開く。 2 つの疑似コードで、入力がどこで文と混ざるかを並べる。

ORM が送っている SQL を見る

多くのアプリは ORM(オブジェクトとテーブルを対応付けるライブラリ)経由で DB を使い、SQL を直接書かない。 しかし ORM は SQL を「書かなくていい」道具であって、「送られない」わけではない。何を送っているかは、見なければわからない。

// ORM の例(擬似コード)
orders = Order.where(status: "paid").includes(:customer).order(ordered_at: :desc).limit(10)

これは、おおよそ次の SQL(と、customer を取る 2 本目)になる。

どの ORM にも「発行した SQL をログに出す」設定がある。開発中は常に ON にして眺める。ここで「1 回の画面表示で SQL が何本出ているか」に気づけるかどうかが、次の話につながる。

N+1: DB のログに出ないのに遅い

チケットの「顧客一覧に注文数」を、素朴に書くとこうなる。

customers = query("SELECT * FROM customers")            // 1 回
for c in customers:
    c.orderCount = query("SELECT COUNT(*) FROM orders WHERE customer_id = ?", [c.id])   // 10 回

1 回は一瞬だ。だから DB の遅いクエリのログには何も出ない。しかし 10 人なら 11 回、1 万人なら 1 万 1 回、DB との往復が発生する。 往復 1 回が 1ms でも 10 秒。遅いのはクエリではなく回数で、これを N+1 問題と呼ぶ。

1 回ずつの往復と、まとめた 1 回を、アプリと DB の間の矢印で並べる。

ORM には関連を一括で読む仕組み(includes、include、DataLoader など)があり、使うと WHERE customer_id IN (…) の 1 本にまとまる。 気づく方法は 1 つで、アプリ側の SQL ログに「同じ形のクエリが大量に出ている」ことを見る。

第二部へ

ここまでで、SQL を書き、表をつなぎ、変更し、トランザクションで守り、索引という実物を見て、アプリから正しく呼ぶところまで来た。 200 行では、書き方の良し悪しが時間に現れなかった。第二部では、同じショップの 1 年後、注文明細が 3,200 万行になった世界に入る。

• 第 2 章の WHERE と ORDER BY の列 → 第二部 1・4・6 章で索引の設計対象になる。
• 第 3 章の COUNT(*) → 第二部 0 章で「数秒かかる」ことを見る。
• 第 4 章の JOIN → 第二部 7 章で「どちらから読むか」で 60 秒差が付く。
• 第 7 章のトランザクション → 第二部 9 章で、書き込み同士がぶつかるとどう待つかを見る。
• この章の N+1 と接続数 → 第二部 10 章で、同時接続を増やすと何が起きるかを測る。

アプリと DB の間には接続があり、入力は値として送り、ORM の SQL は本数を数える。遅いのはクエリではなく回数であることが多い。

確認