如何执行非聚集索引查找而不是聚集索引扫描 [英] How to perform nonclustered index seek instead of clustered index scan

问题描述

我测试了非聚集索引的好处.

I test the benefits of nonclustered indexes.

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

I use db AdventureWorks When i execute the query:

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

但是当我执行查询

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

GO

我仍然看到执行中的普通标签集群索引扫描".为什么不聚集索引搜索"?是否应该是聚集索引搜索"?在哪种情况下应为聚集索引查找".

推荐答案

您正在点击索引临界点: City ='Seattle'的条目太多，以至于每个条目都找不到 AddressLine1 在聚集索引中.对于这个特定查询，一种方法是包括计划的列:

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:

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

但是，这隐藏了 real 问题，即为什么您是否有兴趣在这样的非选择性谓词上选择 all 行?该应用程序不应发出此类请求.

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.

这篇关于如何执行非聚集索引查找而不是聚集索引扫描的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！