EXPLAIN
EXPLAIN とは、MySQL サーバーがどのようにクエリを実行するか、つまり実行計画はどのようなものになるかを知りたいときに使用する基本的なコマンドとなる。
従来は表形式のものしかなかったが、MySQL 5.6 からは JSON フォーマットの出力も得られる。JSON フォーマットのものは、単体でも情報が豊富だが、MySQL Workbench と組み合わせることで可視化が可能となる。
表形式の EXPLAIN
以下は 3 つのテーブルを JOIN する SELECT 文の例となる。
mysql> SELECT City.Name, Country.Code, CountryLanguage.Language FROM City JOIN Country ON City.CountryCode = Country.Code AND City.Id = Country.Capital JOIN CountryLanguage ON CountryLanguage.CountryCode = City.CountryCode LIMIT 10;
+------------+------+------------+
| Name | Code | Language |
+------------+------+------------+
| Oranjestad | ABW | Dutch |
| Oranjestad | ABW | English |
| Oranjestad | ABW | Papiamento |
| Oranjestad | ABW | Spanish |
| Kabul | AFG | Balochi |
| Kabul | AFG | Dari |
| Kabul | AFG | Pashto |
| Kabul | AFG | Turkmenian |
| Kabul | AFG | Uzbek |
| Luanda | AGO | Ambo |
+------------+------+------------+
10 rows in set (0.00 sec) これを EXPLAIN で見てみる。
なお、MySQL 5.7 ではデフォルトの挙動が変更され、何もオプションを指定しない EXPLAIN でも、partitions と filtered というカラムが表示されるようになった。
[ MySQL 5.6 の場合 ]
mysql> EXPLAIN SELECT City.Name, Country.Code, CountryLanguage.Language FROM City JOIN Country ON City.CountryCode = Country.Code AND City.Id = Country.Capital JOIN CountryLanguage ON CountryLanguage.CountryCode = City.CountryCode;
+----+-------------+-----------------+--------+---------------------+---------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+--------+---------------------+---------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | Country | ALL | PRIMARY | NULL | NULL | NULL | 239 | Using where |
| 1 | SIMPLE | CountryLanguage | ref | PRIMARY,CountryCode | PRIMARY | 9 | world.Country.Code | 1 | Using index |
| 1 | SIMPLE | City | eq_ref | PRIMARY,CountryCode | PRIMARY | 4 | world.Country.Capital | 1 | Using where |
+----+-------------+-----------------+--------+---------------------+---------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec) [ MySQL 5.7 の場合 ]
mysql> EXPLAIN SELECT City.Name, Country.Code, CountryLanguage.Language FROM City JOIN Country ON City.CountryCode = Country.Code AND City.Id = Country.Capital JOIN CountryLanguage ON CountryLanguage.CountryCode = City.CountryCode;
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+-------------+
| 1 | SIMPLE | CountryLanguage | NULL | index | PRIMARY,CountryCode | CountryCode | 9 | NULL | 984 | 100.00 | Using index |
| 1 | SIMPLE | Country | NULL | eq_ref | PRIMARY | PRIMARY | 9 | world.CountryLanguage.CountryCode | 1 | 100.00 | Using where |
| 1 | SIMPLE | City | NULL | eq_ref | PRIMARY,CountryCode | PRIMARY | 4 | world.Country.Capital | 1 | 5.00 | Using where |
+----+-------------+-----------------+------------+--------+---------------------+-------------+---------+-----------------------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec) 上記の EXPLAIN には 3 つの行が含まれているが、それぞれテーブルへのアクセスを表すものとなっている。JOIN の場合は上から順番にアクセスが行われると考えて良い。どのテーブルに対するアクセスを表しているかは table フィールドに表示されている。よって、このクエリを実行すると、MySQL 5.6 の場合は Country -> CountryLanguage -> City 、MySQL 5.7 の場合は CountryLanguage -> Country -> City という順番で JOIN されることが分かる。
以下に EXPLAIN の各フィールドについて記載する。
id
SELECT につけられた番号。MySQL は JOIN をひとつの単位として実行するようになっており、JOIN しか行われていないクエリにおいては id は常に 1 となる。
サブクエリや UNION があると 2 以上の id が存在する。
select_type
このテーブルがどのような文脈でアクセスされるかを示す。JOIN の場合、select_type は常に SIMPLE となる。
サブクエリや UNION があると SIMPLE 以外の select_type が存在する。
table
どのテーブルに対するアクセスかを示す。
partitions
パーティショニングが行われている場合に使われるフィールドとなる。今回の例では使用されていないため NULL となる。
type
アクセスタイプとは、どのようにそのテーブルから行データを取ってくるかを示す、アクセスタイプが記載される。
具体的なアクセスタイプ一覧を以下に示す。
| アクセスタイプ | 説明 |
|---|---|
| const | 主キーあるいはユニークキーによるルックアップ (等価比較)。JOIN ではなく、最外部のテーブルに対するアクセスに対するアクセスタイプ。結果は常に 1 行となる。ただし、主キーやユニークキーを使っていたからといって、範囲検索で指定する場合には const にはならない。また、該当する行がない場合、クエリ自体が実行されないため const にはならない |
| system | テーブルに 1 行しか行がない場合の特殊なアクセスタイプ |
| ALL | 全件スキャン。テーブルのデータ部分が全てアクセスされる |
| index | インデックススキャン。テーブルの特定のインデックスの全てのエントリがアクセスされる |
| eq_ref | JOIN の内部表へのアクセスにおいて、主キーあるいはユニークキーによるルックアップが行われる。const に似ているが、JOIN の内部表へのアクセスという点が異なる |
| ref | ユニークキーでないインデックスに対する等価比較。複数行がヒットする可能性がある。最外部のテーブルであろうと JOIN の内部表であろうと同じアクセスタイプとなる。 |
| refornull | ref に加えて、そのインデックスのアクセスの先頭に格納されている NULL のエントリをスキャンする。検索条件として col = ‘val’ OR col IS NULL のような式が含まれている場合に使用される |
| range | インデックスの特定の範囲の行へアクセスする |
| fulltext | フルテキストインデックスを使った検索 |
| index_merge | 複数のインデックスを使って行を取得し、その結果を統合する |
| unique_subquery | IN サブクエリへのアクセスにおいて主キーあるいはユニークキーが使われる。このアクセスタイプは余計なオーバーヘッドが省かれており、相当速い |
| index_subquery | unique_subquery とほとんど同じだが、ユニークで無いインデックスを使ったアクセスが行われる点が異なる。このアクセスタイプも相当速い |
これらのアクセスタイプの中で注意が必要なのは、ALL 、index 及び ref_or_null となる。前者 2 つはテーブルあるいは特定のインデックスが全件アクセスされるため、テーブルのサイズが大きい場合は効率が悪い。ref_or_null に関しては、NULL となっている行はインデックスの先頭にまとめて格納されるが、その件数が多いと MySQL サーバがこなす仕事が多くなってしまう。
なお、ALL 以外のアクセスタイプは、すべてインデックスを使ったものとなる。アクセスタイプが ALL あるいは index の場合は、そのクエリで使用できる適切なインデックスが存在しない兆候かもしれない。
possible_keys
利用できる可能性のあるインデックスの一覧を表示する。
key
possible_keys のうち、実際にオプティマイザが選択したものが表示される。
key_len
選択されたインデックスの長さとなる。インデックスが長すぎるのも非効率となるので、たまには気にする。
ref
検索条件で key と比較されている値やカラムの種類。定数の場合 const となり、JOIN を使用している場合、結合する相手側のテーブルで検索条件として利用されているカラムが表示される
rows
このアクセスタイプによりどれだけの行が取得されるかを示す。最初にアクセスされるテーブルについては、クエリ全体によってアクセスされる行数、それ以降のテーブルについては、1 行の JOIN ごとに平均で何行のアクセスが発生するかを示している。あくまで統計値から計算された概算値なので、必ずしも実際の行数とは一致しない
filtered
行データが取得されてからさらに WHERE 句の検索条件が適用されたときに、どれだけの行が残るかを示すもの。こちらも統計からの概算値となる。MySQL 5.7 のクエリ例では、オプティマイザは City テーブルにアクセスしたあと、WHERE 句の条件をさらに適用しても 5 % 程度しか残らないと判断している
Extra
オプティマイザが与えてくれる動作のヒント。
以下に Extra の値とその説明を示す。
| アクセスタイプ | 説明 |
|---|---|
| Using where | アクセスタイプで説明した方法でテーブルから行を取得したあと、さらに検索条件を適用して行の絞り込みを行うことを示す |
| Using index | インデックス部のみにアクセスすることでクエリが解決されることをしめす。カヴァリングインデックスになっている。別名、インデックスオンリースキャン |
| Using index for group-by | GROUP BY が含まれたクエリをカヴァリングインデックスで解決できることを示す |
| Using filesort | ORDER BY をインデックスにより解決できず、ファイルソート (MySQL のクイックソート実装) によって行のソートを行うことを示す |
| Using temporary | 暗黙的にテンポラリテーブルが作成されることを示す |
| Using where with pushed condition | エンジンコンディションプッシュダウン最適化が行われたことを示す。現在は NDB でのみ有効 |
| Using index condition | インデックスコンディションプッシュダウン (ICP) 最適化が行われたことを示す。ICP とはマルチカラムインデックスにおいて、左端から順番にカラムを指定しない場合でもインデックスを有効利用する実行計画のこと |
| Using MRR | マルチレンジリード (MRR) 最適化が用いられることを示す |
| Using join buffer (Block Nested Loop) | JOIN に適切なインデックスがなく、JOIN バッファが利用されたことを示す |
| Using join buffer (Batched Key Access) | バッチトキーアクセス JOIN (BKAJ) アルゴリズムのために JOIN バッファが使用されることを示す |
| Using sort_union(…), Using union(…), Using intersect(…) | インデックスマージによってテーブルへアクセスされることを示す。インデックスマージとは、複数のインデックスを用いてテーブルへのアクセスを行い、取得した行をあとからマージすること |
| Distinct | DISTINCT が指定されており、なおかつ JOIN をするクエリでは、内部表から高々１つのキーを読むだけでクエリを解決できるものがある |
| Range checked for each record (index map: N) | JOIN において内部表へアクセスするのに最適なインデックスがない場合、駆動表から取得した行の値に基づいて、適切なインデックスがあるかどうかを確認する |
| Not exists | LEFT JOIN において、内部表にマッチする行がないものを検索するために、joined_table.key IS NULL の指定があった場合、内部表にマッチする行があれば田立にそのキーに対する検索をやめても構わない |
| Full scan on NULL key | IN サブクエリが select list (SELECT の結果として返されるカラム) に現れている場合、かつ IN 句のキーが NULL になる可能性がある場合、インデックスによる検索の代わりにフルスキャンをする必要がある |
| const row not found | テーブルが空であることを示す |
| no matching row in const table | 主キーあるいはユニークキーによるルックアップにおいて、該当する行がなかったことを示す。該当する行がある場合、そのクエリのアクセスタイプは const になる |
| FirstMatch(tbl_name) | semijoin 最適化において、FirstMatch アルゴリズムが採用されたことを示す。 |
| LooseScan(m..n) | semijoin 最適化において、LooseScan アルゴリズムが採用されたことを示す |
| Start temporary, End temporary | semijoin 最適化において、テンポラリテーブルによる重複排除のアルゴリズムが採用されたことを示す |
| Imposssible HAVING | HAVING 句の条件が常に偽になってしまうことを示す |
| Impossible WHERE | WHERE 句の条件が常に偽になってしまうことを示す |
| Impossible WHERE noticed after reading const tables | アクセスタイプが const になるテーブルから行を読み込んだ結果、WHERE 句の条件が偽になることが判明した |
| No matching min/max row | MIN/MAX を取得するクエリにおいて、WHERE 句の条件にマッチする行がないことが明確な場合に表示される |
| No matching rows after partition pruning | パーティションの刈り込みを行なったあとで、検索条件にマッチする行がない場合に表示される |
| No talbes used | DUAL テーブルへのアクセスか、FROM 句が省略された |
| Plan isn’t ready yet | EXPLAIN FOR CONNECTION において、まだオプティマイザによる実行計画の作成が終わっていない |
| Select tables optimized away | MIN/MAX を取得するクエリにおいて、インデックスから 1 行だけ読めば良い場合に表示される |
| unique row not found | 駆動表のアクセスタイプが const のとき、かつ内部表がユニークなインデックスで結合されるとき、内部表に該当する行がない場合に表示される |
| Zero limit | LIMIT 0 が指定されたため、結果セットを返す必要がない |