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

[アーティクル]
05/10/2024

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

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

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

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

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

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

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

設計上の考慮事項

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

たとえば、次のデータシナリオでフィルター選択されたインデックスについて考えてみます。いずれの場合も、フィルター選択されたインデックスの 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 を使用してフィルター選択されたインデックスを作成する

オブジェクトエクスプローラーで、フィルター処理されたインデックスを作成するテーブルが格納されているデータベースをプラス記号を選択して展開します。
プラス記号を選択して [テーブル] フォルダーを展開します。
プラス記号を選択して、フィルター処理されたインデックスを作成するテーブルを展開します。
[インデックス] フォルダーを右クリックし、[新しいインデックス] をポイントし、[非クラスター化インデックス] を選択します。
[新しいインデックス] ダイアログボックスの [全般] ページで、 [インデックス名] ボックスに新しいインデックスの名前を入力します。
[インデックスキー列] で [追加] を選択します。
[table_name から列を選択] ダイアログボックスで、インデックスに追加する 1 つまたは複数のテーブル列のチェックボックスをオンにします。
[OK] を選択します。
[フィルター] ページで、[フィルター式] に、フィルター選択されたインデックスの作成に使用する SQL 式を入力します。
[OK] を選択します。

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

この記事の Transact-SQL コードサンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプルデータベースを使用します。このサンプルデータベースは、Microsoft SQL Server サンプルとコミュニティプロジェクトのホームページからダウンロードできます。

オブジェクトエクスプローラーで、データベースエンジンのインスタンスに接続します。
標準バーで、 [新しいクエリ] を選択します。
次の例をコピーしてクエリウィンドウに貼り付け、 [実行] を選択します。

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

次のステップ

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

次の方法で共有