YakstYakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。2つのクエリの違いとオプティマイザの判断MySQLのインデックスを効果的に使う方法の第2弾。似た2つのクエリなのに、実際にはインデックスの使い方がそれぞれ異なることを通じて、オプティマイザがどのようにクエリの実行計画を立案するのか、そしてその結果どうインデックスが使われるのかを解説する。未報告Tweet前の記事では、ひとつのテーブルに対する様々なクエリを対象にして、インデックスのデザイン方法について議論しました。この記事ではより実世界に即した問題解決の例として、よく似ているにも関わらず、ひとつは適したインデックスを使い、もうひとつはフルテーブルスキャンをしてしまうという、2つのクエリを取り上げます。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に対してオプティマイザがフルテーブルスキャンすることを選択する理由が理解できるでしょう。インデックスを使うようにしてさらに高速にすることはできるでしょうか?カバリングインデックスを使えれば、重い処理であるプライマリキーの検索が省けます。すれば、オプティマイザはフルテーブルスキャンではなくインデックスの使用を選択する可能性が高くなります。従って、以下のようなカバリングインデックスを作りましょう。col2);ts列には不等号が使われているので、それ以外の列にインデックスが使えないはずじゃないかという人がいるかもしれません。これは、WHERE句内にcol1やcol2に対する条件がある場合には正しいのですが、ここで挙げたクエリではそうはなってはおらず、カバリングインデックスを狙うためだけに列を追加しただけです。まとめ例え簡単なクエリだとしても、クエリの最適化のためにインデックスがフィルタやソーティングをどのように行うか、カバリングインデックスがどのように動くかを理解するのが最も重要なことです。実行計画に対してクエリがどのように実行されるのか(おおまかにでも)理解しておくのも非常に有意義でしょう。これらのことを知らないと、オプティマイザが下した判断がなぜなのか分からなくなってしまうことがあるはずです。5.7以降では、コストモデルがチューニングされていることにも触れておきましょう。これにより、例えば高速なストレージを使用している場合はランダムリードのコストが通常ディスクより非常に小さいことなどを考慮できるようになるので、オプティマイザはよりよい判断を下せるようになるはずです。@yakstcom_req_jaYakstYakstお問い合わせ。