SQL Server 列存储索引 第一篇：概述

SQL Server 列存储系列：

• SQL Server 列存储索引 第一篇：概述
• SQL Server 列存储索引 第二篇：设计
• SQL Server 列存储索引 第三篇：维护
• SQL Server 列存储索引 第四篇：实时运营数据分析

在2017年，我第一次接触列存储索引（ColumnStore），数据库环境是SQL Server 2012，微软第一次在SQL Server 2012中推广列存储索引，到现在的SQL Server 2017环境，列存储索引发生了很大的变化，举个例子，当时的列存储索引是不能更新的，只能先把删除列存储索引，再更新数据，最后重建列存储索引。虽然使用十分不方便，但是查询性能真的令人惊艳。

现在数据库环境升级到了SQL Server 2017，要重新认识列存储索引了。列存储索引是数据仓库中用于查询和存储大型事实表和维度表的标准实现，它使用基于列的数据存储格式和高于传统行存储格式10倍的压缩率，不仅使得列存储索引得查询性能比传统的面向行的存储高出约10倍，而且存储空间得消耗会减少约10倍。

一，列存储索引的基本概念

列存储索引的实现机制，从顶层设计来说，是先把数据分组，在每一个分组中，再按照列来存储数据。

1，列存储、行存储和增量存储的概念

行存储（Row Store）：是传统的数据存储格式，以行格式来实现。在物理存储上，数据按照行来存储，一行包含所有的数据列。

列存储(Column Store)：是以列格式来存储数据，各个列单独存储。列存储索引实际上以列存储格式来存储“大多数”数据，部分数据以行存储格式存储。在列存储格式中，数据以列为单位来压缩和解压缩，在一行数据中，对于需要的数据列进行解压缩和查询，而对于不需要的列，可以忽略，这样可以快速扫描大型表的整个列。

增量存储（Delta Store）：是以行存储格式存储的聚集索引，列存储索引会把一些数据存储为行存储格式，这些数据被称为deltastore（ 增量存储区），它用于存储在一次插入操作中因为数量太少而无法压缩到列存储中的行，每个增量行组都是通过行版本的聚集B-Tree索引来实现的。

2，行组（Rowgroup）

行组是基础表中的一组数据行，这些数据行作为列存储索引的一个片段，列存储索引对该行组中的所有数据行做为一个整体进行压缩和查询。为了获得高性能和高压缩率，columnstore索引把表切成行组，然后对每个行组中的各个数据列进行压缩。行组中的行数必须足够大，阈值是100万行，以提高压缩率，压缩之后的数据最终以列存储格式进行存储。

在列存储索引中，行组主要分为列存储行组和增量行组，列存储索引同时包含这两种类型的行组，

列存储行组（columnstore rowgroup）是使用列存储压缩的行组，通常情况下，每个列存储的行组的大小总是在100万左右，这个大小不会随着数据的增加而变化，也不会随着数据的更新而发生剧烈的变化。

增量行组（delta rowgroup）是指以聚集的rowstore索引存在的行组，未经压缩，总行数通常在100万以下，用于临时存储更新的数据。

3，列段（column segment）

列段是由列存储行组中的每一列构成的数据，对于每一个列存储行组，每一列都有一个列段；每一个列段都压缩到一起，存储到硬盘上。

二，列存储索引架构概述

聚集列存储索引是整个数据表的物理存储，为了减少列段的碎片并提高性能，columnstore索引可能会将一些数据临时存储到一个称为deltastore的聚集索引中。

deltastore是一个以RowStore格式存储的聚集索引，存储的数据分为两部分：一部分是新增的数据，另一部分是删除的数据。

• 对于新增的数据，该数据逻辑上存在于表中，但是，实际上，并不在列存储中，而是以rowstore格式存储在deltastore中。
• 对于被删除的数据，逻辑上被标记为删除，但是，实际上，并没有从列存储中删除，也就是说物理上没有删除，deltastore存储的是被删除的数据行的ID列表。

deltastore用于进行增量存储，该操作在后台进行，对程序员是透明的。SQL Server基于性能的考虑，不会因为删除或更新少数几行数据，而去更新columnstore，但是会实时更新deltastore。为了返回正确的查询结果，聚集的列存储索引将列存储和deltastore的查询结果组合在一起，即把deltastore新增的行添加到结果集中，把deltastore中删除的行从结果集中删除，从而得到一个正确的结果。

