大网数据广告活动
数据库与应用安全

列存储索引 - 查询性能

· 52CE 我爱测

列存储索引对于分析和数据仓库工作负荷最多可提高 100 倍性能,并且比传统行存储索引最多可提高 10 倍的数据压缩率。 这些建议可帮助查询实现列存储索引旨在提供的快速查询性能。 结尾处提供了有关列存储性能的进一步说明。

适用对象:yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库

有助于实现列存储索引旨在提供的快速查询性能的建议。

列存储索引对于分析和数据仓库工作负荷最多可提高 100 倍性能,并且比传统行存储索引最多可提高 10 倍的数据压缩率。 这些建议可帮助查询实现列存储索引旨在提供的快速查询性能。 结尾处提供了有关列存储性能的进一步说明。

提高查询性能的建议

以下是有助于实现列存储索引旨在提供的高性能的一些建议。

1.组织数据使更多行组不用进行全表扫描

  • 利用插入顺序。 通常情况下,在传统数据仓库中,数据实际上是按时间顺序插入的,而分析是在时间维度中完成的。 例如,按季度分析销售额。 对于此类型的工作负荷,行组消除自动发生。 在 SQL Server 2016 (13.x) 中,可以找出在查询处理过程中跳过的数字行组。

  • 利用行存储聚集索引。 如果常见的查询谓词在某一列(如 C1)上,而该列与行的插入顺序无关,则可在列 C1 上创建一个行存储聚集索引,再通过删除行存储聚集索引来创建聚集列存储索引。 如果使用 MAXDOP = 1 显式创建聚集列存储索引,则得到的聚集列存储索引会在 C1 列上完美排序。 如果指定了 MAXDOP = 8,则会出现值在 8 个行组中重叠。 此策略的一个常见示例是当你最初使用大型数据集创建列存储索引时。 请注意,对于非聚集列存储索引 (NCCI),如果基本行存储表具有聚集索引,则行已经排序。 在这种情况下,生成的非聚集列存储索引将自动进行排序。 要注意的重要一点是,列存储索引本身不维护行的顺序。 当插入新行或更新旧行时,你可能需要重复该过程,因为分析查询性能可能会降低

  • 利用表分区。 可以对列存储索引进行分区,然后使用分区排除来减少要扫描的行组数。 例如,事实数据表存储客户的购买情况,常见的查询模式是查找特定客户的季度购买情况,可以将插入顺序与对客户列进行分区结合使用。 每个分区都会包含特定客户的按时间顺序排序的行。

2.计划足够的内存以便并行创建列存储索引

创建列存储索引默认情况下是一种并行操作,除非内存受到约束。 并行创建索引要求比按顺序创建索引更多的内存。 在内存充足的情况下,创建列存储索引相当于在同一列上生成 B 树所用时间的 1.5 倍。

创建列存储索引所需的内存取决于列数、字符串列的数目、并行度 (DOP) 和数据特性。 例如,如果表包含的行不到 100 万行,SQL Server 仅使用一个线程创建列存储索引。

如果表中包含的行超过 100 万行,但 SQL Server 无法获得足够大的内存授予来使用 MAXDOP 创建索引,SQL Server 会根据需要自动减少 MAXDOP,以便适合可用内存授予。 在某些情况下,DOP 必须减小到一个以便在受到约束的内存下生成索引。

从 SQL Server 2016 (13.x) 开始,查询始终以批处理模式运行。 在以前版本中,仅当 DOP 大于 1 时,才使用批处理执行。

说明的列存储性能

列存储索引通过将高速内存中批处理模式处理与可极大减少 I/O 要求的技术组合使用来实现高查询性能。 由于分析查询扫描大量行,它们通常进行 IO 绑定,因此在查询执行过程中减少 I/O 对于列存储索引的设计至关重要。 数据读取到内存中后,减少内存中操作的数目很重要。

列存储索引通过高数据压缩率、列存储消除、行组消除和批处理来减少 I/O 和优化内存中操作。

数据压缩

列存储索引可实现比行存储索引最多高 10 倍的数据压缩率。 这极大地减少了执行分析查询所需的 I/O,并因此可以提高查询性能。

  • 列存储索引从磁盘读取压缩的数据,这意味着需要将更少字节的数据读取到内存。

  • 列存储索引以压缩形式将数据存储在内存中,这通过减少将相同数据读取到内存的次数来减少 I/O。 例如,与以未压缩形式存储数据相比,列存储索引使用 10 倍压缩率可以在内存中保留 10 倍更多数据。 由于内存中有更多数据,因此列存储索引更有可能通过从磁盘进行更多读取在内存中找到所需的数据。

  • 列存储索引按列(而不是按行)压缩数据,这可实现高压缩率并减少磁盘上存储的数据的大小。 每个列独自压缩和存储。 列中的数据将始终具有相同的数据类型,并往往具有相似的值。 当值相似时,数据压缩技术可以很好地实现更高的压缩率。

  • 例如,如果事实数据表存储客户地址,并且有一列用于国家/地区,则可能值的总数少于 200。 其中的某些值将重复多次。 如果事实数据表具有 1 亿行,则“国家/地区”列将轻松压缩,并需要很少的存储空间。 按行压缩就不能以这种方式利用列值的相似性,并在压缩“国家/地区”列中的值时将使用更多字节。

