MySQL优化之in、exists、join
一、inner join 、 in 、exists
1 explain 2 select a.id 3 from application as a 4 where exists( 5 select 1 6 from dispatch_app_history as d 7 where d.bomber_id = 165 and d.application_id = a.id 8 );
1 explain 2 select a.id 3 from application as a 4 where a.id in ( 5 select d.application_id 6 from dispatch_app_history as d 7 where d.bomber_id = 165 8 );
1 explain 2 select a.id 3 from application as a 4 inner join dispatch_app_history as d on d.application_id = a.id 5 where d.bomber_id = 165;
分析:子查询需要application_id来关联外部表application,因为需要application_id字段,所以MySQL认为无法先执行这个子查询,而对application表进行全表查询。
结论:子查询、join查询具体性能怎么样需要根据实际情况决定
二、not in、not exists
1、执行速度
1 explain 2 select * 3 from dispatch_app as d 4 where d.application_id not in( 5 select dh.application_id 6 from dispatch_app_history as dh 7 );
1 explain 2 select * 3 from dispatch_app as d 4 where not exists ( 5 select 1 6 from dispatch_app_history as dh 7 where d.application_id = dh.application_id 8 )
结论:不考虑其他情况,通常情况下not exists的执行效率要高于not in
2、条件中含有null
1 select * 2 from bomber as b 3 where not exists( 4 select 1 5 from dispatch_app_history as d 6 where d.partner_id = b.partner_id 7 ) 8 group by b.partner_id;
1 select * 2 from bomber as b 3 where b.partner_id not in( 4 select distinct d.partner_id 5 from dispatch_app_history as d 6 );
结论:对于not in,条件中有null值的时候会直接停止执行返回null结果。对于not exists,条件中有null时会清除null后执行。in、exists的执行与not existst相同