in和exists、not in和not exists性能对比

In是把外表和内表做Hash 连接，而 exists 是对外表作loop 循环 ，每次loop循环再对内表进行查询。

当查询两个表的大小相当时，用In 和 exists差别不大。

如果两个表中一个表较小，一个表较大，那么子查询表大的用exists,子查询表小的用In，效率会高的。

也就是说  IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况，这样效率会高的

例如 ：表a(小表)，表b(大表)

1.select * from  a  where aid in (select aid from b) --->效率低：全程扫描b表，用到 a  表上的aid的索引，因为a表小，b表大

    上面in的语句可以理解为：

    select * 

      from a, ( select distinct aid from b) b1

     where a.aid = b1.aid.

 

   select * from a where exists (select aid from  b  where b.aid = a.aid) --->效率高： 全程扫描a表，用到 b 表上的aid的索引。因为b表大。

 上面的exists的语句可以理解为：

  for aid in ( select * from a)

    loop

       if ( exists ( select aid from b where b.aid= a.aid )

       then 

          OUTPUT THE RECORD!

       end if

    end loop

2. select * from b where aid in (select aid from a)----效率高：全程扫描a 表，用到b表上的aid 索引

    select * from b where exists (select aid from a were a.aid= b.aid) --->效率低：全程扫描b 表：用到a 表上的aid索引。

Not in 和Not Exists 的 效率

如果查询语句使用了Not In,那么内外表全部进行扫描，没有乃至索引

Not Exist用到子表中的索引进行查询，所以无论两个表中哪个表大，Not exists 都要比Not in 要快。