列存储索引1:初识列存储索引

列存储索引1:初识列存储索引

     2012以后提供了一种不同于传统B树结构的索引类型,就是内存列存储索引。这种索引应用了一种基于列的存储模式,也是一种新的查询执行的批处理模式,并且为特定的负载提供了巨大的性能提升。它是如何构建?如何工作?又是为什么能对性能有如此大的提升,接下来我们用简明的描述和详尽的示例来解释说明。

     那么列存储索引究竟是什么?大多数时候,列存储索引被描述作为一种数据仓库和数据报表的功能。事实上,你最有可能就是在这种情况下利用这种索引。然而,即使在OLTP数据库中,你也会遇到一些要从大量数据表中获取数据的报表,它们是非常缓慢的。在合适的计划和谨慎的使用下,甚至这些报表也能利用列存储索引得到性能的提高。一个重要的前提是数据非常大,列存储索引是用来与大数据表一起使用的。虽然没有明确的最小要求,但是作为经验,我建议至少要有一千万的行数据在一个单表中才能受益于列存储索引。

    对于这个系列中的例子,将使用 ContosoRetailDW 作为演示 数据库,下载地址:http://www.microsoft.com/en-us/download/details.aspx?id=18279,这是一个626MB的数据库备份,大概1.2GB大小的数据库,对于列存储索引而言有点小,但是对于演示功能来说足够大了。这个数据库本身不包含任何列存储索引,事实上不是一个坏事,为了能更好的体现列存储索引的优点,我们将对同一查询对比带和不带列存储索引的性能。下面的例子是一个典型的来自于BI信息工作人员的查询。

WITH ContosoProducts
AS (SELECT *
    FROM   dbo.DimProduct
    WHERE  BrandName                    = 'Contoso')
SELECT     cp.ProductName,
           dd.CalendarQuarter,
           COUNT(fos.SalesOrderNumber) AS NumOrders,
           SUM(fos.SalesQuantity)      AS QuantitySold
FROM       dbo.FactOnlineSales         AS fos
INNER JOIN dbo.DimDate                 AS dd
      ON   dd.Datekey                   = fos.DateKey
INNER JOIN ContosoProducts             AS cp
      ON   cp.ProductKey                = fos.ProductKey
GROUP BY   cp.ProductName,
           dd.CalendarQuarter
ORDER BY   cp.ProductName,
           dd.CalendarQuarter;

Listing 1: 典型的BI查询

在我的笔记本上,这个查询平均花费了6.27秒来读取已经在缓存中的数据,假如数据被直接从硬盘上读取这个执行将花费8.11秒。由于FactOnlineSales 表中有超过12500000行的数据,这个查询必须扫描整个聚集索引,其实这样还不错,但是假如你整天面对这样的查询,这样的迟缓的响应将变成一个非常恶心的事情,同时也能联想到如果数据库是十倍甚至百倍大小时回事什么样的性能表现?

     注意这些执行时间是基于硬件设备的使用,假如重复执行这些测试在一个高端设备上,这些查询可能会非常迅速。当然如果在一个三年前的廉价笔记本上,将更缓慢的执行。不过,即使如此,我们也将看到在创建列存储索引后将会极大的提升执行效率。

创建列存储索引

     列存储索引有两个类型:聚集和非聚集。有很多相似之处两者之间,也有很多不同。其中一个不同是在2012中只有非聚集列存储索引。2014中才加入了聚集的版本。我们将创建一个非聚集列存储索引,以便读者能在没SQLServer2014的情况下实现。

CREATE NONCLUSTERED COLUMNSTORE INDEX NCI_FactOnlineSales
ON dbo.FactOnlineSales
   (OnlineSalesKey,
    DateKey,
    StoreKey,
    ProductKey,
    PromotionKey,
    CurrencyKey,
    CustomerKey,
    SalesOrderNumber,
    SalesOrderLineNumber,
    SalesQuantity,
    SalesAmount,
    ReturnQuantity,
    ReturnAmount,
    DiscountQuantity,
    DiscountAmount,
    TotalCost,
    UnitCost,
    UnitPrice,
    ETLLoadID,
    LoadDate,
    UpdateDate);

Listing 2: 创建非聚集存储索引

执行这个创建将花费一些时间(我必须要等待接近43秒),但是这是一个一次性的操作,在真实的数据仓库中会在夜间完成这一典型的操作。一旦索引被创建,它会提高SQLServer 中很多查询的效率。

我们获得了什么?(优点)

      当我们再次运行listing 1的代码,结果和以前的一样,但是这个结果几乎是即刻返回的。整个查询只用了0.34秒,是之前没有加入列存储索引速度的18倍多。当然如果从硬盘上读取的话,即使是列存储索引也会变慢,大约需要1.54秒,不过这仍然要比之前的8.11秒快了5倍多。

缺点

     这个由非聚集列存储索引获得的性能提升令人印象深刻的,但是也需要在书写查询的时候非常小心。几乎每个带有列存储索引的表查询都能提高效率,但是你必须带着许多限制来书写代码从而获得更大的性能潜力。比如其中一个这样限制是有关于外部连接的。

     假如编写 listing 1代码的编程人员打算将BrandName为“Contoso ”的所有产品,即使没有卖出去过的,都包含在结果中,那么就需要将Inner Join 变为Right Outer Join,如下listing3 中所示:

WITH ContosoProducts
AS (SELECT *
    FROM   dbo.DimProduct
    WHERE  BrandName                    = ‘Contoso’)
