【データベース設計】インデックス(索引)の基本

インデックスとは、データベースのテーブルに設定できる「索引(さくいん）」のことで、活用することで検索速度が格段に上がる場合があります。

インデックスの特徴

列ごとに作られる。

特定の列に対して、設定します。

高速になる場合が多い。

インデックスが設定されている列に対して、検索が行われた場合は、高速になることが多いです。

ただ、検索内容によっては、インデックスが適用されず、高速化されない場合もあります。

名前を付ける。

インデックスには名前をつけます。

インデックスの種類

ユニーク索引

UNIQUEキーを付加した列に関しては自動的に付加される索引です。

非ユニーク索引

UNIQUEじゃない列に対しても索引を設定できます。ユニークじゃないとしてもある程度データを絞れるのであれば索引の効果はあるようです。

インデックスを作成するには？

「CREATE INDEX文」を使います。

CREATE INDEX インデックス名 ON テーブル名(列名)

インデックスの効果を得られやすい典型例

インデックスは、付加すれば必ず高速化されるわけではありません。ただ、効果を得られやすい典型的なパターンはあるので、参考にしてみて下さい。

WHRE句の絞込み

WHERE句の絞込み時に、使用している列に関しては効果を得られやすいです。

SELECT * FROM USER_MASTER
WHERE USER_ID = '1001'

上記の例で言えば、「USER_ID」へインデックスを貼れば、効果を得られやすい。

ただし、単純な文字列の検索の場合ですが、効果を得る上では、検索の種類によって、下記の制約があるので、注意です。

効果を得られる上での制約

検索の種類	効果
完全一致検索	インデックスが確実に動作し、高速に検索が可能。
前方一致検索	インデックスが動作し、高速に検索できる場合もある。（例えば、１文字だけの指定で絞り込みが不十分の場合は動作をしません。検索条件に最低文字数の制約を科す等で十分に絞り込む必要があります。）
部分一致検索	インデックスを利用できない。
後方一致検索	インデックスを利用できない。

ORDER BY句による並び替え

ORDER BY句による並び替え時にも、インデックスは効果があります。

SELECT * FROM USER_MASTER
ORDER BY USER_ID;

JOIN句による結合条件

結合処理では、内部的に並び替えを行っているので、ORDER BY句同様に効果があります。

SELECT * FROM USER_MASTER
JOIN AUTHORIZATION
ON USER_MASTER.USER_ID = AUTHORIZATION.USER_ID

インデックスを使う上での注意点

データ件数が少ない場合はインデックスは不要

例えば、都道府県マスタのようにデータが47件しかないテーブルの場合はデータが少なすぎて性能には影響しないのでインデックスは不要です。

更新系SQLのオーバーヘッドが増える。

テーブルのデータが変更されると、その都度インデックスも書き換える必要があるので、更新系SQL(INSERT、UPDATE、DELETE)の負荷が増加します。

なので、いくら索引を設定すると、検索速度が向上するからといって、むやみやたらに色々な列に適用するのは避けましょう！

後は、索引を保存するために、ディスク容量を取る点もデメリットですが、大したデメリットではないでしょう。

複合インデックスは順番も大事

where カラムA,カラムBとなっていた場合にカラムBだけインデックスに指定してもインデックスは動きません。カラムAとカラムBの複合インデックスを作成するようにしましょう。

where句にNULLや関数を利用した場合はインデックスが効かない。

ただし、ファンクションインデックスという物を利用した場合は効く可能性があります。