こんにちは。 エキサイト株式会社の三浦です。
MySQLにおいてIndexは、Selectクエリのパフォーマンス向上のために必要不可欠な要素です。 1Indexに対して1カラムを付けるということであればいいですが、複数カラムを付ける場合、付ける順番によってパフォーマンスが変わってきます。
ここでは、どのような順番で付けるのが良いかについて1つの案を提示します。
Indexとカーディナリティ
Indexを貼る際には、カーディナリティを考える必要があります。 例えば、以下のようなArticle（記事）テーブルがあるとします。
| id | code | title | category | publish_date | active |
|---|---|---|---|---|---|
| 1 | aaa | 子育て記事A | child | 2021-01-01 00:00:00 | 1 |
| 2 | bbb | 子育て記事B | child | 2021-01-02 00:00:00 | 0 |
| 3 | ccc | 子育て記事C | child | 2021-01-03 00:00:00 | 0 |
| 4 | aaa | くらし記事A | lifestyle | 2021-01-02 00:00:00 | 1 |
| 5 | bbb | くらし記事B | lifestyle | 2021-01-05 00:00:00 | 1 |
| 6 | aaa | ビューティ記事A | beauty | 2021-01-03 00:00:00 | 0 |
| 7 | bbb | ビューティ記事B | beauty | 2021-01-04 00:00:00 | 1 |
また、以下の条件だとします。
idが主キーでAuto Incrementcode（記事コード）とcategory（記事のカテゴリ）で複合ユニークなデータactiveは0/1のどちらかが入る
active カラムは0と1のどちらかしか無いため、よほど値に偏りがあったりしないとそこまでレコードを絞り込むことが出来ません。
そのような状態を、「カーディナリティが低い」と言います。
一方で id は主キーであり、すなわちユニークなデータなので、 id を指定すれば大きく絞り込むことが可能です。
そのような状態を、「カーディナリティが高い」と言います。
以上のことから、一般的にIndexはカーディナリティが高いカラムに貼るほうが効果が高いため、そのようなカラムに貼るべきです。
複合Indexとカーディナリティ
1Indexに1カラムの場合はもちろんですが、1Indexに複数カラムが付く場合も、カーディナリティが高い順番でIndexを付けるべきだと言われています。
たとえば今回の場合、 code （記事コード）と category （記事のカテゴリ）で複合Indexを作るとしましょう。
上記の例ではそこまで種類に違いはありませんが、これから記事数が増えていくにつれ、カテゴリ数はそこまで増えず、記事コード数は増えていくことが予想されます。
であれば、カーディナリティで言えば記事コードが高く、カテゴリが低いということになるので、順番としては
codecategory
という順番で付けるのが妥当そうな気もします。
ですが実は、そう単純なものではありません。
複合Indexの場合、必ずしもカーディナリティ順でなくても良い
今回のテーブルの場合、 code と category は複合ユニークです。
すなわち、 code だけでは一意にレコードを絞り込むことが出来ないため、 code だけを条件としてSelectクエリが投げられることはあまりないと考えられます。
一方で、記事をカテゴリ別に取得するというケースは十分に考えられます。
すなわち、 category でSelectすることは考えられるケースです。
そのような時、
codecategory
の順番であればこのIndexが当たらない可能性があるため、場合によっては別途Indexを用意する必要がありますが、
categorycode
の順番であればIndexが当たるため、新たにIndexを用意する必要がありません。
Indexは、作りすぎると逆にパフォーマンスが落ちてしまう原因にもなりえます。
今回はかなりシンプルなケースだったので category のIndexくらい作れば良いかもしれませんが、これがカラム数の多いテーブルになった場合などは問題になる可能性が考えられます。
もちろん、今回で言う active のレベルでカーディナリティが低いカラムを1番目に入れ始めるとまた話は別だったり、要求されるパフォーマンス次第かもしれませんが、 category くらいであればカーディナリティにそう目くじらを立てるほど厳しくする必要はないでしょう。
それよりも、不要なIndexの作成を防ぐ方を考えても問題ないのではないでしょうか。
まとめ
基本的に、複合カラムのIndexはカーディナリティが高い順につけるのが良いと言われていますが、上記のように必ずしもそれに従っていれば最適であるというわけではありません。 Indexに付く複数のカラムが複合ユニークかどうか、アプリケーションで実際にどのように使われる想定なのか、それらによって柔軟に順番を決めていけるとよりアプリケーションのパフォーマンスを向上させていくことができるでしょう。
