列存储索引
xVelocity 内存优化的列存储索引，对每列的数据进行分组和存储，然后联接所有列以完成整个索引。 这不同于传统索引，传统索引对每行的数据进行分组和存储，然后联接所有行以完成整个索引。 对于某些查询类型，SQL Server 查询处理器可以利用列存储布局来显著改善查询执行时间。 随着数据仓库、决策支持和商业智能应用爆炸式增长，迫切需要快速读取和处理极其大量的数据集并准确地将其转换为有用的信息和知识。 鉴于数据卷的这种增长和不断提升的预期，通过逐步提高性能来进行维护或改善正在成为一个难题。 SQL Server 列存储索引技术尤其适用于典型的数据仓库数据集。 列存储索引可以通过为常见数据仓库查询（如筛选、聚合、分组和星型联接查询）提供更快的性能，以转变用户的数据仓库体验。
内容
基础知识
基础知识：列存储索引简介
基础知识：列存储索引的限制和局限性
演示示例：具有已分区表的列存储索引
基础知识：典型的列存储索引方案
基础知识：位图筛选器优化
最佳做法
最佳做法：更新列存储索引中的数据
最佳做法：选择列存储索引的列
最佳做法：已分区表
操作指南
如何创建列存储索引
如何确定列存储索引的大小
如何排除列存储索引的性能问题
基础知识：列存储索引简介
SQL Server 数据库引擎中的列存储索引可用于显著加快常见数据仓库查询的处理时间。 典型的数据仓库工作负荷涉及汇总大量数据。 在数据仓库和决策支持系统中通常用于提高性能的技术包括预先计算的汇总表、索引视图、OLAP 多维数据集等。 尽管这些技术可极大提高查询处理的速度，但这些技术可能不灵活、难于维护并且必须针对每个查询问题进行专门设计。
例如，请考虑一个具有维度键列 dk1 和 dk2 的事实数据表 F1。 让 M 成为聚合函数，如 SUM。 请勿在每次运行引用 M(dk1) 的查询时针对列 dk1 计算 M，而是可以创建并使用一个摘要表 F2(dk1, M)，以便可预先计算结果并且更快地执行查询。 但是，如果需要一个引用 M(dk2) 的新查询，则必须使用此信息创建新的摘要表 F3(dk2, M)。 当表中的列数增加并且有许多可能的函数时，此方法会变得难于维护，且不易涵盖所有需要的查询。
这一开销对用户来说很大。 通过使用 SQL Server 列存储索引，用户可以减少其他解决方案的开销。 列存储索引还使查询能够快速计算结果，以致无需预先计算。
SQL Server 列存储技术的主要特征如下所示：
分列数据格式 – 与传统的基于行的数据组织方式（称为“行存储”格式）不同，在具有列存储索引的分列数据库系统（如 SQL Server）中，每次对一个列的数据进行分组和存储。 SQL Server 查询处理可以利用新的数据布局，并显著改进查询执行时间。
加快查询结果 – 列存储索引由于以下原因而可更快地生成结果：
只必须读取需要的列。 因此，从磁盘读到内存中、然后从内存移到处理器缓存中的数据量减少了。
列经过了高度压缩。 这将减少必须读取和移动的字节数。
大多数查询并不会涉及表中的所有列。 因此，许多列从不会进入内存。 这一点与出色的压缩方法相结合，可改善缓冲池使用率，从而减少总 I/O。
高级查询执行技术以简化的方法处理列块（称为“批处理”），从而减少 CPU 使用率。
键列 – 列存储索引中没有键列的概念，因此，索引中的键列数限制 (16) 不适应于列存储索引。
聚集索引键 – 如果基表为聚集索引，则聚集键中的所有列必须出现在非聚集列存储索引中。 如果在 CREATE INDEX 语句中未列出聚集键中的某列，该列将自动添加到列存储索引中。
分区 – 列存储索引使用表分区。 无需对表分区语法进行更改。 针对分区表的列存储索引必须与基表实现分区对齐。 因此，如果分区列为列存储索引中的一列，则非聚集列存储索引只能在已分区表上创建。
记录大小 – 索引键记录大小限制（900 字节）也不适应于列存储索引。
查询处理 – 除列存储索引之外，SQL Server 还引入批处理以利用数据的分列方向。 列存储结构和批处理都会提升性能，但考察性能问题时远不止考虑其中一个因素那么简单。
表无法更新 – 对于 SQL Server 2012，无法更新具有列存储索引的表。 有关解决方法，请参阅最佳做法：更新列存储索引中的数据
有关如何创建列存储索引的语法，请参阅 CREATE COLUMNSTORE INDEX (Transact-SQL)。
数据类型
可以在列存储索引中包括公共业务数据类型。 以下数据类型可包括在列存储索引中。
char 和varchar
nchar 和 nvarchar（varchar(max) 和 nvarchar(max) 除外）
decimal（和 numeric）（精度大于 18 位的情况除外。）
int、bigint、smallint 和 tinyint
float（和 real）
bit
money 和smallmoney
所有日期和时间数据类型（标量大于 2 的 datetimeoffset 除外）
以下数据类型不能包括在列存储索引中：
binary 和varbinary
ntext、text 和 image
varchar(max) 和nvarchar(max)
uniqueidentifier
rowversion（和 timestamp）
sql_variant
精度大于 18 位的 decimal（和 numeric）
标量大于 2 的 datetimeoffset
CLR 类型（hierarchyid 和空间类型）
xml
性能下降的可能性
当将列存储索引用于大型表时，决策支持查询性能通常会有所提高，但某些查询甚至整个工作负荷的执行效果可能会更差。 通过使用基于开销的方法，查询优化器通常决定仅当其提高查询的整体性能时才使用列存储索引。 但是，优化器使用的开销模型是近似的，有时当使用列存储索引访问表比使用行存储（B 树或堆）更好时，优化器会选择使用列存储索引。 如果出现这种情况，请使用 IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX 查询提示，或使用索引提示将优化器定向到行存储索引。 优化器可能还包括列存储索引的一些信息。 因此，在少数情况下，此选项可能无法解决性能问题。 如果列存储索引无助于提高工作负荷性能且您无法使用索引提示来解决问题，请删除列存储索引以恢复行存储处理。
问题领域
SQL Server 列存储索引和基于列的查询处理针对典型的数据仓库查询进行了优化，此类查询以大型、中型以及小型事实数据表为特征，这些事实数据表以星型架构配置联接在一起，然后进行分组和聚合。 尽管事实数据表中的行数很大，但由于数据已聚合，因此这些查询通常返回相对小的结果集。 如果以下条件中的一个或多个成立，则使用列存储索引的查询性能可能较慢。
因为数据没有聚合，所以结果集较大。 （返回一个大型结果集在本质上会比返回一个小结果集更慢。）
没有联接、筛选或聚合。 在这种情况下，没有批处理。 因此，列存储索引的优点只限于压缩和读取较少列而已。
两个大型表必须以创建大型哈希表的方法联接在一起，而这些大型哈希表在内存中放不下，必须溢出到磁盘。
返回许多列，这将导致检索更多的列存储索引。
列存储索引表的联接条件表包括多个列。
如果由于这些原因之一发生用列存储处理时速度缓慢的问题，您可以使用本节前面所述的方法来解决。
返回页首
基础知识：列存储索引的限制和局限性
基本限制
列存储索引：
包含的列数不能超过 1024。
无法聚集。 只有非聚集列存储索引才可用。
不能是唯一索引。
不能基于视图或索引视图创建。
不能包含稀疏列。
不能作为主键或外键。
不能使用 ALTER INDEX 语句更改。 而应在删除后重新创建列存储索引。 （您可以使用 ALTER INDEX 禁用和重新生成列存储索引。）
不能使用 INCLUDE 关键字创建。
不能包括用来对索引排序的 ASC 或 DESC 关键字。 根据压缩算法对列存储索引排序。 不允许在索引中进行排序。 可能按照搜索算法对从列存储索引中选择的值进行排序，但是您必须使用 ORDER BY 子句来确保对结果集进行排序。
不以传统索引的方式使用或保留统计信息。
不能包含具有 FILESTREAM 属性的列。 表中未在索引中使用的其他列可以包含 FILESTREAM 属性。
无法更新具有列存储索引的表
若要解决此问题，请参阅最佳做法：更新列存储索引中的数据。
内存受限的影响
列存储处理针对内存中处理进行了优化。 SQL Server 实现了若干机制，使得数据或大多数数据结构可以在可用内存不足时溢出到磁盘。 如果存在严重的内存限制，则处理过程将使用行存储。 在某些实例中，可能会选择列存储索引作为访问方法，但内存不足以生成所需数据结构。 通过先以列存储操作开始，然后默认为一个较慢的代码路径，在查询遇到严重内存限制时，可能会导致性能出现一定程度的降低。 任何查询的有效内存要求取决于特定的查询。 生成列存储索引要求的内存量大约为：8 MB × 索引中的列数 × DOP（并行度）。通常，内存要求随着作为字符串的列的比例提高而增加。 因此，降低 DOP 可以减少生成列存储索引所需的内存。
一些表达式的计算将比其他表达式更快
当使用列存储索引时，应使用批处理模计算某些常见表达式，而不以一次一行的模式进行计算。 除了使用列存储索引所带来的优势之外，批处理模式还将提供其他查询加速效果。 并不为批处理模式处理启用每个查询执行运算符。
列存储索引不支持 SEEK
如果查询应返回行的一小部分，则优化器不大可能选择列存储索引（例如：needle-in-the-haystack 类型查询）。 如果使用表提示 FORCESEEK，则优化器将不考虑列存储索引。
列存储索引不能与以下功能结合使用：
页和行压缩以及 vardecimal 存储格式（列存储索引已采用不同格式压缩）。
复制
更改跟踪
变更数据捕获
文件流
返回页首
演示示例：具有已分区表的列存储索引
本主题中的示例使用在 AdventureWorksDW2012 示例数据库中创建的名为 FactResellerSalesPtnd 的分区表。 若要测试分区表中的列存储索引，请连接到 AdventureWorksDW2012 数据库，并执行以下代码以创建事实数据表的分区版本。
.gif "注意") 注意 |
|---|
有关示例数据库的信息和如何下载该数据库的说明，请参阅 AdventureWorks 示例数据库。 |
创建 FactResellerSalesPtnd 表
执行以下代码以创建名为 FactResellerSalesPtnd 的 FactResellerSales 表的分区版本。
USE AdventureWorksDW2012; GO CREATE PARTITION FUNCTION [ByOrderDateMonthPF](int) AS RANGE RIGHT FOR VALUES ( 20050701, 20050801, 20050901, 20051001, 20051101, 20051201, 20060101, 20060201, 20060301, 20060401, 20060501, 20060601, 20060701, 20060801, 20060901, 20061001, 20061101, 20061201, 20070101, 20070201, 20070301, 20070401, 20070501, 20070601, 20070701, 20070801, 20070901, 20071001, 20071101, 20071201, 20080101, 20080201, 20080301, 20080401, 20080501, 20080601, 20080701, 20080801, 20080901, 20081001, 20081101, 20081201 ) GO CREATE PARTITION SCHEME [ByOrderDateMonthRange] AS PARTITION [ByOrderDateMonthPF] ALL TO ([PRIMARY]) GO -- Create a partitioned version of the FactResellerSales table CREATE TABLE [dbo].[FactResellerSalesPtnd]( [ProductKey] [int] NOT NULL, [OrderDateKey] [int] NOT NULL, [DueDateKey] [int] NOT NULL, [ShipDateKey] [int] NOT NULL, [CustomerKey] [int] NOT NULL, [EmployeeKey] [int] NOT NULL, [PromotionKey] [int] NOT NULL, [CurrencyKey] [int] NOT NULL, [SalesTerritoryKey] [int] NOT NULL, [SalesOrderNumber] [nvarchar](20) NOT NULL, [SalesOrderLineNumber] [tinyint] NOT NULL, [RevisionNumber] [tinyint] NULL, [OrderQuantity] [smallint] NULL, [UnitPrice] [money] NULL, [ExtendedAmount] [money] NULL, [UnitPriceDiscountPct] [float] NULL, [DiscountAmount] [float] NULL, [ProductStandardCost] [money] NULL, [TotalProductCost] [money] NULL, [SalesAmount] [money] NULL, [TaxAmt] [money] NULL, [Freight] [money] NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [CustomerPONumber] [nvarchar](25) NULL, OrderDate [datetime] NULL, DueDate [datetime] NULL, ShipDate [datetime] NULL ) ON ByOrderDateMonthRange(OrderDateKey); GO -- Using simple or bulk logged recovery mode, and then the TABLOCK -- hint on the target table of the INSERT…SELECT is a best practice -- because it causes minimal logging and is therefore much faster. ALTER DATABASE AdventureWorksDW2012 SET RECOVERY SIMPLE; GO -- Copy the data from the FactResellerSales into the new table INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK) SELECT * FROM dbo.FactResellerSales; GO -- Create the columnstore index CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_FactResellerSalesPtnd] ON [FactResellerSalesPtnd] ( [ProductKey], [OrderDateKey], [DueDateKey], [ShipDateKey], [CustomerKey], [EmployeeKey], [PromotionKey], [CurrencyKey], [SalesTerritoryKey], [SalesOrderNumber], [SalesOrderLineNumber], [RevisionNumber], [OrderQuantity], [UnitPrice], [ExtendedAmount], [UnitPriceDiscountPct], [DiscountAmount], [ProductStandardCost], [TotalProductCost], [SalesAmount], [TaxAmt], [Freight], [CarrierTrackingNumber], [CustomerPONumber], [OrderDate], [DueDate], [ShipDate] );
现在，执行可从列存储索引受益的查询，并确认使用了列存储索引。
测试列存储索引
按下 Ctrl+M，或者在**“查询”菜单上，选择“包括实际的执行计划”**。 这将启用 SQL Server Management Studio 使用的实际执行计划的图形表示形式。
在“查询编辑器”窗口中，执行以下查询。
SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory FROM FactResellerSalesPtnd GROUP BY SalesTerritoryKey;在**“结果”窗口中的“执行计划”**选项卡下，确认查询计划选择了非聚集 csindx_FactResellerSalesPtnd 索引的索引扫描。
注意有关图形显示计划图标的详细信息，请参阅Showplan 逻辑运算符和物理运算符参考。
返回页首
基础知识：典型的列存储索引方案
星型或雪花型数据库架构通常用在维度数据仓库和数据市场中，其中数据检索的速度比数据操作的效率更重要。 SQL Server 2012 中的列存储技术可以检测和加快针对星型和雪花型架构的查询。
示例：
| 注意 |
|---|
以下示例中可能不使用批处理，因为这些表不够大。 批处理执行模式（如并行处理）仅用于更消耗资源的查询。 |
A：联接两个表的聚合查询
请考虑一个星型联接查询，它用于计算每个季度卖出多少件产品 215。 名为 FactResellerSalesPtnd 的事实数据表已在 OrderDateKey 列上进行了分区。 其中一个名为 DimDate 的维度表通过日期键中的主键-外键关系链接到事实数据表。
SELECT d.CalendarYear, d.CalendarQuarter, COUNT(*) AS NumberSold FROM dbo.FactResellerSalesPtnd AS f JOIN dbo.DimDate AS d ON f.OrderDateKey = d.DateKey WHERE ProductKey = 215 GROUP BY d.CalendarYear, d.CalendarQuarter ORDER BY d.CalendarYear, d.CalendarQuarter;
此外，将只存在以单个表作为目标的查询。 在此类情况下，SQL Server 也尝试利用批处理执行的能力和列存储技术以加快查询执行。
B：针对单个表的简单聚合查询
事实数据表为 FactResellerSalesPtnd，且该表已针对 OrderDateKey 列进行了分区。 以下查询返回行数和订单数。
SELECT COUNT(*) AS NumberOfRows, COUNT(DISTINCT(f.SalesOrderNumber)) AS NumberOfOrders FROM dbo.FactResellerSalesPtnd AS f;对于典型的数据仓库方案查询，如果在查询执行过程中使用列存储索引和批处理执行模式，则通常查询速度将提高 1.5 倍到 10 倍。 对于某些星型联接查询，速度增加要高得多。
基础知识：位图筛选器优化
除了以分列格式显示数据的布局之外，SQL Server 还使用位图筛选器，它们可在查询执行过程传递到存储引擎以改善性能。 位图筛选器通过在实现任何联接之前减少所包括的行数，并因此减少联接运算符处理的行数，从而加快查询执行速度。 位图是在哈希联接的生成端创建的，但实际位图检查是在哈希联接的探测端执行的。 您可以通过使用图形或 XML 执行计划观察位图筛选器的使用。
最佳做法：更新列存储索引中的数据
无法更新具有列存储索引的表。 有三种解决此问题的方法。
若要更新具有列存储索引的表，先删除列存储索引，执行任何所需的 INSERT、DELETE、UPDATE 或 MERGE 操作，然后重新生成列存储索引。
对表进行分区并切换分区。 对于大容量插入，先将数据插入到一个临时表中，在临时表上生成列存储索引，然后将此临时表切换到空分区。 对于其他更新，将主表外的一个分区切换到一个临时表中，禁用或删除临时表上的列存储索引，执行更新操作，在临时表上重新生成或重新创建列存储索引，然后将临时表切换回主表。
将静态数据放到具有列存储索引的主表中，将新数据和可能要更改的最新数据放入具有相同架构但没有列存储索引的单独表中。 将更新应用到具有最新数据的表。 若要查询数据，请将查询重写为两个查询，一个查询针对一个表，然后将两个结果集与 UNION ALL 组合。 针对大型主表的子查询将受益于列存储索引。 如果可更新的表小得多，则缺乏列存储索引不会对性能产生多大影响。 尽管您可以查询由对两个表执行 UNION ALL 操作所生成的视图，但您可能看不到明显的性能优势。 性能将取决于查询计划，而查询计划取决于查询、数据和基数估计。 使用视图的优势在于：视图上的 INSTEAD OF 触发器可以将更新转向没有列存储索引的表，并且视图机制对于用户和应用程序是透明的。 如果您将上述任一方法与 UNION ALL 结合使用，则可测试典型查询的性能，并决定使用此方法得到的便利是否大于性能优势损失。
| 注意 |
|---|
请勿将列存储索引创建为一种使表成为只读表的机制。 在将来的版本中，不能保证限制对具有列存储索引的表进行更新。 当需要只读行为时，应通过创建只读文件组并将表移到此文件组中来强制执行。 |
最佳做法：选择列存储索引的列
列存储索引的某些性能优势源自压缩技术，这种技术可减少处理查询时必须读取和操作的数据页数。 压缩最适合具有大量重复值的字符或数字列。 例如，维度表可能具有对应于邮政编码、市/县和销售区域的列。 如果每个市/县有许多邮政编码，并且每个销售区域有许多市/县，则销售区域列的压缩程度最大，市/县列次之，邮政编码列最小。 尽管所有列都适合实现列存储索引，但如果将销售区域代码列添加到列存储索引，则将从列存储压缩中获得最大优势，而邮政编码将获得最小优势。
返回页首
最佳做法：已分区表
列存储索引旨在支持非常大的数据仓库方案中的查询，其中分区是常见现象。 如果具有列存储索引的表中的数据必须定期更新时，则建议使用分区。 有关如何更新列存储索引的分区的详细信息，请参阅前面的最佳做法：更新列存储索引中的数据一节。
返回页首
如何创建列存储索引
创建列存储索引类似于创建任何其他索引。 可以通过使用 Transact-SQL 或使用 SQL Server Management Studio 图形工具创建列存储索引。
使用 Transact-SQL 创建列存储索引
- 在“查询编辑器”窗口中，执行 CREATE COLUMNSTORE INDEX 语句。 有关示例，请参阅上面的创建 FactResellerSalesPtnd 表。 有关详细信息，请参阅 CREATE COLUMNSTORE INDEX (Transact-SQL)。
使用 SQL Server Management Studio 创建列存储索引
通过 Management Studio，使用对象资源管理器连接到 SQL Server 数据库引擎实例。
在对象资源管理器中，展开 SQL Server 实例，依次展开**“数据库”、数据库、表，右键单击表，指向“新建索引”，然后单击“非聚集列存储索引”**。
在**“常规”选项卡之下的“索引名称”对话框中，键入新索引的名称，然后单击“添加”**。
在**“选择列”对话框中，选择参与列存储索引的列，然后单击两次“确定”**以创建该索引。
如何确定列存储索引的大小
列存储索引同时包含段落和词典。 下面的示例演示如何通过合并 sys.column_store_segments 和 sys.column_store_dictionaries 中的 on_disk_size 列来确定列存储索引（在 FactResellerSalesPtnd 表上）的总大小。
SELECT SUM(on_disk_size_MB) AS TotalSizeInMB
FROM
(
(SELECT SUM(css.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
FROM sys.indexes AS i
JOIN sys.partitions AS p
ON i.object_id = p.object_id
JOIN sys.column_store_segments AS css
ON css.hobt_id = p.hobt_id
WHERE i.object_id = object_id('FactResellerSalesPtnd')
AND i.type_desc = 'NONCLUSTERED COLUMNSTORE')
UNION ALL
(SELECT SUM(csd.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
FROM sys.indexes AS i
JOIN sys.partitions AS p
ON i.object_id = p.object_id
JOIN sys.column_store_dictionaries AS csd
ON csd.hobt_id = p.hobt_id
WHERE i.object_id = object_id('FactResellerSalesPtnd')
AND i.type_desc = 'NONCLUSTERED COLUMNSTORE')
) AS SegmentsPlusDictionary
如何排除列存储索引的性能问题
若要确定是否正在使用列存储索引，请检查查询执行计划。 只有存在三个元素才能获得最大优势。
查询执行计划中存在列存储索引。
.gif "Columnstore Index Scan")
列存储索引扫描运算符图标
如果当前未使用列存储索引，并且您认为列存储索引可以给查询带来好处，则在通过使用 WITH (INDEX(<indexname>)) 提示强制使用列存储索引的同时评估查询性能。 下面的示例演示一个使用索引提示的查询。
SELECT d.CalendarYear, d.CalendarQuarter, COUNT(*) AS NumberSold FROM dbo.FactResellerSalesPtnd AS f WITH (INDEX(csindx_FactResellerSalesPtnd)) JOIN dbo.DimDate AS d ON f.OrderDateKey = d.DateKey WHERE ProductKey = 215 GROUP BY d.CalendarYear, d.CalendarQuarter ORDER BY d.CalendarYear, d.CalendarQuarter;当您将指针移到图形查询计划中列存储索引图标的上方时，实际执行模式将作为批处理（而不是行）列出。
位图物理运算符图标存在于图形执行计划中，指示位图筛选器在执行联接操作之前减少了行数。
.gif "Bitmap 运算符图标")
位图运算符图标
返回页首
相关任务
CREATE COLUMNSTORE INDEX (Transact-SQL)