none
クラスター化インデックスと主キーのインデックスについて RRS feed

  • 質問

  • SQLServer 2005で数十万件のデータを想定したテーブルのアクセス(主キーあり)を

    チューニングをしています。

    以下の疑問があり、お答いただけたらうれしいです。

     

    1.主キーのインデックス(非クラスタ)とクラスタ化したインデックスを使用しているのですが

      実際のアクセスの際、どちらのインデックスが使用されるのでしょうか?

      (現在13万件のテーブルにインサートをしているのですがCPU100%が長時間続いています。)

     

    2.希望としてはクラスタ化したインデックスを使用して最大限のアクセスを得たいのですが、

      SELECT文等で、インデックスを指定することは不可能でしょうか?

     

    3.主キーで自動的に作成されるインデックスをマネージメントスタジオでクラスタ化しようとすると

      「主キーが含まれている」の旨のエラーが表示されクラスタ化できません。

      クラスタ化はできないのでしょうか?

     

    以上、よろしくお願い致します。

     

    2008年6月20日 4:52

回答

  • 1.INSERT 処理ではインデックスが多いほうが逆にオーバーヘッド。
    2.検索条件にインデックス列しか含まれていなければそれが使われます。
    3.主キーはクラスタ化インデックス。

     

    パフォーマンスが出なくて困っているなら、インデックスの見直しより前に SQL の見直しを行ったほうがいいと思います。
    「クラスタ化インデックスにすれば速い」という考えもまずいかなと。射影で使用する列を「非クラスタ化インデックス」に、選択で使用する列を「付加列インデックス」に指定するといいかもしれません。

    (あまり詳しくないので、多分)

    2008年6月20日 5:18
  •  

    SQL Serverでは、明示的に指定しない限り主キーはクラスタ化インデックスとして扱われます。
    クラスタ化インデックスは一つのテーブルで一つしか指定できません。
    (クラスタ化インデックスでは、レコードの配置を物理的に配置するため)

     また、クラスタ化インデックスはSELECTには効果がありますが、INSERTや、クラスタ化インデックスのキーを更新するUPDATEは、
    逆に遅くなります。
     クラスタ化インデックスの特性上、新たにキーを追加/更新した場合にレコードの配置を変更するためです。


    > SELECT文等で、インデックスを指定することは不可能でしょうか?

     クエリヒントでインデックスを指定することは可能ですが、オプティマイザ等がきかなくなるためお勧めはしません。


    チューニングのポイントですが、以下のことが考えられます。
     ・テーブルやインデックスにFILLFACTORを設定する。
     ・大量のINSERT前にインデックスを削除し、INSERTか終わった後でインデックスを再作成する。
      (大量にINSERTした後は、UPDATE STASTICSで統計情報を更新しておくと良いかも)

     ・SQL Server 2005を使っているとのことですので、テーブルをパーティション化してみる。

    などが考えられます。
    後はテーブルの構成やインデックスの内容がわからないため、一般的な話になってしまいますが。

    2008年6月20日 7:56
  •  

    > わかりました。マネージメントスタジオでは非クラスタと表示されるのは疑問ですが。

     

    どのようにテーブルを作成したかわからないので考えられる可能性ですが、

     ・NONCLUSTERDを付けて主キーを設定した。

     ・主キー以外にクラスタ化インデックスが作成されていた。

      もしくは主キー作成前にクラスタ化インデックスが作成されていて、主キー作成後に削除した。

    といったことが考えられます。

    主キー以外のキーをクラスタ化インデックスにした方が効率が良い場合もあるので、

    あえて主キーを非クラスタ化インデックスとして作成することもあります。

     

     

    > 毎日定刻に再構築するような必要はあるのでしょうか?(更新追加頻度にもよりますが)

     

    この辺りはデータベースのデータ量や更新頻度などにもよるので一概に言えませんが、

    SQL Serverでは、SQL Management Studioから[管理]-[メンテナンスプラン]にて

    ウィザード形式でメンテナンスプランを作成できますので、様子を見ながら設定してはどうでしょうか?

     

    2008年6月25日 8:12

