QA@IT

SQLServerのクラスタ化インデックスと非クラスタ化インデックスについて

6270 PV

SQLServer 2012で以下のような2つのテーブル(classess, staffs)があり、以下のようなSQLを発行するとします。

-- テーブル定義
CREATE TABLE [dbo].[classes](
    [id] [int] IDENTITY(1001,1) NOT NULL,
    [name] [varchar](50) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[staffs](
    [id] [int] IDENTITY(1001,1) NOT NULL,
    [class_id] [int] NOT NULL,
    [name] [varchar](50) NOT NULL,
    [address] [varchar](1000) NOT NULL
) ON [PRIMARY]

-- 発行したいSQL
SELECT id, class_id, name, address
FROM staffs
WHERE class_id = 1003

このSQLの為に、次の2パターンで実際の実行プランに差異が発生するのですが、
パターン2の場合に、非クラスタ化インデックスをclass_idに貼っているにも関わらず、
インデックススキャンになってしまうのは何故なのでしょうか?

■パターン1(クラスタ化インデックスのみをclass_idに付与)

-- クラスタ化インデックスをclass_idに付与
CREATE UNIQUE CLUSTERED INDEX [idx_staffs_class_id] ON [dbo].[staffs]
(
    [class_id] 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]

■パターン2(クラスタ化インデックスをidに付与、非クラスタ化インデックスをclass_idに付与)

-- クラスタ化インデックスをidに付与
CREATE UNIQUE CLUSTERED INDEX [idx_staffs_id] ON [dbo].[staffs]
(
    [id] 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]

-- 非クラスタ化インデックスをclass_idに付与
CREATE NONCLUSTERED INDEX [idx_staffs_class_id2] ON [dbo].[staffs]
(
    [class_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

また、パターン2の場合の非クラスタ化インデックスは

SELECT COUNT(*)
FROM staffs
WHERE class_id = 1003

のように件数取得の際には参照されるようなのですが、
今回発行したいSQLには何の影響も及ぼさないが、別途件数を取得する処理があるのであれば
付与する意味があるのとの解釈でよろしいでしょうか?

  • 実行計画は掲載できますか? -

回答

staffsテーブルの件数があまり多くないか、class_idのカーディナリティが低いせいではないでしょうか?
その例でのインデックススキャンは実質テーブルスキャンだと思いますが、オプティマイザがテーブルスキャンを選ぶのって基本的にこれが原因のような。

あとは統計情報が更新されておらず、インデックスを有効に利用できなかった可能性もあるかな。
基本的には自動更新されますが、更新系の処理の度に更新されるわけではないので、テストデータを1行ちょろっとINSERTした程度では更新されないんじゃないかな。
オプティマイザは統計をもとに適切なインデックスを選択するので、統計が古いと期待した動きをしてくれないこともあります。

もし本番環境のパフォーマンスチューニングを行おうとテスト環境でインデックスを切り貼りしているのなら、本番と同等のテストデータを使用しないとあまり意味がないので注意してください。

編集 履歴 (0)
ウォッチ

この質問への回答やコメントをメールでお知らせします。