SQL Server 2019全文检索查询缓慢，求性能优化方案

问题描述 #

我正在使用SQL Server 2019，执行如下全文检索查询时需耗时数分钟才能返回结果集。Text列是nvarchar类型且已启用全文索引，但NoteText表的Text列未创建普通索引。尝试使用FREETEXT后查询仍缓慢，恳请提供更优解决方案。

原查询SQL：

Select distinct
txt.NoteKey
from NoteText AS txt
where (CONTAINS(txt.Text, '("Red")') or 
       CONTAINS(txt.Text, '("Blue")') or
       CONTAINS(txt.Text, '("White")') or
       CONTAINS(txt.Text, '("Blue and white")') or
       CONTAINS(txt.Text, '("Colors")')

优化方案 #

• 合并CONTAINS条件：将多个独立的CONTAINS语句合并为一个，减少全文索引的扫描次数，提升效率。改写后的SQL：

Select distinct
txt.NoteKey
from NoteText AS txt
where CONTAINS(txt.Text, '("Red" OR "Blue" OR "White" OR "Blue and white" OR "Colors")')

• 创建覆盖索引：针对查询涉及的列创建非聚集覆盖索引，避免回表查询开销。如果Text列长度符合索引键限制（≤900字节），可执行：

CREATE NONCLUSTERED INDEX IX_NoteText_Text_NoteKey ON NoteText (Text) INCLUDE (NoteKey);

若Text列过长，可仅将NoteKey设为包含列，聚焦于减少数据读取量。

• 维护全文索引：确保全文索引处于最新状态，无碎片化。手动触发全量更新：

ALTER FULLTEXT INDEX ON NoteText START FULL POPULATION;

建议定期执行增量更新，保证索引时效性。

• 移除不必要的DISTINCT：先验证NoteKey在符合条件的记录中是否唯一，若唯一则去掉DISTINCT，减少排序去重的额外开销。

• 改用CONTAINSTABLE：通过关联全文索引内部表的方式，可能获得更好的性能：

SELECT DISTINCT ct.[KEY] AS NoteKey
FROM NoteText txt
INNER JOIN CONTAINSTABLE(NoteText, Text, '("Red" OR "Blue" OR "White" OR "Blue and white" OR "Colors")') ct
ON txt.NoteKey = ct.[KEY];

内容的提问来源于stack exchange，提问作者Wan