SQL実行計画の疑問解決には「とりあえずEXPLAIN」しよう

EXPLAINステートメントとは

EXPLAINは、SQLの実行計画に関する情報を取得するためのステートメントです。実行計画とは「どのインデックスを使って（あるいはインデックスを使わずにテーブルスキャンで）クエリーを処理するか」をMySQLが判断した結果のことです。「インデックスはちゃんと使われているだろうか」「インデックスでどこまでクエリーを効率的に処理できているだろうか」という疑問が湧いた時には、「とりあえずEXPLAINで」となりますよね。

EXPLAINのマニュアルはこちらに、EXPLAIN の出力結果のカラムの意味についてはこちらに記載があります。

EXPLAINの何を見るか

たとえば、次のような重いクエリーがあったとしましょう。

mysql> SELECT COUNT(some_column) FROM some_table WHERE some_column = xxx;
+--------------------+
| COUNT(some_column) |
+--------------------+
|            4791213 |
+--------------------+
1 row in set (1.72 sec)

このような場合は、「とりあえず EXPLAIN 」を見てみましょう。

mysql> EXPLAIN SELECT COUNT(some_column) FROM some_table WHERE some_column = 'xxx';
+----+-------------+------------+------+--------------------------+---------------+---------+-------+---------+--------------------------+
| id | select_type | table      | type | possible_keys            | key           | key_len | ref   | rows    | Extra                    |
+----+-------------+------------+------+--------------------------+---------------+---------+-------+---------+--------------------------+
|  1 | SIMPLE      | some_table | ref  | some_index,another_index | another_index | 34      | const | 9026812 | Using where; Using index |
+----+-------------+------------+------+--------------------------+---------------+---------+-------+---------+--------------------------+
1 row in set (0.00 sec)

EXPLAINはMySQLのオプティマイザーがどの実行計画を選んだかを表示させるステートメントです。possible_keysからは「MySQLはこのクエリーに対してsome_indexまたはanother_indexが使えると判断した」、keyと key_lenからは「実際に使ったのはanother_indexで利用したのキー長さは34バイト」、rowsからは「実行計画上ではこのクエリーは9026812（約900万）行を検査する」、Extra: Using indexからは「テーブルそのものからデータを読み取らず、インデックスだけから読み取るデータで完結する」といったことがEXPLAINの出力結果から読み取れます。

しかし、another_indexが本当にこのクエリーにとって最良の選択なのかはEXPLAINから読み取ることはできません。ひょっとしたらsome_indexの方が速い可能性は十分あります。あるいはpossible_keysに表示されていない（MySQLが「利用できない」と判断した）インデックスの方が効率が良い可能性もないわけではありません（体感ではほぼないが実際にそのようなケースも存在する）。

また、実行計画上は約900万行を検査することになっていますが、「実際に何行検査したのか」は読み取れません。統計情報と実際のデータの分布に乖離がある場合、この値もまた乖離することになります。最後に、これは「EXPLAINを実行した時点で実行計画がこのように選択された」という情報であり、今後統計情報の変化により変更される可能性があります。

EXPLAINの結果を踏まえて確認すること

それでは、先に挙げた懸念を1つずつ確認していきます。

another_indexは本当に最適なインデックスなのか

泥臭い方法ですが、実際に実行してみるのが一番です。USE INDEXおよびIGNORE INDEX構文を使うことで、そのクエリーに利用するインデックスを指定できます。

mysql> EXPLAIN SELECT COUNT(some_column) FROM some_table USE INDEX(some_index) WHERE some_column = 'xxx';
+----+-------------+------------+------+---------------+---------------+---------+-------+---------+--------------------------+
| id | select_type | table      | type | possible_keys | key           | key_len | ref   | rows    | Extra                    |
+----+-------------+------------+------+---------------+---------------+---------+-------+---------+--------------------------+
|  1 | SIMPLE      | some_table | ref  | some_index    | some_index    | 34      | const | 9158986 | Using where; Using index |
+----+-------------+------------+------+---------------+---------------+---------+-------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(some_column) FROM some_table USE INDEX(some_index) WHERE some_column = xxx;
+--------------------+
| COUNT(some_column) |
+--------------------+
|            4547278 |
+--------------------+
1 row in set (1.89 sec)

