mysql exists和in 效率问题

1.sql转化

in 会转化为join

explain select d.id from device d where  d.company_id in(select c.id from company c );
show warnings;

执行如图

exists 不会转化

explain select d.id from device d where exists (select 1 from company c where d.company_id=c.id);
show warnings;

 

2.exists

对于 exists 来说，当小表 t1 做外表时，t1 全表扫描，rows 近 100W；当 大表 t2 做外表时， t2 全表扫描，rows 近 200W 。这也是为什么 t2 做外表时，执行效率非常低的原因。

因为对于 exists 来说，外表总会执行全表扫描的，当然表数据越少越好了

 最终结论： 外层大表内层小表，用in。外层小表内层大表，in和exists效率差不多（甚至 in 比 exists 还快，而并不是网上说的 exists 比 in 效率高）。

3.not in 和not exists

小表做外表的情况下。对于主键来说， not exists 比 not in 快。对于普通索引来说， not in 和 not exists 差不了多少，甚至 not in 会稍快。

大表做外表的情况下，对于主键来说， not in 比 not exists 快。对于普通索引来说， not in 和 not exists 差不了多少，甚至 not in 会稍快。

4.实测

in比exists快

select d.* from device d where exists (select 1 from company c where d.company_id=c.id)
> OK
> 时间: 0.247s


select d.* from device d where  d.company_id in	(select c.id from company c )
> OK
> 时间: 0.097s


select c.* from company c where exists (SELECT 1 from device d where d.company_id=c.id)
> OK
> 时间: 0.075s


select c.*  from company c where c.id IN (SELECT d.company_id from device d )
> OK
> 时间: 0.051s

not in 比exists快 

select c.* from company c where not exists (SELECT 1 from device d where d.company_id=c.id)
> OK
> 时间: 0.099s


select c.* from company c where c.id NOT IN (SELECT d.company_id from device d )
> OK
> 时间: 0.097s


select d.* from device d where NOT exists (select 1 from company c where d.company_id=c.id)
> OK
> 时间: 0.169s


select d.* from device d where  d.company_id not in	(select c.id from company c )
> OK
> 时间: 0.052s