- 追加された行はこの色です。
- 削除された行はこの色です。
[[Open棟梁Project>http://opentouryo.osscons.jp/]] - [[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]
「[[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]」は、「[[Open棟梁Project>https://github.com/OpenTouryoProject/]]」,「[[OSSコンソーシアム .NET開発基盤部会>https://www.osscons.jp/dotNetDevelopmentInfrastructure/]]」によって運営されています。
-[[戻る>SQL Server]]
* 目次 [#c756c9b8]
#contents
*概要 [#x12ae7fc]
インデックスがないテーブルには基本的にデータの並び順に保証がないため、~
これ検索する場合は、性能的に遅い「テーブル スキャン」を実行する。~
このため、検索処理の効率化のために、検索条件に対応した「インデックス」を作成する。
SQL Serverのインデックスには、
-「クラスタ化インデックス」
-「非クラスタ化インデックス」
-「カバリング インデックス」
-「付加列インデックス」
-[[「パーティション インデックス」>SQL Server パーティション分割#bfae5bcf]]
-「インデックス付きビュー」
の２種類のインデックスがある。
の6種類のインデックスがある。
*インデックス種類 [#u7fe9011]
**「クラスタ化インデックス」 [#v844b64f]
「クラスタ化インデックス」はOracleの「索引構成表」と同じであり、~
「電話帳の50音順索引」のように、データが順番に並べられたインデックスのことを言う。
-SQL Serverでは、１テーブルに対し「クラスタ化インデックス」を１つだけ作成可能。
-SQL Serverではテーブルに「主キー」を設定すると、自動的に「クラスタ化インデックス」が作成される。
-「主キー」を「クラスタ化インデックス」にしたくないのであれば、主キー作成時に「NONCLUSTEREDキーワード」を指定する。
**「非クラスタ化インデックス」 [#c5e07092]
「非クラスタ化インデックス」はOracleの「索引」と同じであり、~
「書籍の索引」のように、データとは別の領域に作られたインデックスのことを言う。
-SQL Serverでは、１テーブルに対し「非クラスタ化インデックス」を249個まで作成可能。
**複合インデックス [#yf8a153a]
-キーとして構成されているカラムの全てが検索条件に指定されていなくても、~
キーの先頭から途中までのカラムが指定されていれば、インデックスが使われる。
***「カバリング インデックス」 [#y4bbd54d]
-「カバリング インデックス」とは、「[[複合インデックス>#yf8a153a]]」を指す。
-「カバリング インデックス」は、インデックスに取得データを含めることで、~
ヒープ（のページ）へのジャンプを防止することができ、性能の向上が期待できる。
***「付加列インデックス」 [#oe7890bb]
-「[[カバリング インデックス>#y4bbd54d]]」には、
--カバリンク列がルート・中間・リーフ ページに含まれるため、
---インデックス サイズが大きくなり、
---スキャンや、シーク時のI/O数が多くなり、
---インデックス更新時のオーバーヘッドも高くなる。
>といった性能上の問題が存在する。
-これを回避するために、~
SQL Server 2005からサポートされた「付加列インデックス」が使用できる。
-「付加列インデックス」は、~
--「[[カバリング インデックス>#y4bbd54d]]」の欠点を補った機能であるため、~
基本的に「付加列インデックス」を利用することが推奨される。
--具体的には、リーフノードにのみカラムを追加する（列を付加する）ことで、~
ヒープ（のページ）へのジャンプを防止しつつ、インデックスのサイズも押さえる。
**[[「パーティション インデックス」>SQL Server パーティション分割#bfae5bcf]] [#h6184192]
**「インデックス付きビュー」 [#fa027756]
-ビューに一意「クラスタ化インデックス」を付与することで、~
「クラスタ化インデックス」を持つテーブルのように、~
ビューに結果セットを格納するものである（つまり実体が存在する）。
-このため、特に結合・集計処理を伴う参照クエリで性能向上が期待できる。
-「インデックス付きビュー」には、「非クラスタ化インデックス」を追加できる。
*インデックスの構造 [#g4004d83]
**インデックス ページ [#h2d2b2e3]
インデックスの構造について説明する。
-インデックスは「インデックス ページ」から構成されており、「インデックス ページ」は、
--同位層のページを繋ぐ「ポインタ」と、
--下位層のページへの「ポインタ」
--および「キー値」
>によって構成される。
-「インデックス ページ」の
--最上位層は「ルート レベル ページ」
--最下位層は「リーフ レベル ページ」
--「ルート レベル ページ」と「リーフ レベル ページ」の~
中間のレベルは「中間レベル ページ」と呼ぶ。
#ref(IndexPage.png,left,nowrap,インデックス ページ)
**クラスタ化インデックス [#k75f817f]
**「クラスタ化インデックス」 [#k75f817f]
次に、「クラスタ化インデックス」の構造について説明する。
「クラスタ化インデックス」は、テーブルで「クラスタ化キー（クラスタ化インデックスを作成する際に使用したキー）」を設定すると、~
-「クラスタ化インデックス」は、テーブルで「クラスタ化キー（クラスタ化インデックスを作成する際に使用したキー）」を設定すると、~
そのキー値の昇順にデータが並び替えられて、「リーフ レベル ページ」が実際の「データ ページ」として構成される。
#ref(ClusteredIndex.png,left,nowrap,クラスタ化インデックス)
-「クラスタ化インデックス」は、「クラスタ化キー」の値の昇順にデータが並べられる。
-このため、テーブルに対して「範囲検索」、「順次アクセス」処理をする際に、~
***ディスクI/Oのチューニング [#xf792b51]
「クラスタ化インデックス」でディスクI/Oのチューニングが可能である。
-「非クラスタ化インデックス」で必要となるRID LookUpという処理が不要で、その分性能が良い。
-テーブルに対して「範囲検索」、「順次アクセス」処理をする際に、~
目的のデータが同じ「データ ページ」にある確率が多くなりディスク ヘッドの移動が少なくなる。
-このように、「クラスタ化インデックス」でディスクI/Oのチューニングが可能である。
**非クラスタ化インデックス [#zd59289b]
-「選択度の低い情報」（後述）であっても、~
「範囲検索」、「順次アクセス」で、効果を出し得るインデックスであると言える。
-SQL Serverでは検索で多用される（と想定される）主キーには、~
デフォルトで「クラスタ化インデックス」が付与される。
--しかし、この方法が必ずしも適切であるということにはならない。~
例えば、主キー以外のキーを使用した範囲スキャン検索の性能の向上が優先されるようなテーブルでは、~
主キーに「非クラスタ化インデックス」を付与し、「範囲スキャン検索」処理用のキーに「クラスタ化インデックス」を使用した方が、全体最適化に繋がることがある。
--インサイド Microsoft SQL Server 2005 クエリチューニング＆最適化編~
第４章 ： クエリパフォーマンスのトラブルシューティング
>テーブルを主キー制約で宣言すると、規定でクラスタ化インデックスが主キー列に作成されますが、この方法が常に最適であるとは限りません。~
その名が示すとおり、主キーは一意であり、条件を満たす単一行を検索する場合は、非クラスタ化インデックスが非常に効率的です。~
『主キーの一意性は、非クラスタ化インデックスでも適用できるため、クラスタ化インデックスは、主キー制約を宣言するときに~
NONCLUSTEREDのキーワードを追加して、クラスタ化インデックスが有効なものに対して確保しておきます。』
***適合しないケース [#e09e495c]
また、以下のキーには適していないと言われている
-頻繁に変更される列~
物理的な並び替えが必要になるため。
-広範なキー（複数の列・複数のサイズの大きな列を組み合わせたキー）~
--「クラスタ化インデックス」を持つテーブルに追加した「非クラスタ化インデックス」のリーフ ページには、~
行識別子ではなく、「クラスタ化インデックス」のキー参照が格納される（後述）。~
--このため、「クラスタ化インデックス」のキーのサイズが大きくなると、~
「非クラスタ化インデックス」のサイズが大きくなるため。
***その他 [#x4442507]
「クラスタ化インデックス」作成時には、~
実際のデータ（ヒープ）を並べ替えた結果を格納しておくための作業領域として、~
テーブル サイズの約 1.5 倍の空き領域が必要になるため注意が必要である。
***参考 [#ia944215]
-クラスタ化インデックスの設計ガイドライン~
http://msdn.microsoft.com/ja-jp/library/ms190639.aspx
**「非クラスタ化インデックス」 [#zd59289b]
次に、「非クラスタ化インデックス」の構造について説明する。
-「非クラスタ化インデックス」は、一般的かつ汎用的なインデックスであり、「リーフ レベル ページ」には、行識別子が格納される。
-「非クラスタ化インデックス」では、「リーフ レベル ページ」からヒープ（のページ）上の行情報を引くための、RID LookUpと言う処理が必要となる。
--このため、「リーフ レベル ページ」 → ヒープ（のページ）へのジャンプ~
（これをRID LookUpと言い、場合によってはディスク ヘッドの移動を要する）~
が必要になるため、キーを使用した範囲スキャン検索で、~
データを収集するクエリの性能は、件数が多くなるほど向上しない。
--また、「選択度の低い情報」 も同様に、範囲スキャン検索性能が向上しないため効果が出ない。
が必要になるため、キーを使用した範囲スキャン検索で、データを収集するクエリの性能は、件数が多くなるほど向上しない。
--また、「選択度の低い情報」（後述） も同様に、範囲スキャン検索性能が向上しないため効果が出ない。
-また、「非クラスタ化インデックス」は、
--「クラスタ化インデックス」が存在しない場合
--「クラスタ化インデックス」が存在する場合
>で構造が異なる。
-非クラスター化インデックスのデザイン ガイドライン~
http://msdn.microsoft.com/ja-jp/library/ms187019.aspx
***「クラスタ化インデックス」が存在しない場合の「非クラスタ化インデックス」 [#kc3ba438]
***「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」 [#kc3ba438]
-「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」の「リーフ レベル ページ」は「インデックス ページ」である。
-「データ ページ」は「クラスタ化インデックス」を作成した場合の「データ ページ」とは構造が異なり、「リンク リスト」はもたない。
--このような「クラスタ化インデックス」が存在しない場合の「データ ページ」の集まりを「ヒープ 」と呼ぶ。
--このような「非クラスタ化インデックス」の「データ ページ」の集まりを「ヒープ 」と呼ぶ。
--「ヒープ」では、データの行の順番は特定の順序では格納されず、「データ ページ」にも特定の順序はない。
-「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」での「リーフ レベル（インデックス ページ）」では~
ポインタとして行識別子（ファイルID、ページID、行ID）を格納しており、その行識別子を使って「ヒープ」へジャンプし、検索対象データを探し出す。
#ref(NonClusteredIndex.png,left,nowrap,「クラスタ化インデックス」が存在しない場合の「非クラスタ化インデックス」)
#ref(NonClusteredIndex.png,left,nowrap,「クラスタ化インデックス」が存在しない「非クラスタ化インデックス」)
***「クラスタ化インデックス」が存在する「非クラスタ化インデックス」 [#m1cd6f28]
「クラスタ化インデックス」が存在する「非クラスタ化インデックス」の「リーフ レベル ページ」は同様に~
-「クラスタ化インデックス」が存在する「非クラスタ化インデックス」の「リーフ レベル ページ」は同様に~
「インデックス ページ」であるが、「ポインタ」として「行識別子」ではなく「クラスタ化キー」の値を格納している。
-このため、「クラスタ化インデックス」が存在する「非クラスタ化インデックス」での検索は、
--最初に「非クラスタ化インデックス」を使用して検索し、
--「リーフ レベル ページ」で取得した「クラスタ化キー」の値を使用して「クラスタ化インデックス」を検索する。
--「非クラスタ化インデックス」のキーを使用して「クラスタ化インデックス」のキーのみ取得する場合は、非常に高速。
#ref(NonClusteredIndexWithClusteredIndex.png,left,nowrap,「クラスタ化インデックス」が存在する「非クラスタ化インデックス」)
このため、「クラスタ化インデックス」が存在する「非クラスタ化インデックス」での検索は、
-最初に「非クラスタ化インデックス」を使用して検索し、
-「リーフ レベル ページ」で取得した「クラスタ化キー」の値を使用して「クラスタ化インデックス」を検索する。
**「カバリング インデックス」 [#h137b14d]
-「カバリング インデックス」は、以下により性能の向上が期待できる。
--最初に指定された列をキーにして、木構造を構築し、
--以降に指定された列（カバリンク列）をルート・中間・リーフ ページに含める。
--これにより、カバリンク列に対してはRID LookUpをせずに処理が可能となる。
-例えば、下記DDLで、「カバリング インデックス」が作成できる。
CREATE INDEX index_name
ON table_name(column1, column2, column3)
-この場合、
--「column1」をキーにして、木構造が構築され、
--カバリンク列として「column2、column3」が~
ルート・中間・リーフ ページに含められる。
**「付加列インデックス」 [#a5e6c2be]
-例えば、下記DDLで、「付加列インデックス」が作成できる。
CREATE INDEX index_name
ON table_name (column1)
INCLUDE(column2, column3)
-この場合、
--「column1」をキーにして、木構造が構築され、
--付加列として、「column2、column3」がリーフ ページにのみ含められる。
-付加列インデックス~
http://msdn.microsoft.com/ja-jp/library/ms190806.aspx
**[[「パーティション インデックス」>SQL Server パーティション分割#bfae5bcf]] [#n8eb4f29]
**「インデックス付きビュー」 [#bd67ba1d]
-GROUP BY句を使用した集計処理で指定されるキーの~
選択度が高い（若しくは一意の）場合は、性能向上は期待できない。
-また、「インデックス付きビュー」の基テーブルの更新がされると、
--ビューに格納されている結果セットの更新が必要となるため、~
更新処理が頻繁なビューに対して「インデックス付きビュー」を作成すると~
余計にコストがかかる場合があるので注意する。
--なお、条件を満たしていれば「インデックス付きビュー」の更新も可能であり、~
「インデックス付きビュー」の更新が行われた場合、基テーブルも更新される。
-考慮点
--「インデックス付きビュー」は、FROM句で「インデックス付きビュー」を~
直接指定していないクエリからも、オプティマイザにより、使用されることがある 。
---SQL Server 2005インデックス付きビューによるパフォーマンスの向上~
http://technet.microsoft.com/ja-jp/library/cc917715.aspx
--「インデックス付きビュー」を「パーティション テーブル」とすると、~
さらにクエリ速度、効率を高められる可能性がある。
---インデックス付きビューが定義されている場合のパーティション切り替え~
http://msdn.microsoft.com/ja-jp/library/bb964715.aspx
*インデックスと選択度 [#u8131eb5]
-一般的にインデックスは、
--選択度が高い項目を検索条件に使用する場合に有用である。
--これとは逆に、選択度の低い項目では不利になることが多い。
-選択度
--選択度が高い＝重複が少ない~
（主キー、ユニーク キーなど）
--選択度が低い＝重複が多い。~
（例えば、"男性"、"女性"というデータのみ格納する）
**「非クラスタ化インデックス」と選択度 [#qd900a52]
「非クラスタ化インデックス」は、選択度の低い項目に対しては不利である。
-例えば、"男性"、"女性"というデータのみ格納する項目に対して、~
「非クラスタ化インデックス」を作成し、1000名の "男性" 社員を検索する時に~
「非クラスタ化インデックス」を使用して「インデックス スキャン」した場合を考える。
-この場合、「非クラスタ化インデックス」では、~
「リーフ レベル ページ」の「インデックス ページ」から「データ ページ」にアクセスするため~
「データ ページ」に対して、最大で1000回ものI/Oが発生する可能性がある。
**「クラスタ化インデックス」と選択度 [#y89d89a0]
「クラスタ化インデックス」は、選択度の低い項目に対して"も"有効である。
-例えば、"男性"、"女性"というデータのみ格納する項目に対して、~
「クラスタ化インデックス」を作成し、1000名の "男性" 社員を検索する時に~
「クラスタ化インデックス」を使用して「インデックス スキャン」した場合を考える。
-「クラスタ化インデックス」を作成したテーブルでは、~
「クラスタ化キー」の値（この場合、"男性"、"女性"）毎にデータがまとまっているため、
--"男性"社員情報を読み込むページ数は最小化され、I/O回数も最小化される。
--また、「非クラスタ化インデックス」と異なり、~
「リーフ レベル ページ」の「データ ページ」を直接スキャンすることができる。
-例えば、「データ ページ」に10レコードが格納できる場合、
--1000名の "男性" 社員のレコードは100ページに格納され、
--これが１つのエクステントに規則正しく格納されていれば、
--最小で13回のI/Oで読み取りが完了する。
***計算式 [#c0ca9e5d]
1000（レコード） / 10（レコード / ページ） / 8（ページ / エクステント） ≒ 13エクステント
≒ 13回のI/O
※ SQL Server は、ディスクI/Oを、ディスク上管理単位である「エクステント」単位で処理する。
**選択度と「ページ分割」 [#f9ac1e25]
**選択度とインデックスの「ページ分割」 [#f9ac1e25]
なお、選択度の低いデータでは、どちらのインデックスでも、~
データの挿入時に、「ページ分割」が発生しやすくなり、不利である。
>「ページ分割」については、~
「[[「インデックスの断片化」の管理>#z9ed663c]]」で説明する。
選択度の低い項目をキーにした「クラスタ化インデックス」の作成は、
-検索（「範囲検索」・「順次アクセス」）の効率
-データ更新時の「ページ分割」のオーバーヘッド
のトレードオフを考慮する形になる。
*「インデックスの断片化」の管理 [#z9ed663c]
**「インデックスの断片化」とは [#m65eaba4]
DBの「データ ファイル」は、
-論理的な「セグメント」、
-物理的な「エクステント」
から構成される。
「セグメント」とは、テーブル、インデックスといった、オブジェクトを意味する。
SQL Server は、
-ディスクI/Oを、ディスク上管理単位である64KBの「エクステント」単位で処理する。
-また、「エクステント」は、メモリ上の管理単位である8KBの「ページ」から構成される。
#ref(SegmentExtentPage.png,left,nowrap,「セグメント」、「エクステント」、「ページ」)
-データの追加、更新処理などで、
--「インデックス ページ」、「データ ページ」内の空き領域が埋まった場合、
--「ページ分割」が発生し、一部の「ページ」が、別の「エクステント」に格納されることがある。
-例えば、SQL Serverでは
--「インデックス ページ」、「データ ページ」が埋まると、~
「ページ分割」により新しい行を挿入する余裕を作り出す。~
--この作業にはコストがかかるため、DBサーバ全体のパフォーマンスを低下させる。~
「インデックスの断片化」は、「インデックス ページ」、「データ ページ」の「ページ分割」が進んだ状態を指す。
#ref(Fragmentation.png,left,nowrap,インデックスの断片化（ページ分割が進んだ状態）)
-「インデックスの断片化」が進んだ状態では、I/O 処理の連続性が失われ、~
別の「エクステント」から断片化した「ページ」を取得するという余分なI/Oが発生する。
#ref(ExtraIO.png,left,nowrap,インデックスの断片化による余分なＩ/Ｏの発生)
-一般的に、この状態はセグメント（テーブル、インデックス）を「再構築」することで解消できる。
**「ページ密度」とは [#gebab5b2]
-「ページ分割」は、
--DBサーバ全体のパフォーマンスを低下や、
--「インデックスの断片化」による余分なI/Oの発生に
>繋がる。このため、なるべく「ページ分割」が発生しないようにする必要がある。
-「ページ分割」の発生を抑止するため、
--更新と挿入が頻繁に行われる予定のテーブルや、インデックスには~
「ページ密度」を低く設定し、データの増加に対応する空き領域を残しておく。
--「ページ密度」は、テーブル、インデックスの生成時に設定することができる。
-ただし、「ページ密度」の値が低いと、~
クエリを処理するために読み取るページ（エクステント）が多くなる可能性があるので、~
以下のトレードオフを考慮し、「ページ密度」を決定する必要がある。
--読み取り処理：読み取りページ（エクステント）数の増加
--書き込み処理：「ページ分割」の発生
-例えば、テーブルが読み取り専用で変更されない場合は、~
テーブルや、インデックスの「ページ密度」を高く設定することで、~
読み取りページ（エクステント）数を減らすことができる。
**「ページ密度」の設定 [#s87a04aa]
「ページ密度」は、「FILLFACTOR」オプションで設定することができる。
***「FILLFACTOR」オプション [#f7eadcd6]
-「FILLFACTOR」は、
--「CREATE INDEX」ステートメント
--「DBCC DBREINDEX」ステートメント
--「DBCC INDEXDEFRAG」ステートメント
>のオプションで指定できる。
-このオプションは、
--「インデックス ページ」
--「データ ページ」
>の「ページ密度」を制御する。
-通常、既定の「FILLFACTOR」で適切なパフォーマンスが得られるが、~
場合によっては「FILLFACTOR」を変更することでさらにパフォーマンスが高まる。
***「PAD_INDEX」オプション [#z418b05f]
-「PAD_INDEX」は、「CREATE INDEX」のステートメントのオプションで指定できる。
-このオプションは、インデックスの「リーフ レベル ページ」ではなく、~
インデックスの「中間レベル ページ」の「ページ密度」を制御する。
-「PAD_INDEX」は「FILLFACTOR」で指定されているパーセンテージを使用するので、~
「PAD_INDEX」は「FILLFACTOR」が指定されている場合にのみ有効になる。
*参考 [#y91b575a]
-インデックスの設計の全般的なガイドライン~
https://technet.microsoft.com/ja-jp/library/ms191195.aspx
-SQLServerのインデックスについてざっくりとまとめてみた - Qiita~
https://qiita.com/kz_morita/items/41291516ff3ee2650554
-SQLServer
--インデックスの基礎とメンテナンス~
http://mtgsqlserver.blogspot.jp/search/label/%E3%82%A4%E3%83%B3%E3%83%87%E3%83%83%E3%82%AF%E3%82%B9%E3%81%AE%E5%9F%BA%E7%A4%8E%E3%81%A8%E3%83%A1%E3%83%B3%E3%83%86%E3%83%8A%E3%83%B3%E3%82%B9
---インデックスの構造と内部動作~
１：http://mtgsqlserver.blogspot.jp/2013/03/blog-post_30.html~
２：http://mtgsqlserver.blogspot.jp/2013/03/blog-post_6900.html
**複合インデックス [#s80c95cf]
-複合インデックスの落とし穴 | がっとな日々 | ガットコンピューター~
https://www.gatc.jp/gat/it/it02dbindex.html
-複合インデックスの正しい列の順序~
http://use-the-index-luke.com/ja/sql/where-clause/the-equals-operator/concatenated-keys
----
Tags: [[:データアクセス]], [[:SQL Server]]
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
