前の記事では、ひとつのテーブルに対する様々なクエリを対象にして、インデックスのデザイン方法について議論しました。この記事ではより実世界に即した問題解決の例として、よく似ているにも関わらず、ひとつは適したインデックスを使い、もうひとつはフルテーブルスキャンをしてしまうという、2つのクエリを取り上げます。動作の違いはバグなのでしょうか？それとも想定された動きなのでしょうか？続きを読んでみてください！
対象のクエリ2つ
# Q1
mysql> explain select col1, col2 from t where ts >= '2015-04-30 00:00:00';
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
| 1 | SIMPLE | t | ALL | ts | NULL | NULL | NULL | 4111896 | Using where |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+-------------+
# Q2
mysql> explain select count(*) from t where ts >='2015-04-30 00:00:00';
+----+-------------+-------+-------+---------------+--------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+--------------------------+
| 1 | SIMPLE | t | range | ts | ts | 5 | NULL | 1809458 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+--------------------------+
Q1はフルテーブルスキャンしてしまっていますが、Q2は、Extra列にUsing indexがある通り、カバリングインデックスであるtsを使っています。なぜこんなに実行計画が違ってしまうのでしょうか？
Q1に何が起こっているのかをひも解いていきましょう。
このクエリは、ts列に対する不等号がひとつあり、しかもts列にインデックスが作られています。オプティマイザはこのインデックスが使えるかどうかは試しているようで(possible_keys列に表示がある)、これは論理的には全く正しい動きです。ここでQ1とQ2の実行計画のrows列を見てみると、インデックスを使うことでレコード全体の45%しか読まなくてよくなることが分かります(410万行中180万行)。確かにこれはとてもいい結果だとは言えませんが、フルテーブルスキャンよりはマシだと言えるのでは？
もしそう思ったのなら、注意してもう少し先まで読みましょう。その思い込みは、間違いだということです！
実行計画のコスト見積もり(単純化して説明)
まず最初に、オプティマイザはデータやインデックスがメモリ上にあるのかディスクから読み出さねばならないのかは分かりません。そのため、全てディスク上から読み出す必要があると仮定して動作します。オプティマイザが分かっているのは、ランダムリードよりシーケンシャルリードの方が断然速いということです。
ここで、Q1でtsに対するインデックスを使って実行することを考えてみましょう。ステップ1で、インデックスを範囲スキャンして条件に合致する180万行のレコードを割り出します。これは(インデックスを順番に読むので)シーケンシャルリードになるため、比較的高速と言えます。一方ステップ2では、条件に一致した各レコードからcol1とcol2列を取り出します。(InnoDBでのセカンダリ)インデックスは、一致するレコードに対するプライマリキーの値を提供するだけなので、各レコードに対するプライマリキー値の検索を実施する必要があるのです。
ここで問題があります。180万行のプライマリキーの検索は180万回のランダムリードに等しいので、その処理には時間がかかります。全テーブルのシーケンシャルリード(InnoDBなのでプライマリキーのフルスキャンと同じ意味)よりもずっと長い時間がかかってしまうのです。
一方、tsに対するインデックスを使ってQ2がどのように実行されるかを見てみましょう。ステップ1は全く同じで、合致する180万件のレコードを割り出します。しかし、ステップ2は存在しないのです！これが、「カバリングインデックス」という所以です。他の列のデータを持ってくるために、プライマリキーの指し示すデータを引く処理を実行する必要がないのです。従って、テーブル全体を読み込むより、tsに対するインデックスを使う方がずっと効率的だということになります。
ここで、もうひとつ理解しておくべきことがあります。論理的な観点からすると、フルテーブルスキャンはシーケンシャルな処理です。しかし、InnoDBのページは、ディスク上では完全にシーケンシャルに並んでいるとは限りません。従って、ディスクのレベルでは、フルテーブルスキャンは、1回の大きなシーケンシャルリードというよりは、複数回のランダムリードということになってしまいます。
とは言え、ポインタをたどるクエリを多数発行するよりはずっと高速な処理なのは確実です。それは、フルテーブルスキャンの場合、16KBのページを読み取るとそのページ内のデータは全て使われる一方で、ランダムリードの場合、16KBのページを読み取るとその中の1つのレコード分しか使わないという違いからも明らかです。つまり、最悪の場合、フルテーブルスキャンでは400万レコードを読むのに10万回の(ディスク上の)ランダムリードしか必要ない一方で、(MySQL上の)ランダムリードの場合は180万件のデータを読むのに180万回の(ディスク上の)ランダムリードが発生してしまう可能性があるのです。つまり、フルテーブルスキャンは、桁違いに速いということです。
このクエリの最適化法
これで、Q1に対してオプティマイザがフルテーブルスキャンすることを選択する理由が理解できるでしょう。インデックスを使うようにしてさらに高速にすることはできるでしょうか？カバリングインデックスを使えれば、重い処理であるプライマリキーの検索が省けます。そうすれば、オプティマイザはフルテーブルスキャンではなくインデックスの使用を選択する可能性が高くなります。従って、以下のようなカバリングインデックスを作りましょう。
ALTER TABLE t ADD INDEX idx_ts_col1_col2 (ts, col1, col2);
ts列には不等号が使われているので、それ以外の列にインデックスが使えないはずじゃないかという人がいるかもしれません。これは、WHERE句内にcol1やcol2に対する条件がある場合には正しいのですが、ここで挙げたクエリではそうはなってはおらず、カバリングインデックスを狙うためだけに列を追加しただけです。
まとめ
例え簡単なクエリだとしても、クエリの最適化のためにインデックスがフィルタやソーティングをどのように行うか、カバリングインデックスがどのように動くかを理解するのが最も重要なことです。また、実行計画に対してクエリがどのように実行されるのか(おおまかにでも)理解しておくのも非常に有意義でしょう。これらのことを知らないと、オプティマイザが下した判断がなぜなのか分からなくなってしまうことがあるはずです。
MySQL 5.7以降では、コストモデルがチューニングされていることにも触れておきましょう。これにより、例えば高速なストレージを使用している場合はランダムリードのコストが通常ディスクより非常に小さいことなどを考慮できるようになるので、オプティマイザはよりよい判断を下せるようになるはずです。
