列存储索引 - 设计指南

项目
03/06/2025

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

有关设计列存储索引的概要建议。做出少量明智的决策，就能帮助你实现较高的数据压缩率和查询性能，列存储索引的目标就在于此。

先决条件

本文假设读者熟悉列存储的体系结构和术语。有关详细信息，请参阅列存储索引：概述和列存储索引体系结构。

了解数据要求

在设计列存储索引之前，应该尽可能充分地了解数据要求。例如，仔细考虑如下所述问题的答案：

我的桌子有多大？
我的查询是否主要执行需要扫描大范围数值的分析？列存储索引的设计目的是合理处理大范围扫描，而不是查找特定的值。
工作负载是否要执行大量的更新和删除操作？当数据稳定时，列存储索引可以正常工作。查询应该更新和删除少于 10% 的行。
我是否拥有用于数据仓库的事实表和维度表？
我是否需要对事务性工作负载进行分析？如果是这样，请参阅有关实时运营分析的列存储设计指南。

你可能不需要列存储索引（Columnstore Index）。包含堆或聚集索引的行存储（或 B 树）表最适合用于查找数据、搜索特定值的查询，或者针对较小范围的值执行查询。对于事务性工作负载，应使用行存储索引，因为这些工作负载通常需要进行表查找，而不是大范围的表扫描。

根据需要选择最佳的列存储索引

列存储索引为聚簇或非聚簇索引。聚集列存储索引可以有一个或多个非聚集 B 树索引。试用列存储索引的过程非常简单。如果将某个表创建为列存储索引，可以轻松将该表转换回行存储表，只需删除列存储索引即可。

下面是选项和建议的摘要。

列存储选项	使用时机的建议	压缩
聚集列存储索引	用于： 1) 采用星型或雪花型架构的传统数据仓库工作负荷 2) 在执行极少量更新和删除操作的条件下插入大量数据的物联网 (IOT) 工作负荷。	平均值为10倍
有序列存储索引	当通过单个有序谓词列或列集查询聚集列存储索引时使用。本指导类似于为行存储聚簇索引选择关键列的过程，只是压缩的底层行组的行为有所不同。有关详细信息，请参阅 CREATE COLUMNSTORE INDEX 和使用有序列存储索引进行性能优化。	10 倍平均值
基于聚集列存储索引的非聚集 B 树索引	用于： 1. 针对聚集列存储索引实施主键和外键约束。 2. 加速搜索特定值或小范围值的查询。 3. 加速特定行的更新和删除。	10 倍平均值加上 NCI 的某些附加存储。
以基于磁盘的堆或 B 树索引为基础的非聚集列存储索引	用于： 1) 包含某些分析查询的 OLTP 工作负荷。可以删除针对分析创建的 B 树索引，然后将其替换为一个非聚集列存储索引。 2) 许多执行提取、转换和加载 (ETL) 操作，以便将数据移到单独数据仓库的传统 OLTP 工作负荷。可以通过在某些 OLTP 表中创建非聚集列存储索引来消除 ETL 和单独的数据仓库。	NCCI 是一个附加的索引，所需的存储平均要多出 10%。
基于内存中表的列存储索引	与针对以基于磁盘的表为基础的非聚集列存储索引提供的建议相同，不过，基础表是一个内存中表。	列存储索引是一个附加索引。

为大型数据仓库表使用聚集列存储索引（Clustered Columnstore Index）

聚集列存储索引不仅仅是一个索引，而且是主表存储。它可以实现较高的数据压缩率，大幅提高大型数据仓库事实表和维度表的查询性能。聚集列存储索引最适合用于分析查询而不是事务查询，因为分析查询往往针对大范围值执行操作，而不是查找特定的值。

对于以下情况，请考虑使用聚集列存储索引：

