2013年3月30日土曜日

◆データの検索(SELECT) の基本操作

事前作業(サンプル スクリプトのダウンロードとセットアップ)

Windows エクスプローラーを起動して、サンプル スクリプトをダウンロードしたフ
ォルダーを展開し、このフォルダー内の「CreateTables.txt」ファイルをダブル クリックし
て開きます。ファイルの内容をすべてコピーして、クエリ エディターへ貼り付けます。

image

貼り付け後、ツールバーの[!実行]ボタンをクリックしてクエリを実行します。これにより、
「sampleDB」という名前のデータベースが作成され、その中へ「社員」と「部門」テーブ
ルが作成されます。実行後、「社員」テーブルの 6 件のデータと「部門」テーブルの 2 件の
データが表示されれば、実行が完了です。

SELECT ステートメントによるデータ検索

SQL では、SELECT というステートメントを利用することで、テーブル内のデータを検索できる
ようになります。構文は、次のとおりです。

SELECT 列名1, 列名2, … FROM テーブル名 WHERE 検索する行の条件

SELECT の後には、取得したい列の名前をカンマで区切って指定します。列名へ、アスタリスク「*」を指定した場合は、すべての列という意味になり、テーブル内のすべての列を取得することができます。WHERE 句は省略することも可能で、この場合は、すべての行が取得の対象となります。

Let's Try
  1. まずは、[スタート]メニューの[すべてのプログラム]から[Microsoft SQL Server 2012]
    の[SQL Server Management Studio]をクリックして、Management Studio を起動し
    ます。
  2. [サーバーへの接続]ダイアログが表示されたら、[サーバー名]で SQL Server の名前を入
    力します。
    [認証]では、「Windows 認証」を選択して、[接続]ボタンをクリックします。なお、認
    証については、本自習書シリーズの「ログイン認証とオブジェクト権限」編で詳しく説明して
    いますので、こちらもぜひご覧いただければと思います。
  3. Management Studio が起動したら、次のようにツールバーの[新しいクエリ]ボタンをク
    リックして、[クエリ エディター]を開きます。
    image
  4. クエリ エディターでは、次のように入力して、sampleDB データベースへ接続して、「社員」
    テーブルのデータを参照します。

    USE sampleDB SELECT * FROM 社員


    image
    SELECT ステートメントの記述が完了したら、ツールバーの[!実行]ボタンをクリックし
    て、ステートメントを実行します。これにより、結果ウィンドウがグリッド形式(表形式)へ
    変わって、「sampleDB」データベース内の「社員」テーブルのすべてのデータを取得するこ
    とが確認できます。「USE」は、データベースへ接続するための SQL Server 独自のステート
    メント(Transact-SQL ステートメント)で、SELECT ステートメントで「*」を指定したこ
    とで、社員テーブルのすべてのデータを取得することができます。

    Note: SQL ステートメント内の記号は半角で入力
    SQL ステートメントでは、英数字や記号(スペース、カッコ、カンマ、単一引用符)は、すべて半角で入力する必
    要があります。日本語入力(IME)がオンになっている場合は、デフォルトでは、スペースが全角になるので、半
    角へ変換し忘れないように注意してください。
  5. 次に、社員テーブルの「氏名」列と「給与」列のデータのみを取得してみましょう。特定の列
    データのみを取得するには、次のように 列名をカンマで区切って指定します。
    SELECT 氏名, 給与 FROM 社員

    image
    このように、列名をカンマで区切って記述することで、その列のデータのみを取得できるよう
    になります。

    Note: 実行したいステートメントだけを選択してから[!実行]をクリック
    クエリ エディターでは、ツールバーの[!実行]ボタンをクリックすると、エディター内へ記述したすべてのス
    テートメントが実行されてしまいます。これを回避するには、実行したいステートメントだけを選択(マウスでド
    ラッグ&ドロップまたはキーボードの[Shift]キーを押しながら矢印キーで選択)してから[!実行]ボタンを
    クリックするようにします。

行の絞り込み: WHERE 句

