はじめに
SQL Server では 2016 SP1 2012 以降、クラスター化列ストアインデックス (Clustered Columnstore Index, CCI) が利用できます。
- CCI はテーブルのデータを列方向に圧縮して保持する、OLAP 向けのインデックスです。
- CCI の公式ドキュメントはこちらなどです。
CCI 利用テーブルには少量データ抽出用途で非クラスター化(行)インデックス (Nonclustered Index, 以下 NcI) を設定できます。
この記事では CCI 利用テーブルに NcI を設定した時の実行計画についていくつか確認した内容を記載します。
2020-08-11 時点の、SQL Server on Docker container, 2019-latest で検証しています。
tl;dr
下記のように実行計画が構築されました。
- CCI のみを設定している場合、CCI スキャン利用
- 抽出条件で利用する列に NcI を張り、抽出する列を NcI に含めない場合
- 抽出する行数が少量の場合は NcI シークと CCI への Key Lookup 利用
- 抽出する行数が多くなると CCI スキャン利用
- 抽出条件で利用する列に NcI を張り、抽出する列を NcI に含めるか付加列としている場合は NcI シーク利用
実行計画の確認
準備
下記の cci_table に CCI を設定し、filter_col の値を基にデータを抽出します。
- CCI の列圧縮がかかるように、およそ 400 万行を追加した後 Index Rebuild をかけています。
- CI を経由して作成していないため、"ordered" な CCI ではありません。
- PK については今回利用しないため未設定です。
- データ追加の詳細については割愛します。
-- テーブル定義
create table [cci_table] (
[pk_col] int,
[filter_col] int,
[val_col] nvarchar(10)
)
create clustered columnstore index [cci] on [cci_table]
下記のクエリの実行計画を確認しました。
Table Option による利用インデックス指定は実施していません。
-- n 件のデータ抽出
-- その 1: 少量データ抽出 (n = 1)
select [pk_col], [val_col] from [cci_table] where [filter_col] = 1
-- その 2: 多量データ抽出 (n = 4095999)
select [pk_col], [val_col] from [cci_table] where [filter_col] = 2
CCI のみの場合
この時の実行計画は下記のとおり、当然ですがその 1, その 2 のどちらも CCI スキャンが利用されます。
1 のみ掲載します。(取得ソフトの都合で詳細が見切れていますがご容赦ください。)
抽出条件で利用する列に NcI を張り、抽出する列を NcI に含めない場合
準備で用意した cci_table に NcI を張ります。
create nonclustered index [nci] on [cci_table] (
[filter_col] -- 抽出条件で利用する列
)
まず、その 1: 少量データ抽出時の実行計画は以下のとおり、NcI シーク + Key Lookup です。
Key Lookup については概要表示は CCI スキャンとなっていますが、詳細を表示すると Key Lookup と表示されます。
また、操作の説明も Key Lookup のものになっています。
そして、その 2: 多量データ抽出時の実行計画は以下のとおりです。
抽出件数がその 1 と比べて多いため、CCI スキャンが採用されています。
この記事を書くきっかけは、"その1: 少量データ抽出のケースで CCI の場合でも NcI シーク + Key Lookup が選択されるのだろうか？"という疑問にあったのですが、確認したかぎり"選択される"ようです。
また、Web上の情報を調査したところ下記のように NcI シーク + Key Lookup の実行計画が選択された場合にクエリ処理が遅くなるという情報が散見されました。
- https://feedback.azure.com/forums/908035-sql-server/suggestions/36015868-key-lookup-against-columnstore-index-causes-slow-q
- https://joeydantoni.com/2018/11/14/columnstore-indexes-and-key-lookups-the-worst/
今回は処理性能について未調査のため解消済みの可能性もありますが、CCI + NcI を利用する場合は注意しておくと良いのかもしれません。
【追記】下記コメントを頂戴しました。誠にありがとうございます！
コメント欄もぜひご参照ください。
CCI に対しての KeyLookup は、SQL Server 2019 でも、Row Modeとして、列ストアインデックスを行単位処理として実行しているようですので、実行の効率は良くなく、ケースによっては CCI を使用するようにヒントで補正した方が性能が出るケースは変わらなさそうです。
掲載した画像では Key Lookup 時以外の Execution Mode は見切れてますが、見切れている文字は R ではなく B のため Batch Mode での実行となっています。
抽出条件で利用する列に NcI を張り、抽出する列をインデックスに含めるか付加列としている場合
ここでは付加列を利用します。抽出する pk_col と target_col を NcI に付加します。
付加列を追加したインデックスの公式ドキュメントはこちらです。
なお、先ほど作成した NcI の nci は削除しています。
create nonclustered index [nci_include] on [cci_table] (
[filter_col] -- 抽出条件で利用する列
) include (
[pk_col]
[target_col] -- 抽出する対象列を全て付加列に指定
)
この時の実行計画はその 1, その 2 共に以下のとおりで、NcI シークが採用されました。2 のみ掲載します。
テーブルの件数および抽出件数が増えると CCI スキャンになるのかもしれませんが、今回は未検証です。
おわりに
内容のまとめ (tl;dr の再掲)
- CCI のみを設定している場合、CCI スキャン利用
- 抽出条件で利用する列に NcI を張り、抽出する列に NcI を張っていない場合
- 抽出する行数が少量の場合は NcI シークと CCI への Key Lookup 利用
- 抽出する行数が多くなると CCI スキャン利用
- 抽出条件で利用する列に NcI を張り、抽出する列をインデックスに含めるか、付加列としている場合は NcI シーク利用
雑記/感想
CCI 利用テーブルに NcI を設定した時の実行計画について確認した内容を記載しました。
出発点だった "CCI + NcI のテーブルから少量データを抽出する場合、列圧縮がかかってるデータに対しても Key Lookup が動くんだろうか" という疑問の答えは、"実行計画によると動くらしい" でした。
一方で、Key Lookup の場合でも実行計画の概要では "CCI スキャン" と表示されていて、詳細を表示すると "Key Lookup" 表示のため、実は Key Lookup の前段で CCI スキャンして列圧縮からデータを伸長させているのでは？という疑問もあります。
ただ、どう確認すれば良いのかの検討をつけられず一旦未調査に終わりました。
公式のドキュメントを数日漁ったかぎりでは CCI における Key Lookup に関する記述を発見できなかったため、どこかのタイミングで内部動作が公開される機会を待ちたいと思います。
【追記】ドキュメントと CCI における Key Lookup の動作について、下記コメントを頂戴しました。
誠にありがとうございます！
CCI を使用した場合の Keylookup について、公式のドキュメントでは情報はなさそうですね。
NCI のリーフのページには、該当のレコードの KeyHashValue を持っていますので、CCI でセグメントを読み込んでから、セグメントから該当する KeyHashValue を読み取っているように見えますが。
(CCI のセグメント全体を読み込んでから、該当レコードを読み取り)