每个分区至少包含 100 万行。列存储索引包含每个分区中的行组。如果表太小而无法填充每个分区中的行组，则可能无法获得列存储压缩和查询性能的好处。
查询主要针对值范围执行分析。例如，若要查找某个列的平均值，查询需要扫描所有列值。然后，将值求和以聚合这些值，以此得出平均值。
大多数插入操作是针对大量数据执行的，造成的更新和删除量极少。许多工作负荷（例如物联网 (IOT)）在执行极少量更新和删除操作的条件下插入大量数据。使用聚集列存储索引可让这些工作负荷受益于压缩与查询性能的提升。

对于以下情况，请不要使用聚集列存储索引：

表需要 varchar(max)、nvarchar(max) 或 varbinary(max) 数据类型。或者，设计列存储索引，使其不包含这些列（适用于：SQL Server 2016 (13.x) 和以前的版本）。
表数据不是持久性的。需要快速存储和删除数据时，请考虑使用堆或临时表。
表的每个分区包含的行数不超过 100 万。
在针对表执行的操作中，10% 以上是更新和删除。大量的更新和删除会导致碎片。碎片化会影响压缩率和查询性能，直到运行名为重新组织的操作，这将所有数据强制导入列存储并消除碎片。有关详细信息，请参阅最小化列存储索引中的索引碎片。

有关详细信息，请参阅数据仓库中的列存储索引。

为大型数据仓库表使用有序列存储索引

关于有序列存储索引的可用性，请参阅列存储索引：概述。

请考虑在以下方案中使用有序列存储索引：

当数据相对静态（不频繁写入和删除）且有序列存储索引键是静态的时，有序列存储索引可以比分析工作负荷的非有序列存储索引或行存储索引提供显著的性能优势。
有序列存储索引键的第一列中的值越不同，性能提升越好。这是因为改进了字符串数据的分段移除。有关详细信息，请参阅段消除。
选择经常查询的有序列存储索引键，并可以受益于段消除，尤其是键的第一列。由于表中其他列的段消除导致的性能提升效果较难预测。
在只需查询最近的（例如最近 15 秒）分析数据的用例中，有序列存储索引可为较旧的数据提供段消除。有序列存储数据的键中的第一列必须是日期/时间数据，例如插入或创建的日期/时间。分段消除在有序列存储索引中比无序列存储索引中更有效。
对于其中的键包含 GUID 数据的表，请考虑使用有序列存储索引，其中 uniqueidentifier 数据类型现在可用于段消除。

在以下情况下，有序列存储索引可能不如有效：

与其他列存储索引类似，插入活动的高速率可能会导致存储 I/O 过载。
对于存在大量写入操作的工作负载，由于元组发动机需要进行行组维护，因此段消除的质量会随着时间而降低。可以通过使用 ALTER INDEX REORGANIZE 定期维护列存储索引来缓解此问题。

添加 B 树非聚集索引以提高表查找的效率

从 SQL Server 2016 (13.x) 开始，可以创建非聚集 B 树或行存储索引作为聚集列存储索引中的辅助索引。当列存储索引发生更改时，非聚集 B 树索引会更新。这是一个可以带来优势的强大功能。

使用辅助 B 树索引可以有效搜索特定的行，而无需全面扫描所有行。其他选项也可供使用。例如，可以通过在 B 树索引中使用唯一约束来实施主键或外键约束。由于非唯一值无法插入到 B 树索引中，SQL Server 无法将该值插入列存储中。

对于以下情况，请考虑使用基于列存储索引的 B 树索引：

运行搜索特定值或小范围值的查询。
实施主键约束或外键约束等约束。
有效执行更新和删除操作。使用 B 树索引可以快速找到需要更新和删除的特定行，而无需扫描整个表或表分区。
你有额外的存储空间可用于保存 B 树索引。

使用非聚集列存储索引进行实时分析

从 SQL Server 2016 (13.x) 开始，可以在基于磁盘的行存储表或内存中 OLTP 表中使用非聚集列存储索引。这样，便可以针对事务表实时运行分析。尽管事务是在基础表中发生的，但你可以针对列存储索引运行分析。由于一个表可以管理两种索引，因此，行存储索引和列存储索引都会实时更改。

