こちらの記事は
「いつも何となくカラムにインデックスを作成している」
「インデックスをどのカラムに作ればいいのかわからない」
という方に向けた記事です。
過去の私がそうだったので、この記事が上記のような方にとって役に立つ記事になれば嬉しい限りです。
※こちらの記事ではB-treeインデックスを対象としています。
インデックス作成における３つの設計指針
前置きが長いのは苦手なので、早速結論です。以下の３つの設計指針が存在します。
指針1. 大規模なテーブルに対して作成する
データ量が多い場合にインデックスを作成しましょう。データ量が少ない場合はインデックスを作成しても効果がありません。
目安としては「レコード数が1万件以上」です。
※ただしストレージやサーバーの性能など環境要因によって変わるために、あくまでも目安です。
指針2. カーディナリティの高い列に作成する
インデックスを作るときは、カーディナリティの高い列を選ぶようにしましょう。
こちらの目安としては、特定のキー値を指定したときに、全体のレコード数の5%程度に絞り込めるだけのカーディナリティがあることです。
例えば３６５日のうち１日を指定するSELECT文を考えるとすれば、(1/365)*100=0.3％となるため、このカラムにインデックスを作成する意味はある、と判断できます。
補足：カーディナリティとは？
カーディナリティとは、特定の列の値がどれくらいの種類を持つかということを表す概念です。
例えば性別というカラムであれば、「男性」「女性」「その他」の３種類が想定されるので、カーディナリティは３となります。
指針3. SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する
これは当然ですが、SQL文で検索条件や結合条件として使用されているカラムに対してインデックスを作成するようにしましょう。
インデックスに関するその他の注意事項
主キーおよび一意制約の列には作成が不要
上記のように「主キーおよび一意制約の列には作成が不要」です。
DBMSは主キー制約や一意制約を作成するさい、内部的にはインデックスを作成しているからです。
インデックスは更新性能を劣化させる
インデックスは作成すればするほど、更新性能を劣化させてしまいます。これはインデックスによって得られる恩恵とトレードオフであるため、極力無駄なインデックスは作成しないようにしましょう。
ちなみに更新性能を劣化させるのは、インデックスがテーブルとは独立してDBMS内に存在しているためです。これによりインデックスが作成されている対象のカラム値が更新されると、インデックス内に保持している値も変えなければいけなくなります。
定期的なメンテナンス（再作成）を行う
インデックスはテーブルのデータが更新されていくと、長期的には構造が崩れて劣化していくため、インデックスは定期的にメンテナンス（再作成）を行いましょう。
このコマンドは各種DBによって異なるので、適宜マニュアルを参照してください。
終わりに
以上が、インデックス設計の3つの指針です。
ちなみに今回の記事は以下の書籍をもとにしているので、詳しく知りたい方はよければどうぞ。