列ストア インデックス - 設計ガイダンス

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric プレビューの SQL データベース

列ストア インデックスの設計に関する高度な推奨事項です。 設計に関していくつかの適切な意思決定を行うと、列ストア インデックスの設計が意図する、高いデータ圧縮率とクエリ パフォーマンスを実現できます。

Prerequisites

この記事では、列ストアのアーキテクチャと用語に精通していることを想定しています。 詳細については、「 Columnstore indexes: Overview and Columnstore Index Architectureを参照してください。

データ要件を認識する

列ストア インデックスを設計する前に、データ要件について可能な限り理解してください。 たとえば、次の質問に対する答えを考えてみてください。

• テーブルの大きさはどれくらいか。
• 自分のクエリは主に広範囲の値をスキャンする分析を実行するか。 列ストア インデックスは、特定の値の検索ではなく、広範囲のスキャンに対してうまく機能するように設計されています。
• 自分のワークロードは多数の更新と削除を実行するか。 列ストア インデックスは、データが安定しているときにうまく機能します。 クエリが更新および削除するのは行の 10% 未満である必要があります。
• 私にはデータウェアハウスのファクトテーブルとディメンションテーブルがありますか？
• トランザクション ワークロードの分析を実行する必要があるか。 そうである場合は、リアルタイム運用分析について列ストアの設計ガイダンスをご覧ください。

列ストア インデックスは必要でない場合があります。 ヒープまたはクラスター化インデックスを持つ行ストア (または B ツリー) テーブルは、データをシークするクエリ、特定の値の検索、狭い範囲の値でのクエリを実行するときに最適なパフォーマンスを発揮します。 トランザクション ワークロードでは、広範囲のテーブル スキャンではなく主にテーブル シークを必要とする傾向があるため、行ストア インデックスを使用してください。

ニーズに最適な列ストア インデックスを選ぶ

列ストア インデックスには、クラスタ化されたものと非クラスタ化されたものがあります。 クラスター化列ストア インデックスでは、1 つ以上の非クラスター化 B ツリー インデックスを使用できます。 列ストア インデックスは、簡単に試すことができます。 テーブルを列ストア インデックスとして作成する場合は、列ストア インデックスを削除して、テーブルを行ストア テーブルに簡単に変換できます。

オプションと推奨事項の概要を次に示します。

データウェアハウスの大規模テーブルにはクラスター化列ストアインデックスを使用する。

クラスター化列ストア インデックスは複数のインデックスであり、主テーブル ストレージです。 大規模なデータ ウェアハウスのファクト テーブルとディメンション テーブルに対する高いデータ圧縮率とクエリ パフォーマンスの大幅な向上を実現します。 クラスター化列ストア インデックスは、トランザクション クエリよりも分析クエリに最適です。分析クエリは、特定の値の検索よりも、広範囲の値に対して操作を実行する傾向があるためです。

次の場合は、クラスター化列ストア インデックスの使用を検討してください。

• 各パーティションには少なくとも100万行があります。 列ストア インデックスでは、各パーティション内に行グループがあります。 テーブルが小さすぎて各パーティション内の行グループを満たさない場合は、列ストアの圧縮とクエリのパフォーマンスの利点が得られない可能性があります。
• クエリが主に値の範囲に対する分析を実行する場合。 たとえば、列の平均値を検索するには、クエリですべての列値をスキャンする必要があります。 次に、値を加算して集計し、平均値を判断します。
• 挿入のほとんどは、大量のデータに対して行われ、更新と削除は最小限です。 モノのインターネット (IoT) などの多くのワークロードは大量のデータを挿入し、更新と削除は最小限です。 これらのワークロードは、クラスター化列ストア インデックスを使用する場合の圧縮とクエリ パフォーマンスのメリットを利用できます。

次の場合はクラスター化列ストア インデックスを使用しないでください。

• テーブルに、varchar(max)、nvarchar(max)、varbinary(max) データ型が必要な場合。 または、これらの列を含まないように列ストア インデックスを設計します (適用対象: SQL Server 2016 (13.x) 以前のバージョン)。
• テーブルデータは永続的ではありません。 データをすばやく格納および削除する必要がある場合は、ヒープまたは一時テーブルの使用を検討してください。
• テーブルの行数がパーティションあたり 100 万未満の場合。
• 更新と削除がテーブルに対する操作の 10% を超える場合。 大量の更新と削除は断片化の原因となります。 断片化は、再構成と呼ばれる操作を実行するまでの圧縮率とクエリのパフォーマンスに影響します。これにより、すべてのデータが列ストアに強制的に挿入され、断片化が解消されます。 詳しくは、列ストア インデックスでインデックスの断片化を最小限に抑える方法に関する記事をご覧ください。

