MySQLのEXPAINの見方

EXPLAINとは

  • クエリオプティマイザがクエリをどのように実行するのかを解明する手段
    • どのインデックス使ってるのかなーとか確認する
  • 実際に実行するとこんな感じになる
mysql> EXPLAIN SELECT * FROM covering_test WHERE key1 = 1;
+----+-------------+---------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | covering_test | NULL       | ref  | key1          | key1 | 4       | const |    2 |   100.00 | NULL  |
+----+-------------+---------------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

EXPLAINをどう使うの？

  • 下記2点を達成できるようEXPLAINを読み解いていこう
  • 書き換える前と後でクエリの実行結果が同じになる
  • EXPLAINがよりよい実行計画を表示する

各カラムについて

特に重要なのはtypeカラム

id

  • 各SELECT文を識別するための通し番号で実行順序

select_type

クエリの種類を表す

  • SIMPLE
    • クエリにサブクエリやユニオンが含まれていない
      • クエリがSIMPLEってわけではない!
    • 上の例の通り
  • SUBQUERY
    • SELECTリストのサブクエリに含まれている(FROM句ではない)SELECT文
  • DERIVED
    • FROM句のサブクエリに含まれているSELECT文
      • MySQLはサブクエリを再帰的に実行して一時テーブルに配置する
      • サーバーはこの一時テーブルを「派生テーブル」と呼ぶが、それはサブクエリから派生しているから
  • PRIMARY, UNION, UNION_RESULT
    • PRIMARY - UNIONの1つ目のSELECT文であることを示す
    • UNION - UNIONの2つ目以降のSELECT文であることを示す
    • UNION_RESULT - UNIONの無名の一時テーブルから結果を取得するためのSELECT文であることを示す

table

  • アクセスする対象のテーブル
  • 結合がどの順番で行われているかもこのカラムを上から見ていくと分かる
  • クラスタインデックスやカバリングインデックスで実際のテーブルにアクセスしない場合でも表示されるようだ

partitions

  • 使われているpartitionを示す
  • 使われていない場合はNULL

type

MySQLがテーブル内の行を検索する方法

  • ALL
    • テーブルスキャンと呼ばれるもの
    • インデックスが全く使われず、テーブルを最初から最後までスキャンしなければならない
    • これが出た場合、改善が必要と思われる
  • index
    • カバリングインデックス(ExtraカラムにUsing indexと表示される)とそれ以外で異なる
      • カバリングインデックスの場合
        • インデックスツリーのみが全て読み込まれるのでALLより速い
      • それ以外の場合
        • インデックスからの読み取りを利用して、インデックス順でデータ行を参照する。高コスト
  • range
    • インデックスを利用した範囲検索
    • WHERE句にBETWEENまたは不等号が来ている場合
  • ref
    • ユニーク（PRIMARYまたはUNIQUE）でないインデックスを使った等価検索（WHERE key = value）
  • eq_ref, const
    • PRIARY KEYまたはUNIQUE KEY(単一行であることが分かっている)が使われている
    • eq_refはJOINのとき、constはそれ以外の時という違いがある

possible_keys

  • 利用可能なインデックスの候補

key

  • 実際に利用されたキー

key_len

  • 選択されたキーの長さ
    • intだったら4バイトとか
    • 複合インデックスを使った場合には2つのキーの長さの合算値
  • 短い方が高速

ref

  • キーと比較されている値やカラムの種類
    • 定数の場合にはconst
    • joinの場合には、結合する相手側のテーブルで検索条件として利用されているカラム

rows

  • フェッチされる行数の見積もり。あくまで見積もりなので正確な行数ではない
    • ただし、サブクエリ(DERIVED)の場合は、実際に実行しないと以降の見積もりができないので正確な値が出される
  • フェッチ行が全て結果行として返却されるわけではない。
    • フェッチされた行からWHERE句などで絞り込みが行われる場合もある
  • JOINする場合は、各クエリのrowsの値の積がフェッチされる行の見積もりになる

filtered

  • テーブル条件によってフィルタ処理されるテーブル行の推定の割合
  • typeがRANGE, index, rage, index_mergeの時、利用されるようだがよく分かっていない

Extra

上記カラム以外の情報が出力される
その他っぽいけどとても大事

  • Using index
    • カバリングインデックスが使用されているので、テーブルアクセスが発生していない
  • Using where
    • テーブルから行をフェッチした後に絞り込みが行われている
    • フェッチ時点で行数制限したいので、WHERE句でインデックスを適切に利用する等何らかの改良の余地があると思われる
  • Using temporary
    • GROUP BY や ORDER BY でソートするために一時テーブルを使用することを意味する
  • Using filesort
    • インデックスでのソートではなく、ソートアルゴリズムが使われている
  • Range checked for each record (index map: N)
    • JOINにおいてrangeまたはindex_mergeが利用される

参考