SQL Server如何优化字段包含多个搜索分词的查询性能？

1. 替换多语句表值函数为内联表值函数 #

你当前使用的*多语句表值函数(MTVF)会强制SQL Server生成串行执行计划，无法和外层的筛选、分页逻辑联合优化，逐行处理的模式性能极低。替换为内联表值函数(ITVF)*后，函数逻辑会被查询优化器直接展开到主查询中，可以充分利用索引、并行执行等优化手段，性能会有明显提升。
优化后的函数示例：

CREATE FUNCTION myFunctionName(@searchText NVARCHAR(MAX))
RETURNS TABLE 
AS
RETURN
(
    WITH SearchTokens AS (
        SELECT DISTINCT value AS token 
        FROM STRING_SPLIT(@searchText, ' ')
        WHERE value <> '' -- 过滤用户输入多空格生成的无效空token
    ),
    TokenCount AS (
        SELECT COUNT(*) AS cnt FROM SearchTokens
    )
    SELECT /* 替换为你需要返回的列 */
    FROM myTable t
    CROSS JOIN TokenCount tc
    WHERE EXISTS (
        SELECT 1 FROM SearchTokens st
        WHERE CHARINDEX(st.token, t.myTechnicalColumn) > 0
        HAVING COUNT(DISTINCT st.token) = tc.cnt
    )
)

如果暂时不想改动检索逻辑，也可以将原逻辑中的子查询改为关联分组写法，执行效率也会高于关联子查询：

SELECT t./* 你要查询的所有列 */
FROM myTable t
JOIN @searchToken st ON CHARINDEX(st.token, t.myTechnicalColumn) > 0
GROUP BY t./* 你要查询的所有列 */
HAVING COUNT(DISTINCT st.token) = @searchTextLength

2. 给拼接字段添加全文索引 #

当前用CHARINDEX做包含匹配会触发全表扫描，每一行都要遍历所有分词做字符串比对，数据量越大性能越差。SQL Server的全文索引是专门为自由文本检索设计的特性，会提前对字段内容做分词、建立倒排索引，匹配效率比逐行字符串比对高1~2个数量级。
操作步骤：

1. 确认SQL Server全文检索服务已启动
2. 创建全文目录，对myTechnicalColumn创建全文索引
3. 修改检索逻辑，直接使用CONTAINS语法匹配多关键词
   优化后的函数示例：

CREATE FUNCTION myFunctionName(@searchText NVARCHAR(MAX))
RETURNS TABLE 
AS
RETURN
(
    DECLARE @containsCondition NVARCHAR(MAX) = '"' + REPLACE(STRING_ESCAPE(@searchText, 'json'), ' ', '" AND "') + '"'
    SELECT /* 替换为需要返回的列 */
    FROM myTable
    WHERE CONTAINS(myTechnicalColumn, @containsCondition)
)

注意STRING_ESCAPE用于转义用户输入的双引号等特殊字符，避免全文检索语法报错。

3. 优化分词与字段拼接逻辑 #

• 拆分搜索文本后先做去重、过滤空值处理，避免用户输入重复关键词、连续空格生成无效token，减少不必要的匹配计算
• 若业务不需要区分大小写、重音，可将myTechnicalColumn的排序规则设置为大小写不敏感、重音不敏感，避免比对时额外的字符转换开销
• 生成myTechnicalColumn时，每个字段前后都添加空格，避免不同列的值拼接后产生误匹配（比如列A值为Eng、列B值为ine，拼接后变成Engine导致误命中）

4. 适配分页、排序逻辑减少处理量 #

当前逻辑会先查询所有符合条件的行再做分页，数据量大的话开销极高。可以把分页的OFFSET/FETCH、排序条件直接写到内联函数中，提前过滤不需要返回的行；同时给排序字段添加对应索引，避免执行时的临时排序开销。

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