使用Date列索引时T-SQL查询极慢的优化方案咨询

我有一张包含800万行数据的PaymentItems表，其中10万行的外键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秒并返回全部10万行数据；将排序改为降序（ORDER BY [p].[Date] DESC）时耗时约1秒；删除Date列索引后，升序排序的查询速度也更快。

分析慢查询的执行计划发现，SQL Server并未先按外键过滤行，而是先对全部800万行进行排序（对Date索引执行非聚集索引扫描）。而快查询则是先过滤WHERE条件（执行聚集键查找）。

请问除了删除Date列索引外，还有什么方法可以避免SQL Server生成这类糟糕的执行计划？

建表脚本如下：

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

1. 创建复合覆盖索引（推荐） #

创建一个同时包含过滤条件、排序条件和查询返回列的复合覆盖索引，让SQL Server可以直接从索引中完成过滤、排序和数据返回，无需回表或全量扫描：

CREATE NONCLUSTERED INDEX [IX_PaymentItems_PaymentItemGroupId_Date] 
ON [dbo].[PaymentItems] (
    [PaymentItemGroupId] ASC,
    [Date] ASC
)
INCLUDE ([Id], [Receivers]) -- 包含查询需要返回的列
WITH (DROP_EXISTING = OFF) ON [PRIMARY];

这个索引会先按PaymentItemGroupId快速过滤出目标10万行，再按Date升序排序，直接返回TOP 10数据，从根本上避免无效的全表扫描操作。

2. 更新统计信息 #

SQL Server的执行计划依赖准确的统计信息，若统计信息过时，优化器可能误判符合过滤条件的行数，进而选择错误的执行路径。执行以下命令全量更新表的统计信息：

UPDATE STATISTICS [dbo].[PaymentItems] WITH FULLSCAN;

全量扫描更新能让优化器精准判断过滤后的行数，从而选择先过滤再排序的高效执行计划。

3. 使用查询提示强制指定索引（临时方案） #

如果暂时无法修改索引结构，可以在查询中添加索引提示，强制SQL Server使用IX_PaymentItems_PaymentItemGroupId索引先过滤数据，再进行排序：

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

注意：查询提示属于临时 workaround，长期维护优先选择优化索引方案，避免依赖提示导致后续索引变更时出现兼容问题。

内容的提问来源于stack exchange，提问作者Ben5