MySQL中in、exists、not in、not exists使用

1 in与exists原理解析

作为一个后端程序猿，有必要对经常使用到的in与exists原理进行了解。
假设：A表中的id与B表中的uid关联

（1） in的原理

in语句：

select * from A where id in(select uid from B)

in原理：
in中语句select uid from B只执行一次，然后将B表中查询出的uid结果存储到内存中(以hash集合的方式)，注意：内存中。然后判断A表中的id是否在该集合中存在，如果存在就将A表中的记录加入到结果集里面去。直到A表中所有记录遍历完为止。

in伪代码：

List resultSet =Lists.newArrayList();

RecordArray[] A=(select * from A);

IdArray[] B=(select id from B);
// 遍历A表的所有记录
for(int i=0;i<A.length;i++) {
	// 判断A总某个记录的id是否存在与B集合中
	for(int j=0;j<B.length;j++) {
	
	if(A[i].id==B[j].id) {
	
			resultSet.add(A[i]);
			
			break;

	}

}

}

return resultSet;

in讨论：
由于要遍历B集合，当B表数据较大时，可能会将B中uid全部遍历完
如：A表有10000条记录，B表有1000000条记录，那么最多有可能遍历100001000000次，效率有点差。
如：A表有10000条记录，B表有100条记录，那么最多有可能遍历10000100次，遍历次数大大减少，效率将会有较大的提升。

（2）exists原理

exists语句：
下面的语句与上面的in语句实现同样的效果

select * from A where exists(select id from B where id=A.id) 

exists原理：
exists()是根据A中某个记录的当前id对B进行select id from B where id=A.id查询操作，但是该查询的结果集内容并不重要，重要的是判断其结果集是否为空，空则返回false；非空则返回true，然后保存当前符合要求的A记录。

exists伪代码

List resultSet={};

RecordArray[] A=(select * from A);

for(int i=0;i<A.length;i++) {

	if(exists(A[i].id) {  //执行select id from B where B.id=A.id是否有记录返回
	
		resultSet.add(A[i]);
	
	}

}

return resultSet;

exists讨论
与in一样，都会对最外层的A表进行遍历，他们的主要区别是A表遍历时候的内部操作。
exists()时候内部不做遍历操作，只需要再执行一次查询就行。
如：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。

如：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。

注意：
in()是在内存里遍历比较，而exists()中的语句查询数据库，我们都知道查询数据库所消耗的性能更高，而内存比较很快。

2 in与exists适用场景

很多人多说：
外表大，内表小用in
外表小，内表大用exists

但是：这个多大范围又没有很明确的界限。在mysql里面查询，如果外表比内部小，但是差距不是很大，还是建议用in，因为in毕竟是在内存里面操作。

如：A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，还不如使用in()遍历10000*100次，因为in()是在内存里遍历比较，而exists()需要查询数据库，而连接数据库本来就是一个io操作，对cpu资源，数据库资源都是一种损耗，而内存比较很快。

3 not in 和not exists

not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的bug。

请看下面的例子：
A表：

c1	c2
1	2
1	3

B表

c1	c2
1	2
1	null

select * from A where c2 not in( select c2 from B);                       -->执行结果：无

select * from A where not exists(select c2 from B where A.c2=B.c2);    -->执行结果：
返回 A中的第一行记录

not in出现了不期望的结果集，存在逻辑错误。如果not in中含有空值，则查询将不返回任何记录。如果子查询字段有非空限制，这时可以使用not in。或者在子查询后面做一个空判断

 select * from A where c2 not in( select c2 from B where  c2 is not null);  

下面是官方的解释：

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