インデックスを貼るべきカラムについて

概要

達人に学ぶDB設計徹底指南書で、インデックスを貼るべき列への解説が勉強になったので書きました。

「なんとなくインデックス作っておこ〜」的な人向け。
こういう列にはインデックス必要だなという判断が言語化できるのでオススメです。

結論

インデックスを作るべき列	詳細
大規模なテーブル	目安はレコード数1万件以上ならインデックスを貼る意味がある
カーディナリティの高い列	絞り込みの率が全レコードの5%以下ならインデックスを貼る意味がある
検索条件、結合条件で頻繁に使われる列	SQL 文によってはインデックスが機能しない場合もあるので注意

そもそもインデックスとは

インデックスとはプログラミング的に表現すると、(x, a)という形式の配列です。
x はキー、a はそれに結びつく情報(実データ or ポインタ)を意味します。

インデックスをたどることで、テーブルの特定のレコードに狙い撃ちでアクセスできます。

インデックスの種類にはいくつかあるようなのですが、頻繁に使用されるインデックスは B-tree インデックスとや呼ばれるものらしいです。
なのでこれから説明するインデックスとしては B-tree インデックスのことだと考えていただいて構いません。

B-tree インデックスを作るべき列

1. 大規模なテーブル
2. カーディナリティの高い列
3. SQL 文で WHERE 句の選択条件、結合条件に使用されている列

1. 大規模なテーブル

データ量と処理時間のグラフが以下になります。
データ量が少ない場合は、インデックスを使うよりも高速に処理ができていることが分かります。

実際の処理時間の差はごくわずかですが、それなら余計なインデックスを貼る必要はありません。
しきい値としては、あくまで目安ですが、およそレコード数が1万件以下の場合はほぼ効果がないと考えて良いです。

2. カーディナリティの高い列

カーディナリティとは、特定の値がどれくらいの種類の多さをもつか、という事を表現する概念です。
例えば、「性別」という列があったとして、
①男性
②女性
③不詳
という種類があれば、カーディナリティは「3」です。

一方、「口座番号」や「誕生日」のカーディナリティはもっと高くなります。
「口座番号」は顧客ごとに一意でしょうし、「誕生日」は365になります。

「カーディナリティが高い」の目安としては、特定のキーを指定したときに、全体のレコードの5%程度に絞り込めるということです。

例えば「誕生日」であれば、365日のうち1日を指定すると、約0.3%に絞り込めるため、「誕生日にインデックスを作る意味がある」と判断できます。

カーディナリティの注意点

複合列の組み合わせで考える

例えば a, b, c という列にインデックスを作るとして、カーディナリティはそれぞれ以下だとします。
a = 2
b = 10
c = 5

このとき、1つ1つ見ていくとどれも絞り込みの率は5%よりも大きく、インデックスを作る意味がないように思えます。

しかし、a, b, c を組み合わせて絞り込んだ場合、カーディナリティは 2×10×5 = 100 となり、絞り込みの率は1%となるため、インデックスを貼る意味があると判断できます。

複合インデックスを作る場合も、この考え方を持っていれば判断材料になりますね。

カーディナリティが高くても、特定の値にデータが集中している列は向いていない

例えば 1~100 までの値を取る列があったとして、値の99%が100で、他が1%というようなケースでは、検索性能が安定しないため、インデックスを作るのに向いていません。

3. SQL 文で WHERE 句の選択条件、結合条件に使用されている列

SQL で検索や結合の条件で頻繁に使用される列にはインデックスを作る意味があると判断できます。
ただし、SQL 文によってはインデックスが機能しない場合があるので注意する必要があります。

例えば、col_1 にインデックスを作ったとしても、以下のように演算をしている場合は機能しません。

SELECT * 
  FROM SomeTable  
  WHERE col_1 * 1.1 > 100;

以下のように式変形すると回避できます。

WHERE col_1 > 100/1.1

他にも、「索引列に対して SQL を適用している」、「IS NULL 術後を使っている」などありますが、量が多くなるので詳細は割愛します。