6
回答
求sql大神帮忙优化一下这条sql
滴滴云服务器,限时包月0.9元,为开发者而生>>>   
delete from mlsbackup.MBLTPCUS c where 
c.MBL_ID_NO in (SELECT a.MBL_ID_NO FROM mlsbackup.MBLTPCUS a,mlstst.MBLTPCUS b where a.MBL_ID_NO = b.MBL_ID_NO and a.MBL_NO = a.MBL_NO and a.MBL_PROV = b.MBL_PROV) 
and c.MBL_NO in (SELECT a.MBL_NO FROM mlsbackup.MBLTPCUS a,mlstst.MBLTPCUS b where a.MBL_ID_NO = b.MBL_ID_NO and a.MBL_NO = a.MBL_NO and a.MBL_PROV = b.MBL_PROV)
and c.MBL_PROV in (SELECT a.MBL_PROV FROM mlsbackup.MBLTPCUS a,mlstst.MBLTPCUS b where a.MBL_ID_NO = b.MBL_ID_NO and a.MBL_NO = a.MBL_NO and a.MBL_PROV = b.MBL_PROV);
举报
carson王
发帖于2年前 6回/470阅
共有6个答案 最后回答: 2年前

找到答案了

https://yq.aliyun.com/ask/30781/?spm=5176.100241.asklist.7.r5ro6I

阿里云上线的云栖社区里面的大牛完美的解决了我的问题,认识了阿里大牛。好棒,里面的技术类的干货真多,问答,优质的博文,让我流连忘返,学习到很多实用的知识,还有一群热爱It的小伙伴们,云栖社区,赞一个!

with t1 as (
SELECT a.MBL_ID_NO
FROM mlsbackup.MBLTPCUS a, mlstst.MBLTPCUS b
WHERE a.MBL_ID_NO = b.MBL_ID_NO AND a.MBL_NO = a.MBL_NO AND a.MBL_PROV = b.MBL_PROV
),
t2 as (
      SELECT a.MBL_NO
      FROM mlsbackup.MBLTPCUS a, mlstst.MBLTPCUS b
      WHERE a.MBL_ID_NO = b.MBL_ID_NO AND a.MBL_NO = a.MBL_NO AND a.MBL_PROV = b.MBL_PROV
),
t3 as (
      SELECT a.MBL_PROV
      FROM mlsbackup.MBLTPCUS a, mlstst.MBLTPCUS b
      WHERE a.MBL_ID_NO = b.MBL_ID_NO AND a.MBL_NO = a.MBL_NO AND a.MBL_PROV = b.MBL_PROV
)
DELETE FROM mlsbackup.MBLTPCUS c
WHERE c.MBL_ID_NO IN (select * from t1)
 AND c.MBL_NO IN (select * from t2)
AND c.MBL_PROV IN (select * from t3);



--- 共有 1 条评论 ---
ahdkk不要觉得这样写的很罗嗦,数据量非常大的时候就会看到性能的提升了 2年前 回复

delete from mlsbackup.MBLTPCUS c where 
 exists(select null from  
 mlsbackup.MBLTPCUS a,mlstst.MBLTPCUS b where 
     a.MBL_ID_NO = b.MBL_ID_NO and a.MBL_NO = a.MBL_NO and a.MBL_PROV = b.MBL_PROV 
 and c.MBL_ID_NO=a.MBL_ID_NO
 and c.MBL_NO =a.MBL_NO
 and c.MBL_PROV= a.MBL_PROV)




如果删除的数据量很大且是一次性的执行,建议分批删除

delete from mlsbackup.MBLTPCUS c 
where exists (
select null 
from mlstst.MBLTPCUS b 
where c.MBL_ID_NO=b.MBL_ID_NO
  and c.MBL_NO =b.MBL_NO
  and c.MBL_PROV= b.MBL_PROV);

delete from mlsbackup.MBLTPCUS c 
where (c.MBL_ID_NO,c.MBL_NO,c.MBL_PROV) in (
select b.MBL_ID_NO,b.MBL_NO,b.MBL_PROV 
from mlstst.MBLTPCUS b
where c.MBL_ID_NO=b.MBL_ID_NO
  and c.MBL_NO =b.MBL_NO
  and c.MBL_PROV= b.MBL_PROV);
顶部