ここまで利用した SELECT ステートメントは、WHERE 句を省略していましたが、この場合は、
すべての行データを取得します。SELECT ステートメントでは、WHERE 句を利用すると、絞り
込み条件を指定して、特定の行のみを取得できるようになります。では、これを試してみましょう。

  1. 次のように記述して、「社員番号」が「2」番の社員のみを取得してみます。
    image
    社員番号が 2 番の「鈴木 太郎」さんのみを取得できたことを確認できます。
  2. 次に、「氏名」が「浅田 あさみ」さんのデータのみを取得してみましょう。
    image

    Note: 文字列と日付データ型の値は単一引用符で囲む
    「氏名」列のデータを「'」(単一引用符)で囲んでいるのは、SQL ステートメントでは「文字列(char)と日付
    (date)データ型の値は、単一引用符で囲まなければならない」という決まりがあるためです。文字列を利用する
    場合は、単一引用符を忘れずに付けるようにしましょう。
データの並べ替え: ORDER BY 句

SELECT ステートメントでは、ORDER BY 句を利用すると、特定の列を基準にデータを並べ替え
て、結果を取得できるようになります。これは、次のように利用します。

SELECT 列名1, 列名2, …
FROM テーブル名
OREDER BY 列名[ ASC | DESC ]

ORDER BY の後には、並べ替えの基準となる列名を指定します。デフォルトでは、昇順(数値の
場合は「小から大へ」、英字の場合は「A から Z へ」、日付の場合は「古から新へ」)で並べ替え
ることができ、DESC キーワードを利用した場合は、降順で並べ替えることができます。

Let's Try: 昇順に並べ替え
  1. 次のように入力して、「社員」テーブルのデータを "給与の低い順(昇順)" に並べ替えて、
    表示してみましょう。
    image
    ORDER BY 句で指定した「給与」列を基準にデータを昇順に並べ替えることができたことを
    確認できます。
    データを昇順に並べ替える場合には、次のように「ASC」キーワードを利用しても同じ結果を
    取得することができます。
    image
    ASC キーワードを省略した場合と同じ結果を取得できたことを確認できます。
  2. 次に、"給与の高い順" に並べ替えてみましょう。降順で並べ替えるには、次のように DESC
    キーワードを利用します。
    image
    「給与」列の後へ、DESC キーワードを記述したので、給与が高い順(降順)に並べ替える
    ことができたことを確認できます。Note: NULL 値の並べ替え順
    NULL 値については、Step 4 で説明しますが、SQL Server では、NULL 値は最も小さい値として扱われます。
    なお、Oracle の場合は逆になり、NULL 値が最も大きい値として扱われます(NULLS FIRST オプションを指定
    して、NULL 値を最初に表示することも可能です)。
  3. 次のように入力して、"給与の高い順" に並べ替えて、"同じ給与の社員がいた場合には、社員番号の小さい順" に並べ替えるようにしてみましょう。
    image
    このようにカンマで区切って複数の列を指定すれば、1 つ目の列で同じ値だったとしても、2
    つ目の列で並べ替えることができます。2 つ目の列の並べ替え順は、デフォルトでは昇順です。
  4. 次に、"同じ給与の社員がいた場合には、社員番号の大きい順" に並べ替えるようにしてみましょう。
    image
    Note: ORDER BY 句を指定しない場合の並べ替え順は保証されない
    SQL の世界では、ORDER BY 句を指定しない場合の並べ替え順は保証されません。どういった形で取得されるか
    は、そのときの状況によって変化し、社員番号で並べ替えられるかもしれないし、給与で並べ替えられるかもしれ
    ません。

    Note: ORDER BY 句には数値も指定できる
    ORDER BY 句では、列名の代わりに “数値” を指定して、次のように記述することもできます。
    SELECT 社員番号, 給与 FROM 社員
    ORDER BY 2 DESC, 1

算術演算子: +、-、*、/

SQL では、算術演算子を利用して、数値データに対して演算を行うことができます。算術演算子
には、加算の「+」や減算の「-」、乗算の「*」、除算の「/」などがあります。