列消除

列存储索引会跳过读取查询结果不需要的列。 这种功能(称为“列消除”)可进一步减少执行查询的 I/O,因此可以提高查询性能。

  • 列消除之所以可能是因为数据是按列组织和压缩的。 与此相反,当数据按行存储时,每行中的列值以物理方式存储在一起,并且不能轻松分离。 查询处理器要检索特定列值需要读取整个行,这会增加 I/O,因为不必要地将额外的数据读取到内存。

  • 例如,如果表有 50 列,而查询仅使用其中 5 列,列存储索引仅从磁盘中提取这 5 列。 它会跳过读取其他 45 列。 假定所有列都具有相似的大小,这可另外减少 I/O 90%。 如果相同的数据以行存储形式存储,查询处理器需要读取其他 45 列。

行组消除

在全表扫描中,大部分数据通常不匹配查询谓词条件。 列存储索引通过使用元数据能够跳过读取不包含查询结果所需数据的行组,所有这些都不需要实际 I/O。 这种功能(称为“行组消除”)可减少全表扫描的 I/O,因此可以提高查询性能。

列存储索引何时需要执行全表扫描?

从 SQL Server 2016 (13.x) 开始,可以在聚集列存储索引上创建一个或多个常规非聚集 B 树索引,就像可以在行存储堆上创建一样。 非聚集 B 树索引可以加快具有相等谓词或包含小范围值的谓词的查询速度。 对于更复杂的谓词,查询优化器可以选择全表扫描。 如果没有跳过行组的功能,全表扫描会非常耗时,特别是对于大型表更是如此。

分析查询何时从全表扫描的行组消除受益?

例如,一个零售企业已使用具有聚集列存储索引的事实数据表来为其销售数据建模。 每条新的销售数据均存储交易的各个属性,包括产品销售日期。 有趣的是,尽管列存储索引不保证排序顺序,但此表中的行会按排序日期顺序加载。 随着时间的推移,此表将会增长。 虽然零售企业可能会保留过去 10 年的销售数据,但分析查询可能只需要计算上一季度的聚合。 列存储索引只需查看日期列的元数据就可避免访问前 39 个季度的数据。 这额外地减少了 97% 读入内存并进行处理的数据量。

在全表扫描中跳过哪些行组?

为了确定要消除哪些行组,列存储索引在每个行组中使用元数据来存储每个列段的最小值和最大值。 当列段范围不满足查询谓词条件时,就会跳过整个行组而不执行任何实际 IO。 这样做之所以有效是因为数据通常是按排序顺序加载的,虽然行不一定进行排序,但相似的数据值通常位于同一行组或相邻行组中。

有关行组的详细信息,请参阅“列存储索引指南”

批处理模式执行

批处理模式执行是指为提高执行效率将一组行(通常最多 900 行)一起处理。 例如,查询 SELECT SUM (Sales) FROM SalesData 从表 SalesData 聚合了总销售额。 以批处理模式执行时,查询执行引擎以 900 个值为一组计算聚合。 这样会将元数据(访问成本和其他类型的开销)分布到批处理的所有行中,而不是支付每行的成本,从而大大减少了代码路径。 批处理模式处理在可能的情况下会对压缩数据运行,并消除了行模式处理所用的一些交换运算符。 这可以将分析查询的执行速度提高好几个数量级。

并非所有查询执行运算符都可以在批处理模式下执行。 例如,DML 操作(如 Insert、Delete 或 Update)一次执行一行。 批处理模式运算符面向可提高查询性能的运算符(如 Scan、Join、Aggregate、sort 等)。 由于列存储索引是在 SQL Server 2012 (11.x) 中引入的,因此需要付出不懈努力来扩展可以批处理模式执行的运算符。 下表按照产品版本显示了以批处理模式运行的运算符。

批处理模式运算符 何时使用此项? SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) 和 SQL Database¹ 注释
DML 操作(insert、delete、update、merge) 否 否 否 DML 不是批处理模式操作,因为它不是并行的。 即使我们启用串行模式批处理操作,允许 DML 以批处理模式处理,我们也看不到明显的收益。
columnstore index scan 扫描 不适用 是 是 对于列存储索引,我们可以将谓词推送到 SCAN 节点。
columnstore Index Scan(非聚集) 扫描 是 是 是 是
index seek 不适用 不适用 否 我们以行模式通过非聚集 B 树索引执行查找操作。
compute scalar 计算结果为标量值的表达式。 是 是 是 数据类型存在一些限制。 这适用于所有批处理模式运算符。
串联 (concatenation) UNION 和 UNION ALL 否 是 是
filter 应用谓词 是 是 是
hash match 基于哈希的聚合函数、外部哈希联接、右哈希联接、左哈希联接、右内部联接、左内部联接 是 是 是 聚合的限制:不能对字符串执行 min/max。 可用的聚合函数是 sum/count/avg/min/max。联接的限制:不能对非整数类型执行任何不匹配的类型联接。
merge join 否 否 否
多线程查询 是 是 是
嵌套循环 否 否 否
单线程查询,在 MAXDOP 1 下运行 否 否 是
带有串行查询计划的单线程查询 否 否 是
sort 使用列存储索引的 SCAN 中的 Order by 子句。 否 否 是
top sort 否 否 是
window aggregates 不适用 不适用 是 SQL Server 2016 (13.x) 中的新运算符。

