exits, in, left join(可以完成同一个数据查询与删除)性能比较
https://www.cnblogs.com/roychenyi/p/10724854.html
exits in left join性能比较 ,笔者使用的是MYSQL数据库,这三个关键字方法在其他的关系数据库里也是大同小异,如果各种有兴趣,自行比较。
我这里有一个249_account 表,总数为8538507 249001_account_temp表 总数为 8242734
其中有部分数据,249_account里有,249_account_temp表里没有,要求找出249_account多余数据
使用上面三种方法进行查找,三种查到的数据条数都是 294451条
not exits
select count(*) from cunjk.249_account a where not EXISTS (select card_id from bak.249001_account_temp b where a.card_id = b.card_id ); 32.401s
left join
select count(*) from cunjk.249_account a left JOIN bak.249001_account_temp b on a.card_id = b.card_id where b.card_id is null ; 22.888s
in
select count(*) from cunjk.249_account where card_id not in (select card_id from bak.249001_account_temp ); 40.306s
in 用时 40.306s
not exits 32.401s
left join 22.888s
对于数据量的情况
left join 最省时效率更高,in 最效率最低
如果通过上述三种方式,删除多余的数据:
left join
delete a from cunjk.249_account a left OUTER JOIN bak.249001_account_temp b on a.card_id = b.card_id where b.card_id is null ;
not exits
delete from cunjk.249_account a where not EXISTS (select * from bak.249001_account_temp b where a.card_id = b.card_id ) ;
in
delete from cunjk.249_account where card_id not in (select card_id from bak.249001_account_temp ) ;
总结:虽然in 方法写起来很简单,但是性能不够好,left join 外连接,需要关联条件,看起来有一些复杂,但是性能最优。
请适合自己的方法