SQL Server 列存储索引第二篇：设计

列存储索引可以是聚集的，也可以是非聚集的，用户可以在表上创建聚集的列存储索引（Clustered Columnstore Index）或非聚集的列存储索引（Nonclustered Columnstore Index）。由于聚集索引实际上是表的物理存储，因此，表上只能创建一个聚集索引，该聚集索引要么是聚集的列存储索引，要么是聚集的行存储索引。由于非聚集的索引（列存储索引和行存储索引），是在表的物理存储空间之外额外创建的数据结构，因此一个表可以创建多个非聚集的索引。

由于列存储索引相比普通的B-Tree索引，提高约10被的压缩率和查询性能，因此，对于数据仓库的大型数据表，都可以创建列存储索引。而列存储索引实际上是由两部分构成的：列存储区（columnstore）和增量存储区（deltastore），并且会产生索引的碎片，在创建列存储索引时，需要根据表的更新频率和查询的需求（是值查找，还是分析查询）来为表设计合适的索引。

一，索引的设计思路

在创建索引时，对于一个表：

如果已经创建了聚集的列存储索引，那么该表上不能再创建非聚集的列存储索引，但是可以创建非聚集的行存储索引。
如果已经创建的聚集的行存储索引，那么该表上可以创建一个或多个非聚集的列存储索引，也可以创建一个或多个非聚集的行存储索引。

列存储索引特别适合进行大量数据的分析查询，而行存储索引适合用于少量数据值的查找。

聚集的列存储索引是整个表的物理存储，通常把聚集的列存储索引称作列存储表，而非聚集的列存储索引是在表的物理存储之外额外创建的数据结构，非聚集的索引包含基础表中部分或全部的数据行，也可以只包含部分列，即，列存储索引被定义为表的一列或多列，并具有过滤行的可选条件。

推荐的设计思路：

使用聚集的列存储索引（把表转换为列存储）来存储事实表和大的维度表，用于提高查询性能和数据压缩性能，提高的性能大概在10倍左右。
在行存储表上，使用非聚集的列存储索引对数据进行分析查询。

二，把列存储和行存储结合到一起

从SQL Server 2016 (13.x)版本开始，列存储索引和行存储索引可以结合在一起，利用这两种类型的索引的优点，提高查询性能、并减低存储消耗。

用户可以在rowstore表上创建一个或多个可更新的非聚集列存储索引（updatable nonclustered columnstore index），该索引存储所选列的副本，因此需要额外的空间来存储此数据，但是所选数据平均被压缩10倍。用户可以在列存储索引上运行分析，同时在行存储索引上运行事务。当行存储表中的数据更改时，列存储将更新，因此两个索引都针对相同的数据工作。

用户可以在列存储表上创建一个或多个非聚集的行存储索引，并在基础列存储上执行有效的表查找。

三，设计方案

方案1：创建聚集的列存储索引

表通常是行存储的，为表创建一个列存储索引，就把表转换为列存储格式。聚集的列存储索引不仅仅是一个索引，实际上，聚集的列存储索引就是数据表的物理存储，能够提高10倍的压缩率和数据查询性能。

当表满足以下条件，考虑创建聚集的列存储索引：

对于分区表来说，每个分区至少100万行数据，列存储索引在每个分区中都有行组，如果表太小而无法在每个分区中填充行组，则无法获得列存储压缩和查询性能的好处。
查询主要对值范围执行分析，例如，要查找列的平均值，查询需要扫描所有列的值，然后，通过将它们求和以确定平均值来汇总这些值。
大多数插入的数据量是海量的，而更新和删除操作最少。

相反，如果每个分区少于100万行数据，或者表上的更新和删除操作非常多（更新操作会导致碎片），或者含有LOB字段，即包含 varchar(max), nvarchar(max) 和 varbinary(max)数据类型，那么不要创建聚集的列存储索引。

方案2：在聚集的列存储索引上创建非聚集的行存储索引，用于少量值得查找

从SQL Server 2016（13.x）开始，用户可以在聚集得列存储索引上创建非聚集得B-Tree索引，当列存储索引发生更改时，非聚集得B-Tree索引也会更新。通过使用辅助的B树索引，用户可以有效地搜索特定行，而无需扫描所有行。

方案3：使用非聚集的列存储索引进行实时分析

从SQL Server 2016（13.x）开始，用户可以在行存储表（Disk-Based表或内存内存优化表）上创建非聚集的列存储索引，使得用户可以在事务表上进行实时分析。在基础表上进行事务处理时，数据会更新到列存储索引上，用户可以在列存储索引上进行分析性的查询。由于一个表同时管理两种类型的索引，因此，行存储索引和列存储索引都可以实时进行更新。由于列存储索引的数据压缩性能比行存储索引高约10倍，因此只需要少量的额外存储。例如，如果压缩的行存储表占用20 GB，则列存储索引可能需要额外的2 GB。所需的额外空间还取决于非聚集列存储索引中的列数。

四，分区对列存储的影响

可以对分区表创建列存储索引，对于每一个分区，都有一个或多个行组，可以认为对每个分区单独创建列存储索引。由于列存储索引对数据量有一个显式的要求，100万行，如果每个分区没有一百万行，那么大多数数据行可能会转到增量存储，而在增量存储中它们将无法获得列存储压缩的性能优势。除非你有足够大的数据量，否则，为列存储索引使用更少的分区。

举个例子：

将100万行加载到一个分区或未分区的表中，您将获得一个包含100万行的压缩行组，这对于高数据压缩和快速查询性能非常有用。
将100万行平均加载到10个分区中，每个分区获得10万行，这比列存储压缩的最低阈值还小，这导致列存储索引可能有10个增量行组，每个组有10万行。

虽然有一些方法可以把增量行组强制进入列存储，但是，如果这些是columnstore索引中仅有的行，则压缩的行组将太小而无法获得最佳的压缩和查询性能。

五，选择合适的数据压缩算法

列存储索引为提供了两种数据压缩的算法：列存储压缩（columnstore compression）和存档压缩（archive compression）。用户可以在创建索引时选择压缩选项，稍后使用ALTER INDEX … REBUILD对其进行更改。

1，使用列存储压缩以获得最佳查询性能
与行存储索引相比，列存储压缩通常可实现10倍更好的压缩率。它是列存储索引的标准压缩方法，可实现快速查询性能。

2，使用存档压缩以获得最佳数据压缩
当查询性能不太重要时，归档压缩旨在最大程度地压缩数据，与列存储压缩相比，它实现了更高的数据压缩率，但代价不菲。压缩和解压缩数据需要更长的时间，因此不适合快速查询性能。

参考文档：

Columnstore indexes – Design guidance

Tags: SQL Server SQL Server 管理列存储索引