由于列存储索引实现的数据压缩率比行存储索引高出 10 倍，因此只需少量的额外存储。例如，如果压缩的行存储表占用 20 GB，列存储索引可能只需要额外的 2 GB 空间。所需的额外空间还取决于非聚集列存储索引中的列数。

对于以下情况，请考虑使用非聚集列存储索引：

在事务行式存储表上实时执行分析。可将设计用于分析的现有 B 树索引替换为非聚集列存储索引。
不再需要单独的数据仓库。在传统上，公司会在行存储表中运行事务，然后将数据载入单独的数据仓库以运行分析。对于许多工作负载，可以通过在事务表中创建非聚集列存储索引，来消除加载过程和单独的数据仓库。

SQL Server 2016 (13.x) 提供多种策略来保持这种方案的高性能。很容易尝试，因为可以启用非聚集列存储索引，且不会对 OLTP 应用程序进行更改。

若要添加更多的处理资源，可以针对可读的辅助副本运行分析。使用可读的辅助副本可将事务工作负荷与分析工作负荷的处理分隔开来。

有关详细信息，请参阅开始使用列存储进行实时运营分析

有关选择最佳列存储索引的详细信息，请参阅 Sunil Agarwal 的博客 Which columnstore index is right for my workload?（哪种列存储索引适合我的工作负荷？）。

使用表分区来管理数据和提高查询性能

列存储索引支持分区，这是管理和存档数据的一种良好方式。分区还可将操作局限于一个或多个分区，从而提高查询性能。

使用分区来简化数据管理

对于大型表，管理数据范围的唯一可行办法就是使用分区。行存储表的分区优势同样适用于列存储索引。

例如，行存储表和列存储表都可使用分区来实现以下目的：

控制增量备份的大小。可以将分区备份到单独的文件组，然后将它们标记为只读。通过执行此作，将来的备份将跳过只读文件组。
通过将旧分区转移到更经济的存储来节省存储成本。例如，可以使用分区切换将分区转移到更经济的存储位置。
通过限制针对分区的操作来有效执行操作。例如，您可以专门针对有碎片的分区进行索引维护。

此外，对于列存储索引，可以使用分区实现以下目的：

将存储成本进一步节省 30%。可以使用压缩选项压缩旧分区 COLUMNSTORE_ARCHIVE 。查询性能可能较慢，但如果分区查询不频繁，这可能是可以接受的。

使用分区提高查询性能

使用分区可将查询限制为仅扫描特定的分区，从而限制扫描的行数。例如，如果索引已按年份分区，而查询要分析上一年的数据，则它只需扫描一个分区中的数据。

对列存储索引使用更少的分区

除非数据相当大，否则，可以为列存储索引使用比行存储索引所用更少的分区以实现最佳性能。如果每个分区包含的行数不超过 100 万，大多数行可能会转到增量存储，在其中，这些行不会获得列存储压缩的性能优势。例如，如果将 100 万行加载到包含 10 个分区的表中，并且每个分区接收 100,000 行，则所有行都将转到增量行组。

示例：

将 1,000,000 行加载到一个分区或非分区表中。获取一个包含 100 万行的压缩行组。这非常适合用于提高数据压缩率和查询性能。
将 100 万行均匀载入 10 个分区。每个分区包含 10 万行，低于列存储压缩的最小阈值。因此，列存储索引可以包含 10 个增量行组，每个行组包含 10 万行。可通过某些方法强制将增量行组载入列存储。但是，如果这些行是列存储索引中唯一的行，则压缩行组太小，无法获得最佳压缩和查询性能。

有关分区的详细信息，请参阅 Sunil Agarwal 的博客文章 Should I partition my columnstore index?（我是否应该将列存储索引分区？）。

选择适当的数据压缩方法

