SQL JOIN关联查询中使用LIKE模糊匹配的高效优化方法

原查询慢的根本原因：关联条件使用LIKE CONCAT('%', SOPOrderReturn.DocumentNo, '%')属于前后带通配符的模糊匹配，无法命中任何索引，会触发两张表全表扫描后逐行做匹配运算，数据量较大时耗时会呈指数级增长。

以下是不需要修改ERP原表结构的可落地优化方案，按改造成本从低到高、效果从优到劣排序：

方案1：缩小参与匹配的数据集范围（改造成本最低，优先尝试） #

在关联前先对两张表加业务过滤条件，减少参与匹配的行数，不需要修改原有匹配逻辑：

• 对NLPostedNominalTran表按需加时间范围、业务类型等过滤条件，只查你需要的区间数据，避免全表扫描所有历史交易
• 对SOPOrderReturn表过滤掉无效单据，比如作废单据、历史归档单据等，只保留可能匹配的有效单据号

示例代码：

SELECT t.TransactionDate
      ,t.GoodsValueInDocumentCurrency
      ,t.Reference
      ,t.Narrative
      ,s.DocumentNo
      ,t.OpLock
      ,t.DateTimeCreated
  FROM (
    -- 先过滤交易表，只保留需要查询的范围数据
    SELECT * FROM NLPostedNominalTran 
    WHERE TransactionDate >= '2024-01-01' AND TransactionDate < '2024-07-01'
  ) t
  LEFT JOIN (
    -- 先过滤单据表，只保留有效单据
    SELECT DocumentNo FROM SOPOrderReturn WHERE IsVoid = 0 AND CreateDate >= '2024-01-01'
  ) s ON t.Narrative LIKE CONCAT('%', s.DocumentNo, '%')

方案2：模糊匹配改等值匹配（效率提升最大，适用于单据号有固定规则的场景） #

如果你的业务中SOPOrderReturn.DocumentNo有固定格式，比如固定长度、固定前缀、纯数字等规则，可以先用正则/字符串截取函数从Narrative字段中提取出符合单据号格式的部分，再用等值匹配关联，性能比前后通配的LIKE高数十倍。

示例代码（以单据号为8位纯数字为例，可根据实际格式调整正则规则）：

SELECT t.TransactionDate
      ,t.GoodsValueInDocumentCurrency
      ,t.Reference
      ,t.Narrative
      ,s.DocumentNo
      ,t.OpLock
      ,t.DateTimeCreated
  FROM NLPostedNominalTran t
  LEFT JOIN SOPOrderReturn s 
    ON REGEXP_SUBSTR(t.Narrative, '[0-9]{8}') = s.DocumentNo -- 提取Narrative中符合8位数字的片段做等值匹配

注：不同数据库的正则提取函数不同，SQL Server用SUBSTRING+PATINDEX、Oracle用REGEXP_SUBSTR、MySQL用REGEXP_SUBSTR，可根据你使用的数据库调整语法。

方案3：临时表预处理加索引（兼容性最高，无前置要求） #

如果以上两个方案都不适用，可以用临时表预处理数据，给临时表加索引提升匹配效率，不需要修改ERP原表结构：

-- 第一步：导出需要的交易数据到临时表，按需加过滤条件
SELECT TransactionDate, GoodsValueInDocumentCurrency, Reference, Narrative, OpLock, DateTimeCreated
INTO #TempNLTran
FROM NLPostedNominalTran
WHERE TransactionDate BETWEEN '2024-01-01' AND '2024-06-30'

-- 第二步：导出有效单据号到临时表，加索引
SELECT DocumentNo
INTO #TempSOPDoc
FROM SOPOrderReturn
WHERE IsValid = 1
CREATE NONCLUSTERED INDEX IX_TempSOPDoc_DocumentNo ON #TempSOPDoc (DocumentNo)

-- 第三步：关联查询
SELECT t.*, s.DocumentNo
FROM #TempNLTran t
LEFT JOIN #TempSOPDoc s ON t.Narrative LIKE CONCAT('%', s.DocumentNo, '%')

-- 查询完成后删除临时表
DROP TABLE #TempNLTran
DROP TABLE #TempSOPDoc

注：如果数据库不支持临时表加索引，也可以用CTE做预处理，相比直接关联原表也能减少磁盘IO开销。

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