MySQL优化之in、exists、join
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相同
版权声明：本文为yuluodisuihe原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。