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

SQL Server 列存储系列:

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

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

一,索引的设计思路

在创建索引时,对于一个表:

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

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

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

推荐的设计思路:

  • 使用聚集的列存储索引(把表转换为列存储)来存储事实表和大的维度表,用于提高查询性能和数据压缩性能,提高的性能大概在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

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
posted @ 2020-10-30 10:42  悦光阴  阅读(560)  评论(0编辑  收藏  举报