理解列存储索引

 

版权声明：原创作品，谢绝转载！否则将追究法律责任。            

   

优点和使用场景

SQL Server 内存中列存储索引通过使用基于列的数据存储和基于列的查询处理来存储和管理数据。 列存储索引适合于主要执行大容量加载和只读查询的数据仓库工作负荷。 与传统面向行的存储方式相比，使用列存储索引存档可最多提高 10 倍查询性能，与使用非压缩数据大小相比，可提供多达 7 倍数据压缩率。

SQL 2012和SQL 2014列存储索引的比较

在SQL server 2012中，一旦启用了列存储索引，将不能够对已启用列存储索引的数据存储执行变更写入操作，也就是说列存储索引适合查询只读的或者基本不发生变化的存档数据。一旦对表添加了列存储索引，表就变成了只读的了。如果我们需要进行Insert、Update、Delete等操作，我们需要先禁用列存储索引，然后进行表操作，最后再重建列存储索引。这个特性，使得列存储索引更适合存放静态数据的数据仓库。

但是这一情况在SQL server 2014中已经有所改善。该技术能够将数据仓库的查询性能提升数倍。不同于普通的索引或堆采用B-Tree结构(以行的形式)，列存储索引以列的形式存储数据，并使用主动压缩以减少查询请求所需的磁盘I/O。但是在SQL Server 2012中列存储索引仍有很多限制，SQL Server 2014中增强了该技术，并打破之前版本的限制。在SQL Server 2014创建列存储索引时不会影响表的写入功能，这意味着用户可以在一个有列存储索引的表中发出INSERT、UPDATE、DELETE等指令。SQL Server 2012中用户在表里只能创建非聚簇列存储索引，在单一索引中包含表中所有列。SQL Server 2014增加了对创建聚簇列存储索引的支持。用户仍然可以像在SQL Server 2012中那样创建一个非聚簇列存储索引，但是这个非聚簇列存储索引是只读查询，无法更新。只有聚簇列存储索引才可以更新。

参考资料：

http://tech.it168.com/a2013/0829/1527/000001527798.shtml

行存储的缺点

SQL Server在处理查询时通常是提取全部索引页，包括查询中用不到的那部分，也就是说，读取所有列的记录，即便是有些不需要。把不需要的数据读取出来，不仅浪费大量内存，同时也增大I/O，对系统的整体性能有着很大的影响。

列存储的优势

假定我们有一张有3列数据的表，这3列从左往右依次是int、varchar和bool类型，并且该表有100条（行）记录。对于都是int类型的第一列数据，应用压缩算法是很容易的，同时压缩率也会很高。这也将表明，我们不必访问该表的所有列，仅需访问感兴趣的相关列的子集，这从另一方面来讲，可以减少磁盘的I/O、提升缓存，因此，磁盘存储会被更加高效低利用，就像索引维护一样。

列存储索引查询示意图

   

图一（微软官方）

 

图二（来源网络）

来源：http://www.uml.org.cn/sjjm/201212141.asp

列存储索引能够提供更快查询的三个原因（来源网络）

第一个原因：因为索引中的列数据被压缩了，它占用的空间更小，因此SQL Server只需扫描更少的页。

第二个原因，因为只需要扫描更少的页，SQL Server就可以把它们保留在内存中，大大地提高了数据停留在内存缓冲区的可能性。因此，在那些内存大的系统中，你会看到更大的性能差异，尤其是当索引被扫描多次并且相应的行存储索引不能装入内存时更明显。

第三，SQL Server只需要提取查询中用到的列的索引页，而常规索引需要提取全部索引页，包括查询中用不到的那部分。因此，你又一次得到了益处，更少的页需要处理，就会更快得到结果，同样SQL Server也更可能把列缓存保持在内存缓存区中。