mysql in 原理_mysql中in和exists性能对比及原理

in和exists都是范围查询，但他们有着很大区别，注重性能的程序员会慎重选择，那么他们又什么区别

1.查询原理的区别

①exists

exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当 exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录，反之如果exists里的条 件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为 false。

所以exists逐行扫描然外查询，外查询的每一行都会执行一下exists子句判断括号里面是true还是false，是true就返回这一行的记录。所以exist必须要联合查询。比如select * fro tmp1 where exists (select 1 from tmp2 where tmp2.id = tmp1.id)。这样查询时扫描tmp1每一行，扫描每一行时执行一下exists 括号的子查询，判断当前扫描行的tmp1.id在tmp2中是否存在，存在就返回这一行。

②in

很多人说in和or一样。其实不是的。在没有索引的情况下，in和or都是扫描全表。但in不是一个条件一个条件比的。而是外查询和子查询做hash连接，所以速度会高于or。

2.语法比较对象不一样

exists的用法是select * from a where exists()。而in的用法是select * from where id in()。

可以看出exists是按行比较，而in是按字段。exists执行时只看括号中是true或false，有记录就是true，所以对值没有限定，而in的值必须是字段序列。

in查询的子条件返回结果必须只有一个字段

例如

select * from user where userId in (select id from B);

而不能是

select * from user where userId in (select id, age from B);

而exists就没有这个限制

3.性能区别

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和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列的索引。

4.not in 和not exists

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