2013年3月23日土曜日

◆インデックスの基礎

インデックスがない場合の検索時の内部動作

Step1 で説明したように、インデックスが存在しないテーブルでは、どんなデータを検索する場合にも、必ず先頭から最後まで探し続けなければなりません=テーブル スキャン(全件検索)が実行されます。

それでは、これを試してみましょう。

  1. まずは、Management Studio を起動して、クエリ エディターを開きます。
  2. 次に、Step 1 でアタッチした「sampleDB」データベース内へ作成済みの「社員」テーブルのデータを確認します。

    USE sampleDB
    SELECT * FROM 社員


    image
    このテーブルには、5,600件の社員データを格納してあり、「社員番号」や「姓」、「名」、「性別」、「TEL」などを格納しています。
グラフィカル実行プランで内部動作の確認

SQL Server では、内部的な処理がどのように実行されたのかを簡単に確認できる「グラフィカル実行プラン」という機能があります。これにより、データ検索がどのように実行されたのかを確認することができます。では、これを試してみましょう。

  1. グラフィカル実行プランは、Management Studio で、「クエリ」メニューの「実際の実行プランを含める」をクリックすることで表示することができます。
    image
  2. 続いて、クエリ エディターで次のように入力して、「社員」テーブルから「姓」列が「Aoki」さんのデータを検索してみましょう。

    USE sampleDB
    SELECT * FROM 社員 WHERE 姓 = 'Aoki'

    image
    結果は、12件の「Aoki」さんを取得することができました。
  3. 次に、[実行プラン]タブをクリックして、実行プラン(内部的な実行方法)を表示します。
    image
    「Table Scan」アイコンが表示されて、テーブル スキャン(全件検索)が実行されたことを確認できます。「社員」テーブルには、インデックスを 1つも作成していないので、SQL Server が Aoki さんのデータを探すには、テーブル スキャンをするしかありません。
グラフィカル実行プランを表示しないようにする

一度設定した「実際の実行プランを含める」は、クエリ エディターを閉じるか、次のように、もう一度[クエリ]メニューから[実際の実行プランを含める]をクリックするまで有効です。
image

Note: 推定実行プランの表示(クエリを実行せずに実行プランを確認)
グラフィカル実行プランは、クエリを実行しなくても確認することができます。これを行うには、次のように[クエリ]メニューの[推定実行プランの表示]をクリックします。
image

インデックスの作成: CREATE INDEX
インデックスの作成

インデックスを作成するには、CREATE INDEX ステートメントを利用します。構文は、次のとおりです。

CREATE [CLUSTERED または NONCLUSTERED] INDEX インデックス名
ON テーブル名(列名)

CLUSTERED を指定した場合は「クラスター化インデックス」、NONCLUSTERED を指定した場合は「非クラスター化インデックス」が作成されます(両者の違いについては、Step3 で説明します)。省略時は、非クラスター化インデックスが作成されます。

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

インデックスは、オブジェクト エクスプローラーで、次のように[インデックス]フォルダーを右クリックして、[新しいインデックス]をクリックしても作成することができます。
image

image

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

  1. クエリ エディターで次のように入力して、社員テーブルの「姓」列に対してインデックスを作成してみましょう。
    CREATE INDEX index_姓 ON 社員(姓)
  2. [クエリ]メニューから[実際の実行プランを含める]をクリックして、グラフィカル実行プランの表示を有効にしてから、前の手順でテーブル スキャンになったクエリ(Aoki さんの検索)を実行してみましょう。
    SELECT * FROM 社員 WHERE  姓 = 'Aoki'

    image
    実行後、[実行プラン]タブを開くと、今度は、テーブル スキャンではなく、Index Seek と RID Lookup というアイコンが表示されていることを確認できます。Index Seek は、インデックスを利用した検索であることを表し、利用したインデックスの名前「社員.index_姓」が表示されます。RID Lookup については、後述します。
  3. 次に、インデックスを作成していない、ほかの列で検索してみましょう。クエリ エディターへ次のように入力して、「名」列に「Taro」が格納されているデータを検索してみましょう。
    image
    結果は、テーブル スキャンになります。
    インデックスは、列ごとに作成するものなので、作成した列以外の検索では使用されません。「名」列には、インデックスを作成していないので、SQL Server が、「Taro」というデータを探すには、テーブル スキャンをするしかないのです。したがって、「名」列での検索を高速化したい場合には、この列にもインデックスを作成する必要があります。
