【IT168 技术】微软在SQL Server 2012中引入了非聚集列存储索引，列存储索引能够将数据库的性能大幅提升并通过列存储压缩技术将空间使用大幅降低。但同时引入很多限制，比如无法建成聚集的列存储索引，这意味着列索引仅仅是在原有的行存储索引之上引用了底层数据而已，反而会消耗更多的存储空间。在SQL Server 2012中一旦将非聚集列存储索引建立在某个表上时，该表将变为只读，即使在数据仓库中使用列索引，每次更新或添加数据也会是一件非常琐碎的事。SQL Server 2014中的可更新聚集列索引则解决了该问题。
一、什么是可更新聚集列存储索引
聚集列存储索引的概念可以类比于传统的行存储，聚集索引是数据本身，列存储的概念也是一样。相比行存储索引，使用列存储索引有如下好处：
·首先对于聚合、扫描、分组等大查询类操作仅仅需要读取选择的列，对于需要Join多个表的星型结构等场景的查询性能提升尤其明显;
·其次是列索引可以更新，并且每个表中只需要一个聚集列索引即可，简化了维护操作;
·列索引由于是按列存储，同一列中数据类型是一样的，因此可以更容易的实现更高压缩比;
·列存储的表由于非常高的压缩比率，占用更少的存储空间，对于OLAP类对IO高消耗的查询来说，进一步提升了查询性能。
二、列存储索引适用场景
SQL Server传统的行存储方式适合于短平快的OLTP操作，因为每个聚集索引键可以用于标识行。该行存储在物理磁盘上也是连续的，因此可以利用Seek操作以非常低的成本完成选择性高的查询;而列存储索引同一行的每一列并不在物理上连续，并且列存储聚集索引中并没有“主键”的概念，因此并不存在Seek操作。因此并不适用于OLTP类操作。
列存储更适用于OLAP操作，在OLAP操作中，并发小、查询性能瓶颈在IO而不是CPU，列存储索引可以有效提升该类查询性能。因此，列存储索引只支持Scan操作，如图1所示。
▲图1.列存储索引只支持Scan操作
三、列索引的存储方式
列存储索引望文生义，即按列存储。该过程可以分为3个阶段，第一阶段是对一定数据的行进行分组，这就是所谓的“行组”。每个行组能够容纳102,400到1,048,576行，分组完成后，再按列切分成为列片段。最后将列片段压缩后，插入实际的列存储。如图2所示。
▲图2.列存储的过程
我们注意到其中有一部分行不够分组的最低条件(102,400行)，那么就直接让这部分数据以传统行存储的形式进行存储，这就是所谓的DeltaStore，等数据增长到可以分组时再进行分组。
上述列存储的两部分数据分布可以通过SQL Server 2014新引入的DMV进行观测，如图3所示。在图3中，对目前已经存在31,465行的聚集列索引插入了1000行新的数据，则SQL Server认为这部分数据不满100,000行，因此以DeltaStore的方式存在。
▲图3.压缩后的列和Deltastore
当再插入1000行数据时，可以观察到DeltaStore中的数据又增加了1000行，达到2000行时，依然存在DeltaStore中。如图4所示。
▲图4.再次插入的数据依然在DeltaStore中
当插入大量的行进行观测时会发现，虽然数据已经达到列片段的最低阈值102,400行，但该部分数据依然以DeltaStore的方式存储，如图5。
▲图5.插入大量数据后也无法将数据压缩
那么究竟何时会压缩这些数据并以列存储的形式存放呢，根据BOL的说法(http://msdn.microsoft.com/en-us/library/dn223749(v=sql.120).aspx)，会有一个后台的线程定期检测，当发现为“关闭”状态的行组时，对其进行压缩并以列存储的形式存放，此外当重建或整理索引时也会进行压缩转存，如图6所示。
▲图6.重建索引后归档列存储索引
四、空间占用比较
可更新列存储聚集索引的压缩比率非常高，因为相同列中存放同一类数据，同类数据有更好的压缩比。由于INT类型的数据更容易有更高的压缩比，下面首先简单对一个600万行纯INT的表进行对比，来查看列存储的压缩比例，使用行存储和列存储的空间占用如图7所示。
▲图7.不同存储占用空间
由图7可以看出，使用行存储存671万数据大约需要188MB的空间，而列存储仅仅需要21MB 空间，使用列存储压缩后数据几乎只有行存储的1/9，压缩比率惊人。在2014中，微软对于列存储提供了进一步压缩选项，对于归档数据，可以使用列存储归档压缩方式，该方式使用了XPRESS压缩算法，可以使用下述T-SQL代码对列存储索引数据进行进一步压缩：
ALTER TABLE dbo.Sales REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE) ;
进一步压缩后，表空间占用如图8所示：
▲图8.进一步压缩后的空间占用
在图8看出，使用了归档列存储压缩，数据被压缩到15MB，相比较于188MB的行存储，空间占用只有1/12。
上述例子表中只有INT类型列只是为了阐述列存储能够做到的压缩比率，与实际场景并不贴近，下面的例子采集于某客户的真实报表库，一个大表中有不同种类的字段类型，表结构如图9所示。
▲图9.采集于真实系统的表结构
首先，查看行存储下该表的数据量与大小，如图10所示。
▲图10.示例表数据量与空间占用
该表使用行存储有221万条左右数据，空间占用大约需要2GB左右。现在使用列存储索引，再次查看空间使用，如图11所示。
▲图11 示例表使用列存储后数据量与空间占用
由图11看出，列存储即使在比较复杂的表结构下，列存储空间占用只有行存储的三分之一。
虽然列存储索引总是以压缩的形式存放数据，但还可以通过配置进一步对列存储索引进行压缩，这就是所谓的归档压缩。归档压缩如其名称所示，目的是对于表中的归档数据进行压缩，实现方式是通过在列存储索引上进行表分区，对归档数据再次进行压缩(比如按时间列进行归档，将去年以及之前的数据分到归档区中)以进一步减少空间占用。
现在对列存储示例表中的数据按时间进行分区，2014年5月之前的数据分4个区，也就是所谓的归档区，2014年5月的数据是活动区，分区情况如图12所示。
▲图12. 列存储示例表分区情况
分区1-4是2014年5月1日以前的数据，业务上认为是历史数据，被使用的频率大大低于当前的“热”数据，因此可以利用列存储归档压缩对分区1-4进行进一步压缩：
ALTER TABLE Result REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (1,2,3,4)) ;
此时，列存储数据空间使用由751MB下降到495MB，而频繁被用到的分区5不受影响，如图13所示。
▲图13.对归档分区压缩后，列存储索引空间占用
五、性能比较
对于行存储而言，OLAP类操作对某一列或多列做聚合运算，都需要将所有的列读出来，IO往往成为查询性能的瓶颈。但列存储则只需要读取被选择的列，列的高压缩比率又进一步降低了IO，从而提升了性能。
下面对于图9示例表中的数据进行一个简单的聚合OLAP查询，首先在行存储上进行查询，结果如图14所示。
▲图14.对行存储表进行聚合查询
下面，在列存储上进行同样的查询，结果如图15所示。
▲图15.对列存储表进行同样的聚合查询
由图14和图15的对比可以看出，对于聚合等OLAP类操作，列存储在查询性能相比行存储上有了戏剧性的提升，而列存储极高的压缩比有反过来降低了IO从而更进一步提升了查询性能。
六、小结
本文阐述了SQL Server 2014中可更新列存储索引的原理、概念、适用场景、空间使用情况和性能比对。SQL Server 2014 列存储可以直接更新使得管理变得极为方便，对于数据仓库和类OLAP查询来说，性能得到了巨大的提升且空间占用极大减少，使得SQL Server作为决策和分析系统的基础架构，可以帮助决策和分析系统实现更多可能。
作者简介
宋沄剑，微软SQL Server最有价值专家，《SQL Server 2012管理高级教程》译者，SQL Server专家协会北京分会创始人之一，目前就职于北京格瑞趋势科技有限公司任数据库高级顾问，专注于帮助大型企业用户设计数据库方案和解决数据库问题。
