解读SQL Server 2014可更新列存储索引——存储机制

概述

SQL Server 2014被号称是微软数据库的一个革命性版本,其性能的提升的幅度是有史以来之最。

可更新的列存储索引作为SQL Server 2014的一个关键功能之一,在提升数据库的查询性能方面贡献非常突出。据微软统计,在面向OLAP查询统计类系统中,相比其他SQL传统版本的数据库,报表查询的性能最大可提升上十倍。

下面我们从存储的角度来了解下SQL Server 2014的可更新列存储索引。

什么是列存储

微软为了提升SQL Server的查询性能,更好的支持大数据分析,早在SQL Server 2012中就引入了列存储的技术,

列存储的本质是将一个张表按照不同的列拆分,然后每一列单独存储,这样一来,存储的单位由原来的每一行变成了每一列。

像下面这张表,表中10个列分布在10个页面中,在page1中包括了表中ROW1到ROWn中列C1的数据,在page2中包括ROW1到ROWn中列C2的数据,后面依次类推。

解读SQL Server 2014可更新列存储索引——存储机制

备注:我之前有博文做过简单介绍,大家可以参考这个地址:http://www.cnblogs.com/i6first/p/3217584.html

这样做的好处就是:

  • 更好的数据压缩,减少磁盘的空间占用

数据的相似性越高、重复的值越多,压缩的效果就越明显。
列存储中的每一列数属于同一种数据类型,表达的是同一个数据概念(比如都是性别),内容重复度很高,因此相比行压缩和页压缩而言,压缩效率会更好。
这样一来不仅可以有效节省磁盘空间,而且可以在同样的内存中记录更多的数据,提升查询的性能。

  • 提升查询的性能

在一个查询中,我们往往只是想获取表中我们感兴趣的一列或者某几列的数据,

传统的查询做法必须把表中的所有的数据都扫描一遍,从而筛选出这些指定的列,

当表中包含的列比较多、数据量时,这种查询的效率的就会很低,

但如果使用了列存储,因为每一个列都是集中且彼此独立地存储,查询时只需要扫描这些指定列所在的存储区域就好了,不需要读其他不需要列的数据。查询的范围就小了很多。

比如这个查询:

select c1 from myuser1

因为mytable表中所有的C1列都是集中存储的,一次查询只扫描C1列的存储区域就好了。

什么是列存储索引

在传统的表上创建列存储索引后便可实现表的列存储。

在SQL Server 2014中,有两种列存储索引:列非聚集索引和列聚集索引。

(1)、列非聚集索引的特点

  • 跟非聚集索引一样,创建列非聚集索引时必须为索引列创建一个副本,占用额外的磁盘空间,不过因为数据压缩的原因,其占用的空间会比较小
  • 列非聚集索引的表是不可以更新的

备注:SQL Server 2012上只能建列非聚集索引,不能建列聚集索引。

(2)、列聚集索引的特点

  • 索引页就是数据页,高压缩率大大减少磁盘空间的占用
  • 可以对表进行更新
  • 列聚集索引必须是表的唯一索引,如果表中存在聚集索引或者非聚集索引,则必须删除原来的索引才能创建列存储索引
  • 只有企业版、开发版、评估版中才能使用
  • 不会改变列的物理存储顺序,其目的主要是为了提升性能和实现较高的数据压缩

备注:读者可以访问此地址,了解更多关于列聚集索引相关特性及使用限制。另外,下文如无特别说明,所描述的列存储均是包含列聚集索引的存储结构。

创建列聚集索引

列存储索引创建时不需要指定列名,索引一旦创建完成,表中所有的行就会以列的方式存储。

CREATE CLUSTERED COLUMNSTORE INDEX ci_myUser ON MyUser1; GO

为了更好的说明在列聚集索引创建时“行表”——>“列存储”的过程,我做了一张简图,并对每个序号的说明如下:

解读SQL Server 2014可更新列存储索引——存储机制

①:首先,表被拆分成一个或者多个行组(ROW GROUP);

一般而言,每个行组中的行数必须满足最小为102,400、最大1,048,576后才能转换成列存储。但如果直接在表上创建列存储索引时,这条规则可以“忽视”,因为即使表的行数少于102,400,也可以形成能够转接为列存储的行组。

