列ストア インデックス - クエリ パフォーマンス

  • [アーティクル]

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

列ストア インデックスによって提供される非常に高速なクエリ パフォーマンスを実現するための推奨事項について説明します。

列ストア インデックスは、従来の行ストア インデックスよりも最大で 100 倍も優れたパフォーマンスを分析およびデータ ウェアハウスのワークロードにもたらし、また最大 10 倍のデータ圧縮率を実現します。 ここで説明する推奨事項は、列ストア インデックスによって提供される非常に高速なクエリ パフォーマンスを実現するのに役立ちます。 列ストアのパフォーマンスに関する詳しい説明は、この記事の最後にあります。

クエリ パフォーマンスを向上させるための推奨事項

ここでは、列ストア インデックスによって提供される優れたパフォーマンスを実現するための推奨事項をいくつか示します。

1.データを整理してフル テーブル スキャンからより多くの行グループを削除する

  • 挿入順序の活用: 従来のデータ ウェアハウスでは、データを時間順に挿入し、時間ディメンションで分析を行うのが一般的です。 たとえば、四半期ごとに売り上げデータを分析する場合などです。 このようなワークロードの場合、行グループの削除は自動的に行われます。 SQL Server 2016 (13.x) では、クエリ処理の一部として複数の行グループがスキップされます。

  • 行ストア クラスター化インデックスの活用: 行の挿入順序とは無関係な列 (C1 など) に一般的なクエリ述語がある場合は、C1 列に行ストア クラスター化インデックスを作成し、その行ストア クラスター化インデックスを削除することでクラスター化列ストア インデックスを作成します。 MAXDOP = 1 を指定して明示的にクラスター化列ストア インデックスを作成した場合、その列ストア インデックスは C1 列に配置されます。 MAXDOP = 8 を指定した場合は、8 つの行グループにわたって値が重複します。 大きなデータ セットで最初に列ストア インデックスを作成する場合には、この方法が一般的です。 なお、非クラスター化列ストア インデックス (NCCI) については、ベースとなる行ストア テーブルにクラスター化インデックスがある場合、行は既に順序付けされており、 非クラスター化列ストア インデックスも自動的に順序付けされます。 また、列ストア インデックスでは行の順序が保持されない点に注意してください。 新しい行の挿入や古い行の更新によって分析クエリのパフォーマンスが低下する可能性があるので、その場合はこのプロセスを繰り返してください。

  • テーブルのパーティション分割の活用: 列ストア インデックスをパーティション分割し、その後パーティションを削除することで、スキャンする行グループの数を減らすことができます。 たとえば、ファクト テーブルには、顧客による購入が格納されます。 一般的なクエリ パターンとしては、特定の顧客による四半期ごとの購入情報を特定するには、挿入順序と顧客列のパーティション分割を組み合わせることができます。 各パーティションには、特定の顧客の行が時間順に格納されます。 また、列ストアからデータを削除する必要がある場合は、テーブルのパーティション分割の使用を検討してください。 不要になったパーティションの切り替えと切り捨ては、より小さな行グループを持つことによって生じる断片化の生成なしでデータを削除する効率的な方法です。

  • 大量のデータを削除しないようにします。 圧縮された行の行グループからの削除は、同期操作ではありません。 行グループを圧縮解除し、行を削除してから再圧縮すると、コストが高くなります。 したがって、圧縮された行グループからデータを削除すると、これらの行グループで返される行が少ない場合でも、それらがスキャンされます。 複数の行グループの削除された行の数が、少数の行グループにマージされるのに十分な大きさである場合、列ストアを再編成すると、インデックスの品質が向上し、クエリのパフォーマンスが向上します。 データの削除プロセスで通常は行グループ全体を空にする場合は、テーブルのパーティション分割の使用を検討し、不要なパーティションを切り替え、行を削除する代わりに切り捨てます。

    注意

    SQL Server 2019 (15.x) 以降、タプル ムーバーは、内部しきい値で指定した所定の期間存在していたと判断された小さい OPEN デルタ行グループを自動的に圧縮したり、大量の行が削除された COMPRESSED 行グループをマージしたりするバックグラウンド マージ タスクによってサポートされています。 これにより、時間の経過とともに、列ストア インデックスの品質が向上します。
    列ストア インデックスから大量のデータを削除する必要がある場合は、その操作を一定期間にわたる小さな削除バッチに分割することを検討します。これにより、バックグラウンド マージ タスクで小さな行グループをマージするタスクを処理してインデックスの品質を向上させることができ、データの削除後にインデックスの再編成メンテナンス ウィンドウをスケジュールする必要がなくなります。
    列ストアの用語と概念の詳細については、列ストア インデックス: 概要を参照してください。