USE INDEXはpossible_keysの値を書き換える（上書きする）ためのキーワードです。インデックスはコンマ区切りで複数指定可能で「USE INDEXで指定したインデックスの中から利用するインデックスを決定せよ」というような意味合いになります（通常は指定するインデックスが1つで「このインデックスを利用してクエリーを処理せよ」という意味合いで利用するキーワード）。

IGNORE INDEXはその逆で、MySQLが選んだpossible_keysから特定のインデックスを除外します。「possible_keysの選定はMySQLに任せるが、IGNORE INDEXで指定したインデックスは利用してはいけない」というような意味合いになります（よってテーブル上の全てのインデックスをIGNORE INDEXに指定すると強制的にテーブルスキャンにできる）。possible_keysが3つ以上ある場合で、MySQLが次にどのインデックスを選ぼうとするのかを知りたい時などに利用できます。

上記の例ではUSE INDEX(some_index)でsome_indexを選ばせた場合に1割程度遅くなったということで、「MySQLの選んだanother_indexは正しかった」ということが判りました。実際に実行して試してみる場合、（元のクエリーも試してみるクエリーも）必ずクエリーを複数回実行することを忘れないでください。

クエリーの実行速度はバッファプールに大きく依存します。多くの場合、1回目のクエリーはバッファプールが温まっておらず非常に低速です。通常のトラフィックで多く利用されているインデックスは（それが正しいものであれ間違っているものであれ）どの時間帯でもバッファプールに多く乗っている可能性が高く、1回目のクエリー同士を比較してしまうと結果はバッファプールの偏りに依存することがあるからです。

クエリーは本当に900万行も検査しているのか

そのクエリーがどれだけ行を読み込んだのかはHandler_%ステータス変数で確認できます。ステータス変数はSHOW STATUSステートメントで参照できるMySQL内部のステータスです（多くはカウンター）。ステータス変数の多くは「セッションスコープ（接続中のスレッドのみのステータス変数）」と「グローバルスコープ（MySQLの起動から現在まで全てのステータス変数の累計）」があり、それぞれSHOW SESSION STATUS,SHOW GLOBAL STATUSステートメントで表示できます（SESSIONまたはGLOBALキーワードを省略した場合はセッションスコープの値が出力される）。

ステータス変数には多くの種類がありますが、ここではHandler_で始まるステータス変数を確認しましょう。

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(some_column) FROM some_table WHERE some_column = xxx;
+--------------------+
| COUNT(some_column) |
+--------------------+
|            4791213 |
+--------------------+
1 row in set (1.72 sec)

mysql> SHOW SESSION STATUS LIKE 'Handler\_%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 1       |
| Handler_delete             | 0       |
| Handler_discover           | 0       |
| Handler_external_lock      | 2       |
| Handler_mrr_init           | 0       |
| Handler_prepare            | 0       |
| Handler_read_first         | 0       |
| Handler_read_key           | 1       |
| Handler_read_last          | 0       |
| Handler_read_next          | 4791438 |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_next      | 0       |
| Handler_rollback           | 0       |
| Handler_savepoint          | 0       |
| Handler_savepoint_rollback | 0       |
| Handler_update             | 0       |
| Handler_write              | 0       |
+----------------------------+---------+
18 rows in set (0.00 sec)

FLUSH STATUSは「セッションスコープのステータス変数をクリアする（クリアされないものもある）」ステートメントです。まれに「グローバルスコープでステータス変数をクリアする」と誤解されているケースがありますが、セッションスコープ限定です。余計な値が混じらないようにSHOW SESSION STATUSの前に実行してみました。各ステータス変数の詳細はリファレンスマニュアルに説明を譲りますが、ここからはインデックスを利用した行の読み取りを意味するHandler_read_nextがおよそ48万回コールされていることが分かります。統計情報をもとにオプティマイザーが「行の検査が必要」と判断した行数は90万行なので、見積もりと実際の間には40万行の乖離があったことになります。

このような事態はなぜ発生するのでしょうか。端的には統計情報が「サンプリング値」をもとに作成されることが原因です。オプティマイザーは統計情報をもとに実行計画を計算しますので、入力値となる統計情報（＝サンプリング値）が間違っている（実際のデータの分布と著しく乖離している）場合、出力である実行計画もまた間違っているものとなる可能性があります。「統計情報が間違っている」といえばANALYZE TABLEステートメントです。とりあえず実行してみましょう。

