使用“或”運算符時的 SQL Server 索引掃描

我們實現了一個Google風格的搜尋，在前端觸發去抖動後執行 SQL 查詢。（我們知道 SQL 可能是錯誤的技術，但我在這裡陷入了啟動混亂。）查詢：

SELECT 
   TOP(50) [Name], [Surname]
FROM 
   [dbo].[Clients]
WHERE 
   [Name] LIKE @SearchTerm + '%' OR
   [Surname] LIKE @SearchTerm + '%'

這是一個相當大的表，所以我在兩列上添加了兩個非聚集索引以幫助加快速度：

CREATE NONCLUSTERED INDEX [IX_Patients_Name] ON [dbo].[Clients]
(
   [Name] ASC
)
INCLUDE([Surname]);

CREATE NONCLUSTERED INDEX [IX_Patients_Surname] ON [dbo].[Clients]
(
   [Surname] ASC
)
INCLUDE([Name]);

我的想法是 SQL 會在兩列上進行索引搜尋，但查詢優化器似乎決定使用索引掃描。

對於這個簡單的案例來說，這可能不是一個真正的問題，但我們有更複雜的版本，帶有多個連接等。

有什麼方法可以優化此查詢以使用搜尋？

正如您在問題中提到的那樣，這種 Google 風格的查詢並不是 SQL Server 真正“擅長的”。Erik Darling 在他的文章The Only Thing Worse than Optional Parameters…中談到了這種確切的查詢反模式。

所有這些。

使用這種類型的查詢可以自然地進行搜尋，但是正如您所注意到的那樣，進行掃描更為常見。這是來自 StackOverflow2010 範例數據庫的範例。

首先，我將創建這兩個有用的索引：

CREATE NONCLUSTERED INDEX IX_DisplayName ON dbo.Users (DisplayName) INCLUDE ([Location]);
CREATE NONCLUSTERED INDEX IX_Location ON dbo.Users ([Location]) INCLUDE (DisplayName);
GO

然後，我將創建一個類似於您所擁有的過程：

CREATE OR ALTER PROCEDURE dbo.sp_Test
   @SearchTerm nvarchar(100)
AS
BEGIN;
   SELECT TOP (50)
       DisplayName, 
       [Location]
   FROM 
       dbo.Users
   WHERE 
       DisplayName LIKE @SearchTerm + '%' OR
       [Location] LIKE @SearchTerm + '%'
END;
GO

如果我使用相當有選擇性的參數執行該過程，我將得到一個 index union plan。如果參數的選擇性較低，則改為使用覆蓋索引之一的掃描。

DBCC FREEPROCCACHE;
GO
EXEC dbo.sp_Test @SearchTerm = N'Josh';
GO
DBCC FREEPROCCACHE;
GO
EXEC dbo.sp_Test @SearchTerm = N'S';
GO

執行計劃在這裡。

請注意，即使您UNION直接將其編寫為單獨的查詢也是如此。

如鍊接文章中所述，可靠地獲取索引聯合計劃的一種方法是向FORCESEEK您要聯合的表添加提示。

如果我將 proc 更改為此，我不會對任何一個計劃進行掃描：

CREATE OR ALTER PROCEDURE dbo.sp_Test
   @SearchTerm nvarchar(100)
AS
BEGIN;
   SELECT TOP (50)
       DisplayName, 
       [Location]
   FROM 
       dbo.Users WITH (FORCESEEK)
   WHERE 
       DisplayName LIKE @SearchTerm + '%' OR
       [Location] LIKE @SearchTerm + '%'
END;
GO

無論如何簡化了問題，查詢的更大問題是您使用TOP的沒有ORDER BY，這可能會根據使用的索引產生截然不同的搜尋結果。確保您的真實查詢具有ORDER BY, 或者以某種方式解決了此問題。

引用自：https://dba.stackexchange.com/questions/275352