2.列ストア インデックスを並列で作成するための十分なメモリの計画

列ストア インデックスの作成は、メモリに制限がない限り既定で並列操作になります。 インデックスを並列で作成するには、インデックスを順次作成する場合よりも多くのメモリが必要です。 十分なメモリがある場合、列ストア インデックスの作成には、同じ列で B-Tree を構築する場合の約 1.5 倍の時間がかかります。

列ストア インデックスを作成するために必要なメモリは、列数、文字列型の列数、並列処理の最大限度 (DOP)、およびデータの特性によって異なります。 たとえば、テーブル内の行数が 100 万未満の場合、SQL Server はスレッドを 1 つだけ使用して列ストア インデックスを作成します。

テーブルに 100 万を超える行があり、SQL Server で MAXDOP を使用してインデックスを作成するための十分なメモリ許可を取得できない場合、SQL Server は必要に応じて自動的に MAXDOP を減らし、使用できるメモリ許可に合うように調整します。 場合によっては、メモリが制限された状況でインデックスを構築できるように、DOP を 1 まで小さくする必要があります。

SQL Server 2016 (13.x) 以降では、クエリは常にバッチ モードで動作します。 以前のリリースでは、バッチ実行は DOP が 1 よりも大きい場合にのみ使用されます。

列ストアのパフォーマンスについて

列ストア インデックスは、高速のインメモリ バッチ モードの処理と、I/O 要件を大幅に削減する手法とを組み合わせることによって、クエリのパフォーマンスを向上させます。 分析クエリは大量の行をスキャンするため、通常は I/O バウンドであり、そのためクエリの実行中に I/O を減らすことは、列ストア インデックスの設計に大きく影響します。 メモリにデータが読み込まれた後は、インメモリ操作の数を減らすことが不可欠です。

列ストア インデックスは、高いデータ圧縮率、列ストアの削除、行グループの削除、およびバッチ処理により、I/O を減らしてインメモリ操作を最適化します。

データ圧縮

列ストア インデックスは、行ストア インデックスよりも最大で 10 倍のデータ圧縮率を実現します。 これにより、分析クエリを実行するために必要な I/O が大幅に削減され、クエリのパフォーマンスが向上します。

  • 列ストア インデックスは圧縮されたデータをディスクから読み取るため、メモリに読み込まれるデータ量が少なくなります。

  • 列ストア インデックスでは、データを圧縮してメモリに格納し、同じデータがメモリに読み込まれる回数を減らすことで、I/O を削減します。 たとえば、圧縮率が 10 倍であれば、圧縮せずにデータを格納した場合と比べて 10 倍のデータをメモリ内に保持できます。 メモリ内のデータが増えると、列ストア インデックスがメモリ内で必要なデータを探す際に、ディスクからの読み取りが少なくなる可能性が高くなります。

  • 列ストア インデックスでは、行ではなく列でデータが圧縮されることで高い圧縮率が実現され、ディスクに格納されるデータのサイズが縮小されます。 各列は個別に圧縮、格納されます。 同じ列内のデータは常に同じデータ型であり、同じような値を持つ傾向があります。 データ圧縮では、値が同等の場合に圧縮率が大幅に向上します。

  • たとえば、ファクト テーブルに顧客の住所が格納されていて、国/地域の列がある場合、そこに格納される値の総数は 200 よりも少なくなります。 これは、値の一部のが重複するからです。 ファクト テーブルに 1 億行あったとしても、国/地域の列は簡単に圧縮できるので、必要とするストレージもごくわずかで済みます。 行ごとに圧縮する場合は、このような列の圧縮とは異なり、国/地域の列の値を圧縮するのにより多くの容量を使用します。

列の削除

