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

1、基于SQL Server列式存储的原理及应用,演讲人：周欣 2016.12,目录,1.1什么是列存储 在SQL Server里，“页”是数据存储的基本单位。记录数据时，在逻辑上组织为包含行和列的表，在物理上按列数据格式存储的数据，称为列存储。 磁盘的每个“页”仅存储来自单列的值，一行数据存储在多个“页” 内。由于单列的数据类型一致，使得数据压缩算法更加高效，占用物理磁盘空间相对减少。,一、列存储的原理,1.2列存储技术的主要特征 每次对一个列的数据进行分组和存储。SQL Server 查询处理可以利用新的数据布局，并显著改进查询执行时间。 列存储索引由于以下原因而可更快地生成结果： 多数查询并

2、不会涉及表中的所有列，只需读取需要的列。因此，从磁盘读到内存、然后移到处理器缓存中的数据量减少了，同时减少总 I/O 。 列经过了高度压缩。这将减少必须读取和移动的字节数。 高级查询执行技术以简化的方法处理列块（称为“批处理”），从而减少 CPU 使用率。 如果基表为聚集索引，则聚集键中的所有列必须出现在非聚集列存储索引中。 如果在 CREATE INDEX 语句中未列出聚集键中的某列，该列将自动添加到列存储索引中。 使用表分区时，针对分区表的列存储索引必须与基表实现分区对齐。 因此，如果分区列为列存储索引中的一列，则非聚集的列存储索引只能在已分区表上创建。,一、列存储的原理,1.3列存储的过

3、程 为获得高性能和高压缩率，列存储索引首先将表划分为由行构成的组，称为行组，然后按列切分，最后按列压缩每个行组（如下图）。其中部分不足以分组的数据，以传统行存储的形式存储，这就是所谓的“增量存储” （Deltastore），等数据增长到可以分组时再进行分组。 “增量存储”仅用于聚集列存储索引，它是一个聚集索引，可以不断地存储行，并在行数达到某个阈值后，将行移入列存储，从而提高列存储压缩率和性能。为提高压缩率，行组中的行数必须足够大，并且还要足够小，以便从内存中操作中受益。每个行组通常可包含的最大行数是 1,048,576 行，最小行数为102,400 行。在“增量存储“达到最大行数后，它会关闭

4、。 元组移动进程会检查是否有已关闭行组。 在它找到已关闭行组后，会对其进行压缩并且将其存储到列存储中。,一、列存储的原理,目录,2.1列存储索引的种类 列存储索引，SQL Server2012引入非聚集列存储索引； SQL Server2014增加聚集列存储索引；SQL Server2016加强 了聚集列存储索引的功能（使用者可指定索引；增加快照和读提交隔离级别；索引碎片整理；批量模式的增强）,二、列存储的应用范围,2.2为什么使用列存储索引 列存储索引可提供极高的数据压缩级别（通常是传统方法的 7-10 倍），从而明显降低数据仓库存储成本。 此外，对于分析，它们提供的性能比 btree 索引

5、高出一个量级，可对数据仓库最多提高 10 倍查询性能。 它们是数据仓库和分析工作负载的首选数据存储格式。 从 SQL Server 2016 开始，可以使用列存储索引对操作工作负荷执行实时分析。 列存储索引速度较快的原因 列存储来自同一个域的值，并且通常具有相似的值，从而提高了压缩率。 这可以最大程度地减少或消除系统中的 IO 瓶颈，同时大大减少内存占用量。 较高的压缩率通过使用更小的内存中空间提高查询性能。 批处理执行可同时处理多个行，通常可将查询性能提高 2-4 倍。 查询通常仅从表中选择几列，这减少了从物理介质的总 I/O。,二、列存储的应用范围,2.3何时应使用列存储索引 行存储索引最

6、适合用于查找数据、搜索特定值的查询，或者针对较小范围的值执行的查询。 可对事务工作负载使用行存储索引，因为这些工作负载往往需要进行表查找而不是表扫描。 对于扫描大量数据（尤其是大型表中）的分析查询，列存储索引可提高性能。 可对数据仓库和分析工作负载（尤其是对事实数据表）使用列存储索引，因为它们往往需要进行全表扫描而不是表查找。 行存储表创建列存储索引 从 SQL Server 2016开始，你可以在行存储表上创建可更新的非聚集列存储索引。 列存储索引将存储所选列的副本，因此只需要为此准备额外的空间，但是数据的压缩率平均可提高 10 倍。 如果采取这种做法，你可以同时对列存储索引以及行存储索引上

7、的事务运行分析。 当行存储表中的数据更改时，列存储将会更新，因此这两个索引适用于相同的数据。 列存储索引使用行存储索引 从 SQL Server 2016开始，你可以对一个列存储索引使用一个或多个非聚集行存储索引。 这样，便可以针对基础列存储上执行有效的表查找。 其他选项也可供使用。 例如，可以通过在行存储表中使用 UNIQUE 约束来强制主键约束。 由于不唯一的值无法插入行存储表，SQL Server 无法将值插入列存储。 列存储索引不适用的场景 列存储索引同一行的每列在物理磁盘上并不是连续的，并且列存储聚集索引中并没有“主键”的概念，因此并不存在Seek操作，如果大量OLTP类的查询，性能将会出现问题，或者更新操作，尤其是插入、删除操作频繁的场合。,二、列存储的应用范围,目录,3.1如何创建列存储索引 创建列存储索引可以通过两种途径来实现。 使用T-SQL创建列存储索引 - 创建聚集索引（列存储索引） CREATE CLUSTERED COLUMNSTORE INDEX ON - 创建非聚集索引（列存储索引） CREATE NONCLUSTERED COLUMNSTORE INDEX ON () 使用管理工具创