1，增量行组（Delta Rowgroup）

增量行组是仅与列存储索引一起使用的聚集B树索引，它存储数据行，直到行数量达到阈值（1,048,576行），然后把数据移入列存储中，从而提高了列存储的压缩和性能。

当增量行组达到最大行数时，它将从“打开”状态转换为“关闭”状态。一个名为元组移动器（tuple-mover）的后台进程会定时检查封闭行组。如果该进程找到一个封闭的行组，那么它将压缩增量行组，并将其作为COMPRESSED行组存储到列存储中。

在压缩增量行组后，现有的增量行组将转换为TOMBSTONE状态，然后在无引用的情况下由元组移动器删除。

2，增量存储（deltastore）

列存储索引的每个行组都可以具有一个增量行组，所有的增量行组统称为增量存储。

在大批量加载数据的期间，大多数行直接进入列存储，而无需通过增量存储。在批量加载结束时，或者执行INSERt操作时，由于插入行的数量太少，无法满足列存储的最小大小（102,400行），这些少量的数据行将进入增量存储，而不是列存储。 对于行数少于102,400的小型批量负载，所有行均直接进入deltastore。

3，非聚集列存储索引

非集群列存储索引和集群列存储索引的功能相同，区别在于，非聚集索引是在行存储表上创建的辅助索引，而聚集列存储索引是整个表的主存储。非聚集索引包含基础表中部分或全部行和列的副本，索引被定义为表的一列或多列，并具有过滤行的可选条件。

三，列存储索引的更新

加载（load）或插入（insert）的少量数据会直接进入deltastore，而不会进入到columnstore中。只有当deltastore中的数据行超过102,400行时，后台进程tuple-mover才会把数据压缩、进而更新到columnstore中。

1，少量的数据加载和插入会直接进入deltastore

列存储索引不是实时更新的，它通过缓冲区deltastore来临时存储数据，这样做的目的是避免columnstore的频繁更新，以提高性能。首先，只有当数据的更新数量超过阈值102,400行时，才会触发列存储索引的更新。

列存储索引一次将至少102,400行压缩到列存储索引中，从而提高了列存储的压缩率和查询性能。要以批量方式压缩数据行，columnstore索引会累积少量加载（bulk）和插入（insert），并把数据插入到deltastore中。增量存储操作在后台进行，为了返回正确的查询结果，聚集列存储索引将列存储和增量存储中的查询结果组合在一起。

当行到达时，它们会进入增量存储区：

• 插入INSERT INTO ... VALUES语句。
• 批量加载结束时，它们的数量少于102,400行。
• 更新，每次更新都实现为删除和插入。

增量存储区还存储已删除行的ID列表，这些行已被标记为已删除但尚未从列存储中实际删除。

2，把deltastore中数据更新到columnstore中

每一个delta rowgroup最多存储1,048,576行，当delta rowgroup存储的数据行达到该阈值时，delta rowgroup的状态由OPEN转换为CLOSED，一个名为元组移动器（tuple-mover）的后台进程检查封闭行组。如果该进程找到一个封闭的行组，则将压缩该行组并把其存储到列存储中。压缩增量行组后，现有的增量行组将转换为TOMBSTONE状态，随后在无引用的情况下由元组移动器删除，并将新的压缩行组标记为COMPRESSED状态。

您可以使用ALTER INDEX重建或重新组织索引，以将增量行组强制进入列存储。请注意，如果在压缩过程中存在内存压力，则列存储索引可能会减少压缩的行组中的行数。

3，列存储索引的更新

对数据执行Insert、Delete和Update，列存储索引是如何更新的？如下图所示：

整体来看，NCCI的更新操作会比常规的BTree索引的维护成本更高。

对于Insert操作：插入NCCI的行将直接进入到增量行组（delta rowgroup），该行组在物理上组织为rowstore的聚集索引。为了使插入操作更有效率，增量行组中的行以未经压缩的方式存储。在SQL Server 2014中，增量行组进行PAGE压缩。 在SQL Server 2016中，增量行组不会压缩。

对于Delete操作：在执行delete命令后，首先把该行从rowstore表中删除。其次从NCCI中删除该行，由于NCCI确实没有任何关键列，需要以某种方式有效地在NCCI中找到该行。

