如何优化迭代删除的TSQL脚本？处理7万Token与400万行数据

原脚本的核心问题 #

• 循环执行7万次DELETE操作，每次触发全表扫描（LIKE '%token%'无法利用常规索引），CPU与IO消耗陡增
• 无意义的动态SQL使用，额外增加执行解析开销与注入风险
• 单Token单循环的处理方式，完全浪费了数据库的批量处理能力

优化方向一：一次性批量删除（无锁表风险时优先） #

直接通过临时表关联完成全量匹配删除，彻底消除循环开销。适合匹配行数较少、不会引发长时间锁表的场景：

IF OBJECT_ID('tempdb..#tokens') IS NOT NULL
    DROP TABLE #tokens

CREATE TABLE #tokens(token nvarchar(50))
-- 替换为从Excel导入7万Token的逻辑，比如OPENROWSET/BULK INSERT
INSERT INTO #tokens(token) 
SELECT t.token FROM (VALUES ('aaa'), ('bbb'), ('ccc')) AS t(token)

-- 一次性删除所有匹配行
DELETE mt
FROM MyTable mt
WHERE EXISTS (
    SELECT 1 
    FROM #tokens t
    WHERE mt.token LIKE '%' + t.token + '%'
)

DROP TABLE #tokens

优化方向二：分批批量删除（避免锁表/日志暴涨） #

若直接全量删除会导致锁表或事务日志溢出，采用分批删除策略，每次处理固定行数，平衡性能与资源占用：

IF OBJECT_ID('tempdb..#tokens') IS NOT NULL
    DROP TABLE #tokens

CREATE TABLE #tokens(token nvarchar(50))
INSERT INTO #tokens(token) 
SELECT t.token FROM (VALUES ('aaa'), ('bbb'), ('ccc')) AS t(token)

-- 分批大小可根据服务器性能调整，示例为1000行/批
DECLARE @BatchSize INT = 1000
DECLARE @DeletedRows INT = 1

WHILE @DeletedRows > 0
BEGIN
    DELETE TOP (@BatchSize) mt
    FROM MyTable mt
    WHERE EXISTS (
        SELECT 1 
        FROM #tokens t
        WHERE mt.token LIKE '%' + t.token + '%'
    )
    
    SET @DeletedRows = @@ROWCOUNT
    -- 可选：添加微延迟，降低CPU瞬时压力
    WAITFOR DELAY '00:00:00.100'
END

DROP TABLE #tokens

优化方向三：索引优化（解决LIKE '%xxx%'性能瓶颈） #

LIKE '%token%'无法利用常规前缀索引，可通过以下方式突破：

• 反向列索引：若你的匹配模式是Token在末尾（如x.aaa），新增反向计算列并建索引，将模糊匹配转为前缀匹配：

  -- 仅需执行一次的索引准备
  ALTER TABLE MyTable ADD ReverseToken AS REVERSE(token)
  CREATE NONCLUSTERED INDEX IX_MyTable_ReverseToken ON MyTable(ReverseToken)
  
  -- 分批删除时修改匹配逻辑
  DELETE TOP (@BatchSize) mt
  FROM MyTable mt
  WHERE EXISTS (
      SELECT 1 
      FROM #tokens t
      WHERE mt.ReverseToken LIKE REVERSE(t.token) + '%'
  )
  

• 全文索引：若需任意位置匹配Token，创建全文索引替代LIKE，大幅提升匹配效率：

  -- 仅需执行一次的全文索引准备
  CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT
  CREATE FULLTEXT INDEX ON MyTable(token) KEY INDEX PK_MyTable -- 替换为你的表主键索引名
  
  -- 删除时使用全文检索
  DELETE TOP (@BatchSize) mt
  FROM MyTable mt
  WHERE EXISTS (
      SELECT 1 
      FROM #tokens t
      WHERE CONTAINS(mt.token, t.token)
  )
  

额外细节优化 #

• 移除动态SQL：原脚本的动态SQL完全冗余，直接使用常规DELETE语句即可减少解析开销
• 临时表索引：为#tokens的token列建非聚集索引，提升关联匹配效率：

  CREATE NONCLUSTERED INDEX IX_tokens_token ON #tokens(token)
  

• 事务控制：若需保证数据一致性，可单批事务处理，但不要将所有分批放入一个大事务，避免日志暴涨

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