如何一次性删除百万级数据库中不符合指定Username&ApplicationID组合的行？

我有一张超过300万行的大型数据库表，存储着用户信息、详细资料及其使用的应用（同一用户对应每个应用会生成一条记录，共涉及70个独立应用）。客户筛选后决定仅保留约十分之一的用户，要求删除其余所有数据。我的SQL基础较弱，仅能完成简单的条件删除操作，无法编写基于列表筛选的删除脚本。

麻烦的是，客户返回的保留列表未包含UserId（若包含UserId，操作会简单很多）。我需要一个能批量删除所有不符合指定Username和ApplicationID组合的脚本，且变更委员会要求必须一次性执行完成，不允许分多次按应用执行。该表包含31列，示例数据如下：

示例数据表1 #

补充完整示例数据表 #

步骤1：创建临时表存储保留组合 #

先把客户提供的保留列表导入临时表，提升后续删除操作的效率。假设临时表名为KeepList，字段类型需与原表保持一致：

CREATE TABLE KeepList (
    Username VARCHAR(50), -- 替换为原表Username的实际类型
    ApplicationID INT,    -- 替换为原表ApplicationID的实际类型
    PRIMARY KEY (Username, ApplicationID) -- 加联合主键加速匹配
);

将客户的保留数据插入临时表（示例如下，替换为实际保留列表）：

INSERT INTO KeepList (Username, ApplicationID)
VALUES 
    ('JB1', 50122),
    ('SH2', 201),
    ('TT1', 2699);

步骤2：执行一次性删除操作 #

使用NOT EXISTS子句匹配需保留的组合，删除不符合的行。假设原表名为UserAppData：

DELETE FROM UserAppData
WHERE NOT EXISTS (
    SELECT 1
    FROM KeepList
    WHERE KeepList.Username = UserAppData.Username
      AND KeepList.ApplicationID = UserAppData.ApplicationID
);

大表性能优化建议 #

针对300万行的大表，即使要求一次性执行，也可通过以下方式减少资源消耗：

• 为原表的Username和ApplicationID创建联合索引（若尚未存在）：

  CREATE INDEX IX_UserAppData_Username_AppID ON UserAppData (Username, ApplicationID);
  

• 若数据库支持（如SQL Server），开启SET NOCOUNT ON减少日志输出：

  SET NOCOUNT ON;
  DELETE FROM UserAppData
  WHERE NOT EXISTS (...);
  

• 执行前务必备份原表，避免误删：

  SELECT * INTO UserAppData_Backup FROM UserAppData;
  

验证删除结果 #

删除后可通过以下语句验证正确性：

-- 查看剩余行数，应与保留组合数一致（假设原表每个组合唯一）
SELECT COUNT(*) FROM UserAppData;

-- 检查是否存在未被保留的行（返回空则删除正确）
SELECT * FROM UserAppData
WHERE NOT EXISTS (
    SELECT 1 FROM KeepList
    WHERE KeepList.Username = UserAppData.Username
      AND KeepList.ApplicationID = UserAppData.ApplicationID
);

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