Let's Try
  1. 次のように算術演算子の「*」を利用して、「給与」の値を「3 倍」にした結果を取得してみま
    しょう。
    image
    このように、算術演算子を利用することで、数値データに対して、演算を行えるようになりま
    す。

    Note: NULL 値に対する演算結果は「NULL」
    NULL 値に対しては、何かしらの演算を行っても、結果は NULL 値になります。
    Note: 列に対する別名をつける: AS
    算術演算子を使って取得した列の名前は「(列名なし)」と表示されます。列名は、ORDER BY 句やアプリケーシ
    ョンからデータを取得する際に必要になるので、このままだと操作がしづらくなります。このような場合のために、
    結果列に対して、新しい列名をつける機能として「AS」があります。これは、次のように利用します。
    image
    このように、AS を使用すると、結果列に対して新しい列名を付け、ORDER BY 句やアプリケーションから利用
    できるようになります。
    また、AS は、既存の列に対して別名を付けたり、AS を省略して、次のように使用することもできます。
    image

文字列連結演算子: +

SQL では、「+」(文字列連結演算子)を利用すると、文字データを連結することができます。た
だし、これは SQL Server 独自の演算子で、SQL 規格(他のデータベース)では、「||」(縦線:
Vertical Line を2 個)を使用して、文字データを連結します。

Let's Try
  1. 次のように入力して、社員の「氏名」列へ “さん” をつけて、表示してみましょう。
    image
    このように、「+」を利用すると、文字データを連結できるようになります。
    表示された結果には、氏名と “さん” の間に余分なスペースがありますが、これは、「氏名」
    列のデータ型が Char(50) と定義されているためです。Char データ型では、指定したバイ
    ト数に満たない部分をスペースで埋めます(データ型については、本自習書シリーズの
    「Transact SQL 入門」で詳しく説明しています)。

    Note: 余分なスペースを詰める関数
    Char データ型では、指定したバイト数に満たない部分をスペースで埋めると説明しましたが、その余分なスペー
    スを詰めることができる関数もあります。それが「RTRIM」関数です。この関数を使用すると、次のように余分
    なスペースを詰められるようになります(RTRIM 関数については、本自習書シリーズの「Transact SQL 入門」
    で説明しています)。
    image
重複データの排除: DISTINCT

SQL では、DISTINCT 句を利用して、重複したデータを取り除くことができます。これは、次の
ように利用します。

SELECT DISTINCT 列名 FROM テーブル名

image

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

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

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

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

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


    image_thumb[46]
  2. 作成後、オブジェクト エクスプローラーで、「社員2」テーブルの[インデックス]フォルダーを展開して、「PK_社員2_~(クラスター化)」というインデックスが作成されていることを確認してみましょう。
    image_thumb[48]
クラスター化がある場合の非クラスター化インデックスの内部構造

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

image_thumb[51]

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

Let's Try
  1. まずは、「姓」列へ作成していたクラスター化インデックスを削除します。
    DROP INDEX 社員.index_姓
  2. 次に、「社員番号」列に対してクラスター化インデックスを作成します。

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

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

    CREATE INDEX index_姓 ON 社員(姓)

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

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


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

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


    image_thumb[56]

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

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

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

image

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

image

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

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

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


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

GUI での作成

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

image

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

image

Let's Try

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

  1. まずは、「姓」列へ作成していた非クラスター化インデックスを削除します。
    DROP INDEX 社員.index_姓
  2. 次に、「姓」と「名」列を含めたカバリング インデックスを作成してみましょう。

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


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

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

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

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

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

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

image

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

image

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

付加列インデックス(Include オプション)
付加列インデックス

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

image

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

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

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

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

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

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

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

image

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

image

Let's Try

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

  1. まずは、前の Step で作成したカバリング インデックス「index_姓名」を削除します。
    DROP INDEX 社員.index_姓名
  2. 次に、「姓」列に対して、「名」列を含む付加列インデックスを作成します。

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

  3. 作成が完了したら、カバリング インデックスのときと同じ検索を実行してみましょう。
    SELECT 社員番号, 姓, 名FROM 社員WHERE 姓 = 'Aoki'

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

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

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

image

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

◆インデックスの保守

断片化とは

インデックスのリーフ ページは、Index Scan を高速化するために、次のようにそれぞれのページ同士がリンクしています。

image

リーフ ページは、物理的に連続して格納されている場合は、先読み(先行読み取り)機能が効果的に働き、パフォーマンス良くデータを取得することができます。
断片化(fragmentation: フラグメンテーション)は、次のようにリーフ ページが連続的ではなく断片的に格納された状態です。

