【DB】インデックスを作成すべきカラム

大規模なテーブル

大前提として、そもそもインデックスはある程度の規模のあるテーブルに作成することが推奨されます。
データ量が少なければ、インデックスを参照するよりもフルスキャンした方が早いこともあるからです。
ここでいうデータ量が少ないというのは、目安として1万レコード以下のことを指します。

カーティナリティの高い列

カーディナリティとは

カーティナリティとは、その列が取り得る値の種類を指します。
例えば、性別は男と女の2種なので、カーディナリティは低いと言えます。
一方、商品IDなどは、商品の数だけ存在するのでカーティナリティは高いと言えます。

カーティナリティの高低の判断は、その列をキーに絞り込みを行った結果、全体の5％程度に絞れるかどうかが一つの基準になります。
例えば、性別：男で絞った場合、ほぼ性別が半々と言う前提であれば、50％に絞れます。
これは、5％よりもずっと大きいので、カーディナリティは低いと言えます。
つまり、絞り切れていないということですね。
一方、商品IDであれば、例えば商品が100個あるとすれば、単一の商品IDで全体の1%に絞れます。
これは十分に絞り込まれていますので、カーディナリティが高いと言えます。
(ただし、商品が2種類しかない場合などは50％になるのでカーディナリティは低いと言えます。)

複数インデックスのカーティナリティの考え方

複数のカラムにインデックスを作成する場合、カーディナリティはその合計として考えられます。
例えば、a,b,c列にインデックスを作成するとして、それぞれのカーディナリティが2,10,5とします。
単体ではカーディナリティは低いが、複合で絞り込みを行うとした場合は、カーディナリティは十分高くなります。

インデックスが上手く機能しないケース

以下のようなケースはインデックスが上手く機能しない場合があります。

値の分布に極端な偏りがある場合

カーディナリティが高くても、特定の値に偏りがある場合は、インデックスの効果を十分に発揮できない可能性があります。
例えば、1～100の値を取るが、レコードの99%の値が100であった場合、絞り込みを行っても広範囲(多数のレコード)を処理しないといけないので、処理速度はそこまで向上しません。
(しかし、100以外の値を検索するというような処理が業務上多く発生するのであれば、このインデックスは効果を発揮します。)

SQL中でカラムに演算を行う

例えば、

price * 1.1 >= 1000

price >= 1000 / 1.08

NULL値のレコードに対するインデックスは保持していない

NULL値のレコードに対するインデックスは作成されません。
そのため、「IS NULL」などはインデックスが効かず、処理が低速となる場合があります。
(※ただし、DBMSによっては効くものもあるようです。)

否定条件

<> や != などの条件はインデックスが効きません。
対象となるデータが多すぎるためです。
例えば、商品IDが1000種類あって、

item_id <> 1

OR条件やIN句の使用

OR条件やIN句を使用するとインデックスが使用されないため(※)低速となります。

item_id = '0001' OR item_id = '0002'

item_id IN ('0001', '0002')

これをインデックスを使用するようにするためには以下のようにUNIONを使って書き換えます。

SELECT * FROM item WHERE item_id = '0001'
UNION ALL
SELECT * FROM item WHERE item_id = '0002';

※ただしDBMSによってはインデックスが効くものもあるようです。
パフォーマンスが気になる場合は、統計情報を確認するのが良いでしょう。

カラムに対して型変換をかける

例えば、col1は文字列型だとします。
以下の場合、SQL中でcol1に対して型変換がかかるため、インデックスが効かなくなります。
×：WHERE col1 = 10
以下の場合は問題ありません。
○：WHERE col1 = '10'

その他インデックス作成時の注意点

主キーや一意制約キーには作成不要

主キーや一意制約キーは重複しているかをDBMSで判定する必要があるため、自動的にインデックスが作成されます。
したがって、手動で作成する必要はありません。

インデックスは更新速度を低下させる

インデックスは検索の速度を向上させるが、一方で更新速度を低下させます。
データに対して変更が加わった場合、同時にインデックスも更新する必要があるためです。
そのため、テーブル内のインデックスの数に応じて更新速度は劣化していきます。
無駄なインデックスは作成しないようにする必要があります。