たとえ1つのテーブルだけに対して実行されるクエリでも、パフォーマンスが悪いというのはよくあることです。その理由は簡単で、インデックスの作り方がまずいため、実行計画がおかしくなってしまうのです。ここでは、1つのテーブルのみに対する色々なクエリを最適化するためのガイドラインを挙げてみたいと思います。
おことわり : あらゆる状況をカバーしようとはせず、一般的なガイドラインを提示するに留めるつもりです。ここで挙げたものがうまく適用できない例を簡単に見つけることができるのは間違いないでしょうが、ほとんどの場合はここに書いたことが十分なのも事実です。また、MySQL 5.6以上にあるIndex Condition Pushdownのような機能については、話を単純にするために扱わないことにします。そういった機能が、クエリのレスポンスタイムを(良い方にも悪い方にも)劇的に変えてしまうことがある点に注意しておきましょう。
インデックスは何をしてくれるのか？
インデックスには、フィルタ、ソーティング(order by)あるいはグルーピング(group by)、カバリングの3つのはたらきがあります。最初の2つについてはご存知の通りですが、「カバリングインデックス」が何かご存じない人は多いでしょう。しかし簡単なことです。普通のクエリの実行の流れは以下のようになっています。
- インデックスを使って一致するレコードを探し、データへのポインタを取得
- 対応するデータへのポインタをたどる
- レコードを返す
カバリングインデックスが使用できる時には、クエリから要求される全てのフィールドがインデックスに含まれてしまっています。つまり、上の流れの2がスキップできるので、以下の流れで実行できます。
- インデックスを使って一致するレコードを探す
- レコードを返す
多くの場合、データは大きくてメモリに乗り切らない一方で、インデックスは小さくメモリに乗り切る可能性があります。つまり、カバリングインデックスを使うことで、ディスクへのアクセスを大きく減らすことができ、桁違いにパフォーマンスを良くできるのです。
ここで、これとは違った一般的な例を見てみましょう。
等号が1つの場合
以下は最も基本的な例です。
SELECT * FROM t WHERE c = 100
このクエリに対しては、もちろん(c)にインデックスを作ることを考えます。ただし、この条件で得られるデータの選択性が低い場合は、オプティマイザはより効率がよいであろうフルテーブルスキャンを実行することを選択する可能性もあります。
また、このクエリのパターンとしては、全フィールドではなく一部のみを選択したい場合が考えられます。
SELECT c1, c2 FROM t WHERE c = 100
ここで、(c, c1, c2)というインデックスを作る意味が出てきます。なぜなら、これはカバリングインデックスになるからです。ただし、(c1, c2, c)というインデックスは作らないように！これも全ての列をカバーしてはいますが、行をフィルタするのには使えなくなってしまうからです(フィルタにはインデックスの最も左にある列しか使えないことを思い出しましょう)。
等号が複数ある場合
SELECT * FROM t WHERE c = 100 and d = 'xyz'
これも最適化は簡単です。(c, d)あるいは(d, c)というインデックスを作ればいいだけです。
ここでよくある間違いは、(c)と(d)という2つのインデックスを作ってしまうことです。MySQLではindex_mergeアルゴリズムを使ってこの2つのインデックスを両方使うこともできはしますが、通常は非常によくない方法と考えてよいでしょう。
等号と不等号がある場合
SELECT * FROM t WHERE c > 100 and d = 'xyz'
不等号の列でインデックスを使ってしまうと、それ以降の列をインデックスで使えなくなってしまう点に注意しなければなりません。
従って、インデックスを(d, c)と作成すれば、cとdの両方をフィルタすることができて望ましいでしょう。しかし、(c, d)と作成してしまうと、cのフィルタにしか使えないので、効率的とは言えません。
従って、等号のみを使うときとは違って、不等号を使う際にはインデックス内の列の順番も意味を持ってきます。
複数の不等号がある場合
SELECT * FROM t WHERE c > 100 and b < 10 and d = 'xyz'
2つの不等号がありますが、どちらの条件もインデックスでフィルタすることはできないのはもう分かりますね(注)。従って、(d, b)と(d, c)のどちらのフィルタを使うべきかの選択ということになります。
データを見てみないことには、どちらがベターな選択科は分かりません。単純に、不等号を使った時に最も選択性の高い列を選べばよいでしょう。また、等号を使っている列を最初に持ってこなくてはなりません。
(注) 両方の不等号を「フィルタ」する方法も実は存在します。bでパーティションを作り(d, c)にインデックスを作るか、cでパーティションを作り(d, b)にインデックスを作ればよいのです。詳細はここでは省きますが、いくつかの場面で使えるはずです。
等号とソートがある場合
SELECT * FROM t WHERE c = 100 and d = 'xyz' ORDER BY b
最初の項で言ったように、インデックスはフィルタとソーティングに使えるわけですから、このクエリも簡単に最適化できます。ただし、不等号と同じように、インデックス内の列の順番は注意して決める必要があります。その際のルールは、フィルタが最初で、その後にソーティング、です。
それを念頭に置けば、(b, c, d)や(b, d, c)のようにフィルタをせずにソーティングしてしまうようなインデックスではなく、(c, d, b)あるいは(d, c, b)といったインデックスを作ればいいことがすぐ分かるでしょう。
また、以下のようなクエリがあるとしましょう。
SELECT c1, c2 FROM t WHERE c = 100 and d = 'xyz' ORDER BY b
このクエリに対しては、フィルタとソーティングだけでなくカバリングインデックスも行う、超高効率なインデックス(c, d, b, c1, c2)を作ることができます。
不等号とソートがある場合
これには大きく2つのパターンがあります。まずは以下のようなクエリです。
SELECT * FROM t WHERE c > 100 and d = 'xyz' ORDER BY b
これに対しては、2つの方法が考えられます。
- dでフィルタし、bでソートする
- dとcでフィルタする
どちらの戦略の方が効率がよいでしょうか？これはデータによって違うので、試してみる必要があるでしょう。
もうひとつのパターンは、このクエリです。
SELECT * FROM t WHERE c > 100 ORDER BY b
こちらは等号がないので、フィルタかソーティングのどちらかを選ぶ必要があります。多くの場合はフィルタを選ぶことになるでしょう。
まとめ
この記事で全てのケースをカバーできてはいませんが、注意しないとひどいインデックスを作ってしまうということはお分かりでしょう。また別の記事で、一見しただけではよく分からないクエリでも、この記事から分かったことを適用すれば簡単に理解できる例を提示してみようと思います。
