インデックスを設計する

SQL Server には、さまざまなワークロードをサポートするために、いくつかのインデックスの種類が用意されています。 大まかに言えば、インデックスはテーブルまたはビューに関連付けられたディスク上の構造と考えることができます。これにより、SQL Server はテーブル全体をスキャンする場合と比較して、インデックス キー (テーブルまたはビュー内の 1 つ以上の列で構成される) に関連付けられている行をより簡単に見つけることができます。

クラスター化インデックス

DBA ジョブの面接に関する一般的な質問は、SQL Server の基本的なデータ ストレージ テクノロジであるインデックスであるため、クラスター化インデックスと非クラスター化インデックスの違いを求めることです。 クラスター化インデックスは基になるテーブルであり、キー値に基づいて並べ替えられた順序で格納されます。 1 つのテーブルにクラスター化インデックスは 1 つだけ存在できます。行は 1 つの順序でのみ格納できるためです。 クラスター化インデックスのないテーブルはヒープと呼ばれ、ヒープは通常、ステージング テーブルとしてのみ使用されます。 パフォーマンス設計の重要な原則は、クラスター化インデックス キーをできるだけ狭くすることです。 クラスター化インデックスの 1 つ以上のキー列を検討する場合は、一意の列または多数の異なる値を含む列を選択する必要があります。 適切なクラスター化インデックス キーのもう 1 つのプロパティは、連続してアクセスされ、テーブルから取得されたデータを並べ替えるために頻繁に使用されるレコードに対するプロパティです。 並べ替えに使用する列にクラスター化インデックスを設定すると、データが既に目的の順序で格納されるため、クエリが実行されるたびに並べ替えるコストを回避できます。

[非クラスター化インデックス]

非クラスター化インデックスは、データ行とは別の構造です。 非クラスター化インデックスには、インデックスに対して定義されたキー値と、キー値を含むデータ行へのポインターが含まれます。 SQL Server に含まれる列機能を使用して、非クラスター化インデックスのリーフ レベルに追加の非キー列を追加できます。これにより、さらに多くの列をカバーできます。 1 つのテーブルに複数の非クラスター化インデックスを作成できます。

次の例は、インデックスを追加するか、既存の非クラスター化インデックスに列を追加する必要がある場合を示しています。

キー参照演算子を使用したクエリとクエリ実行計画

クエリ プランは、インデックス シークを使用して取得された各行について、クラスター化インデックス (テーブル自体) からさらに多くのデータを取得する必要があることを示します。 非クラスター化インデックスがありますが、製品列のみが含まれます。 クエリ内の他の列を非クラスター化インデックスに追加すると、実行プランの変更を確認してキー参照を削除できます。

キー検索なしでのインデックスとクエリプランの変更

上で作成したインデックスは、カバーインデックスの例です。 キー列に加えて、クエリをカバーし、テーブル自体にアクセスする必要がない追加の列を含めます。

非クラスター化インデックスとクラスター化インデックスの両方を一意として定義できます。つまり、キー値の重複は発生しません。 テーブルに PRIMARY KEY 制約または UNIQUE 制約を作成すると、一意のインデックスが自動的に作成されます。

このセクションでは、SQL Server の b ツリー インデックス (行ストア インデックスとも呼ばれます) について説明します。 次の図は、b ツリーの一般的な構造を表しています。

SQL Server と Azure SQL のインデックスのBツリーアーキテクチャ

インデックス b ツリー内の各ページはインデックス ノードと呼ばれ、b ツリーの最上位ノードはルート ノードと呼ばれます。 インデックス内の下位ノードはリーフ ノードと呼ばれ、リーフ ノードのコレクションはリーフ レベルです。

インデックスデザインは、芸術と科学の融合です。 キーに列が少ない狭いインデックスでは、更新に要する時間が短縮され、メンテナンスのオーバーヘッドが少なくなります。ただし、より多くの列を含むより広いインデックスほど多くのクエリでは役に立たない場合があります。 アプリケーションのクエリによって選択された列に基づいて、いくつかのインデックス作成方法を試す必要がある場合があります。 通常、クエリ オプティマイザーは、クエリに最適な既存のインデックスであると見なされるものを選択します。しかし、それは構築できるより良いインデックスがないことを意味するものではありません。

データベースの適切なインデックス作成は、複雑なタスクになる可能性があります。 テーブルのインデックスを計画するときは、いくつかの基本的な原則に留意する必要があります。

• システムのワークロードを理解します。 主に挿入操作に使用されるテーブルは、読み取りアクティビティが多いデータ ウェアハウス操作に使用されるテーブルに比べて、追加のインデックスのメリットが少なくなります。
• 最も頻繁に実行されるクエリを中心にインデックスを最適化します。
• クエリ内の列に適したデータ型を選択します。 インデックスは、整数のデータ型、一意の列、または null 以外の列で最適に機能します。
• 述語と結合句で頻繁に使用される列に非クラスター化インデックスを作成し、オーバーヘッドを最小限に抑えるためにできるだけ狭くします。
• データ サイズ/ボリュームを検討してください。 小さいテーブルのスキャンは比較的安価ですが、大きなテーブルのスキャンはコストがかかります。

SQL Server によって提供されるもう 1 つのオプションは、フィルター選択されたインデックスの作成です。 フィルター選択されたインデックスは、多数の行がその列で同じ値を共有する大きなテーブルの列に最適です。 次の例は、退職または退職した従業員を含むすべての従業員のレコードを格納する従業員テーブルです。

