列存储索引 - 查询性能

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics 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 万行，则数据库引擎仅使用一个线程来创建列存储索引。

如果表包含 100 多万行，但数据库引擎无法获得足够大的内存授予，以便使用 MAXDOP 创建索引，数据库引擎会根据需要自动减少 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 列，则列存储索引仅从磁盘中提取五列。 它跳过其他 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 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、ate、datetime、datetime2、time

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

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 个非重复字符串值的字符串列段。 每个非重复字符串值平均引用 10,000 次，假设有 100 万行。 在使用字符串谓词下推时，查询执行会基于字典中的值来计算谓词。 如果谓词符合条件，则引用字典值的所有行都将自动符合条件。 这在两个方面提高了性能：

• 仅返回符合条件的行，从而减少了需要传递出 scan 节点的行数。
• 字符串比较数会减少。 在此示例中，只需要 100 次字符串比较，而不用比较 100 万次。 存在一些限制：
  • 不能对增量行组执行字符串谓词下推。 增量行组中的列没有字典。
  • 如果字典条目超过 64 KB，则不会进行字符串谓词下推。
  • 不支持对 null 值进行求值的表达式。

段消除

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

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

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

段消除不适用于 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)