詳細については、データ ウェアハウスの Columnstore インデックスを参照してください。

大規模なデータ ウェアハウス テーブルに対して順序付き列ストア インデックスを使用する

順序付けされた列ストア インデックスの可用性については、「列ストア インデックスの : 概要」を参照してください。

次のシナリオでは、順序付き列ストア インデックスの使用を検討してください。

• データが比較的静的で (頻繁に書き込みと削除が行われず)、順序付き列ストア インデックス キーが静的な場合、順序付き列ストア インデックスは、非順序列ストア インデックスまたは分析ワークロードの行ストア インデックスよりもパフォーマンス上の大きな利点を提供できます。
• 順序付けられた列ストア インデックス キーの最初の列の値が異なるほど、パフォーマンスが向上する可能性があります。 これは、文字列データのセグメントの削除が改善されたためです。 詳細については、「セグメントの削除」を参照してください。
• 頻繁にクエリを実行し、セグメントの削除 (特にキーの最初の列) の利点を得ることができる順序付き列ストア インデックス キーを選択します。 テーブル内の他の列でのセグメントの削除によるパフォーマンスの向上は、予測不可能です。
• 最新の分析データのみを照会する必要があるユース ケース (たとえば、過去 15 秒の順序付き列ストア インデックスなど) は、古いデータのセグメント除去を提供できます。 順序付けられた列ストア データのキーの最初の列は、挿入日や作成日などの日付/時刻データである必要があります。 セグメントの削除は、順序付けされていない列ストア インデックスよりも順序付け列ストア インデックスの方が効果的です。
• GUID データと一緒にキーを含むテーブルの順序付き列ストア インデックスを検討します。ここで、uniqueidentifier データ型をセグメントの削除に使用できるようになりました。

順序付き列ストア インデックスは、次のシナリオでは効果的ではない可能性があります。

• 他の列ストア インデックスと同様に、挿入アクティビティのレートが高いと、過剰なストレージ I/O が発生する可能性があります。
• 書き込み操作が多いワークロードでは、タプル ムーバーによる行グループのメンテナンスにより、セグメント削除の品質が時間の経過につれて低下します。 これは、ALTER INDEX REORGANIZEを使用した列ストア インデックスの定期的なメンテナンスによって軽減できます。

Bツリー非クラスター化インデックスを追加して、テーブル検索の効率を向上させる

SQL Server 2016 (13.x) 以降では、クラスター化列ストア インデックスのセカンダリ インデックスとして非クラスター化 B ツリー インデックスまたは行ストア インデックスを作成できます。 列ストア インデックスが変更されると、非クラスター化 B ツリー インデックスが更新されます。 これは、うまく利用するとメリットのある強力な機能です。

セカンダリ B ツリー インデックスを使用すると、すべての行をスキャンせずに特定の行を効率的に検索できます。 他のオプションも使用できます。 たとえば、B ツリー インデックスで UNIQUE 制約を使用して、主キーまたは外部キー制約を適用できます。 一意でない値は B ツリー インデックスへの挿入に失敗するため、SQL Server では列ストアに値を挿入できません。

列ストア インデックスの B ツリー インデックスは次の目的で使用することを検討してください。

• 特定の値または小さい範囲の値を検索するクエリを実行する。
• 主キー制約や外部キー制約などの制約を適用する。
• 更新や削除の操作を効率的に実行する。 B ツリー インデックスは、テーブルまたはテーブルのパーティション全体をスキャンせずに、更新や削除のために特定の行をすばやく検索できます。
• B ツリー インデックスの格納に使用可能な追加の記憶域がある。

非クラスター化カラムストア インデックスを使用してリアルタイムで分析を行う

SQL Server 2016 (13.x) 以降では、行ストア ディスク ベース テーブルまたはインメモリ OLTP テーブルで非クラスター化列ストア インデックスを使用できます。 これにより、トランザクション テーブルに対して分析をリアルタイムで実行できるようになります。 トランザクションは基になるテーブルで発生しますが、列ストア インデックスに対して分析を実行できます。 1 つのテーブルで両方のインデックスを管理するため、行ストアと列ストア両方のインデックスに対して変更をリアルタイムで使用できます。

