フィルター選択されたインデックスの作成

適用対象: SQL Server Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

この記事では、SQL Server Management Studio (SSMS) または Transact-SQL を使用してフィルター選択されたインデックスを作成する方法について説明します。 フィルター選択されたインデックスは、最適化されたディスク ベースの行ストア非クラスター化インデックスであり、適切に定義されたデータのサブセットから選択するクエリに対応する際に特に適しています。 フィルター選択されたインデックスは、フィルター述語を使用して、テーブル内の一部の行にインデックスを作成します。 適切にデザインされている、フィルター処理されたインデックスは、クエリのパフォーマンスを向上させ、テーブル全体のインデックスと比較してインデックスのメンテナンス コストおよびストレージ コストを削減できます。

フィルター選択されたインデックスは、テーブル全体のインデックスよりも次の点で優れています。

1. クエリのパフォーマンスとプランの品質の向上。

   フィルター選択されたインデックスを適切にデザインすると、クエリのパフォーマンスと実行プランの品質が向上します。これは、このインデックスが、テーブル全体の非クラスター化インデックスよりも小さく、フィルター選択された統計情報を含むためです。 フィルター選択された統計情報は、フィルター選択されたインデックスの行のみを対象としているため、テーブル全体の統計情報よりも正確です。

2. インデックスのメンテナンス コストの削減。

   インデックスのメンテナンスが行われるのは、データ操作言語 (DML) ステートメントがインデックス内のデータに影響を与える場合のみです。 フィルター選択されたインデックスにより、インデックスのメンテナンス コストは、テーブル全体の非クラスター化インデックスと比較して削減されます。これは、フィルター選択されたインデックスは小さく、インデックス内のデータが変更された場合にのみメンテナンスされるためです。 特に、含まれるデータの変更頻度が引く場合は、多数のフィルター選択されたインデックスを作成できます。 同様に、フィルター選択されたインデックスに頻繁に変更されるデータのみが含まれている場合は、インデックスのサイズを小さくすると、統計情報の更新コストが削減されます。

3. インデックスのストレージ コストの削減。

   フィルター選択されたインデックスを作成すると、テーブル全体のインデックスが必要ない場合に、非クラスター化インデックスのディスク ストレージを削減できます。 ストレージ要件をあまり増やすことなく、テーブル全体の非クラスター化インデックスを複数のフィルター選択されたインデックスに置き換えることができます。

設計上の考慮事項

列にクエリに関連する値が少数しかない場合は、値のサブセットにフィルター処理されたインデックスを作成できます。 作成したインデックスは、同じキー列に定義されているテーブル全体の非クラスター化インデックスよりも小さく、メンテナンス コストが少なくなります。

たとえば、次のデータ シナリオでフィルター選択されたインデックスについて考えてみます。 いずれの場合も、 WHERE フィルター選択されたインデックスの句は、フィルター選択されたインデックスの WHERE 利点を得るために、クエリの句のサブセットである必要があります。

• 列の値のほとんどが NULL で、クエリで NULL 以外の値からのみ選択される場合。 NULL 以外のデータ行に対してフィルター選択されたインデックスを作成できます。
• テーブルの行が、定期的なワークフローまたはキュー プロセスによって処理済みとしてマークされている場合。 時間が経つにつれて、テーブル内のほとんどの行は処理済みとしてマークされます。 まだ処理されていない行に対してフィルター処理されたインデックスは、まだ処理されていない行を検索する定期的なクエリに役立ちます。
• テーブルに異種データ行がある場合。 1 つまたは複数のカテゴリのデータに対してフィルター選択されたインデックスを作成できます。 これにより、クエリのフォーカスをテーブルの特定の領域に狭めて、これらのデータに対するクエリのパフォーマンスを向上させることができます。 繰り返しになりますが、作成したインデックスは、テーブル全体の非クラスター化インデックスよりも小さく、メンテナンス コストが少なくなります。

制限事項と制約事項

• ビューでフィルター選択されたインデックスを作成することはできません。 ただし、クエリ オプティマイザーにとって、ビューで参照されているテーブルに定義されたフィルター選択されたインデックスは役立ちます。 クエリ オプティマイザーでは、クエリ結果が正しくなる場合、ビューから選択するクエリに対してフィルター選択されたインデックスが検討されます。

• フィルター式でアクセスされる列が CLR データ型の場合、テーブルにフィルター選択されたインデックスを作成することはできません。

• フィルター選択されたインデックスは、インデックス付きビューよりも次の点で優れています。

  • インデックスのメンテナンス コストの削減。 たとえば、インデックス付きビューを更新する場合よりもフィルター選択されたインデックスを更新する場合の方が、クエリ プロセッサで使用する CPU リソースが少なくなります。

  • プランの品質の向上。 たとえば、クエリのコンパイル時、同等のインデックス付きビューよりも多くの状況でフィルター選択されたインデックスを使用することがクエリ オプティマイザーで検討されます。

  • オンラインでのインデックス再構築。 フィルター選択されたインデックスは、クエリで使用可能なときに再構築できます。 インデックス付きビューでは、オンライン インデックスの再構築はサポートされていません。 詳細については、ALTER INDEX (Transact-SQL) のオプションを参照してくださいREBUILD。

  • 一意ではないインデックス。 フィルター選択されたインデックスは一意ではないインデックスにすることができますが、インデックス付きビューは一意である必要があります。

