正在获取RID查找而不是表扫描

Question

SQL Fiddle：http://sqlfiddle.com/#！3/23cf8

在这个查询中，当我在Id上有一个In子句，然后还选择其他列时，首先计算In，然后通过RID查找：拉入Details列和其他列

--In production and in SQL Fiddle, Details is grabbed via a RID Lookup after the In clause is evaluated
SELECT [Id]
,[ForeignId]    
,Details    
--Generate a numbering(starting at 1) 
--,Row_Number() Over(Partition By ForeignId Order By Id Desc) as ContactNumber --Desc because older posts should be numbered last
FROM SupportContacts
Where foreignId In (1,2,3,5)

通过此查询，可以通过表扫描获取详细信息。

With NumberedContacts AS 
(
SELECT [Id]
,[ForeignId]
--Generate a numbering(starting at 1) 
,Row_Number() Over(Partition By ForeignId Order By Id Desc) as ContactNumber --Desc because older posts should be numbered last
FROM SupportContacts
Where ForeignId In (1,2,3,5) 
)
Select nc.[Id]
,nc.[ForeignId]   
,sc.[Details]
From NumberedContacts nc
Inner Join SupportContacts sc on nc.Id = sc.Id
Where nc.ContactNumber <= 2 --Only grab the last 2 contacts per ForeignId
;

在SqlFiddle中，第二个查询实际上得到一个RID查找，而在具有一百万条记录的生产中，它生成一个表扫描(IN子句消除了99%的行)

否则，SQL Fiddle中显示的查询计划是相同的，唯一的区别是，对于第二个查询，SQL Fiidle中的RID Lookup是生产中的表扫描：(

1. 我想了解导致这种行为的可能性？在这里使用表格扫描，你会看什么样的东西来帮助确定它的原因？

2. 我如何影响它在那里使用RID查找？

从实际执行计划中的操作成本来看，如果我能使用RID查找，我相信第二个查询的性能可以非常接近第一个查询。如果我不选择Detail列，那么这两个查询在生产中的性能非常接近。只有在添加了其他列(如Detail)之后，第二个查询的性能才会显著下降。当我把它放在SQL Fiddle中，看到执行计划使用了RID查找时，我很惊讶，但有点困惑。。。

它没有聚集索引，因为在使用不同聚集索引进行测试时，此查询和其他查询的性能稍差。不过，那是在我开始添加其他列(如Details)之前，我可以对其进行更多的实验，但在我开始在黑暗中使用随机索引之前，我想了解一下现在发生了什么。

Answer 1

如果您要更改主索引以包含Details列，该怎么办？

如果您使用：

CREATE NONCLUSTERED INDEX [IX_SupportContacts_ForeignIdAsc_IdDesc] 
ON SupportContacts ([ForeignId] ASC, [Id] DESC)
INCLUDE (Details);

那么既不需要RID查找也不需要表扫描，因为您的查询只能从索引本身得到满足。。。。

Answer 2

查询计划的差异将取决于不同环境中存在的索引类型以及这些表的数据统计信息。

优化器使用统计数据(主要是数据频率的直方图)和可用索引来决定哪个执行计划最快。

因此，例如，您已经注意到，当包含"Details"列时，性能会下降。这是一个几乎可以肯定的迹象，表明"Details"列不是索引的一部分，或者如果它是索引的一部份，则该列中的数据大多是唯一的，因此索引访问将等同于(或几乎等同于)表扫描。

通常，当出现这种情况时，优化器会选择表扫描而不是索引访问，因为它可以利用块读取等功能，以比索引的碎片读取更快的速度访问表记录。

要影响优化器将选择的路径，您需要查看可能添加/修改的索引，以提高索引访问效率，但这应该小心，因为这可能会对其他查询产生不利影响，并可能降低插入性能。

您可以帮助优化器的另一项重要活动是确保表格统计信息保持最新，并以适合表格数据中频率分布变化率的频率刷新

Answer 3

如果使用相关索引+RID执行查询时，99%的行确实会被省略，那么生产环境中最可能的问题是您的统计数据已经过时，并且优化器没有意识到(1,2,3,5)中的ForeignID会将结果集限制为总数据的1%。

这里有一个很好的链接，可以从Pinal Dave那里了解更多关于统计数据的信息：http://blog.sqlauthority.com/2010/01/25/sql-server-find-statistics-update-date-update-statistics/

至于在不更新统计信息的情况下强制优化器遵循正确的路径，您可以使用表提示-如果您知道计划应该使用的索引，其中包含ID和ForeignID列，则将其作为提示保存在查询中，并强制SQL优化器使用索引：

http://msdn.microsoft.com/en-us/library/ms187373.aspx

仅供参考，如果你想从第二个查询中获得最佳性能，请使用这个索引，避免你正在经历的头痛：

create index ix1 on SupportContacts(ForeignID, Id DESC) include (Details);