<span>列存储索引1：初识列存储索引</span>

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就是这样的操作符，将会引起查询返回到行模式中，虽然也能获取一部分性能提升，但是不能从批处理模式中得到显著提升。

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

Execution plan showing batch mode

Figure 1-1: 执行计划显示为Ｂａｔｃｈ

当然在２０１４中批处理模式的操作符增加很多，其中ｏｕｔｅｒｊｏｉｎ也是其中之一，总之在性能和限制上，２０１４都有显著的提高，这一点是毋庸置疑的。

对比效果.

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

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

总结

通过使用列存储索引通过两个因素来提升性能。一个是通过新的索引架构来节省Ｉ／Ｏ，另一个是批处理模式。很不幸的是，在ＳＱＬＳｅｒｖｅｒ２０１２中仅有少量操作符可以使用列存储索引，造成许多查询被迫采用行模式执行，丧失了批处理模式的性能获得。不过好消息是，绝大多数的限制在ＳＱＬＳｅｒｖｅｒ２０１４中得到了完善。

翻译自StairWay

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

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

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