MySQL | SQLite経験者向け、30日で習得するMySQL：実務SQL力 － Day8 JOINの最適化

SQL MySQL
スポンサーリンク

Day8 後半のゴール

「EXPLAIN を読んで“実行順序”を言葉で説明できるようになる」

前半で、
SQLは書いた順に実行されるわけではない
ON と WHERE には役割の違いがある
MySQL はJOIN順序を入れ替えられる
というところまで来ました。

後半のゴールはここです。

EXPLAIN を使って「どのテーブルから先に読んでいるか」を説明できる
インデックスの有無で実行計画がどう変わるかをイメージできる
INNER JOIN と LEFT JOIN で、ON と WHERE の違いが結果にどう出るかを理解する

ここからは、頭の中で EXPLAIN を“読む練習”をしていきます。

例題テーブルをもう一度整理する

「users × orders のシンプルな2テーブルJOINで考える」

まずは、よく使っているこの2テーブルを前提にします。

CREATE TABLE users (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(50)  NOT NULL,
  email VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  user_id  INT NOT NULL,
  total    INT NOT NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
) ENGINE=InnoDB;

CREATE INDEX idx_orders_user_id
  ON orders (user_id);
SQL

ポイントはこうです。

users.id は PRIMARY KEY（インデックスあり）
orders.user_id にもインデックスあり

この状態で、JOINクエリの実行順序を考えていきます。

パターン1：ユーザーを1人決めて、その人の注文を取る

「WHERE users.id = ? のとき、どこから読むのが自然か」

クエリはこれです。

SELECT
  users.id,
  users.name,
  orders.id   AS order_id,
  orders.total
FROM users
JOIN orders
  ON users.id = orders.user_id
WHERE users.id = 1;
SQL

人間の感覚で考えると、こう動くのが自然です。

users から id=1 の行を1件だけ取る（PRIMARY KEYインデックス）
その1件の id を使って、orders.user_id=1 を探す（idx_orders_user_id）

MySQL も、だいたいこのような実行計画を選びます。

EXPLAIN をイメージすると、
users が「最初に読むテーブル」、orders が「次にJOINされるテーブル」として並び、
両方ともインデックスを使う形になります。

ここでのポイントは、

WHERE users.id = 1 という条件が、
「usersを先に絞るべきだ」という強いヒントになっている

ということです。

JOINの最適化を考えるときは、
「どの条件が一番強く絞り込めるか」を見るのがコツです。

パターン2：高額注文からユーザーをたどる

「WHERE orders.total >= ? のとき、順番が逆転しうる」

次のクエリを考えます。

SELECT
  users.id,
  users.name,
  orders.id   AS order_id,
  orders.total
FROM users
JOIN orders
  ON users.id = orders.user_id
WHERE orders.total >= 100000;
SQL

もし orders.total にインデックスがあり、
「10万以上の注文は全体の1%しかない」ような状況なら、
人間の感覚としてはこう動くのが効率よさそうです。

orders から total>=100000 の行だけを先に取る
その行に含まれる user_id を使って、users をJOINする

MySQL も、そう判断すればJOIN順序を入れ替えます。

EXPLAIN をイメージすると、
orders が「最初に読むテーブル」、users が「次にJOINされるテーブル」として並び、
orders では total のインデックス、users では PRIMARY KEY が使われます。

ここで大事なのは、

FROM users JOIN orders と書いていても、
内部的には orders → users の順で処理されることがある

という事実です。

「書いた順番」ではなく、
「どの条件でどれだけ絞れるか」がJOIN順序を決める材料になります。

パターン3：ON に条件を書くか、WHERE に書くか

「INNER JOIN では結果は同じでも、“意味”は違う」

次に、ON と WHERE の違いを、実行順序の観点から見てみます。

同じ結果になるINNER JOINの例を2つ並べます。

SELECT *
FROM users
JOIN orders
  ON users.id = orders.user_id
WHERE users.id = 1;
SQL
SELECT *
FROM users
JOIN orders
  ON users.id = orders.user_id
 AND users.id = 1;
SQL

論理的な順序で書き直すと、こうなります。

FROM users
JOIN orders ON users.id = orders.user_id AND users.id = 1

MySQL は、
「users.id=1 だけを先に取ってからJOINする」
という実行計画を選べるので、
結果としてはどちらも同じになります。

ただし、意味としてはこう違います。

ON に書いた条件
→ 「結合するときの条件」

WHERE に書いた条件
→ 「結合した後に残す行を決める条件」

INNER JOIN では、
「結合候補を作ってからフィルタする」か
「結合候補を作る段階でフィルタする」かの違いが、
結果に出にくいだけです。

この違いがはっきり出るのが LEFT JOIN です。

パターン4：LEFT JOIN での ON と WHERE の違い

「“NULL側も残す”かどうかが変わる」

LEFT JOIN の典型的な例を見てみます。

「ユーザー一覧と、そのユーザーの最新注文（ない人も含む）」
のようなイメージです。

まずは、LEFT JOIN の基本形です。

SELECT
  users.id,
  users.name,
  orders.id   AS order_id,
  orders.total
FROM users
LEFT JOIN orders
  ON users.id = orders.user_id;
