如何提升SQL Server按关键词出现频次排序的搜索语句执行效率

问题根因说明 #

你遇到的CONTAINS返回结果少的问题，核心是两种匹配逻辑的差异：

• 原LIKE '%pic%'是模糊匹配，只要字符串任意位置出现连续的pic三个字符就命中，比如包含topic、picnic的内容都会被返回
• CONTAINS是全文搜索函数，默认只匹配独立的完整单词，只有内容里单独出现pic这个词才会命中，所以结果量远低于原语句

优化方案 #

方案1：全文搜索优化（性能最优，适合10万行以上的大表） #

全文搜索的性能远高于全表模糊匹配，只要调整配置和语法即可对齐需求：

1. 首先确认你已经为[Table_Web]表创建了全文索引，覆盖[Link], [Title], [Description], [KeyWords], [H1]五个字段，这是CONTAINS高性能的前提
2. 调整搜索语法对齐匹配范围：如果你需要匹配所有以pic开头的单词，把搜索词改为带通配符的格式即可大幅提升命中量
3. 全文搜索自带的RANK权重本身和关键词出现频率正相关，可直接用于排序，也可以在全文过滤后的小结果集里复用你原来的计数逻辑，计算开销极低

优化后代码示例：

SELECT 
    t.[Link], t.[Title], t.[Description], t.[Country]
FROM
    [DMaor_MyData].[dbo].[Table_Web] t
INNER JOIN 
    FREETEXTTABLE([Table_Web], ([Link], [Title], [Description], [KeyWords], [H1]), '"pic*"') ft 
    ON t.主键字段 = ft.[Key] -- 把这里替换成你表的实际主键字段名
ORDER BY 
    ft.RANK DESC, -- 全文自带的词频相关排序，绝大多数场景可直接使用
    -- 如果你需要完全对齐原来的计数逻辑，把上面一行替换为下面的语句即可
    -- (LEN(CONCAT(t.[Link], t.[Title], t.[Description], t.[KeyWords], t.[H1])) - LEN(REPLACE(CONCAT(t.[Link], t.[Title], t.[Description], t.[KeyWords], t.[H1]), 'pic', ''))) / LEN('pic') DESC,
    t.[LINK]
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

该方案性能比原语句高10~100倍，因为全文索引会直接过滤掉绝大多数不匹配的行，后续排序计算只针对少量命中数据。

方案2：计算列+索引优化（完全对齐原模糊匹配逻辑，适合中小表） #

如果你必须保留%pic%任意位置匹配的逻辑，不想调整匹配规则，可以用持久化计算列优化重复计算的开销：

1. 先给表添加持久化计算列，避免每次查询重复拼接字段：

ALTER TABLE [DMaor_MyData].[dbo].[Table_Web] 
ADD SearchContent AS CONCAT([Link], [Title], [Description], [KeyWords], [H1]) PERSISTED;

2. 给计算列创建覆盖索引，避免查询时回表：

CREATE NONCLUSTERED INDEX IX_Table_Web_SearchContent 
ON [DMaor_MyData].[dbo].[Table_Web] (SearchContent)
INCLUDE ([Link], [Title], [Description], [Country]);

3. 优化后的查询语句：

SELECT 
    [Link], [Title], [Description], [Country]
FROM
    [DMaor_MyData].[dbo].[Table_Web]
WHERE 
    SearchContent LIKE '%pic%'
ORDER BY 
    (LEN(SearchContent) - LEN(REPLACE(SearchContent, 'pic', ''))) / LEN('pic') DESC, 
    [LINK]
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

该方案性能比原语句高3~10倍，完全保留了原模糊匹配的逻辑，缺点是前导通配符的匹配依然会走索引扫描，数据量超过100万行时性能不如全文搜索方案。

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