インデックス作成における３つの設計指針

前置きが長いのは苦手なので、早速結論です。以下の３つの設計指針が存在します。

データ量が多い場合にインデックスを作成しましょう。データ量が少ない場合はインデックスを作成しても効果がありません。

目安としては「レコード数が1万件以上」です。

※ただしストレージやサーバーの性能など環境要因によって変わるために、あくまでも目安です。

インデックスを作るときは、カーディナリティの高い列を選ぶようにしましょう。

こちらの目安としては、特定のキー値を指定したときに、全体のレコード数の5%程度に絞り込めるだけのカーディナリティがあることです。

例えば３６５日のうち１日を指定するSELECT文を考えるとすれば、(1/365)*100=0.3％となるため、このカラムにインデックスを作成する意味はある、と判断できます。

カーディナリティとは、特定の列の値がどれくらいの種類を持つかということを表す概念です。

例えば性別というカラムであれば、「男性」「女性」「その他」の３種類が想定されるので、カーディナリティは３となります。

これは当然ですが、SQL文で検索条件や結合条件として使用されているカラムに対してインデックスを作成するようにしましょう。

インデックスに関するその他の注意事項

上記のように「主キーおよび一意制約の列には作成が不要」です。

DBMSは主キー制約や一意制約を作成するさい、内部的にはインデックスを作成しているからです。

インデックスは作成すればするほど、更新性能を劣化させてしまいます。これはインデックスによって得られる恩恵とトレードオフであるため、極力無駄なインデックスは作成しないようにしましょう。

ちなみに更新性能を劣化させるのは、インデックスがテーブルとは独立してDBMS内に存在しているためです。これによりインデックスが作成されている対象のカラム値が更新されると、インデックス内に保持している値も変えなければいけなくなります。

インデックスはテーブルのデータが更新されていくと、長期的には構造が崩れて劣化していくため、インデックスは定期的にメンテナンス（再作成）を行いましょう。

このコマンドは各種DBによって異なるので、適宜マニュアルを参照してください。

以上が、インデックス設計の3つの指針です。

ちなみに今回の記事は以下の書籍をもとにしているので、詳しく知りたい方はよければどうぞ。