インデックスの内部構造
インデックスの内部構造

インデックスは、内部的には次のようなツリー(Tree:木)構造で作成されます。

image

ツリーは、最上部を「ルート」ノード、中間部を「中間」ノード、最下部を「リーフ」ノードと呼びます。データは、昇順(小さい順)に並べ替えられて、データの範囲によって枝分かれします。ツリーの構造(枝分かれの数や何階層になるのか)は、データ量やデータサイズによって変化します。
SQL Server には、インデックスの種類として、「クラスター化インデックス」と「非クラスター化インデックス」の 2つがありますが、両者の違いについては、Step3 で詳しく説明します。

非クラスター化インデックスの内部構造

前の手順では、社員テーブルの「姓」列に対してインデックス(非クラスター化インデックス)を作成しましたが、この場合のインデックスの内部構造は、次のようになります。

image

インデックス内は、「姓」列のデータが昇順に並べ替えられて、データの範囲によって枝分かれしています。リーフ ノードには、実際のデータへのポインター(位置情報)が格納され、ポインターには「行識別子」(RID:Row ID)が使用されています。
このように、インデックスを構成した場合の検索は、ツリーを調べるだけで済むので、高速になります。たとえば、前の手順で行った「WHERE 姓='Aoki'」という検索条件であれば、「A」が格納されている範囲のみを調べるだけで済むのです。
なお、正確なインデックスの内部構造については、Step3 で詳しく説明します。

インデックスの削除と無効化
インデックスの削除: DROP INDEX

インデックスを削除するには、DROP INDEX ステートメントを利用します。構文は、次のとおりです。

DROP INDEX テーブル名.インデックス名
または
DROP INDEX インデックス名 ON テーブル名

GUI でのインデックスの削除

オブジェクト エクスプローラーから、インデックスを削除したい場合には、次のように[インデックス]フォルダーを展開して、該当インデックスを右クリックし、[削除]をクリックします。

image

インデックスの無効化: ALTER INDEX .. DISABLE

インデックスは、無効化することも可能です。無効化は、削除に似ていますが、無効化した場合は、インデックスを再作成することなく、再構築するだけで、再び有効にすることができるので、一時的に外しておきたいインデックスがある場合に便利です(インデックスの再構築については、Step 4 で説明します)。
インデックスを無効化するには、ALTER INDEX ステートメントを次のように利用します。

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

無効化したインデックスを、有効化したい場合は、次のように記述して、インデックスを再構築します。

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

GUI でのインデックスの無効化と有効化

オブジェクト エクスプローラーから、インデックスを無効化したい場合は、[インデックス]フォルダーを展開して、該当インデックスを右クリックし、[無効化]をクリックします。また、無効化したインデックスを有効化したい場合は[再構築]をクリックします。

image

それでは、インデックスの無効化を試してみましょう。

  1. 前の手順で作成した「index_姓」インデックスを無効化してみましょう。

    ALTER INDEX index_姓
    ON 社員 DISABLE

  2. 次に、インデックスが無効化されたことを確認するために、「姓」列が「Aoki」さんのデータを検索してみましょう。
    SELECT * FROM 社員WHERE 姓 = 'Aoki'

    image
    グラフィカル実行プランを表示すると、結果は、テーブル スキャンとなり、インデックスが利用されていないことを確認できます。
  3. 次に、「index_姓」インデックスを有効化してみましょう。

    ALTER INDEX index_姓
    ON 社員 REBUILD

  4. 有効化(再構築)が完了したら、もう一度同じ検索を実行してみましょう。
    SELECT * FROM 社員 WHERE 姓 = 'Aoki'

    image
    今度は、Index Seek が表示されて、インデックスが利用されたことを確認できます。

1 件のコメント:

  1. インデックスってどうしたらいいの?
    …と途方に暮れていましたが、とても分かり易い記事で助かりました。ありがとうございます。

    返信削除