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

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

この記事には、列ストア インデックスを使用して高速なクエリ パフォーマンスを実現するための推奨事項が含まれています。

列ストア インデックスは、分析とデータ ウェアハウスのワークロードで最大 100 倍のパフォーマンスを実現でき、従来の行ストア インデックスよりも最大 10 倍優れたデータ圧縮を実現できます。 これらの推奨事項は、列ストア インデックスが提供するように設計されている高速なクエリ パフォーマンスをクエリで実現するのに役立ちます。

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

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

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

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

• 行ストア クラスター化インデックスを使用します。 一般的なクエリ述語が挿入順序に関係のない列 (たとえば、 C1) にある場合は、列 C1に行ストア クラスター化インデックスを作成します。 次に、行ストア クラスター化インデックスを削除し、クラスター化列ストア インデックスを作成します。 MAXDOP = 1を使用してクラスター化列ストア インデックスを明示的に作成すると、結果として得られるクラスター化列ストア インデックスは列C1に完全に順序付けられます。 MAXDOP = 8を指定すると、8 つの行グループ間の値の重複が表示されます。 非クラスター化列ストア インデックス (NCCI) の場合、テーブルに行ストア クラスター化インデックスがある場合、行はクラスター化インデックス キーによって既に並べ替えられます。 この場合、非クラスター化列ストア インデックスも自動的に順序付けされます。 列ストア インデックスは、本質的に行の順序を維持しません。 新しい行が挿入されるか、古い行が更新されると、分析クエリのパフォーマンスが低下する可能性があるため、プロセスを繰り返す必要があります。

• テーブルのパーティション分割を実装します。 列ストア インデックスをパーティション分割してから、パーティションの削除を使用して、スキャンする行グループの数を減らすことができます。 たとえば、ファクト テーブルには、顧客による購入が格納されます。 一般的なクエリ パターンは、 customerで四半期ごとの購入を見つけることです。 この場合は、挿入順序列と列のパーティション分割 customer 結合します。 各パーティションには、挿入時に並べ替えられた各 customerの行が含まれます。 また、列ストアから古いデータを削除する必要がある場合は、テーブルパーティション分割の使用を検討してください。 不要なパーティションの切り替えと切り捨ては、断片化を発生させずにデータを削除する効率的な方法です。

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

  Note

  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 倍のデータをメモリに保持できます。 メモリ内のデータが多いと、列ストア インデックスで必要なデータがメモリ内で検出され、ディスクからの不要な読み取りが発生しない可能性が高くなります。

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

たとえば、ファクト テーブルには顧客の住所が格納され、 country-regionの列があります。 指定できる値の合計数が 200 未満です。 これらの値の一部は何度も繰り返されます。 ファクト テーブルに 1 億行が含まれている場合、 country-region 列は簡単に圧縮され、ストレージはほとんど必要としません。 行単位の圧縮では、この方法では列の値の類似性を大文字にすることはできません。また、 country-region 列の値を圧縮するには、より多くのバイトを使用する必要があります。

列の削除

列ストア インデックスは、クエリ結果に必要のない列の読み込みをスキップします。 列の削除により、クエリ実行の I/O がさらに減少するため、クエリのパフォーマンスが向上します。

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

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

行グループの削除

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

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

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

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

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

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

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

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

バッチ モードでの実行

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

すべてのクエリ実行演算子をバッチ モードで実行できるわけではありません。 たとえば、挿入、削除、更新などのデータ操作言語 (DML) 操作は、一度に 1 行ずつ実行されます。 スキャン、結合、集計、並べ替えなどのバッチ モード演算子を使用すると、クエリのパフォーマンスを向上させることができます。 SQL Server 2012 (11.x) で列ストア インデックスが導入されて以来、バッチ モードで実行できる演算子を拡充する継続的な取り組みが行われています。 次の表は、製品のバージョンに従ってバッチ モードで実行される演算子を示しています。

¹ 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
  • smallmoney、 money、 decimal および 数値 精度 < = 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であり、一般的に文字列として表されます。 クエリのパフォーマンスを向上させるには、ファクト テーブルからディメンション テーブルの行を参照するために、代理キー (通常は integer 列) を作成することをお勧めします。

列ストア インデックスは、数値または整数ベースのキーを含む結合と述語を使用して分析クエリを効率的に実行します。 SQL Server 2016 (13.x) では、文字列列を含む述語を SCAN ノードにプッシュダウンすることで、文字列ベースの列を含む分析クエリのパフォーマンスが大幅に向上しました。

文字列述語のプッシュダウンでは、列用に作成されたプライマリ/セカンダリ ディクショナリを利用して、クエリのパフォーマンスを向上させます。 たとえば、100 個の個別の文字列値で構成される行グループ内の文字列列セグメントを考えてみましょう。 各個別の文字列値は、100 万行を想定して、平均で 10,000 回参照されます。 文字列述語プッシュダウンでは、クエリ実行によってディクショナリ内の値に対して述語が計算されます。 述語が修飾される場合、ディクショナリ値を参照するすべての行が自動的に修飾されます。 これにより、次の 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' など) では、セグメントの削除がサポートされません。

順序付けされたクラスター化列ストア インデックス は、特に文字列列のセグメントの削除の利点もあります。 順序付けされたクラスター化列ストア インデックスでは、インデックス キーの最初の列でのセグメントの削除を行うと、並べ替えが行われるため、最も効果的です。 テーブル内の他の列でのセグメントの削除によるパフォーマンスの向上は、それよりも予測しにくいです。 順序付けされたクラスター化列ストア インデックスの詳細については、「大規模なデータ ウェアハウス テーブルに順序付けされたクラスター化列ストア インデックスを使用する」を参照してください。 順序付け列ストア インデックスの可用性については、「 順序付き列インデックスの可用性」を参照してください。

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

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

関連するコンテンツ

• 列ストア インデックスのデザイン ガイドライン
• 列ストア インデックス - データ読み込みガイダンス
• 列ストアを使用したリアルタイム運用分析の概要
• データ ウェアハウスの列ストア インデックス
• クエリのパフォーマンスを向上させてリソースの消費を削減するためにインデックスのメンテナンスを最適化する
• 列ストア インデックスのアーキテクチャ
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)