自動完成太慢：可能的優化？

October 13, 2017

我網站的自動完成搜尋功能會搜尋包含銷售商品型號的 varchar 欄位。
該欄位可以包含從 1 到 75 個字元的字元串，並且該表包含 400 000 行。我想出了一個查詢，它只從字元串的開頭搜尋，執行大約需要 150-250 毫秒，這是可以接受的，但現在我的經理希望查詢搜尋任何子字元串，這會使查詢慢 3-10 倍（大約 1000-2000 毫秒）。
我已經建構了一個 JS fiddle 來給你一個數據是什麼樣子的例子，以及兩個查詢是什麼樣的。
http://sqlfiddle.com/#!6/9efa3/2/0
表上已經有一些索引。加速這個自動完成搜尋欄位的最佳實踐是什麼？（數據庫版本為 SQLSERVER 2008R2）
這是我正在處理的數據的一個簡短範例：
CREATE TABLE [Products](
   [productid] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   [model] [nvarchar](75) NOT NULL 
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
   [productid] ASC
));

insert into products values ('UMPX1AA0011 danish e-315 woot');
insert into products values ('P27y719VC');
insert into products values ('VG2y439m-LED');
insert into products values ('UMUyX165AAB01');
insert into products values ('U28y79VF');
insert into products values ('U52417HJ');
insert into products values ('VA25746M-LED WITH FLYING CORNERS');
insert into products values ('S19F350HNN 1pc california storage');
insert into products values ('VA211917A');
insert into products values ('PM2500X2');
insert into products values ('E22470SWHE');
insert into products values ('V22465WLYDP');
insert into products values ('I129LMH1HKC');
insert into products values ('OM5EN X 35 the new version');
insert into products values ('DLS3060WDB');
insert into products values ('PVW');
insert into products values ('LI23721S');
insert into products values ('V173516LBM');
insert into products values ('VX2376-SMHD-A');
insert into products values ('GUM5FX1AA1001');
insert into products values ('GPM300X11');
insert into products values ('GUM-WH6AA002');
insert into products values ('2435V5LSB');
insert into products values ('P2418HZ');
insert into products values ('Stylish sectional one of a kind y-5151');
這些是我正在比較的兩個查詢
--runs acceptably fast, about 100-250ms
select * from products where model like 'y-5151'+'%';
--takes too long, around 1000-2500ms
select * from products where model like '%' + 'y-5151' +'%'

我採用的解決方案是建構一個“半三角”表，其中包含Aaron Bertrand在他的以下兩篇部落格文章中建議的所有模型 # 子字元串的預處理版本：
https://sqlperformance.com/2017/02/sql-indexes/seek-leading-wildcard-sql-server
https://sqlperformance.com/2017/02/sql-performance/follow-up-1-leading-wildcard-seeks
我的解決方案是創建一個新表，在其中查找以搜尋字元串開頭的模型。每個模型都是這樣列出的，假設 product_ID 是51，模型是7500 Twin Bed
ID|Model
----------------
51|7500 Twin Bed
51|500 Twin Bed
51|00 Twin Bed
51|0 Twin Bed
51| Twin Bed
51|Twin Bed
51|win Bed
51|in Bed
51|n Bed
51| Bed
51|Bed
51|ed
51|d
這樣你就不需要做一個完整的萬用字元搜尋，一個簡單的select distinct id_product from products_dictionary where model like 'Twin%'將返回所需的結果。現在查詢不到 100 毫秒。
這是我用來創建表並填充它的程式碼。同樣，這一切都在 Aaron 的部落格文章中得到了正確的描述：
CREATE TABLE [dbo].[products_dictionary](
   [Id_product] [int],
   [model] [nvarchar](75) NOT NULL
)
insert into [products_dictionary] 
select p.id_product,f.fragment from products p 
cross apply dbo.CreateStringFragments(p.model) AS f;

create clustered index index_idprod_substrmodel on [Products_dictionary]([model],[Id_product])

當您需要進行複雜和密集的字元串搜尋時，全文索引通常是“轉到”選項，因為它基於從字元串值創建的字典，而不是每次都掃描整個表（當您使用 ‘%某些文本％‘類型的搜尋），如果查詢優化器諮詢字典確定，它將使用索引搜尋。
我創建了一個表並用 100,000 條記錄填充它。
只需使用正常搜尋模式，例如：
select * from products where model like '%' + 'kind' +'%'
我得到了這個結果：
請注意，返回 1102 條記錄，使用與返回一行的其他單詞相同的查詢select * from products where model like '%' + 'human' +'%'：
現在使用全文索引：
使用查詢：select * from products where freetext(model,'kind')
它在返回幾行時效果最好，因為它使用索引查找，而不是掃描
select  * from products where freetext(model,'Human') 
還有一點需要注意：
如果發生某種阻塞，您的查詢可能會比預期的“有時”慢。當有人試圖更新或插入記錄並在包含該行/行的頁面上持有 (X) 鎖時，可能會發生這種情況。由於全表掃描，在使用 ‘% some text %’ 格式搜尋查詢時，您將請求哪個頁面。
使用全文索引可以避免使用索引查找的這些情況，除非您正在搜尋完全相同的記錄，該記錄當時已更新。
還有許多搜尋變體、模糊匹配、“拼寫檢查”變體，它們可能很有用。
**SQL Server 2008**中有一本書Pro Full-Text Search可以幫助您建構正確的搜尋查詢