SQL Server 2014聚集列存储索引

 转载请声明引用和原文博客(http://www.cnblogs.com/wenBlog

简介

  以前已经写过两篇介绍列存储索引的篇章,可是只有非聚集列存储索引,前日再来简单介绍一下见面的列存储索引,也就是可更新列存储索引。在SQL
Server
2012中首次引入了依照列存储数据格式的蕴藏格局。叫做“列存储索引”。前一篇我早已比较了行存储索引与非聚集的列存储索引(http://www.cnblogs.com/wenBlog/p/5682024.html)。其中对于在小表的指定值或者小范围的查询来讲,尤其针对事务性的负载行存储是很有分寸的。不过对于分析性负载像数据仓库和BI,在查询准将会对大气数目开展全扫描,例如事实表,那时候列存储索引就是更好地挑选。

列存储索引结构

  在列存储索引中,数据按照独立列组织到手拉手形成索引结构。每列都多少都放在被中度减弱的数码汇总,叫做数据段。那个数据段只包括该列的值,对于大型表它分到四个数据段中,每个数据段中只含有100万行数据,那就叫做行组、数据段由一个要么多少个数据页组成。数据将在内存和硬盘上以数据段的款型传输。

  那种索引提升了数据仓库的查询效用。那种经过减弱得到数量格式要比B-Tree结构的压缩率高7倍多。同时由于列存储索引使用了批处理方式实施,数据处理也是批处理的,较少了CPU的施用。列存储索引强化了搜索数据的快慢,与行存储分裂的是并非查询所有列。因为那么些缘故,更少数据被读取到内存中,再到电脑缓存处理。相关的那个要素都会压缩硬盘IO,升高总体查询的习性。

  在2014中列存储索引有以下限制:

                  最多辅助1024列在您的目录中;

                  列存储索引不可能被定义为唯一性索引;

                  不可能创设视图;

                  不能包括稀疏列;

                  不可能应用ALTER
INDEX来修改索引,只好drop然后再一次创立;

                  不可以动用INCLUDE关键字。

                  不可能排连串;

                  不可能选用FILESTREAM属性。

                  当然还有一对数据类型不可能包括在列存储索引中(binary
, varbinary , ntext , text, , image, varchar(max) , nvarchar(max),
uniqueidentifier, rowversion , sql_variant,精度大于18 的decimal,CLR
和xml等)   

 

一边,对于索引列900字节的限量也不适用与列存储索引。

在SQL Server2012
中,只可以创设非聚集列存储索引,并且不能更新。为了革新您必须删除索引,然后开展插队、更新或者去除的操作后在重建索引。

在2014中列存储索引得到了不小的升官,比如消除了只读限制。增加了聚集列存储索引,列存储索引作为了表的储存格局,存储表的数目。

正如聚集和非聚集列存储索引

区别

聚集列存储索引

非聚集列存储索引

索引列 需要指定列上创建 所有列都包含在内
 存储  额外增加百分之10的空间作为索引  压缩十倍的数据量,如果表之前是页压缩,则可以压缩5倍左右
 更新  是  否
 排序  在创建之前进行排序  否

 

 

列存储索引的布局图:

图片 1

如图增量存储部分我们誉为deltastore,用于存储不够最小行组大小的数额。流程就是将行数据提取成列数据,然后开展削减存储,多余的有的放到deltastore中。

聚集索引插入、删除和翻新已毕逻辑:

插入新行的时候,值被贮存在deltastore中,直到达到最小rowgroup(行组)大时辰,然后压缩并活动到列存储数据段中。

去除数据时,行将被去除从deltastore存储中,不过在列存储索引数据段中只是被标记为除去,除非重建后才会被真正删除。

更新的时候,在deltastore存储中行数据被删除,然后在列存储数据段中被标记为除去,新的列别插入到deltastore中。

末尾当重建索引的时。SQLServer将会删除所有标记为除去的数据段,数据存储在deltastore中的将与数据段中的数据统一,然后进行压缩。

 

 

上边大家来显示下怎样从列存储索引中取得属性:

 

咱俩先是创设一个真相表在数据库中脚本如下:

 1 USE SQLShackDemo
 2 
 3 GO
 4 --创建表
 5 CREATE TABLE [dbo].[FactFinance](
 6 
 7 [FinanceKey] [int] NOT NULL,
 8 
 9 [DateKey] [int] NOT NULL,
10 
11 [OrganizationKey] [int] NOT NULL,
12 
13 [DepartmentGroupKey] [int] NOT NULL,
14 
15 [ScenarioKey] [int] NOT NULL,
16 
17 [AccountKey] [int] NOT NULL,
18 
19 [Amount] [float] NOT NULL,
20 
21 [Date] [datetime] NULL
22 
23 ) ON [PRIMARY]
24 
25 GO
26 
27 --创建聚集索引:
28 
29 CREATE CLUSTERED INDEX [IX_FactFinance_FinanceKey_DateKey] ON [dbo].[FactFinance] ( [FinanceKey],[DateKey])
30  GO
31 
32 
33 --查询表:
34 
35 SELECT [FinanceKey]
36 
37 ,[DateKey]
38 
39 ,[OrganizationKey]
40 
41 ,[DepartmentGroupKey]
42 
43 FROM [FactFinance]

 

图片 2

 

让大家检查下聚集索引围观操作符,Estimated I/O Cost(估计IO花销)
的值为0.183866,Estimated CPU
Cost
(猜测CPU花销)为0.0435069,为了相比较列索引的值,大家先记住:

图片 3

 

近期大家成立列存储索引在非聚集索引:

 

 

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_FactFinance_FinanceKey_DateKey_OrganizationKey_DepartmentGroupKey]

ON [FactFinance]

([FinanceKey],[DateKey],[OrganizationKey],[DepartmentGroupKey])

GO
SELECT [FinanceKey] ,[DateKey] ,[OrganizationKey] ,[DepartmentGroupKey] FROM [FactFinance]

 

 

图片 4

 

以此列存储索引围观操作符如下所示:

图片 5

 

如上所示,Estimated I/O
Cost从0.183866下挫到0.0112731,这是因为SQL引擎只检索须要的列,节省了IO和内存资源。Estimated
CPU的年华没有变动。

 

IO强化与事先比较是显然的,大家也足以比较八个查询,启用I/O
statistics,检查IO的hits 表现如下:

 

SET STATISTICS IO ON 
GO
 SELECT [FinanceKey] ,[DateKey] ,[OrganizationKey] ,[DepartmentGroupKey] FROM [FactFinance] with (index (IX_FactFinance_FinanceKey_DateKey)) 
GO 
SELECT [FinanceKey] ,[DateKey] ,[OrganizationKey] ,[DepartmentGroupKey] FROM [FactFinance] with (index(IX_FactFinance_FinanceKey_DateKey_OrganizationKey_DepartmentGroupKey))

 

正如所示,相比执行安顿,使用列存储索引的要比行索引的好四倍,那么愿意一下拍卖大数量时的10倍质量:

 

图片 6

当相比较逻辑读时你也能发现貌似的结果。分明那一个逻辑读也是四倍+关系。

图片 7

那么大家可以依照下图概括一下观念的行索引与列存储所以的常见差异:

图片 8

列存储索引的创立

也可以利用SSMS创制索引: Indexes -> New Index
->Non-Clustered Columnstore Index 如下:

图片 9

 

与非聚集索引创设类似,选拔列,然后那一个列没有排序也无法应用Include选项:

图片 10

 

下图中本人在SQL Server2014 公司版中,创制聚集索引:

图片 11

 

急需注意的是如若在表上已经有此外索引,尝试成立聚集列存储索引就会冒出错误,正如大家后面说的,同一个表中不可以或者其余索引:

图片 12

不用选拔列,所有数据都包括在内了:

图片 13

几个好的利用场景:

比方您有重型的事实表并且存在询问难题的,或者SSAS存在任何质量难题的,列存储是一个科学的方案。一下两种状态是通过测试的可比好的应用场景:

  • 对此高频率响应的报表/仪表板,尤其分析当品质表现不好的时候,会有很科学的性质。
  • 对于ETL的历程来讲,源数据的列存储索引将会大幅度进步质量,倘使数额丰盛大依旧足以考虑暂时创办列存储索引。然后实施ETL。

 

总结:

列存储索引是一个运用SQL
Server品质优化的方案,通过减弱IO消耗,越发对数据仓库和BI查询都是由明显品质提高。它经过排序数据作为列存储,然后压缩,并利用批处理来拍卖数量。当然,必必要力保使用列存储索引的施用带来了好处,而不会唤起其余品质难点才能接纳。比如必要小心运用的硬件环境和多少,倘若没有join、过滤、或者聚合导出巨大的数据量没有丰盛的内存则将被暂时放入硬盘进行switch
off,从而挑起查询质量下跌。尽量在利用之前在测试环境中测试是或不是适合采纳,同时还要关怀其他环节是还是不是受影响。

补充,在2016中伸张的多少个自我以为不错新的feature:

按照聚集列存储索引的 B 树索引;

基于内存优化表的列存储索引;

CREATE TABLE 和 ALTER TABLE 中的列存储索引的滑坡延迟选项;

单线程查询的批处理实施。

 

相关文章