制約とインデックス②

制約とインデックス②

2.インデックス

(1).インデックスとは

データベースからデータを取り出すには、一般に2つの方法があります。一つはテーブルスキャンまたは全走操作と呼ばれる方式です。この方式では全ての行にアクセスします。目的の行が見つかるまで、全ての行を1行づつ調べなければならないので効率の良い方法ではありません。
もうひとつは、索引を使う方法です。索引には、どの行がどこにあるかを示す情報が登録されています。この方式では、最初に索引にアクセスし、目的の行の格納場所を知ることによって、直接アクセスが可能となります。
索引の働きにをする機能には、次の３つがあります。

・主キー制約
・一意制約
・インデックス

ある列に主キー制約または一意制約が設定されると、その列の値に基づく索引が自動的に作成されます。一方、インデックスの場合には、CREATE INDEX文を実行し、対象となる列を指定することによって、明示的にインデックスオブジェクトを作成します。いずれの場合にも、作成される索引では、対象となる列の値から、その値を持つ行がどこにあるかを知ることができます。また、作成された索引は、いずれの場合もインデックスリーフというデータベースの中の特別な場所に作成されます。主キー制約、一意制約、インデックスによる索引には次のような違いがあります。

・主キー制約と一意制約は、一意性による行の特定と検索の高速化という、2つの目的を持っています。一方、インデックスは、検索の高速化だけを目的としています。その為、インデックスの対象となる列には、一意ではない値を格納することができます。
・主キー制約は、1つの表に1つしか設定できませんが、一意制約とインデックスは1つの表に複数設定することができます。
・主キー制約の対象となる列には、NULL値を格納できませんが、一意制約とインデックスの対象となる列には、NULL値を格納できます。

(2).インデックスの作成

インデックスを作成するには、CREATE INDEX文を使用します。構文は以下のとおりとなります。

CREATE [UNIQUE] INDEX <インデックス名>
ON <表名> (<列名1> [ASC| DESC],<列名2> [ASC| DESC] …)

UNIQUEキーワードを指定する場合には、対象となる列の値は一意でなければなりません。

(3).インデックスの削除

インデックスを削除するには、DROP INDEX文を使用します。構文は以下のとおりとなります。

DROP INDEX <インデックス名>

(4).複合インデックス

前項で主キーは複数の列に設定することが可能と説明しましたが、インデックスも複数の列を対象にすることができます。複数の列の組み合わせを対象とするインデックスのことを複合インデックスといいます。

(5).インデックス適用の目安

インデックスを提供する際の指針として以下を参考にするとよいでしょう。

・表の結合条件として使う列に対しては、必ずインデックスを作成します。表を結合するSQL文では、結合条件をWHERE句に指定する為、インデックスを作成しておけば大幅にパフォーマンスが向上します。

・小さな表に対してインデックスを作成しても、パフォーマンスは改善されません。

・多くの異なる値が格納されている列にインデックスを作成すると、大幅にパフォーマンスが向上します。

・目安として、表の総行数の10~15%未満の行数が結果セットとして返されるクエリーでは、インデックスの効果が発揮されます。

・表に対してインデックスを作成すると、データ検索のパフォーマンスが向上しますが、更新のパフォーマンスは低下します。リアルタイムでデータの更新が頻発する表に対してはインデックスの設定は控えた方がいいでしょう。

・インデックスの情報は、表のデータとは別に格納されるため、データベース領域を独自に消費します。その為、データベースのサイズを見積もるときには、インデックスのファイルサイズも考慮しなければなりません。

・インデックスは、表に対して作成します。ビューに対しては作成できません。

・インデックスの情報は、表とは別のディスクに格納することを推奨します。別々に保存しておけば更新が発生した際に同時に更新処理を行うことができるので処理の高速化が期待できます。