インデックスを使用して最適化する
インデックスは、テーブル行への最適化された参照パスを作成することで、データの取得を高速化するデータ構造です。 インデックスがない場合、データベース エンジンはテーブル内のすべての行をスキャンして一致するレコードを検索する必要があります。これは、テーブルの拡大に伴って非常に遅くなる完全なテーブル スキャンです。
インデックスは書籍のインデックスのように機能します。すべてのページを読んで記事を見つける代わりに、インデックスを参照して関連するページに直接ジャンプします。 データベースは同様にインデックスを使用し、数百万行の比較を少数の効率的な検索に変換する可能性があります。
ただし、データベースはデータと共にインデックス構造を維持する必要があるため、インデックスはストレージ領域を消費し、 INSERT、 UPDATE、および DELETE 操作が遅くなります。 このトレードオフにより、インデックスの選択は、クエリのパフォーマンスと書き込みスループットの両方に直接影響する重要な設計上の決定になります。
インデックスの種類が異なると、目的が異なります。
行ストアインデックスを利用する
効率的なインデックスの設計は、優れたデータベースとアプリケーションのパフォーマンスを実現するための鍵となります。 インデックスの不足、インデックスの過剰なインデックス作成、またはインデックスの設計が不十分な場合は、データベース パフォーマンスの問題の主な原因となります。
行ストア インデックスは行形式でデータを整理し、行のすべての列を同じページにまとめて格納します。これにより、完全なレコードを取得したり、頻繁に更新を実行したりするトランザクション ワークロードに最適です。
クラスター化インデックスは 、キー値に基づいてデータ行を並べ替え、テーブルに格納します。 これらのキー値は、インデックス定義に含まれる列です。 データ行自体は 1 つの順序でしか並べ替えられないので、1 つのテーブルに設定できるクラスター化インデックスは 1 つだけです。
非クラスター化インデックス には、データ行とは別の構造があります。 非クラスター化インデックスには、非クラスター化インデックスのキー値が含まれており、各キー値のエントリにはキー値が含まれているデータ行へのポインターが含まれています。 1 つのテーブルまたはインデックス付きビューに複数の非クラスター化インデックスを作成できます。
-- Create clustered index on primary key (defines physical row order)
CREATE CLUSTERED INDEX IX_Product_ProductID
ON Product(ProductID);
-- Create non-clustered index on frequently searched column
CREATE NONCLUSTERED INDEX IX_Product_Category
ON Product(Category)
INCLUDE (ProductName, Price);
クラスター化インデックスは、効率的な範囲クエリ、安定した狭いキー、または ID 列や日付フィールドなどの自然な並べ替え順序が必要な場合に最適です。これは、物理的な行の順序を定義し、順序付けられたデータに対するスキャンを最適化するためです。
非クラスター化インデックスは、クラスター化キーと一致しない特定の述語、結合、または並べ替えパターンの高速検索が必要な場合、またはキー参照を回避するために追加の列を含めることでクエリをカバーする場合に最適です。
どちらを選択するかは、データへのアクセス方法によって異なります。プライマリ アクセス パスにクラスター化インデックスを使用し、非クラスター化インデックスを使用して、書き込み操作で発生するコストのバランスを取りながら、代替パターン、高度に選択されたパターン、または頻繁にクエリされるパターンをサポートします。
列ストア インデックスを理解する
従来の行ストア インデックスは、データを行ごとに格納します。これは、個々のレコードを取得するトランザクション システムに最適です。 しかし、何百万行もの行をスキャンして集計 (SUM、 AVG、 COUNT) を計算する分析クエリでは、不要な列を読み取る時間が無駄になります。 列ストア インデックスは、クエリに必要な列のみを読み取り、列ごとにデータを格納することでこれを解決することを目的としています。
列ストア アーキテクチャを理解する
列ストア インデックスは、最大 1,048,576 行を含む行 グループにデータを整理します。 各行グループ内では、エンジンは各列を個別に 列セグメント として格納し、個別に圧縮します。 このアーキテクチャにより、クエリ オプティマイザーはクエリに必要な列のみを読み取り、無関係なデータを完全にスキップできます。
データを挿入すると、小さなバッチは最初に デルタストア (B+ ツリー インデックスを使用する一時的な行ストア構造) に移動します。 デルタ行グループが十分な行 (少なくとも 102,400) を蓄積すると、 タプルムーバー と呼ばれるバックグラウンド プロセスによって列ストアに圧縮されます。 102,400 行以上の一括読み込みによって到着する行は、デルタストアをバイパスし、列ストアに直接圧縮します。
次の表では、列ストア インデックスの推奨事項について説明します。
| Scenario | 勧告 | 理由 |
|---|---|---|
| データウェアハウスのファクトテーブル | 列ストアを使用する | 分析に使用される数百万を超える行を含むテーブルは、列形式のストレージと圧縮のメリットを得られます |
| レポート データベース | 列ストアを使用する | 集計クエリを使用した読み取り負荷の高いワークロードは、列指向アクセスで高速に実行されます |
| 履歴データ | 列ストアを使用する | ほとんど更新しないが頻繁に分析するアーカイブデータは、高い圧縮率を実現します |
| 小さいテーブル (<100 万行) | 列ストアを回避する | オーバーヘッドが利点を上回る。行グループは効果的に圧縮するために十分な行が必要です。 |
| 頻度の高い更新/削除 | 列ストアを回避する | 変更により、行が更新されるのではなく削除済みとしてマークされ、断片化が発生する |
| 1 行のルックアップ | 列ストアを回避する | 行ストア インデックスを使用すると、個々のレコードを取得する時間が短縮されます |
Clustered Columnstore Index (CCI) を使用する
クラスター化列ストア インデックス (CCI) は、既存のクラスター化行ストア インデックスを置き換えて、テーブル全体のプライマリ ストレージ構造となる列ストア インデックスの一種です。 行ストア テーブルと共にセカンダリ列形式のコピーを作成する非クラスター化列ストア インデックス (NCCI) とは異なり、CCI ではすべてのテーブル データが列形式でのみ格納されます。
つまり、テーブルには従来の行ベースのストレージがなく、エンジンはすべての列を個別に圧縮して格納します。 CCI と NCCI はどちらも同じ列圧縮とバッチ処理の最適化を使用しますが、分析がプライマリ ワークロードであり、行レベルのトランザクション アクセス パターンは必要ない場合は CCI を使用します。 これに対し、NCCI を使用すると、トランザクション クエリの行ストア インデックスを維持しながら、同じテーブルに対する分析クエリの列構造を提供できます。
CREATE CLUSTERED COLUMNSTORE INDEX ステートメントを使用して、クラスター化列ストア インデックスを作成できます。 次に例を示します。
-- Create clustered columnstore index (replaces clustered rowstore)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory
ON SalesHistory;
-- Rebuild to improve compression
ALTER INDEX CCI_SalesHistory ON SalesHistory REBUILD;
非クラスター化列ストア インデックス (NCCI) を使用する
非クラスター化列ストア インデックス (NCCI) は、既存の行ストア テーブルと共に選択した列の個別の列コピーを作成し、同じテーブルがトランザクション ワークロードと分析ワークロードの両方を効率的に処理できるようにします。 このテーブルは、高速な単一行の検索と更新のために元のクラスター化行ストア インデックスを保持し、NCCI は分析クエリに最適化された列ベースのアクセスを提供します。 クエリ オプティマイザーは、クエリ パターンに基づいて行ストア構造と列ストア構造の間で自動的に選択します。
CREATE NONCLUSTERED COLUMNSTORE INDEX ステートメントを使用して、非クラスター化列ストア インデックスを作成できます。 次に例を示します。
-- Create non-clustered columnstore for analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Product_Analytics
ON Product(Price, StockQuantity, Category, ProductName);
列ストア インデックスを監視する
sys.dm_db_column_store_row_group_physical_stats動的管理ビューに対してクエリを実行することで、列ストア インデックスの正常性とパフォーマンスを監視できます。
次のクエリは、状態、行数、削除された行、ストレージ サイズなどの行グループ統計を示しています。 開いている行グループはデルタストアでの挿入を引き続き受け入れ、閉じた行グループはタプルムーバーがそれらを圧縮するのを待機し、圧縮された行グループは列形式でデータを格納します。 削除された行数が多い、または多数の小さな行グループは、 ALTER INDEX REORGANIZEで解決できる断片化を示します。
-- Check columnstore health
SELECT
object_name(object_id) AS TableName,
state_desc,
total_rows,
deleted_rows,
size_in_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('SalesHistory');
インデックスの選択は、クエリのパフォーマンスと書き込みの両方のスループットに直接影響します。 初期開発中にインデックスを慎重に設計し、運用環境でのコストのかかる再構築やパフォーマンスの問題を回避します。