MySQLのインデックスに関する考察（特に複合インデックスについて）

昨今、インデックスに関して思うところがあったので、改めていくつかの観点で確認してみました。

なお、インデックスを本当に理解する上ではデータ構造であるB+ツリー構造や同構造上での処理手順に関して理解しておくと良さそうなのですが、それはなかなかにハードルが高そうなので、とりあえずは見た目で確認できる範囲としてEXPLAINの実行結果や処理速度の計測結果辺りからインデックスの適用方法と効果に関して確認したいと思います。

確認対象（MySQL）

使用するDBはMySQLです。MariaDBではありません。
この辺、少し整理しておきたいと思います。

過去記事でもしばしば触れてきましたが、私が扱う環境ではWebmin/Virtualminを使用して環境構築・運用保守を行っています。
DBに関してはVirtualminインストール時に合わせてインストールされますが、MySQLおよびMariaDBのいずれをインストールするかと言う選択はありません。蛇足ながらApacheおよびNginxのいずれを使用するかに関してはVirtualminのインストーラに対する引数で指定可能です。
上記状況において、CentOSではMariaDBがインストールされていましたが、Ubuntu（少なくともfocal）においてはMySQLがインストールされるようです。

昨今までCentOS組として無条件にMariaDBを使用してきた身としてはMySQLで不都合が発生しないのか気にはなります。例えば、MariaDBの方がMySQLよりも性能的に優れていると聞いていたので、実際のところその辺はどうなのかとか。
一応Ubuntu用MariaDBもあるようなので、改めてUbuntu上でのMySQLとMariaDBの性能比較なども行ってみたいと思いますが、今回はMySQLに関して検証したいと思います。

なお、MySQLのバージョンは「8.0.23-0ubuntu0.20.04.1」でした。ハイフン以降は無視して、現時点（2021年3月13日）での最新バージョンである「8.0.23」と判断して良さそうです。

EXPLAINの使い方

インデックスがどのように機能しているかを確認する方法としては「EXPLAIN」の結果を見るのが一般的です。
例えば、以下のようなSQLを考えます。

select * from t_test where key1=1;

上記に関してインデックスがどのように機能するかを確認する場合は以下のように実行してみます。

explain select * from t_test where key1=1;

単に実行したいSQLの前に「explain」を付けるだけです。

出力結果に関しては色々と複雑なので詳細はGoogle先生に聞いていただくとして、今回は「type」と「Extra」に着目してみます。

type

「type」として出力される内容はいくつかありますが、PRIMARYもしくはUNIQUEのようにテーブル上で一意なキーではないキーを対象とする場合は実際に表示されるのは以下の3つくらいかと思います（少なくとも今回の実験ではこの3タイプしか出てきません）。

Extra

一方「Extra」には色々な情報が表示される可能性があるようですが、今回の実験の範囲で理解しておくべきは以下の3つでした。
解釈に関してはネット上では様々な解説がされていて正確かつ簡易にまとめることは難しいため、今回の事例に則した個人的な解釈結果を記述しておきます。

最後の「Using where」に関しては「type=ALL」つまりインデックスが機能していない場合はこれになりますし、where句に複数の条件が指定された場合でインデックスと無関係な条件が指定されている場合もこれになります。後者の状況においてはインデックス自体は機能していて処理速度も向上しているので、問題と言う訳ではなくまだ改善の余地がある（インデックスが適用できていない条件も含めてより適切なインデックスを検討できるのでは？）と言うくらいの解釈で良いかと思います。

性能測定方法

EXPLAINでのインデックス適用状況確認だけでなく、実際にどの程度の性能改善が見られるかも実測してみたいと思います。
SQLの実行はLaravel上のプログラムから行い、結果に関してもLaravelのクエリログ機能を使用して確認します。

具体的な実行イメージは以下の通り。

DB::enableQueryLog();
（処理実行）
DB::disableQueryLog();
print_r(DB::getQueryLog());
DB::flushQueryLog();

「DB::getQueryLog()」の結果において処理に要した時間がミリ秒単位で出力されていますので、これを確認します。
なお、下記では同条件下で対象SQLを10回実行し、その平均値を示しています。

インデックスに関する基本的な動作確認

