在 where 子句中同時使用 ‘contains’ 和 ‘=’ 時查詢速度慢

September 16, 2019

以下查詢需要大約 10 秒才能在具有 12k 條記錄的表上完成

select top (5) *
from "Physician"
where "id" = 1 or contains("lastName", '"a*"')

但是，如果我將 where 子句更改為

where "id" = 1

或者

where contains("lastName", '"a*"')

它會立即返回。

兩列都被索引，lastName 列也被全文索引。

CREATE TABLE Physician
(
  id         int identity    NOT NULL,
  firstName  nvarchar(100)   NOT NULL,
  lastName   nvarchar(100)   NOT NULL
);

ALTER TABLE Physician
 ADD CONSTRAINT Physician_PK
 PRIMARY KEY CLUSTERED (id);

CREATE NONCLUSTERED INDEX Physician_IX2
  ON Physician (firstName ASC);

CREATE NONCLUSTERED INDEX Physician_IX3
  ON Physician (lastName ASC);

CREATE FULLTEXT INDEX
   ON "Physician" ("firstName" LANGUAGE 0x0, "lastName" LANGUAGE 0x0)
   KEY INDEX "Physician_PK"
   ON "the_catalog"
   WITH stoplist = off;

這是執行計劃

可能是什麼問題呢？

你的執行計劃
查看查詢計劃時，我們可以看到一個索引被觸動來服務兩個過濾操作。
非常簡單地說，由於 TOP 運算符，設置了行目標。可以在此處找到有關行目標的更多資訊和先決條件
來自同一來源：
行目標策略通常意味著支持非阻塞導航操作（例如，嵌套循環連接、索引查找和查找）而不是阻塞、基於集合的操作（如排序和散列）。只要客戶端可以從快速啟動和穩定的行流中受益（可能有更長的總體執行時間——請參閱上面的 Rob Farley 的文章），這將很有用。還有更明顯和傳統的用途，例如一次顯示一個頁面的結果。
使用具有行目標集的左半連接對整個表進行探測，希望盡可能快速有效地返回 5 行。
這不會發生，導致對 .Fulltextmatch TVF 進行多次迭代。
重新創造
根據您的計劃，我能夠在某種程度上重現您的問題：
CREATE TABLE dbo.Person(id int not null,lastname varchar(max));

CREATE UNIQUE INDEX ui_id ON  dbo.Person(id)
CREATE FULLTEXT CATALOG ft AS DEFAULT;  
CREATE FULLTEXT INDEX ON dbo.Person(lastname)   
  KEY INDEX ui_id   
  WITH STOPLIST = SYSTEM;  
GO  

INSERT INTO dbo.Person(id,lastname)
SELECT top(12000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
REPLICATE(CAST('A' as nvarchar(max)),80000)+ CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as varchar(10))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
CREATE CLUSTERED INDEX cx_Id on dbo.Person(id);
執行查詢
SELECT TOP (5) *
FROM dbo.Person
WHERE "id" = 1 OR contains("lastName", '"B*"');
結果生成與您的查詢計劃相當的查詢計劃：
在上面的範例中，全文索引中不存在 B。因此，查詢計劃的效率取決於參數和數據。
可以在Row Goals, Part 2: Semi Joins by Paul White中找到對此的更好解釋
…換句話說，在應用的每次迭代中，一旦找到第一個匹配項，我們就可以停止查看輸入 B，使用下推連接謂詞。這正是行目標所擅長的事情：生成優化計劃的一部分，以快速返回前 n 個匹配行（此處 n = 1）。
例如，更改謂詞以便更快地找到結果（在掃描開始時）。
select top (5) *
from dbo.Person
where "id" = 124 
or contains("lastName", '"A*"');
where "id" = 124由於全文索引謂詞已經返回 5 行，滿足謂詞，因此被消除TOP()。
結果也表明了這一點
id lastname 
1  'AAA...'   
2  'AAA...'
3  'AAA...'
4  'AAA...'
5  'AAA...'
以及 TVF 處決：
在此處輸入圖像描述
插入一些新行
INSERT INTO dbo.Person
SELECT 12001, REPLICATE(CAST('B' as nvarchar(max)),80000);
INSERT INTO dbo.Person
SELECT 12002, REPLICATE(CAST('B' as nvarchar(max)),80000);
執行查詢以查找這些先前插入的行
SELECT TOP (2) *
from dbo.Person
where "id" = 1
or contains("lastName", '"B*"');
這再次導致對幾乎所有行的迭代次數過多，無法返回最後一個但找到的值。
id   lastname
1     'AAA...'
12001 'BBB...'
正在解決
使用 traceflag 4138 刪除行目標時
SELECT TOP (5) *
FROM dbo.Person
WHERE "id" = 124 
OR contains("lastName", '"B*"')
OPTION(QUERYTRACEON 4138 );
優化器使用更接近於實現 a 的連接模式UNION，在我們的例子中這是有利的，因為它將謂詞下推到它們各自的聚集索引搜尋，並且不使用行目標左半連接運算符。
不使用上述跟踪標誌的另一種編寫方式：
SELECT top (5) *
FROM
(
SELECT * 
FROM dbo.Person
WHERE "id" = 1 
UNION
SELECT * 
FROM dbo.Person
WHERE contains("lastName", '"B*"')
) as A;
使用生成的查詢計劃：
直接應用全文函式的地方
作為旁注，對於 op，查詢優化器修補程序 traceflag 4199 解決了他的問題。他通過添加OPTION(QUERYTRACEON(4199))到查詢來實現這一點。我最終無法重現這種行為。此修補程序確實包含半連接優化：
跟踪標誌：4102 功能：SQL 9 - 如果查詢的執行計劃包含半連接運算符，則查詢性能變慢通常，當查詢包含 IN 關鍵字或 EXISTS 關鍵字時，會生成半連接運算符。啟用標誌 4102 和 4118 來克服這個問題。
來源
額外的
在基於成本的優化期間，優化器還可以將索引假離線添加到執行計劃中，由LogOp_Spool Index on fly Eager （或物理對應物）實現
它對我的數據集執行此操作，TOP(3)但不適用於TOP(2)
SELECT TOP (3) *
from dbo.Physician
where "id" = 1
or contains("lastName", '"B*"')  
在第一次執行時，一個急切的假離線在返回謂詞請求的行子集之前讀取並儲存整個輸入後來的執行從工作表中讀取並返回相同或不同的行子集，而不必執行子再次結點。
來源
將搜尋謂詞應用於此索引急切假離線：