はじめに
この記事では、MySQLのEXPLAINステートメントの実行結果の見方について解説します。
EXPLAINを使いこなすことによって、クエリのパフォーマンス改善につながることがあります。
しかし実行結果に表示される項目は数が多く、それぞれ何を示しているか分かりにくいと感じるかもしれません。
慣れるまでは、この記事やEXPLAINの実行結果早見表などを都度確認しながら読み解くことをおすすめします。
EXPLAINとは
EXPLAINとは、DBMSがどのようにクエリを処理するか実行計画を表示するためのステートメントです。
実行したいクエリの先頭に、EXPLAINをつけて実行するだけです。
このクエリを実行して大丈夫かな？と思った時は、とりあえずEXPLAINを実行して問題ないか確認するといいでしょう。
EXPLAINは実際にクエリは実行されないのでご安心ください。
例えば、以下のようにして実行します。
EXPLAIN SELECT * FROM users;
すると次のような結果が表示されます。
それぞれの列が何を示し、その値がどうなっていたら良いのかというのは都度調べなければわからないですね..
それぞれの列が何を示し、表示される値の例を解説していきます。
特にtype, key, Extraはパフォーマンスチューニングで役に立つので、要チェックです。
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | users | ALL | NULL | NULL | 100 | 100.00 |
EXPLAINの結果を解説
id
SELECTの実行順序を示しています
select_type
その名の通り、SELECTのタイプです。
例えば、以下のような表示がされます。
- SIMPLE
- UNIONやサブクエリをしない単純なSELECT
- SUBQUERY
- サブクエリ内の一番最初のSELECT
table
そのクエリがアクセスしようとしているテーブルです。
type
typeとは、スキャン方法などクエリがどの様にしてテーブルにアクセスするかを示します。
基本的にALLやindexとなっていたら注意してください。
特にALLになっていると見直しが必須だと思っていただいて過言ではありません。
代表的な値は以下のとおりです。
- ALL
- インデックスを使わないフルテーブルスキャンを行おうとしています。
- とても遅いので基本的にALLと表示されていたら、クエリの見直しが必要です。
- ただし、レコード数が非常に少ないテーブルに関してはフルテーブルスキャンでもパフォーマンス的に影響は少ないです。
- index
- インデックスフルスキャンを行おうとしています。
- テーブルフルスキャンである
ALLに比べたら高速ですが、該当インデックスを全てスキャンするため遅い処理です - 良さそうに見える名前ですが、見直しが必要です。
- const
- 主キーやユニークキーを用いてアクセスしようとしています。
- 最も処理が高速なため、表示されていたら理想的だといえるでしょう
- eq_ref
- テーブルを結合するときに主キーやユニークキーが使われることを示します。
- JOINするときにこの値になれば理想的であり、constの次に高速です。
- ref
- ユニークではないインデックスで等価検索を行おうとしています。
- eq_refの次に高速です。
- range
- インデックスを使った範囲検索を行おうとしています。
- よく
BETWEENをや<,>=の様な比較演算子を使ったクエリで用いられます。 - refの次の高速です
一般的には左から順に高速なので、覚えておくと良いでしょう。
const, eq_ref, ref, range, index, ALL
possible_keys
候補として使える他のインデックスを出力します。
実際に使われるインデックスはkeyに表示されるので、注意してください。
key
keyとはオプティマイザが選択した、実際に使われるインデックスを示します。
NULLと表示されていたらクエリの見直しが必要です。
インデックス名の他に、下記のような値が表示されることがあります。
- PRIMARY
- インデックスとして主キーを使った検索を行おうとしており、高速です。
- 主キーのインデックスは、一意かつ非NULLの制約を持つため、高速な検索が期待できます。
- NULL
- DBMSが適切なインデックスを見つけられず、インデックスを使わない検索をしようとしています。
- フルテーブルスキャンがされるので、見直しが必要です。
key_len
key_lenとはとは選択されたインデックス（key）の長さをバイト単位で示したものです。
一般的に、この値が小さいほど高速と言われています。
ref
keyと比較されている値やカラムです。
rows
クエリを実行するのに読み取りする必要があるレコード数の見積もりです。
あくまで推定値なので、実際の実行結果とは異なる可能性もあります。
値が小さいほど、その分レコードを読み込む量が少ないため高速になります。
Extra
オプティマイザがクエリ実行でどの様な戦略を採用したかを示します。
付加方法とよく言われますが、クエリの改善に役立つ情報が載っているので重要な項目です。
一般的にUsing filesortやUsing Temporaryと表示されていたら見直しが必要です。
- Using filesort
- ORDER BYでインデックスが利用できず、クイックソートを行おうとしていることを示します。
- 特にレコード数が多い時はパフォーマンスに影響するので見直しが必要です
- そのカラムにインデックスを設定したり、インデックスが適切に設定されているカラムをORDER BYに指定したりすると良いでしょう。
- Using temporary
- クエリを解決するためにテンポラリテーブル（一時テーブル）を使おうとしていることを示します。
- GROUP BYやORDER BY、DISTINCTなど中間結果を保持する時によく見かけます。
- 一時テーブルの作成などに時間がかかり、オーバーヘッドが大きくパフォーマンスに影響を与えるため、見直しが必要です。
- Using Where
- 検索するときにWhere句を使おうとしていることを示します。
- 頻繁に出力されますが、あまり気にする必要はないです。
- Using Index
- 検索する時にインデックスを使用できていることを示します。
- むしろ表示されているとクエリの高速化につながる可能性が高いです。
パフォーマンスを最適化する上で確認したい項目
パフォーマンスを向上させるために特に確認したい項目及びチェックリストです。
- typeがALLまたはindexになっていないか
- keyがNULLになっていないか
- ExtraでUsing filesortやUsing temporaryが表示されていないか
特にtypeがALLになるケースは多いので、必ず確認しましょう！