データベースの索引について詳しい方教えてください

Question

データベースについて色々調べたところ以下のような索引があることがわかりました。

・主キー索引
・ユニーク索引
・非ユニーク索引
・クラスタ索引
・非クラスタ索引
（他にも種類があればぜひ教えてください）

これらの索引について質問です。

1. 主キー索引・ユニーク索引はテーブル定義時にPRIMARY KEY制約・UNIQUE制約を付与した列に関しては自動でインデックスが作られるのでしょうか？

2. PRIMARY KEY制約・UNIQUE制約をテーブル定義時に付与していない列に関して主キー索引・ユニーク索引は作れるのでしょうか？

3. 索引はどのように使い分けるのでしょうか？

索引の順番と実データの順番を同じにするクラスタ索引がもっとも効率がよく思われます。
それ以外の索引（非ユニーク索引、非クラスタ索引）を用いるのはどのようなケースでしょうか？

根本的な勘違いがあるかもしれません。
部分的な回答でも結構ですのでよろしくおねがいしますm(_ _)m

Answer 1

1. 主キー索引・ユニーク索引はテーブル定義時にPRIMARY KEY制約・UNIQUE制約を付与した列に関しては自動でインデックスが作られるのでしょうか？

INSERTするキーが既に存在しているものかどうかを確認するには索引を見るのが効率的なので、ほとんどのRDBMSでは自動的に索引が作られます。もしくは、既存の索引で利用できるものがあれば新たに作成せずそれを確認に使用するようになるケースもあります。

1. PRIMARY KEY制約・UNIQUE制約をテーブル定義時に付与していない列に関して主キー索引・ユニーク索引は作れるのでしょうか？

通常ALTER TABLE文やCREATE INDEX文で後から追加可能です。

1. 索引はどのように使い分けるのでしょうか？

索引の順番と実データの順番を同じにするクラスタ索引がもっとも効率がよく思われます。
それ以外の索引（非ユニーク索引、非クラスタ索引）を用いるのはどのようなケースでしょうか？

クラスタ索引は性能を最も向上させたいSQLの結果の順序に合わせて作成します。テーブルのレコードはそのクラスタ索引の順序に並んでいるため、それ以外の索引を作成しようとするとそれはどうしても非クラスタ索引ということになってしまいます。

非ユニーク索引はデータを一意に特定することはできなくても、テーブル全体から一部のレコードを効率的に抽出することが可能であれば作成する意味があります。例えば、従業員テーブルの部署カラムに索引を張ると、通常部署に従業員は複数いるため非ユニーク索引となりますが、特定の部署の従業員レコードだけ抽出するようなSQLを効率化できます。

Comments

[jimyo]

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

>クラスタ索引は性能を最も向上させたいSQLの結果の順序に合わせて作成します。テーブルのレコードはそのクラスタ索引の順序に並んでいるため、それ以外の索引を作成しようとするとそれはどうしても非クラスタ索引ということになってしまいます。

すこし理解できなかったのですが、「クラスタ索引はテーブル内のデータが空のとき（一番最初）にしか作れない」ということでしょうか？
例えば テーブル名「ユーザー」、カラム「ID、名前、マイナンバー」というデータベースで「マイナンバー」カラムに対してクラスタ索引を作る場合を考えます。
すでにいくつかデータがある場合
0, 田中太郎, 100
1, 山田花子, 97
2, 佐藤太郎, 102
3, 加藤花子, 15

もうマイナンバーカラムについては整列して挿入されてないのでクラスタ索引は作れないということでしょうか？

[SVC34]

通常は後から付け直すことが可能です。その場合、クラスタ索引定義後（同時もしくは表再編時）にデータは並びなおされます。

なお、「クラスタ索引」はRDBMSによって実装や言葉の意味が異なるので注意が必要です。

DB2、PostgreSQLでは表のレコードがクラスタ索引の順に整列されます。Oracle、SQL Server、MySQLではキーだけでなくレコード全体をB+ツリー構造で保持した索引をクラスタ索引と呼びます（さらにOracleでは「索引構成表」という言葉を用いています）。

DB2、SQL Server、PostgreSQLでは任意の索引をクラスタ索引に指定可能ですが、OracleやMySQLでは主キーに対してのみ作成可能です。

[jimyo]

なるほど！ありがとうございました

Answer 2

SVC34さんが書かれているのでわざわざ追記するほどでもないので取り消します。