当计划中未使用该索引时，为什么在SQL Server中添加索引会减慢查询速度？

I am stumped. If I remove an index that is not used by the query, then the query goes from 46 seconds down to 6 seconds.

我很难过。如果我删除查询未使用的索引，则查询从46秒下降到6秒。

Here is the query:

这是查询：

SELECT TOP (10) 
[Extent1].[alarm_class_id] AS [alarm_class_id], 
[Extent2].[domain_id] AS [domain_id], 
[Extent3].[description] AS [description], 
[Extent1].[list_id] AS [list_id], 
[Extent2].[list_source] AS [list_source], 
[Extent1].[list_detail_id] AS [list_detail_id], 
[Extent1].[plate] AS [plate], 
[Extent1].[notes] AS [notes], 
[Extent1].[locale_id] AS [locale_id], 
[Extent1].[end_date] AS [end_date], 
[Extent2].[eoc_list_id] AS [eoc_list_id], 
[Extent2].[list_type_id] AS [list_type_id]
FROM   [dbo].[list] AS [Extent2]
INNER JOIN [dbo].[ron_list_detail] AS [Extent1] ON [Extent1].[list_id] = [Extent2].[list_id]
INNER JOIN [dbo].[domain_lookup] AS [Extent3] ON [Extent2].[domain_id] = [Extent3].[domain_id]
WHERE ([Extent2].[domain_id] IN (7)) AND (([Extent1].[end_date] IS NULL) OR ([Extent1].[end_date] > (SysDateTimeOffset())))

Here is the index:

这是索引：

CREATE NONCLUSTERED INDEX [ron_CI_list_detail-list_id-plate] 
ON [dbo].[ron_list_detail] ([list_id] ASC, [plate] 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, FILLFACTOR = 90) ON [PRIMARY]
GO

Additional info: the query plan does not use this index unless I supply a hint. If I supply the hint, the the query is within a second.

附加信息：除非我提供提示，否则查询计划不会使用此索引。如果我提供提示，则查询在一秒钟之内。

I am confused - thoughts? (would have shown the query plan, but I couldn't figure out how to paste the image)

我很困惑 - 想法？（会显示查询计划，但我无法弄清楚如何粘贴图像）

2 个解决方案

#1

One of the reasons I can think of is outdated statistics on other indices. Here's how it might happen:

我能想到的其中一个原因是其他指数的过时统计数据。以下是它可能发生的方式：

Your existing indices have outdated statistics that trick the optimiser to think they are better for your query. As a result, the new index isn't used;
您现有的索引已经过时的统计信息会欺骗优化者认为它们更适合您的查询。结果，不使用新索引;
When it comes to actual query execution, resulting plan misses by a lot and query takes a long time to run;
当涉及到实际的查询执行时，由此导致的计划错过很多次并且查询需要很长时间才能运行;
When you nail the index down with a hint, query optimiser has no choice but to use it, and it runs much faster (as it should be).
当您使用提示确定索引时，查询优化器别无选择，只能使用它，并且运行速度更快（应该如此）。

When optimising indices, make sure all of them are freshly rebuilt and all statistics are updated (not necessarily with fullscan, default mode usually works just as well). Most probably, after you execute something like

在优化索引时，请确保所有索引都已重新刷新并且所有统计信息都已更新（不一定使用fullscan，默认模式通常也可以正常工作）。最有可能的是，在执行类似的操作之后

alter index all on [dbo].[ron_list_detail] rebuild;

the plan might straighten up without the need of any hints.

该计划可能会在不需要任何提示的情况下理顺。

Another thing is that your index doesn't look optimal for this particular query. I would rather think of domain_id, list_id, because the plate column is only returned as the output and isn't used in any of the join conditions.

另一件事是您的索引对于此特定查询看起来不是最佳的。我宁愿考虑domain_id，list_id，因为plate列只作为输出返回，并且不用于任何连接条件。

#2

Turns out it is the TOP in the query.

原来它是查询中的TOP。

SELECT ... is fast SELECT TOP (1191) ... is slow SELECT TOP (1192) .... is fast

SELECT ...很快SELECT TOP（1191）......很慢SELECT TOP（1192）....很快

any number <= 1191 is slow any number >= 1192 is fast No Top at all is fast

任何数字<= 1191都是慢的任何数字> = 1192是快的没有顶部是快的

weird!!!

奇怪的！！！

#1