目次
MySQL EXPLAINとは
EXPLAINとは、MySQLがどのような実行計画でクエリ実行するかを表示するコマンド。EXPLAINの結果を見ることで効率の悪いクエリを見つけたり、改善結果を確認できる。
EXPLAIN (
SELECT f.film_id, f.title, f.release_year, a.first_name, a.last_name
FROM film f
INNER JOIN film_actor fa ON fa.film_id = f.film_id
INNER JOIN actor a ON a.actor_id = fa.actor_id
);
JOIN時のEXPLAINの見方
JOINする場合は、駆動表が一番最初に表示される。
先程の例だと、actorテーブルが駆動表になっているということになる。
駆動表を固定したい場合は、MySQL 8.
試しに先程の例をfilmテーブルを駆動表になるようにしていしてみると、
JOIN_ORDER(先に来るテーブル, 後に来るテーブル), JOIN_PREFIX(先に来るテーブル), JOIN_SUFFIX(後に来るテーブル) というように指定できる。
EXPLAINの各項目解説
id, select_type
この2つの項目は、クエリの種類が次の3つの場合によって異なる。
- JOIN
- UNION
- サブクエリ
今回は、一番頻度が高いのはJOINだと思うので、JOINの場合について書く。
実行するクエリがJOINだけの場合、テーブルがいくつあってもidは1つでselect_typeは常にSIMPLE。
冒頭に例で上げたEXPLAINでも、どのテーブルも「id: 1」で全て「select_type: SIMPLE」になっている。
これは、複数テーブルであっても1回の処理でデータを取得する実行計画であることを意味する。
参考
MySQLのJOIN時のアルゴリズムはNested Loop JOIN（NLJ）のみ。
NLJについては、SQL結合のアルゴリズムNested Loopsとは？で解説しています。
JOINの場合は単純なため、あまり意識しなくても大丈夫。サブクエリとかUNIONが絡んでくるときに複雑になる。
table
アクセスされるテーブル名。
type
「Record Access Type（レコードアクセスタイプ）」と言われる。
MySQLがどのようにテーブルへアクセスするかを教えてくれる項目。値は次の表の通り。
| ALL | インデックスを用いない、テーブルスキャン。このタイプが出たら要注意。改善の余地あり。 |
| const | PRIMARY KEY やUNIQUE KEY による等価比較が行われたときのタイプ。これらのキーには重複したレコードが無いため、キーとリテラル（具体的な値）の条件でクエリ実行されると結果は0行か1行のみになる。この場合、オプティマイザは検索結果を定数(const)のようにみなす。 |
| index | フルインデックススキャン。良さげな名前だけど、該当のインデックスをスキャンする重い処理。ORDER BY + LIMIT で行数を絞り込んでいる場合は、先頭の数エントリを読み込むだけで済むので問題にはならない。 |
| eq_ref | JOINするときにPRIMARY KEYやUNIQUE KEYが使われることを表す。constに似ているが、JOINの内部表へのアクセスで用いられる点が異なる。 |
| ref | PRIMARY KEY、UNIQUE KEYでないインデックス（ユニークでないインデックス）を使って等価検索(WHERE key = value)を行うことを表す。JOINでも単一テーブルのSELECTでもインデックスがユニークでなければ、refになる。 |
| ref_or_null | PRIMARY, UNIQUE でないインデックスを使って等価検索(WHERE key = value)を行い、かつOR条件で同じインデックスに対してIS NULLが指定されているときに使われるタイプ。 |
| range | インデックスを用いた範囲検索（不等号やBETWEEN）。検索範囲が大きくても小さくてもrangeとなるので注意が必要。 |
| fulltext | フルテキストインデックスを利用した場合のタイプ。 |
| index_merge | 2種類のインデックスを使用し、フェッチした行が統合されるときに利用されるタイプ。 |
| unique_subquery | DEPENDENT SUBGUERY においてPRIMARY KEY、UNIQUE KEYのインデックスを用いてサブクエリが評価されるタイプ。とても高速。 |
| index_subquery | DEPENDENT SUBGUERY においてユニークではないインデックスを用いてサブクエリが評価されるタイプ。そこそこ高速。 |
possible_keys, key, key_len
possible_keysはオプティマイザが利用可能なインデックスの候補として挙げたキー一覧。
keyは、実際にオプティマイザに選択されたキー。どのインデックスが使われるかはkeyを見ればわかる。
key_lenは、選択されたキーの長さ。
ref
検索条件で、keyと比較されている値やカラムが表示される。
リテラル（定数）が指定されている場合は、constと表示され、JOINしている場合は、結合する相手側のテーブルで参照されるカラムが表示される。
冒頭のEXPLAIN例では、filmテーブルがfilm_actorテーブルのfilm_idカラムを参照してJOINしていることを意味する。
rows
テーブルからフェッチされる行数の見積もり。（推定値）
この項目はあくまでも見積もりなので、実際にフェッチされる正確な行数ではない。また、フェッチされた全ての行が結果として返されるわけでもない。
Extraに「Using where」が表示されている場合は、フェッチした行に対してさらにWHERE句で絞り込んだものが結果として返される。
Extra
クエリ実行するためにオプティマイザがどんな戦略を選択したかを示す。
Using Where
テーブルから行をフェッチしたあとにWHERE句の条件で絞り込んでいることを意味する。
インデックスを使っていて、適切な行数まで絞り込めているなら、Using whereが表示されていても気にすることはないが、次の場合には注意・改善が必要。
- レコードアクセスタイプ（type項目）がテーブルスキャン（ALL）または、インデックススキャン（index）
- インデックスを使っているがフェッチする行数（rows）がとても多く、その大半がWHERE句で絞り込まれる（はじかれる）
- JOIN時に内部表でUsing whereが表示されている
Using index
クエリが1つのインデックスにアクセスするだけで解決することを意味しています。このようなクエリは行データにアクセスする必要がないので高速。
レコードアクセスタイプの「index」と違って、Extraの「Using index」はとても良い意味。ただし、レコードアクセスタイプが「index」の場合も、Extraには「Using index」が表示されるので注意！！
Using filesort
MySQLのソートには2通りあって、
1つはインデックスの順番で行をフェッチする方法。この方法は高速。
もう1つは、行をフェッチしてから並び替える方法で、この行のソートアルゴリズムをfilesortと呼ぶ。ファイルソートの中身はクイックソートですが、テンポラリファイルとメモリ上のバッファをりようするためこのような名前になっている。
ファイルソートは常に処理が遅いわけではなく、行数が少なければ十分に高速なので、Using filesortが表示されたときに注意しないといけないのは、ソートされる行数。ソートする行数が多い場合は、インデックスを追加して、クエリがインデックスの順番でソートできるようにすると良い。
このUsing filesortは、JOINを行うときに注意が必要。MySQLがJOIN時にソートを実行するには、次の3つの方法のみ。
※ 上から効率の良い順。
- 駆動表をインデックスの順番で行をフェッチしてソートされた状態でJOINする。この場合は、「Using filesort」は表示されない。
- 駆動表に対してファイルソートして、ソートされた状態でJOINする。この場合は、駆動表において「Using filesort」が表示される。
- JOINを全て実行した後にファイルソートを行う。駆動表において「Using temporary; Using filesort」が表示される。
内部表をJOINするときには、インデックス順に行をフェッチするのは不可能なので、JOINを終えたあとにファイルソートするしかない。
これらの方法は、上から効率の良い順になっているので、ソートする行数が多い場合には、3番目より2番目、2番目より1番目のソートになるようにクエリやインデックスをチューニングした方が良い。
ポイント
- 1, 2番目の方法になるようにするには、ソートするカラムがあるテーブルを駆動表になるようにする
- 複数のテーブルのカラムでソートする場合は、3番目の方法を取らざるを得ないので、仕様やテーブル設計を見直すか、MySQL以外の方法でなんとかするか
Using temporary
クエリ実行にテンポラリテーブルが必要なことを意味する。
MySQLがクエリ実行時に内部的にテンポラリテーブルを利用するのは次の場合。
- JOINの結果をソートする場合
- ORDER BY と DISTINCTを併用した場合
- 集計関数を使う場合
- UNION
- DERIVED
これらの内、DERIVEDやUNIONでは「Using temporary」は表示されない。
なので、「Using temporary」が表示されるのは上記のJOIN時のソートでテンポラリテーブルが必要な場合、集計関数を使う場合、ORDER BYとDISTINCTを併用した場合の3つ。
まだまだありますが、必要に駆られたら情報を付け足していきます。