列ストア インデックスは、クエリ結果に必要のない列の読み込みをスキップします。 この機能は列の削除と呼ばれ、クエリ実行の I/O をさらに削減できるので、クエリのパフォーマンスが向上します。

  • 列を削除することができるのは、データが 1 列ずつ整理されて圧縮されるからです。 これに対し、データが行ごとに格納されている場合は、各行の列の値が物理的に一緒に保存されているので、簡単に分離することができません。 クエリ プロセッサでは、特定の列の値を取得するために行全体を読み取る必要があり、余分なデータが不必要にメモリに読み込まれるために、I/O が増加します。

  • たとえば、テーブルに 50 列あり、クエリではその内 5 列のみを使用する場合、列ストア インデックスはディスクからその 5 列のみをフェッチし、 他の 45 列の読み取りはスキップします。 すべての列が同じようなサイズであると仮定した場合、これによりさらに 90% の I/O を削減できます。 同じデータが行ストアに保存されている場合、クエリ プロセッサはさらに 45 列を読み取る必要があります。

行グループの削除

フル テーブル スキャンの場合、通常はデータの大部分がクエリ述語の条件と一致しません。 メタデータを使用することで、列ストア インデックスはクエリ結果に必要なデータが存在しない行グループの読み取りをスキップすることができます。実際の I/O を発生させることもありません。 この機能は行グループの削除と呼ばれ、フル テーブル スキャンの I/O を削減できるので、クエリのパフォーマンスが向上します。

列ストア インデックスがフル テーブル スキャンを実行する必要があるのはどのような場合ですか。

SQL Server 2016 (13.x) 以降、行ストア ヒープの場合と同様に、クラスター化列ストア インデックスに通常の非クラスター化 B ツリー インデックスを作成できるようになりました。 非クラスター化 B ツリー インデックスを使用して、等値述語または値の範囲が狭い述語を持つクエリを高速化できます。 より複雑な述語の場合、クエリ オプティマイザーがフル テーブル スキャンを選択する場合があります。 行グループをスキップすることができなければ、特に大規模テーブルではフル テーブル スキャンに非常に長い時間がかかります。

フル テーブル スキャンの際に行グループを削除することで、分析クエリにはどのようなメリットがありますか。

小売業を営む A 社を例に説明します。A 社は、クラスター化列ストア インデックスを持つファクト テーブルを使用して売上データをモデル化しています。 各売上データには、トランザクションのさまざまな属性 (製品の販売日など) が保存されます。 興味深いことに、列ストア インデックスでは並べ替え順は保証されませんが、このテーブルの行は日付順に読み込まれます。 時間の経過と共に、テーブルが増大していきます。 A 社が過去 10 年間の売上データを保管していたとしても、分析クエリでは前四半期の集計だけを計算すればよいのであれば、 列ストア インデックスは日付列のメタデータを調べるだけで、過去 39 四半期分のデータへのアクセスを回避できます。 これにより、メモリに読み込まれて処理されるデータ量をさらに 97% 削減できます。

フル テーブル スキャンでは、どの行グループがスキップされますか。

スキップする行グループを決定するために、列ストア インデックスはメタデータを使用して、各行グループの各列セグメントの最小値と最大値を格納します。 列セグメントの範囲のいずれもクエリ述語の条件を満たしていない場合は、行グループ全体がスキップされます。実際の IO は発生しません。 これが機能するのは、データは通常並べ替え順に読み込まれ、行の並べ替えは保証されていないものの、類似するデータ値は通常は同じ行グループまたは隣接する行グループ内に存在するためです。

行グループの詳細については、「列ストア インデックス デザイン ガイドライン」を参照してください。

バッチ モードでの実行

バッチ モードでの実行とは、実行効率を上げるために、通常 900 行までの行をまとめて処理することです。 たとえば、クエリ SELECT SUM (Sales) FROM SalesData は SalesData テーブルから総売上高を集計します。 バッチ モードでは、クエリ実行エンジンが 900 個の値をグループにまとめて計算します。 各行を個別に計算するのではなく、メタデータやアクセスやその他のオーバーヘッドをバッチ内のすべての行に分散させるので、コード パスを大幅に削減できます。 バッチ モードの処理は、可能な場合は圧縮データに対して行われるので、行モードの処理で使用される一部の交換操作が不要になります。 このため、分析クエリの実行速度が大幅にアップします。

すべてのクエリ実行演算子をバッチ モードで実行できるわけではありません。 たとえば、Insert、Delete、Update などの DML 操作は、一度に 1 行ずつ実行されます。 バッチ モードの演算子は、Scan、Join、Aggregate、Sort など、クエリのパフォーマンスを向上させる演算子を対象としています。 SQL Server 2012 (11.x) で列ストア インデックスが導入されて以来、バッチ モードで実行できる演算子を拡充する継続的な取り組みが行われています。 次の表は、バッチ モードで実行される演算子と、対応する製品のバージョンを示します。

