none
付加列インデックスと複合インデックスのインデックス容量やページサイズの違いについて RRS feed

  • 質問

  • お世話になっております。

    version:SQLServer2008 R2

    カバリングインデックスを作成する際は付加列か複合インデックスがあると思いますが、両者の違いについて下記のように理解しておりました。

    ========

    付加列→リーフノードにだけカラムが追加されるので、複合インデックスよりもページサイズは小さく、したがってディスク容量も小さめ

    複合インデックス→ルートノードと中間ノードにもカラムが追加されるので、付加列よりも絞込みには有利だが、ページサイズは付加列の場合より大きく、したがってディスク容量も大きめ

    ========

    しかし、実際に付加列インデックスと複合インデックスを同様のカラムをつかって作成してみたところ、まったく同じページサイズとなりました。。。

    こちら、付加列インデックスと複合インデックスの違いについての認識が間違っていますでしょうか?

    もし間違っていなければ、なぜこのような結果になったのか、どなたかご教授いただけますと大変助かります。

    TestTable

    SomeID1 INT

    SomeID2 INT

    SomeID3 INT

    SomeName NVARCHAR(200)

    ■付加列インデックス

    CREATE NONCLUSTERED INDEX [IndexTest1] ON [dbo].[TestTable]
    (
        [SomeID1] ASC
    )
    INCLUDE ( [SomeID2],
    [SomeID3],
    [SomeName]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO

    ■複合インデックス

    CREATE NONCLUSTERED INDEX [IndexTest2] ON [dbo].[TestTable]
    (
        [SomeID1] ASC,
        [SomeID2] ASC,
        [SomeName] ASC,
        [SomeID3] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO

    sys.dm_db_index_physical_statsにおけるpage_countがいずれも24323となりました。。。

    以上、ご確認およびご回答いただけますと幸いです。よろしくお願いいたします。

    2016年3月24日 11:05

回答

  • > 付加列→リーフノードにだけカラムが追加されるので、複合インデックスよりもページサイズは小さく、したがってディスク容量も小さめ
    概ねご認識通りです。
    細かい話をすると、ページサイズは 8KB で固定ですので、レコードサイズ(行サイズ)が小さく、従って使用するページ数が少なくなりディスク容量も小さめ、といった辺りがより厳密には正確かと思います。

    > 複合インデックス→ルートノードと中間ノードにもカラムが追加されるので、付加列よりも絞込みには有利だが、ページサイズは付加列の場合より大きく、したがってディスク容量も大きめ
    ご認識通りです。
    細かい話をすると、上の通り「何のサイズが大きいか」だけ注意頂ければと思います。

    > 中間ノードが無ければ、付加列=複合インデックスとなりそうだなと理解しました。

    ご認識通りです。
    但し、オプティマイザが付加列を絞りこみで上手く使ってくれるかどうかはちょっと分からないので、そのあたりは実行プランを見ながら確認してみるのもありかと思います。


    MCITP(Database Developer/Database Administrator)

    • 回答としてマーク maaaaaaaa8 2016年4月7日 2:57
    2016年3月28日 3:31

すべての返信

  • すみません、自己解決したかもしれません。。。

    DBCC IND()

    で該当インデックスを調べたところ、付加列も複合インデックスも、リーフノードのみから構成されているようでした。

    中間ノードが無ければ、付加列=複合インデックスとなりそうだなと理解しました。

    レコード量が多かったり、インデックスのサイズが大きい場合は中間ノードができて、中間ノードができたときだけ付加列インデックスサイズ<複合インデックスサイズ となりそうですね。。。

    こちらの認識にもし間違いがあったり、補足などあれば教えていただけますと大変ありがたいです。

    2016年3月24日 11:56
  • > 付加列→リーフノードにだけカラムが追加されるので、複合インデックスよりもページサイズは小さく、したがってディスク容量も小さめ
    概ねご認識通りです。
    細かい話をすると、ページサイズは 8KB で固定ですので、レコードサイズ(行サイズ)が小さく、従って使用するページ数が少なくなりディスク容量も小さめ、といった辺りがより厳密には正確かと思います。

    > 複合インデックス→ルートノードと中間ノードにもカラムが追加されるので、付加列よりも絞込みには有利だが、ページサイズは付加列の場合より大きく、したがってディスク容量も大きめ
    ご認識通りです。
    細かい話をすると、上の通り「何のサイズが大きいか」だけ注意頂ければと思います。

    > 中間ノードが無ければ、付加列=複合インデックスとなりそうだなと理解しました。

    ご認識通りです。
    但し、オプティマイザが付加列を絞りこみで上手く使ってくれるかどうかはちょっと分からないので、そのあたりは実行プランを見ながら確認してみるのもありかと思います。


    MCITP(Database Developer/Database Administrator)

    • 回答としてマーク maaaaaaaa8 2016年4月7日 2:57
    2016年3月28日 3:31
  • nagino様

    ご回答いただきありがとうございます。

    おおむね認識どおりとのことで安心しました。また、補足も丁寧にしていただき大変勉強になりました。

    付加列を絞り込みで使ってくれるかについては、実行プランを見て確認するよう気をつけたいと思います。

    回答としてマークさせていただきました。


    2016年4月7日 2:58