SELECT     cp.ProductName,
           dd.CalendarQuarter,
           COUNT(fos.SalesOrderNumber) AS NumOrders,
           SUM(fos.SalesQuantity)      AS QuantitySold
FROM       dbo.FactOnlineSales         AS fos
INNER JOIN dbo.DimDate                 AS dd
      ON   dd.Datekey                   = fos.DateKey
RIGHT JOIN ContosoProducts             AS cp
      ON   cp.ProductKey                = fos.ProductKey
GROUP BY   cp.ProductName,
           dd.CalendarQuarter
ORDER BY   cp.ProductName,
           dd.CalendarQuarter;

Listing 3: 引入一个外链接

在没有列存储索引的情况下(或者带有暗示模仿忽视列存储索引的情况),当数据已经在缓存中时,这个查询运行了6.71秒。包含了变化造成的在执行计划中的额外消耗,这部分大概花费了0.44秒在,耗时增加了接近百分之7。

当在我的SQLServer2012中不带提示的去运行这个查询时,优化器将立即选择一个带有列存储索引的执行计划,结果正如期望是更快的,接近4.24秒。当然这依然是要比6.71秒那种不含列存储索引的效率高的,但是与之前0.34秒的情况比较起来没有明显变化,那到底是为什么在同时都应用了列存储索引的情况下,仅仅从inner改为了outer 就产生了如此大的性能变化呢?

批处理模式

     列存储索引是由于使用了一种叫做“批处理执行模式”的模式,用一种完全不同的方式来执行查询,但是在2012中这一模式是有很多限制的,仅有少量操作符可以用来使用这一模式,只要使用了不再这些操作符中的操作符,这个查询将返回到原来的查询模式中。比如Outer Join就是这样的操作符,将会引起查询返回到行模式中,虽然也能获取一部分性能提升,但是不能从批处理模式中得到显著提升。

    最快速的方式去核实这个模式就是通过执行计划来查看该查询在SSMS 中的图像。检查两个属性“Estimated Execution Mode” 和“Actual Execution Mode”,下图极为在批处理模式下查询执行计划的示例,两个属性都为batch。

Execution plan showing batch mode

Figure 1-1: 执行计划显示为Batch

当然在2014中批处理模式的操作符增加很多,其中outer join 也是其中之一,总之在性能和限制上,2014都有显著的提高,这一点是毋庸置疑的。

对比效果.

没有一种简单的方式去预测当你创建列存储索引后性能的提升。目前只有通过在真实环境下比较查询性能或者在一个尽可能真实的测试环境下来测试比较,它带来的好处。

对于能够运行在批处理模式下的查询而言,我们已经能看到在添加列存储索引后性能提升了5到70倍,相比较于行模式的查询,性能的提升永远是更小的,一般为50%到20倍的提升。

总结

通过使用列存储索引通过两个因素来提升性能。一个是通过新的索引架构来节省I/O,另一个是批处理模式。很不幸的是,在SQLServer2012中仅有少量操作符可以使用列存储索引,造成许多查询被迫采用行模式执行,丧失了批处理模式的性能获得。不过好消息是,绝大多数的限制在SQLServer 2014 中得到了完善。

翻译自StairWay

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/119740.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • Avro介绍[通俗易懂]

    Avro介绍[通俗易懂]ApacheAvro是一个数据序列化系统。Avro所提供的属性:1.丰富的数据结构2.使用快速的压缩二进制数据格式3.提供容器文件用于持久化数据4.远程过程调用RPC5.简单的动态语言结合功能,Avro和动态语言结合后,读写数据文件和使用RPC协议都不需要生成代码,而代码生成作为一种可选的优化只值得在静态类型语言中实现。Avro的SchemaAvro的Schema用…

  • Jquery tmpl的使用

    Jquery tmpl的使用jquerytmpl简介:动态请求数据更新页面非常常用的方法,例如博客评论的分页动态加载,微博的滚动加载和定时请求加载以及ajax请求返回数据等。这些情况下,动态请求返回的数据一般不是已拼好的html就是JSON或XML,总之不在浏览器端拼接数据就在服务器端拼接数据。浏览器端根据JSON生成HTML有个很苦恼的地方就是,结构不复杂的时候还好,结构一复杂,就需要很小心的写出几乎无法维护的javas…

  • miniconda可以运行python吗_pycharm怎么配置anaconda环境

    miniconda可以运行python吗_pycharm怎么配置anaconda环境一,Pytorch环境安装打开已经装好的miniconda,用condaenvlist命令查看有哪些已经配置好的环境。如图所示,目前显示有两个环境。创建虚拟环境condacreate-n环境名字(英文)python=x.x(python版本)如:condacreate-npytorchpython=3.7用命令condaactivate虚拟环境名称,进入环境。安装pytorch环境,可以在官网查看相应的命令。这里选用的是cpu版本,命令如下:condain

  • H5即时聊天通讯系统源码 带app版本「建议收藏」

    H5即时聊天通讯系统源码 带app版本「建议收藏」介绍:PHP开发的H5即时通讯聊天系统源码带群聊可封装APP,即时通讯源码带app版本通讯聊天系统。网盘下载地址:http://kekewl.cc/oXD4GRfeddC图片:

  • Python用map函数解决Serise列表到字符串的转换

    Python用map函数解决Serise列表到字符串的转换原始数据执行过程改变后样式这些技巧虽然看着很简单很简单,但是也得加深印象啊,挺实用的,小白和大家一起学习加油!

  • SSL和SSH有什么区别

    SSL和SSH有什么区别

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号