SQLServer 2012 列存储索引

SQL Server 数据库引擎中的列存储索引可用于显著加快常见数据仓库查询的处理时间。 典型的数据仓库工作负荷涉及汇总大量数据。 在数据仓库和决策支持系统中通常用于提高性能的技术包括预先计算的汇总表、索引视图、OLAP 多维数据集等。 尽管这些技术可极大提高查询处理的速度，但这些技术可能不灵活、难于维护并且必须针对每个查询问题进行专门设计。

数据类型的限制：

◆ char 和varchar
◆ nchar 和 nvarchar（varchar(max) 和 nvarchar(max) 除外）
◆ decimal （和 numeric）（精度大于 18 位的情况除外。）
◆ int 、bigint、smallint 和 tinyint
◆ float （和 real）
◆ bit
◆ money 和smallmoney
◆ 所有日期和时间数据类型（标量大于 2 的 datetimeoffset 除外）

索引对象限制：

◆ 包含的列数不能超过 1024。
◆ 无法聚集。 只有非聚集列存储索引才可用。
◆ 不能是唯一索引。
◆ 不能基于视图或索引视图创建。
◆ 不能包含稀疏列。
◆ 不能作为主键或外键。
◆ 不能使用 ALTER INDEX 语句更改。 而应在删除后重新创建列存储索引。 （您可以使用 ALTER INDEX 禁用和重新生成列存储索引。）
◆ 不能使用 INCLUDE 关键字创建。
◆ 不能包括用来对索引排序的 ASC 或 DESC 关键字。 根据压缩算法对列存储索引排序。 不允许在索引中进行排序。 可能按照搜索算法对从列存储索引中选择的值进行排序，但是您必须使用 ORDER BY 子句来确保对结果集进行排序。
◆ 不以传统索引的方式使用或保留统计信息。
◆ 不能包含具有 FILESTREAM 属性的列。 表中未在索引中使用的其他列可以包含 FILESTREAM 属性。

列存储索引不能与以下功能结合使用：

◆ 页和行压缩以及 vardecimal 存储格式（列存储索引已采用不同格式压缩）。
◆ 复制
◆ 更改跟踪
◆ 变更数据捕获
◆ 文件流

影响列存储索引的查询性能条件：

◆ 因为数据没有聚合，所以结果集较大。 （返回一个大型结果集在本质上会比返回一个小结果集更慢。）
◆ 没有联接、筛选或聚合。 在这种情况下，没有批处理。 因此，列存储索引的优点只限于压缩和读取较少列而已。
◆ 两个大型表必须以创建大型哈希表的方法联接在一起，而这些大型哈希表在内存中放不下，必须溢出到磁盘。
◆ 返回许多列，这将导致检索更多的列存储索引。
◆ 列存储索引表的联接条件表包括多个列。

更多参考：列存储索引

列存储索引创建语法：CREATE COLUMNSTORE INDEX (Transact-SQL)

CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name 
    ON <object> ( column  [ ,...n ] )
    [ WITH ( <column_index_option> [ ,...n ] ) ]
    [ ON {
           { partition_scheme_name ( column_name ) } 
           | filegroup_name 
           | "default" 
         }
    ]
[ ; ]

<object> ::=
{
    [database_name. [schema_name ] . | schema_name . ]
     table_name
{

<column_index_option> ::=
{
      DROP_EXISTING = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
 }

创建测试：

创建表并创建列存储索引：

--	DROP TABLE DemoTab
select * into DemoTab from sys.sysobjects

select * from DemoTab

CREATE NONCLUSTERED COLUMNSTORE INDEX CIX_DemoTab ON DBO.DemoTab (name)
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX CIX_DemoTab 
ON DBO.DemoTab (name)
WITH (DROP_EXISTING = ON ,MAXDOP = 0)
ON "default"
GO

查看列索引数据存储类型：

DBCC TRACEON(3604,-1)
DBCC IND('DemoDB','DemoTab',-1)

可以看到，列存储索引是存储在 大对象数据页（ LOB data）上的。

查看索引列的IO情况：

set statistics io on

select name from DemoTab
select name from DemoTab where name = 'sysfos'
select name from DemoTab order by name
select name,COUNT(*) from DemoTab group by name

上面的几个查询中，逻辑读都为10，而且都使用了列存储索引。如果不想属于列存储索引，可以设置查询提示 IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX

select name from DemoTab option(IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)
select name from DemoTab where name = 'sysfos' option(IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)
select name from DemoTab order by name option(IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)
select name,COUNT(*) from DemoTab group by name option(IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

此时发现，不使用列存储时IO反而变小了！性能并没有提升！原因是数据量太少了！

列存储表中插入数据：

现在增加更多数据，提示以下错误

insert into DemoTab select * from DemoTab

Msg 35330, Level 15, State 1, Line 1
INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

列存储中是禁止插入数据的，这就使该表为只读访问。因此列存储的使用一般在数据仓库或其他静态表中使用。

若就行插入数据，必须把索引禁止，插入数据后在重建索引。

alter index CIX_DemoTab on DBO.DemoTab disable
go
insert into DemoTab select * from DemoTab
go 10	 --重复10次
alter index CIX_DemoTab on DBO.DemoTab rebuild
go

此时在对比使用列存储索引和不使用的区别：

set statistics io on

select name from DemoTab
select name from DemoTab option(IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

这时查看，IO的差异就非常明显了！使用列存储省去了很多IO读取，也省下了内存空间的使用。

复合列的列存储索引：

刚才测试列存储索引只使用单列字段，现在使用复合字段。

DROP INDEX CIX_DemoTab ON DBO.DemoTab
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX CIX_DemoTab ON DBO.DemoTab (id,name,crdate)
GO

执行以下查询。

select id from DemoTab
select name from DemoTab
select crdate from DemoTab
select id,name,crdate from DemoTab

其实没列的IO已经确定了，查询列存储中的索引列时，IO为各个列IO的总和。通过上面的执行，可以发现， 使用列存储时都是使用列存储扫描，因为查询需要查询整列数据。

若使用任何不在列存储中的列，将不会使用列存储，IO将可能增大很多！如下

select id,name,crdate from DemoTab WHERE id = 79 and xtype = 'U'
select id,name,crdate,xtype from DemoTab WHERE id = 79

按列查询IO那么好，把所有列都创建索引会怎样呢？？

DROP INDEX CIX_DemoTab ON DBO.DemoTab
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX CIX_DemoTab 
ON DBO.DemoTab (name,id,xtype,uid,info,status,base_schema_ver,replinfo
,parent_obj,crdate,ftcatid,schema_ver,stats_schema_ver,type,userstat,
sysstat,indexdel,refdate,version,deltrig,instrig,updtrig,seltrig,category,cache)
GO

select * from DemoTab
select * from DemoTab option(IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

IO果然都很少了！无论怎么查询，按列查数据IO都比之前的少！

但是如果用聚集索引！还是聚集索引有优势！~

更多参考：列存储索引