主页 / user-11385442

Ben5's questions

Martin Hope
Ben5
Asked: 2025-03-07 16:44:10 +0800 CST
  • 8

我有一张PaymentItems有 800 万行的表。其中 100'000 行具有外键PaymentItemGroupId = '662162c6-209c-4594-b081-55b89ce81fda'。

我已经在列PaymentItems.Date（ASC）上创建了一个非聚集索引，以便能够更快地对日期条目进行排序/查找。

运行以下查询时，大约需要 3 分钟：

SELECT TOP 10 [p].[Id], [p].[Receivers]
FROM [PaymentItems] AS [p]
WHERE [p].[PaymentItemGroupId] = '662162c6-209c-4594-b081-55b89ce81fda'
ORDER BY [p].[Date]

有趣的是，如果没有 TOP 10，它将需要 18 秒并返回所有 100,000 行。当我按降序而不是升序排序时（ORDER BY [p].[Date] DESC），它将花费大约 1 秒。当我删除索引时，按升序排序也会更快。

我分析了慢查询的查询计划，看起来 MS SQL Server 不会首先通过外键过滤行，而是会先对所有 800 万行进行排序（索引扫描非聚集在Date索引上）。

在快速查询中，会先过滤where条件（key lookup clustered）。

除了删除索引之外我还能做什么来Date防止导致 SQL Server 构建这样的错误查询计划？

这是实际的查询计划： https://www.brentozar.com/pastetheplan/? id=xBBArQl9kh

以下是创建表的脚本：

CREATE TABLE [dbo].[PaymentItems](
    [Id] [uniqueidentifier] NOT NULL,
    [PaymentItemGroupId] [uniqueidentifier] NOT NULL,
    [Date] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_PaymentItems] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PaymentItems_Date] ON [dbo].[PaymentItems]
(
    [Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_PaymentItems_PaymentItemGroupId] ON [dbo].[PaymentItems]
(
    [PaymentItemGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
sql-server