image

このように、リンクしているページが物理的に離れた場所にある状態が断片化が発生した状態です。このままでは、先読み機能が効果的に働かなくなり、Index Scan のパフォーマンスが大きく低下します。したがって、インデックスを作成した後は、断片化を事前防止する、あるいは断片化を解消するための保守を行うことが非常に重要になります。

断片化の原因(ページ分割)

デフォルトでは、ページ内は、追加または更新されたデータによって満杯に埋まっています。この状態で、間に割り込む値が追加されると、「ページ分割」が発生します。たとえば、次の図は、「姓」列へ作成したインデックスのリーフ ページが満杯に埋まっている状態を表していますが、このとき「Akiba」さんという間に割り込む値が INSERT されたとすると、ページには入りきらないので、物理的に離れた場所へ新しいページが作成されます。

image

この動作(ページ分割)は、インデックスが常に昇順に並べ替えられた状態に保たれている必要があるために行われています。間に割り込む値が追加されると、その値によって溢れ出たデータを格納するための新しいページが必要になるのです。また、このページには、溢れ出た1行のみが格納されるのではなく、元のページと新しいページが半分半分になるように調整されます。これは、もう一度間に割り込む値が追加されたとしても、簡単にページ分割を発生させないようにするための処置です(ページ分割を連続して発生させないための処置です)。このようなページ分割は、元のページと新しいページに半分半分にデータを割り当てることから、「50-50ページ分割」とも呼ばれています。
50-50ページ分割によって作成された新しいページは、物理的には、最後の空きページが利用される場合がほとんどです。これによって、リーフ ページが断片化した状態が生まれます。

断片化の調査: dm_db_index_physical_stats

断片化が発生しているかどうかを調べるには、Step3 でインデックスの階層数と使用ページ数を調べるために利用した dm_db_index_physical_stats 動的管理関数を利用します。構文は次のとおりです。

image

この関数の出力結果のうち、主なものは次のとおりです。

image

avg_fragmentation_in_percent という列で、断片化の割合を確認することができます。

スキャン モード

dm_db_index_physical_stats 関数の第5引数で指定するスキャン モードは、次の 3種類があります。

image

LIMITED モードを使用すると、最も高速に断片化を調査することができます。

LIMITED モードが高速な理由

dm_db_index_physical_stats 関数の LIMITED モードが高速な理由は、インデックスのリーフ レベルをスキャンせずに、ルート ページと中間ページのみを調査して、断片化の割合を算出するためです。次の図のように、中間ページにはリーフ ページのページ番号が格納されているので、ページ番号が連続か不連続であるかをチェックするだけで、断片化の割合を調べることができます。

image

ただし、LIMITED モードの場合には、avg_page_space_used_in_percent(ページの平均使用密度)や、record_count(ページ内の行数)など、結果を取得できない列もあります。詳しくは、オンライン ブックの次の場所を参考にしてください。

image

Let's Try
  1. まずは、前の Step で作成した付加列インデックス「index_姓名」の、インデックス ID を調べましょう。

    SELECT name, index_id, * FROM sys.indexes WHERE object_id = OBJECT_ID('社員')

    image
    インデックスID(index_id)が「3」であることを確認できます。
  2. 続いて、dm_db_index_physical_stats 関数を利用して、「index_姓名」の断片化の状態をチェックしてみましょう。

    SELECT
    avg_fragmentation_in_percent, index_id, index_level, page_count
    FROM
    sys.dm_db_index_physical_stats
    (DB_ID('sampleDB'), OBJECT_ID('社員'), 3, NULL , 'DETAILED')
    ORDER BY index_id, index_level DESC


    image
    「avg_fragmentation_in_percent」列は、すべての階層(index_level)で、「0」となっていることから、断片化が全く発生していないことを確認できます。また、このときの、リーフ レベル(index_level が 0)のページ数(page_count 列)が、「35」ページであることも確認できます。
  3. 次に、社員テーブルに対して、データを 1件 INSERT してみましょう。

    INSERT INTO 社員 VALUES(10001, 'Aiba', 'Jiro', '男性', '03-1234-xxxx', '')

  4. データの追加後、もう一度同じクエリを実行して、index_姓名の断片化の状態をチェックしましょう。
    image
    今度は、リーフ レベルで、「5.555…」パーセントの断片化が発生し、ページ数がデータを追加する前より 1ページ増えて、「36」ページになっていることを確認できます。
  5. 続いて、さらに、社員テーブルに対して、9 件のデータを追加してみましょう。

    INSERT INTO 社員
    VALUES(10002, 'Eto', 'Goro', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10003, 'Fujiwara', 'Goro', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10004, 'Goto', 'Goro', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10005, 'Inoue', 'Goro', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10006, 'Kato', 'Goro', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10007, 'Matsumoto', 'Goro', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10008, 'Oshima', 'Goro', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10009, 'Saito', 'Goro', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10010, 'Yamamoto', 'Goro', '男性', '03-1234-xxxx', '')

  6. データの追加後、もう一度断片化の状態をチェックします。
    image
    今度は、リーフ レベルで、「37.20…」パーセントの断片化が発生していることを確認できます。また、ページ数も「43」ページへ増えています。
