基于NOT IN子句关联多表删除主表记录的SQL能否简化优化
优化方案
你现在这套写法冗余度很高,还带着NOT IN的经典坑,完全可以大幅简化。
现有写法的问题
- 重复写了5次结构几乎完全一样的关联子查询,代码太啰嗦,后续维护的时候如果要加、减子表,或者改判断规则,得逐处修改,很容易漏改、错改
NOT IN写法有致命隐患:只要任意一个子查询返回的mainid包含NULL值,整个NOT IN的判断结果会直接失效,最终可能把主表数据全误删,生产风险极高
简化后的实现
核心业务逻辑完全不变:只要任意一张子表里,对应mainid分组下的最大sttime大于@mydate,这条主表记录就需要保留,删除所有不在这个保留集合里的主表记录。
我们可以把所有子表的聚合结果合并成一个公共判断集合,用NOT EXISTS替代原来的NOT IN,既消掉冗余也规避NULL坑:
DELETE a FROM myMainTbl a WHERE NOT EXISTS ( SELECT 1 FROM ( -- 把所有子表的mainid和对应最大sttime合并到一起 SELECT mainid, MAX(sttime) AS max_sttime FROM mySubTbl_1 GROUP BY mainid UNION ALL SELECT mainid, MAX(sttime) AS max_sttime FROM mySubTbl_2 GROUP BY mainid UNION ALL SELECT mainid, MAX(sttime) AS max_sttime FROM mySubTbl_3 GROUP BY mainid UNION ALL SELECT mainid, MAX(sttime) AS max_sttime FROM mySubTbl_4 GROUP BY mainid UNION ALL SELECT mainid, MAX(sttime) AS max_sttime FROM mySubTbl_5 GROUP BY mainid ) AS valid_main WHERE valid_main.mainid = a.id AND valid_main.max_sttime > @mydate )
如果你的数据库支持公用表表达式(CTE,比如MySQL 8.0+、SQL Server、PostgreSQL等),还可以把公共判断集抽离出来,代码可读性会更好:
WITH valid_main AS ( SELECT mainid, MAX(sttime) AS max_sttime FROM mySubTbl_1 GROUP BY mainid UNION ALL SELECT mainid, MAX(sttime) AS max_sttime FROM mySubTbl_2 GROUP BY mainid UNION ALL SELECT mainid, MAX(sttime) AS max_sttime FROM mySubTbl_3 GROUP BY mainid UNION ALL SELECT mainid, MAX(sttime) AS max_sttime FROM mySubTbl_4 GROUP BY mainid UNION ALL SELECT mainid, MAX(sttime) AS max_sttime FROM mySubTbl_5 GROUP BY mainid ) DELETE a FROM myMainTbl a WHERE NOT EXISTS ( SELECT 1 FROM valid_main v WHERE v.mainid = a.id AND v.max_sttime > @mydate )
实操注意事项
- 给所有子表的
(mainid, sttime)字段建联合索引,能大幅加快聚合计算的速度 - 正式执行删除前,一定要先把DELETE语句换成对应的
SELECT *查询,确认待删除的记录范围完全符合业务预期,避免误删数据
内容的提问来源于stack exchange,提问作者Murulimadhav
相关产品推荐
相关产品推荐

