In、or、exists、not in、not exists

目录

in和or：没有索引或主键时，随数据量增多，or性能急剧下降

in和exists：主表小、子表大用exists；主表大、子表小用in

not in和not exists：无论哪个表大，not exists都比not in快

---

in和or：没有索引或主键时，随数据量增多，or性能急剧下降

前提条件：所在的列是否有索引或者主键。

in和or所在列有索引或主键：or和in没啥差别，执行计划和执行时间都几乎一样；

in和or所在列没有索引或主键：性能差别大了。在没有索引的情况下，随着in或者or后面的数据量越多，in的效率不会有太大的下降，但是or会随着记录增多而性能急剧下降。

	所在列有索引或主键	所在列没有索引或主键
in	执行计划和执行时间几乎一样	随数据量增多，效率不会太大下降
or	执行计划和执行时间几乎一样	随数据量增多，性能急剧下降

 

exists和in：主表小、子表大用exists；主表大、子表小用in

exists是对外表作loop循环，每次loop循环再对内表进行查询，而in是把外表和内表作hash连接，一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in。

/* 主表小子表大用exists */

-->效率高，用到了BigTable上cc列的索引
select *
  from SmallTable
 where exists (select cc from BigTable where cc = SmallTable.cc);

-->效率低，用到了SmallTable上cc列的索引
-->select * from SmallTable where cc in (select cc from BigTable);

----------------------------------------------------------------------------- 

/* 主表大子表小用in */

-->效率高，用到了BigTable上cc列的索引
select * from BigTable where cc in (select cc from SmallTable);
　　

-->效率低，用到了SmallTable上cc列的索引
-->select * from BigTable where exists (select cc from SmallTable where cc = BigTable.cc);

 

not exists和not in：无论哪个表大，not exists都比not in快

not in：内外表均全表扫，不会走索引；

not exists：子查询依然能用到表上的索引；

所以无论哪个表大，not exists效率更高。