断片化の解消(インデックスの再構築と再構成)

断片化を解消するには、次の 3つの方法があります。

  • インデックスのオフライン再構築
  • インデックスのオンライン再構築(Enterprise エディション のみで利用可能)
  • インデックスの再編成

これらは、いずれも ALTER INDEX ステートメントを利用して実行することができます。

インデックスのオフライン再構築(REBUILD)

インデックスの再構築(オフライン)は、内部的には新しい領域へインデックスを再作成し、古いインデックスを削除することで、断片化を解消します。したがって、インデックスの再構築中は、インデックス全体がロックされるので、再構築中は、別のトランザクションからはインデックスに対して一切アクセスすることができません。再構築が完了するまでは、ユーザー操作は待ち状態になります。これが「オフライン」と呼ばれる理由です。
クラスター化インデックスの場合は、実際のデータそのものをインデックス内へ格納しているので、再構築中にはテーブル データすべてがアクセスできないことに注意する必要があります。

Note: 再構築にかかる時間
再構築にかかる時間は、インデックスが使用するページ数(ディスク容量)が大きければそれだけ時間がかかります。使用するページ数が大きいのは、行サイズが大きいインデックスで、クラスター化インデックス、カバリングインデックスなどです。特にクラスター化インデックスは、実際のデータそのものを格納しているため、再構築には非常に時間がかかります。

インデックスをオフライン再構築(REBUILD)するには、ALTER INDEX ステートメントを次のように利用します。

ALTER INDEX インデックス名 ON テーブル REBUILD

Let's Try
  1. 社員テーブルへ作成した付加列インデックス「index_姓名」を再構築してみましょう。

    ALTER INDEX index_姓名 ON 社員 REBUILD


    「コマンドは正常に完了しました」と表示されれば、再構築が完了しています。
  2. 次に、dm_db_index_physical_stats 関数をクエリして、断片化の状態をチェックしてみましょう。
    image
    リーフ レベルの avg_fragmentation_in_percent が「0」% になり、断片化が完全に解消されたことを確認できます。また、page_count も「35」ページ(断片化する前と同じ)へ戻っていることを確認できます。
インデックスのオンライン再構築

インデックスの再構築は、オンラインで行うことも可能です。これは、SQL Server 2005 から提供された機能で、Enterprise エディションでのみ利用することができます。インデックスの再構築をオンラインで実行した場合は、再構築中にユーザーがデータへアクセスすることができます。
インデックスの再構築をオンラインで行うには、ALTER INDEX ステートメントを次のように実行します。

ALTER INDEX インデックス名 ON テーブル REBUILD WITH ONLINE = ON

インデックスの再編成(REORGANIZE)

インデックスの再編成は、SQL Server 2000 までは、DBCC INDEXDEFRAG コマンドとして提供されていた機能です。インデックスの再編成は、リーフ ページの断片化のみを解消し、再編成の実行中もユーザーがアクセスすることができます。ただし、ロック中のページはスキップされて、また、断片化の度合いがひどい場合には、インデックスの再構築(REBUILD)よりも実行時間のかかってしまうので注意してください。
インデックスの再編成を実行するには、ALTER INDEX ステートメントを次のように実行します。

ALTER INDEX インデックス名 ON テーブル REORGANIZE

