MySQLの「Indexが当たる」とはどういうことなのか

こんにちは。エキサイト株式会社の三浦です。

MySQLを使用してデータを取得する際、その取得速度を高めるためにほとんどの場合Indexを使用することになります。

シンプルなSQLでデータを取得するのであれば、そのSQLに当たるIndexはわかりやすいでしょう。

ですが、複雑なSQLだとそう簡単には行きません。そういったSQLにIndexを適用しようとして、うまくパフォーマンスが上がらず苦労した経験がある方も多いのではないでしょうか。

今回は、そもそも「SQLにIndexが当たる」とはどういうことなのかを考え、それを元にうまくIndexを当てる方法を考察していきます。

1つのカラムを条件とするSQL

以下のような article テーブル（記事テーブル）を考えてみます。

article_code	story	category	publish_date_time
article1	テスト	lifestyle	2022-01-01 00:00:00
article2	テストテスト	child	2022-01-01 01:00:00
article3	テストテストテスト	beauty	2022-01-01 02:00:00
...	...	...	...

このようなテーブルから、特定の category の記事を取得するSQLは以下になります。

SELECT
    *
FROM
    article
WHERE
    category = "***";

この場合の適切なIndexは簡単です。 category をキーとするIndexがあれば良いでしょう。

ちなみに、なぜ category をキーとするIndexであれば、上記のSQLの取得速度が早くなるのでしょうか？それは以下のように、 category でソートされたデータが作られるからです。

article_code	story	category	publish_date_time
***	***	beauty	***
***	***	beauty	***
***	***	beauty	***
***	***	beauty	***
...	...	...	...
***	***	child	***
***	***	child	***
***	***	child	***
***	***	child	***
...	...	...	...

category でソートされているため、特定の category のデータが非常に見つけやすく、結果としてSQLの実行速度が早くなるわけです。

これは例えば、我々が紙の単語辞典から特定の単語を見つけたいときに、あいうえお順で単語が並んでいれば見つけやすいのと同じ道理です。

では次に、category を条件として取得したデータを publish_date_time 順に並び替えることを考えましょう。

SQLとしては以下になります。

SELECT
    *
FROM
    article
WHERE
    category = "***"
ORDER BY
    publish_date_time;

これを高速化するためには、どんなIndexがあれば良いでしょうか。

今度は、Indexからどのようなデータが作られれば高速化するか、という観点から考えていきます。

category で絞り込み、その後 publish_date_time で並び替えるのであれば、つまり最初からそのようなデータとなっていれば良いわけです。すなわち、以下のようなデータが好ましいということになります。

article_code	story	category	publish_date_time
***	***	beauty	2022-01-01 02:00:00
***	***	beauty	2022-01-05 01:00:00
***	***	beauty	2022-01-11 01:00:00
***	***	beauty	2022-01-15 01:00:00
...	...	...	...
***	***	child	2022-01-01 01:00:00
***	***	child	2022-01-03 02:00:00
***	***	child	2022-01-13 02:00:00
***	***	child	2022-01-13 03:00:00
...	...	...	...

このようなデータであれば、 category を絞り込むのも容易ですし、絞り込んだ時点ですでに publish_date_time で並び替えられているのでそのまま使用すれば良いことになります。

そして、このデータを実現するIndexが category,publish_date_time の複合Indexとなります。

つまり、このIndexが今回のSQLにおいて適切なIndexとなるわけです。

このように考えていくと、案外Indexの当て方が簡単に思えてきませんか？

では別のケースを考えてみましょう。

複数の category でデータを絞り込みたい場合は、IN句を使用します。

SELECT
    *
FROM
    article
WHERE
    category IN ("***", "***");

この場合に適切なIndexはなんでしょうか？

これもそこまで難しくはなく、一番最初のSQLのように category で並び替えられていれば問題ありません。

article_code	story	category	publish_date_time
***	***	beauty	***
***	***	beauty	***
***	***	beauty	***
***	***	beauty	***
...	...	...	...
***	***	child	***
***	***	child	***
***	***	child	***
***	***	child	***
...	...	...	...

これで十分高速化されるでしょう。

では次に、複数の category で絞り込んだ後に publish_date_time で並び替える場合はどうでしょうか？

SELECT
    *
FROM
    article
WHERE
    category IN ("***", "***")
ORDER BY
    publish_date_time;

2番目のSQLのようなデータ（ category,publish_date_time の複合Index）で考えてみましょう。

article_code	story	category	publish_date_time
***	***	beauty	2022-01-01 02:00:00
***	***	beauty	2022-01-05 01:00:00
***	***	beauty	2022-01-11 01:00:00
***	***	beauty	2022-01-15 01:00:00
...	...	...	...
***	***	child	2022-01-01 01:00:00
***	***	child	2022-01-03 02:00:00
***	***	child	2022-01-13 02:00:00
***	***	child	2022-01-13 03:00:00
...	...	...	...

これで十分でしょうか？

実際にこれで並び替えることを考えると、

ことが必要になるのが想像できます。

ここで重要なのは「 publish_date_time で並び替える」ことが必要になってしまっている点であり、結果として十分な速度が出ない可能性があります。もちろん全体のデータ件数が少なければ十分かもしれませんが、件数が増えていくとどこかで速度が不十分になることが予想されます。

では、どうすればよいのでしょうか？

理想としては、「複数の category で絞り込んだ結果を、 publish_date_time で並び替えたデータ」を作る、などでしょうか。すなわち、

article_code	story	category	publish_date_time
***	***	beauty	2022-01-01 02:00:00
***	***	beauty	2022-01-05 01:00:00
***	***	beauty	2022-01-11 01:00:00
***	***	beauty	2022-01-15 01:00:00
...	...	...	...
***	***	child	2022-01-01 01:00:00
***	***	child	2022-01-03 02:00:00
***	***	child	2022-01-13 02:00:00
***	***	child	2022-01-13 03:00:00
...	...	...	...
***	***	beauty,child	2022-01-01 01:00:00
***	***	beauty,child	2022-01-01 02:00:00
***	***	beauty,child	2022-01-03 02:00:00
***	***	beauty,child	2022-01-05 01:00:00
***	***	beauty,child	2022-01-11 01:00:00
***	***	beauty,child	2022-01-13 02:00:00
***	***	beauty,child	2022-01-13 03:00:00
***	***	beauty,child	2022-01-15 01:00:00
...	...	...	...