バッチ モードで実行される演算子 用途 SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) と SQL Database1 説明
DML 操作 (Insert、Delete、Update、Merge) いいえ no いいえ DML 操作は並列ではないため、バッチ モードでは実行できません。 直列モードのバッチ処理を有効にして、DML のバッチ モードでの処理を許可したとしても、パフォーマンスの向上はほとんど認められません。
列ストア インデックス スキャン SCAN 使用不可 はい はい 列ストア インデックスの場合は、SCAN ノードに述語をプッシュできます。
列ストア インデックス スキャン (非クラスター化) SCAN はい はい はい はい
Index Seek 使用不可 使用不可 いいえ 行モードの非クラスター化 B ツリー インデックスを通じてシーク操作を実行します。
Compute Scalar スカラー値に評価される式。 はい はい はい データ型にいくつか制限事項があり、 すべてのバッチ モード演算子が該当します。
連結 (concatenation) UNION および UNION ALL いいえ はい はい
filter 述語の適用 はい はい はい
Hash Match ハッシュ ベースの集計関数、外部ハッシュ結合、右ハッシュ結合、左ハッシュ結合、右内部結合、左内部結合 はい はい はい 集計の制限: 文字列には最小値/最大値はありません。 使用可能な集計関数は SUM/COUNT/AVG/MIN/MAX です。
結合の制限: 非整数型では不一致の型が結合されません。
Merge Join いいえ no いいえ
マルチ スレッド クエリ はい はい はい
入れ子になったループ いいえ no いいえ
MAXDOP 1 で実行されるシングル スレッド クエリ いいえ no はい
直列クエリ プランを持つシングル スレッド クエリ いいえ no はい
sort 列ストア インデックスを持つ SCAN 上の ORDER BY 句 いいえ no はい
Top Sort いいえ no はい
Window Aggregates 使用不可 使用不可 はい SQL Server 2016 (13.x) の新しい演算子です。

1 SQL Server 2016 (13.x)、SQL Database Premium レベル、Standard レベル - S3 以降、およびすべての仮想コア層、および Analytics Platform System (PDW) に適用されます

詳細については、「クエリ処理アーキテクチャ ガイド」をご覧ください。

集計プッシュダウン

SCAN ノードから条件を満たす行をフェッチしてバッチ モードで値を集計する、集計計算の通常の実行パスです。 このやり方で良いパフォーマンスを得られますが、SQL Server 2016 (13.x) では、次の条件を満たしていれば、集計操作を SCAN ノードにプッシュして、集計計算のパフォーマンスを大幅に (バッチ モードでの実行に加えてさらに) 向上できます。

  • 集計は MIN、MAX、SUM、COUNT、COUNT(*) です。
  • 集計演算子は SCAN ノードまたは GROUP BY を含む SCAN ノード上にある必要があります。
  • この集計は、個別の集計ではありません。
  • 集計列は、文字列型の列ではありません。
  • 集計列は、仮想列ではありません。
  • 入力と出力のデータ型は次のいずれかであり、64 ビットに収まる必要があります。
    • tinyint、int、bigint、smallint、bit
    • 有効桁数が 18 以上の smallmoney、money、decimal、numeric
    • smalldate、date、datetime、datetime2、time

たとえば、以下の両方のクエリで集計プッシュ ダウンが可能です。

SELECT  productkey, SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI
GROUP BY productkey;
    
SELECT  SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI;

文字列述語のプッシュ ダウン

データ ウェアハウスのスキーマを設計する際は、1 つ以上のファクト テーブルと多数のディメンション テーブルで構成されたスター スキーマまたはスノーフレーク スキーマを使用することをお勧めします。 ファクト テーブル にはビジネスの測定値やトランザクションを格納し、 ディメンション テーブル にはファクトの分析が必要なディメンションを格納します。

たとえば、特定の地域における特定の商品の売上を表すレコードがファクトで、一連の地域や商品などを表すのがディメンションす。 ファクト テーブルとディメンション テーブルは、主キーと外部キーのリレーションシップによって接続されます。 1 つ以上のディメンション テーブルをファクト テーブルと結合する分析クエリが最もよく使用されます。

