跨两张表执行字段部分匹配时查询过慢的优化求助

当前查询耗时的核心原因是：b.[Piece Barcode] LIKE '%' + a.Barcode + '%'属于前导通配符的模糊匹配，SQL Server无法利用常规B树索引，会触发两张表的全量匹配计算（29万×18万的量级会产生近53亿次匹配尝试），直接导致CPU长时间满载。

以下是针对性优化方案：

1. 改用全文索引实现高效包含匹配 #

• 给merged表的[Piece Barcode]字段创建全文索引，全文索引专门优化了字符串包含查询的性能，比通配符LIKE效率提升显著。
• 修改后的查询语句：

SELECT a.Id
INTO __MatchingScans
FROM AllScans AS a
INNER JOIN merged AS b 
ON CONTAINS(b.[Piece Barcode], a.Barcode)

• 注意事项：如果条码包含数字或特殊字符，需设置全文索引的停用词列表为空，避免核心条码片段被误判为停用词。

2. 提取核心条码做精确匹配 #

如果AllScans的条码是「前缀+核心码+后缀」结构，且核心码有明确特征（比如示例中的纯数字），可以先提取核心码再做精确匹配：

• 先创建临时表存储提取后的核心码：

SELECT 
    a.Id,
    -- 提取条码中的纯数字段，可根据实际条码规则调整正则逻辑
    SUBSTRING(a.Barcode, PATINDEX('%[0-9]%', a.Barcode), PATINDEX('%[^0-9]%', SUBSTRING(a.Barcode, PATINDEX('%[0-9]%', a.Barcode), LEN(a.Barcode)) + ' ') - 1) AS CoreBarcode
INTO #TempAllScans
FROM AllScans AS a
WHERE PATINDEX('%[0-9]%', a.Barcode) > 0

• 再用精确匹配关联merged表（确保merged表的[Piece Barcode]字段有非聚集索引）：

SELECT t.Id
INTO __MatchingScans
FROM #TempAllScans AS t
INNER JOIN merged AS b 
ON b.[Piece Barcode] = t.CoreBarcode

• 优势：精确匹配能直接利用索引，查询速度会大幅提升。

3. 用CLR自定义函数优化字符串匹配 #

如果条码规则复杂，无法用SQL内置函数提取核心码，可以编写CLR函数实现高效字符串包含判断——CLR的字符串处理效率远高于T-SQL的LIKE操作：

• 编写C#的CLR函数，实现判断b.[Piece Barcode]是否包含a.Barcode的逻辑；
• 在SQL Server中注册该CLR函数（需开启CLR集成支持，且函数设置为SAFE权限）；
• 修改查询语句调用该CLR函数替代LIKE匹配。

4. 分批处理降低资源负载 #

如果以上方案暂时无法实施，可采用分批处理的方式，避免一次性处理全量数据导致CPU持续满载：

DECLARE @StartId INT = 0, @EndId INT = 10000
WHILE @StartId < (SELECT MAX(Id) FROM AllScans)
BEGIN
    INSERT INTO __MatchingScans
    SELECT a.Id
    FROM AllScans AS a
    INNER JOIN merged AS b 
    ON b.[Piece Barcode] LIKE '%' + a.Barcode + '%'
    WHERE a.Id BETWEEN @StartId AND @EndId

    SET @StartId = @EndId + 1
    SET @EndId = @EndId + 10000
END

• 优势：拆分查询任务，降低单次查询的CPU占用，避免长时间资源耗尽。

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