列存储索引 - 查询性能

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric SQL 数据库

本文包含有关使用列存储索引实现快速查询性能的建议。

列存储索引在分析和数据仓库工作负荷上的性能最高可提高 100 倍，数据压缩比传统行存储索引高出 10 倍。 这些建议可帮助查询实现列存储索引旨在提供的快速查询性能。

提高查询性能的建议

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

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

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

• 使用行存储聚集索引。 如果常见的查询谓词位于与插入顺序无关的列（例如 C1）上，请在列 C1上创建行存储聚集索引。 然后，删除行存储聚集索引并创建聚集列存储索引。 如果使用显式 MAXDOP = 1创建聚集列存储索引，则生成的聚集列存储索引在列 C1上完全排序。 如果指定 MAXDOP = 8，则会看到八个行组中的值重叠。 对于非聚集列存储索引（NCCI），如果表具有行存储聚集索引，则行已按聚集索引键排序。 在这种情况下，非聚集列存储索引也会自动排序。 列存储索引本身不会维护行的顺序。 插入新行或更新较旧的行时，可能需要重复此过程，因为分析查询性能可能会恶化。

• 实现表分区。 可以分区列存储索引，然后使用分区消除来减少要扫描的行组数。 例如，事实数据表存储客户购买情况。 常见的查询模式是查找按季度购买。customer 在这种情况下，请将插入顺序列与列上的分区组合在一起 customer 。 每个分区都包含每个 customer分区的行，在插入时排序。 此外，如果需要从列存储中删除旧数据，请考虑使用表分区。 切换和截断不需要的分区是删除数据而不生成碎片的有效策略。

• 避免删除大量数据。 从行组中删除压缩行不是同步操作。 解压缩行组，删除行，然后重新压缩可能会产生很高的系统开销。 因此，从压缩行组中删除数据时，这些行组仍会扫描，即使它们返回的行数更少。 如果多个行组的已删除行数足够大，无法合并为更少的行组，则重新组织列存储会提高索引的质量，提高查询性能。 如果数据删除过程通常清空整个行组，请考虑使用表分区。 切换不再需要的分区并截断它们，而不是删除行。

  注意

  从 SQL Server 2019 （15.x）开始，元组移动器由后台合并任务帮助。 此任务自动压缩一段时间以来已存在的较小 OPEN 增量行组，由内部阈值决定，或合并从中删除大量行的 COMPRESSED 行组。 随着时间的推移，这会提高列存储索引的质量。 如果需要从列存储索引中删除大量数据，请考虑在一段时间内将此操作拆分为较小的删除批处理。 批处理允许后台合并任务处理合并较小的行组的任务，并提高索引质量。 然后，无需在删除数据后计划索引重组维护时段。 有关列存储术语和概念的详细信息，请参阅列存储索引：概述。

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 要求的技术组合使用来实现高查询性能。 由于分析查询会扫描大量行，因此它们通常是 I/O 绑定的，因此在查询执行过程中减少 I/O 对于列存储索引的设计至关重要。 将数据读入内存后，减少内存中操作的数量至关重要。

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

数据压缩

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

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

• 列存储索引将数据以压缩的形式存储在内存中，通过避免将数据读取到内存中来减少 I/O。 例如，使用 10 次压缩，与以未压缩形式存储数据相比，列存储索引在内存中可以保留 10 倍的数据。 内存中的数据越多，列存储索引更有可能在内存中查找所需的数据，而不会从磁盘中产生不必要的读取。

• 列存储索引按列（而不是按行）压缩数据，从而实现高压缩率并减少磁盘上存储的数据的大小。 每个列独自压缩和存储。 列中的数据始终具有相同的数据类型，并且往往具有类似的值。 列存储数据压缩技术非常适用于在值相似时实现更高的压缩率。

例如，事实数据表存储客户地址，并具有一个列 country-region。 可能值的总数小于 200。 其中一些值多次重复。 如果事实数据表有 1 亿行，则 country-region 列可以轻松压缩，并且需要很少的存储。 逐行压缩无法以这种方式利用列值的相似性，并且必须使用更多字节来压缩列中的值 country-region 。

列消除

列存储索引会跳过读取查询结果不需要的列。 列消除进一步减少了查询执行的 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%。

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

为了确定要消除哪些行组，列存储索引在每个行组中使用元数据来存储每个列段的最小值和最大值。 当任何列段范围都不符合查询谓词条件时，将跳过整个行组，而无需执行任何实际的 I/O。 这很有效，因为数据通常按排序顺序加载。 尽管无法保证行排序，但类似的数据值通常位于同一行组或相邻行组中。

有关行组的详细信息，请参阅《列存储索引设计指南》。

批模式执行

批处理模式执行 处理组中的行，通常一次最多处理 900 行，以提高效率。 例如，查询 SELECT SUM(Sales) FROM SalesData 计算表中的总销售额 SalesData 。 在批处理模式下，查询引擎处理 900 行组中的数据。 此方法通过将元数据访问成本和其他类型的开销分散到批处理中的所有行中，而不是产生每行的开销，从而减少元数据访问成本和其他类型的开销。 此外，批处理模式在可能的情况下适用于压缩数据，并删除行模式下使用的一些交换运算符，从而显著加快了分析查询的速度。

