大数据离不开列式存储

简介

SQL server 11.0 发布，介绍了一种基于列式存储的索引属性来加速数据仓库查询。这种新的索引，结合了增强的查询优化功能和执行功能，在某些情况下能提高百倍甚至是上千倍的数据仓库的查询行能并且能够定期的提供一系列的查询语句得到十倍以上的加速来适应所设计的环境。这些都是在我们熟悉的SQLserver语句，程序以及系统管理环境下实现的。这个属性与所有在SQLserver 客户端执行的报表工具能兼容，其中包括SQLserver reporting service。

列式索引是把每一列存储到不同的磁盘页面上而不是像传统的在每一页上存储多行。我们术语“行存储”来描述在每一页中使用堆或者是B-树存储的多行数据。行存储和列存储的区别可以使用下图来说明：

幻灯播放

在列式存储索引中，列C1…C6 被存储到不同的页面组中。这样做的好处有：

·        只需要从硬盘取出需要查询的列

·        由于数据冗余，它更容易压缩在一列的数据

·        由于数据被高度压缩，缓存区的 命中率提高。常用列的频繁访问部分保留在内存，而很少使用的部分被调出。

SQl Server 中的列式存储索引采用微软的专利Vertipaq TM 技术，并且在SQLServer Analysis 和Powerpivot 中使用到。SQL Server 列式存储索引不需要适应内存的大小，但是他可以最有效的使用尽可能多的服务器上的可用内存。按照需要列的分区被换进和换出内存。

SQL Server Denali 列式存储索引是单一的列式存储，没混合形式。这是因为所有的数据是按照列分页存储。这些可以提高I/O扫描的性能以及缓存命中率。SQLServer是第一个主要的数据库产品来支持纯列式存储索引。

使用列式索引

为了改善查询性能，需要在数据仓库中的一张Fact表中建立一个列式存储索引。也可以考虑在某些维度表上有大量的数据（超过1千万行）建立这种索引。建好索引之后，只需要提交查询语句，你会发现查询会提高好几倍。

例如，卫门建立了1TB内部的测试数据仓库，一张fact表包含14.4亿行数据。创建列式存储索引可以包含表的所有列，如下：

CREATE COLUMNSTORE INDEX cstoreon [dbo].[catalog_sales]

([cs_sold_date_sk]

,[cs_sold_time_sk]

,[cs_ship_date_sk]

,[cs_bill_customer_sk]

,[cs_bill_cdemo_sk]

,[cs_bill_hdemo_sk]

,[cs_bill_addr_sk]

,[cs_ship_customer_sk]

,[cs_ship_cdemo_sk]

,[cs_ship_hdemo_sk]

,[cs_ship_addr_sk]

,[cs_call_center_sk]

,[cs_catalog_page_sk]

,[cs_ship_mode_sk]

,[cs_warehouse_sk]

,[cs_item_sk]

,[cs_promo_sk]

,[cs_order_number]

,[cs_quantity]

,[cs_wholesale_cost]

,[cs_list_price]

,[cs_sales_price]

,[cs_ext_discount_amt]

,[cs_ext_sales_price]

,[cs_ext_wholesale_cost]

,[cs_ext_list_price]

,[cs_ext_tax]

,[cs_coupon_amt]

,[cs_ext_ship_cost]

,[cs_net_paid]

,[cs_net_paid_inc_tax]

,[cs_net_paid_inc_ship]

,[cs_net_paid_inc_ship_tax]

,[cs_net_profit])

性能说明

在32位256GBRAM的处理机上，对比建立和没有建立列式存储索引的Fact表。

select w_city, w_state, d_year,SUM(cs_sales_price) as cs_sales_price

from warehouse, catalog_sales,date_dim

where w_warehouse_sk =cs_warehouse_sk

and cs_sold_date_sk = d_date_sk

and w_state in ('SD','OH')

and d_year in (2001,2002,2003)

group by w_city, w_state, d_year

order by d_year, w_state,w_city;

这样的表现确实是惊人的，SQL Server的列式存储索引技术在一个相对经济的多处理系统上进行星型连接查询中（包括一个14.4行数据的表）几乎是实时的相应。在内存受限的环境下， 列式存储工作集任然可以在RAM中满足，但是行式存储不行，它能很容易的实现上千倍的加速。当俩种存储方式都能适应内存时，差别会减少，通常在有分组聚合的星型连接时6倍到100倍的提速。具体会根据数据，工作量和硬件的不同而不同。

