インデックス付きビューのデザイン

  • [アーティクル]

ビューは仮想テーブルとも呼ばれます。これは、ビューによって返される結果セットの一般的な形式が列と行を備えたテーブルと同じであり、またビューはテーブルと同じように SQL ステートメントで参照できるためです。標準的なビューの結果セットは、データベースに永続的に格納されません。クエリで標準的なビューが参照されるたびに、SQL Server 2005 ではビューの定義が内部的にクエリに置き換えられ、クエリがベース テーブルのみを参照するように形成されるまで変更されます。その後、結果として作成されたクエリが通常どおり実行されます。詳細については、「ビューの解決」を参照してください。

標準的なビューでは、多量のデータの集計、多数の行の結合など大量の行を複雑に処理するビューの場合、ビューを参照する各クエリの結果セットを動的に構築するオーバーヘッドが非常に大きくなることがあります。このようなビューをクエリで頻繁に参照する場合は、ビューに一意クラスタ化インデックスを作成するとパフォーマンスが向上します。一意クラスタ化インデックスをビューに作成すると、クラスタ化インデックスを含むテーブルをデータベースに格納する場合のように、結果セットがデータベースに格納されます。

ビューにインデックスを作成するもう 1 つの利点は、FROM 句でビューを直接指定していないクエリで、オプティマイザがビュー インデックスの使用を開始することです。既存のクエリは、コードを書き直さなくても、インデックス付きビューからデータを効率良く取得できるようになります。詳細については、「ビューのインデックスの解決」を参照してください。

ベース テーブル内のデータに対して変更が行われると、そのデータ変更は、インデックス付きビューに格納されたデータに反映されます。ビューのクラスタ化インデックスは一意であるという要件があることで、データ変更の影響を受けたインデックス内の行を SQL Server が検索する効率が向上します。

クエリとビュー定義の両方に次の照合要素が含まれているときに、SQL Server 2005 クエリ オプティマイザがクエリの処理時にインデックス付きビューを利用する能力は、従来のバージョンより向上しています。

  • スカラ式。たとえば、クエリ オプティマイザは、次のクエリをその述語のスカラ式と照合できます。

    SELECT ColA, ColB FROM TableT WHERE ColC * (ColD + 10) > 50

    このビューに作成されたインデックスに対しては、次のようになります。

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC * (ColD + 10) AS ExpCol
FROM dbo.TableT

    ユーザー定義関数を含むスカラ式でも、同様の方法で照合できます。

  • スカラ集計関数。たとえば、選択リストにスカラ集計関数が含まれている次のようなクエリです。

    SELECT COUNT_BIG (*) FROM dbo.TableT

    次のビューに作成されたインデックスと照合することができます。

    CREATE VIEW V2 WITH SCHEMABINDING AS
SELECT COUNT_BIG (*) AS Cnt 
FROM dbo.TableT

クエリ オプティマイザでは、クエリ プランを選択するときに、次の点についても考慮されます。

  • クエリ述語で定義された値の間隔が、インデックス付きビューで定義された間隔の範囲内に収まるかどうか。たとえば、次のビューに作成されたインデックスについて考えてみます。

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC FROM dbo.TableT
WHERE ColA > 1 and ColA < 10

    ここで、次のクエリについて考えてみましょう。

    SELECT ColB, ColC FROM dbo.TableT
WHERE ColA > 3 and ColA < 7

    クエリで定義された 3 と 7 との間隔が、インデックス付きビューで定義された 1 と 10 との間隔の範囲内に収まるので、クエリ オプティマイザではビュー V1 とこのクエリが照合されます。

  • クエリで定義される式の数は、インデックス付きビューで定義された式に等しくなります。SQL Server では、列参照、リテラル、論理演算子 AND、OR、NOT、BETWEEN、IN、および比較演算子 =、<>、>、<、>=、<= を考慮して式の照合を試みます。+ や % などの算術演算子、およびパラメータは考慮されません。
    たとえば、クエリ オプティマイザでは次のクエリと

    SELECT ColA, ColB from dbo.TableT
