ユニークインデックスについて

質問

仕事の関係で、テーブル定義の際にユニークインデックスをどの列の組み合わせにするのか考える必要があるのですが、今までユニークインデックスの存在自体知らず、困っています。

自分自身でネット等で調べた結果以下のことは理解できました。

　１．ユニークインデックスで指定された列は値が一意でなければならない。

　２．主キーの列にはNULL値は不可だが、ユニークインデックスの列はNULL値も可。

　３．主キーは一つのテーブルにつき、一つしか設定できないが、
　　　ユニークインデックスは複数設定できる。（同じ列は指定できない）

　４．主キー設定時、実は暗黙的にユニークインデックスとNOT NULL制約が作成されている。

ここまでは分かったのですが、主キーとは別で、明示的にユニークインデックスを指定する必要性とはなんなのでしょうか？

主キーとは別で明示的に指定した方が良い場合と、その実例をどなたか教えて頂けないでしょうか？
よろしくお願いします。

回答No.1

entree · ベストアンサー

既に確認されている4 項目の認識で問題ありません。

> ここまでは分かったのですが、主キーとは別で、明示的にユニークインデックスを
> 指定する必要性とはなんなのでしょうか？

ユニークインデックスとは制約と思っていただいて構いません。
一意でないデータが入らないことを保証するためのものであり、
索引を付与することではありません。

なので、これは「NOT NULL 制約をなぜ追加しますか？」と
いう質問の回答と同じようなことです。

> 主キーとは別で明示的に指定した方が良い場合と、その実例をどなたか
> 教えて頂けないでしょうか？

整合性のチェックをアプリ側で実装するのではなく、DB側にあらかじめ用意された
機能を使うことで開発コストやバグを抑えるといった利点があるでしょう。

ただ、暗黙的に索引が追加されてしまうため、更新時のパフォーマンスが悪くなるとか
そういった問題も発生しえるわけです。

これは「参照制約をつけるべきか否か」という質問と同じようなことでしょう。
正規化やDOA の観点からはきっちりと制約を付けることは望ましいけど、
パフォーマンスやメンテナンスの容易さ（※）の兼ね合いが大事ということに
なるかと思います。

※制約がビッシリ存在するとデータを手修正するのが大変ですよね？

お礼

ご回答ありがとうございます。

ユニークインデックスを付けた方がよい場合と、
あえて付けない方がよい場合もあるのですね。

回答No.4

kaihatusha

＞主キーとは別で明示的に指定した方が良い場合
インデックスとはDBでデータを検索する際の索引です。

ですのでデータを抽出する条件句(Where句)や結合句で指定されない列に指定しても意味はありません。

またインデックスを指定する列が1,または2のように索引をつけても意味がないような種類のデータしか格納されない場合はインデックスを付加しても意味がありません。

またデータ件数がすくないテーブルに対して索引をつけてもパフォーマンスは向上しません。

またインデックスはインデックス用の領域を持ちます。これはUpdateなど更新が実行されると領域が肥
大化していきます。この領域はOracleなどではテーブルの再作成などを実行しないとクリアされない為、断片化の原因となりパフォーマンス悪化の原因となる事がある為、注意が必要です。

上記を踏まえて索引を付けるのはデータ件数がある程度になるテーブルでかつ、データ抽出の際の条件句や結合句となる列、でかつ、ある程度のデータ種類のある列である必要があります。

また上記条件を満たしていてもインデックスを付加したら必ずパフォーマンスが向上するという訳ではないのでインデックス付加後と前とでSQLの実行計画やコスト、実行速度などを計測して付加を判断するという事も必要になります。

いろいろと面倒ですが不要なインデックスを付ければパフォーマンスの悪化を招くこともありますので気をつけましょう。

お礼

ご回答ありがとうございます。

Updateのたびにインデックス用の領域が肥大するということを初めて知りました。
勉強になります。

回答No.3

いけだ ひろし（@ike-2000）

#2さんにちょっと追記します。
>インデックスは検索のパフォーマンスをあげるために作成するものです。
>その項目が一意になるのであれば、ユニークインデックスにすればいいと思います。

DB屋さんに聞いた話ですがユニークインデックスの効果は絶大だそうです。ユニークであれば一発で求まるためです。重複であればインデックスからハッシュ的な別途検索処理が動くためパフォーマンスが低下するそうです。
そのため、「インデックスを付けるなら無理してでもユニークインデックスにしろ。」と言われたことがあります。通番を振るなどして無理してユニークキーにしたことがあります。

ご参考まで。

お礼

ご回答ありがとうございます。

その時の環境次第な面もあるかもしれませんが、
パフォーマンス的にも、ユニークインデックスが有利な場合が多いのでしょうね。

回答No.2

yamada_g

インデックスは検索のパフォーマンスをあげるために作成するものです。
その項目が一意になるのであれば、ユニークインデックスにすればいいと思います。

重複する値を許さないという意味であるならユニーク制約が正しいと思います。
（ユニーク制約を設定した場合、基本的にはユニークインデックスが作成されますが）

ですので、ユニーク制約を指定するという意味で考えてみます。
>テーブル定義の際にユニークインデックスをどの列の組み合わせにするのか考える
それは、要件で決まることなのではないのでしょうか？
業務上、その（それらの）項目が重複してはいけないならユニーク制約を付けましょうとしか言えない気がします。
たとえば、何かの会員登録などで「主キーはユーザ名だけど同一メールアドレスでの複数登録は許さない」
といった場合は、主キーとは別にメールアドレスにユニーク制約を付ける必要があります。

もしくは、テーブルの主キーにはサロゲートキーを使い、ナチュラルキーにあたる項目にはユニーク制約を付ける場合もあります。
これはモデリングの手法によりますね。

自分はDB設計をしたりする立場ではないので妥当な回答であるか分かりませんが、参考になれば幸いです。

お礼

ご回答ありがとうございます。

分かりやすい例を示していただきありがとうございます。
主キー以外でも一意にしたい項目がある場合もあるのですね。