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

  • [アーティクル]

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

1SQL 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
    • smallmoney、 money、 decimal および numeric 有効桁数 <= 18
    • 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 データ型 (最大) データ型の長さなどには適用されません。

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

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

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

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

次のステップ