精华内容
下载资源
问答
  • 列存储索引

    2018-04-19 14:46:00
    2012以后提供了一种不同于传统B树结构的索引类型,就是内存列存储索引。这种索引应用了一种基于列的存储模式,也是一种新的查询执行的批处理模式,并且为特定的负载提供了巨大的性能提升。它是如何构建?如何工作?...

    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 中得到了完善。

     

    来源:http://www.cnblogs.com/wenBlog/p/4970493.html

    转载于:https://www.cnblogs.com/littlewrong/p/8882775.html

    展开全文
  • 2012以后提供了一种不同于传统B树结构的索引类型,就是内存列存储索引。这种索引应用了一种基于列的存储模式,也是一种新的查询执行的批处理模式,并且为特定的负载提供了巨大的性能提升。它是如何构建?如何工作?...

    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。

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

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

    对比效果.

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

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

    总结

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

    翻译自StairWay

    展开全文
  • 为了更好的理解列存储索引,接下来我们一起通过列存储索引与传统的行存储索引地对比2014中的列存储索引带来了哪些改善。由于已经很多介绍列存储,因此这里我仅就性能的改进进行重点说明。测试场景我创建了5个测试,...

    原因:

    之前已经写过一篇关于列存储索引的简介http://www.cnblogs.com/wenBlog/p/4970493.html,很粗糙但是基本阐明了列存储索引的好处。为了更好的理解列存储索引,接下来我们一起通过列存储索引与传统的行存储索引地对比2014中的列存储索引带来了哪些改善。由于已经很多介绍列存储,因此这里我仅就性能的改进进行重点说明。

    测试场景

    我创建了5个测试,尽量保证测试环境避免来自外界的重负载进而影响到结果。测试结果基于两个独立的表,分别是:

    FactTransaction_ColumnStore - 这个表仅有一个聚集列存储索引,由于列存储索引的限制,该表不再有其他索引。

    FactTransaction_RowStore - 该表将包含一个聚集索引和一个非聚集列存储索引和一个非聚集行存储索引。

    首先我用脚本文件创建表和索引,然后用30m行数据填充到三个表中。由于所有的测试我都制定了最大并行度的hint ,因此可以指定内核数量来查询。

    测试1-填充表

    为了更好地测试,一个表由列存储索引构成,而另一个表仅有行存储索引构成。填充数据来自于另一个表'FactTransaction'。

    IO 和时间统计

    Table 'FactTransaction_ColumnStore'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'FactTransaction'. Scan count 1, logical reads 73462, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (30000000 row(s) affected)

    SQL Server Execution Times: CPU time = 98204 ms, elapsed time = 109927 ms.

    Table ' FactTransaction_RowStore '. Scan count 0, logical reads 98566047, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'FactTransaction'. Scan count 1, logical reads 73462, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (30000000 row(s) affected)

    SQL Server Execution Times: CPU time = 111375 ms, elapsed time = 129609 ms.

    观察测试

    表名

    填充时间

    逻辑读

    FacTransaction_ColumnStore

    1.49 mins

    0

    FacTransaction_RowStore

    2.09 mins

    98566047

    测试2-比较搜索

    注意这里在行存储索引上我指定表的hint,迫使表通过索引查找。

    -- Comparing Seek....

    SET Statistics IO,TIME ON

    Select CustomerFK

    From [dbo].FactTransaction_RowStore WITH(FORCESEEK)

    Where transactionSK = 4000000

    OPTION (MAXDOP 1)

    Select CustomerFK

    From [dbo].FactTransaction_ColumnStore

    Where transactionSK = 4000000

    OPTION (MAXDOP 1)

    SET Statistics IO,TIME OFF

    IO 和时间统计

    Table 'FactTransaction_RowStore'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

    Table 'FactTransaction_ColumnStore'. Scan count 1, logical reads 714, physical reads 0, read-ahead reads 2510, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times: CPU time = 0 ms, elapsed time = 83 ms.

    执行计划

    观察测试2

    正如上图所示,行存储索引表的索引查找远比列存储索引表查询快的多。这主要归因于2014的sqlserver不支持聚集列存储索引的索引查找。执行计划对比图中一个是索引扫描导致更多的逻辑读,因此导致了性能的下降。

    表名

    索引类型

    逻辑读

    运行时间

    FacTransaction_ColumnStore

    Column

    714

    83 ms

    FacTransaction_RowStore

    Row

    3

    0 ms

    Test 3 - Comparing SCAN

    注意这次我指定的hint都是索引扫描,当然列存储索引上优化器默认为索引扫描。

    -- Comparing Scan....

    SET Statistics IO,TIME ON

    Select CustomerFK

    From [dbo].FactTransaction_RowStore WITH(FORCESCAN)

    Where transactionSK = 4000000

    OPTION (MAXDOP 1)

    Select CustomerFK

    From [dbo].FactTransaction_ColumnStore WITH(FORCESCAN)

    Where transactionSK = 4000000

    OPTION (MAXDOP 1)

    SET Statistics IO,TIME OFF

    IO 和时间统计

    Table 'FactTransaction_RowStore'. Scan count 1, logical reads 12704, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 32 ms, elapsed time = 22 ms.

    Table 'FactTransaction_ColumnStore'. Scan count 1, logical reads 714, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 2 ms.

    执行计划

    观察测试3

    正如之前提到的,索引扫描列存储要比行存储快,俩个逻辑读和运行时间表明列存储索引在大表扫描上是更优的方式,因此更适合于数据仓库的表。

    表名

    索引类型

    逻辑读

    运行时间

    FacTransaction_ColumnStore

    Column

    714

    2 ms

    FacTransaction_RowStore

    Row

    12704

    22 ms

    测试4-聚合查询

    测试行存储表使用基于聚集索引。

    SET Statistics IO,TIME ON

    Select CustomerFK,BrandFK, Count(*)

    From [dbo].[FactTransaction_RowStore] WITH(INDEX=RowStore_FactTransaction)

    Group by CustomerFK,BrandFK

    OPTION (MAXDOP 4)

    测试行存储表,使用CustomerFK 和BrandFK的索引。(覆盖索引)

    Select CustomerFK,BrandFK, Count(*)

    From [dbo].[FactTransaction_RowStore] WITH(INDEX=RowStore_CustomerFK_BrandFK)

    Group by CustomerFK,BrandFK

    OPTION (MAXDOP 4)

    测试行存储索引使用CustomerFK 和BrandFK的列存储索引(覆盖索引)

    Select CustomerFK,BrandFK, Count(*) From [dbo].[FactTransaction_RowStore] WITH(INDEX=ColumnStore_CustomerFK_BrandFK) Group by CustomerFK,BrandFK OPTION (MAXDOP 4)

    Test on the columnstore table using the Clustered Index.

    Select CustomerFK,BrandFK, Count(*)

    From [dbo].[FactTransaction_ColumnStore]

    Group by CustomerFK,BrandFK

    OPTION (MAXDOP 4)

    SET Statistics IO,TIME OFF

    IO 和时间统计

    使用基于聚集索引查询行存储的表。

    Table 'FactTransaction_RowStore'. Scan count 5, logical reads 45977, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times: CPU time = 9516 ms, elapsed time = 2645 ms.

    使用行存储的非聚集索引测试行存储表。(覆盖索引)

    Table 'FactTransaction_RowStore'. Scan count 5, logical reads 71204, physical reads 0, read-ahead reads 2160, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times: CPU time = 5343 ms, elapsed time = 1833 ms.

    使用非聚集列存储索引测试行存储表。(覆盖索引)

    Table 'FactTransaction_RowStore'. Scan count 4, logical reads 785, physical reads 7, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times: CPU time = 141 ms, elapsed time = 63 ms.

    使用聚集索引测试列存储表。

    Table 'FactTransaction_ColumnStore'. Scan count 4, logical reads 723, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times: CPU time = 203 ms, elapsed time = 118 ms.

    执行计划

    观察测试4

    这里才是列存储索引开始“闪耀”的地方。两个列存储索引的表查询要比传统的航索引在逻辑读和运行时间上性能好得多。

    表名

    索引使用

    索引类型

    逻辑读

    运行时间

    FacTransaction_ColumnStore

    ClusteredColumnStore

    Column

    717

    118

    FacTransaction_RowStore

    RowStore_FactTransaction

    Row

    45957

    2645

    FacTransaction_RowStore

    RowStore_CustomerFK_BrandFK

    Row

    71220

    1833

    FacTransaction_RowStore

    ColumnStore_CustomerFK_BrandFK

    Column

    782

    63

    测试5-比较更新(数据子集)

    这个测试中,我将更新少于100m行数据,占总数据的30分之一。

    SET Statistics IO,TIME ON

    Update [dbo].[FactTransaction_ColumnStore]

    Set TransactionAmount = 100

    Where CustomerFK = 112

    OPTION (MAXDOP 1)

    Update [dbo].[FactTransaction_RowStore]

    Set TransactionAmount = 100

    Where CustomerFK = 112

    OPTION (MAXDOP 1)

    SET Statistics IO,TIME OFF

    IO 和时间统计

    Table 'FactTransaction_ColumnStore'. Scan count 2, logical reads 2020, physical reads 0, read-ahead reads 2598, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (913712 row(s) affected)

    SQL Server Execution Times: CPU time = 27688 ms, elapsed time = 37638 ms.

    Table 'FactTransaction_RowStore'. Scan count 1, logical reads 2800296, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (913712 row(s) affected)

    SQL Server Execution Times: CPU time = 6812 ms, elapsed time = 6819 ms.

    执行计划

    观察测试5

    在这种情况下 ,列存储索引的表要比行存储的更新慢的多。

    表名

    索引类型

    逻辑读

    运行时间

    FacTransaction_ColumnStore

    Column

    2020

    37638 ms

    FacTransaction_RowStore

    Row

    2800296

    6819 ms

    注意对于行存储表逻辑读还是要比行存储的要多很多。这是归因于列存储索引的压缩比率更高,因此占用更少的内存。

    总结

    列存储索引(包含聚集和非聚集)提供了大量的优势。但是在数据仓库上使用还是要做好准备工作。一种合适地使用情况是非聚集索引不能被更新且禁用对底层表的更新。如果是巨大且没有分区的表,可能存在一个问题,整个表的索引每次都会被重建,因此如果表是巨大的则禁止使用列存储索引。因此必须要有好的分区策略来支持这种索引。

    有几个应用列存储索引的地方:事实表的聚合、Fast Track Data Warehouse Servers、恰当环境SSAS的Cube…

    展开全文
  • 2012以后提供了一种不同于传统B树结构的索引类型,就是内存列存储索引。这种索引应用了一种基于列的存储模式,也是一种新的查询执行的批处理模式,并且为特定的负载提供了巨大的性能提升。它是如何构建?如何工作?...

         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

    转载于:https://www.cnblogs.com/wenBlog/p/4970493.html

    展开全文
  • 行存储索引改换成列存储索引 My team and I were recently tasked with refactoring older data marts, particularly those that were created with SQL Server 2008 in mind. As we all know, SQL Server has ...
  • 行存储索引改换成列存储索引 Data compression is required to reduce database storage size as well as improving performance for the existing data. SQL Server 2008 introduced Data compression as an ...
  • 可更新的列存储索引作为SQL Server 2014的一个关键功能之一,在提升数据库的查询性能方面贡献非常突出。据微软统计,在面向OLAP查询统计类系统中,相比其他SQL传统版本的数据库,报表查询的性能最大可提升上十倍。...
  • 接上文:SQL Server 列存储索引性能总结(5)——列存储等待信息,前面的文章主要集中在聚集列存储上,下面也是时候引入一下费聚集列存储索引的内容。    这篇文章集中在不同列存储索引的“压缩”上面。还是使用...
  • 接上文SQL Server 列存储索引性能总结(8)——列存储中的Dictionary,本文演示一下创建或重建及重组列存储索引时所需要的内存,因为在 SQL Server 列存储索引性能总结(5)——列存储等待信息中我们也看到了如果...
  • 列存储索引有推荐的最佳导入行数,过小会导致数据先进入Delta Store(行组,row group),这个是行存储的B 树结构,然后通过Tuple Mover对数据进行编码和压缩最终成为片段(segment),这时候才能说进入了真正的...
  • 聚集列存储索引(CC Index)是SQL Server 2014中两大最引人瞩目的特性之一,设计为用于超过1千万条记录的数据表。使用者无需明确的指定索引,也能够保证分析式查询的优良性能。但2014版本中的这一特性存在着一个缺陷,...
  • 比较列存储索引与行索引 原文:比较列存储索引与行索引原因: 之前已经写过一篇关于列存储索引的简介http://www.cnblogs.com/wenBlog/p/4970493.html,很粗糙但是基本阐明了列存储索引的好处。为了...
  • 这几天我一直在跟进公司的一个性能问题,里面涉及到聚集列存储索引的问题。跟微软的技术支持讨论了一下,他们的建议可以考虑转成非聚集的列存储索引,那我到底怎么做好呢?我觉得有必要研究一下这两者的差异,说不定...
  • 接上文:SQL Server 列存储索引性能总结(4)——列存储压缩,本文介绍列存储相关的锁    上周六,我在加班,为公司的Azure SQL DB测试聚集列存储索引,按照网上的说法,对堆表建立聚集列存储索引应该很快的,...
  • 理解列存储索引

    2016-12-11 20:03:00
    版权声明:原创作品,谢绝转载!否则将追究法律责任。 优点和使用场景 ... 与传统面向行的存储方式相比,使用列存储索引存档可最多提高 10 倍查询性能,与使用非压缩数据大小相比,可提供多达 ...
  • SQL Server 列存储索引强化 SQL Server 列存储索引强化 SQL Server 列存储索引强化... 1 1. 概述... 1 2.背景... 2 2.1 索引存储... 2 2.2 缓存和I/O.. 2 2.3 Batch处理...
  • SQL Server 2014聚集列存储索引 ... 之前已经写过两篇介绍列存储索引的文章,但是只有非聚集列存储索引,今天再来简单介绍一下聚集的列存储索引,也就是可更新列存储索引。在SQL Serv...
  • 接上文:SQL Server 列存储索引性能总结(7)——导入数据到列存储索引的Delta Store,前面提到了几次Dictionary,本文快速介绍一下它到底是什么,以便更好地理解列存储。 不过这部分不会讲太深入,因为这个功能只能...
  • 接上文:SQL Server 列存储索引性能总结(3)——列存储的锁,列存储的其中一个强项在于非常高的压缩率,如果没有这个压缩功能,列存储不可能有极大的性能提升。本文就来演示一下压缩方面的内容。 案例测试    ...
  • 接上文:SQL Server 列存储索引性能总结(10)——行组的大小影响索引需要维护,这个不多说,而维护通常就是两种:重建和重组。在一些可控的环境下(比如自己的机房),我选择使用Ola Hallengren的一整套维护脚本。...
  • SQL Server 2016新特性:列存储索引新特性 SQL Server 2016新特性:列存储索引新特性 行存储表可以有一个可更新的列存储索引,之前非聚集的列存储索引是只读的。 非聚集的列存储索引支持筛选条件。 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 8,484
精华内容 3,393
关键字:

列存储索引