为什么这是一个索引扫描而不是索引搜索？ [英] Why is this an Index Scan and not a Index Seek?

问题描述

以下是查询：

SELECT      top 100 a.LocationId, b.SearchQuery, b.SearchRank
FROM        dbo.Locations a
INNER JOIN  dbo.LocationCache b ON a.LocationId = b.LocationId
WHERE       a.CountryId = 2
AND         a.Type = 7

位置索引：

PK_Locations：

PK_Locations:

LocationId

LocationId

IX_Locations_CountryId_Type：

IX_Locations_CountryId_Type:

CountryId，Type

CountryId, Type

LocationCache索引：

PK_LocationCache：

PK_LocationCache:

LocationId

LocationId

IX_LocationCache_LocationId_SearchQuery_SearchRank：

IX_LocationCache_LocationId_SearchQuery_SearchRank:

LocationId，SearchQuery，SearchRank

LocationId, SearchQuery, SearchRank

执行计划：

所以它在Locations上进行索引搜索，使用覆盖索引，很酷。

So it's doing a Index Seek on Locations, using the covering index, cool.

但为什么它在覆盖索引的LocationCache上进行索引扫描？

But why it is doing a Index Scan on the LocationCache covering index?

覆盖索引在索引中有LocationId，SearchQuery，SearchRank（而不是包含的列）。

That covering index has LocationId, SearchQuery, SearchRank in the index (not as "Included columns").

悬停在索引扫描上：

此查询需要进入SQL Server FTS目录服务的索引视图，由自动完成插件使用，因此需要100％优化。

This query needs to go in an indexed view served by a SQL Server FTS catalogue, consumed by an autocomplete plugin, so it needs to be 100% optimized.

目前上述查询需要3秒钟。它应该是< 0。

At the moment that above query is taking 3 seconds. It should be < 0.

任何想法？

推荐答案

同时请记住当使用 INNER LOOP JOIN 强制覆盖索引在<$ c上使用时，它将导致查询在执行其他更改时可能执行得很糟糕$ c> dbo.LocationCache 。

Whilst bearing in mind that it will result in a query that may perform badly as and when additional changes are made to it, using an INNER LOOP JOIN should force the covering index to be used on dbo.LocationCache.

SELECT      top 100 a.LocationId, b.SearchQuery, b.SearchRank
FROM        dbo.Locations a
INNER LOOP JOIN dbo.LocationCache b ON a.LocationId = b.LocationId
WHERE       a.CountryId = 2
AND         a.Type = 7

这篇关于为什么这是一个索引扫描而不是索引搜索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！