EXPLAIN
- SQL の実行計画についての情報を取得するためのステートメント
- 注意点
- EXPLAIN 実行時の負荷に注意
- MySQL 5.5 以下でサブクエリがあると、サブクエリが実行されるのでご注意
- MySQL 5.6 以上は特段の負荷はなさそう
- 統計データは推定値であり、確実に正確ではない
- EXPLAIN 実行時の負荷に注意
例
MySQL [employees]> EXPLAIN SELECT * FROM employees WHERE hire_date = '1986-06-26'; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299778 | 10.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)-
- SELECT 識別子
- サブクエリなどがあると増える
- 特に気にしなくて良さそう
-
- SELECT の種類
- オプティマイザがどんなクエリの解析を行っているか見る
- 例
SIMPLE
- クエリにサブクエリや UNION が含まれていないことを意味する
SUBQUERY
MySQL [sakila]> EXPLAIN SELECT ( SELECT 1 FROM actor LIMIT 1 ) FROM film; +----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+ | 1 | PRIMARY | film | NULL | index | NULL | idx_fk_language_id | 1 | NULL | 1000 | 100.00 | Using index | | 2 | SUBQUERY | actor | NULL | index | NULL | idx_actor_last_name | 137 | NULL | 13 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.07 sec)- サブクエリが含まれている SELECT 文である
- ただ、 FROM 句には含まれていない
DERIVED
MySQL [sakila]> EXPLAIN SELECT film_id FROM ( SELECT film_id FROM film GROUP BY film_id ) AS der; +----+-------------+------------+------------+-------+------------------------------------------------------------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+------------------------------------------------------------------+---------+---------+------+------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL | | 2 | DERIVED | film | NULL | index | PRIMARY,idx_title,idx_fk_language_id,idx_fk_original_language_id | PRIMARY | 2 | NULL | 1000 | 100.00 | Using index | +----+-------------+------------+------------+-------+------------------------------------------------------------------+---------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.01 sec)- FROM 句にサブクエリが含まれている SELECT 文である
UNION / UNION RESULT
MySQL [sakila]> EXPLAIN SELECT customer_id FROM payment WHERE payment_date >= '2005-05-01' AND payment_date < '2005-06-01' UNION SELECT customer_id FROM payment WHERE payment_date >= '2005-06-01' AND payment_date < '2005-07-01'; +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | payment | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 2 | UNION | payment | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.01 sec)- UNION : UNION の 2 つ目のクエリであることを示す
- UNION RESULT : UNION の無名の一時テーブルから結果を取得するための SELECT 文
-
対象のテーブル
AS 句でエイリアスを指定している場合には、エイリアス名
FROM 句にサブクエリが含まれる場合、table 列は
形式となり、N はサブクエリの id を示す - 前掲 DERIVED の EXPLAIN 情報を参照
UNION が含まれる場合、UNION RESULT テーブル列には UNION の対象となる id のリストが含まれる
- 前掲 UNION / UNION RESULT の EXPLAIN 情報を参照
例
1 MySQL [sakila]> EXPLAIN 2 SELECT actor_id, 3 ( SELECT 1 FROM film_actor WHERE film_actor.actor_id = 4 der_1.actor_id LIMIT 1 ) 5 FROM ( 6 SELECT actor_id 7 FROM actor LIMIT 5 8 ) AS der_1 9 UNION ALL 10 SELECT film_id, 11 ( SELECT @var1 FROM rental LIMIT 1 ) 12 FROM ( 13 SELECT film_id, 14 ( SELECT 1 FROM store LIMIT 1 ) 15 FROM film LIMIT 5 16 ) AS der_2; +----+----------------------+------------+------------+-------+---------------+---------------------+---------+----------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+----------------------+------------+------------+-------+---------------+---------------------+---------+----------------+------+----------+-------------+ | 1 | PRIMARY | <derived3> | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | 3 | DERIVED | actor | NULL | index | NULL | idx_actor_last_name | 137 | NULL | 13 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | film_actor | NULL | ref | PRIMARY | PRIMARY | 2 | der_1.actor_id | 27 | 100.00 | Using index | | 4 | UNION | <derived6> | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | 6 | DERIVED | film | NULL | index | NULL | idx_fk_language_id | 1 | NULL | 1000 | 100.00 | Using index | | 7 | SUBQUERY | store | NULL | index | NULL | idx_unique_manager | 1 | NULL | 1 | 100.00 | Using index | | 5 | UNCACHEABLE SUBQUERY | rental | NULL | index | NULL | idx_fk_staff_id | 1 | NULL | 1 | 100.00 | Using index | +----+----------------------+------------+------------+-------+---------------+---------------------+---------+----------------+------+----------+-------------+ 7 rows in set, 2 warnings (0.00 sec)- EXPLAIN 結果 1 行目
- 最初の SELECT 文
- table が
となっているため、id 3 を参照する
- EXPLAIN 結果 2 行目
- 元の SQL の 6 〜 7 行目
- id 3 は FROM 句のサブクエリになっているため、 DERIVED と表示されている
- id が 3 なのはクエリの 3 つ目の SELECT 文の一部であるから
- EXPLAIN 結果 3 行目
- 元の SQL の 3 〜 4 行目
- DEPENDENT SUBQUERY と表示されているので EXPLAIN 結果の 2 行目の結果に依存することを意味する
- 相関サブクエリ
- EXPLAIN 結果 4 行目
- 元の SQL の 9 行目
- UNION と表示されているため、これ以降は 2 つ目の SELECT 文であることを示す
- table が
となっているため、id 6 を参照する
- EXPLAIN 結果 5 行目
- 元の SQL の 13 〜 15 行目
- id 6 は FROM 句のサブクエリになっているため、 DERIVED と表示されている
- EXPLAIN 結果 6 行目
- 元の SQL の 14 行目
- EXPLAIN 結果 7 行目
- 元の SQL の 11 行目
- ユーザ変数を利用しているため UNCACHEABLE SUBQUERY となっている
- EXPLAIN 結果 1 行目
-
- レコードが参照するパーティション
- PARTITIONS 句が指定されている場合のみなので、 NULL の場合が多いかと
- 特に気にしなくて良い
-
対象のテーブルに対してどのような方法でアクセスするか、インデックスの利用有無、読み取り範囲など分かる
- Optimizer が見積もった結果
- 特に InnoDB はクラスターインデックスなので、下手にセカンダリーインデックスを使うよりも速いケースもある
下記は最も重要なアクセスタイプを、最も不適切なものから順に示す
型 内容 ALL 行を検索するためにテーブルを最初から最後までスキャンする必要がある(フルテーブルスキャン)ので遅い index インデックスの順序でテーブルをスキャンするので遅い。カバリングインデックス(Extra 列に "Using index" が表示)を利用している場合は良い。 range 制限付きのインデックススキャン。フルスキャンよりは良い。WHERE 句に BETWEEN, <, IN, OR などが含まれているクエリは範囲スキャンである。 ref 非ユニークキーによる選択。単一の値にマッチする行を返すインデックスアクセスなので良い。 eq_ref 最大で 1 つの値が返されることを MySQL が知っているインデックスルックアップなので良い。JOIN において PRIARY KEY または UNIQUE KEY が利用される時のアクセスタイプ。 const, system テーブルアクセスが 1 回のため最速 NULL テーブルアクセスが不必要な場合。インデックス付きの列から最小値を取得するとか - ダメなのは ALL ではなく、本当の最適なアクセスと違うとき
- rows, key_len が判断の助けになる
- ダメなのは ALL ではなく、本当の最適なアクセスと違うとき
-
- MySQL がこのテーブル内の行の検索に使用するために選択できるインデックスを示す
-
- 実際に使用されたインデックスを表示
-
- 選択されたインデックスキーのバイト数
- インデックスキーの長さが短いほうが高速に走査される
-
インデックスと比較されるカラム
JOIN が実行されている時には、結合する相手側のテーブルで検索条件として利用されているカラムが表示される
例えば次の例では、Country テーブルは City テーブルと City.CountryCode カラムで JOIN されるということを示している。
mysql> EXPLAIN SELECT * FROM Country,City WHERE Country.Code=City.CountryCode AND Country.Name LIKE 'A%'; +----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+ | 1 | SIMPLE | City | ALL | NULL | NULL | NULL | NULL | 4079 | | | 1 | SIMPLE | Country | eq_ref | PRIMARY | PRIMARY | 3 | world.City.CountryCode | 1 | Using where | +----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+ 2 rows in set (0.00 sec)
-
- 調査される行の大まかな見積もり
- インデックスを付与することで、この行数が減ったかどうかを確認する
-
- テーブル条件によってフィルタ処理される行の割合
-
- EXPLAIN 追加情報
- そのクエリを実行するためにオプティマイザがどのような戦略を選択したかということを示すフィールド
- Executor の都合により表示
- 例
- Using index
- カバリングインデックスを使用することを示す。
- Using where
- 既存のインデックスでは WHERE 句をすべて満たせていないため、インデックスを再確認する
- Using temporary
- クエリの実行にテンポラリテーブルを必要としている
- 最悪ディスクに書き出すので適切なインデックスを付与する、クエリの書き換えを検討する
- Using filesort
- インデックスを使用しないで、メモリ上でソート処理を行おうとするので遅くなる
- WHERE 句の結果が小さい場合にはそこまで問題ならないが、大きい場合には注意が必要
- Using where; Using filesort
- Executor が WHERE をフィルタしている、Executor が ORDER BY のソートを行っている
- Using index
-
ドキュメント
その他
- オプティマイザが判断したインデックスが本当に最適なインデックスかどうかは USE INDEX および IGNORE INDEX 構文を利用して見る
- USE INDEX
- possible_keys の値を書き換えるためのキーワード
- IGNORE INDEX
- オプティマイザが選んだ possible_keys から特定のインデックスを除外する
- 実際に実行して見る場合には複数回実行すること
- バッファープールにのっているかどうかで結果が変わってくる
- USE INDEX
- 更新クエリの場合には SELECT に置き換えて実施してみる
- オプティマイザが判断したインデックスが本当に最適なインデックスかどうかは USE INDEX および IGNORE INDEX 構文を利用して見る
フルスキャン
- テーブルに含まれているレコード数を最初から最後まで全部読み込む方法
- テーブルフルアクセス
レンジスキャン
- テーブルの一部のレコードのみアクセスする方法