T-SQL LIKE查询性能优化求助：大表关联更新耗时过长问题

首先明确你的场景和原查询代码：

场景：tst数据集包含85000条记录，#tmprec临时表包含255000条记录。目标是为#tmprec中符合条件的记录，从tst匹配以sConsigneePostcodeFirst开头的邮政编码，当前查询执行耗时达7分钟，已尝试添加索引、使用LIKE查询、全文检索索引等手段，需要进一步性能优化方案。

原查询代码：

WITH tst AS (
    SELECT 
        nz.sPostcode op,
        zp.nZoneCountryID,
        zp.sPostcode,
        zp.nZoneID,
        zp.nInjectionID
    FROM dbo.tblZonePostcode zp
    INNER JOIN tblNewZone nz ON nz.nPostcodeID = zp.NewZoneId
    WHERE 
        CHARINDEX('%', zp.sPostcode) = 1 
        AND zp.sPostcode IS NOT NULL 
        AND isnumeric(zp.sPostcodeRange) = 0
)
SELECT * INTO #tmptst FROM tst;
CREATE INDEX idx_sPostcode12 ON #tmptst (sPostcode);
CREATE INDEX idx_x1 ON #tmptst (nZoneCountryID);
CREATE INDEX idx_x2 ON #tmptst (nInjectionID);
CREATE INDEX idx_x3 ON #tmptst (nZoneID);

