如何高效删除Table1中与Table2的account_number和offer_code均匹配的数据行？

嘿，针对你遇到的大表关联删除效率问题，我给你几个靠谱的高效解决方案，分不同数据库场景来说：

先说说你之前踩的坑 #

你第一次写的多表DELETE语法不对（不同数据库的多表删除语法有差异），而第二种用CONCAT的方法之所以慢到离谱，是因为字符串拼接操作会让数据库完全无法利用索引，只能对两张超大表做全表扫描——6550万行的全表扫描+字符串拼接，性能能好才怪。

MySQL/MariaDB 专属高效方案 #

MySQL支持多表DELETE的正确语法，你需要明确指定要删除的表（这里是table1的别名A）：

DELETE A
FROM table1 A
INNER JOIN table2 B 
    ON A.account_number = B.account_number 
    AND A.offer_code = B.offer_code;

核心优化必须做：

• 给两张表的(account_number, offer_code)创建复合索引，这是提升关联速度的关键：

  CREATE INDEX idx_table1_acc_offer ON table1(account_number, offer_code);
  CREATE INDEX idx_table2_acc_offer ON table2(account_number, offer_code);
  

• 如果数据量实在太大，一次性删除可能锁表太久影响业务，建议分批删除：

  WHILE EXISTS (SELECT 1 FROM table1 A INNER JOIN table2 B ON A.account_number = B.account_number AND A.offer_code = B.offer_code) DO
      DELETE A
      FROM table1 A
      INNER JOIN table2 B 
          ON A.account_number = B.account_number 
          AND A.offer_code = B.offer_code
      LIMIT 10000; -- 每次删1万行，可根据服务器性能调整数值
  END WHILE;
  

SQL Server 专属高效方案 #

SQL Server的多表删除语法和MySQL类似，写法如下：

DELETE A
FROM table1 A
INNER JOIN table2 B 
    ON A.account_number = B.account_number 
    AND A.offer_code = B.offer_code;

同样要先加复合索引：

CREATE NONCLUSTERED INDEX idx_table1_acc_offer ON table1(account_number, offer_code);
CREATE NONCLUSTERED INDEX idx_table2_acc_offer ON table2(account_number, offer_code);

超大表分批删除的写法：

DECLARE @RowCount INT = 1;
WHILE @RowCount > 0
BEGIN
    DELETE TOP (10000) A -- 每次删1万行，可按需调整
    FROM table1 A
    INNER JOIN table2 B 
        ON A.account_number = B.account_number 
        AND A.offer_code = B.offer_code;
    SET @RowCount = @@ROWCOUNT;
END

PostgreSQL 专属高效方案 #

PostgreSQL用USING子句来实现关联删除，语法更简洁：

DELETE FROM table1 A
USING table2 B
WHERE A.account_number = B.account_number 
  AND A.offer_code = B.offer_code;

优化步骤同样是创建复合索引：

CREATE INDEX idx_table1_acc_offer ON table1(account_number, offer_code);
CREATE INDEX idx_table2_acc_offer ON table2(account_number, offer_code);

分批删除的PL/pgSQL写法：

DO $$
DECLARE
    row_count INT;
BEGIN
    LOOP
        DELETE FROM table1 A
        USING table2 B
        WHERE A.account_number = B.account_number 
          AND A.offer_code = B.offer_code
        LIMIT 10000;
        GET DIAGNOSTICS row_count = ROW_COUNT;
        EXIT WHEN row_count = 0;
    END LOOP;
END $$;

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