インデックスキーが同じで、付加列インデックスのカラム違いが存在する場合のインデックス再編について

Question

お世話になります。

SQLServer2008のテーブルの付加列インデックスについての質問です。

例えば仮に

T_UserHoge　が、以下のような構造で、同じDelFlgというキーで

二つのインデックスが存在する場合ですが、

CREATE TABLE [dbo].[T_UserHoge](

    [SystemID] [int] NOT NULL,

    [Data_A] [varchar](32) NOT NULL,

    [Data_B] [varchar](32) NOT NULL,

    [Data_C] [varchar](32) NOT NULL,

    [DelFlg] [int] NOT NULL,

 CONSTRAINT [PK_T_UserHoge] PRIMARY KEY CLUSTERED 

(

    [SystemID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX01] ON [dbo].[T_UserHoge] 

(

    [DelFlg] ASC

)

INCLUDE ( [SystemID],

[Data_A],

[Data_B]) 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 [IX02] ON [dbo].[T_UserHoge] 

(

    [DelFlg] ASC

)

INCLUDE ( [Data_B],

[Data_C]) 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 [IX_A] ON [dbo].[T_UserHoge] 

(

    [DelFlg] ASC

)

INCLUDE ( [SystemID],

[Data_A],

[Data_B],

[Data_C]) 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

ご教示お願い致します。

Answer 1

そんなのはクエリ側の特性によるので、何ともいえないですネ。
クエリされないならインデクスは1つどころか0個でまとめてもいいし、
全般に負荷が低いとかDelFlgの検索件数が小さいなら付加列自体要らないし、
SystemIDが使われてないならClustered IndexをDelFlg側に付けるとかの話になるので

---

jzkey

Answer 2

gatuwoさん、こんにちは。フォーラムオペレーターのクモです。
MSDNフォーラムにご投稿くださいましてありがとうございます。

一つにすることができるかどうかは、クエリの内容と頻繁に使用するクエリの種類によって異なります。

まずは、インデックス「IX01」を少し変更させます。

CREATE NONCLUSTERED INDEX [IX01] ON [dbo]. [T_UserHoge] ( [DelFlg] ASC)
INCLUDE (
-------[SystemID]------- 
「SystemID」はプライマリ キー CLUSTERED であるため、NONCLUSTEREDインデックスに追加する必要はありません。CLUSTEREDインデックス キーとして存在します。
[Data_A],[Data_B]) 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]

次のようなクエリを頻繁に使用する場合は、2 つの NONCLUSTERED インデックスを一つにしたほうがいいかもしれません。
SystemID、Data_A、Data_B、Data_CをT_UserHoge(...DelFlg...)から選択します。


どうぞよろしくお願いいたします。

---

MSDN/ TechNet Community Support Kumo ～参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、 ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、[e-mail]までお気軽にお問い合わせください。～

Answer 3

gatuwoさん

jzkeyさんの仰る通り、もろもろの状況によりますが、二つのインデックスともに必要な状況で、かつどちらのインデックスも活躍してくれているという前提であれば、まとめて問題ありません。

一般的に、インデックスが多いほうがreadは速くなり、writeは遅くなる傾向にあります。

したがって、理想的な話をするなら、gatuwoさんが運用されている環境におけるread/writeのクエリの総実行時間を小さくする方が、理想的なインデックスの組み合わせとなります。

そのため「まとめたほうが良い」とまでは言い切れませんが、経験上、負荷列が増えることによる読み取りページ数増加に伴う実行速度の低下は無視できるほど小さなものですし、インデックス数は少ないに越したことはありません。

以上をまとめると、「どちらのインデックスも活躍してくれている状況なのであれば、自分だったらまとめる」というのが僕の意見です。

--

参考になりましたら投票および「回答としてマーク」をお願いいたします。

Answer 4

付加列に追加している列のサイズがそれほど大きくなく、数も多くないので、1つの非クラスタ化インデックスにまとめても、まとめなくても、大きくパフォーマンスに影響を及ぼすことはないかと思います。
一般論で言えば、多数の列が存在するテーブル上の特定の列のみを参照するクエリの場合、条件句に指定された列に対してインデックスを指定、かつ、該当クエリで条件句以外で参照される列のみを付加列として指定した非クラスタ化インデックスを作成することで、クラスタ化インデックス(HEAP:RID)へのLookupが発生しない、かつ、クエリ実行時に読み取りが必要となるページ数を最小にすることができるため、クエリのパフォーマンスが向上することが期待できます。
しかしながら、特定のクエリに合わせて、大量の非クラスタ化インデックスを作成すると、インデックス領域のサイズが増え、かつ、インデックスのメンテナンスが必要となる分、DMLクエリ(UPDATE/INSERT/DELETE)のパフォーマンスが低下する恐れがあります。
そのため、上記の制約を踏まえながら、トレードオフで検討する感じになるかとおもいます。
なお、以下のようなクエリを実行することで、インデックスの使用頻度がわかるため、インデックスを削除する際の検討材料にすることができるかと思います。

---
select ind.name, ind.type_desc, ddi.* from sys.dm_db_index_usage_stats ddi
left join sys.indexes ind on ddi.index_id = ind.index_id and ddi.object_id = ind.object_id
---