其实简单想想也能理解,因为索引创建时,SQL Server不可能等着表着行数增大到102,400后再去形成列存储。

在下面我演示了一个示例,表MyUser1有102行数据,我在表上创建了一个列存储索引,大家可以看到只有一个包含了102行的行组,且该行组已经转换为列存储了。

SELECT COUNT(*)AS rows_count FROM myuser1
SELECT i.object_id, object_name(i.object_id) AS TableName, i.name
AS IndexName,
CSRowGroups.state_description,CSRowGroups.row_group_id,CSRowGroups.total_rows, CSRowGroups.*, 100*(total_rows - ISNULL(deleted_rows,0))/total_rows AS PercentFull
FROM sys.indexes AS i JOIN sys.column_store_row_groups AS CSRowGroups
ON i.object_id = CSRowGroups.object_id
AND i.index_id = CSRowGroups.index_id --WHERE object_name(i.object_id) = '<table_name>'
ORDER BY object_name(i.object_id), i.name, CSRowGroups.row_group_id;

图中,该表只有一个行组,行组ID为0(row_group_id),因为state_description为compressed(下文会详细介绍state_description的值所代表的意义),说明该行组已经按照列方式存储了。

解读SQL Server 2014可更新列存储索引——存储机制

解读SQL Server 2014可更新列存储索引——存储机制

②:将行组按列划分列块;

行组在达到指定的大小后(102400-1048576),必须按照列进行拆分,每一列形成一个列块。每个列块包含了这一列的所有数据。

再来看我的示例,表MyUser1一共有33列,按照列块的定义,就会有33个列块。

SELECT max_column_id_used FROM SYS.TABles WHERE OBJECT_ID=OBJECT_ID('MYUSER1')
SELECT i.name, p.object_id, p.index_id, i.type_desc,
COUNT(*) AS number_of_segments FROM sys.column_store_segments AS s
INNER JOIN sys.partitions AS p
ON s.hobt_id =
p.hobt_id INNER JOIN sys.indexes AS i
ON p.object_id = i.object_id
GROUP BY i.name, p.object_id, p.index_id,
i.type_desc ; GO

图示:myuser1表的列数33。

解读SQL Server 2014可更新列存储索引——存储机制

图示:myuser1表的列块数33(number_of_segments)。

解读SQL Server 2014可更新列存储索引——存储机制

③:每个列块被压缩后存储在物理磁盘上;

步骤2中列块的形成不是目的,只是手段。

列块必须压缩后才能真正按照列方式存储,根据MSDN说法,压缩后形成的列存储最大可以节省7倍的磁盘空间,

在如下示例中,我做了两个表,simpletable上有列聚集索引,simpletable_nocci上没有列聚集索引,只有聚集索引,每张表都包含了1048577行数据,且数据内容完全相同。

我们通过sp_spaceused来查看下两个表的磁盘空间占用情况,

sp_spaceused 'simpletable_nocci'
GO sp_spaceused
'simpletable'
GO

解读SQL Server 2014可更新列存储索引——存储机制

显然,simpletable_nocci的磁盘空间占用亮是simpletable的2倍多。

INSERT和BULK INSERT

在含有列聚集索引的表插入的行需要经过行——行组——列块——列存储的过程。这与传统表中插入数据是不同的。。

下面我们通过两种SQL Server insert方法来了解列存储中插入数据的过程。

(1) INSERT

Insert,也称为TRICKLE  Insert,我们通常使用的insert into就是TRICKLE  Insert。

在SQL 2014中,每次insert的行不会直接写入到列存储中,

因为这样会产生大量的索引碎片,而且这种零散的插入不能获得很好的压缩效果,影响列存储和查询的性能。

对于这些新插入的数据,SQL 2014中引入了DELTA STORE临时表,

新些插入的行还是按照行的方式存储在DELTA STORE中,并可以通过B-Tree进行检索。也就说,DELTA STORE其实传统表的存储结构一样,也是行式存储。

如图:

解读SQL Server 2014可更新列存储索引——存储机制

