基于另一表筛选删除数据的MariaDB技术求助

针对你的MariaDB 10.3环境，以及百万级数据量的场景，推荐以下两种高效且可靠的方法，替代容易出问题的NOT IN方式：

方案1：LEFT JOIN 批量删除 #

利用多表JOIN直接定位并删除不匹配的行，这是MariaDB处理批量删除的高效方式：

DELETE se
FROM se
LEFT JOIN npcs ON se.npcid = npcs.id
WHERE npcs.id IS NULL;

原理：LEFT JOIN会保留se表的所有行，其中无法匹配到npcs表的行，对应的npcs.id会是NULL，通过这个条件即可精准删除不需要的行。

方案2：NOT EXISTS 子查询 #

通过子查询判断行是否需要保留，逻辑更直观，且能避免NOT IN的潜在问题：

DELETE FROM se
WHERE NOT EXISTS (
    SELECT 1 FROM npcs WHERE npcs.id = se.npcid
);

原理：逐行检查se表的npcid是否在npcs表中存在，不存在则执行删除操作。

关键注意事项 #

• 先验证再删除：执行以下SELECT语句确认要删除的行是否符合预期，避免误删：

  -- 对应方案1的验证语句
  SELECT COUNT(*) FROM se LEFT JOIN npcs ON se.npcid = npcs.id WHERE npcs.id IS NULL;
  -- 对应方案2的验证语句
  SELECT COUNT(*) FROM se WHERE NOT EXISTS (SELECT 1 FROM npcs WHERE npcs.id = se.npcid);
  

• 添加索引提升效率：百万级数据下，索引能大幅加速查询和删除操作，确保以下字段有索引：

  CREATE INDEX idx_se_npcid ON se(npcid);
  CREATE INDEX idx_npcs_id ON npcs(id);
  

• 事务保障安全：如果使用InnoDB引擎，建议通过事务操作，确认无误后再提交：

  START TRANSACTION;
  -- 执行删除语句（二选一）
  DELETE se FROM se LEFT JOIN npcs ON se.npcid = npcs.id WHERE npcs.id IS NULL;
  -- 查看删除的行数，确认是否符合预期
  SELECT ROW_COUNT();
  -- 没问题就提交，出错则回滚
  COMMIT;
  -- ROLLBACK;
  

• 为什么NOT IN不可靠：当npcs表的id列存在NULL值时，NOT IN会返回空结果，导致没有行被删除；同时当IN列表包含5万个ID时，会极大降低语句解析效率，甚至出现数据遗漏的情况，因此不推荐使用。

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