sql-server - 为什么表上主键的存在会显着提高列存储索引的性能？

Question

我试图看到列存储索引可以在表上提供的性能提升。该表大约有 370 万行、11 列，并存储为堆(即没有主键)。我在表上创建一个列存储索引并运行以下查询:

SELECT 
    [Area], [Family],
    AVG([Global Sales Value]) AS [Average GlobalSalesValue],
    COUNT([Projected Sales])
FROM 
    dbo.copy_Global_Previous5FullYearSales
WHERE 
    [Year] > 2012  
GROUP BY 
    [Area], [Family]

创建表语句如下:

CREATE TABLE [dbo].[copy_Global_Previous5FullYearSales]
(
    [SBU] [NVARCHAR](10) NULL,
    [Year] [INT] NULL,
    [Global Sales Value] [MONEY] NULL,
    [Area] [NVARCHAR](50) NULL,
    [Sub Area] [NVARCHAR](50) NULL,
    [Projected Sales] [MONEY] NULL,
    [Family] [NVARCHAR](50) NULL,
    [Sub Family 1] [NVARCHAR](50) NULL,
    [Sub Family 2] [NVARCHAR](50) NULL,
    [Manufacturer] [NVARCHAR](40) NULL,
    [rowguid] [UNIQUEIDENTIFIER] NOT NULL,
    [ID] [INT] IDENTITY(1,1) NOT NULL,

    PRIMARY KEY CLUSTERED ([ID] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
              IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
              ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

在这种情况下，我从列存储索引中获得的性能提升可以忽略不计。带有列存储索引的查询的运行速度几乎与没有索引的原始查询一样慢，在某些情况下甚至更慢，即使也使用了批处理模式。

令人惊讶的是，当我在现有表上创建一个不断增加的主键 ID 并重建列存储索引时，我的 CPU 时间提高了 15 倍，运行时间提高了 3 倍。

我不明白添加主键会如何影响以压缩格式存储数据的列存储索引的查询性能。此外，主键仅更改页面的顺序，在这种情况下，将是无。

下面是执行计划

Answer 1

键的存在改变了列存储的方式 已建 .因为构建器获取其输入是为了结果段是段消除的更好候选者。阅读更多 Ensuring Your Data is Sorted or Nearly Sorted by Date to Benefit from Date Range Elimination :

The most common type of filter in data warehouse queries is by date. Columnstore segment elimination helps you skip entire one-million-row segments if the system can determine that no rows qualify, simply by looking at the minimum and maximum values for a column in a segment. So you usually will want to make sure that your segments are sorted, or nearly sorted, by date, so date filters can be executed as fast as possible.

您的订单来自 ID但我很确定这会导致功能依赖的副作用。