explainの使い方

from:

https://nysql.g.hatena.ne.jp/py4s-tnk/20090502/1241278619

ポイント

・クエリの実行計画はツリーで表現されるが、explainは表で表現しているので、explainを見るためにはツリー構造をイメージできないといけないらしい
・重要なのは、select_typeとかtypeらしい。
・実際のデータを利用してexplainする。データ件数によってexplainの結果が変わってくるから。
・select以外の解析はできない。updateをselectに書き換えるなどしてexplainを使うとよい

mysql> explain select * from hoge;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | hoge  | ALL  | NULL          | NULL | NULL    | NULL | 100000 |       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)

■id

・joinの場合、番号が同じ
・サブクエリの場合、外部クエリとサブクエリで別々のidがつけられる。

■select_type

・sqlがサブクエリやUNIONを使わない場合、SIMPLEとなる。
・sqlがjoinで構成される場合、select_typeは常にSIMPLEとなる。どんなに複雑なjoinでも。
・sqlにサブクエリを含む場合、PRIMARY、SUBQUERY、DEPENDENT SUBQUERY、UNCACHEABLE SUBQUERY、DERIVEDのどれかになる
・sqlにUNIONを含む場合、PRIMARY、UNION、UNION RESULT、DEPENDENT UNION、UNCACHEABLE UNIONのどれかとなる
・select_type=PRIMARYの意味は、サブクエリの場合、外部クエリを示す。UNIONの場合、最初にFETCHされるテーブルを示すらしい
・相関関係のないサブクエリは、SUBQUERY。相関関係のあるサブクエリはDEPENDENT SUBQUERYとなる。
・from句におけるサブクエリの場合は、DERIVEDとなる。
・実行毎に結果が変わる可能性があるサブクエリは、UNCACHEABLE SUBQUERYとなる。
・通常は外部クエリが実行されてからサブクエリが実行されるが、select_typeがDERIVEDとなっている場合は、サブクエリが最初に実行されるらしい。
・select_typeがSUBQUERYの場合は、サブクエリは一回だけ実行され、二回目以降はキャッシュされたデータが利用されるらしい。
・select_typeが、DEPENDENT SUBQUERYかUNCACHEABLE SUBQUERYの場合は、SUBQUERYみたいにキャッシュされず、毎回サブクエリが実行されるらしい。

■type

・ここがindexとかALLになっていると、SQLのチューニングが必要になるらしい。

・constが最速。PRIMARY KEYとかUNIQUEインデックスが使われているとこれになるらしい。
・eq_refはjoinにおけるtypeで、constと同じと考えてよい（たぶん）
・refは、非ユニーク（つまりPRIMARYでもUNIQUEでもない）なインデックスを使って=検索をした場合のもの
・rangeはインデックスによる範囲検索
・indexはインデックス全部をスキャンしてることを示す。
・ALLは、いわゆる全表検索

■possible_keys

・インデックスを使用した場合に、インデックス名が表示される

・オプティマイザがあげた、インデックス候補となるキーの一覧

■key

・インデックスを使用した場合に、インデックス名が表示される

・オプティマイザが選択したキー

■key_len

・選択されたkeyの長さ。これが短いほうが有利らしい

■ref

・where句で定数と比較している場合はconstとなる。joinの場合は、joinする相手側テーブルのカラムがここに表示される。

■rows

・フェッチされる予測行数（基本的に見積もりなのだが、select_typeがDERIVEDの場合は例外で正しい行数が表示される。理由はDERIVEの場合はexplainの場合でも実際にSQLが実行されるかららしい）

・ここで表示される行数はあくまでフェッチされる行数なので、where句で絞込みをしてる場合は返される行数はさらに少なくなる。

■extra

・オプティマイザのひとりごとが表示されるらしい。

・ここが理解できると、オプティマイザの挙動が理解できるらしい

・Using temporary(一時表の作成）とUsing filesortは避けるようにしたほうがいいらしい。

・参考:Mysqlpracticewiki.com

参考:漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!!

参考:MySQLのオプティマイザが少し変わったらしい