mysql> ANALYZE TABLE some_table;
+------------------------+---------+----------+----------+
| Table                  | Op      | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| some_schema.some_table | analyze | status   | OK       |
+------------------------+---------+----------+----------+
1 row in set (0.36 sec)

mysql> EXPLAIN SELECT COUNT(some_column) FROM some_table WHERE some_column = 'xxx';
+----+-------------+------------+------+--------------------------+---------------+---------+-------+---------+--------------------------+
| id | select_type | table      | type | possible_keys            | key           | key_len | ref   | rows    | Extra                    |
+----+-------------+------------+------+--------------------------+---------------+---------+-------+---------+--------------------------+
|  1 | SIMPLE      | some_table | ref  | some_index,another_index | another_index | 34      | const | 9270576 | Using where; Using index |
+----+-------------+------------+------+--------------------------+---------------+---------+-------+---------+--------------------------+
1 row in set (0.00 sec)

残念ながら、結果は大して変わりませんでした。この理由には2つ考えられます。

1. InnoDBはデフォルトの設定で「前回の統計情報の更新から累計してテーブル全体の10%以上（MySQL 5.5とそれ以前は6.25%）が更新された場合、バックグラウンド（非同期）で統計情報を再作成する」ようになっている
2. InnoDBのサンプリングの設定はMySQL 5.5とそれ以前では1インデックスあたり8ページ（ハードコード）、MySQL 5.6では1インデックスあたり20ページ（設定可能）。InnoDBページのデフォルトは16KBなので1つのインデックスのサイズが数十GB、100GBを超えたとしてもデフォルトのままでは128KB～320KB程度しかサンプルを取らない。これはインデックスのサイズがせいぜい数MBであれば十分な精度だが、サイズが大きくなるに従って精度が悪く（＝統計情報が間違いやすく）なる。

まず 1. の通り、トラフィックの流れている環境であればバックグラウンドで統計情報の再作成が頻繁に行われているため、強制的に統計情報を再作成するANALYZE TABLEを実行しても大きな変化はなかったと考えられます。閾値にギリギリで届かないような更新量でない限りはANALYZE TABLEの実行によって大きく実行計画が変わることはまれです（ただしInnoDBに限る。MyISAMの場合はバックグラウンドで統計情報を再作成する機能は存在しないため、ANALYZE TABLEを実行するまで統計情報は古いまま）。

そして 2. の通り、インデックスのサイズが大きくなれば大きくなるほどInnoDBの統計情報の誤差は大きくなります。MySQL 5.6とそれ以降では、1インデックスあたりのサンプリングページ数をinnodb_stats_persistent_sample_pagesオプションで指定、またはCREATE TABLEやALTER TABLEでテーブルごとに指定できるようになったためこれを大きくすることも手ですが、サンプリングのページ数が増えれば統計情報の再作成処理も重くなりますので、最適な値を見つける必要があるでしょう。

Handler_%ステータス変数とEXPLAINのrowsが大きく乖離している場合、「ベースとなる統計情報が間違っているためオプティマイザーが導き出した実行計画もまた間違っている」可能性があることを考慮してください（ただし、オプティマイザーの精度が上がっても同じインデックスを利用している限りクエリーそのものの速度は向上しないことに注意）。

そのExtraは本当に望ましいのか

EXPLAINの見方を説明している時によく聞かれる質問として「Extraに"Using index"（ほかにも"Using intersect"や"Using temporary"など）が出ていますがこれは直した方が良いですか？」というものがあります。正直これはケースバイケースで、全てのケースを説明するわけにはいきません。まずはマニュアルのEXPLAINの追加情報を参照してください。意味的なものはここにほぼ網羅されています。Extra列の出力しうる表示はたくさんありますが、ここでは比較的よく目にする3つに絞って説明したいと思います。

Using filesort

"Using filesort"は、行のフェッチと評価のあとに追加でクイックソートが発生していることを示します。この時のクエリーの処理シーケンスは以下のようになっています。

while (1行読む)
{
  /* 行を読み込み、評価し、条件にマッチしたものをソートバッファに詰める */
}

ソート処理;