我们如何找到它？诀窍是使用聚集索引键列，或包含RID的堆（即没有聚集索引的表）。这些列自动包含在NCCI中，即使没有选择它们作为NCCI定义的一部分，列存储索引使用这些列把每个增量行组组织为聚簇索引。

要查找要删除的行

• 首先，SQL Server优先在每个增量行组（deltastore）中查找该行。如果找到该行，则把该行从增量行组中删除。
• 如果找不到该行，则该行存在于压缩的行组中。由于没有有效的方法来搜索压缩的行组，因此把行标识符插入到称为“删除缓冲区”（delete buffer）的内部Btree中。该“删除缓冲区”会定期合并到“删除位图”（delete bitmap）中。当访问NCCI进行查询时，这些行将被自动过滤。如果有大量的增量行组，则Delete操作可能会变得非常缓慢。

为了最大程度地减少NCCI的增量行组的数量，SQL Server以指数方式增长增量行组。例如，当行数达到100万时，第一个增量行组将关闭。第二个增量行组达到100万个标记，但是第一个增量行组尚未压缩，我们在内部将阈值提高到200万个。第三个增量行组以400万个封闭，第二个以800万个为上限，最大为3200万个。即使增量行组可能有200万行，但在压缩时将其压缩为100万或更少的大块，也就是说维持每个压缩的行组的行数在100万左右。

还要注意的另一点是，当从压缩的行组中删除一行时，它将导致索引碎片。 SQL Server 2016提供了一个在线操作来删除已删除的行。

对于Update操作：对NCCI进行更新等价于同时执行Delete和Insert操作，即使在增量行组（delta rowgroup）中找到数据行，也不会就地更新（in-place update）。

四，执行模式

SQL Server数据库引擎使用两种不同的处理模式来处理Transact-SQL语句：

• 行模式执行（Row mode execution）
• 批模式执行（Batch mode execution）

这两个执行模式，分别用于查询行存储数据和列存储数据，适用于不同的查询类型，各有自己的优势和劣势。

1，行模式执行

行模式执行是与传统数据表一起使用的查询处理模式，其中数据以行格式存储。SQL Server 引擎读取所需行或索引的所有列，SQL Server从读取的每一行中，检索SELECT语句，JOIN谓词或筛选器谓词所引用的结果集所需的列。这种执行模式，会把所需行的所有列都读取出来，即使有些列是不需要的，这种执行模式特别对于查询少量数据特别有效，适用于少量数据的值查找（seek）。

2，批模式执行

批处理模式执行用于处理列存储的数据，同时处理多行，因此称为批处理。批处理中的每一列都作为矢量存储在单独的内存区域中，批处理模式还使用针对多核CPU优化的算法，并提高了现代硬件上的内存吞吐量。

批处理模式执行与列存储存储格式紧密集成并进行了优化，批处理模式在可能的情况下对压缩数据进行操作，不需要对数据进行解压缩就可以处理数据，这与行处理模式是截然不同的，相比行处理模式必须解压数据才能对数据进行处理，批处理模式能够获得更好的并行性和更快的查询性能。

当以批处理方式执行查询并访问列存储索引中的数据时，SQL Server 引擎会在列段中同时读取多行，但是SQL Server仅读取结果所需的列，这些列由SELECT语句，JOIN谓词或过滤器谓词引用，对于数据行中的其他列，完全不需要读取，这显著地提高查询的性能。

五，列存储索引的优势

列存储索引能够实现更高的数据压缩率，通常是10倍于普通的数据存储，显著地降低了数据仓库的存储成本。对于数据分析，列存储索引提供的查询性能比B-Tree索引快一个数量级。鉴于这两个原因，列存储索引成为数据仓库中用于数据存储和数据分析的首选方案。

总体来说，对列存储索引进行查询，能够获得高效率的原因主要是：

• 列存储的值来自相同的值域或范畴，通常具有很多高度相似的值，这使得数据压缩的效率非常高。进而减少了数据的存储和读取，最小化或消除了系统的I / O瓶颈，并显着减少了内存占用。
• 高压缩率通过使用较小的内存占用空间来提高查询性能，由于SQL Server可以在内存中执行更多查询和数据操作，因此查询性能可以提高。
• 批处理执行模式在同一时刻会处理多行数据，通常将查询性能提高2到4倍。
• 查询语句通常仅从表中选择少量的几列，相比行存储，从表中读取所有列，列存储的格式减少了硬盘的总I / O。