首页 > 代码库 > 解读SQL Server 2014可更新列存储索引——存储机制

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

2024-07-04 20:13:58   223人阅读

 

概述

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的数据，后面依次类推。

  image

备注：我之前有博文做过简单介绍，大家可以参考这个地址：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

 

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

 

image

 

①：首先，表被拆分成一个或者多个行组（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的值所代表的意义），说明该行组已经按照列方式存储了。

11

 

9

 

②：将行组按列划分列块；

行组在达到指定的大小后（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。

12

图示：myuser1表的列块数33（number_of_segments)。

10

 

 

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

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

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

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

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

 

sp_spaceused ‘simpletable_nocci‘
GO sp_spaceused 
‘simpletable‘
GO

13

显然，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其实传统表的存储结构一样，也是行式存储。

         如图：

15

 

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

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

          如图：

           14

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;

 

8

 

（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‘
    5

       t1.txt的格式如下：

3

不过如果一次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，对于哪些已被记为删除的记录就不要到物理存储查找了，也不需要出现在查询的结果中。

16

 

  •      如果删除的行在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，

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


联系
我们