MySQL EXISTS /NOT EXISTS IN和NOTIN 效率比较

首先说下他们的用法

in和not in 大家肯定都不陌生，用法如下

SELECT * from `user` as a WHERE a.ID in(SELECT b.ID from student as b)

SELECT * from `user` as a WHERE a.ID not in(SELECT b.ID from student as b

用来判断某一个字段是否存再子查询的结果集中

而EXISTS /NOT EXISTS 也能实现这个功能.

下面看一下使用EXISTS 来实现同样的效果

SELECT *from `user` as a WHERE EXISTS(SELECT b.ID from student as b WHERE a.ID=b.ID)
SELECT *from `user` as a WHERE NOT EXISTS(SELECT b.ID from student as b WHERE a.ID=b.ID)

EXISTS 用来判断查询到的结果是否为空，它并不返回任何值，NOT EXISTS 也是一样，

那么我们下面来比较一下这几个的执行效率，要想比较它的执行效率，

1. in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询
2. not exists:做NL，对子查询先查，有个虚表，有确定值，所以就算子查询有NULL最终也有值返回
3. not in:做hash，对子查询表建立内存数组，用外表匹配，那子查询要是有NULL那外表没的匹配最终无值返回。

IN是先把内表的结果集查出来，然后再逐渐查询外表判断条件是否相等。拿下面的这个例子来讲解

SELECT * from `user` as a  WHERE a.ID in(SELECT b.ID from student as b)

他会先把student 表的所有ID查出来存入内存，然后便利这些ID，查看User表中的ID是否和前面的ID相等，

而EXISTS它会直接去遍历user 表然后去判断EXISTS的值是false还是True

所以可以看出

1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;

T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。

• 1
• 2

2) select * from T1 where T1.a in (select T2.a from T2) ;

T1数据量非常大而T2数据量小时，T1>>T2 时，2) 的查询效率高。

• 1
• 2

简而言之，一般式：外表大，用IN；内表大，用EXISTS。

 这个结果看起来是正确的，但是当外表并不比内表大多少的时候，还是推荐使用 Exists，因为Exists 能更好的使用索引。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in： 

例如：表A（小表），表B（大表）

 

1：

select * from A where cc in (select cc from B)  效率低，用到了A表上cc列的索引；

 

select * from A where exists(select cc from B where cc=A.cc)  效率高，用到了B表上cc列的索引。 

相反的

 

2：

select * from B where cc in (select cc from A)  效率高，用到了B表上cc列的索引；

 

select * from B where exists(select cc from A where cc=B.cc)  效率低，用到了A表上cc列的索引。

 

 

not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。 所以无论那个表大，用not exists都比not in要快。 

Not in 和Not Exists 的 效率

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

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