百万条记录的SUM查询即使使用索引也很慢，如何进行优化？

Question

我有一个包含约350万条记录的数据库表。该表存储合同数据记录，包括金额、日期和与其他表相关的一些ID（VendorId、AgencyId、StateId），以下是数据库表：

CREATE TABLE [dbo].[VendorContracts]
(
    [Id] [uniqueidentifier] NOT NULL,   
    [ContractDate] [datetime2](7) NOT NULL,
    [ContractAmount] [decimal](19, 4) NULL, 
    [VendorId] [uniqueidentifier] NOT NULL,
    [AgencyId] [uniqueidentifier] NOT NULL,
    [StateId] [uniqueidentifier] NOT NULL,

    [CreatedBy] [nvarchar](max) NULL,
    [CreatedDate] [datetime2](7) NOT NULL,
    [LastModifiedBy] [nvarchar](max) NULL,
    [LastModifiedDate] [datetime2](7) NULL,
    [IsActive] [bit] NOT NULL,

    CONSTRAINT [PK_VendorContracts] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
                      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

我在我的网站上有一个页面，我希望用户能够选择供应商，并查看按州划分的合同金额总额。

我有以下查询：

SELECT SUM(ContractAmount), StateId
FROM [dbo].[VendorContracts]
WHERE VendorId = '...'
GROUP BY StateId

我创建了这个索引来帮助解决这个问题：

CREATE NONCLUSTERED INDEX [IX_VendorContracts_VendorId] ON [dbo].[VendorContracts]
(
    [VendorId] ASC
)
INCLUDE([Id],[StateId],[ContractAmount],[ContractDate],[AgencyId]) 
WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)

对于大多数供应商来说，这很有效，我可以在一两秒钟内快速返回结果。但是有几个供应商每个都有大约100万条记录，那些需要相当长的时间，通常需要几分钟。

所以我想知道是否有任何方法进一步优化，通过修改我的索引？或其他任何方式？

或者我可能走错了路？不确定在1百万条记录上运行如此巨大的SUM查询是否是一个好的做法。这张表不会很频繁地添加记录，我们可能每个月只添加一两千条记录，因此我考虑可能有一个夜间流程，可以获取SUM数据并将其存储在单独的表中，然后在运行报告时查询该表。

- Steven

---

在任何索引中都没有包含PK值的理由。我会认为你的PK并不是很有用，而且会导致你的总体效率低下——这是一个非常不同的问题。对于这个特定的查询，你需要一个包括ContractAmount的<VendorId，StateId>索引。根据你对这个表的使用情况，可能有更好的聚集选择。我怀疑你需要更多地关注你的数据类型。ContractDate不太可能需要时间部分。CreateBy永远不会有2Gb字符。等等。 - SMor

索引视图是另一种方法。但要注意 - 没有免费的午餐，这种物化必须在 DML 语句期间进行维护。而且，根据您的版本，您可能需要编写特定的代码来利用它。视图的缺点是对于类似于您现在拥有的查询不会有用 - 例如，当您需要选择特定供应商和州的实际行时。 - SMor

@SMor - 感谢您的建议，我可以更新我的索引和数据类型。我承认在SQL数据库索引/优化等方面我是非常新手，但我发现我需要学习，因为我正在处理这个庞大的表 :) - Steven

Answer 1

您应该在编写查询时避免使用“SELECT *”语句。

SELECT SUM(ContractAmount) AS TotalContractAmount,
       VendorId,
       StateId
FROM   [dbo].[VendorContracts]
GROUP  BY VendorId,
          StateId

将其制作成一个索引视图,以便系统自动预计算和维护结果(在VendorId, StateId上使用唯一聚集索引)。索引视图可能会对数据修改产生开销，但您表示此表不经常添加记录，每月可能只添加几千条。

在Azure SQL数据库中，SQL Server应该自动执行索引视图匹配，识别原始查询可以由索引视图提供服务。如果发现匹配无法发生(请验证执行计划)，则可能需要显式地从索引视图中选择并使用NOEXPAND提示。

- Martin Smith

---

是的，这是一个Azure SQL数据库。我注意到有些奇怪的地方，第一次运行查询时很慢，然后每次传递作为参数的VendorId都非常快。这可能是Azure自动创建索引视图吗？ - Steven

谢谢Martin，所以确认一下，我需要创建一个包含所有供应商数据的索引视图，然后使用特定的VendorId查询该视图？ - Steven

@Steven - 不会的。Azure不会自动创建它 - 可能是因为从磁盘读取物理页面与已经在缓冲区高速缓存中的数据之间的差异。 - Martin Smith

关于“确认一下”的回复 - 是的，这将为此特定查询提供最佳性能。Smor确实指出您现有的索引（IX_VendorContracts_VendorId）不是最优选择。 StateId 应该是次要键列，以避免任何需要排序或哈希来进行分组的要求。您可能首先想尝试使用它。对于大型供应商，仍需要在运行时将数百万行折叠为约50行，而索引视图可以避免这种情况。 - Martin Smith

好的，谢谢。我可以将StateId列添加到索引中，而不仅仅是作为一个INCLUDE。我原本以为只有需要过滤的列才应该在索引中，这就是为什么我只包括了VendorId - 但我认为既然我正在对StateId进行GROUP BY，那么它也应该被包括进来？ - Steven

@Steven - 在(VendorId，StateId) INCLUDE (ContractAmount)上创建索引，可以在VendorId上进行等值查询。当将StateId添加为辅助键列时，每个VendorId内的结果都按StateId排序（因此已经按组排序），这可以输入到流聚合运算符中，依次计算每个组的总计。 - Martin Smith

Answer 2

如果您有一个运行中的数据库系统需要进行一些报告，那么您应该考虑在Azure SQL DB中使用列存储索引。它支持聚集和非聚集列存储。与B-Trees/Heaps相比，列存储具有高度压缩比，因此对于报告/分析非常有用。此外，还有一个名为批处理模式的优化执行引擎，可大幅提高查询大量行的效率，例如报告查询。

您可以在文档页面上阅读语法。

不知道您的工作负载情况，我无法确定您是否应该使用聚集或非聚集。但是，尝试使用非聚集来查看是否有帮助相对较容易。虽然索引视图可用于使特定查询模式更快，但它也有一些限制-例如，如果您的主表上有很多更新发生，那么这些更新查询现在必须锁定主表+索引以及索引视图上的那些。这可能会导致锁定方面的阻塞。请尝试通过非聚集列存储索引使用批处理模式（如果您尚未移动到兼容模式150，则需要进行升级以获得最新/最佳的性能增强）来查看是否解决了您的问题。

- Conor Cunningham MSFT