列ストア インデックスは行ストア インデックスよりもデータ圧縮率が 10 倍高いため、必要な追加ストレージがわずかで済みます。 たとえば、圧縮された行ストア テーブルが 20 GB を必要とする場合、列ストア インデックスではさらに 2 GB 必要な場合があります。 必要な追加領域は、非クラスター化列ストア インデックス内の列数によっても異なります。

次の場合は、非クラスター化列ストア インデックスの使用を検討してください。

• トランザクション行ストアテーブルでリアルタイムに分析を実行する。 分析用に設計された既存の B ツリー インデックスを非クラスター化列ストア インデックスで置き換えることができます。

• 別個のデータウェアハウスの必要性を排除する。 従来、企業では行ストア テーブルでトランザクションを実行し、データを別のデータ ウェアハウスに読み込んで分析を実行しています。 多くのワークロードでは、トランザクション テーブルに非クラスター化列ストア インデックスを作成して、読み込みプロセスと別のデータ ウェアハウスを排除できます。

SQL Server 2016 (13.x) には、このシナリオのパフォーマンスを高めるいくつかの方法が用意されています。 OLTP アプリケーションに変更を加えないで非クラスター化列ストア インデックスを有効にできるため、簡単に試すことができます。

処理リソースを追加するには、読み取り可能なセカンダリに対して分析を実行できます。 読み取り可能なセカンダリを使用すると、トランザクションのワークロードと分析ワークロードの処理が分離されます。

詳細については、「リアルタイム運用分析のための列ストアの使用を開始する」を参照してください。

最適な列ストア インデックスの選択について詳しくは、Sunil Agarwal のブログで「Which columnstore index is right for my workload?」(自分のワークロードに適した列ストア インデックス) をご覧ください。

テーブルのパーティションを使用してデータ管理とクエリのパフォーマンスを高める

列ストア インデックスでは、データの管理とアーカイブの優れた方法であるパーティション分割がサポートされます。 パーティション分割では、操作を 1 つ以上のパーティションに制限することでクエリのパフォーマンスも向上します。

パーティションを使用してデータを管理しやすくする

大規模なテーブルの場合、データの範囲を管理する唯一の現実的な方法は、パーティションの使用です。 行ストア テーブルのパーティションの利点は、列ストア インデックスにも適用されます。

たとえば、行ストアと列ストアの両方のテーブルは、次の目的でパーティションを使用します。

• 増分バックアップのサイズを制御する。 パーティションをバックアップしてファイル グループを分離し、読み取り専用としてマークできます。 これにより、将来のバックアップでは読み取り専用のファイル グループがスキップされます。
• 古いパーティションを低コストのストレージに移動してストレージ コストを節約する。 たとえば、パーティションの切り替えを使用して、パーティションを低コストの記憶域の場所に移動できます。
• パーティションに対する操作を制限して操作を効率的に実行する。 たとえば、インデックスのメンテナンスのために、断片化されたパーティションのみを対象とすることができます。

さらに、列ストア インデックスでは、パーティション分割を次の目的で使用します。

• ストレージ コストをさらに 30% を節約する。 COLUMNSTORE_ARCHIVE圧縮オプションを使用して、古いパーティションを圧縮できます。 クエリのパフォーマンスが低下する可能性があり、パーティションのクエリが頻繁でない場合は許容される可能性があります。

パーティションを使用してクエリのパフォーマンスを向上させる

パーティションを使用して、スキャンするクエリを特定のパーティションのみに制限して、スキャンする行数を制限できます。 たとえば、インデックスが年でパーティション分割され、クエリが昨年のデータを分析する場合、スキャンする必要があるのは 1 つのパーティション内のデータだけです。

列ストア インデックスに使用するパーティションを少なくする

データ サイズが十分に大きくない限り、列ストア インデックスは、行ストア インデックスに使用するよりも少ないパーティションで最高のパフォーマンスを発揮します。 パーティションごとに少なくとも 100 万行がない場合、ほとんどの行がデルタストアに入る可能性があり、その場合、列ストア圧縮の性能向上の恩恵を受けることができません。 たとえば、10 個のパーティションがあるテーブルに 100 万行を読み込み、各パーティションが 100,000 行を受け取ると、すべての行がデルタ行グループに移動します。

Example:

