none
インデックス付Viewを作成するときの懸念点について RRS feed

  • 質問

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

    Version: Microsoft SQL Server 2008 R2 (SP1)

    ご相談したい内容は、
    「データが頻繁に更新or挿入されるテーブルを使ってインデックス付Viewを作成しても問題ないか?」です。

    https://technet.microsoft.com/ja-jp/library/ms187864(v=sql.105).aspx
    こちらの「インデックス付きビューをデザインする際のガイドライン」に、


    ==========

    インデックス付きビューは、基になるデータを更新する頻度が低い場合に最も効果的です。インデックス付きビューをメンテナンスする方が、テーブル インデックスのメンテナンスよりもコストがかかる場合があります。基になるデータを頻繁に更新する場合、インデックス付きビューの使用によるパフォーマンス向上よりも、インデックス付きビューのデータ メンテナンスにかかるコスト増加の方が大きい場合があります。

    ==========

    とあります。ただ、個人的には、↑はcount_big(*)やsum(*)などの集計処理を含むインデックス付Viewの場合には更新コストが大きいので、頻繁に基テーブルが更新されるようなケースではインデックス付Viewは不適切な選択だ、という意味かなと解釈しております。
    今回やりたいことは、ハイフン(-)が入った電話番号カラムから、ハイフンを取り除いた数値だけの電話番号カラムを作って、インデックスを張って問い合わせ処理を高速に行いたい、というものです。
    計算列を作成し、計算列へのインデックス追加も検討したのですが、諸事情によりテーブルへのカラムの新規追加はできる限り避けたいです。

    インデックスを作成しようと考えているViewでは、replace, castしか追加の演算として行っておりませんので、更新時のコストも問題ないのかなと考えております。

    したがって、ガイドライン的にはNGな「挿入/更新が頻繁に実行されるテーブルを使ってインデックス付Viewを作成する」ですが、今回のケースでは作成しても問題ないのかな?というのが個人的な見解です。

    しかしながら不安がありますので、私の認識があっているか、また、このようなケースでインデックス付Viewを作成することによる、懸念点などございましたらご教授いただけますと大変ありがたいです。


    以下、私のほうで実験してみた結果です。
    ■ 要件
    電話番号でデータを検索するシステムを作成したいが、下記2点を満たすことが必要
        ・カラム[tel]にはハイフン(-)または数値(0-9)のみ。ハイフンはどこに入ってもOK(例:090-0000-0000 , 03-000-0000)
        ・検索時の入力は数値のみで行いたい(例:09000000000 , 030000000)

    -- 実験用テーブル
    CREATE TABLE [dbo].[TelTest](
    	[pk] [int] IDENTITY(1,1) NOT NULL,
    	[tel] [varchar](20) NOT NULL
    CONSTRAINT [PK_TelTest] PRIMARY KEY CLUSTERED
    (
    	[pk] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    go
    
    -- TelTestテーブルに電話番号(っぽいテストデータ)を100件いれる
    CREATE TABLE [dbo].[TelNumber](
    	[num] [int] IDENTITY(1,1) NOT NULL
    	,[d] [datetime] NOT NULL
    )
    go
    
    insert into TelNumber(d) values(getdate())
    go 999
    
    insert into TelTest (tel) 
    select 
    	  cast((select top (1) num from TelNumber order by newid()) as varchar)
    	+ '-'
    	+ cast((select top (1) num from TelNumber order by newid()) as varchar)
    	+ '-'
    	+ cast((select top (1) num from TelNumber order by newid()) as varchar)
    
    go 100
    
    
    --実際のデータ量は一千万件ほどを予定しており、下記SQLではインデックスが効かずとても遅いため、インデックス付Viewを作成
    declare @key varchar(20)
    set @key = replace((select top (1) tel from TelTest), '-', '')
    
    --scanされるので遅い(1000件だと問題ないけど一千万件だと遅い)
    select * from TelTest where Replace(tel, '-', '') = @key
    go
    
    --挿入/更新コスト確認
    --insert
    insert into TelTest (tel) 
    select 
    	  cast((select top (1) num from TelNumber order by newid()) as varchar)
    	+ '-'
    	+ cast((select top (1) num from TelNumber order by newid()) as varchar)
    	+ '-'
    	+ cast((select top (1) num from TelNumber order by newid()) as varchar)
    
    --udpate
    update TelTest set tel = '111-1111-1111' where pk = 500
    go
    
    --インデックス付Viewを作成
    CREATE VIEW [dbo].[VTel]
    WITH SCHEMABINDING
    AS
        SELECT  cast(Replace(tel, '-', '') as varchar(20)) as tel2, pk
        FROM dbo.TelTest
    GO
    
    CREATE UNIQUE CLUSTERED INDEX IDX_TelTest 
        ON dbo.vTel (tel2, pk);
    GO
    
    declare @key varchar(20)
    set @key = replace((select top (1) tel from TelTest), '-', '')
    
    --seekになる
    select * from VTel with(noexpand) where tel2 = @key
    go
    
    --挿入/更新コスト確認
    --insert(View作成前と大差なし)
    insert into TelTest (tel) 
    select 
    	  cast((select top (1) num from TelNumber order by newid()) as varchar)
    	+ '-'
    	+ cast((select top (1) num from TelNumber order by newid()) as varchar)
    	+ '-'
    	+ cast((select top (1) num from TelNumber order by newid()) as varchar)
    
    --udpate(View作成前と大差なし)
    update TelTest set tel = '111-1111-1111' where pk = 500
    
    drop view VTel
    drop table TelTest
    drop table TelNumber


    2016年11月9日 6:05

回答

  • >ただ、個人的には、↑はcount_big(*)やsum(*)などの集計処理を含むインデックス付Viewの場合には更新コストが大きいので、頻繁に基テーブルが更新されるようなケースではインデックス付Viewは不適切な選択だ、という意味かなと解釈しております。

    違うでしょ。
    「インデックス付Viewは更新コストが大きいので更新が少ない方が向いている」という一般的なことしか書いてないでしょ。
    これは元テーブルとViewとで2個インデックスを作るので至極至極当たり前の話しですよね。

    その上で、「テストしてみたら、頻繁に更新してもあんまりコストかからないし、それよりもクエリのパフォーマンス上げる方が優先だぜ」と判断したのなら、全然アリだと思いますよ。

    繰り返すと、
    一般的な話は
    「インデックス付Viewは更新コストが大きいので更新が少ない方が向いている」
    でしかないので、あとは個別案件毎の判断になる、ということ、
    テスとした上で貴方が貴方が判断したらそれで正しいのです。

    • 回答の候補に設定 星 睦美 2016年11月17日 4:21
    • 回答としてマーク maaaaaaaa8 2016年11月18日 1:56
    2016年11月9日 8:19

すべての返信

  • 内容は読んでいませんが挙げられたURLのページ内で見落とされがちな注意点を引用しておきます。

    インデックス付きビューは SQL Server 2008 のどのエディションでも作成できます。SQL Server 2008 Enterprise では、クエリ オプティマイザによる判断でインデックス付きビューが自動的に使用されます。その他のエディションでインデックス付きビューを使用するには、NOEXPAND テーブル ヒントを指定する必要があります。

    少し説明するとEnterpriseエディション以外の場合、通常のクエリーではインデックス付きビューは期待する動作をしません。各々のクエリー文にNOEXPANDヒントを付与する必要があります。自動生成等でクエリー文を編集できない場合、インデックス付きビューは全く役に立ちません。

    2016年11月9日 6:25
  •  佐祐理さん

    早速のご返信ありがとうございます。ご指摘いただきました点に関しては把握しておりまして、with(noexpand)をクエリにつける改修は可能ですので、インデックス付ビューが期待する効果を発揮できる点については確認済みです。

    2016年11月9日 7:48
  • >ただ、個人的には、↑はcount_big(*)やsum(*)などの集計処理を含むインデックス付Viewの場合には更新コストが大きいので、頻繁に基テーブルが更新されるようなケースではインデックス付Viewは不適切な選択だ、という意味かなと解釈しております。

    違うでしょ。
    「インデックス付Viewは更新コストが大きいので更新が少ない方が向いている」という一般的なことしか書いてないでしょ。
    これは元テーブルとViewとで2個インデックスを作るので至極至極当たり前の話しですよね。

    その上で、「テストしてみたら、頻繁に更新してもあんまりコストかからないし、それよりもクエリのパフォーマンス上げる方が優先だぜ」と判断したのなら、全然アリだと思いますよ。

    繰り返すと、
    一般的な話は
    「インデックス付Viewは更新コストが大きいので更新が少ない方が向いている」
    でしかないので、あとは個別案件毎の判断になる、ということ、
    テスとした上で貴方が貴方が判断したらそれで正しいのです。

    • 回答の候補に設定 星 睦美 2016年11月17日 4:21
    • 回答としてマーク maaaaaaaa8 2016年11月18日 1:56
    2016年11月9日 8:19
  • アドマイヤコジーンさん

    早速のお返事ありがとうございます!

    >「インデックス付Viewは更新コストが大きいので更新が少ない方が向いている」という一般的なことしか書いてないでしょ。
    >これは元テーブルとViewとで2個インデックスを作るので至極至極当たり前の話しですよね。

    分かりづらい表現ですみません。。。もちろん、インデックスを1つ追加することによる更新コストの増加に関しては把握しているつもりですが、非クラスタ化インデックスを1つ追加することによる更新コストよりもずっと大きなコストが、インデックス付Viewの作成によって生じるものなのかなという印象を抱いて心配になってしまいまして、、

    それで両者に違いがあるとしたら、インデックス付Viewは更新時に集計の演算が発生する場合があることかなと思い、それであればたしかにOLTP系には向かないという説明も納得できるなと、深読みのしすぎだったかもしれませんね。

    やはり最後は個別判断ですよね。

    しっかりテストした上で最終的な判断をしたいと思います。

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

    (他の方の回答もいただけないか待ってみて、そのあと回答としてマークさせていただきますね。)

    2016年11月9日 8:44