SQL实现单字符容错的智能模糊搜索优化方案问询

需要从大型数据仓库拉取数据列表，当前已实现按空格拆分搜索词分别进行模糊搜索的功能，同时会将搜索词列表传给JS端做高亮处理。现在希望实现单字符容错搜索：例如搜索'paston'时，能匹配'piston'或'peston'这类仅单个字符不同的结果。自己想到的方案是将搜索词的每个字符依次替换为通配符'_'，再用OR拼接条件，但担心该方案性能不佳。询问SQL中是否有现成的实现结构或更优建议，相关代码如下：

SQL存储过程 #

ALTER PROCEDURE [dbo].[GetStoklar]                
@PageIndex  nvarchar(15)
,@PageSize nvarchar(15)
,@Ara nvarchar(max)
AS
  BEGIN 
    DECLARE  @sql NVARCHAR(MAX);   
    DECLARE @NAME VARCHAR(100);
SET @sql = '
Select STOK_KODU,STOK_ADI,GRUP_KODU from TBLSTSABIT where 1=1'

DECLARE CUR CURSOR FOR
 SELECT Deger FROM dbo.splitstring(''+@Ara+'')
OPEN CUR
FETCH NEXT FROM CUR INTO @NAME
WHILE @@FETCH_STATUS = 0
  BEGIN
   SET @sql=@sql+' AND STOK_ADI+STOK_KODU+GRUP_KODU LIKE  ''%'+@NAME+'%'''
      FETCH NEXT FROM CUR INTO @NAME
  END

CLOSE CUR

DEALLOCATE CUR 
SET @sql = @sql + ' order by STOK_KODU asc offset (CAST('+@PageIndex+' as int)*CAST('+@PageSize+' as int)) Rows fetch next CAST('+@PageSize+' as int) rows only
'
                  EXEC sp_executesql @sql; 
                    PRINT  @SQL;
 
    RETURN 
  END 

C#端代码 #

StringBuilder test = new StringBuilder();
JsonModel jsonmodel = new JsonModel();
if (Arama != "")
{
    foreach (var item in Arama.Split(' '))
    {
        test.Append(item + "~");
    }
}
test.Remove(test.ToString().Length - 1, 1);
jsonmodel.Filtre = test.ToString();
int pagesize = 15;
var tbrow = isStatic.GetStokListesi(pageindex, pagesize, Arama);
jsonmodel.NoMoredata = tbrow.Count < pagesize;
jsonmodel.HTMLString = isStatic.RenderToString(PartialView("_partial", tbrow));
return Json(jsonmodel);

查询函数 #

public static List<Stoklar> GetStokListesi(int pageindex, int pagesize, string Arama)
{
    using (blabla db = new blabla())
    {
        return db.Database.SqlQuery<Stoklar>("[GetStoklar] @PageIndex,@PageSize,@Ara",
             new SqlParameter("@PageIndex", pageindex.ToString()), new SqlParameter("@PageSize", pagesize.ToString()), new SqlParameter("@Ara", Arama)).ToList();
    }
}

询问能否在SQL端进行更合理的优化实现？

一、用编辑距离（Levenshtein距离）实现精准单字符容错 #

SQL Server无内置编辑距离函数，需自定义实现，核心是计算两个字符串的编辑步数（插入、删除、替换），仅保留步数≤1的结果。为避免全表扫描，先过滤长度差≤1的记录，减少计算量。

1. 自定义Levenshtein距离函数 #