• 1,000,000 行を 1 つのパーティションまたは非パーティション テーブルに読み込みます。 1,000,000 行を含む 1 つの圧縮行グループを取得します。 これは、高いデータ圧縮と高速なクエリ パフォーマンスに優れています。
• 1,000,000 行を 10 個のパーティションに均等に読み込みます。 各パーティションは、列ストア圧縮の最小しきい値未満である 100,000 行を受け取ります。 その結果、列ストア インデックスには、それぞれ 100,000 行を含む 10 個のデルタ行グループがある可能性があります。 デルタ行グループを列ストアに強制的に移動する方法があります。 ただし、これらが列ストア インデックス内の唯一の行である場合、圧縮された行グループは小さすぎて、最適な圧縮とクエリパフォーマンスを実現できません。

パーティション分割について詳しくは、Sunil Agarwal のブログ記事「Should I partition my columnstore index?」(自分の列ストア インデックスは分割する必要があるか) をご覧ください。

適切なデータの圧縮方法を選ぶ

列ストア インデックスは、データ圧縮に関する 2 つの選択肢 (列ストア圧縮とアーカイブ圧縮) を提供しています。 圧縮オプションは、インデックスの作成時、または後で ALTER INDEX ... REBUILD を使用して変更するときに選択できます。

列ストア圧縮を使用して最適なクエリ パフォーマンスを実現する

列ストア圧縮は通常、行ストア インデックスよりも 10 倍高い圧縮率を実現します。 これは列ストア インデックスの標準的な圧縮方法で、高速なクエリ パフォーマンスを実現します。

アーカイブ圧縮を使用して最適なデータ圧縮を実現する

アーカイブ圧縮は、クエリのパフォーマンスが重要ではないときに、圧縮率を最大化するように設計されています。 列ストア圧縮よりも高いデータ圧縮率を実現しますが、価格は高くなります。 データの圧縮と圧縮解除に時間がかかるため、高速なクエリ パフォーマンスには適していません。

行ストア テーブルを列ストア インデックスに変換するときに最適化を使用する

データが行ストア テーブルに既に存在する場合は、CREATE COLUMNSTORE INDEX を使用して、テーブルをクラスター化列ストア インデックスに変換できます。 次のように、テーブルが変換された後にクエリのパフォーマンスを改善するいくつかの最適化があります。

MAXDOP を使用して行グループの品質を向上させる

ヒープまたはクラスター化 B ツリー インデックスを列ストア インデックスに変換するプロセッサの最大数を構成できます。 プロセッサを構成するには、最大並列度オプション (MAXDOP) を使用します。

大量のデータがある場合、MAXDOP 1 が遅すぎる可能性があります。 MAXDOP を 4 に増やすとうまく動作します。 この結果、最適な行数を持たない、いくつかの行グループが生成される場合は、ALTER INDEX REORGANIZE を実行してそれらをバックグラウンドでマージできます。

B ツリー インデックスの並べ替え順序を保持する

B ツリー インデックスは並べ替えられた順序で行を既に格納しているため、行を列ストア インデックスに圧縮するときにその順序を維持すると、クエリのパフォーマンスが向上することがあります。

列ストア インデックスは、データを並べ替えませんが、メタデータを使用して、各行グループ内の各列セグメントの最小値と最大値を追跡します。 値の範囲をスキャンする場合、行グループをスキップするときに簡単に計算できます。 データが並べ替えられていると、より多くの行グループをスキップできます。

変換中に並べ替え順序を維持するには:

• DROP_EXISTING 句を指定した CREATE COLUMNSTORE INDEX を使用します。 これにより、インデックスの名前も保持されます。 行ストア インデックスの名前を既に使用しているスクリプトがある場合は、更新する必要はありません。

  次の例では、MyFactTable という名前のテーブル上のクラスター化された行ストア インデックスをクラスター化列ストア インデックスに変換します。 インデックス名 ClusteredIndex_d473567f7ea04d7aafcac5364c241e09 は同じままになります。

  CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09
  ON MyFactTable
  WITH (DROP_EXISTING = ON);
  

セグメントの削除を理解する

それぞれの行グループには、テーブルの 1 つの列につき 1 つの列セグメントが含まれます。 それぞれの列セグメントは一緒に圧縮され、物理メディアに格納されます。

セグメントを読み取らずに高速に削除できるように、各セグメントにメタデータがあります。 データ型の選択は、列ストア インデックスに対するクエリの一般的なフィルター述語に基づくクエリ パフォーマンスに大きな影響を与える可能性があります。 詳細については、「セグメントの削除」を参照してください。

Related tasks

列ストア インデックスを作成および管理するための一般的なタスクの概要については、「 関連タスク」を参照してください。

Related content

• 列ストア インデックスの新機能
• データウェアハウジングにおける列ストアインデックス
• 列ストア インデックス - クエリ パフォーマンス