MySQLのソートは（filesortと出力されているが、必ずしもテンポラリーファイルを使用するとは限らない）クイックソートです。クイックソートの平均計算時間が示す通り、ソート処理はソート対象の行が多くなれば多くなるほど（線形以上に）遅くなっていきます。また、インデックスを利用したソートの無効化（インデックスが既にソート済みの状態で並べられているため、追加のソートが必要ない状態）はLIMIT句での最適化が効きますが、クイックソートが実行される場合にはこの最適化が効きません。WHERE句で絞り込んだ結果が十分小さい場合はこれが出力されても特に問題にはならないでしょう。絞り込んだ結果がどんどん大きくなる（例えばユーザーコンテンツなどは時間経過とともにどんどん増えていくのが常）場合は注意が必要です。

Using index

MySQLのインデックスはほぼB+Treeです。MySQLのB+Treeインデックスのリーフには「テーブル内での行の位置」が記録されています（MyISAMであれば.MYDファイルの先頭からオフセットバイト数、InnoDBであればクラスターインデックスの値が記録されている）。そのため、インデックスを利用した行フェッチを行う際は、以下の3ステップで行われます。

1. インデックス上から条件にマッチするリーフを探す
2. インデックスのリーフ上に書かれた情報から行の位置を探す
3. 行をフェッチする

Using indexが示すのはインデックス上に書かれた情報だけで（インデックスは「ソート済みのデータの複製（サブセット）」でありインデックスを作成したカラムの値を含む）、要求された情報の取り出しが終了したため 2. と 3. のステップを省略した、というものです。大概の場合悪い意味ではありませんが、「100バイトのインデックスを100リーフ読んでUsing index」と「4バイトのインデックスを10リーフ読んで各10バイトの行を10行フェッチ」というケースもあります（さすがにここまで極端な例はないと思うが、必ずしも最良を表すものではないということで）。

Using temporary

"Using temporary"はソートのために暗黙の（CREATE TEMPORARY TABLEステートメントで作成するテンポラリーテーブルに対して「暗黙の」としている）テンポラリーテーブルを利用していることを示します。単にインデックスがないカラムでソート、インデックスがあっても関数や演算子を利用した結果でのソートはクイックソートで済みますが、集計関数を利用した結果でのソートは暗黙のテンポラリーテーブルが必要になります（テンポラリーテーブルを作成した後にクイックソート）。

また、MySQLでは昇順（ASC）と降順（DESC）が混じったソートは暗黙のテンポラリーテーブルを使用することも有名です。暗黙のテンポラリーテーブルは一定のサイズ（max_heap_table_size,tmp_table_sizezのいずれか小さい方）を超えるとMyISAM（5.7ではデフォルトでInnoDB）としてストレージ上に固定化されるため、ソート以上にWHERE句での絞り込み後の結果サイズによって性能が劣化します。

EXPLAINの変更点

EXPLAINはずっと昔からあり利用用途も変わっていませんが、MySQL 5.6と5.7でそれぞれ少しずつ機能が追加がされています。

MySQL 5.6での変更

• DELETE,INSERT,REPLACE,UPDATE ステートメントがEXPLAINできるようになった。5.5とそれ以前のバージョンではSELECTのみEXPLAINにかけることができた。
• EXPLAIN format=json ..がサポートされた。 format=jsonキーワードを指定すると出力が少し増えたものがJSON形式で返ってくるようになる。またEXTENDEDキーワードを指定した時と同じように最適化後のSQLをワーニングバッファに格納する。

MySQL 5.7での変更

• EXTENDEDキーワードとPARTITIONSキーワードがデフォルトで指定された状態になった。MySQL 5.6とそれ以前ではこれらは同時に指定できなかった。
• EXPLAIN FOR CONNECTION n構文がサポートされた。 "n" にSHOW PROCESSLISTで出力される "Id" を指定することで、現在実行中のステートメントを（コピー＆ペーストすることなく）EXPLAINできる。

まとめ

EXPLAINはSQLの実行計画に関する情報を取得するためのステートメントです。EXPLAINの結果は「その時点で実行計画がこのように選択された」という情報であり、統計情報の変化により変更される可能性があります。InnoDBの統計情報はサンプリングのため、テーブルが大きくなると統計情報と実際の値の分布が異なってくる傾向があります。利用されているインデックスが最適なものかどうかは、USE INDEX,IGNORE INDEX句を利用して実際に比べてみるのが一番です。

一概には言えませんが、"Using filesort", "Using temporary"はWHERE句で絞り込んだ後の結果セットが大きくなるほど遅くなっていくので注意しましょう。