EXPLAINとは
データベースがSQL文をどのように実行するかの実行計画を確認するコマンドです。
業務では、追加したインデックスが実際に使われているかを確認するために使用しました。
使い方は非常にシンプルで、SQL文の先頭に EXPLAIN を付けて実行するだけ。
例えばこんなUsersテーブルがあるとします。
CREATE TABLE Users (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
name VARCHAR(100) NOT NULL,
age INT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email (email)
);
サンプルデータ
INSERT INTO Users (email, name, age)
VALUES
('tanaka@example.com', '田中', 25),
('suzuki@example.com', '鈴木', 30),
('yamada@example.com', '山田', 35);
これでEXPLAIN実行すると
EXPLAIN
SELECT *
FROM Users
WHERE email = 'tanaka@example.com';
このような実行計画が返ってきます
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------+
| 1 | SIMPLE | Users | ref | idx_email | idx_email | 1022 | const | 1 | |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------+
1 row in set (0.00 sec)
うーん、わかりそうでわからない...
ひとつずつ見ていきます。
EXPLAIN 実行結果の読み方
id: 1 : SQL の実行順序を表す番号
今回のSQLは単純なSELECTだけなので1になっています。
JOIN やサブクエリが入ると、複数のid が表示されることがあります。
select_type: SIMPLE: SELECTの種類
今回の SIMPLE は、シンプルなSELECT文という意味です。
JOIN や UNION、サブクエリなどを使った複雑なSQLになると、
- PRIMARY
- SUBQUERY
- DERIVED
などが表示されます。
table: Users: 実際にアクセスしているテーブル名
JOIN を使う場合は、複数のテーブルが表示されることもあります。
type: ref: どのようにテーブルを検索するか
⚠️ EXPLAIN の中でも、パフォーマンスを見るのにかなり重要な項目。
今回の ref は、インデックスを利用した検索を意味しています。
今回は email カラムにインデックスがあるため、
INDEX idx_email (email)を利用して検索しています。
もしインデックスが無い場合は、 ALL となり、テーブル全件検索していることになります。
possible_keys: idx_email: 利用できる可能性のあるインデックス
今回の SQL では email カラムで検索しているため、idx_email が使えそう、とMySQLが判断しています。
key: idx_email: 実際に使用されたインデックス
possible_keys は「候補」でしたが、key は、本当に使われたインデックスを表しています。
key_len: 1022: 使用したインデックスの長さ（バイト数）
主に複合インデックスを分析する際に利用される項目です。
複合インデックスがどこまで効いているかを推測する材料となりますが、今回のようなシンプルなSQLではあまり意識する必要はありません。
ref: const: インデックス検索時に何と比較しているか
今回の SQL は、'tanaka@example.com'のような固定値で検索しているため const になっています。
rows: 1: 何行くらい読み込む必要がありそうか」の予測値
MySQL が「何行くらい読み込む必要がありそうか」を予測した値です。
今回はインデックス検索なので、1行程度読めば見つかりそうと判断されています。
数値が大きいほど、処理コストが高くなる可能性があります。
Extra: :追加情報
今回は何も表示されていません。
例えば、
Using where
なら WHERE 条件で絞り込みを行っていることを意味します。
Using temporary
Using filesort
などが表示されると、MySQL が追加の処理をしていると捉えることができるので、
パフォーマンス改善のヒントになることがあります。
ただ、
データ件数が少ない
一時テーブルのコストが小さい
インデックスで解決できない処理
なら、問題ないケースもあります。
まとめ
EXPLAINを実行する目的のほとんどが、パフォーマンスの確認かと思うので、
私のようなSQL初学者はまず
- type
- key
- rows
あたりがしっかり読み取れると良いかと思います！