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

        做企业应用几年来，每天与SQL Server打交道，深知日益增长的数据给OLTP效率带来的巨大影响，User反应系统慢、有时甚至卡死（通常是出现死锁）。对于有大量OLTP的应用系统来说，系统的运行效率显得尤为重要。如何使系统获得性能上的最佳用户体验，是摆在IT面前的一项艰巨任务。硬件升级、索引、高效执行的SQL、相关性能提升的配置等，往往是我们考虑的。对于拥有海量数据的数据仓库（DW）、决策支持系统（DSS）及BI等，可以想象，查询的性能是多么的至关重要。今天所讲的主题——列存储索引，即是与这海量数据中查询信息息息相关，下面一起来了解一下列存储索引。

        列存储索引对每列的数据进行分组和存储，然后联接所有列以完成整个索引。这不同于传统索引，传统索引对每行的数据进行分组和存储，然后联接所有行以完成整个索引。微软过去宣布的阿波罗计划(即SQL Server Denali)，就给数据库引擎带来了列存储索引，并在Pass大会的Demo中，利用传统索引的查询花费了一分钟多，而运行列存储索引的类似查询对数十亿行数据的表都可以立即返回结果。说到这里，可能刚刚接触这个概念的新学者都很想尽快了解关于列存储索引的方方面面，不急，下面将尽可能全面地搜集和整理关于列存储索引相关的知识，一起来学习学习。

• 行存储与列存储
  首先，让我们了解一下什么是列存储。我们知道，在SQL Server里，Page是数据存储的基本单位，而数据行是实际数据的存储单位，它们从Page Header之后就开始依次存储在Page上。这种按行在Page上存储记录的方式就是行存储。当数据是按单列而不是多行进行连续存储时，就是所谓的列存储。
  

  

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

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

• 什么是列存储索引？
  在数据页里，列存储索引存储列与行存储里的行截然相反。在常规索引中，行被存储在磁盘的页上，所有每行的索引数据都被一起保存在一页中，每列数据在一个索引中是跨所有页保留的。但是对于列存储索引，列被存储在磁盘页的单独集合上，即每列数据被保存在一起，这样每个数据页上仅只包含来自单个列的数据（如Figure 1所示）。在创建查询计划时，查询优化器会把列存储索引看作是访问数据的一个数据源，就像其他索引一样。优化的目的是改进和加快数据仓库查询处理，因为，在这样的情况下，不用读取表中所有列的数据，从而有效地减少磁盘I/O,并使更多的数据装载到内存。在列存储方案中，数据是按列被分析的。因此，数据基数越小，一列中就有越多重复的值，压缩率也就越高。列存储索引中，使用了一项名为Vertipaq的压缩引擎技术（这也是PowerPivot中的压缩引擎），它有效地压缩了索引中的数据。 

• SQL Server 列存储技术的主要特征
  分列数据格式--每次对一个列的数据进行分组和存储。 SQL Server 查询处理可以利用新的数据布局，并显著改进查询执行时间。
  加快查询结果--列存储索引由于以下原因而可更快地生成结果：
                         1、只须读取需要的列。 因此，从磁盘读到内存中、然后从内存移到处理器缓存中的数据量减少了。
                         2、列经过了高度压缩。这将减少必须读取和移动的字节数。
                         3、大多数查询并不会涉及表中的所有列。 因此，许多列从不会进入内存。 这一点与出色的压缩方法相结合，可改善缓冲池使用率，从而减少总 I/O。
                         4、高级查询执行技术以简化的方法处理列块（称为“批处理”），从而减少 CPU 使用率。
  键列--列存储索引中没有键列的概念，因此，索引中的键列数限制 (16) 不适应于列存储索引。
  聚集索引键--如果基表为聚集索引，则聚集键中的所有列必须出现在非聚集列存储索引中。 如果在 CREATE INDEX 语句中未列出聚集键中的某列，该列将自动添加到列存储索引中。
  分区--列存储索引使用表分区。 无需对表分区语法进行更改。 针对分区表的列存储索引必须与基表实现分区对齐。 因此，如果分区列为列存储索引中的一列，则非聚集列存储索引只能在已分区表上创建。
  记录大小--索引键记录大小限制（900 字节）也不适应于列存储索引。
  查询处理--除列存储索引之外，SQL Server 还引入批处理以利用数据的分列方向。 列存储结构和批处理都会提升性能，但考察性能问题时远不止考虑其中一个因素那么简单。
  表无法更新--对于 SQL Server 2012，无法更新具有列存储索引的表。 有关解决方法，请参阅最佳做法：更新列存储索引中的数据

• 数据类型
  可以在列存储索引中包括公共业务数据类型。 以下数据类型可包括在列存储索引中：
  char、varchar、nchar 和 nvarchar（varchar(max) 和 nvarchar(max) 除外） 
  int、bigint、smallint、tinyint、float（和 real）、decimal（和 numeric）（精度大于 18 位的情况除外。），money 和smallmoney
  bit
  所有日期和时间数据类型（标量大于 2 的 datetimeoffset 除外）

