【SQL码农】SQL Server 2012新特性_列存储索引(3)

SQL Server 2012新特性_列存储索引(3)

http://blog.csdn.net/burgess_liu/article/details/7431386

本篇Blog将介绍列存储索引相关的性能、查询提示及使用限制等方面的知识。若要了解列存储及列存储索引的概念及特征，请点击这里；若要了解列存储索引的创建与使用，请点击这里。 

列存储的性能
要了解类存储索引的性能到底如何，不妨先来搭建一下测试的环境。下面我们创建基本两张完全一样的表，不同的是，一张表没有列存储索引，而另一张则有：

不带column Store Index (N'tbl_Engineers_WithoutColumnStore)的脚本：

[sql]  view plain copy

1. -- Drop the table if it exists  
2. IF OBJECT_ID('tbl_Engineers_WithoutColumnStore') IS NOT NULL   
3.     DROP TABLE tbl_Engineers_WithoutColumnStore  
4. GO  
5. SET ANSI_NULLS ON  
6. GO  
7. --Create the table  
8. CREATE TABLE tbl_Engineers_WithoutColumnStore (  
9.     EngineerID INT IDENTITY,  
10.     EngineerName VARCHAR(15),  
11.     BelongsTo VARCHAR(15)  
12. )  
13.   
14. -- Creating clustered index  
15. CREATE CLUSTERED INDEX IX_EngineerID ON tbl_Engineers_WithoutColumnStore(EngineerID)  
16. -- Creating normal nonclustered index  
17. CREATE NONCLUSTERED INDEX IX_BelongsTo ON tbl_Engineers_WithoutColumnStore(BelongsTo)  
18.   
19. --Prepare the data  
20. ;With Cte As  
21. (  
22.     Select   
23.         Id=1  
24.         ,EngineerName='Engineer' + CAST( 1 As Varchar(50))  
25.         ,BelongsTo = Cast ('Shanghai' As Varchar(50))  
26.     Union All  
27.     Select   
28.         Id +1   
29.         ,EngineerName= 'Engineer' + CAST( Id+1 As Varchar(50))   
30.         ,BelongsTo = Cast(Choose(((ID%5)+1),'Shanghai','Henan','Jiangsu','Chongqing','Zhejiang')As Varchar(50))  
31.               
32.     From Cte   
33.     Where Id < 2000000  
34. )  
35. Insert into tbl_Engineers_WithoutColumnStore  
36. Select EngineerName,BelongsTo from Cte  
37. Option (Maxrecursion 0)  

 

带column Store Index (N'tbl_Engineers_WithColumnStore)的脚本：

[sql]  view plain copy

1. -- Drop the table if it exists  
2. IF OBJECT_ID('tbl_Engineers_WithColumnStore') IS NOT NULL   
3.     DROP TABLE tbl_Engineers_WithColumnStore  
4. GO  
5. SET ANSI_NULLS ON  
6. GO  
7. --Create the table  
8. CREATE TABLE tbl_Engineers_WithColumnStore (  
9.     EngineerID INT IDENTITY,  
10.     EngineerName VARCHAR(15),  
11.     BelongsTo VARCHAR(15)  
12. )  
13. --Prepare the data  
14. ;With Cte As  
15. (  
16.     Select   
17.         Id=1  
18.         ,EngineerName='Engineer' + CAST( 1 As Varchar(50))  
19.         ,BelongsTo = Cast ('Shanghai' As Varchar(50))  
20.     Union All  
21.     Select   
22.         Id +1   
23.         ,EngineerName= 'Engineer' + CAST( Id+1 As Varchar(50))   
24.         ,BelongsTo = Cast(Choose(((ID%5)+1),'Shanghai','Henan','Jiangsu','Chongqing','Zhejiang')As Varchar(50))  
25.               
26.     From Cte   
27.     Where Id < 2000000  
28. )  
29. --Insert the record  
30. Insert into tbl_Engineers_WithColumnStore  
31. Select EngineerName,BelongsTo from Cte  
32. Option (Maxrecursion 0)  
33.   
34. -- Creating clustered index  
35. CREATE CLUSTERED INDEX IX_EngineerID ON tbl_Engineers_WithColumnStore(EngineerID)  
36. -- Creating column store nonclustered index  
37. CREATE NONCLUSTERED COLUMNSTORE INDEX  IX_CS_BelongsTo ON tbl_Engineers_WithColumnStore(BelongsTo)  

对上面两张表，都塞入了200万笔记录，下面开始性能测试:
执行下面的查询

查询1：不带列存储索引

查询2：带列存储索引

通过上面的两个查询，可以看出带与不带列存储索引执行的性能差异，很显然，带的时候无论在逻辑读取、预读，还是SQL Server执行时间上都具有明显的性能提升。

下面看看实际的执行计划对比：

查询1：不带列存储索引

查询2：带列存储索引

通过上面的执行计划对比图，我们可以看到，传统索引的存储模式是行存储，执行模式是按行处理，即一次仅处理一行；而对于列存储，执行模式是批处理。对于两种模式的效率或资源消耗上，列存储拥有传统的行存储无法比拟的优势，不仅处理的更快，而且I/O也更少。

 

IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX的用途

如果在查询中不想使用非聚集的 columnstore 索引，我们可以使用查询提示（IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX）来解决，如下图：

从上面的执行计划中，我们可以看到使用IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX后，物理运算已经切换到Clustered Index Scan.

列存储索引的使用限制

• 不支持聚集列存储索引
  
• 列存储索引不能有包含的列
  
• 不能对计算列创建列存储索引
  
• 不支持多个非聚集列存储索引