再構築と再編成の違い

インデックスの再編成は、同じ領域を再利用して、それぞれのページを比較して並び替えを行うことで、断片化を解消しています。このような内部動作の違いがあるので、再編成は断片化の割合が大きい場合には、非常に時間がかかってしまいます。
これに対して、インデックスの再構築は、新しい領域にインデックスを再作成します。

image

Note: 断片化の割合が 30% 未満なら再編成、それ以上なら再構築
再構築を行うか、再編成を行うのかのおおまかな指針は、断片化の割合が 30% 未満なら再編成、それ以上なら再構築です。再編成(REORGANIZE)は、断片化の割合がひどい場合には、非常に時間がかかります。たとえば、次の 2つのグラフは、弊社のお客様のデータで、再編成と再構築の実行時間を比較したものです。

image

Note: 再構築と再編成の詳細比較
再構築と再編成の違いについては、次の表を参考にしてください。

image

断片化の事前防止策: FILLFACTOR

インデックスの再構築や再編成を実行して断片化を解消しても、データの追加や更新、削除が行われていくと、また断片化が発生します。断片化の度合いがひどくなっていくと、パフォーマンスへの悪影響も起こってきます。したがって、断片化がすぐに発生しないように事前防止策を講じておくことが重要です。これを行えるのが「FILLFACTOR」です。
FILLFACTORは、「充填率」とも呼ばれ、インデックスの再構築時にリーフ ページ内の領域をデータで占める割合を制御するためのオプションです。これは0~100%の間に設定することができます。たとえば、FILLFACTOR を 80%に設定した場合は、次のように 80%をデータで埋め、20%を空き領域として残すことができます。

image

このように、事前に空き領域を作成しておけば、データが追加されてもこの領域が利用されるので、(空き領域がなくなるまでは)50-50 ページ分割の発生(断片化)を防ぐことができます。

Note: FILLFACTOR のデフォルト値
FILLFACTOR は、デフォルトは 0% に設定されますが、これは 100% と同じ意味で、ページ内の領域をデータですべて埋めます。したがって、デフォルトでは、リーフページは満杯に埋められ、この状態で間に割り込むデータが追加されると 50-50 ページ分割(断片化)が発生します。

FILLFACTOR を設定したインデックスの再構築

FILLFACTOR は、インデックスの再構築時に設定することができます。これは、ALTER INDEX ステートメントを次のように記述します。

ALTER INDEX インデックス名 ON テーブル REBUILD WITH ( FILLFACTOR = 値 )

Let's Try
  1. まずは、社員テーブルに対してデータを 10 件 INSERT して、断片化が発生することを確認してみましょう。(前の Step では、インデックスの再構築を実行したので、断片化が完全に解消されている状態です)。

    INSERT INTO 社員
    VALUES(10011, 'Aiba', 'Saburo', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10012, 'Matuda', 'Saburo', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10013, 'Kato', 'Goro', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10014, 'Matsumoto', 'Goro', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10015, 'Okada', 'Ryu', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10016, 'Yamamoto', 'Hiroko', '女性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10017, 'Oda', 'Hiroshi', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10018, 'Sakamoto', 'Kenji', '男性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10019, 'Uchia', 'Yumi', '女性', '03-1234-xxxx', '')
    INSERT INTO 社員
    VALUES(10020, 'Nakamura', 'Hiromi', '女性', '03-1234-xxxx', '')

  2. データの追加後、dm_db_index_physical_stats 関数を利用して、断片化の状態をチェックしてみましょう。

    SELECT
    avg_fragmentation_in_percent, index_id, index_level, page_count
    FROM
    sys.dm_db_index_physical_stats
    (DB_ID('sampleDB'), OBJECT_ID('社員'), 3, NULL , 'DETAILED')
    ORDER BY index_id, index_level DESC


    image
    リーフ ページが「37.2…」パーセントの断片化が発生していることを確認できます。このように、インデックスを再構築した後に、データが追加された場合は、また、断片化が発生してしまうのです。
  3. 次に、FILLFACTOR を利用して、断片化を事前防止してみましょう。FILLFACTOR を設定するには、次のように ALTER INDEX ステートメントを実行して、インデックスを再構築します。

    ALTER INDEX index_姓名 ON 社員 REBUILD WITH ( FILLFACTOR = 70 )


    FILLFACTOR には、70 を指定することで、30% の空き領域を作っています。
  4. 次に、dm_db_index_physical_stats 関数を利用して、断片化の状態をチェックします。
    image
    断片化が「0」% になって、断片化が起きていない状態であることを確認できます。
    また、リーフ レベルの page_count が、「50」ページへ増えていることにも注目します。これは、30% の空き領域を作ったことで、使用するページ数が増えているためです。
  5. dm_db_index_physical_stats 関数では、avg_page_space_used_in_percent 列を取得すると、ページの平均使用密度を調べることができます。

    SELECT
    avg_page_space_used_in_percent
    ,avg_fragmentation_in_percent, index_id, index_level, page_count
    FROM
    sys.dm_db_index_physical_stats
    (DB_ID('sampleDB'), OBJECT_ID('社員'), 3, NULL , 'DETAILED')
    ORDER BY index_id, index_level DESC


    image
    FILLFACTOR を70%へ設定しているので、avg_page_space_used_in_percent(ページの平均使用密度)が「69.4…」(約70%)であることを確認できます。
  6. 次に、もう一度、社員テーブルに対して、データを 10件 INSERT してみましょう。
  7. データの追加後、dm_db_index_physical_stats 関数を利用して、断片化の状態をチェックしてみましょう。
    image
    結果は、断片化が発生していないことを確認できます。また、page_count も増えていないことを確認できます。
    このように、FILLFACTOR を設定しておくと、今後追加されるデータを考慮して、あらかじめページに余裕をもたせて、インデックスを再構築することができるので、再構築後すぐに断片化が発生することを防ぐことができます。
