如何执行非聚簇索引查找而不是聚簇索引扫描

I test the benefits of nonclustered indexes.

我测试了非聚簇索引的好处。

I use db AdventureWorks When i execute the query:

我使用db AdventureWorks当我执行查询时：

SELECT [address].City, [address].[AddressLine1] 
FROM [AdventureWorks].[Person].[Address] as [address]
WHERE [address].City = 'Seattle'

I see in execution plan tab

我在执行计划选项卡中看到

/*
Missing Index Details from SQLQuery3.sql - 
The Query Processor estimates that implementing the following index could improve the query cost by 97.9636%.
*/

/*
USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Person].[Address] ([City])

GO
*/

And i see in the execution plain tab icon "Clustered index scan" and i know that it is bad because index seek is better

我在执行简单选项卡图标“聚集索引扫描”中看到，我知道它很糟糕，因为索引搜索更好

But when i execute query

但是当我执行查询时

USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX CityIdx
ON [Person].[Address] ([City])

GO

I still see the in execution plain tab "Clustered index scan". WHY not "Clustered index seek" ? Does it should be "Clustered index seek" ? IN which cases it should be "Clustered index seek".

我仍然看到执行中的普通标签“Clustered index scan”。为什么不“聚集指数寻求”？它应该是“聚集索引寻求”吗？在哪些情况下它应该是“聚集索引寻求”。

1 个解决方案

#1

You are hitting the index tipping point: there are simply too many entries with City = 'Seattle' to bother seeking, for each one, the AddressLine1 in the clustered index. One approach, for this particular query, is to include the projected column:

您正在达到索引引爆点：City ='Seattle'的条目太多，无法为每个条目寻找聚集索引中的AddressLine1。对于此特定查询，一种方法是包括投影列：

CREATE NONCLUSTERED INDEX CityIdx
ON [Person].[Address] ([City])
INCLUDE ([AddressLine1]);

But that hides the real issue, namely why are are you interested in selecting all rows on such a non-selective predicate? The application should not make such requests.

但这隐藏了真正的问题，即为什么你有兴趣选择这种非选择性谓词的所有行？申请不应提出此类要求。