Azure SQLDatabase列ストアインデックスを利用する

SQL Database

f:id:vasilyjp:20181120114036j:plain

こんにちは。開発部に所属している鶴見です。 弊社ではAzure SQLDatabaseを利用して運用している箇所があります。 SQLDatabaseのデータ検索に利用している列ストアインデックスについて紹介します。

はじめに

Azure SQLDatabaseはMicrosoft社が提供しているマネージドデータベースであり、SQL Serverエンジンと互換性があります。 ZOZOTOWNで扱っている大量データの中から対象データをピンポイントに探す場合は、SQLDatabaseのB-treeインデックスを利用し高速に検索しています。 しかし、B-treeインデックスは広範囲の検索が苦手という弱点があります。 そこで、広範囲の検索をする場合は、列ストアインデックスを利用してみました。 主にデータウェアハウスやデータ分析にて利用されていますが、 ここ数年の進化によりOLTPでも利用しやすいインデックスとなりました。

列ストアインデックスとは

広範囲の検索で性能向上が見込める列指向型インデックスです。 B-treeインデックスが行単位でデータを扱うのに対して、列ストアインデックスは列単位でデータを扱います。

f:id:vasilyjp:20181119193609p:plain

列ストアインデックスとB-treeインデックスの比較です。

インデックス種類 B-treeインデックス 列ストアインデックス
指向性 行指向 列指向
読込 ページ単位（1ページ8kb） セグメント単位（1セグメント約100万件）
圧縮 可 可
scan操作 可 可
seek操作 可 不可
単一行検索 得意 苦手
広範囲検索 苦手 得意

B-treeインデックスで1カラム100万件のデータ取得を行うと大量のデータページを読込みます。 対して、列ストアインデックスは1カラム100万件を1セグメントとしているため、1回の読込みで済みます。 また、列単位で圧縮されるため圧縮率が高く非圧縮データ サイズと比較して、最大10倍のデータ圧縮が見込めます。 列単位で管理されることによりB-treeインデックスのように指定するカラムの順番を気にする必要もありません。 よって大量データを集計する処理などデータ分析に向いています。

SQL Server2012で登場した当初は「行データの更新・削除・挿入は行えない」などの制限があり、 常にデータ更新のあるデータベースでは利用しにくい状況でした。 しかし、SQL Server2014～2017にかけて制限が緩和され、データ更新に対応しました。
SQLDatabaseで列ストアインデックスを利用できるモデルは次の通りです。

  • Premiumレベル
  • Standard S3レベル以上
  • General Purposeレベル
  • Business Criticalレベル

列ストアインデックス作成

次の構文にて列ストアインデックスを作成可能です。

CREATE NONCLUSTERED COLUMNSTORE INDEX [index_name] ON [table_name]
(
    [column_1],
    [column_2],
    [column_3]
)

性能検証

実際にテストデータで性能を検証してみます。 テストテーブルに1億件のデータを登録し、そのうち1千万件データを取得した場合のB-treeインデックスと列ストアインデックスの処理能力を検証します。

-- テストテーブル
CREATE TABLE test (
    id int NOT NULL,
    age int NOT NULL,
    name nvarchar(100) null
 CONSTRAINT primary_key_test PRIMARY KEY CLUSTERED 
(
    id ASC
)

1億件のテストデータを登録した後、 カラム[age]に対してB-treeインデックス、列ストアインデックスをそれぞれ作成します。

-- B-treeインデックス作成
CREATE NONCLUSTERED INDEX [NONCLUSTERED_INDEX]
ON [dbo].[test]
([age])
GO

-- 列ストアインデックス作成
CREATE NONCLUSTERED COLUMNSTORE INDEX [COLUMNSTORE_INDEX_test] 
ON [dbo].test
([age])
GO

検証のため各インデックスが利用されるようヒント句でインデックスを固定しクエリを発行します。 （テストデータ1億件のうち、age = 21が1000万件となるようにデータを作成しています）

-- 1000万件取得クエリ B-treeインデックス使用
select count(*) From test with(index(NONCLUSTERED_INDEX)) where age = 21

-- 1000万件取得クエリ 列ストアインデックス使用
select count(*) From test with(index(COLUMNSTORE_INDEX_test)) where age = 21

検証結果

検証結果は次の通りです。

B-treeインデックス（非クラスター化インデックス）を利用した場合。
実行プラン / 実行時間 f:id:vasilyjp:20181119193725p:plainf:id:vasilyjp:20181119194608p:plain

列ストアインデックス（非クラスター化列ストアインデックス）を利用した場合。
実行プラン / 実行時間 f:id:vasilyjp:20181119194016p:plainf:id:vasilyjp:20181119194030p:plain

  • 列ストアインデックスが利用されていることが確認できる。
  • クエリにてCOUNTを行ったためハッシュ ベースの集計関数が利用されHash Match操作となった。
  • 並列実行のためParallelism操作となった。
インデックス種類 CPU time elapsed time
B-treeインデックス 2220ms 1647ms
列ストアインデックス 46ms 8ms

CPU time及び、elapsed time（経過時間）の結果から、列ストアインデックスを利用した方が高速でした。 処理時間が「CPU time > elapsed time」となっているのは、並列で処理されているためです。

f:id:vasilyjp:20181119193948p:plain

実行プラン詳細にて、論理操作はインデックススキャンであることが確認できます。 これは列単位で全件スキャンを行っているためです。 また、列ストアインデックスを利用すると実行モードはBatchとなります。

気を付けたいポイント

一見良さそうな列ストアインデックスですが、メンテナンスを怠ると性能が低下します。 列ストアインデックスに含まれる列データが大量に更新された場合、更新データは非圧縮状態になります。 圧縮されていることで最適なパフォーマンスとなるため、非圧縮データが増えてしまうと読込むデータが増え性能低下します。

列ストアインデックス状態確認

次のクエリで列ストアインデックスの圧縮状況を確認できます。

-- 列グループ状態確認
select OBJECT_NAME(object_id) AS object_name ,* from sys.column_store_row_groups

f:id:vasilyjp:20181119194103p:plain

total_rowsの値を確認すると、約100万件の単位でグループ化されていることが分かります。 state_descriptionの値がCOMPRESSEDの場合はデータ圧縮されており最適な状態です。 データ更新されるとstate_descriptionの値がOPENに変わり非圧縮状態となります。 OPENの件数が増えた場合は、インデックス再構成を実行して圧縮状態に戻すことができます。 インデックス再構成はオンラインで実行可能です。

更新が多いテーブルで列ストアインデックスを利用する場合、どのようなタイミングでインデックス再構成を行うか考える必要があります。 実際に利用する場合は、よく検証してから利用することをお勧めします。

まとめ

狭域の検索はB-treeを利用し、広範囲の検索は列ストアインデックスに置き換えました。 これにより、どのような検索であっても高速に処理することが可能となりました。 B-treeインデックスに比べると、考慮すべきことが多い列ストアインデックスですが、使いどころによってはかなり有効かと考えます。

参考文献 列ストア インデックス: 概要 | Microsoft Docs

最後に

本文でも紹介しましたが、本システムにはまだまだ問題が残されています。弊社では一緒にデータ基盤を作ってくれる方を大募集しています。 ご興味がある方は以下のリンクから是非ご応募ください！

www.wantedly.com

カテゴリー