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は避けるようにしたほうがいいらしい。
参考:漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!!