WHERE ColA < ColB

    次のビューに作成されたインデックスとが照合されます。

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB FROM dbo.TableT
WHERE ColB > ColA

SQL Server では、すべてのインデックスと同様に、クエリ オプティマイザで役立つと判断された場合のみ、クエリ プランにインデックス付きビューの使用が選択されます。

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

インデックス付きビューをデザインする際のガイドライン

インデックス付きビューは、基になるデータを更新する頻度が低い場合に最も効果的です。インデックス付きビューをメンテナンスする方が、テーブル インデックスのメンテナンスよりもコストがかかる場合があります。基になるデータを頻繁に更新する場合、インデックス付きビューの使用によるパフォーマンス向上よりも、インデックス付きビューのデータ メンテナンスにかかるコスト増加の方が大きい場合があります。基になるデータがバッチで定期的に更新され、更新と更新の間は主に読み取り専用として扱われる場合、更新前にインデックス付きビューを削除し、後で再構築することを検討してください。これを行うと、更新のパフォーマンスが向上する場合があります。

次の種類のクエリでは、インデックス付きビューによってパフォーマンスが向上します。

  • 多数の行を処理する結合と集計。

  • 多数のクエリで頻繁に実行される結合操作と集計操作。
    たとえば、商品の在庫を記録する OLTP (オンライン トランザクション処理) データベースでは、多数のクエリで ProductMaster、ProductVendor、および VendorMaster の各テーブルが結合されることが予想されます。このような結合を実行する各クエリではそれほど多くの行を処理しなくても、多数のクエリの結合処理全体では処理する行数が大量になる可能性があります。このような関係が変更されることは少ないので、結合の結果を格納するインデックス付きビューを定義すると、システム全体のパフォーマンスが向上する場合があります。

  • 意思決定支援ワークロード。
    分析システムには、頻繁に更新しない集約データや集計データを格納するという特徴があります。さらにデータを集計したり多数の行を結合することが、多くの意思決定支援クエリの特徴です。また、意思決定支援システムには、多くの列を持つ幅の広いテーブルや大きい列、またはその両方が含まれている場合があります。このような列の限定されたサブセットを参照するクエリでは、クエリ内の列のみを格納したインデックス付きビューや、これらの列の限定されたスーパーセットを格納したインデックス付きビューを使用することで、利点が得られる場合があります。1 つのテーブルの列のサブセットを格納した、限定されたインデックス付きビューを作成することを、テーブルが列方向に分割されることから、列方向のパーティション分割ストラテジと呼びます。たとえば、次のテーブルとインデックス付きビューについて考えてみます。

    CREATE TABLE wide_tbl(a int PRIMARY KEY, b int, ..., z int)
CREATE VIEW v_abc WITH SCHEMABINDING AS
SELECT a, b, c
FROM dbo.wide_tbl
WHERE a BETWEEN 0 AND 1000
CREATE UNIQUE CLUSTERED INDEX i_abc ON v_abc(a)

    次のクエリでは、v_abc だけを使用して、答を得ることができます。

    SELECT b, count_big(*), SUM(c)
FROM wide_tbl 
WHERE a BETWEEN 0 AND 1000
GROUP BY b

    ビュー v_abc では、テーブル wide_tbl よりも占有するページ数が少なくなります。したがって、オプティマイザにとっては、前述のクエリの回答を得るには、このビューをアクセス パスとして選択することが適しています。
    テーブルのサブセットを使用するのではなく、テーブルを列方向に完全に分割するには、インデックス付きビューではなく、必要な列だけを含めるために INCLUDE 句を使用するテーブルで非クラスタ化インデックスを使用することをお勧めします。詳細については、「CREATE INDEX (Transact-SQL)」を参照してください。

