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倍左右
 更新  是  否
 排序  在创建之前进行排序  否

 

 

列存储索引的组织图:

一旦图增量存储部分我们叫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]

 

 

给我们检查下聚集索引围观操作符,Estimated I/O Cost(估计IO花销)
的值为0.183866,Estimated CPU
Cost
(估计CPU花销)为0.0435069,为了比较列索引的值,我们事先记住:

 

今昔咱们创建列存储索引在非聚集索引:

 

 

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_FactFinance_FinanceKey_DateKey_OrganizationKey_DepartmentGroupKey]

ON [FactFinance]

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

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

 

 

 

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

 

倘若齐所示,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加倍性能:

 

当于逻辑读时你呢克发现貌似的结果。明显是逻辑读也是四加倍+关系。

那么我们得以依据下图概括一下民俗的行索引与列存储所以的一般性区别:

列存储索引的创造

啊会利用SSMS创建索引: Indexes -> New Index
->Non-Clustered Columnstore Index 如下:

 

暨非聚集索引创建类似,选择列,然后这些列没有排序也未能够采用Include选项:

 

生图备受自我当SQL Server2014 企业版备受,创建聚集索引:

 

得小心的凡要是当表上已经发另外索引,SQL Server尝试创建聚集列存储索引就见面产出错误,正如我们事先说之,同一个表中不能够要其他索引:

决不选择列,所有数据都含有在内了:

差一点单好之采用场景:

比方你有大型的事实表并且是询问问题的,或者SSAS存在任何性能问题之,列存储是一个对的方案。一下点儿栽状态是透过测试的可比好的使用场景:

  • 于高频率响应的表/仪表板,尤其分析当性能表现不出彩之时光,会生出甚科学的性能。
  • 对于ETL的长河来讲,源数据的列存储索引将会晤大增强性,如果数额足够深还足以考虑临时创办列存储索引。然后实施ETL。

 

总结:

列存储索引是一个应用SQL
Server性能优化的方案,通过削减IO消耗,尤其对数据仓库和BI查询都是出于判性能提升。它通过排序数据作为列存储,然后压缩,并运用批处理来处理数量。当然,必须要确保以列存储索引的采用带来了好处,而无见面挑起外性能问题才能够采取。比如用专注利用的硬件条件暨数码,如果没join、过滤、或者聚合导出了不起的数据量没有足够的内存则拿让小放入硬盘进行switch
off,从而挑起查询性能降低。尽量在应用之前以测试环境中测试是否入利用,同时还要关注外环节是否受影响。

补充,在2016着长的几乎单自看对新的feature:

冲聚集列存储索引的 B 树索引;

根据内存优化表的列存储索引;

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

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

 

相关文章