CREATE FUNCTION dbo.LevenshteinDistance
(
    @s NVARCHAR(4000),
    @t NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN
    DECLARE @len_s INT = LEN(@s), @len_t INT = LEN(@t);
    IF @len_s = 0 RETURN @len_t;
    IF @len_t = 0 RETURN @len_s;

    -- 用CTE生成数字序列替代Numbers表（若有预建Numbers表性能更优）
    WITH Numbers AS (
        SELECT TOP (4000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM sys.all_columns
    )
    DECLARE @d TABLE(i INT, j INT, dist INT);
    INSERT INTO @d
    SELECT n, 0, n FROM Numbers WHERE n <= @len_s
    UNION ALL
    SELECT 0, n, n FROM Numbers WHERE n <= @len_t;

    DECLARE @i INT = 1, @j INT, @cost INT;
    WHILE @i <= @len_s
    BEGIN
        SET @j = 1;
        WHILE @j <= @len_t
        BEGIN
            SET @cost = CASE WHEN SUBSTRING(@s, @i, 1) = SUBSTRING(@t, @j, 1) THEN 0 ELSE 1 END;
            UPDATE @d
            SET dist = (
                SELECT MIN(d) FROM (
                    VALUES (
                        (SELECT dist FROM @d WHERE i = @i-1 AND j = @j)+1,
                        (SELECT dist FROM @d WHERE i = @i AND j = @j-1)+1,
                        (SELECT dist FROM @d WHERE i = @i-1 AND j = @j-1)+@cost
                    ) AS vals(d)
                ) AS min_dist
            ) WHERE i = @i AND j = @j;
            SET @j += 1;
        END;
        SET @i += 1;
    END;

    RETURN (SELECT dist FROM @d WHERE i = @len_s AND j = @len_t);
END;
GO

2. 修改存储过程使用编辑距离 #

将参数类型改为INT避免冗余转换，用参数化动态SQL防止注入，同时加入长度过滤逻辑：

ALTER PROCEDURE [dbo].[GetStoklar]                
@PageIndex INT,
@PageSize INT,
@Ara NVARCHAR(MAX)
AS
BEGIN 
    SET NOCOUNT ON;

    -- 拆分搜索词到临时表
    DECLARE @SearchTerms TABLE(Term NVARCHAR(100));
    INSERT INTO @SearchTerms
    SELECT LTRIM(RTRIM(Deger)) 
    FROM dbo.splitstring(@Ara)
    WHERE LTRIM(RTRIM(Deger)) <> '';

    DECLARE @sql NVARCHAR(MAX) = N'
    SELECT STOK_KODU, STOK_ADI, GRUP_KODU 
    FROM TBLSTSABIT t
    WHERE 1=1';

    -- 为每个搜索词添加编辑距离条件
    DECLARE @Term NVARCHAR(100);
    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT Term FROM @SearchTerms;
    OPEN cur;
    FETCH NEXT FROM cur INTO @Term;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql += N'
        AND (
            dbo.LevenshteinDistance(t.STOK_ADI + t.STOK_KODU + t.GRUP_KODU, @CurrentTerm) <= 1
            OR LEN(t.STOK_ADI + t.STOK_KODU + t.GRUP_KODU) BETWEEN LEN(@CurrentTerm)-1 AND LEN(@CurrentTerm)+1
        )';
        FETCH NEXT FROM cur INTO @Term;
    END;
    CLOSE cur;
    DEALLOCATE cur;

    -- 分页与排序
    SET @sql += N'
    ORDER BY STOK_KODU ASC 
    OFFSET (@PageIndex * @PageSize) ROWS 
    FETCH NEXT @PageSize ROWS ONLY';

    -- 执行参数化查询
    EXEC sp_executesql @sql, 
        N'@PageIndex INT, @PageSize INT, @CurrentTerm NVARCHAR(100)',
        @PageIndex = @PageIndex, @PageSize = @PageSize, @CurrentTerm = @Term;
END 
GO

二、优化通配符方案降低性能损耗 #

若坚持使用通配符逻辑，可通过限制OR条件数量、先过滤长度匹配记录来优化：

1. 生成单字符通配符的函数 #

CREATE FUNCTION dbo.GenerateSingleWildcardTerms
(
    @Term NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN
(
    SELECT REPLACE(@Term, SUBSTRING(@Term, n, 1), '_') AS WildcardTerm
    FROM (SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_columns) Numbers
    WHERE n BETWEEN 1 AND LEN(@Term)
    UNION ALL
    SELECT @Term + '_' -- 允许末尾多一个字符
    UNION ALL
    SELECT '_' + @Term -- 允许开头多一个字符
)
GO

2. 存储过程中使用优化后的通配符逻辑 #

ALTER PROCEDURE [dbo].[GetStoklar]                
@PageIndex INT,
@PageSize INT,
@Ara NVARCHAR(MAX)
AS
BEGIN 
    SET NOCOUNT ON;

    DECLARE @SearchTerms TABLE(Term NVARCHAR(100));
    INSERT INTO @SearchTerms
    SELECT LTRIM(RTRIM(Deger)) 
    FROM dbo.splitstring(@Ara)
    WHERE LTRIM(RTRIM(Deger)) <> '';

    DECLARE @sql NVARCHAR(MAX) = N'
    SELECT STOK_KODU, STOK_ADI, GRUP_KODU 
    FROM TBLSTSABIT t
    WHERE 1=1';

    DECLARE @Term NVARCHAR(100);
    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT Term FROM @SearchTerms;
    OPEN cur;
    FETCH NEXT FROM cur INTO @Term;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql += N'
        AND EXISTS (
            SELECT 1 
            FROM dbo.GenerateSingleWildcardTerms(@CurrentTerm) wt
            WHERE t.STOK_ADI + t.STOK_KODU + t.GRUP_KODU LIKE ''%'' + wt.WildcardTerm + ''%''
        )';
        FETCH NEXT FROM cur INTO @Term;
    END;
    CLOSE cur;
    DEALLOCATE cur;

    SET @sql += N'
    ORDER BY STOK_KODU ASC 
    OFFSET (@PageIndex * @PageSize) ROWS 
    FETCH NEXT @PageSize ROWS ONLY';

    EXEC sp_executesql @sql, 
        N'@PageIndex INT, @PageSize INT, @CurrentTerm NVARCHAR(100)',
        @PageIndex = @PageIndex, @PageSize = @PageSize, @CurrentTerm = @Term;
END 
GO

三、通用性能优化建议 #

• 索引优化：创建合并字段的计算列并加索引，或用全文索引：

  -- 创建计算列
  ALTER TABLE TBLSTSABIT ADD CombinedText AS STOK_ADI + STOK_KODU + GRUP_KODU PERSISTED;
  -- 普通索引（仅后缀模糊搜索有效）
  CREATE NONCLUSTERED INDEX IX_TBLSTSABIT_CombinedText ON TBLSTSABIT(CombinedText);
  -- 全文索引（适合各类模糊搜索）
  CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
  CREATE FULLTEXT INDEX ON TBLSTSABIT(CombinedText) KEY INDEX PK_TBLSTSABIT;
  

• 替换游标：SQL Server 2017+可用STRING_AGG生成条件，减少游标开销：

  DECLARE @Conditions NVARCHAR(MAX);
  SELECT @Conditions = STRING_AGG(
      N'AND dbo.LevenshteinDistance(t.CombinedText, ''' + Term + ''') <=1',
      N' '
  ) FROM @SearchTerms;
  SET @sql = N'SELECT ... FROM TBLSTSABIT t WHERE 1=1 ' + @Conditions;
  

• 分页优化：确保STOK_KODU为主键或唯一索引，提升ORDER BY分页性能。

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