SQL Servere 通過LIKE在另一個字元串中查找字元串

最新 08-03

我們經常需要在另一個字元串中搜索字元串。對於匹配需要精確的情況，我們可以使用帶有通配符的LIKE運算符來指定模式。雖然這滿足需求，但在較大的數據集上查詢會非常慢，因為它需要SQL Server在遍歷所有行時檢查每個字元串。通過給欄位添加索引可以大大加快操作速度，但是索引也有其局限性。

本文蟲蟲給大家介紹了模糊搜索LIKE在開頭(%abcd)或結尾搜索（abcd%），而是在字元串中的任何位置搜索（%abcd%）的情況。

為了說明這個問題，首先我們構建一個簡單的數據表作為實例：

CREATE TABLE dbo.String

(

StringId INT NOT NULL PRIMARY KEY IDENTITY(1, 1) ,

String VARCHAR(20) NOT NULL

);

我們使用大小5M行的樣本數據集填充String表，其中String列中只有20個字元長度，StringID為一個整數，非空（NOT NULL），主鍵（PRIMARY KEY）。

我們用他來測試不同查詢的性能比對：

不用索引

要查找String列中以字元序列"abcd"開頭的字元串數，我們使用以下查詢：

SELECT COUNT(1)

FROM dbo.String

WHERE String LIKE "abcd%";

該查詢在14秒內執行。

要查找包含"abcd"序列的所有字元串的計數，查詢為：

SELECT COUNT(1)

FROM dbo.String

WHERE String LIKE "%abcd%";

執行時間也一樣的。因為SQL Server必須執行全表掃描並在任何一種情況下檢查每個字元串的匹配項。

索引優化

加速查詢的常用的技術給該欄位String創建索引：

CREATE NONCLUSTERED INDEX ix_nc_test ON dbo.String (String);

在構建索引之後，使用"abcd%"謂詞執行查詢在1ms內得到執行。第二個查詢不會走索引，所以不能得到加速。

因為索引從第一個字元排序到最後一個字元，並且查找匹配字元串的操作類似於我們之前看到的操作。如果LIKE謂詞是"%abcd"並且我們想要搜索以"abcd"字元結尾的字元串，我們可以使用另一個優化，對該欄位穿件反向索引。

但是，正向和反向索引好像都幫不到我們原始的需求（實際上是模糊查詢），如果查詢字元可能出現在我們表中的字元串可能任何的位置。

構建臨時表

這是一個普遍的問題，如果我們對字元串長度有長短限制，我們可以犧牲存儲空間和通過來剪切字元串，構造臨時表，通過"abcdefgh"這樣的字元串中構造八個子字元串並存儲它們以下列方式在臨時表，包含StringID和拆分的子段：

abcdefgh

bcdefgh

cdefgh

defgh

efgh

fgh

執行此操作後，我們可以索引列並使用後置通配符執行LIKE搜索。這會幫我們找到搜索的字元串，如果該字串出現在這個字元串中的話。

當然，這意味著對於長度為3的字元串，我們在新表中得到3行。隨著字元串長度的增加，存儲它所需的行數也會幾何增加。比如，要存儲長度為n的字元串所需的字元總數（以及空間）等於等差數列：n + (n-1) + (n-2) + … + 2 + 1,其值為(n*(n+1))/2。對於長度為100的字元串，這將變為5050個字元。對於長度為20的字元串，必須存儲的字元數為210.所以這個過程會導致需要更多的資源處理查詢。

對於較長的字元串，使用這種臨時表查詢性能可能無法證明這些資源的開銷。考慮到諸如系統的整體吞吐量之類的其他因素，可以將此技術的應用限制為諸如電子郵件，用戶名，短文章和類似的字元串，等等對較短欄位。

下面我們在一個StringSplit臨時表中測試性能，這個表為填充了欄位分割後String表中的數據，其中字元串按照上面所述分割方法進行拆分（StringId對於每個子字元串都是相同的）：

CREATE TABLE dbo.StringsSplit

(

StringId INT NOT NULL ,

StringSplit VARCHAR(20) NOT NULL

);

5M的原始表處理完成後表中的行計數為100M。我們在StringSplit列上創建聚簇索引：

CREATE CLUSTERED INDEX ix_c_test ON dbo.StringSplit (StringSplit);

構建好後，現在我們可以通過以下詢查詢：

SELECT COUNT(StringId)

FROM dbo.String