ディメンション テーブル Products について考えてみましょう。 一般的な主キーは ProductCode で、通常は文字列データ型として表されます。 クエリのパフォーマンスのためには、代理キー (通常は整数型の列) を作成して、ファクト テーブルからディメンション テーブル内の行を参照することをお勧めします。

列ストア インデックスでは、数値または整数ベースのキーが関与する結合/述語を使用する分析クエリが非常に効率よく実行されます。 ただし、多くの顧客ワークロードでは、ファクト/ディメンション テーブルをリンクする文字列ベースの列を使用した場合、列ストア インデックスを含むクエリのパフォーマンスが低くなることがわかっています。 SQL Server 2016 (13.x) では、文字列型の列を持つ述語を SCAN ノードをプッシュ ダウンすることで、文字列ベースの列を持つ分析クエリのパフォーマンスを大きく向上しています。

文字列述語のプッシュ ダウンは、列向けに作成されるプライマリ/セカンダリ辞書を利用して、クエリのパフォーマンスを向上させます。 たとえば、行グループ内の文字列型の列セグメントが 100 個の異なる文字列値で構成されている場合、 行数を 100 万と仮定すると、それぞれの文字列の値が平均 10,000 回参照されることになります。

文字列述語のプッシュ ダウンでは、クエリ実行時にディクショナリの値に対して述語を計算します。そこで条件を満たしていれば、ディクショナリの値を参照するすべての行が自動的に条件を満たすことになります。 これにより、次の 2 点においてパフォーマンスが向上します。

  1. 条件を満たす行だけが返されるので、SCAN ノード外に出ていく行数が削減されます。

  2. 文字列比較の数が大幅に削減されます。 この例では、100 万回の比較に対して、文字列の比較は 100 回で済んでいます。 なお、制限事項もいくつかあります。

    • デルタ行グループでは文字列述語のプッシュ ダウンはできません。 デルタ行グループの列には辞書がありません。
    • 辞書が 64 KB を超えている場合、文字列述語のプッシュ ダウンはできません。
    • NULL を評価する式はサポートされていません。

セグメントの削除

データ型の選択は、列ストア インデックスに対するクエリの一般的なフィルター述語に基づくクエリ パフォーマンスに大きな影響を与える可能性があります。

列ストア データでは、行グループは列セグメントで構成されます。 セグメントを読み取らずに高速に削除できるように、各セグメントにメタデータがあります。 このセグメントの削除は、数値、日付、時刻のデータ型、およびスケールが 2 以下の datetimeoffset データ型に適用されます。 SQL Server 2022 (16.x) からは、セグメントの削除機能が、文字列、バイナリ、guid データ型、およびスケールが 2 より大きい datetimeoffset データ型まで拡張されます。

文字列の最小/最大セグメントの削除 (SQL Server 2022 (16.x) 以降) をサポートする SQL Server のバージョンにアップグレードした後、列ストア インデックスは REBUILD または DROP/CREATE を使用して再構築されるまで、この機能を利用できません。

セグメントの削除は、(最大) データ型の長さなどの、LOB データ型には適用されません。

現時点では、SQL Server 2022 (16.x) 以降でのみ、LIKE述語のプレフィックス (column LIKE 'string%' など) に対してクラスター化列ストアの行グループ除去がサポートされます。 LIKE のプレフィックス以外の使用 (column LIKE '%string' など) では、セグメントの削除がサポートされません。

Azure Synapse Analytics では、SQL Server 2022 (16.x) 以降では、順序付けされたクラスター化列ストア インデックスを作成できます。これは、列ごとの順序付けにより、セグメントの削除 (特に文字列列) を支援します。 順序付けされたクラスター化列ストア インデックスでは、インデックス キーの最初の列でのセグメントの削除を行うと、並べ替えが行われるため、最も効果的です。 テーブル内の他の列でのセグメントの削除によるパフォーマンスの向上は、それよりも予測しにくいです。 順序付けされたクラスター化列ストア インデックスの詳細については、「大規模なデータ ウェアハウス テーブルに順序付けされたクラスター化列ストア インデックスを使用する」を参照してください。

クエリ接続オプション SET STATISTICS IO を使用すると、セグメントの削除を実際に表示できます。 次のような、セグメントの削除が発生したことを示す出力を探します。 行グループは列セグメントで構成されるため、セグメントの削除を示している可能性があります。 クエリの次の SET STATISTICS IO 出力例では、約 83% のデータがクエリによってスキップされました。

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

次のステップ