SQL

このクエリは、

users を全部残す
orders に対応する行がなければ、orders側がNULLになる

という結果になります。

ここに「特定ユーザーだけ」に絞る条件を足すとき、
WHERE に書くか ON に書くかで意味が変わります。

WHERE に書く場合

SELECT
  users.id,
  users.name,
  orders.id   AS order_id,
  orders.total
FROM users
LEFT JOIN orders
  ON users.id = orders.user_id
WHERE users.id = 1;
SQL

これは、

users を全部LEFT JOINしたあとで、
users.id=1 の行だけ残す

という動きになります。

結果としては、

id=1 のユーザーは必ず1行出る
注文がなければ orders側がNULL

という、期待どおりのLEFT JOINの挙動です。

ON に書く場合（よくある落とし穴）

SELECT
  users.id,
  users.name,
  orders.id   AS order_id,
  orders.total
FROM users
LEFT JOIN orders
  ON users.id = orders.user_id
 AND users.id = 1;
SQL

この場合、

JOINするときの条件が
「users.id = orders.user_id かつ users.id = 1」

になります。

論理的には、

users 全員に対してLEFT JOINはする
ただし、orders がくっつくのは users.id=1 のときだけ

という動きです。

結果としては、

users 全員が出る
id=1 のユーザーには注文がくっつく
それ以外のユーザーは orders側がNULL

という結果になります。

つまり、

WHERE に書くと「結果全体を絞る」
ON に書くと「結合の仕方だけを変える」

という違いが、LEFT JOINでははっきり出ます。

Day8 の時点では、

INNER JOINではONとWHEREの違いは結果に出にくい
LEFT JOINではONとWHEREの違いが結果に直結する

という感覚を持っておけば十分です。

実行順序を意識した“書き方の癖”

「人間が読んでも“どこで絞るか”が分かるSQLにする」

実務でJOINを書くとき、
実行順序を意識した“書き方の癖”をつけておくと、
後から自分や他人が読んだときに理解しやすくなります。

例えば、こういう順番を意識します。

FROM で「ベースになるテーブル」を書く
JOIN で「必要なテーブル」を足していく
ON には「テーブル同士をどう結びつけるか」だけを書く
WHERE には「最終的にどの行を残すか」の条件を書く

さっきの例で言うと、

SELECT
  users.id,
  users.name,
  orders.id   AS order_id,
  orders.total
FROM users
JOIN orders
  ON users.id = orders.user_id
WHERE users.id = 1;
SQL

という書き方は、

users がベース
orders は users.id と user_id で結びつく
最終的には users.id=1 だけ欲しい

という意図が素直に読めます。

この「意図が読めるSQL」は、
MySQLの実行計画とも相性が良くなりやすいです。

JOIN最適化とインデックスの関係を一言でまとめる

「“どの条件でどのテーブルを絞るか”をインデックスで支える」

Day6〜Day8の内容をつなげると、
JOIN最適化の本質はこう言えます。

どのテーブルから先に絞るのが効率的かを考える
その絞り込みに使うカラムに、適切なインデックスを張る

例えば、

WHERE users.id = ?
→ users.id はPRIMARY KEYなのでOK

WHERE orders.total >= ?
→ total にインデックスがあると、ordersから先に絞れる

WHERE orders.user_id = ?
→ user_id にインデックスがあると、特定ユーザーの注文だけ先に取れる

JOINの実行順序は、
「どの条件でどれだけ絞れるか」と
「その条件にインデックスがあるか」で決まっていきます。

Day8 後半では、
この“考え方のつながり”が見えていれば十分です。

Day8 後半のまとめ

users × orders のような2テーブルJOINでも、WHERE users.id = ? のように強く絞り込める条件があれば、MySQL は「users から1行だけ取って、それをキーに orders を探す」という実行順序を選び、逆に WHERE orders.total >= ? のように orders 側で強く絞れる条件があれば、「orders から先に絞ってから users をJOINする」という順序に入れ替えることがある。
INNER JOIN では、ON に条件を書くか WHERE に書くかで結果が変わらないケースが多いが、論理的には「ON＝結合条件」「WHERE＝結合後のフィルタ」という役割の違いがあり、この違いは LEFT JOIN で顕在化し、LEFT JOIN ... ON users.id = orders.user_id に対して WHERE users.id = 1 と書くのと、ON users.id = orders.user_id AND users.id = 1 と書くのとでは、「どのユーザーが結果に残るか」が変わりうる。
JOIN の最適化を考えるときは、「このクエリはどのテーブルから先に絞るのが効率的か」「その絞り込みに使うカラムにインデックスはあるか」という視点が重要で、FROM に書いた順番そのものよりも、「どの条件でどれだけ絞れるか」と「その条件がONなのかWHEREなのか」が、実行順序と結果の両方に影響する。

ここまで腹落ちしていれば、
EXPLAIN を実際に眺めたときに「なぜこの順番でJOINしているのか」を言葉にできるようになり、
“なんとなくJOINを書く人”から“一応、実行順序を意識している人”に一段階ステップアップできています。

前へ
12
スポンサーリンク
タイトルとURLをコピーしました