SQLのパフォーマンスチューニングにおいて、EXPLAINは欠かせない分析ツールです。クエリの実行計画を確認することで、インデックスが効いていない箇所や結合順序の無駄など、パフォーマンス改善の糸口を見つけることができます。
この記事では、EXPLAINの基本的な使い方から、EXPLAIN ANALYZEを用いた実行時間の把握まで、10万件のテストデータを使って実演形式で紹介していきます。
MySQLは8.4.5を利用しています。
テストテーブルの作成とデータ準備
今回は users テーブルと orders テーブルの2つを用意し、それぞれに大量のダミーデータを挿入します。外部キーやインデックスは付けずに、実行計画をわかりやすくすることを目的としています。
テーブル作成SQL
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
age INT
);
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
total DECIMAL(10,2),
created_at DATETIME
);
ダミーデータ挿入SQL（10万件以上対応）
-- users に10万件
INSERT INTO users (name, age)
SELECT
CONCAT('User_', num), FLOOR(20 + (RAND() * 40))
FROM (
SELECT @rownum := @rownum + 1 AS num
FROM
information_schema.columns c1,
information_schema.columns c2,
(SELECT @rownum := 0) AS init
LIMIT 100000
) AS numbers;
-- orders に3万件
INSERT INTO orders (user_id, total, created_at)
SELECT
FLOOR(1 + (RAND() * 10000)),
ROUND(RAND() * 1000, 2),
NOW() - INTERVAL FLOOR(RAND() * 365) DAY
FROM
information_schema.columns c1,
information_schema.columns c2
LIMIT 30000;
EXPLAINの基本的な使い方
EXPLAIN
SELECT
*
FROM
users
WHERE
age > 30;
このように、対象のクエリの前に EXPLAIN をつけるだけで実行計画が表示されます。
実行例：users テーブルの全件検索
| id |
select_type |
table |
partitions |
type |
possible_keys |
key |
key_len |
ref |
rows |
filtered |
Extra |
| 1 |
SIMPLE |
users |
NULL |
ALL |
NULL |
NULL |
NULL |
NULL |
99960 |
33.33 |
Using where |
結合クエリに対するEXPLAINの使用
EXPLAIN
SELECT
U.id,
U.name,
O.total
FROM
users AS U
INNER JOIN
orders AS O
ON U.id = O.user_id;
| id |
select_type |
table |
partitions |
type |
possible_keys |
key |
key_len |
ref |
rows |
filtered |
Extra |
| 1 |
SIMPLE |
O |
NULL |
ALL |
NULL |
NULL |
NULL |
NULL |
29448 |
100.00 |
Using where |
| 1 |
SIMPLE |
U |
NULL |
eq_ref |
PRIMARY |
PRIMARY |
4 |
testdb.O.user_id |
1 |
100.00 |
NULL |
ポイント
- ordersテーブルはインデックスなしの全件スキャン（7〜9ms）
- usersテーブルは主キーインデックスによる高速検索（1件あたり0.0016ms）
EXPLAIN ANALYZEによる詳細な実行時間の確認
MySQL 8.0以降で利用できる EXPLAIN ANALYZE は、従来の EXPLAIN に比べて、より実行時に近い情報を得ることができる強力なツールです。
通常の EXPLAIN は「このクエリはこういう風に実行されるかもしれない」という推定計画を返します。一方で EXPLAIN ANALYZE はクエリを実際に実行した上で、「どれくらいの時間がかかり」「何行が処理されたか」「何回ループされたか」などの実測結果を返してくれます。
EXPLAIN ANALYZE
SELECT
U.name,
O.total
FROM
users AS U
INNER JOIN
orders AS O
ON U.id = O.user_id;
-> Nested loop inner join (cost=13276 rows=29448) (actual time=0.469..61.2 rows=30000 loops=1)
-> Filter: (O.user_id is not null) (cost=2969 rows=29448) (actual time=0.454..9.35 rows=30000 loops=1)
-> Table scan on O (cost=2969 rows=29448) (actual time=0.453..7.42 rows=30000 loops=1)
-> Single-row index lookup on U using PRIMARY (id=O.user_id) (cost=0.25 rows=1) (actual time=0.00157..0.00159 rows=1 loops=30000)
EXPLAINとの違い
- EXPLAIN： 実行計画の「予測値」を表示（rows, cost, typeなど）
- EXPLAIN ANALYZE： 実際にクエリを実行し、「実測値（actual time, loops, 実際のrows）」を返す
この違いにより、例えば EXPLAIN では “rows=10000” と推定されていたのに、実際は “rows=2” だったといったケースもあります。そのような差異を把握することで、MySQLオプティマイザの挙動と実際の処理のズレを確認できます。
各出力項目の意味
actual time=X..Y: クエリステップの開始から終了までにかかった時間（ミリ秒）rows=N: 実際に処理されたレコード数loops=M: そのステップが何回繰り返されたか
例えば、上記出力の末尾には：
loops=30000：ordersの各行に対して、usersの1件をルックアップしている（ネストループ構造）actual time=0.00157..0.00159：usersへの主キー検索は1件あたり約0.0016ミリ秒と非常に高速
なぜ便利なのか？
以下のような状況では EXPLAIN ANALYZE が特に効果を発揮します：
- 実行時間が長いが原因がわからない
- インデックスを貼ったのに効いているか不明
- サブクエリやJOINのパフォーマンスに差がある
- 実行計画の読みと、実際の実行結果に差があると感じたとき
従来の EXPLAIN では判断が難しかった「どの部分に時間がかかっているのか？」という疑問に、EXPLAIN ANALYZEは明確な数値で答えてくれます。
注意点
EXPLAIN ANALYZEは実際にクエリを実行します。 そのため、大量データの更新・削除・挿入クエリなどでは使用に注意が必要です。誤ってUPDATEやDELETEで実行すると、実データに影響を及ぼします（SELECTでのみ使うようにしてください）。
開発・検証環境での強力な武器
たとえばパフォーマンスチューニング中に、クエリの候補が2つあるとき：
- どちらが早いか？
- どちらがテーブル全体をスキャンしているのか？
- インデックスは実際に使われているのか？
こういった判断をするときに、EXPLAIN ANALYZEが実行結果を伴った形でガイドしてくれます。
今回の例のように、usersテーブルに対して主キー検索が高速に行われているという確認ができるのも、実際の実行時間が明示されているからこそわかるポイントです。
MySQL まとめ
EXPLAINはクエリの実行計画を視覚化するのに非常に有用- インデックスの使用状況やスキャンタイプを確認できる
EXPLAIN ANALYZEを使えば実行時間の詳細まで取得可能
今回は使い方に焦点を当てましたが、次回は各出力カラムの意味について詳しく解説する予定です。
次回のテーマ
EXPLAIN出力の各カラム（type, key, rows, Extraなど）を解説
