第 1 回 「インデックスアーキテクチャとデータアクセス方法を理解する」～ システム構築 ～
NEC
E ラーニング事業部
鈴木 智行
2001 年 11 月 22 日
目次
.gif){kind=icon}
1-1. インデックスの紹介
1-2. クラスタ化インデックスの構造
1-3. 非クラスタ化インデックスの構造 Part1
1-4. 非クラスタ化インデックスの構造 Part2
1-5. 制約とインデックス
1-6. sysindexes システムテーブルの理解
1-7. クラスタ化インデックスを使用したデータアクセス方法
1-8. 非クラスタ化インデックスを使用したデータアクセス方法 Part1
1-9. 非クラスタ化インデックスを使用したデータアクセス方法 Part2
1-10. インデックスを使用しないデータアクセス方法
1-1. インデックスの紹介
データベースへのデータアクセス方法は大きく分けて２通りあります。
直接、データにアクセスする（テーブルスキャンという）
インデックス（索引）を用いてデータにアクセスする（インデックススキャンという）
一般的に考えるとデータにすばやくアクセスするためには、インデックススキャンが有利です。これはマニュアルを読む場合を例にとると簡単に理解できるでしょう。
例えば皆さんが SQL Server のマニュアルからクエリアナライザについて詳しく知りたい時にはどうするでしょうか？ ほとんどの場合、索引を使ってクエリアナライザについて記述してあるページを調べ、そのページを直接開くことになるでしょう。あまり 1 ページ目から順番にめくっていき、クエリアナライザのページを見つけるということはしないはずです。
しかし、必ずしも索引を用いる方法が有利なわけではありません。例えば SQL Server の試験を受けるときにマニュアルを参照すると思いますが、試験合格のためには特定の分野だけでなく全般的な知識がやはり必要です。そのような場合は索引を用いて調べるよりも 1 ページ目から順番に読んで調べていくのではないでしょうか？ また、もう １ つの例としてはマニュアルを校正する場合です。内容に追加があった場合にはページが変わります。既存に索引が作られていれば、変更後のページに対応するように索引のページも変更しなければいけません。索引の量が多ければ多いほど、大変負担がかかります。
以上のことはデータベースの世界でも同じです。全ての場合においてインデックスがデータアクセスを有利に働かせるものではありません。
ただし、データアクセスを評価する場合にはインデックスを 1 つの大きな要素としてとらえることは必要です。そのためにインデックスを理解することはクエリパフォーマンスを検討するために非常に大事なことなのです。
SQL Server のインデックスは大きく分けて 2 種類あり、クラスタ化インデックスと非クラスタ化インデックスが挙げられます。クラスタ化インデックスは 1 テーブルに対し 1 個まで、非クラスタ化インデックスは 1 テーブルに対し 249 個まで作成可能です。またこのそれぞれのインデックスは、データの一意性を保つ（一意なインデックスという）ようにしたり、複数の列に対してまとめて設定する（複合インデックスという）ことも可能です。
1-2. クラスタ化インデックスの構造
SQL Server のインデックスはすべて図 1-2-1 のような B-Tree 構造にもとづき、 8 KB のインデックスページ（リンクリストと下位層のページのポインタおよびキー値によって構成）に格納されています。 B-Tree の最上位層はルートレベル、最下位層はリーフレベル、ルートレベルとリーフレベルの間のレベルは中間レベルと呼びます（ルートレベルと中間レベルをあわせて非リーフレベルと呼ぶ場合もあります）。
.gif)
図 1-2-1 インデックスは B-Tree 構造に基づいている（図は全体で 3 レベルの場合）
クラスタ化インデックスの特徴は、テーブルでクラスタ化キーを設定するとそのキー値の昇順にデータが並び替えられて、クラスタ化インデックスのリーフレベルが実際のデータページ（リンクリストと実際のデータによって構成）として構成されることです。
すなわちテーブル内の行データはクラスタ化インデックスによって決められた順序に従って、ディスクドライブ内で物理的に配置されることになります。このためクラスタ化インデックスは以下の場合に優位性をもつことがあります。
● クラスタ化キー値で範囲検索をする
● クラスタ化キー値の順番でアクセスする
この理由は、データにアクセスする場合に目的のデータが同じページにある確率が多くなって物理 I/O が少なくなるケースや、大量の行を取り出す場合に連続的な I/O が行われることによって I/O のパフォーマンスが上がるケースなどでおわかりいただけるでしょう。
しかし、クラスタ化インデックスを最初に作成した時点では連続したページであっても、頻繁なデータ更新によってページ分割が発生し、 I/O 処理の連続性が失われる場合があります。このような環境ではクラスタ化インデックスは有効に働きません。
クラスタ化インデックスを有効に利用するためには、 FILL FACTOR オプションや PAD INDEX オプションを使ってリーフページ（クラスタ化インデックスの場合はデータページ）や非リーフページ（インデックスページ）のインデックス初期作成時の充填率を少なく指定してデータ分割をなるべく行わせないようにしたり、 DBCC INDEXDEFRAG ステートメントを使ってインデックスの断片化を解消したり、 DBCC DBREINDEX ステートメントあるいは CREATE INDEX ステートメントの DROP_EXISTING 句を使ってインデックスの再構築を行うことで、インデックスを最適化してインデックスページの連続性を保つことを考慮に入れなければいけません。
1-3. 非クラスタ化インデックスの構造 Part1
SQL Server 2000 （正確には SQL Server 7.0 以降）の非クラスタ化インデックスは、クラスタ化インデックスが存在しない場合とクラスタ化インデックスが存在する場合で大きく構造が異なります。 1-3 ではクラスタ化インデックスが存在しない場合の非クラスタ化インデックスの構造を見ていきましょう。
クラスタ化インデックスが存在しない非クラスタ化インデックスのリーフレベルはインデックスページです。データページはクラスタ化インデックスを作成した場合のデータページとは構造が異なり、リンクリスト（前後ページを指すポインタのリスト）はもちません。このようなクラスタ化インデックスが存在しない場合のデータページの集まりをヒープと呼びます。
ヒープでは、データの行の順番は特定の順序では格納されず、データページにも特定の順序はありません。図 1-3-1 のようにクラスタ化インデックスが存在しない非クラスタ化インデックスでのリーフレベル（インデックスページ）ではポインタとして行識別子（ファイル ID 、ページ ID 、行 ID ）を格納しています。その行識別子を使ってヒープへジャンプし直接、検索対象データを探し出すことになります。
.gif)
図 1-3-1 クラスタ化インデックスが存在しない非クラスタ化インデックスでのリーフレベルは行識別子を格納する
このためクラスタ化インデックスが存在しない非クラスタ化インデックスは、一般的に以下の場合に優位性をもつことがあります。
選択度が高い検索条件を使用する（テーブルから比較的少数のデータを検索する）
この理由は、重複する値を多く含んだ大きなテーブルに非クラスタ化インデックスを作成した場合、データ取得のためにその非クラスタ化インデックスを使ったほうが単にテーブルスキャンをするよりも I/O が増えてしまう可能性を考えればおわかりいただけるでしょう。例えば 1000 名の " 男性 " 社員を検索したい時に非クラスタ化インデックスを使用すれば、データページに対して 1000 回の I/O が発生する可能性がありますが、実際の男性社員情報が 100 ページに格納されているとすれば、テーブルスキャンでは 100 回の I/O で済みます。このような環境では非クラスタ化インデックスは有効に働きません。
したがって非クラスタ化インデックスを決定する際には DBCC SHOW_STATISTICS ステートメントでインデックスの選択度を表示し、非クラスタ化インデックスとして有効かどうかを事前に判断しておく必要があります。
1-4. 非クラスタ化インデックスの構造 Part2
1-4 ではクラスタ化インデックスが存在する場合の非クラスタ化インデックスの構造を見ていきましょう。クラスタ化インデックスが存在する場合の非クラスタ化インデックスでリーフレベルは 1-3 と同様にインデックスページなのですが、図 1-4-1 のようにポインタとして行識別子ではなくクラスタ化キーを格納しています。そのクラスタ化キーを用いて再度クラスタ化インデックスをルートページから探索し、検索対象データを探し出します。
.gif)
図 1-4-1 クラスタ化インデックスが存在する非クラスタ化インデックスでのリーフレベルはクラスタ化キーを格納する
このためクラスタ化インデックスが存在する非クラスタ化インデックスは、以下の場合に優位性をもつことがあります。
● ページ分割がデータページに対して行われる
● 選択度が高い検索条件を使用する
通常、データページにページ分割が発生すると、ページデータの約半分が新しく割り当てられたページに移動します。そのとき「クラスタ化インデックスが存在しない非クラスタ化インデックス」の場合は、リーフページに行識別子を格納しているためデータの物理的な移動に伴い、行識別子を変更しなければいけなくなります。
しかし「クラスタ化インデックスが存在する非クラスタ化インデックス」の場合は、リーフページにクラスタ化キーを格納しているためにデータの物理的な移動に影響を受けず、余分なオーバーヘッドが少なくなります。また「クラスタ化インデックスが存在しない非クラスタ化インデックス」同様に選択度が高い場合は有利ですが、クラスタ化インデックスをサーチするため余分な I/O が発生するので、なるべく最低限の I/O で済むように「クラスタ化インデックスが存在する非クラスタ化インデックス」は構成しないと優位性は保てません。
そのためにはインデックスのサイズを小さくします。例えば int ではなく tinyint などの出来る限り小さいデータ型を使用するようにしたり、 char ではなく、 varchar を使用して文字の平均サイズを小さくします。このことは非クラスタ化インデックス自身も考慮しなければいけませんが、非クラスタ化インデックスのリーフページにクラスタ化キー値が格納されるため、クラスタ化インデックスに関しても同じように考える必要があります。
1-5. 制約とインデックス
制約はデータベースでデータ整合性を確保するための重要な方法です。制約はパフォーマンスとあまり関係はありませんが、制約の中にはインデックスと切っても切り離せない関係のものがあります。インデックスと関連が深い以下の制約は覚えておく必要があるでしょう。
PRIMARY KEY 制約
PRIMARY KEY 制約は、行を一意に識別する主キーをテーブルに対して定義することで、挿入、変更などによって同じ主キー値が存在することを防いでくれます。 PRIMARY KEY 制約の特徴は以下のとおりです。
1 つのテーブルに対して1つしか定義できない
NULL 値は許可されない
PRIMARY KEY 制約作成時には、指定の列に一意のインデックスが暗黙的に作成されます。この時、クラスタ化インデックスまたは非クラスタ化インデックスが指定できます。デフォルトはクラスタ化インデックスですが、既にクラスタ化インデックスが存在する場合には非クラスタ化インデックスが作成されます。このインデックスは単独では削除できません。削除したい場合には PRIMARY KEY 制約を削除する必要があります。
●UNIQUE KEY 制約
UNIQUE KEY 制約は、行を一意に識別するキーをテーブルに対して定義することで、 挿入、変更などによって同じキー値が存在することを防いでくれます。 UNIQUE KEY 制約は通常、主キーとならなかった代替キーに対して指定します。 UNIQUE KEY 制約の特徴は以下のとおりです。
1 つのテーブルに対して複数定義できる。
NULL 値を許可する
UNIQUE KEY 制約作成時には、指定の列に一意のインデックスが暗黙的に作成されます。この時、クラスタ化インデックスまたは非クラスタ化インデックスが指定できます。デフォルトは非クラスタ化インデックスになります。このインデックスは単独では削除できません。削除したい場合には UNIQUE KEY 制約を削除する必要があります。
以上をまとめたものが、図 1-5-1 です。
制約 |
暗黙的に作成されるインデックス |
1 テーブルに定義できる数 |
NULL 値 |
|---|---|---|---|
PRIMARY KEY |
一意なクラスタ化インデックス （デフォルト） |
1 |
許可しない |
UNIQUE KEY |
一意な非クラスタ化インデックス |
最大 249 |
許可する |
図 1-5-1 PRIMARY KEY 制約と UNIQUE KEY 制約を作成すると暗黙的にインデックスは作成される
1-6. sysindexes システムテーブルの理解
sysindexes システムテーブルはテーブルとインデックスに関する情報が格納される重要なテーブルであり、データベース内のインデックスとテーブルごとに 1 行のデータを保持します。インデックスの理解を深めるためにはsysindexesテーブルを理解することが必要です。その中でも以下の列は特に重要です。
indid 列
0 ‥ このエントリがヒープであることを示します
1 ‥ このエントリがクラスタ化インデックスであることを示します
2 ～ 250 ‥ このエントリが非クラスタ化インデックスであることを示しますFirstIAM 列
indid が 0 の場合、インデックスを使わずにヒープにアクセスするための最初の IAM ページを示します
root 列
indid>=1 または <255 の場合、インデックスを使用した場合のルートページを示します
name列
indid が 0 の場合はテーブル名、 indid>=1 または <255 の場合はインデックス名を示します
first 列
リーフレベルの先頭ページまたはルートページを示します
IAM とは Index Allocation Map の略でヒープまたはインデックスに使用されているエクステントを管理しているページであり、オブジェクトごとに必要に応じて割り当てられ、ファイル内にランダムに配置されます。 IAM が複数ある場合には図 1-6-1 のようにオブジェクト用のすべての IAM ページはチェーンとしてリンクされます。
.gif)
図1-6-1 IAM ページはチェーンとしてリンクされる
1-7. クラスタ化インデックスを使用したデータアクセス方法
では、今までの知識を総動員してクラスタ化インデックスを使用したデータアクセス方法を具体的にみていきましょう。図 1-7-1 の社員テーブルを使用します。
列名 |
データ型 |
インデックス |
|---|---|---|
shainID |
smallint |
一意なクラスタ化（ IX_shianID ） |
LastName |
varchar(50) |
－ |
FirstName |
varchar(50) |
－ |
Age |
tinyint |
－ |
図 1-7-1 社員テーブルの shainID 列に IX_shianID という名前の一意なクラスタ化インデックスが作られている
例えば社員テーブルに関して以下のクエリが発行されたと仮定して、クラスタ化インデックス（今回は 3 レベルだとする）を使用し、データを検索します。
クエリ
select * from 社員テーブル where shainID between 10 and 30
このクエリは図 1-7-2 のようにデータアクセスされます。
.gif)
図 1-7-2 クラスタ化インデックスを使用してデータを検索する
sysindexes システムテーブルにおいて name 列から社員テーブルの IX_shianID インデックスを検索し（indid 列は 1）、root 列でクラスタ化インデックスのルートページの場所を取得します
shainID=10 のページがどこにあるかを調べるために、ルートページで比較処理を行い、適切な中間レベルのページの場所を取得します
shainID=10 のページがどこにあるかを調べるために、中間レベルのページで比較処理を行い、適切なリーフレベルのページの場所を取得します
リーフレベル（実際のデータページ）から、shainID=10 の行を探し出します
リーフレベルはクラスタ化キー順に並んでいるため、shainID=10 から 30 までの行データを取得します（必要であれば、リンクリストを用いて次のページの場所を取得します
1-8. 非クラスタ化インデックスを使用したデータアクセス方法 Part1
次にクラスタ化インデックスが存在しない非クラスタ化インデックスを使用したデータアクセス方法を具体的にみていきましょう。図 1-8-1 の社員テーブルを使用します。
列名 |
データ型 |
インデックス |
|---|---|---|
shainID |
smallint |
－ |
LastName |
varchar(50) |
非クラスタ化（ IX_ LastName ） |
FirstName |
varchar(50) |
－ |
Age |
tinyint |
－ |
図 1-8-1 社員テーブルの LastName 列に IX_ LastName という名前の非クラスタ化インデックスが作られている
例えば社員テーブルに関して以下のクエリが発行されたと仮定して、非クラスタ化インデックス（今回は 3 レベルだとする）を使用し、データを検索します。
クエリ select * from 社員テーブル where LastName = ‘Suzuki’
このクエリは図 1-8-2 のようにデータアクセスされます。
.gif)
図 1-8-2 非クラスタ化インデックスを使用してデータを検索する（クラスタ化インデックスが存在しない場合）
sysindexes システムテーブルにおいて name 列から社員テーブルの IX_ LastName インデックスを検索し（indid 列は >=1 または <255）、root 列で非クラスタ化インデックスのルートページの場所を取得します
LastName = ‘Suzuki’のページがどこにあるかを調べるために、ルートページで比較処理を行い、適切な中間レベルのページの場所を取得します
LastName = ‘Suzuki’のページがどこにあるかを調べるために、中間レベルのページで比較処理を行い、適切なリーフレベルのページの場所を取得します
リーフレベルから、LastName = ‘Suzuki’の行を探し出します
リーフレベルで行識別子を格納しているため、データベージ（ヒープ）へジャンプし、行データを取得します
1-9. 非クラスタ化インデックスを使用したデータアクセス方法 Part2
次にクラスタ化インデックスが存在する非クラスタ化インデックスを使用したデータアクセス方法を具体的にみていきましょう。図 1-9-1 の社員テーブルを使用します。
列名 |
データ型 |
インデックス |
|---|---|---|
shainID |
smallint |
一意なクラスタ化（ IX_shianID ） |
LastName |
varchar(50) |
非クラスタ化（ IX_ LastName ） |
FirstName |
varchar(50) |
－ |
Age |
tinyint |
－ |
図 1-9-1 社員テーブルの shainID 列に IX_shianID という名前の一意なクラスタ化インデックスが、 LastName 列に IX_ LastName という名前の非クラスタ化インデックスが作られている
例えば社員テーブルに関して以下のクエリが発行されたと仮定して、非クラスタ化インデックス（今回は 3 レベルだとする）を使用し、データを検索します。（クラスタ化インデックスも 3 レベルだとします）
クエリ
select * from 社員テーブル where LastName = ‘Suzuki’
このクエリは図 1-9-2 のようにデータアクセスされます。.gif)
図 1-9-2 非クラスタ化インデックスを使用してデータを検索する（クラスタ化インデックスが存在する場合）
sysindexes システムテーブルにおいて name 列から社員テーブルの IX_ LastName インデックスを検索し（indid 列は >=1 または <255）、root 列で非クラスタ化インデックスのルートページの場所を取得します
LastName = ‘Suzuki’のページがどこにあるかを調べるために、ルートページで比較処理を行い、適切な非クラスタ化インデックスの中間レベルのページの場所を取得します
LastName = ‘Suzuki’のページがどこにあるかを調べるために、非クラスタ化インデックスの中間レベルのページで比較処理を行い、適切な非クラスタ化インデックスのリーフレベルのページの場所を取得します
非クラスタ化インデックスのリーフレベルから、LastName = ‘Suzuki’の行を探し出します
sysindexes システムテーブルにおいて name 列から社員テーブルの IX_shianID インデックスを検索し（indid 列は 1）、root 列でクラスタ化インデックスのルートページの場所を取得します
非クラスタ化インデックスのリーフレベルでクラスタ化キー値を格納しているため、shainID=10（LastName = ‘Suzuki’にあたる）のページがどこにあるかを調べるために、ルートページで比較処理を行い、適切なクラスタ化インデックスの中間レベルのページの場所を取得します
shainID=10 のページがどこにあるかを調べるために、クラスタ化インデックスの中間レベルのページで比較処理を行い、適切なクラスタ化インデックスのリーフレベルのページの場所を取得します
クラスタ化インデックスのリーフレベル（実際のデータページ）から、shainID=10 の行を取得します
1-10. インデックスを使用しないデータアクセス方法
最後にインデックスを使用しないデータアクセス方法を具体的にみていきましょう。図 1-10-1 の社員テーブルを使用します。
列名 |
データ型 |
インデックス |
|---|---|---|
shainID |
smallint |
－ |
LastName |
varchar(50) |
－ |
FirstName |
varchar(50) |
－ |
Age |
tinyint |
－ |
図 1-10-1 社員テーブルにはインデックスが作られていない
例えば社員テーブルに関して以下のクエリが発行されたと仮定してデータを検索します。
クエリ
select * from 社員テーブル
このクエリは図 1-10-2 のようにデータアクセスされます。
.gif)
図 1-10-2 インデックスを使用せずにデータを検索する
sysindexes システムテーブルにおいて社員テーブルの indid 列=0を検索し、FirstIAM 列で最初の IAM ページの場所を取得します
IAM ページには社員テーブルに関するすべてのページリストが格納されており、どこのエクステントに社員テーブルがあるかがわかります。そのため関連するヒープ（データページ）をすべて読み取ることができます。
ヒープ（データページ）から全データを取得します
.jpg)
鈴 木 智行: NEC E ラーニング事業部に所属。入社以来、インストラクタとして教育業務に従事。汎用機、 UNIX を経て、 1994 年より マイクロソフト認定トレーナー (MCT) として、管理者向け教育を担当。 SQL Server は 4.21a から携わっており、現在は主に SQL Server 2000 に関わるデータベース教育を中心に担当。 Windows 2000 および SQL Server 2000 での MCSE , MCDBA を取得しており、情報処理技術者試験のテクニカルエンジニア（データベース）も取得済。 SQL Server の優位性をアピールできるように Oracle Gold も取得した。今後 Oracle Plutinum を取得予定し、日々データベースを極めることに努力している。