并非所有查询执行运算符都可以在批处理模式下执行。 例如，数据操作语言（DML）操作（如插入、删除或更新）一次执行一行。 Batch 模式运算符（如 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 数据库1	注释
DML 操作（insert、delete、update、merge）		否	否	否	DML 不是批处理模式操作，因为它不是并行的。 即使我们启用串行模式批处理操作，允许 DML 以批处理模式处理，我们也看不到明显的收益。
columnstore index scan	扫描	不可用	是	是	对于列存储索引，我们可以将谓词推送到 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 数据库高级层、标准层 - S3 及更高层以及所有 vCore 层以及分析平台系统（PDW）

有关详细信息，请参阅查询处理体系结构指南。

聚合下推

聚合计算的常规执行路径是从 SCAN 节点提取符合条件的行，然后以批处理模式聚合值。 虽然这可提供良好的性能，但从 SQL Server 2016（13.x 开始），聚合操作可以推送到 SCAN 节点。 如果满足以下条件，聚合下推会根据批处理模式执行量级数来提高聚合计算的性能：

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

例如，聚合下推在以下两个查询中完成：

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

字符串谓词下推

在设计数据仓库架构时，建议的架构建模是使用星型架构或雪花型架构，其中包括一个或多个事实数据表和多个维度表。

提示

事实数据表 存储业务度量值或事务，而 维度表 存储分析事实数据需要跨越的维度。 有关维度建模的详细信息，请参阅 Microsoft Fabric 中的维度建模。

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

让我们设想一个维度表 Products。 典型的主键通常 ProductCode表示为字符串。 对于查询的性能，最佳做法是创建代理键（通常是 整数 列），以引用事实数据表中维度表中的行。

列存储索引使用联接和谓词高效运行分析查询，这些查询涉及基于数字或整数的键。 SQL Server 2016 （13.x） 通过将字符串列的谓词向下推送到 SCAN 节点，显著提高了使用基于字符串的列的分析查询的性能。

字符串谓词下推利用为列创建的主/辅助字典来提高查询性能。 例如，考虑行组中包含 100 个非重复字符串值的字符串列段。 每个非重复字符串值平均引用 10,000 次，假设有 100 万行。 使用字符串谓词下推，查询执行根据字典中的值计算谓词。 如果谓词限定，则引用字典值的所有行都将自动限定。 这在两个方面提高了性能：

• 仅返回限定行，减少需要流出扫描节点的行数。
• 字符串比较数会减少。 在此示例中，只需要 100 次字符串比较，而不用比较 100 万次。 存在一些限制：
  • 不能对增量行组执行字符串谓词下推。 增量行组中的列没有字典。
  • 如果字典大小超过 64 KB，则不能执行字符串谓词下推。
  • 不支持计算 null 的表达式。

段消除

数据类型选择可能会对列存储索引上查询的基于通用筛选谓词的查询性能产生重大影响。

在列存储数据中，行组由列段组成。 每个段都有元数据可用于快速消除段，无需读取它们。 此段消除适用于数值、日期和时间数据类型，以及小于或等于两位数的 datetimeoffset 数据类型。 从 SQL Server 2022（16.x）开始，段消除功能扩展到字符串、二进制数据类型、guid 数据类型和 datetimeoffset 数据类型，以扩大两个以上的规模。

升级到支持字符串 min/max 段消除的 SQL Server 版本（SQL Server 2022（16.x）及更高版本后，列存储索引在使用 REBUILD 或 DROP/CREATE重新生成之前不会使此功能受益。

段消除不适用于 LOB 数据类型，例如 (max) 数据类型长度。

目前，只有 SQL Server 2022 (16.x) 和更高版本支持对 LIKE 谓词的前缀（例如 column LIKE 'string%'）使用聚集列存储行组消除。 对于 LIKE 的非前缀用法（例如 column LIKE '%string'），不支持段消除。

有序聚集列存储索引 也受益于段消除，尤其是字符串列。 在有序聚集列存储索引中，索引键中第一列的段消除最为有效，因为它已排序。 由于表中其他列的段消除导致性能提升将不太可预测。 有关有序聚集列存储索引的详细信息，请参阅对大型数据仓库表使用有序聚集列存储索引。 有关有序列存储索引可用性，请参阅 有序列索引可用性。

使用查询连接选项 SET STATISTICS IO，可以查看操作中的段消除。 查找如下所示的输出，以指示已发生段消除。 行组由列段组成，因此这可能表示段消除。 查询的以下 SET STATISTICS IO 输出示例，查询跳过了大约 83% 的数据：

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

相关内容

• 列存储索引设计指南
• 列存储索引 - 数据加载指南
• 开始使用列存储进行实时运营分析
• 数据仓库中的列存储索引
• 优化索引维护以提高查询性能并减少资源消耗
• 列存储索引体系结构
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)