SQL Server列式存储的原理及应用

1、基于SQL SERVER列式存储的原理及应用演讲人：周欣2016.12目录一、列存储的原理 什么是列存储 列存储技术的主要特征 列存储的过程二、列存储的应用范围 列存储索引的种类 为什么使用列存储 何时应使用列存储三、列存储的应用 如何创建列存储索引 与行存储的性能对比 1.1什么是列存储在SQL Server里，“页”是数据存储的基本单位。记录数据时，在逻辑上组织为包含行和列的表，在物理上按列数据格式存储的数据，称为列存储。磁盘的每个“页”仅存储来自单列的值，一行数据存储在多个“页” 内。由于单列的数据类型一致，使得数据压缩算法更加高效，占用物理磁盘空间相对减少。一、列存储的原理 1.2列存

2、储技术的主要特征每次对一个列的数据进行分组和存储。SQL Server 查询处理可以利用新的数据布局，并显著改进查询执行时间。列存储索引由于以下原因而可更快地生成结果： 多数查询并不会涉及表中的所有列，只需读取需要的列。因此，从磁盘读到内存、然后移到处理器缓存中的数据量减少了，同时减少总 I/O 。列经过了高度压缩。这将减少必须读取和移动的字节数。高级查询执行技术以简化的方法处理列块（称为“批处理”），从而减少 CPU 使用率。如果基表为聚集索引，则聚集键中的所有列必须出现在非聚集列存储索引中。 如果在 CREATE INDEX 语句中未列出聚集键中的某列，该列将自动添加到列存储索引中。使用表

3、分区时，针对分区表的列存储索引必须与基表实现分区对齐。 因此，如果分区列为列存储索引中的一列，则非聚集的列存储索引只能在已分区表上创建。一、列存储的原理 1.3列存储的过程为获得高性能和高压缩率，列存储索引首先将表划分为由行构成的组，称为行组，然后按列切分，最后按列压缩每个行组（如下图）。其中部分不足以分组的数据，以传统行存储的形式存储，这就是所谓的“增量存储” （Deltastore），等数据增长到可以分组时再进行分组。“增量存储”仅用于聚集列存储索引，它是一个聚集索引，可以不断地存储行，并在行数达到某个阈值后，将行移入列存储，从而提高列存储压缩率和性能。为提高压缩率，行组中的行数必须足够大

4、，并且还要足够小，以便从内存中操作中受益。每个行组通常可包含的最大行数是 1,048,576 行，最小行数为102,400 行。在“增量存储“达到最大行数后，它会关闭。 元组移动进程会检查是否有已关闭行组。 在它找到已关闭行组后，会对其进行压缩并且将其存储到列存储中。一、列存储的原理目录一、列存储的原理 什么是列存储 列存储技术的主要特征 列存储的过程二、列存储的应用范围 列存储索引的种类 为什么使用列存储 何时应使用列存储三、列存储的应用 如何创建列存储索引 与行存储的性能对比 2.1列存储索引的种类列存储索引，SQL Server2012引入非聚集列存储索引； SQL Server2014

5、增加聚集列存储索引；SQL Server2016加强了聚集列存储索引的功能（使用者可指定索引；增加快照和读提交隔离级别；索引碎片整理；批量模式的增强）二、列存储的应用范围非聚集列存储索引聚集列存储索引创建条件对常用列建立索引不能基于视图或索引视图创建不能使用 INCLUDE 关键字创建（没有键列。所有列均为包含列）列数限制包含的列数不能超过 1024包含的列数不能超过 1024索引的存储需要附加的存储空间以存储索引中列的副本索引本身包含数据更新索引只读索引，不支持插入、更新和删除操作。仅支持重新生成索引或者切入和切出分区进行更新可更新索引组合非唯一索引，可以与表中的其他索引结合使用是表的唯一索

6、引。不能与任何其他索引组合使用不能作为主键或外键数据压缩可配置为使用列存储或列存储存档压缩可配置为使用列存储或列存储存档压缩排序存储不以排序方式物理存储列不以排序方式物理存储列 2.2为什么使用列存储索引列存储索引可提供极高的数据压缩级别（通常是传统方法的 7-10 倍），从而明显降低数据仓库存储成本。 此外，对于分析，它们提供的性能比 btree 索引高出一个量级，可对数据仓库最多提高 10 倍查询性能。 它们是数据仓库和分析工作负载的首选数据存储格式。 从 SQL Server 2016 开始，可以使用列存储索引对操作工作负荷执行实时分析。列存储索引速度较快的原因列存储来自同一个域的值，并

7、且通常具有相似的值，从而提高了压缩率。 这可以最大程度地减少或消除系统中的 IO 瓶颈，同时大大减少内存占用量。较高的压缩率通过使用更小的内存中空间提高查询性能。批处理执行可同时处理多个行，通常可将查询性能提高 2-4 倍。查询通常仅从表中选择几列，这减少了从物理介质的总 I/O。二、列存储的应用范围 2.3何时应使用列存储索引行存储索引最适合用于查找数据、搜索特定值的查询，或者针对较小范围的值执行的查询。 可对事务工作负载使用行存储索引，因为这些工作负载往往需要进行表查找而不是表扫描。对于扫描大量数据（尤其是大型表中）的分析查询，列存储索引可提高性能。 可对数据仓库和分析工作负载（尤其是对事

8、实数据表）使用列存储索引，因为它们往往需要进行全表扫描而不是表查找。行存储表创建列存储索引从 SQL Server 2016开始，你可以在行存储表上创建可更新的非聚集列存储索引。 列存储索引将存储所选列的副本，因此只需要为此准备额外的空间，但是数据的压缩率平均可提高 10 倍。 如果采取这种做法，你可以同时对列存储索引以及行存储索引上的事务运行分析。 当行存储表中的数据更改时，列存储将会更新，因此这两个索引适用于相同的数据。列存储索引使用行存储索引从 SQL Server 2016开始，你可以对一个列存储索引使用一个或多个非聚集行存储索引。 这样，便可以针对基础列存储上执行有效的表查找。 其他

9、选项也可供使用。 例如，可以通过在行存储表中使用 UNIQUE 约束来强制主键约束。 由于不唯一的值无法插入行存储表，SQL Server 无法将值插入列存储。列存储索引不适用的场景列存储索引同一行的每列在物理磁盘上并不是连续的，并且列存储聚集索引中并没有“主键”的概念，因此并不存在Seek操作，如果大量OLTP类的查询，性能将会出现问题，或者更新操作，尤其是插入、删除操作频繁的场合。二、列存储的应用范围目录一、列存储的原理 什么是列存储 列存储技术的主要特征 列存储的过程二、列存储的应用范围 列存储索引的种类 为什么使用列存储 何时应使用列存储三、列存储的应用 如何创建列存储索引 与行存储的性能对比 3.1如何创建列存储索引创建列存储索引可以通过两种途径来实现。使用T-SQL创建列存储索引- 创建聚集索引（列存储索引）CREATE CLUSTERED COLUMNSTORE INDEX ON - 创建非聚集索引（列存储索引）CREATE NONCLUS