列存储索引为数据压缩提供两个选项：列存储压缩和存档压缩。可在创建索引时选择压缩选项，或在以后使用 ALTER INDEX ... REBUILD 更改。

使用列存储压缩实现最佳查询性能

列存储压缩实现的压缩率通常比行存储索引要高出 10 倍。它是列存储索引的标准压缩方法，可提高查询性能。

使用存档压缩实现最佳数据压缩

如果查询性能不是那么重要，可以使用存档压缩来最大程度地提高压缩率。它的数据压缩率比列存储压缩更高，但同时会造成一个弊端。它在压缩和解压缩数据时花费的时间更长，因此不是很适合用于提高查询性能。

将行存储表转换为列存储索引时使用优化

如果数据已在行存储表中，你可以使用 CREATE COLUMNSTORE INDEX 将该表转换为聚集列存储索引。转换表后，可以使用多种优化方法来提高查询性能，如下文所述。

使用 MAXDOP 提高行组质量

可以配置最大数目的处理器，用于将堆或聚集 B 树索引转换为列存储索引。若要配置处理器，请使用最大并行度选项 (MAXDOP)。

如果你有大量数据，MAXDOP 1 可能太慢。将 MAXDOP 提高到 4 即可正常工作。如果这样做导致某些行组不包含最佳行数，可以运行 ALTER INDEX REORGANIZE，在后台将这些行合并在一起。

保留 B 树索引的排序顺序

由于 B 树索引已按排序顺序存储行，将行压缩到列存储索引中时保留这种顺序可以提高查询性能。

列存储索引不会将数据排序，但会使用元数据来跟踪每个行组中每个列段的最小值和最大值。扫描一系列值时，它可以快速计算何时要跳过行组。将数据排序后，可以跳过其他行组。

若要在转换期间保留排序顺序，请执行以下操作：

结合 DROP_EXISTING 子句使用 CREATE COLUMNSTORE INDEX。这也会保留索引的名称。如果脚本已使用行存储索引的名称，则无需更新它们。

此示例将名为 MyFactTable 的表中的聚集行存储索引转换到聚集列存储索引。索引名称 ClusteredIndex_d473567f7ea04d7aafcac5364c241e09 保持不变。
```
CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09
ON MyFactTable
WITH (DROP_EXISTING = ON);
```

了解段消除

每个行组包含表中每个列的一个列段。每个列段被压缩在一起并存储于物理介质上。

每个段都有元数据可用于快速消除段，无需读取它们。数据类型选择可能会对列存储索引上查询的基于通用筛选谓词的查询性能产生重大影响。有关详细信息，请参阅段消除。

下面是用于创建和维护列存储索引的任务。

