この記事で使用しているテーブル名、カラム名、条件値、件数は、情報の漏洩を防ぐため一部を置き換えています。以下では、usersが約160万件、storesが約100件あるものとして説明します。
160万件を読んでから並び替えていた
店舗名で並び替える一覧で、表示が遅くなる問題がありました。
もともとは、usersを起点にstoresを結合し、店舗名順に並び替えるSQLでした。
FROM users
LEFT JOIN stores ON stores.id = users.store_id
ORDER BY stores.name
LIMIT 51;
この形では、約160万件のusersを先に読み取り、storesと結合したあとで店舗名順にソートしていました。EXPLAIN ANALYZEでは、一覧の表示に約4.1秒かかっていました。
約100件のstoresから読めば速くなるはず
そこで、件数の少ないstoresを起点に処理できるよう、次の形へ変更しました。
FROM stores
INNER JOIN users ON users.store_id = stores.id
ORDER BY stores.name
LIMIT 51;
狙いは次のとおりです。
storesを店舗名順に読み取る
-> 各店舗に紐づくusersを取得する
-> 必要な51件を取得できた時点で処理を終える
ここで、LEFT JOINとINNER JOINの違いを整理します。
LEFT JOINとは？
左側のテーブルのレコードをすべて取得し、結合条件に一致する右側のテーブルのレコードがあれば結合する方法です。右側に一致するレコードがない場合でも、左側のレコードは結果に残り、右側のカラムは
NULLになります。
INNER JOINとは？
結合条件に一致する、両方のテーブルのレコードだけを取得する方法です。どちらか一方にしか存在しないレコードは、結果に含まれません。
今回は一覧の仕様として、店舗に紐付くユーザだけを表示すればよかったため、INNER JOINへ変更できました。
ローカル環境では、この変更後に期待どおりstoresから読み始める実行計画になり、約9msまで改善しました。
INNER JOINに変えたのに、開発環境では遅いまま
しかし、開発環境で確認してみると、なぜか直っておらず遅いままでした。発行されたSQLには確かにINNER JOINが含まれており、変更自体は反映されています。それでもEXPLAIN ANALYZEを見ると、DBはusersから先に読み始め、結合と並び替えを行っていました。
原因は、INNER JOINでは結合順が固定されないためです。MySQLは良くも悪くもいい感じに、より効率がよいと判断した順序でテーブルを読み取ります。SQLをstores INNER JOIN usersと書いても、常にstoresから処理されるわけではありませんでした。
開発環境では、次のような順序で処理されていました。
usersから約160万件の対象レコードを読み取る
-> storesを結合する
-> stores.nameで全体をソートする
-> 先頭の51件を返す
これだと遅いままなので、以下の形へ変更しました。
結合順を固定して、storesから読ませる
約100件のstoresから読むようにSTRAIGHT_JOINを使い、結合順を固定しました。
FROM stores
STRAIGHT_JOIN users ON users.store_id = stores.id
ORDER BY stores.name
LIMIT 51;
これにより、storesを店舗名順に読んでからusersを取得する実行計画になりました。
STRAIGHT_JOINとは？
STRAIGHT_JOINは、MySQLで結合するテーブルの順番を固定したいときに使える書き方です。
通常のINNER JOINでは、DBが処理しやすい順番を自動で選びます。一方、STRAIGHT_JOINを使うと、storesを先に読んだあとにusersを結合する、というように読み込み順を指定できます。
今回のように、どちらのテーブルから読み始めるかで処理時間が大きく変わる場合に、意図した順番で実行されているかを確認するために使えます。
まとめ
今回の件で、INNER JOINに変えただけでは、意図した順序で処理されるとは限らないと分かりました。SQLの見た目だけで安心せず、EXPLAIN ANALYZEで実際の読み取り順も確認する必要がありそうです。