MariaDB中基于A、B列匹配且C列差异的表A数据删除需求

现有两张数据库表：

• 表A：包含100万行数据，列名有A、B、C、D、E，其中A、B、C三列已建立索引
• 表B：包含5000行数据，列名有A、B、C、D、E、F，按A、B、C列分组后每组行数不超过100行

需要删除表A中满足以下条件的行：和表B的A、B列值相同，但该(A,B)组合对应的C列值在表B里找不到。

示例数据 #

表A（标记Delete的是需要删除的行）

表B

方法1：用EXISTS子查询（推荐，利用现有索引提速） #

因为表A的A、B、C已经建了索引，这个写法能快速定位目标行，避免全表扫描：

DELETE FROM 表A
WHERE EXISTS (
    SELECT 1 FROM 表B 
    WHERE 表B.A = 表A.A AND 表B.B = 表A.B
)
AND NOT EXISTS (
    SELECT 1 FROM 表B 
    WHERE 表B.A = 表A.A AND 表B.B = 表A.B AND 表B.C = 表A.C
);

逻辑很直接：

• 第一个EXISTS先筛出表A里和表B有相同(A,B)组合的行
• 第二个NOT EXISTS把这些行里C值在表B对应(A,B)中存在的排除掉，剩下的就是要删的

方法2：先提取合法组合再关联删除 #

先把表B里的合法(A,B)和(A,B,C)组合提出来，再通过关联找到要删的行：

DELETE a
FROM 表A a
JOIN (
    SELECT DISTINCT A, B FROM 表B
) b_ab ON a.A = b_ab.A AND a.B = b_ab.B
LEFT JOIN (
    SELECT DISTINCT A, B, C FROM 表B
) b_abc ON a.A = b_abc.A AND a.B = b_abc.B AND a.C = b_abc.C
WHERE b_abc.C IS NULL;

解释：

• 第一个子查询拿表B所有存在的(A,B)对
• 第二个子查询拿表B所有合法的(A,B,C)三元组
• 左关联后，b_abc.C IS NULL的就是表A中(A,B)在表B里但C不在对应组合的行

性能优化提示 #

1. 确认表A的(A,B,C)索引正常生效，这是快速查询的关键
2. 可以给表B临时建(A,B)和(A,B,C)索引，毕竟表B只有5000行，建索引成本极低，能大幅加快子查询速度
3. 如果表A数据量太大，怕一次性删除锁表太久，可以分批删（以MySQL为例）：

-- 每次删1000行，直到符合条件的行删完
WHILE EXISTS (
    SELECT 1 FROM 表A
    WHERE EXISTS (SELECT 1 FROM 表B WHERE 表B.A=表A.A AND 表B.B=表A.B)
    AND NOT EXISTS (SELECT 1 FROM 表B WHERE 表B.A=表A.A AND 表B.B=表A.B AND 表B.C=表A.C)
) DO
    DELETE FROM 表A
    WHERE EXISTS (SELECT 1 FROM 表B WHERE 表B.A=表A.A AND 表B.B=表A.B)
    AND NOT EXISTS (SELECT 1 FROM 表B WHERE 表B.A=表A.A AND 表B.B=表A.B AND 表B.C=表A.C)
    LIMIT 1000;
END WHILE;

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