任务	参考文章	备注
将表创建为列存储。	CREATE TABLE (Transact-SQL)	从 SQL Server 2016 (13.x) 开始，可以将表创建为聚集列存储索引。不需要先创建行存储表，然后将其转换为列存储。
创建具有列存储索引的内存表。	CREATE TABLE (Transact-SQL)	从 SQL Server 2016 (13.x) 开始，你可以创建具有列存储索引的内存优化表。也可以在创建表后使用 ALTER TABLE ADD INDEX 语法添加列存储索引。
将行存储表转换为列存储。	CREATE COLUMNSTORE INDEX (Transact-SQL)	将现有堆或 B 树转换为列存储。示例演示了如何在执行此转换时处理现有的索引以及索引的名称。
将列存储表转换为行存储。	CREATE CLUSTERED INDEX (Transact-SQL) 或将列存储表转换回行存储堆	通常不需要这样转换，但有时需要。示例演示如何将列存储转换为堆或聚集索引。
在行存储表中创建列存储索引。	CREATE COLUMNSTORE INDEX (Transact-SQL)	一个行存储表可以有一个列存储索引。从 SQL Server 2016 (13.x) 开始，列存储索引可以包含筛选条件。示例演示了基本语法。
为操作分析创建高性能索引。	开始使用列存储进行实时运营分析	介绍如何创建互补性列存储索引和 B 树索引，以便 OLTP 查询使用 B 树索引，分析查询使用列存储索引。
为数据仓库创建高性能列存储索引。	数据仓库中的列存储索引	介绍如何使用列存储表上的 B 树索引来创建高性能数据仓库查询。
使用 B 树索引对列存储索引强制实施主键约束。	数据仓库中的列存储索引	演示如何合并 B 树和列存储索引，以便对列存储索引强制实施主键约束。
删除列存储索引	DROP INDEX (Transact-SQL)	删除列存储索引时，使用与 B 树索引相同的标准 DROP INDEX 语法。删除聚集列存储索引会将列存储表转换为堆。
从列存储索引中删除行	DELETE (Transact-SQL)	使用 DELETE (Transact-SQL) 删除行。 “列存储”行：SQL Server 会将该行标记为逻辑上已删除，但在重建索引之前不会回收该行的物理存储空间。 deltastore 行：SQL Server 在逻辑上和物理上对该行进行删除。
更新列存储索引中的行	UPDATE (Transact-SQL)	使用 UPDATE (Transact-SQL) 更新行。列存储行：SQL Server 将行标记为已在逻辑上删除，然后将更新后的行插入增量存储中。 deltastore 行：SQL Server 在 deltastore 中更新该行。
强制增量存储中的所有行进入列存储。	ALTER INDEX (Transact-SQL) ...REBUILD 优化索引维护以提高查询性能并减少资源消耗	结合使用 `ALTER INDEX` 和 `REBUILD` 选项，以强制所有行都转入列存储。
对列存储索引进行碎片整理	ALTER INDEX (Transact-SQL)	`ALTER INDEX ... REORGANIZE` 联机对列存储索引进行碎片整理。
合并具有列存储索引的表。	MERGE (Transact-SQL)

若要为以下服务创建空的列存储索引：

SQL Server 或 SQL 数据库，具体请参阅 CREATE TABLE (Transact-SQL)。
有关 Azure Synapse Analytics，请参阅 CREATE TABLE (Azure Synapse Analytics)。

有关如何将现有行存储堆或 B 树索引转换为聚集列存储索引，或创建非聚集列存储索引的详细信息，请参阅 CREATE COLUMNSTORE INDEX (Transact-SQL)。

通过

列存储索引 - 设计指南

先决条件

了解数据要求

根据需要选择最佳的列存储索引

为大型数据仓库表使用聚集列存储索引（Clustered Columnstore Index）

为大型数据仓库表使用有序列存储索引

添加 B 树非聚集索引以提高表查找的效率

使用非聚集列存储索引进行实时分析

使用表分区来管理数据和提高查询性能

使用分区来简化数据管理

使用分区提高查询性能

对列存储索引使用更少的分区

选择适当的数据压缩方法

使用列存储压缩实现最佳查询性能

使用存档压缩实现最佳数据压缩

将行存储表转换为列存储索引时使用优化

使用 MAXDOP 提高行组质量

保留 B 树索引的排序顺序

了解段消除

反馈

其他资源

通过

列存储索引 - 设计指南

先决条件

了解数据要求

根据需要选择最佳的列存储索引

为大型数据仓库表使用聚集列存储索引（Clustered Columnstore Index）

为大型数据仓库表使用有序列存储索引

添加 B 树非聚集索引以提高表查找的效率

使用非聚集列存储索引进行实时分析

使用表分区来管理数据和提高查询性能

使用分区来简化数据管理

使用分区提高查询性能

对列存储索引使用更少的分区

选择适当的数据压缩方法

使用列存储压缩实现最佳查询性能

使用存档压缩实现最佳数据压缩

将行存储表转换为列存储索引时使用优化

使用 MAXDOP 提高行组质量

保留 B 树索引的排序顺序

了解段消除

相关任务

相关内容

反馈

其他资源