まずは、最もシンプルなケースとして1つのカラムにインデックスを適用し、同カラムに対する条件指定とインデックスの効果に関して確認したいと思います。
具体的にはkey1（BIGINT）に1〜100の数字をランダムに設定したレコードを10万件用意し、これらに関して各種SQLを実行してみます。

最初は単純な等価比較の結果です。

select * from t_test where key1=1;

インデックスの効果に関しては、10万件のレコードのフルスキャンが10万回の確認を要するのに対してインデックスを適用すると対象レコードを見つけるために必要な処理回数はlog(10万）回になるとのこと。この時のlogの底はインデックスの管理構造であるツリー構造（B+ツリー）の各ノードにおける分岐の数です。例えば100個の要素を一列に並べて調べて確認していく場合は単純に100回の確認作業が必要になりますが、それを10に枝分かれするツリー構造にした場合、リーフにたどり着くまでに必要なブランチの選択は2（底を10とするlog100）回であることはイメージできますね。当然ながら、条件に合わせてブランチを選択する仕組みが存在することが前提ですが。
なお、上記はあくまで検索処理に関してのみの論理的な数字なので、実際にインデックスを適用した操作においてはそこまでの劇的な変化はありません。先に示した実測結果でも4倍強程度ですし、経験的にも数十倍レベルであることが多いように思います。

次に大小比較の場合を確認してみます。

select * from t_test where key1 > 50;

実は、上記だとkey1に適用したインデックスが機能しません。
MySQLに限らないことのようですが、DBは実行されたSQLに対してインデックスを使用した方が効率が良いかどうかを判断している模様で、上記条件では効率的ではないと判断されるようです。なお比較する数字を変動させた結果、「key1 > 86」からインデックスが有効になりました。小さい方の比較においても「key1 < 15」から有効でした。
ネット情報によるとMySQLでは全レコードの10%程度に絞り込めるかどうかでインデックスの有効性を判断しているとの説もあったのですが、上記結果から判断すると15％程度から有効になっているように思われます。この辺はMySQLのバージョンや実行するSQLの内容によって違ったりするんでしょうかね？

なお、「key1 > 90」での実行結果は以下の通り。

性能改善度合いとしては等価比較と比べてかなり弱いですね。

複合インデックス

SQLにおいては複数の条件を組み合わせて指定する場合がほとんどではないかと思います。
以下ではkey1,key2（共にBIGINT）のカラムを持ち、key1は1〜100、key2は1〜10の数字をランダムに適用したレコードを10万件用意し、それらに対してSQLを実行して行きます。
またインデックスに関してはkey1,key2それぞれに対する単独のインデックス、およびkey1,key2、key2,key1の組み合わせに対する複合インデックスを個別に作成し、それぞれに関して計測を行います。

まずは基本的な等価比較から。

select * from t_test where key1=1 and key2=1;

単独カラムに対するインデックスにおいてkey1とkey2それぞれに対するインデックスでは性能が倍ほど違います。まずは絞り込まれたレコード数がkey2での絞り込みの方が10倍多いと言うこともあるかと思いますが、カーディナリティ（当該カラムに含まれる値の種類の多さ）の差による効率の違いもあるのではないかと思います。インデックスはカーディナリティが高いカラムに適用した方が効果的であると言われていますので、その意味でもkey1（カーディナリティ100）の方がkey2（カーディナリティ10）よりもインデックスとして効果的であったことを示す結果ではないかと推測します。

また、単独カラムに対するインデックスより複合インデックスを適用したケースの方が早いです。
同じ複合インデックスにおいてkey1,key2よりもkey2,key1の方が数字的には早いですが、この辺は誤差の範囲かと。カーディナリティから判断すればkey1,key2の方が早そうに思うのですが。

key2の方を大小比較に変えてみます。

select * from t_test where key1=1 and key2 > 5

key2単独カラムに関する条件としてはインデックスが有効にならない比較内容である点が本確認における重要な点です。
結果は以下の通り。

興味深いのは複合インデックスkey2,key1が無効である点です。両方のキーを合わせた絞り込み結果は全レコードの0.5％程度になるので、その意味ではインデックスを有効とみなす条件はクリアしているように思うのですが、EXPLAINの出力および実測結果のいずれを見てもインデックスは有効になってはいません。
一方でkey1,key2においては「key2 > 5」と言う条件が支障にはなっていません。むしろkey1単独カラムのみのインデックス適用時との比較においても性能が向上していることから、key2による絞り込みが有効に働いていることが確認できます。

つまり、複合インデックスを適用する場合、同じカラムの組み合わせに対するインデックスであっても実行するSQLの条件に合わせて適切にカラムの指定順序を指定しないと有効ではないと言うことになり、見方を変えれば同じカラムの組み合わせに対しても様々なSQLの条件を考慮した場合に異なる並びのインデックスを複数用意する必要が生じる可能性があることを意味します。
なかなかややこしいですね…

総括

もっと色々な観点があるかと思いますが、大まかに下記のような内容が確認できたので今回はこれで良しとしておきます。

• インデックスを適用したカラムを条件とするSQLでもインデックスの使用が効果的でないと判断された場合はインデックスは使用されない
• 等価比較に対して大小比較はインデックスの効果が低そう
• やはりカーディナリティが高いカラムの方がインデックスとしてより有効っぽい
• 複合インデックスにおけるカラムの順序は重要

特にリレーションに関連するカラムに対してインデックスを適用すると言うことは普段から行っていることではありますが、その際に複合インデックスを作成したことは実はあまりありません。無論、複合インデックスとする必要がないケースが多いと言うこともありますが、そもそも検討自体をあまりしてこなかったと言うのが正直なところです。

複合インデックスの適用に関しては食わず嫌い感があります。
原因の１つとして「インデックスの作り過ぎは良くない」と言う説が影響しています。
インデックスを作成することはメリットだけではありません。レコードの登録・更新時にインデックスの再構築が必要になるためパフォーマンスに若干なりとも影響があることと、インデックス分のメモリが余計に必要になることなどがデメリットとして上げられます。
また、複合インデックスを適用してもあまり効果的でなかった言う記憶も影響しています。実際、前述した最後の実験結果においても、key1単独に対するインデックスとkey1,key2に対する複合インデックスでは処理時間に大差がある訳ではありませんし、むしろ単独で効果のあるカラムを他のカラムと組み合わせて複合インデックス化したら効果が無くなったと言う事例もあったように思いますので、その辺から「複合インデックス＝使えない奴」的な印象を持ってしまっていたように感じます。

しかし、実のところインデックス作成によるパフォーマンスおよびメモリへの影響がどの程度あるかに関しては調べてみてもはっきりしません。
そもそも「作り過ぎ」とはどの程度からかも分かりません。ネット情報によると全てのカラムにインデックスが適用されているケースを見たと言うような例も見受けられましたが、これはさすがに極端な例ですし、本来その必要もないはずです。
複合インデックスの有効性に関しても、過去に不満足な結果になったケースではカラムの選択や指定順序に問題があった可能性は否めません。
単独カラムに対するインデックスと複合インデックスの差に関しても、今回の例では大差はありませんでしたが、SQLが複雑になっていった場合に明確な差が出てくるケースがあるかもしれません。SQL実行時に対象テーブル1つに対して適用されるインデックスは、同テーブルに複数のインデックスが存在した場合でもその中の1つだけと言うMySQLの制約もありますので、複雑な条件に対してより有効なインデックスを作成しようとすれば、むしろ自動的に複合インデックスの形を取らざるを得ないと考えるべきかもしれません。

先入観を持たず、EXPLAIN等を使って有効な形のインデックスを過不足ない数作成することが重要と言うことですね（当たり前な結論ですが）。

なお、インデックス以前にそもそもSQLの組み方が性能に大きく影響すると言うことはありますが、それはまた別の話として。
また、MySQLにはインデックスマージなる能力もあって、ケースによっては別々のカラムに対して適用した複数のインデックスを組み合わせたような動きをすることもあるようです。なんとなく「それじゃ複合インデックスって不要では？」と思わせるような話ですが、本機能は条件がOR結合している場合のみ有効っぽいですし、その他にも機能する条件があるようで、複合インデックスを補完できるものではなさそうです。複数のインデックスが合わせ技的に機能してくれることは美味しい話なので、これはこれで別途調査したいと思います。