使用有序列存储索引进行性能优化

2025-05-20

适用于： Microsoft Fabric 中的 SQL Server 2022 （16.x）及更高版本 Azure SQL 数据库 Azure SQL 托管实例 SQL 数据库

通过启用高效的段消除，有序列存储索引通过跳过大量与查询谓词不匹配的有序数据，从而提供更快的性能。将数据加载到有序列存储索引中，并通过索引重建保持其顺序，可能需要比非有序索引更长的时间，因为要进行数据排序。然而，使用有序列存储索引，可以使查询在之后运行得更快。

当用户查询列存储表时，优化器将检查存储在每个段中的最小值和最大值。超出查询谓词边界的段不会从磁盘读取到内存。如果要读取的段数及其总大小较小，查询可以更快地完成。

有关有序列存储索引可用性，请参阅有序列存储索引可用性。

有关最近添加的列存储索引功能的详细信息，请参阅列存储索引中的新增功能。

已排序与非有序列存储索引

在列存储索引中，每一行组中每列的数据都压缩为单独的段。每个段都包含描述其最小值和最大值的元数据，因此查询执行期间不会从磁盘读取超出查询谓词边界的段。

如果未对列存储索引进行排序，索引生成器在将数据压缩为段之前不会对数据进行排序。这意味着，可能会发生具有重叠值范围的段，从而导致查询从磁盘读取更多段，并且需要更长的时间才能完成。

创建有序列存储索引时，数据库引擎会按照索引生成器将现有数据压缩为段之前指定的顺序键对现有数据进行排序。通过排序的数据，可以减少或消除分段重叠，从而使查询更高效地消除不必要的段，并提高性能，因为从磁盘读取的段更少。

根据可用内存、数据大小、并行度、索引类型（聚集索引与非聚集索引）以及索引构建类型（脱机与在线），排序列存储索引的排序可能是完全排序（无段重叠）或部分排序（部分段重叠）。例如，当可用内存不足以进行完全排序时，会发生部分排序。使用有序列存储索引的查询通常比使用非有序索引执行更快，即使已排序索引是使用分部排序生成的。

创建或重新生成的有序聚集列存储索引，如果同时使用ONLINE = ON和MAXDOP = 1选项，则会提供完全排序。在这种情况下，排序不受可用内存的限制，因为它使用 tempdb 数据库溢出内存中不适合的数据。这会使索引生成过程由于额外的 tempdb I/O 而变慢。但是，通过联机索引重新生成，查询可以在重新生成新的有序索引时继续使用现有索引。

如果要排序的数据量足够小，可以完全容纳于可用内存中，则也可以为使用 ONLINE = OFF 和 MAXDOP = 1 选项创建或重建的已排序的聚集列存储索引和非聚集列存储索引提供完全排序。

在所有其他情况下，排序列存储索引中的排序是不完整的。

注意

目前，只能在 Azure SQL 数据库、Azure SQL 托管实例^AUTD 和 SQL Server 2025（17.x）预览版中创建或重新生成有序列存储索引。

若要检查列的段范围并确定是否存在任何段重叠，请使用以下查询，将占位符替换为架构、表和列名称：

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       cols.name AS column_name,
       pnp.index_id,
       cls.row_count,
       pnp.data_compression_desc,
       cls.segment_id,
       cls.column_id,
       cls.min_data_id,
       cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
   AND
   cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
      AND
      o.name = '<Table Name>'
      AND
      cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;

例如，此查询中完全排序的列存储索引的输出可能如下所示。请注意，不同段落的min_data_id列和max_data_id列之间没有重叠。

schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo         Table1     Column1     1        479779    COLUMNSTORE           0          1         -17         1469515
dbo         Table1     Column1     1        887658    COLUMNSTORE           1          1         1469516     2188146
dbo         Table1     Column1     1        930144    COLUMNSTORE           2          1         2188147     11072928

注意

在有序列存储索引中，同一批的 DML 或数据加载操作所生成的新数据仅在此批次内进行排序。没有包含表中现有数据的全局排序。

若要在插入新数据或更新现有数据后对索引中的数据进行排序，请重新生成索引。

对于分区列存储索引的脱机重建，将逐个分区进行重建。正在重新生成的分区中的数据不可用，直到该分区的重新生成完成为止。

联机重新生成期间数据仍可用。有关详细信息，请参阅联机执行索引操作。

查询性能

有序列存储索引的性能取决于查询模式、数据大小、数据排序方式、段的物理结构以及可用于查询执行的计算资源。

采用以下模式的查询通常使用有序列存储索引更快地运行。

具有相等性、不相等或范围谓词的查询。
谓词列与有序 CCI 列相同的查询。

在此示例中，表T1具有按顺序排序的Col_CCol_B聚集列存储索引，以及 Col_A。

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON T1
ORDER (Col_C, Col_B, Col_A);

查询 1 和 2 的性能比查询 3 和 4 更能从有序列存储索引中获益，因为它们引用了所有已排序的列。

-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- query 3
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_A = 'a';

-- query 4
SELECT *
FROM T1
WHERE Col_A = 'a' AND Col_C = 'c';

数据加载性能

将数据加载到具有有序列存储索引的表中的性能类似于已分区表。由于数据排序作，加载数据所需的时间可能比使用非有序列存储索引更长，但查询之后可以更快地运行。

减少段重叠

重叠段的数量取决于待排序的数据大小、可用内存以及有序列存储索引构建过程中最大并行度（MAXDOP）设置。以下策略可减少段重叠，但它们可以使索引生成过程花费更长的时间。

如果联机索引生成可用，则在创建有序聚集列存储索引时，请同时使用ONLINE = ON和MAXDOP = 1选项。这会创建完全排序的索引。
如果联机索引生成不可用，请使用 MAXDOP = 1 此选项。
按加载前的排序键预先对数据进行排序。

如果 MAXDOP 大于 1，用于有序列存储索引生成的每个线程都适用于数据子集并在本地对其进行排序。已由不同线程排序的数据不会经过全局排序。使用并行线程可以减少创建索引的时间，但它会生成比使用单个线程时更多的重叠段。使用单线程操作可以提供最高的压缩质量。指定MAXDOP可以通过CREATE INDEX命令实现。

示例

检查有序列和顺序序号

SELECT object_name(c.object_id) AS table_name,
       c.name AS column_name,
       i.column_store_order_ordinal
FROM sys.index_columns AS i
INNER JOIN sys.columns AS c
ON i.object_id = c.object_id
   AND
   c.column_id = i.column_id
WHERE column_store_order_ordinal <> 0;

创建有序列存储索引

聚集有序列存储索引：

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);

非聚集有序列存储索引：

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);

添加或删除订单列并重新生成现有的有序列存储索引

聚集有序列存储索引：

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

非聚簇有序列存储索引：

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

联机创建有序聚集列存储索引，对堆表进行完全排序

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);

使用完全排序重新生成联机有序聚集列存储索引

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

通过