• 性能下降的可能性
  当将列存储索引用于大型表时，决策支持查询性能通常会有所提高，但某些查询甚至整个工作负荷的执行效果可能会更差。 通过使用基于开销的方法，查询优化器通常决定仅当其提高查询的整体性能时才使用列存储索引。 但是，优化器使用的开销模型是近似的，有时当使用列存储索引访问表比使用行存储（B 树或堆）更好时，优化器会选择使用列存储索引。 如果出现这种情况，请使用IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX 查询提示，或使用索引提示将优化器定向到行存储索引。 优化器可能还包括列存储索引的一些信息。 因此，在少数情况下，此选项可能无法解决性能问题。如果列存储索引无助于提高工作负荷性能且您无法使用索引提示来解决问题，请删除列存储索引以恢复行存储处理。
  问题领域

  SQL Server 列存储索引和基于列的查询处理针对典型的数据仓库查询进行了优化，此类查询以大型、中型以及小型事实数据表为特征，这些事实数据表以星型架构配置联接在一起，然后进行分组和聚合。 尽管事实数据表中的行数很大，但由于数据已聚合，因此这些查询通常返回相对小的结果集。 如果以下条件中的一个或多个成立，则使用列存储索引的查询性能可能较慢：
  1、因为数据没有聚合，所以结果集较大。 （返回一个大型结果集在本质上会比返回一个小结果集更慢。）
  2、没有联接、筛选或聚合。 在这种情况下，没有批处理。 因此，列存储索引的优点只限于压缩和读取较少列而已。
  3、两个大型表必须以创建大型哈希表的方法联接在一起，而这些大型哈希表在内存中放不下，必须溢出到磁盘。
  4、返回许多列，这将导致检索更多的列存储索引。
  5、列存储索引表的联接条件表包括多个列。
  如果由于这些原因之一发生用列存储处理时速度缓慢的问题，您可以使用本节前面所述的方法来解决。

• 列存储索引的限制和局限性
  基本限制：
  1、包含的列数不能超过 1024。
  2、无法聚集。 只有非聚集列存储索引才可用。
  3、不能是唯一索引。
  4、不能基于视图或索引视图创建。
  5、不能包含稀疏列。
  6、不能作为主键或外键。
  7、不能使用 ALTER INDEX 语句更改。 而应在删除后重新创建列存储索引。 （您可以使用 ALTER INDEX 禁用和重新生成列存储索引。）
  8、不能使用 INCLUDE 关键字创建。
  9、不能包括用来对索引排序的 ASC 或 DESC 关键字。 根据压缩算法对列存储索引排序。 不允许在索引中进行排序。 可能按照搜索算法对从列存储索引中选择的值进行排序，但是您必须使用 ORDER BY 子句来确保对结果集进行排序。
  10、不以传统索引的方式使用或保留统计信息。
  无法更新具有列存储索引的表
  内存受限的影响：列存储处理针对内存中处理进行了优化。 SQL Server 实现了若干机制，使得数据或大多数数据结构可以在可用内存不足时溢出到磁盘。 如果存在严重的内存限制，则处理过程将使用行存储。 在某些实例中，可能会选择列存储索引作为访问方法，但内存不足以生成所需数据结构。 通过先以列存储操作开始，然后默认为一个较慢的代码路径，在查询遇到严重内存限制时，可能会导致性能出现一定程度的降低。 任何查询的有效内存要求取决于特定的查询。生成列存储索引要求的内存量大约为：8 MB × 索引中的列数 × DOP（并行度）。通常，内存要求随着作为字符串的列的比例提高而增加。 因此，降低 DOP 可以减少生成列存储索引所需的内存。
  一些表达式的计算将比其他表达式更快：当使用列存储索引时，应使用批处理模计算某些常见表达式，而不以一次一行的模式进行计算。 除了使用列存储索引所带来的优势之外，批处理模式还将提供其他查询加速效果。 并不为批处理模式处理启用每个查询执行运算符。
  列存储索引不支持 SEEK：如果查询应返回行的一小部分，则优化器不大可能选择列存储索引（例如：needle-in-the-haystack 类型查询）。 如果使用表提示 FORCESEEK，则优化器将不考虑列存储索引。
  列存储索引不能与以下功能结合使用：页和行压缩以及 vardecimal 存储格式（列存储索引已采用不同格式压缩）；复制；更改跟踪；变更数据捕获；文件流

注：本篇blog主要参考MSDN：http://msdn.microsoft.com/zh-cn/library/gg492088(v=SQL.110).aspx#Update 及国外的一些blog（有些地方翻译地不太好，不过大致感觉一下其大概意思也好），后续主要从实例方面学习。