次の種類のクエリでは、通常、インデックス付きビューを使用してもパフォーマンスは向上しません。

  • 多くの書き込みを行う OLTP システム。

  • 多くの更新を行うデータベース。

  • 集計または結合を含まないクエリ。

  • GROUP BY キーに高い次数の基数を持つデータの集計。高い次数の基数とは、キーに多数の異なる値が格納されることを意味します。一意キーではすべてのキーに異なる値が格納されるので、基数の次数が最も高くなります。インデックス付きビューによってクエリがアクセスする必要のある行数が減り、パフォーマンスが向上します。ビューの結果セットの行数がベース テーブルの行数とほとんど変わらない場合は、ビューを使用してもパフォーマンスはほとんど向上しません。たとえば、行数が 1,000 行のテーブルでの次のクエリを考えてみましょう。

    SELECT PriKey, SUM(SalesCol)
FROM ExampleTable
GROUP BY PriKey

    テーブル キーの基数が 100 の場合、このクエリの結果を使用して構築されるインデックス付きビューには 100 行しか格納されません。ビューを使用するクエリで必要な読み取り回数は、平均するとベース テーブルに対して必要な読み取り回数の 10 分の 1 になります。キーが一意キーの場合、キーの基数は 1000 となり、ビューの結果セットは 1000 行を返します。ビューと ExampleTable ベース テーブルの行数が等しい場合、クエリでベース テーブルを直接読み取る代わりに、インデックス付きビューを使用しても、パフォーマンスは向上しません。

  • 拡大型結合。拡大型結合とは、ベース テーブル内の元のデータよりもビューの結果セットの方が大きくなる場合です。

インデックス付きビューとクエリの組み合わせ

インデックスを作成できるビューの種類には制約があるので、問題を完全に解決するビューをデザインできない場合がありますが、小規模なインデックス付きビューを複数デザインし、プロセスの一部の処理速度を速めることができます。

次の例を考えてみます。

  • 頻繁に実行するクエリで、あるデータベースのデータを集計し、次に別のデータベースのデータを集計して、その結果を結合するとします。インデックス付きビューでは、複数のデータベースからのテーブルを参照できないので、プロセス全体を実行する 1 つのビューをデザインすることはできません。ただし、各データベースの集計を行うインデックス付きビューを、それぞれのデータベースに作成することはできます。オプティマイザでインデックス付きビューと既存のクエリを照合できると、少なくとも集計処理の速度は速くなります。処理速度を上げるために既存のクエリを書き直す必要はありません。結合処理の速度は速くなりませんが、クエリではインデックス付きビューに格納された集計を使用するので、クエリ全体の速度は向上します。
  • 頻繁に実行するクエリで、複数のテーブルのデータを集計し、次に UNION を使用してその結果を結合するとします。インデックス付きビューでは UNION を使用できません。そこで、個別の集計操作を行うビューを再度デザインします。オプティマイザはインデックス付きビューを選択して、クエリの速度を上げます。クエリを書き直す必要はありません。UNION 処理は速くなりませんが、個別の集計処理は速くなります。

インデックス付きビューは、複数の操作を実行できるようにデザインします。オプティマイザでは、FROM 句で指定されていない場合でもインデックス付きビューを使用できるので、インデックス付きビューを適切にデザインすると、多数のクエリの処理速度を向上できます。

たとえば、次のようになビューのインデックスを作成する場合を考えてみます。

CREATE VIEW ExampleView WITH SCHEMABINDING
AS
SELECT GroupKey, SUM(Colx) AS SumColx, COUNT_BIG(Colx) AS CountColx
FROM MyTable
GROUP BY GroupKey

このビューは、ビューの列を直接参照するクエリに使えるだけでなく、ベース テーブルに照会し、SUM(Colx)、COUNT_BIG(Colx)、COUNT(Colx)、および AVG(Colx) などの式を含むクエリにも使用できます。このようなクエリはビュー内の少数の行を取得するだけなので、ベース テーブルから全行を読み取るよりも処理速度は常に速くなります。

同様に、データを集計し、日単位にグループ化するインデックス付きビューは、7 日、30 日、または 90 日など、1 日を超えるいくつかの異なる範囲を集計するクエリに使用できます。

参照

概念

インデックス付きビューの作成
計算列に対するインデックスの作成
インデックス付きビューの DBCC エラーに関するトラブルシューティング

ヘルプおよび情報

SQL Server 2005 の参考資料の入手