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

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

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

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

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

• 多数のクエリで頻繁に実行される結合操作と集計操作。

  たとえば、商品の在庫を記録する 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 日を超えるいくつかの異なる範囲を集計するクエリに使用できます。

関連項目

概念