性能特点

列式存储索引查询过程主要是为星型连接查询进行可很大程度上优化，但是其他类型的查询也可以受益。Fact-to-Fact 连接和多列连接可能起不到太大的作用。OLTP-style查询，包括点查询，每列宽行提去通常就像B-树索引并不会表现的很好。列式存储索引并不总是会改善数据仓库的查询性能。如果不能的话，通常查询优化器会选择堆或者是B-树来处理数据。当然在列式存储索引并不如使用堆或者是B-树索引的效果是而且优化器选择了列式索引，我们也可以使用提示来修改查询使用堆和B-树索引。

压缩结果

在包含实际用户的不同的事实表中通常会有4到15倍的数据压缩，列式存储索引是第二索引;行式存储任然存在，尽管在查询处理时不需要，最终被调出。列式聚簇索引将会是主数据的副本，计划在将来实现。这将在既有的性能增加的基础上能节省更多的空间。

读取数据

在Denali 发布时，包含列式索引的表将不会随着Insert，update，delete和merge语句以及批量载入的操作而被更新。如果要把数据load到列索引表里，你可以切换 分区或者是禁用掉索引，然后更新表再从新建立索引。列式存储索引在分区表上必须分区对齐。大多数数据参数用户都会有一个每日装载的周期，而且进入数据仓库的数据都是默认只读，所以完全可以使用列式所引。

你可以使用Union All 来合并一张表和一个列式存储索引或者是一个可更新的表来创建视图，并且不需要把列式索引更新到逻辑表上。这张视图表可以用来被查询引用。这样的话允许动态插入新的数据到单一的逻辑表中并且保持列存储索引的高性能。

所有表没有列存储索引的还保持全部更新。这样的话允许你任意的创建维度表然后与列存储结构的fact表连接进行一系列的查询。这样会很有用，例如，一个零售商分析员想把1000个产品加入到学习组中，然后在这个学习组上运行重复的查询。所有的这些产品的ID可以放置到学习组的维度表中。这个表可以joinwith 列式结构的fact表。

在预发布的版本当中，列式存储索引构建需要比构建聚簇B-树索引2到3倍长的时间，所以用户需要适应这个ETL中的时间差。然而，用户不需要在需要花费大量时间的聚集求和上花费太多的时间，所以实际上ETL的时间可能会减少。

列式存储索引的好处

最主要的好处就是列式存储索引可以通过鼓励使你的用户以交互查询的方式来获得业务价值，列式存储的优异性能使之成为了可能。对一个经济的SMP服务器，你的内存足够来处理数十亿行数据频繁访问以及可以得到及时的响应。

列式存储所以也可以通过减少对预建的聚合汇总的依赖来减少IT的负担以及ETL的时间，无论他们是索引视图，用户定义的汇总表还是OLAPcube。设计和维护聚合通常很困难。列式存储比聚合更强健因为如果一个查询被修改，列式存储任然可以支持它，然而特殊的聚合在加速查询的时候却起不到什么作用。

使用OLAP系统的用户仅仅是为了能获得查询性能的优化 ，但是有一些任然习惯于使用T-SQL语句来写query的用户可能会发现他们可以在自己的环境中无需太多的改变就能降低复杂性和成本。用户喜欢使用OLAP中提供的复杂的报表工具，具有维度建模的能力，预置功能以及决策支持的特殊查询语言，同样可以从中受益。此外，他们现在可以通过基于列式存储的SQLServer数据仓库来使用ROLAP，并且能够达到或超过了他们在过去使用OLAP时的性能，同样节省了构建Cube的时间。

列式存储性能优势根源

正如上面所讨论的，列存储索引功能通过压缩和每次查询设计到很少列的事实，减少从磁盘到内存的数据抽取的时间来提高性能。这仅仅是性能优势的原因之一。同时也是因为采用了多个其他专有的技术。这些方法还没有公开，但是有一点可以肯定的，性能提升！

结论

在SQL Server Denali中的列式存储索引和相关的查询处理能力是突破性的技术，给数据仓库查询处理带来了前所未闻的性能优势。你的用户将是最终的受益者，现在他们可以用他们自己喜欢的报表工具使用更少的时间来获取更多的商业价值，