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

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

インデックスの特徴

列ごとに作られる。

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

高速になる場合が多い。

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

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

名前を付ける。

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

インデックスの種類

ユニーク索引

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や関数を利用した場合はインデックスが効かない。

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

インデックスが使えない場合の対処法

そもそもインデックスが使えない状況もあるでしょう。

アプリ側で対処する。

もちろん、好き勝手に検索できるのが一番良いのですが、性能のためにはできるだけ譲歩をしてもらうのが望ましいでしょう。

できるだけ絞り込ませるようにする。

絞り込みが効くように「受付ID」で検索するときは、「受付日」も必須入力にするなど。

範囲検索はできるだけ合意をとる。

例えば、「期間検索」という要件がある場合は、1ヶ月間までにするとか。（もし、1年分検索したいのにと言われたら、1ヶ月分を12回検索して統合してくださいと運用回避をお願いする感じです。）

データマートによる対処

「特定のクエリで必要とされるデータのみ保持する」という相対的に小さいテーブルのことです。アクセス対象テーブルサイズを小さくすることでI/O量を減らすのが目的。

データマートの注意点

データマートは、特に運用回りで考慮するポイントが多いので注意です。

データの鮮度が悪くならないようにする。

オリジナルテーブルの部分的なコピーになります。頻繁に更新することになると鮮度は上がりますが、パフォーマンスは落ちます。

大抵の場合、1日一回夜間バッチなどで更新する作戦などが採用されることが多いです。その場合、1日前の情報でも良いかという要件次第になるでしょう。

データマートのサイズ

列数やレコード数が削減できなければあまりパフォーマンスに効果はないと言えるでしょう。

データマートの数

あまりの数が多いのはアンチパターンといえるでしょう。どこにどのテーブルが紐づいているのかが業務要件には出てこないのでわからなくなりますし、ストレージ容量も逼迫します。

バッチウィンドウ

データマートを作るにあたっても時間を要します。バッチの時間も逼迫します。

インデックスオンリースキャンを使う。

MySQLを始め様々なDBでサポートはされている。

注意点

列が追加されたら使えなくなる。なので、保守性は低い技術ではある。