はじめに
DBパフォーマンス向上を目的としてインデックスを張ることは一般的な手法ですが、その効果を高めるためには対象となるカラムを適切に選択したいところです。
今回はインデックス対象とすべきカラムを選択する際に検討するいくつかのことをまとめてみました。
そもそもインデックスとは
indexの訳には「索引」というものがありますが、これが一番わかりやすいと思います。
本を読む時に、あるテーマについて書かれたページを探す時、二つの方法があります。
- 最初のページから最後のページまで順番に探していく。
- 本の末尾にある索引から探して、該当ページ数を調べる。
それぞれのメリット／デメリットを整理すると、次のようになります。
| メリット | デメリット | |
|---|---|---|
| 1 | 1. 索引作成の手間が不要。 2. 索引のためのページも不要（紙の節約になる）。 |
1. 探すのに時間がかかる。 |
| 2 | 1. 短時間で探せる。 | 1. 索引作成の手間がかかる。 2. 索引のためのページも必要。 3. 索引があっても探したいテーマと無関係だったら意味がない。 |
以上のことをDBに置き換えると次のようになります。
テーブルから特定のレコードを抽出したい時、次の二つの方法があります。
- 対象テーブルを先頭から順にシーケンシャルに探していく（＝インデックスのない状態）
- インデックスを参照して該当レコードのIDを調べる。（＝インデックスのある状態）
（※インデックスは一般的に木構造で表現されますが、ここではわかりやすくするため表形式で表現しています）
それぞれのメリット／デメリットを整理すると、次のようになります。
| メリット | デメリット | |
|---|---|---|
| 1 | 1. インデックス作成のためのオーバーヘッド無し。 2. インデックスのためのデータも不要（容量の節約になる）。 |
1. 探すのに時間がかかる。 |
| 2 | 1. 短時間で探せる。 | 1. インデックス作成のオーバーヘッドが必要。 2. インデックスを保存するための容量も必要。 3. インデックスがあっても探したいカラムのインデックスがなければ意味がない。 |
以上がインデックスに関する説明です。
インデックス対象となるカラムを選択する時に検討すること
WHEREやJOINで使用される → インデックス候補
インデックスは特定のカラムから該当するレコードを探すために使用されるので、WHEREやJOINで指定されるカラムはインデックス候補となります。レコード件数が1万件を超えるテーブル → インデックス候補
レコード件数が少ない場合、インデックスによる検索よりシーケンシャルな全件検索の方が早い場合があります。
明確な閾値ではありませんので、1万件は目安として捉えてください。外部キー → インデックス候補
MySQLの場合、外部キーを作成すると自動的にインデックスが作成されます。カーディナリティが20以上 → インデックス候補
カーディナリティとは、カラムに設定される値の種類の数です。下記テーブルのageに設定される値の種類は20と25の二種類なので、カーディナリティは2です。
カーディナリティが小さいと、一つの値に多くのレコードが紐づくことになるため、結局探すのに時間がかかることになります。
インデックス内の一つの値が指し示すレコード件数はレコード全体の5%程度が良いとされています。
（上記の例ではtrueとfalse、それぞれが全体の50%のレコードを指し示しているので、望ましくありません。）
カーディナリティの20以上という値は、インデックス内の一つの値が指し示すレコード数が、各値で均等な場合に、それぞれ5%になるという意味で設定したものです。
そのため、レコード数に偏りがある場合はカーディナリティーが大きくてもインデックスが有効でない場合があるので注意が必要です。カラムに対してSQL関数が使われている → インデックス候補となるかはクエリ次第
例えば、ageカラムにインデックスが張られていて、クエリが下記のようになっている場合、インデックスは使用されません。
WHERE AVG(age)>= 20
この場合、関数インデックスを使用するか、関数を使用しないようにすることでインデックスが使用されるようにする必要があります。
インデックスが使用されているか確認する
カラムにインデックスを張っても、それが必ずしも使用されるとは限りません。
MySQLではクエリの先頭にEXPLAINをつけることで、そのクエリで使用されるインデックスを確認することができます。
EXPLAIN SELECT * FROM users WHERE name = 'Bob';
実行結果のpossible_keysが使用されるインデックスの候補で、keyが実際に使用されたインデックスです。
インデックスが意図通りに使用されない場合
WHEREの条件で複数のカラムが指定される場合などは、単一のインデックスは使用されないことがあります。
その場合は、複数のカラムに対して一つのインデックスを張る複合インデックスを張ります。
CREATE INDEX ON users(name, age);
複合インデックスが使用されない場合は、指定するカラムの順序をクエリに合わせて調整します。
まとめ
本記事では、インデックスを張る時に確認することを記述しました。
- WHEREやJOINで使用されるかどうか
- レコード件数が1万件を超えるかどうか
- 外部キーかどうか
- カーディナリティが20以上かどうか
- カラムに対してSQL関数が使われているかどうか
ボトルネックになりがちなDBパフォーマンス向上のために、本記事がお役に立てると幸いです。