UPDATE #tmprec 
SET calculatedPostCodeZone = (
    SELECT TOP 1 zp.op 
    FROM #tmptst zp 
    WHERE 
        zp.nZoneCountryID = CalculatedCountryId 
        AND zp.nInjectionID = CalculatedinjectionPointId 
        AND zp.nZoneID = CalculatedZoneId 
        AND CHARINDEX(zp.sPostcode, sConsigneePostcodeFirst) = 1
)
WHERE 
    CalculatedRateChartGroupID > 0 
    AND calculatedRatechartValues IS NULL 
    AND calculatedPostCodeZone IS NULL 
    AND CalculationStatus IS NULL 
    AND 0 < (SELECT count(1) FROM #tmptst);

我来给你梳理几个针对性的优化方向，都是针对你这种大规模数据集前缀匹配场景的实用方案：

1. 用复合覆盖索引替代单字段索引，彻底优化子查询性能 #

你现在给#tmptst建了四个单字段索引，但UPDATE的子查询是多条件过滤+前缀匹配，单字段索引的组合效率远不如覆盖型复合索引。数据库在处理多条件查询时，复合索引能直接定位到目标数据，还能避免回表查询：

-- 先删掉原来的单字段索引
DROP INDEX IF EXISTS idx_sPostcode12 ON #tmptst;
DROP INDEX IF EXISTS idx_x1 ON #tmptst;
DROP INDEX IF EXISTS idx_x2 ON #tmptst;
DROP INDEX IF EXISTS idx_x3 ON #tmptst;

-- 创建复合覆盖索引：过滤条件字段在前，要返回的op字段包含进来
CREATE NONCLUSTERED INDEX idx_tmptst_covering 
ON #tmptst (nZoneCountryID, nInjectionID, nZoneID, sPostcode)
INCLUDE (op);

这个索引能让子查询直接从索引里拿到所有需要的数据，不用再去查临时表的物理数据，IO开销会大幅降低。

2. 把标量子查询改成JOIN式UPDATE，避免逐行循环 #

原UPDATE里的标量子查询是对#tmprec的每一条符合条件的记录都执行一次子查询，相当于25万次循环查询，这是性能慢的核心原因之一。改成JOIN的集合运算方式，让数据库一次性完成批量匹配：

-- 替换原来的UPDATE语句
UPDATE r
SET r.calculatedPostCodeZone = zp.op
FROM #tmprec r
INNER JOIN (
    -- 提前给每个匹配组取TOP 1的op，避免一条#tmprec记录匹配多个结果
    SELECT 
        zp.nZoneCountryID,
        zp.nInjectionID,
        zp.nZoneID,
        zp.sPostcode,
        zp.op,
        -- 按邮政编码长度倒序，确保最长的前缀优先匹配（符合业务逻辑）
        ROW_NUMBER() OVER (PARTITION BY zp.nZoneCountryID, zp.nInjectionID, zp.nZoneID, zp.sPostcode ORDER BY LEN(zp.sPostcode) DESC) AS rn
    FROM #tmptst zp
) zp ON 
    r.CalculatedCountryId = zp.nZoneCountryID
    AND r.CalculatedinjectionPointId = zp.nInjectionID
    AND r.CalculatedZoneId = zp.nZoneID
    AND CHARINDEX(zp.sPostcode, r.sConsigneePostcodeFirst) = 1
WHERE 
    r.CalculatedRateChartGroupID > 0 
    AND r.calculatedRatechartValues IS NULL 
    AND r.calculatedPostCodeZone IS NULL 
    AND r.CalculationStatus IS NULL
    AND zp.rn = 1; -- 只取每个匹配组的第一条（最长前缀）

集合运算的效率远高于逐行查询，尤其是在大数据量下，这个改动能把执行时间压缩到原来的几分之一。

3. 预处理前缀格式，让匹配逻辑更高效 #

原tst里的sPostcode带%开头，CHARINDEX(zp.sPostcode, sConsigneePostcodeFirst) = 1其实等价于前缀匹配，咱们可以提前把%去掉，用LEFT函数做精准的前缀比对，让数据库更好地利用索引：

-- 重构tst的CTE，去掉sPostcode开头的%，存成纯前缀
WITH tst AS (
    SELECT 
        nz.sPostcode op,
        zp.nZoneCountryID,
        STUFF(zp.sPostcode, 1, 1, '') AS sPostcodePrefix, -- 去掉开头的%
        zp.nZoneID,
        zp.nInjectionID
    FROM dbo.tblZonePostcode zp
    INNER JOIN tblNewZone nz ON nz.nPostcodeID = zp.NewZoneId
    WHERE 
        zp.sPostcode LIKE '%[%]' -- 更精准判断开头是%
        AND zp.sPostcode IS NOT NULL 
        AND ISNUMERIC(zp.sPostcodeRange) = 0
)
SELECT * INTO #tmptst FROM tst;

-- 更新复合索引，用预处理后的sPostcodePrefix
CREATE NONCLUSTERED INDEX idx_tmptst_covering 
ON #tmptst (nZoneCountryID, nInjectionID, nZoneID, sPostcodePrefix)
INCLUDE (op);

-- 对应的JOIN条件改成LEFT前缀匹配
AND LEFT(r.sConsigneePostcodeFirst, LEN(zp.sPostcodePrefix)) = zp.sPostcodePrefix

这种方式比CHARINDEX或LIKE更直接，数据库能更高效地利用索引进行匹配，减少不必要的计算。

4. 优化临时表的统计信息，让查询计划更准确 #

临时表默认的统计信息可能不够完善，导致数据库选错查询计划。咱们可以在创建#tmptst后手动更新统计信息：

SELECT * INTO #tmptst FROM tst;
-- 全量扫描更新统计信息，让数据库生成最优的查询计划
UPDATE STATISTICS #tmptst WITH FULLSCAN;
-- 再创建复合索引
CREATE NONCLUSTERED INDEX idx_tmptst_covering ON #tmptst (...) INCLUDE (...);

如果tst数据集不是每次都有大幅变化，还可以考虑把tst的结果持久化到一个物理表，加上合适的索引，避免每次都重新生成临时表。

5. 分批处理大表记录，减少资源占用 #

如果JOIN还是慢，可以把#tmprec的符合条件的记录分成若干批次处理，避免一次性处理25万条记录占用过多内存和锁资源：

DECLARE @BatchSize INT = 10000; -- 每次处理1万条，可根据服务器性能调整
DECLARE @MaxBatchId INT;
DECLARE @CurrentBatchId INT = 0;

-- 给#tmprec加一个临时的自增字段，用来分批
ALTER TABLE #tmprec ADD BatchId INT IDENTITY(1,1);

SELECT @MaxBatchId = MAX(BatchId) FROM #tmprec WHERE 
    CalculatedRateChartGroupID > 0 
    AND calculatedRatechartValues IS NULL 
    AND calculatedPostCodeZone IS NULL 
    AND CalculationStatus IS NULL;

WHILE @CurrentBatchId < @MaxBatchId
BEGIN
    UPDATE r
    SET r.calculatedPostCodeZone = zp.op
    FROM #tmprec r
    INNER JOIN (
        SELECT 
            zp.nZoneCountryID,
            zp.nInjectionID,
            zp.nZoneID,
            zp.sPostcodePrefix,
            zp.op,
            ROW_NUMBER() OVER (PARTITION BY zp.nZoneCountryID, zp.nInjectionID, zp.nZoneID, zp.sPostcodePrefix ORDER BY LEN(zp.sPostcodePrefix) DESC) AS rn
        FROM #tmptst zp
    ) zp ON 
        r.CalculatedCountryId = zp.nZoneCountryID
        AND r.CalculatedinjectionPointId = zp.nInjectionID
        AND r.CalculatedZoneId = zp.nZoneID
        AND LEFT(r.sConsigneePostcodeFirst, LEN(zp.sPostcodePrefix)) = zp.sPostcodePrefix
    WHERE 
        r.BatchId > @CurrentBatchId
        AND r.BatchId <= @CurrentBatchId + @BatchSize
        AND r.CalculatedRateChartGroupID > 0 
        AND r.calculatedRatechartValues IS NULL 
        AND r.calculatedPostCodeZone IS NULL 
        AND r.CalculationStatus IS NULL
        AND zp.rn = 1;

    SET @CurrentBatchId += @BatchSize;
    -- 每次批次后提交事务，释放锁资源
    COMMIT TRANSACTION;
    BEGIN TRANSACTION;
END

-- 最后删掉临时加的BatchId字段
ALTER TABLE #tmprec DROP COLUMN BatchId;

分批处理能减少锁的持有时间，避免长时间阻塞，同时降低单次查询的资源压力，适合服务器配置一般的场景。

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