Day16 後半のゴール
「JOIN を含む EXPLAIN を読んで“どこがボトルネックか”を言えるようになる」
前半では、単一テーブルの EXPLAIN を「table / type / key / rows」の4つに絞って見ました。
後半では、一気に実務寄りに寄せていきます。
JOIN を含むクエリの EXPLAIN を読むpossible_keys と key の違いを理解するExtra に出てくる「Using index」「Using filesort」「Using temporary」の意味をつかむ
ここまで行くと、「遅いクエリを見たときに、どこから手を付けるか」がかなりクリアになります。
JOIN を含む EXPLAIN の基本的な見方
「“どのテーブルを起点に、どの順番でつないでいるか”を見る」
まずは、シンプルな JOIN クエリから始めます。
SELECT
o.id,
u.name,
o.total
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.email = 'foo@example.com';
これに対して EXPLAIN を実行します。
EXPLAIN
SELECT
o.id,
u.name,
o.total
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.email = 'foo@example.com';
インデックスがそれなりに貼られている前提で、だいたいこんな結果になります（簡略版）。
| id | select_type | table | type | possible_keys | key | rows | Extra |
|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | u | ref | PRIMARY,idx_email | idx_email | 1 | Using where |
| 1 | SIMPLE | o | ref | fk_orders_user | fk_orders_user | 10 | NULL |
ここでまず見るのは、やはり table / type / key / rows です。
users（u）から先に読んでいる
u は idx_email で1行だけ取れている
orders（o）は fk_orders_user（user_id のインデックス）で 10 行くらい取るつもり
ということが分かります。
JOIN の EXPLAIN を読むときのコツは、
どのテーブルを起点にしているか
そのテーブルから何行くらい取ってきて
次のテーブルをどうつないでいるか
を、上から順に追っていくことです。
possible_keys と key の違い
「“使える候補”と“実際に選ばれた一本”」
JOIN が絡むと、possible_keys と key の違いが効いてきます。
possible_keys
このクエリで「使えそうなインデックスの候補」の一覧
key
その中から「実際にオプティマイザが選んだインデックス」
さっきの例で users の行を見ると、こうなっていました。
| table | type | possible_keys | key |
|---|---|---|---|
| u | ref | PRIMARY,idx_email | idx_email |
これは、
PRIMARY（id の主キー）も idx_email も、理論上は使える
その中から、オプティマイザは idx_email を選んだ
という意味です。
もし、possible_keys に何か出ているのに key = NULL になっていたら、
「使えそうなインデックスはあるのに、あえて使っていない」
ということになります。
この場合は、
統計情報的に「インデックスを使ってもあまり絞れない」と判断された
クエリの書き方的に、インデックスが効きにくい条件になっている
といった可能性があります。
Day16 の段階では、
possible_keys に名前が出ているか
key に実際のインデックス名が入っているか
を見て、
インデックスが「候補として存在しない」のか
「あるのに使われていない」のか
を切り分けられるようになるだけで、かなり前進です。
Extra カラムの読み方
「“Using index”はうれしい、“Using filesort / temporary”は要注意」
Extra カラムには、「追加のヒント」がいろいろ出てきます。
全部覚える必要はありませんが、実務でよく見るものだけ押さえましょう。
特に重要なのは、この3つです。
Using index
Using filesort
Using temporary
順番にイメージをつかんでいきます。
Extra: Using index
「インデックスだけで必要な情報が取れている“カバーリングインデックス”」
Extra に「Using index」と出ている場合、それはかなり良いサインです。
意味としては、
インデックスに載っている情報だけで、SELECT で必要なカラムが全部取れている
テーブル本体の行データを読みに行かなくてよい
という状態です。
例えば、次のようなインデックスがあるとします。
CREATE INDEX idx_users_email_name
ON users (email, name);
そして、こういうクエリを投げます。
EXPLAIN
SELECT name
FROM users
WHERE email = 'foo@example.com';
このとき、インデックス idx_users_email_name には email も name も載っているので、
MySQL はテーブル本体を読まずに、インデックスだけで処理を完結できます。
EXPLAIN の結果は、だいたいこうなります。
| table | type | key | rows | Extra |
|---|---|---|---|---|
| users | ref | idx_users_email_name | 1 | Using index |
「Using index」が出ているときは、
インデックスだけで完結していて、I/O 的にかなり効率が良い
という意味で、「うれしい状態」と覚えておいてOKです。
Extra: Using filesort
「ORDER BY のために“別途ソート処理”が走っているサイン」
Extra に「Using filesort」と出ている場合は、少し注意が必要です。
名前に “file” とありますが、必ずしもディスクに書くとは限らず、
メモリ上でのソートも含めた「独自のソート処理」を指します。
意味としては、
インデックスだけでは ORDER BY の順序を満たせないので、
一度データを集めてから別途ソートしている
という状態です。
例えば、次のようなテーブルとインデックスがあるとします。
CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
created_at DATETIME NOT NULL,
INDEX idx_posts_user (user_id)
) ENGINE=InnoDB;
ここで、こういうクエリを投げます。
EXPLAIN
SELECT *
FROM posts
WHERE user_id = 1
ORDER BY created_at DESC;
インデックスは user_id だけに貼られていて、created_at は含まれていません。
この場合、EXPLAIN の Extra に「Using filesort」が出ることが多いです。
| table | type | key | rows | Extra |
|---|---|---|---|---|
| posts | ref | idx_posts_user | 100 | Using where; Using filesort |
これは、
user_id でインデックス検索はできている
しかし、created_at での並び替えはインデックスでカバーできない
そのため、取得した行を別途ソートしている
という意味です。
行数が少なければ問題になりませんが、
大量の行を filesort するようなクエリは、パフォーマンス上のボトルネックになりやすいです。
対策としては、
ORDER BY に使うカラムも含めた複合インデックスを貼る
（例：INDEX idx_posts_user_created (user_id, created_at)）
といった方向が見えてきます。
Extra: Using temporary
「GROUP BY や複雑な集計で“作業用一時テーブル”が作られている」
Extra に「Using temporary」と出ている場合も、要注意サインです。
意味としては、
このクエリを処理するために、一時テーブルを内部的に作っている
という状態です。
典型的には、GROUP BY や複雑な集計で出てきます。
例えば、次のようなクエリです。
EXPLAIN
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id;
適切なインデックスがない場合、EXPLAIN はこうなりがちです。
| table | type | key | rows | Extra |
|---|---|---|---|---|
| orders | ALL | NULL | 10000 | Using temporary; Using filesort |
ここでは、
orders を全件スキャンして
一時テーブルを作り
そこで GROUP BY とソートをしている
という動きになっています。
行数が多いと、一時テーブルの作成とソートが重くなります。
対策としては、
GROUP BY に使うカラムにインデックスを貼る
（例：INDEX idx_orders_user (user_id)）
などが考えられます。
Day16 の段階では、
Using temporary や Using filesort が出ていたら、「ちょっと重そうだな」と警戒する
インデックス設計で改善できないかを考える
くらいの感覚を持てれば十分です。
JOIN とインデックス設計の関係を EXPLAIN で確認する
「“どのテーブルのどのカラムにインデックスが必要か”を逆算する」
JOIN のパフォーマンスは、インデックス設計にかなり依存します。
EXPLAIN は、その確認ツールとして非常に優秀です。
先ほどの orders と users の JOIN をもう一度見ます。
SELECT
o.id,
u.name,
o.total
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.email = 'foo@example.com';
良い状態の EXPLAIN はこうでした。
| table | type | key | rows | Extra |
|---|---|---|---|---|
| u | ref | idx_email | 1 | Using where |
| o | ref | fk_orders_user | 10 | NULL |
ここから読み取れるのは、
users.email にインデックスがある（idx_email）
orders.user_id にインデックスがある（fk_orders_user）
その結果、users から1行取り、orders から関連する10行だけをインデックスで取れている
ということです。
もし、orders.user_id にインデックスがなかったらどうなるか。
EXPLAIN はこうなりがちです。
| table | type | key | rows | Extra |
|---|---|---|---|---|
| u | ref | idx_email | 1 | Using where |
| o | ALL | NULL | 10000 | Using where |
orders 側が type = ALL で key = NULL になり、
「users から1行取ったあと、orders を全件スキャンして user_id で絞り込む」
という動きになります。
この差は、テーブルが大きくなるほど効いてきます。
EXPLAIN を見ながら、
JOIN の ON 句に出てくるカラム
WHERE 句で絞り込みに使っているカラム
にインデックスが貼られているか、
そして実際に key として使われているか、
を確認する癖をつけると、インデックス設計の精度が一気に上がります。
「遅いクエリを見たときの最初の一歩」を決めておく
「EXPLAIN を叩いて“type / key / rows / Extra”に一言コメントする」
Day16 の締めとして、「実務でどう使うか」をイメージしておきましょう。
もしあなたが、
このクエリ、なんか遅いな…
と感じたとき、最初にやるべきことは決まっています。
そのクエリに対して EXPLAIN を叩く
各テーブルの行について、
type / key / rows / Extra をざっと眺める
「どのテーブルがフルスキャンになっているか」
「どこで Using filesort / temporary が出ているか」
を一言で説明してみる
ここまでできれば、
「どこから手を付けるべきか」が見えてきます。
インデックスを貼るべきカラムはどこか
クエリの書き方を変えた方がよさそうな箇所はどこか
テーブル設計そのものを見直す必要がありそうか
Day16 は、「EXPLAIN を怖がらずに、会話相手として使い始める日」です。
完璧に理解する必要はありません。
まずは、「このクエリ、EXPLAIN するとこういう顔をしているのか」と眺めるところからで十分です。
Day16 後半のまとめ
JOIN を含むクエリの EXPLAIN では、各行が「どのテーブルをどの順番でどう読んでいるか」を表しており、possible_keys は「理論上使えそうなインデックスの候補」、key は「その中から実際にオプティマイザが選んだ一本」であるため、「possible_keys に名前があるのに key が NULL」なら“インデックスはあるが使われていない”という判断ができる。Extra カラムは追加のヒントを教えてくれ、「Using index」はインデックスだけで必要なカラムが取れている“カバーリングインデックス”状態でうれしいサイン、「Using filesort」は ORDER BY のために別途ソート処理が走っている、「Using temporary」は GROUP BY などのために一時テーブルが作られているサインで、特に大きなテーブルで filesort や temporary が出ている場合はインデックス設計やクエリの書き方を見直す余地が大きい。
最終的に、「遅いクエリに出会ったらまず EXPLAIN を叩き、各テーブル行について type / key / rows / Extra を見て“どこがフルスキャンか”“どこで filesort / temporary が発生しているか”を一言で説明する」という習慣を持つことで、インデックス設計・クエリ改善・テーブル設計のどこから手を付けるべきかを論理的に判断できるようになり、ここから先のパフォーマンスチューニングの土台がしっかり固まっていく。
