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

適用対象: はいSQL Server (サポートされているすべてのバージョン) はいAzure SQL データベース はいAzure SQL Managed Instance はいAzure Synapse Analytics はいParallel Data Warehouse

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

列ストア インデックスは、従来の行ストア インデックスよりも最大で 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 no no DML 操作は並列ではないため、バッチ モードでは実行できません。 直列モードのバッチ処理を有効にして、DML のバッチ モードでの処理を許可したとしても、パフォーマンスの向上はほとんど認められません。
列ストア インデックス スキャン SCAN NA はい はい 列ストア インデックスの場合は、SCAN ノードに述語をプッシュできます。
列ストア インデックス スキャン (非クラスター化) SCAN はい はい はい はい
Index Seek NA NA 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 NA NA はい SQL Server 2016 (13.x) の新しいオペレーター。

1SQL Server 2016 (13.x)、SQL Database Premium 層、Standard 層 - S3 以上、およびすべての仮想コア層と Parallel Data Warehouse に適用されます。

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

集計プッシュ ダウン

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

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

集計プッシュ ダウンは、キャッシュに対応した実行時に圧縮/エンコード データを効率的に集計し、SIMD を活用することでさらに高速になります。

集計プッシュダウン

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

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 を評価する式はサポートされていません。

参照

列ストア インデックスのデザイン ガイドライン 列ストア インデックスのデータ読み込みガイダンス
列ストアを使用したリアルタイム運用分析の概要
データ ウェアハウスの列ストア インデックス
インデックスの再構成と再構築
列ストア インデックスのアーキテクチャ
CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)