我测试了非聚集索引的好处.
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.
更多推荐
如何执行非聚集索引查找而不是聚集索引扫描
发布评论