如何简化多NOT IN条件的DELETE语句并优化性能？

可以将原语句简化为单条NOT IN语句，利用UNION ALL合并sel1和sel2的数据集即可——因为已知两者无交集，UNION ALL无需执行去重操作，性能比UNION更优。

简化后的语句 #

DELETE FROM data_table 
WHERE
  (location, param_id, ref_time, fcst_time) NOT IN (
    SELECT location, param_id, ref_time, fcst_time FROM sel1
    UNION ALL
    SELECT location, param_id, ref_time, fcst_time FROM sel2
  );

逻辑说明 #

原语句中NOT IN sel1 AND NOT IN sel2的逻辑，等价于NOT IN (sel1 ∪ sel2)（不在两个数据集的并集中）。由于sel1和sel2无交集，用UNION ALL直接合并就能得到完整的并集，避免了UNION的去重开销。

针对超大数据量的性能优化建议 #

考虑到data_table数据量超过1亿行，且待删除行极少，建议优先采用以下方案提升性能和稳定性：

1. 用NOT EXISTS替代NOT IN
   NOT EXISTS在处理大表时通常更稳定，且不会因子查询返回NULL导致逻辑异常：

   DELETE FROM data_table dt
   WHERE NOT EXISTS (
       SELECT 1 FROM sel1 s1
       WHERE dt.location = s1.location
         AND dt.param_id = s1.param_id
         AND dt.ref_time = s1.ref_time
         AND dt.fcst_time = s1.fcst_time
   )
   AND NOT EXISTS (
       SELECT 1 FROM sel2 s2
       WHERE dt.location = s2.location
         AND dt.param_id = s2.param_id
         AND dt.ref_time = s2.ref_time
         AND dt.fcst_time = s2.fcst_time
   );
   

2. 添加复合索引
   确保data_table在(location, param_id, ref_time, fcst_time)字段上创建复合索引，这能大幅加速行匹配过程，避免全表扫描的巨大开销。

3. 分步骤删除（避免长时锁表）
   先筛选出待删除行的主键存入临时表，再批量删除，减少对主表的锁持有时间：

   -- 假设data_table有主键id，先选出待删除记录的主键
   CREATE TEMP TABLE to_delete AS
   SELECT dt.id
   FROM data_table dt
   WHERE NOT EXISTS (
       SELECT 1 FROM sel1 s1
       WHERE dt.location = s1.location
         AND dt.param_id = s1.param_id
         AND dt.ref_time = s1.ref_time
         AND dt.fcst_time = s1.fcst_time
   )
   AND NOT EXISTS (
       SELECT 1 FROM sel2 s2
       WHERE dt.location = s2.location
         AND dt.param_id = s2.param_id
         AND dt.ref_time = s2.ref_time
         AND dt.fcst_time = s2.fcst_time
   );
   
   -- 批量删除主表数据
   DELETE FROM data_table dt
   USING to_delete td
   WHERE dt.id = td.id;
   

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