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

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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

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

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

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

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

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

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

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

設計上の考慮事項

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

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

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

制限事項と制約事項

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

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

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

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

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

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

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

  • フィルター選択されたインデックスは 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. テーブル名から列を選択] ダイアログ ボックスで、インデックスに追加する 1 つまたは複数のテーブル列のチェック ボックスをオンにします。

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

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

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

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

この記事には AdventureWorks2022 サンプル データベースが必要です。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。

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

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

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

USE AdventureWorks2022;
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 AdventureWorks2022;
GO

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

次のステップ

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