◆インデックスの構造と内部動作２

• 自動的に作成されるインデックス（主キー制約と UNIQUE 制約）
• クラスター化がある場合の非クラスター化インデックスの内部構造
• カバリング インデックス（複合インデックス）
• 付加列インデックス（Include オプション）

自動的に作成されるインデックス（主キー制約と UNIQUE 制約）

PRIMARY KEY（主キー）制約または UNIQUE 制約を設定している場合は、自動的にインデックスが作成されます。デフォルトでは、PRIMARY KEY制約の場合には「クラスター化インデックス」、UNIQUE制約の場合には「非クラスター化インデックス」が作成されて、インデックスの名前は制約の名前と同じになります。
どちらの制約もデータを一意に保つためのものなので、この列を検索条件に指定した場合は、必ず １件の検索結果が返ります。インデックスは、大量のデータの中から数件のデータを取り出すときに最も効果を発揮するので、一意な値を持つ列に対して作成されたインデックスは、最も効果があります。また、主キーは検索条件としても頻繁に使用されるものなので、インデックスが自動的に作成されることはうれしいことです。

Let's Try

1. クエリ エディターへ次のように入力して、「社員番号」列を主キーとする「社員2」という名前のテーブルを作成します。
   

   CREATE TABLE 社員2 (社員番号 int PRIMARY KEY, 姓 char(20), 名 char(20))

   
   
2. 作成後、オブジェクト エクスプローラーで、「社員2」テーブルの［インデックス］フォルダーを展開して、「PK_社員2_～（クラスター化）」というインデックスが作成されていることを確認してみましょう。
   
   

クラスター化がある場合の非クラスター化インデックスの内部構造

ここでは、クラスター化インデックスと非クラスター化インデックスの両方が存在する場合について説明します。
クラスター化インデックスを作成すると、非クラスター化インデックスの構造が変更され、リーフレベルへ格納されるポインターが「行識別子」から「クラスター化インデックスの値」へ変更されます。たとえば、社員テーブルの「社員番号」列へクラスター化インデックスを作成し、「姓」列に、非クラスター化インデックスを作成している場合は、次の図のようなインデックス構造になります。

このような構造になるメリットは、「姓」と「社員番号」列のみを取得する検索を非常に高速に取得できるようになる点です。

Let's Try

1. まずは、「姓」列へ作成していたクラスター化インデックスを削除します。
   

   DROP INDEX 社員.index_姓

2. 次に、「社員番号」列に対してクラスター化インデックスを作成します。
   

   CREATE CLUSTERED INDEX index_社員番号 ON 社員(社員番号)

3. 続いて、「姓」列に対して非クラスター化インデックスを作成します。
   

   CREATE INDEX index_姓 ON 社員(姓)

4. 次に、「Aoki」さんの「社員番号」と「姓」列のみを取得するようにデータを検索してみましょう。
   

   SELECT 社員番号, 姓 FROM 社員 WHERE 姓 = 'Aoki'

   
   
   グラフィカル実行プランを表示すると、「姓」列へ作成した非クラスター化インデックス「社員.index_姓」の Index Seek のみが実行されたことを確認できます。RID Lookupアイコンが表示されないことがポイントです。「社員番号」と「姓」列のデータは、非クラスター化インデックスのリーフ レベルへ格納されているので、実際のデータを探すことなく（RID Lookup をすることなく）、インデックスの Seek のみで検索が完了しているのです（検索が高速に実行できています）。
5. 続いて、「社員番号」と「姓」列以外の列として「名」列も取得してみましょう。
   

   SELECT 社員番号, 姓, 名 FROM 社員 WHERE 姓 = 'Aoki'

   
   
   
   今度は、グラフィカル実行プランに「キー参照」アイコンが表示されます。これは、RID Lookup と同様に、実際のデータを参照したという内部動作です。RID Lookup からキー参照へ変わったのは、実際のデータへのポインターがクラスター化インデックスの値（キー）に変更されたためです。「名」列の値は、非クラスター化インデックスのリーフ レベルに格納されていないので、取得するには、実データへアクセスしなければなりません。
   

カバリング インデックス（複合インデックス）

カバリング インデックス（Covering Index）は、非クラスター化インデックスのリーフレベルへ検索で取得したいデータを格納して、キー参照（または RID Lookup）を行うことなく、Index Seekのみで完了する、つまり検索をインデックスのみでカバーするテクニックです。
たとえば、前の Step で試した次の検索は、「名」列のデータを取得するために、「キー参照」が行われていました。

SELECT 社員番号, 姓, 名 FROM 社員 WHERE 姓 = 'Aoki'

このような場合に、「姓」と「名」列のカバリング インデックスを作成しておけば、リーフ レベルへ「名」列の値を格納できるようになるので、実際のデータを探す（キー参照をする）ことなく、インデックスのみで検索を完了させることができます。これにより、パフォーマンスを向上させることができます。

カバリング インデックスの作成

カバリング インデックスを作成するには、CREATE INDEX ステートメントを次のように利用します。

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

列名をカンマで区切って指定することで、それらの列をインデックスのリーフ ページへ含めることができるようになります。

GUI での作成

カバリング インデックスを、オブジェクト エクスプローラーから作成する場合は、次のように操作します。

インデックスを作成する列の選択時に、複数の列を選択することで、カバリング インデックスを作成することができます。

