【図解】SQL Server / Azure Synapse Analyticsのインデックスの種類と考え方を整理する

こんにちは。この記事では、SQL ServerやAzure Synapse Analyticsで登場するインデックスの種類と、その考え方を絵などを用いながら整理してみたいと思います。

それではまいります。

インデックスの種類

最初に、SQL Server/Synapse Analyticsでサポートされるインデックスの種類を一覧にしてみたいと思います。

それぞれ以下の参考ドキュメントをもとに整理していますが、もし理解が正しくなければすみません、ご指摘頂けましたら幸いです。

分類インデックスSQL ServerAzure Synapse Analytics
行ストアクラスター化インデックス
非クラスター化インデックス
列ストアクラスター化インデックス
非クラスター化インデックス
メモリ最適化ハッシュインデックス
メモリ最適化された非クラスター化

*行ストアには「一意インデックス」と「フィルター選択されたインデックス」という概念もありますが、これらはクラスター化/非クラスター化インデックスに内包されるようでしたので、上の表には含めませんでした。

参考:

SQL Server

https://docs.microsoft.com/ja-jp/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15

Synapse

https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index

今回は、このうちSQL Server / Synapse双方で利用される上の3つのインデックスについて、一般的な概念と、Synapseにおける考慮点を整理しておこうと思います。

ヒープテーブル

と、その前にヒープテーブルを紹介します。

  • クラスター化インデックスを使用しないテーブル
  • 順序を指定せずにデータが格納される
  •  1 つまたは複数の非クラスター化インデックスを、ヒープとして格納されているテーブルに作成することができる
  • 行を格納するための永久的な論理的順序を指定するには、テーブルにクラスター化インデックスを作成し、テーブルがヒープにならないようにする
  • テーブルをヒープのままにしておく妥当な理由がない限り、ほとんどのテーブルには、慎重に選択されたクラスター化インデックスが必要

参考:

https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/heaps-tables-without-clustered-indexes?view=sql-server-ver15

Azure Synapse Analyticsにおけるヒープテーブル

  • クラスター列ストア テーブルは、6,000 万行を超えて初めて最適な圧縮が実現されるので、それに満たない場合はヒープかクラスター化インデックスの使用を検討する。クエリパフォーマンスが向上する可能性がある。

参考:

https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index#heap-tables

行ストアインデックス

クラスター化インデックス

  • リーフノードにインデックスキーで並び変えられた状態で実データが含まれている
  • ルート・中間ノードには、中間ノードまたはリーフノードページへのポインタが含まれる
  • 1つのテーブルには1つだけクラスター化インデックスを定義できる(データ行は 1 つの順序でしか並べ替えられないため)
  • クラスター化インデックスを含むテーブルをクラスター化テーブルとよぶ
  • B+ツリー構造インデックスの1つ
  • テーブル内のデータ行が並べ替えられた順に格納されるのは、テーブルにクラスター化インデックスが含まれているときだけ
  • クラスター化インデックスが含まれないテーブルのデータ行は、ヒープと呼ばれる順序付けられていない構造に格納される

参考:

https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15

https://docs.microsoft.com/ja-jp/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15#clustered-index-architecture

Azure Synapse Analyticsにおけるクラスター化インデックス

  • クラスター化インデックスは、1 つの行をすばやく取得する必要がある場合に、クラスター化列ストア テーブルを上回る可能性がある。
  • 1 行または極めて少数の行の検索を極めて高速で実行する必要があるクエリの場合、クラスター化インデックスまたは非クラスター化セカンダリ インデックスを検討する。
  • クラスター化インデックスを使用するデメリットは、クラスター化インデックスの列で非常に選択的なフィルターを使用するクエリのみに効果が得られること。

参考:

https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index#clustered-and-nonclustered-indexes

非クラスター化インデックス

非クラスター化インデックスのイメージ(ヒープテーブルにインデックスを作成した場合)
  • データとインデックスが独立した構造になっていて、実データはヒープテーブルまたはクラスター化テーブル上に存在する
  • リーフノードには実データへのポインタが格納されている。データがヒープテーブルに存在する場合は行を指すポインタが、クラスター化テーブルに存在する場合は、クラスター化インデックスキーを指す。
  • 一つのテーブルに複数(上限はある)作成できる

Azure Synapse Analyticsにおける非クラスター化インデックス

クラスター化インデックスに同じ。

https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index#clustered-and-nonclustered-indexes

列ストアインデックス

クラスター化列ストア インデックス

https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15#clustered-columnstore-index
  • 列単位でデータを格納する列指向のオブジェクト
  • 一般的なデータ ウェアハウスのデータ セットに特に適している
  • 列ストア インデックスによって、フィルター処理クエリ、集計クエリ、グループ化クエリ、スター結合クエリなどの一般的なデータ ウェアハウス クエリのパフォーマンスが向上する
  • 「行グループ」「列セグメント」「デルタストア」「Delete Bitmap」「Tuple Mover」などから構成される
  • B+ツリー構造のインデックスではない

クラスター化列ストアインデックスの詳細のしくみはこちらのQiitaに分かりやすくまとまっていましたので、そちらのリンクを貼っておきます。

https://qiita.com/masahiro-yamaguchi/items/f2b3abb1f0d65e3fbf6c

参考:

https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15#clustered-columnstore-index

https://docs.microsoft.com/ja-jp/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15

Azure Synapse Analyticsにおけるクラスター化列ストアインデックス

  • 専用 SQL プールでは、テーブルにインデックス オプションが指定されていない場合、既定でクラスター化列ストア インデックスが作成される
  • セグメントの品質が最も最適化されるのは、圧縮された行グループごとに少なくとも 10 万行が存在するときで、行グループごとの行数が 1,048, 576 行(最大行)に近づくほどパフォーマンスが向上する。(これはたぶんSQL Serverでも一緒かな・・)

参考:

https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index#clustered-columnstore-indexes

https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index#optimizing-clustered-columnstore-indexes

以上、SQL ServerとSynapse Analyticsで登場するインデックスの概要についてのまとめでした。

参考になりましたら幸いです。

おしまい

この記事を気に入っていただけたらシェアをお願いします!

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

ABOUT US
Yuu113
初めまして。ゆうたろうと申します。 兵庫県出身、東京でシステムエンジニアをしております。現在は主にデータ分析、機械学習を活用してビジネスモデリングに取り組んでいます。 日々学んだことや経験したことを整理していきたいと思い、ブログを始めました。旅行、カメラ、IT技術、江戸文化が大好きですので、これらについても記事にしていきたいと思っています。