mysql的exists与inner join 和 not exists与 left join 性能差别惊人
insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);
?
如果t1的数据量很大时,性能会非常慢。经过实践,用以下方法能提高很多。
insert into t1(a1)select b1 from t2left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id where t1.id is null;
?
?
select * from t1 where exists(select 1 from t2 where t1.id=t2.r_id);
?替换为:
select t1.* from t1 inner join (select distinct r_id from t2) t2 on t1.id= t2.r_id
?
这是实践的得出的结果。不知否有其他更好的方法,或则这个只是特例而已。?
?