• フィルター選択されたインデックスは 1 つのテーブルで定義され、単純な比較演算子のみをサポートします。 複数のテーブルを参照するフィルター式や複雑なロジックを含むフィルター式が必要な場合は、ビューを作成する必要があります。 フィルター選択されたインデックスでは、演算子はサポート LIKE されていません。

• フィルター選択されたインデックス式がクエリ述語と同等であり、クエリ結果を含むフィルター選択されたインデックス式の列がクエリで返されない場合、フィルター選択されたインデックス式の列は、フィルター選択されたインデックス定義のキーまたは含まれる列である必要はありません。

• フィルター選択されたインデックス式と同等ではない比較でクエリ述語で列が使用される場合、フィルター選択されたインデックス式の列は、フィルター選択されたインデックス定義のキーまたは含まれる列である必要があります。

• フィルター選択されたインデックスの式の列がクエリ結果セットに含まれる場合、その列をフィルター選択されたインデックスの定義でキー列または付加列にする必要があります。

• テーブルのクラスター化インデックス キーは、フィルター選択されたインデックス定義にキーまたは含まれる列である必要はありません。 クラスター化インデックス キーは、フィルター選択されたインデックスなど、すべての非クラスター化インデックスに自動的に含まれます。 詳細については、「インデックスのアーキテクチャとデザイン ガイド」を参照してください。

• フィルター選択されたインデックスでは、その式に指定された比較演算子によって暗黙的または明示的なデータ変換が行われる場合、変換が比較演算子の左辺で行われると、エラーが発生します。 解決策は、比較演算子の右側にデータ変換演算子 (CAST または CONVERT) を使用して、フィルター処理されたインデックス式を記述することです。

• CREATE INDEX (Transact-SQL) 構文でフィルター処理されたインデックス作成に必要なSETオプションを確認する

• フィルターは、主キー制約または一意制約には適用できませんが、 プロパティを使用 UNIQUE してインデックスに適用できます。

• 計算列にフィルター選択されたインデックスを作成することはできません。

アクセス許可

テーブルまたはビューに対する ALTER 権限が必要です。 ユーザーは、sysadmin 固定サーバー ロール、または db_ddladmin および db_owner の固定データベース ロールのメンバーである必要があります。 フィルター選択されたインデックス式を変更するには、CREATE INDEX WITH DROP_EXISTING を使用します。

SSMS を使用してフィルター選択されたインデックスを作成する

1. オブジェクト エクスプローラーで、フィルター処理されたインデックスを作成するテーブルが格納されているデータベースをプラス記号を選択して展開します。

2. プラス記号を選択して [テーブル] フォルダーを展開します。

3. プラス記号を選択して、フィルター処理されたインデックスを作成するテーブルを展開します。

4. [インデックス] フォルダーを右クリックし、 [新しいインデックス] をポイントし、 [非クラスター化インデックス] を選択します。

5. [新しいインデックス] ダイアログ ボックスの [全般] ページで、 [インデックス名] ボックスに新しいインデックスの名前を入力します。

6. [インデックス キー列] で [追加] を選択します。

7. [table_name から列を 選択] ダイアログ ボックスで、インデックスに追加するテーブルの列のチェック ボックスまたはチェック ボックスをオンにします。

8. [OK] を選択します。

9. [フィルター] ページで、 [フィルター式] に、フィルター選択されたインデックスの作成に使用する SQL 式を入力します。

10. [OK] を選択します。

Transact-SQL を使用してフィルター選択されたインデックスを作成する

この例では、AdventureWorks サンプル データベースでダウンロード可能な AdventureWorks2019 データベースを使用します。

1. オブジェクト エクスプローラーで、 データベース エンジンのインスタンスに接続します。

2. 標準バーで、 [新しいクエリ] を選択します。

3. 次の例をコピーしてクエリ ウィンドウに貼り付け、 [実行] を選択します。

USE AdventureWorks2019;
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

フィルター選択されたインデックス FIBillOfMaterialsWithEndDate は、次のクエリに対して有効です。 クエリ実行プランを表示して、クエリ オプティマイザーでフィルター選択されたインデックスが使用されたかどうかを確認できます。

USE AdventureWorks2019;
GO

SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
    AND ComponentID = 5
    AND StartDate > '01/01/2008' ;
GO

次のステップ

インデックスの作成と関連する概念の詳細については、次の記事を参照してください。

• CREATE INDEX (Transact-SQL)
• SQL Server と Azure SQL のインデックスのアーキテクチャとデザイン ガイド
• 実際の実行プランの表示
• インデックスの種類