使用單個搜尋字元串搜尋多列時優化性能

我們有一個系統可以導入客戶從資訊亭或線上填寫的豁免。由於數據輸入是由客戶執行的，因此欄位上的輸入格式可能非常不一致。我想讓我的使用者輸入單個搜尋字元串，並有最好的機會找到客戶的記錄。這是我編寫的範例表和查詢，以及使用者可以輸入的一些範例搜尋字元串。我的應用程序會將類似搜尋的“%”萬用字元添加到使用者的輸入中。該表目前大約有 30K 行，預計每年還會增長 20K。

1. 這是編寫查詢以便單個搜尋字元串可以搜尋所有這些列的好方法嗎？
2. 為了提高性能，我應該在這些列中的每一列上創建一個單獨的索引嗎？對於 FirstName 和 LastName 列，對於這種類型的搜尋，每列上的單獨索引或多列索引會更好嗎？

DECLARE 
   @search VarChar(100)

SET @search = '%George Smith%'
/*
SET @search = '%George%'
SET @search = '%Smith%'
SET @search = '%gsmith2643@gmail.com%'
SET @search = '%47684765%'
*/

SELECT * FROM Waiver w
WHERE 
   w.FirstName like @search or
   w.LastName like @search or
   CONCAT(w.FirstName, ' ', w.LastName) like @search or
   w.Email like @search or
   w.DriversLicense like @search
   

在這種情況下，我會盡量避免使用 OR 運算符。我實際上在最近的一篇博文中提到了停止在 UPDATES 中使用 OR 運算符。雖然那篇文章是關於更新的，但這個概念應該在這裡翻譯得很好。基本上，使用 OR 運算符將使索引具有挑戰性。您需要知道在您創建的任何索引中哪個列需要排在第一位，並希望它是 SELECTIVE 並且始終有輸入。

此外，您需要避免在搜尋字元串中使用前導 ‘%’，因為在這種情況下沒有索引可以提供幫助。視覺化為什麼沒有索引的最簡單方法將幫助它想到電話簿。我知道，很多人可能不知道這些是什麼了。現在，圖片試圖查找您缺少某人姓氏的第一個字母的電話號碼。如果你知道這個名字以“M”開頭，你可能只是翻到女士。然後如果你登陸“Ma”，可能會再翻幾頁，但需要“Mc”。無論哪種情況，你都會到達那裡很快。但是如果沒有第一個字母，您將不得不查看整個電話簿的從前到後的每條記錄，以找到像 ‘%cCaffrey 這樣的名字，例如。

由於名字和姓氏之間的連接，您可能需要考慮計算列或索引視圖上的索引。您可能需要進行試驗，看看哪一種最適合您的方案。例如，這將是一個帶有索引的計算列。

ALTER TABLE Waiver ADD FullName AS CONCAT(FirstName, ' ', LastName)) PERSISTED
GO
CREATE NONCLUSTERED INDEX IX_FullName ON dbo.Waiver (FullName)
GO

如果您繼續在 WHERE 子句中連接這些列，它們每次都會產生全表掃描。SQL Server 無法知道兩列等於您的條件，直到它為表中的每一行連接它們。這些問題現在可能不會成為 30k 行的問題，但隨著行數的增長可能會成為大問題。

如果您必須使用通用搜尋框，我會嘗試像這樣編寫您的查詢。在這種情況下，您可以為每一列創建一個唯一索引。你正在使用SELECT *，所以無論如何你都會一直在做 KEY LOOKUPS 。

DECLARE @search VarChar(100)

SET @search = 'Smith'

SELECT *
FROM Waiver
WHERE LastName LIKE @search + '%'

UNION

SELECT *
FROM Waiver
WHERE FullName LIKE @search + '%'

UNION

SELECT *
FROM Waiver
WHERE Email LIKE @search + '%'

UNION
   
SELECT *
FROM Waiver
WHERE DriversLicense LIKE @search + '%'

我從範例中的搜尋中刪除了 FirstName，因為它已經在 FullName 搜尋中遇到。

您仍然需要評估常見搜尋詞的附加索引。

話雖如此，我建議您看看是否可以在網頁上創建多個搜尋框，每個搜尋條件一個。

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