小さなテーブルのクラスター化された列ストアインデックス

Nov 23 2020

クラスター化された列ストアのインデックス付きテーブルは、一般に大きなテーブルに役立ちます。理想的には数百万行です。また、そのようなテーブルで使用可能な列のサブセットのみを選択するクエリでも役立ちます。

これらの2つの「ルール」/ベストプラクティスに違反するとどうなりますか？

クラスター化された列にインデックス付きのテーブルを格納するのと同じように、最大で数千または数十万の行しか格納されません。
そして、すべての列が必要なクラスター化された列ストアテーブルに対してクエリを実行します。

私のテストでは、行に格納されたクラスター化インデックステーブルと比較してパフォーマンスの低下は見られません。私たちの場合、これは素晴らしいことです。

これらの2つのルールに違反する「長期的な」影響はありますか？または、まだ現れていない隠れた落とし穴はありますか？

コンテキストが必要な理由：さまざまなベンダーデータベースの多くのインスタンスに使用されるデータベースモデルを設計しました。スキーマはすべてのデータベースで同じままですが、ベンダーが異なればデータ量も異なります。したがって、テーブルに少量のデータ（<1 000 000）が含まれる小さなベンダーはほとんどありません。行ストアモデルと列ストアモデルの2つの異なるデータベースを維持することはできません。

回答

3 J.D. Nov 24 2020 at 00:20

@YunusUYANIKが、片側だけに対応するスキーマを設計することの潜在的な欠点を指摘するために、両方のシナリオに適切に対応するテーブルに行ストアと列ストアの両方のインデックスを作成してみませんか？確かに、両方の方法で同じフィールドにインデックスを付けることになるかもしれませんが、主な欠点は、ストレージスペースの使用が増えることです。これは、通常、パフォーマンスを計画する際の懸念事項ではありません。

スキーマと各ベンダーのテーブル内のデータ量に依存するため、ベンダーの述語に基づいて、さまざまな量のデータに対して適切なクエリでインデックスの設計が使用されていることを確認する必要があります。。最悪の場合、インデックスヒントを使用しなければならないこともありますが、両方のタイプのインデックスを正しく設計すれば、そうなる可能性は低いと思います。

4 YunusUYANIK Nov 23 2020 at 22:16

列ストアインデックスは、データサイズの圧縮に大きな利点があります。列ストアインデックスの一般的な目的は、圧縮されているため、大量のデータをすばやく読み取ることです。

CCIはColumnstoreClustered Index、ClusteredはClusteredIndexです

列ストアインデックスは、データサイズを4MBから2MBに圧縮します。

2つの表と3つの部分でパフォーマンスを確認できます。

最初のものは最小限の`SELECT`操作です：

SELECT * FROM Users_CCI WITH(INDEX=CCI_Users) WHERE Id=12333

SELECT * FROM Users_Clustered WHERE Id=12333

結果：Columnstore Scanここに間違った見積もりがあります。さらに、論理的な読み取りの違い。それはあなたにとって重要ではないと言うことができますが、最小限のSELECTクエリを使用する場合は、おそらく何千回も使用します。そして、それは全体的なパフォーマンスに影響を与えます。

2つ目は、最小限の`UPDATE`操作です。

UPDATE Users_CCI SET Age=10 WHERE  Id=2

UPDATE Users_Clustered SET Age=10 WHERE  Id=2

結果：ご覧のとおり、読み取り、CPU、時間の違いがあります。

3番目は`REBUILD`操作です：

USE [StackOverflow2013]
GO
ALTER INDEX [CCI_Users] ON [dbo].[Users_CCI] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)
GO
USE [StackOverflow2013]
GO
ALTER INDEX [PK_Users_Clustered_Id] ON [dbo].[Users_Clustered] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

UPDATE Users_CCI SET Age=10 
UPDATE Users_Clustered SET Age=10

インデックスの断片化のためにすべての行を再構築して更新すると、クラスター化インデックスよりも断片化されたクラスター化列ストアインデックスが表示されます。そして、私は示しませんでしたが、Clustered Columnstore Index再構築プロセスは、より多くのトランザクションログを生成しますClustered Index。

小さなテーブルのクラスター化された列ストアインデックス

回答

最初のものは最小限のSELECT操作です：

2つ目は、最小限のUPDATE操作です。

3番目はREBUILD操作です：

最初のものは最小限の`SELECT`操作です：

2つ目は、最小限の`UPDATE`操作です。

3番目は`REBUILD`操作です：