CREATE TABLE [HumanResources].[Employee](
     [BusinessEntityID] [int] NOT NULL,
     [NationalIDNumber] [nvarchar](15) NOT NULL,
     [LoginID] [nvarchar](256) NOT NULL,
     [OrganizationNode] [hierarchyid] NULL,
     [OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),
     [JobTitle] [nvarchar](50) NOT NULL,
     [BirthDate] [date] NOT NULL,
     [MaritalStatus] [nchar](1) NOT NULL,
     [Gender] [nchar](1) NOT NULL,
     [HireDate] [date] NOT NULL,
     [SalariedFlag] [bit] NOT NULL,
     [VacationHours] [smallint] NOT NULL,
     [SickLeaveHours] [smallint] NOT NULL,
     [CurrentFlag] [bit] NOT NULL,
     [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
     [ModifiedDate] [datetime] NOT NULL)

この表には、従業員が現在雇用されているかどうかを示す CurrentFlag という列があります。 この例では、 bit データ型を使用し、2 つの値を表します。1 つは現在使用されており、0 は現在使用されていません。 WHERE CurrentFlag = 1列にCurrentFlagを使用してフィルター選択されたインデックスを作成すると、現在の従業員の効率的なクエリが可能になります。

また、ビューにインデックスを作成することもできます。ビューに集計やテーブル結合などのクエリ要素が含まれている場合、パフォーマンスが大幅に向上します。

列ストア インデックス

列ストア インデックスでは、大規模な集計ワークロードを含むクエリのパフォーマンスが向上します。 当初はデータ ウェアハウスを対象としていた列ストア インデックスが、大きなテーブルのクエリ パフォーマンスの問題に対処するために、他のさまざまなワークロードに採用されています。 b ツリー インデックスと同様に、クラスター化列ストア インデックスは特別な方法で格納されたテーブル自体を表し、非クラスター化列ストア インデックスはテーブルとは別に格納されます。 クラスター化列ストア インデックスには、本質的にテーブル内のすべての列が含まれますが、並べ替えられません。

非クラスター化列ストア インデックスは、通常、2 つのシナリオで使用されます。 1 つ目は、列ストア インデックス (XML、CLR、sql_variant、ntext、text、image など) で列のデータ型がサポートされていない場合です。 クラスター化列ストア インデックスには常にテーブルのすべての列が含まれるため、非クラスター化インデックスが唯一のオプションです。 2 つ目のシナリオには、ハイブリッド トランザクション分析処理 (HTAP) アーキテクチャで使用されるフィルター選択されたインデックスが含まれます。このアーキテクチャでは、レポートが同時に実行されている間にデータがテーブルに読み込まれます。 インデックス (通常は日付フィールド) をフィルター処理すると、効率的な挿入とレポートのパフォーマンスが実現します。

列ストア インデックスでは、各列が個別に格納されるため、必要な列のみをスキャンして IO を削減し、列内の同様のデータによる圧縮を増やすという 2 つの利点があります。 データ ウェアハウスのファクト テーブルなど、大規模なデータ セットをスキャンする分析クエリに最適です。 列ストア インデックスは、シングルトン値参照用の b ツリー非クラスター化インデックスを使用して拡張できます。

これらのインデックスは、バッチ実行モード、一度に 1 つずつではなく一度に行のセット (通常は約 900) を処理する利点もあります。 この方法により、CPU 命令が大幅に削減されます。

SELECT SUM(Sales) FROM SalesAmount;

バッチ モードでは、従来の行処理よりもパフォーマンスを向上させることができます。 行ストアのバッチ モードでは、列ストア インデックスと同じレベルの読み取りパフォーマンスはありませんが、分析クエリでは最大 5 倍のパフォーマンス向上が見られます。

データ ウェアハウス ワークロードの列ストア インデックスのもう 1 つの利点は、102,400 行以上の一括挿入操作用に最適化された読み込みパスです。 102,400 は列ストアに直接読み込む最小値ですが、行グループと呼ばれる行の各コレクションは、最大で約 1,024,000 行にすることができます。 行グループの数は少なくなりますが、より多くの行グループを使用すると、要求されたレコードを取得するためにスキャンする必要がある行グループが少なくなるため、 SELECT クエリの効率が向上します。 これらの読み込みはメモリ内で発生し、インデックスに直接読み込まれます。 ボリュームが小さい場合、データはデルタ ストアと呼ばれる b ツリー構造に書き込まれ、インデックスに非同期的に読み込まれます。

列ストア インデックス読み込み例

この例では、同じデータが FactResellerSales_CCI_Demo と FactResellerSales_Page_Demo の2つのテーブルに読み込まれています。 FactResellerSales_CCI_Demoにはクラスター化された列ストア インデックスがあり、FactResellerSales_Page_Demoには2つの列を持つクラスター化されたbツリーインデックスがあり、ページ圧縮されています。 ご覧のとおり、各テーブルは FactResellerSalesXL_CCI テーブルから 1,024,000 行を読み込んでいます。 SET STATISTICS TIME が ON の場合、SQL Server はクエリの実行時間の経過を追跡します。 列ストア テーブルへのデータの読み込みには約 8 秒かかり、ページ圧縮テーブルへの読み込みには 20 秒近くかかりました。 この例では、列ストア インデックスに入るすべての行が 1 つの行グループに読み込まれます。

1 回の操作で 102,400 行未満のデータを列ストア インデックスに読み込む場合、デルタ ストアと呼ばれる b ツリー構造に読み込まれます。 データベース エンジンは、タプル ムーバーと呼ばれる非同期プロセスを使用して、このデータを列ストア インデックスに移動します。 デルタ ストアを開いていると、クエリのパフォーマンスに影響する可能性があります。これらのレコードの読み取りは列ストアからの読み取りよりも効率が低いためです。 また、#D0 オプションを使用してインデックスを再構成して、デルタ ストアを列ストア インデックスに強制的に追加および圧縮することもできます。