すべての返信

  • 1.INSERT 処理ではインデックスが多いほうが逆にオーバーヘッド。
    2.検索条件にインデックス列しか含まれていなければそれが使われます。
    3.主キーはクラスタ化インデックス。

     

    パフォーマンスが出なくて困っているなら、インデックスの見直しより前に SQL の見直しを行ったほうがいいと思います。
    「クラスタ化インデックスにすれば速い」という考えもまずいかなと。射影で使用する列を「非クラスタ化インデックス」に、選択で使用する列を「付加列インデックス」に指定するといいかもしれません。

    (あまり詳しくないので、多分)

    2008年6月20日 5:18
  •  囚人 さんからの引用

    1.INSERT 処理ではインデックスが多いほうが逆にオーバーヘッド。
    2.検索条件にインデックス列しか含まれていなければそれが使われます。
    3.主キーはクラスタ化インデックス。

     

    パフォーマンスが出なくて困っているなら、インデックスの見直しより前に SQL の見直しを行ったほうがいいと思います。
    「クラスタ化インデックスにすれば速い」という考えもまずいかなと。射影で使用する列を「非クラスタ化インデックス」に、選択で使用する列を「付加列インデックス」に指定するといいかもしれません。

    (あまり詳しくないので、多分)

     

    ご回答ありがとうございました。

    3の回答ですが、マネージメントスタジオで見ると、(一意、非クラスタ化)と表示されます。

    また、クラスタ化インデックスを追加したのは、自分ではなく、チューニングアドバイザです。

    CPU100%占有からみて、非クラスタインデックスが使用されているのではとの憶測です。

     

    SQLの見直しですが、SELECT文とINSERT文を繰り返すだけなので、見直しは困難との

    結論から、インデックス面からチューニングを試みています。

    付加列インデックスとは、プライマリXMLのことでしょうか??

     

     

    2008年6月20日 5:43
  •  

    SQL Serverでは、明示的に指定しない限り主キーはクラスタ化インデックスとして扱われます。
    クラスタ化インデックスは一つのテーブルで一つしか指定できません。
    (クラスタ化インデックスでは、レコードの配置を物理的に配置するため)

     また、クラスタ化インデックスはSELECTには効果がありますが、INSERTや、クラスタ化インデックスのキーを更新するUPDATEは、
    逆に遅くなります。
     クラスタ化インデックスの特性上、新たにキーを追加/更新した場合にレコードの配置を変更するためです。


    > SELECT文等で、インデックスを指定することは不可能でしょうか?

     クエリヒントでインデックスを指定することは可能ですが、オプティマイザ等がきかなくなるためお勧めはしません。


    チューニングのポイントですが、以下のことが考えられます。
     ・テーブルやインデックスにFILLFACTORを設定する。
     ・大量のINSERT前にインデックスを削除し、INSERTか終わった後でインデックスを再作成する。
      (大量にINSERTした後は、UPDATE STASTICSで統計情報を更新しておくと良いかも)

     ・SQL Server 2005を使っているとのことですので、テーブルをパーティション化してみる。

    などが考えられます。
    後はテーブルの構成やインデックスの内容がわからないため、一般的な話になってしまいますが。

    2008年6月20日 7:56
  • ご回答ありがとうございました。

     

    >SQL Serverでは、明示的に指定しない限り主キーはクラスタ化インデックスとして扱われます。
     わかりました。マネージメントスタジオでは非クラスタと表示されるのは疑問ですが。


    > SELECT文等で、インデックスを指定することは不可能でしょうか?

     クエリヒントでインデックスを指定することは可能ですが、オプティマイザ等がきかなくなるためお勧めはしません。

     わかりました。


    >チューニングのポイントですが、以下のことが考えられます。
     ・テーブルやインデックスにFILLFACTORを設定する。
     ・大量のINSERT前にインデックスを削除し、INSERTか終わった後でインデックスを再作成する。
      (大量にINSERTした後は、UPDATE STASTICSで統計情報を更新しておくと良いかも)

     ・SQL Server 2005を使っているとのことですので、テーブルをパーティション化してみる。

    などが考えられます。

    わかりました。統計やインデックスの再構築についてはORACLEと比べてどうなのでしょうか?

    毎日定刻に再構築するような必要はあるのでしょうか?(更新追加頻度にもよりますが)

    FILLFACTORについては、予測が難しいのですがこれも検討したいと思います。

    丁寧にご回答していただき、ありがとうございました。

     

     

    2008年6月20日 8:54
  •  

    > わかりました。マネージメントスタジオでは非クラスタと表示されるのは疑問ですが。

     

    どのようにテーブルを作成したかわからないので考えられる可能性ですが、

     ・NONCLUSTERDを付けて主キーを設定した。

     ・主キー以外にクラスタ化インデックスが作成されていた。

      もしくは主キー作成前にクラスタ化インデックスが作成されていて、主キー作成後に削除した。

    といったことが考えられます。

    主キー以外のキーをクラスタ化インデックスにした方が効率が良い場合もあるので、

    あえて主キーを非クラスタ化インデックスとして作成することもあります。

     

     

    > 毎日定刻に再構築するような必要はあるのでしょうか?(更新追加頻度にもよりますが)

     

    この辺りはデータベースのデータ量や更新頻度などにもよるので一概に言えませんが、

    SQL Serverでは、SQL Management Studioから[管理]-[メンテナンスプラン]にて

    ウィザード形式でメンテナンスプランを作成できますので、様子を見ながら設定してはどうでしょうか?

     

    2008年6月25日 8:12