おわりに

最後までこの自習書の内容を試された皆さま、いかがでしたでしょうか?
インデックスは、SQL Server のパフォーマンスを大きく左右する非常に重要な機能です。1つのテーブルに対して複数のインデックスを作成することができますが、, むやみに作成するのは良くありません。更新のオーバーヘッド(データ更新時に、実際のデータだけでなく、インデックス自体を更新する負荷)があるからです。
また、せっかくインデックスを作成しても、インデックスが活用されなかったり、検索のパフォーマンスが向上しなかったりすることもあります。インデックスは、特性をきちんと理解した上で、付加列インデックスなどを効果的に活用して、パフォーマンスの向上に役立てていただければと思います。
また、インデックスの作成後は、定期的な断片化のチェックも重要です。これを怠ると、「ある日突然遅くなった」という事態になりかねません。FILLFACTOR を設定して断片化を事前防止し、定期的にインデックスを再構築または再編成を実行して、健全な状態へ保つようにしましょう。

列ストア インデックスによる飛躍的な性能向上(2012 の新機能)

SQL Server 2012 からは、パフォーマンスを飛躍的に向上させることができる「列ストア インデックス」という新しいタイプのインデックス機能も提供されました。このインデックスは、SQL Server 2008 R2 の PowerPivot for Excel で実装されたインメモリのカラムベース エンジン(xVelocity エンジン)を RDB へ応用したものです。このエンジンでは、列単位でインデックスを格納し、それらは高度に圧縮されています。
列ストア インデックスは、大量のデータに対する集計処理時に大きな性能向上を期待できる機能で、特に、夜間バッチ処理時(夜間バッチでの日次集計や月次集計処理など)や、DWH(データ ウェアハウス)環境での集計処理時に大変役立つ機能です。弊社のお客様データ(1億2千万件の DWH)を利用して、列ストア インデックスの性能効果を検証したところ、以下のような結果が得られました。

列ストア インデックスの作成方法

列ストア インデックスの作成方法は、非常に簡単で、次のように COLUMNSTORE キーワードを追加するだけです。

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

列ストア インデックスは、性能を向上させるために大変役立つ機能なので、ぜひ活用してみてください。本自習書シリーズの新機能編「No.3 DWH(データ ウェアハウス)関連の新機能」では、列ストア インデックスの効果を簡単に確認できるようにしたスクリプト(ステップ バイ ステップ形式での検証スクリプト)を紹介していたり、列ストア インデックスの利用時の注意点などを説明したりしているので、こちらもぜひご覧いただければと思います。