¹适用于 SQL Server 2016 (13.x)、SQL Database 高级层、标准层(S3 及更高)和所有 vCore 层,以及 并行数据仓库

聚合下推

聚合计算的常规执行路径是从 SCAN 节点提取符合条件的行,然后以批处理模式聚合值。 尽管这提供了良好的性能,但使用 SQL Server 2016 (13.x),可以将聚合运算推送到 SCAN 节点,以便在批处理模式执行的基础上将聚合计算的性能提高好几个数量级,前提是要满足以下条件:

  • 聚合为 MIN、MAX、SUM、COUNT 和 COUNT(*)。
  • 聚合运算符必须基于 SCAN 节点或包含 GROUP BY 的 SCAN 节点。
  • 此聚合不是非重复聚合。
  • 聚合列不是字符串列。
  • 聚合列不是虚拟列。
  • 输入和输出数据类型必须是以下类型之一,并且必须适合 64 位。
    • tinyint、int、bigint、smallint、bit
    • 精度 <= 18 的 smallmoney、money、decimal 和 numeric
    • smalldate、date、datetime、datetime2、time

通过在对缓存友好的执行中对压缩/编码数据进行高效聚合并利用 SIMD 可进一步加快聚合下推速度

aggregate pushdown

例如,在以下两个查询中完成了聚合下推:

SELECT  productkey, SUM(TotalProductCost)    
FROM FactResellerSalesXL_CCI    
GROUP BY productkey    
    
SELECT  SUM(TotalProductCost)    
FROM FactResellerSalesXL_CCI    

字符串谓词下推

在设计数据仓库架构时,建议的架构建模是使用星型架构或雪花型架构,其中包括一个或多个事实数据表和多个维度表。 事实数据表 存储业务度量值或事务,而 维度表 存储分析事实数据需要跨越的维度。

例如,事实可以是一条表示某一特定区域中某一特定产品的销售额的记录,而维度则表示一组区域、产品等。 事实数据表和维度表通过主键/外键关系进行连接。 最常用的分析查询将一个或多个维度表与事实数据表进行联接。

让我们设想一个维度表 Products。 典型的主键是通常用字符串数据类型表示的 ProductCode。 为提高查询性能,最佳做法是创建代理键(通常为整数列),从事实数据表引用维度表中的行。

列存储索引非常高效地运行具有联接/涉及数值的谓词或基于整数的键的分析查询。 但是,在很多客户工作负荷中,我们发现使用基于字符串的列链接事实/维度表,结果是使用列存储索引的查询性能并不如预期。 SQL Server 2016 (13.x) 通过将包含字符串列的谓词下推到 SCAN 节点,使用基于字符串的列显著提高了分析的性能。

字符串谓词下推利用为列创建的主/辅助字典来提高查询性能。 例如,让我们考虑在行组中创建一个包含 100 个不同字符串值的字符串列段。 假定有 100 万行,这意味着平均每个不同的字符串值被引用了 10,000 次。

使用字符串谓词下推,执行查询时针对字典中的值计算谓词,如果它符合条件,引用字典值的所有行都将自动符合条件。 这在两个方面提高了性能:

  1. 仅返回符合条件的行,从而减少了需要传递出 SCAN 节点的行数。
  2. 显著减少了字符串比较次数。 在此示例中,只需要 100 次字符串比较,而不用比较 100 万次。 如下所述,有一些限制:
    • 不能对增量行组执行字符串谓词下推。 增量行组中的列没有字典。
    • 如果字典大小超过 64 KB,则不能执行字符串谓词下推。
    • 不支持计算结果为 NULL 的表达式。

另请参阅

列存储索引设计指南
列存储索引数据加载指南
开始使用列存储适进行实时运行分析
针对数据仓库的列存储索引
列存储索引碎片整理
列存储索引体系结构
CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)

历史资料。此文对应 2018-11-30 的中文文档快照,适用产品版本和命令行为以原文为准。

来源与署名:Microsoft 和文档贡献者(原始作者元数据 MikeRayMSFT)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站展开固定版本仓库内的引用内容,将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。

资料来源与适用范围

资料来源
Microsoft / SQL Server 中文文档历史快照
适用范围
历史中文快照 2018-11-30;详见原文产品版本;文档日期 12/01/2017
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-16
授权说明
CC BY 4.0(文档)/ MIT(示例代码)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。