Let's Try

それでは、カバリング インデックスを作成してみましょう。

1. まずは、「姓」列へ作成していた非クラスター化インデックスを削除します。
   

   DROP INDEX 社員.index_姓

2. 次に、「姓」と「名」列を含めたカバリング インデックスを作成してみましょう。
   

   CREATE INDEX index_姓名 ON 社員(姓, 名)

   
   
3. 作成が完了したら、姓が「Aoki」さんの「社員番号」と「姓」、「名」列を取得する検索を実行してみましょう。
   

   SELECT 社員番号, 姓, 名 FROM 社員 WHERE 姓 = 'Aoki'

   
   

カバリング インデックスの注意

カバリング インデックスを作成するときは、列の順番に注意する必要があります。(姓, 名) と指定する場合と (名, 姓) と指定する場合では、構造が異なります。カバリング インデックスは、

一番左へ指定した列でツリー構造が作成されるので、WHERE 句の検索条件で指定される列を、一番左へ指定しておくようにします。
2つ目以降の列データは、リーフ レベルへ格納されますが、その分余計にディスク領域を消費することにも注意する必要があります。特に追加した列のデータ サイズが大きい場合には要注意です。また、実際のデータの更新時のインデックス自身を更新するオーバー ヘッドもデータ サイズが増える分だけ大きくなります。

カバリング インデックスの正確な内部構造

カバリング インデックスで 2つ目以降へ指定した列データは、正確には、次の図のように、中間ページとルート ページにも格納されます。また、中間ページとルート ページには、行識別子（RID）またはクラスター化インデックスのキー値も格納されます。

このように、カバリング インデックスでは、中間ページとルート ページにも 2つ目以降へ指定した列データ格納するので、その列データのサイズが大きい場合には、非常にインデックス サイズが大きくなってしまい、パフォーマンスの低下に繋がるケースがあります。
これを解消してくれる機能が、次に説明する「付加列インデックス」（Include オプション）です。付加列インデックスの場合は、次のように中間とルートへ値を格納することなく、リーフのみへ値を格納できるようになります。

付加列インデックスは、カバリング インデックスの欠点を補うために搭載された機能です。

付加列インデックス（Include オプション）

付加列インデックス

付加列インデックスは、「Include オプション」とも呼ばれ、インデックスのリーフ レベルへ指定した列を含める（Include する）ことができる機能で、SQL Server 2005 から提供されました。カバリング インデックス (複合インデックス) との違いは、前のページにも掲載しましたが、次の図のとおりです。

カバリング インデックスは、インデックスのリーフ レベルだけでなく、ルートと中間ページにもカバリングへ含めた列（「名」列）の値を格納しますが、付加列インデックスの場合はリーフ レベルにしか値を格納しません。これにより、インデックス サイズを小さくすることができます。インデックス サイズを小さくできれば、インデックスの Seek および Scan で読み取るページ数 (I/O 数) を少なくできるので、パフォーマンスが向上します。
付加列インデックスは、カバリング インデックス（複合インデックス）の欠点を補った機能なので、どちらを利用するかを迷ったら、迷わず付加列インデックスを利用することをお勧めします。

付加列インデックスの作成

付加列インデックスを作成するには、CREATE INDEX ステートメントを次のように利用します。

CREATE INDEX index_姓名 ON 社員(列名) INCLUDE(リーフへ含めたい列名1, 列名2, …)

INCLUDE キーワードを付けて、含めたい列をカッコ内へ記述します。

GUI でのインデックスの作成

付加列インデックスを、オブジェクト エクスプローラーから作成する場合は、次のように操作します。

［新しいインデックス］ダイアログで、インデックスを作成する列を追加したあとに、次のように［含まれている列］タブをクリックして開きます。

Let's Try

それでは、付加列インデックスを作成してみましょう。

1. まずは、前の Step で作成したカバリング インデックス「index_姓名」を削除します。
   

   DROP INDEX 社員.index_姓名

2. 次に、「姓」列に対して、「名」列を含む付加列インデックスを作成します。
   

   CREATE INDEX index_姓名 ON 社員(姓) INCLUDE(名)

3. 作成が完了したら、カバリング インデックスのときと同じ検索を実行してみましょう。
   

   SELECT 社員番号, 姓, 名FROM 社員WHERE 姓 = 'Aoki'

   
   
   結果は、カバリング インデックスのときと同様、Index Seek のみが実行されたことを確認できます。

Note： カバリングと付加列インデックスの中身の参照
隠しコマンドの DBCC IND と DBCC PAGE を利用して、実際のカバリング インデックスの中身（中間またはルート ページ）を参照すると、次のようになります。

カバリング インデックスは、中間またはリーフ ページにも、「名」列が含まれていることを確認できます。
これに対して、付加列インデックスの中身を参照すると、次のようになります。

付加列インデックスの場合は、「名」列が含まれていないことを確認できます。
これにより、付加列インデックスの方がカバリング インデックスよりも、インデックスのサイズを小さくすることができます。この差はデータのサイズが大きければ大きいほど差が顕著になります。
前述したように、インデックス サイズが小さくなれば、インデックスの Seek および Scan で読み取るページ数 (I/O 数) を少なくできるので、付加列インデックスの方がパフォーマンスを向上させることができます。