当DELTA STORE中行组的行数达到所要求的1048,576行时,该行组就会被标记为CLSOED,不再允许新数据插入。

然后SQL 2014的后台进程Move Truple扫描到CLSOED的行组时,会将该行组从delta store迁到列存储,最后将该行组标记为COMPRESSED。

如图:

解读SQL Server 2014可更新列存储索引——存储机制

RowGroup1的Row已经达到行组的最大值,该行组被标记为CLOSED,表示不能在接受新数据插入。

RowGroup2的Row小于1048576行,即使满足行组要求的最小值,也还是在OPEN状态,直到达到行组最大大小。

行组的状态可以通过如下语句查询得到:

SELECT i.object_id, object_name(i.object_id) AS TableName, CSRowGroups.state_description
FROM sys.indexes AS i JOIN sys.column_store_row_groups AS CSRowGroups
ON i.object_id = CSRowGroups.object_id
AND i.index_id = CSRowGroups.index_id --WHERE object_name(i.object_id) = '<table_name>'
ORDER BY object_name(i.object_id), i.name, row_group_id;

解读SQL Server 2014可更新列存储索引——存储机制

(2)BULK INSERT

Bulk insert可以理解为一种高性能的插入方法,Bulk Insert常常用于大数据导入操作,其性能要比Trickle  Insert好很多,

(有兴趣的读者可以自己验证下,插入相同行的数据,哪个更快一点。)

也正是基于此,Bulk Insert在列存储中插入数据的方法与和Trickle  Insert也有些区别。

当一次Bulk Insert的数据达到行组的最小值102,400时,该行组可以不经过delta Store而直接按照列方式存储。

这里需要引起我们注意的是,之前我们说Trickle  Insert的列存储形成过程,不仅要求行组达到1048576,而且还必须先存储在delta Store中。

由此可见在大批量的数据导入中,Bulk insert是首选的方法。

下面的实例中,我将一个含有102,400的t1.txt通过bulk insert导入到数据库中,可以看到该行组的结果直接变为COMPRESSED。

bulk insertBULK INSERT simpletable FROM 'd:\temp\t1.txt'
解读SQL Server 2014可更新列存储索引——存储机制

t1.txt的格式如下:

解读SQL Server 2014可更新列存储索引——存储机制

不过如果一次Bulk Insert插入的数据大于一个行组但小于两个行组时,多出的这部分数据必须也存储在delta Store中。

DELETE和UPDATE

因为列聚集索引的表可能同时包含行存储的delta store和列存储,所以在处理删除和更新时,两个不同区域会有所不同。

(1)delete

我们先看删除操作:

  • 如果删除的行在列存储中,SQL Server只是从逻辑上删除它,其占用的物理空间并不会释放

SQL Server 2014 的delete bitmap是用来跟踪列存储中的每一个记录删除情况的表,它跟delta store一样也是基于行和B-tree的方式存储。

当某一行需要被删除时,delete bitmap会将该行对应的bit标记为删除状态,实际上行的所在的物理区域没有发生变化。

这样就要求所有的查询语句必须先扫描delete bitmap,对于哪些已被记为删除的记录就不要到物理存储查找了,也不需要出现在查询的结果中。

解读SQL Server 2014可更新列存储索引——存储机制

  • 如果删除的行在Delta Store中,这跟传统行存储的删除方式没有区别,SQL Server会从逻辑上和物理上都删除该数据。

(2)update

理解了insert和delete后,我们再来看update就非常简单了。

  • 如果update发生在列存储中

SQL Server会将该行在delete bitmap中bit标记为删除状态,同时插入一新行到delta store中。

  • 如果update发生在delta store中

SQL Server直接更新delta store中这行的数据。

结论

尽管SQL Server 2014的列存储已经支持数据更新,但并不意味着生产环境下的报表能够从中获益。

列存储天生是为OLAP设计,其数据特征更趋向于静态,即使是数据的导入,微软也建议使用bulk insert,

所以如果数据库的日常操作中存在大量的增、删、改等操作,使用列存储的技术可能会适得其反。

上一篇:03-JavaScript之数据类型


下一篇:[C]simple code of count input lines,words,chars