複合インデックスは順番が大事。WHEREとORDER BYから考えるインデックス設計
目次
はじめに
SQLのパフォーマンスを改善しようとすると、
「検索に使っているカラムにインデックスを貼ればいい」
と考えがちです。
自分も最初は、インデックスがあるかどうかを中心に見ていました。
ただ、大量データを扱う中で感じたのは、
インデックスは「あるかどうか」だけではなく、「どの順番で作られているか」も重要
ということです。
特に、
WHERE ORDER BY LIMIT
が組み合わさるSQLでは、複合インデックスの設計によって実行方法が変わることがあります。
この記事では、次のようなSQLを例に、
SELECT * FROM posts WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20;
なぜ (user_id, created_at) のような複合インデックスを考えるのか
を整理します。
そもそも複合インデックスとは
複合インデックスとは、複数のカラムを組み合わせて作るインデックスです。
例えば、
CREATE INDEX index_posts_on_user_id_and_created_at ON posts (user_id, created_at);
とすると、
user_id + created_at
を組み合わせたインデックスになります。
ここで重要なのが、
(user_id, created_at) (created_at, user_id)
上記2つは同じではないということです。
カラムの順番にも意味があります。
MySQLの複合インデックスでは、基本的に左側のカラムから順番に利用されます。
例えば (user_id, created_at) なら、user_id 単体や user_id + created_at を条件とする検索では利用しやすい一方、created_at だけを条件にした検索では、同じようには利用できません。
まずSQLを見て考える
例えば、SNSの投稿一覧で、
特定ユーザーの最新投稿を20件取得する
という処理があるとします。
SQLは次のようになります。
SELECT * FROM posts WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20;
このSQLでは、
WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20
という3つの要素があります。
ここで考えたいのが、
データベースは、この20件をどうやって探すのか？
ということです。
user_id だけにインデックスがある場合
例えば、
CREATE INDEX index_posts_on_user_id ON posts (user_id);
だけがあるとします。
これによって、
WHERE user_id = 100
に該当する投稿は探しやすくなります。
ただし、そのユーザーが大量に投稿している場合、
user_id = 100 の投稿を探す ↓ 該当する投稿を取得 ↓ created_at順に並べる ↓ 上位20件を返す
という処理になる可能性があります。
つまり、
WHEREの絞り込みには使えても、ORDER BYまで効率よく処理できるとは限りません。
created_at だけならどうなるか
逆に、
CREATE INDEX index_posts_on_created_at ON posts (created_at);
だけがある場合を考えます。
created_at の順番にデータをたどりやすくなります。
ただ、今回必要なのは、
WHERE user_id = 100に一致する投稿だけです。
そのため、実行方法によっては、
新しい投稿から確認 ↓ user_id = 100 か確認 ↓ 違ったら次へ ↓ 20件見つかるまで続ける
という形になる可能性があります。
大量のユーザーの投稿が混ざっている場合、これも必ずしも効率がよいとは限りません。
(user_id, created_at) を考える
そこで、
CREATE INDEX index_posts_on_user_id_and_created_at ON posts (user_id, created_at);
という複合インデックスを考えます。
このインデックスでは、イメージとして、
user_id ↓ その中で created_at 順
のようにデータが並びます。
今回のSQLは、
WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20;
なので、
user_id = 100 の範囲を探す ↓ created_at の順番を利用する ↓ 必要な20件を取得する
という実行方法を取れる可能性があります。
MySQLでは、複合インデックスの先頭カラムが WHERE で固定されている場合、後続カラムの並びを ORDER BY に利用し、追加のソートを避けられることがあります。
順番を逆にすると同じではない
では、
(user_id, created_at)
ではなく、
(created_at, user_id)
でもよいのでしょうか。
今回のSQLでは、この2つを同じものとして扱うことはできません。
WHERE user_id = 100 ORDER BY created_at DESC
では、まず user_id = 100 の範囲に絞り、その中から created_at の順番を利用したいので、
(user_id, created_at)
の方が今回の検索パターンに合っています。
複合インデックスには、左端プレフィックスという考え方があります。
例えば、
(user_id, created_at, id)
というインデックスがあれば、基本的には、
user_id user_id + created_at user_id + created_at + id
のように、左側から連続したカラムを使う検索で利用しやすくなります。
一方、
created_at created_at + id
のように先頭の user_id を使わない検索では、同じようには利用できません。
これが、
複合インデックスは順番が大事
と言われる理由の一つです。
「WHEREに使うカラムを先にすればいい」でもない
ここで注意したいのが、
WHEREに書いてあるカラムを全部先に置けばいい
という単純な話でもないことです。
例えば、
WHERE status = 1 AND user_id = 100 ORDER BY created_at DESC
というSQLがあったとしても、
(status, user_id, created_at)
が常に最適とは限りません。
実際には、
- それぞれのカラムの値の偏り
- どのSQLがよく実行されるか
- 何件取得するか
- JOINの有無
- 他のクエリでもそのインデックスを使うか
なども考える必要があります。
そのため、自分は、
SQLを見ただけでインデックスを決めず、必ず EXPLAIN で確認する
ようにしています。
EXPLAINで本当に使われているか確認する
例えば、
EXPLAIN SELECT * FROM posts WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20;
を実行します。
見るポイントとしては、
possible_keys key rows Extra
などがあります。
特に、
keyで、想定している複合インデックスが実際に使われているか確認します。
また、ORDER BY をインデックスの順序だけでは処理できない場合、Extra に Using filesort が表示されることがあります。
Using filesort が表示されたからといって、必ずしも問題があるわけではありません。
ただ、
なぜ追加のソートが必要になっているのか？
を確認する材料にはなります。
条件が合えば、MySQLはインデックスの順序を利用して ORDER BY を処理し、追加のソートを避けることができます。
Before / Afterで確認する
例えば、最初は、
INDEX(user_id)だけだったとします。
Before
WHERE user_id = 100 ↓ 対象を取得 ↓ created_atで並び替え ↓ 20件返す
その後、
INDEX(user_id, created_at)を追加します。
After
user_id = 100 の範囲を探す ↓ インデックス上の順序を利用 ↓ 必要な20件を取得
という実行方法になる可能性があります。
ただし、
複合インデックスを追加しただけで、改善したと判断してはいけません。
変更前にEXPLAIN ↓ インデックス変更 ↓ 変更後にEXPLAIN
という形で、実行計画を比較することが大切です。
インデックスは増やしすぎてもよくない
検索を速くしたいからといって、あらゆる組み合わせのインデックスを作ればよいわけではありません。
インデックスを追加すると、
INSERT UPDATE DELETE
の際に、そのインデックスも更新する必要があります。
さらに、インデックス自体がストレージ領域も使用します。
そのため、
遅い ↓ とりあえずインデックス追加
ではなく、
実際のSQLを見る ↓ WHEREを見る ↓ ORDER BYを見る ↓ 既存インデックスを見る ↓ EXPLAINする ↓ 必要なら複合インデックスを検討 ↓ もう一度EXPLAINする
という流れで考えるようにしています。
まとめ
複合インデックスは、
検索に使うカラムをまとめてインデックスにすればいい
というものではありません。
今回の、
SELECT * FROM posts WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20;
のようなSQLなら、
WHERE ↓ user_id ORDER BY ↓ created_at
という実際の検索方法を考え、
(user_id, created_at)のような複合インデックスを検討できます。
大切なのは、
インデックス単体を見るのではなく、SQL全体を見ることです。
自分は大量データを扱うようになってから、
どのカラムにインデックスがある？
どんなWHERE条件？
どんなORDER BY？
どの順番でインデックスが作られている？
実際にそのインデックスは使われた？まで確認するようになりました。
複合インデックスは「何を入れるか」だけではなく、「どの順番で入れるか」まで考える。
SQLのパフォーマンスを見る上で、特に意識したいポイントの一つです。
