こんにちは。 エキサイト株式会社の三浦です。
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の実行速度が早くなるわけです。
これは例えば、我々が紙の単語辞典から特定の単語を見つけたいときに、あいうえお順で単語が並んでいれば見つけやすいのと同じ道理です。
1つのカラムを条件とし、別のカラムで並び替える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の当て方が簡単に思えてきませんか？
では別のケースを考えてみましょう。
IN句を条件とするSQL
複数の 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 | *** |
| ... | ... | ... | ... |
これで十分高速化されるでしょう。
IN句で絞り込み、別のカラムで並び替えるSQL
では次に、複数の 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 |
| ... | ... | ... | ... |
これで十分でしょうか？
実際にこれで並び替えることを考えると、
- 各
categoryごとにデータを絞り込む - 各
categoryで絞り込んだレコードをあわせ、publish_date_timeで並び替える
ことが必要になるのが想像できます。
ここで重要なのは「 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 |
| ... | ... | ... | ... |
などです。 ですが残念ながら現状、すべての組み合わせでIndexを作る、という機能は存在しません。
つまり、ここが「Indexだけでパフォーマンスを改善する」方法の限界点と言えます。
改善方法としては、
- もし
categoryの組み合わせパターンが決まりきっているのであれば、その組み合わせを1つのデータとして持つデータ構造を作り、それをもとに新しくテーブルを作る - NoSQLを使用する
などが挙げられるでしょう。
最後に
Indexは、一見難しそうに見えますが、実は人がたくさんのデータの中から何かを見つけたり、並び替えたりするのと道理としては大差ありません。 そのため、仕組みさえ理解していれば、複雑なSQLでも適切なIndexを考えるのは不可能ではないのです。
一方で、だからこそIndexだけで解決できないSQLも存在します。
きちんと仕組みを理解し、適切なIndexとは何かを考えるのと同時に、Index以外の手段が必要な場合があることも想定してデータ構造やSQLを考えていきましょう。
