mysql中exists 和in的区别是什么

今天就跟大家聊聊有关mysql中exists 和in的区别是什么，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

　　MySQL中in和exists的性能优劣以及各自的检索数据的过程 ，以下面的语句为例子：

　　select * from user a where name='liuwenhe' and exists (select stuid from department b where depname='yunwei' and a.stuid =b.stuid );

　　select * from user where name='liuwenhe' and stuid in (select stuid from department where depname='yunwei');

　　MySQL exists和in检索数据的过程：

　　1.首先说下exists检索过程,

　　注意其中A代表(user a where name='liuwenhe')的结果集，B代表(department b where depname='yunwei' )的结果集：

　　exists对外表A用loop逐条查询，每次查询都会去验证exists的条件语句(也就是exists后面括号里面的语句)，当 exists里的条件语句能够返回记录行时(只要能返回结果即可，不管你查询的是什么内容!!!)，条件就为真，就会返回当前loop到的A的这条记录，反之如果exists里的条件语句不能返回记录行，条件为假，则当前loop到的A的这条记录被丢弃，注意：exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为 false;

　　对于exists的检索过程可以用下面的脚本概括：

　　for ($i = 0; $i < count(A); $i++) {

　　$a = get_record(A, $i); #从A表逐条获取记录

　　if (B.id = $a[id]) #如果子条件成立，即返回true

　　$result[] = $a; 　　}

　　return $result;

　　例如：

　　select * from user where exists (select stuid from department where depname='yunwei' );

　　对user表的记录逐条取出，由于exists条件中的select stuid from department where depname='yunwei' 永远能返回记录行，那么user表的所有记录都将被加入结果集，所以与 select * from user;是一样的

　　例如：

　　select * from user where exists (select stuid from department where depname='yunwei+' );

　　not exists与exists相反，也就是当exists条件有结果集返回时，loop到的记录将被丢弃，否则将loop到的记录加入结果集

　　总的来说，如果user表结果集有n条记录，那么exists查询就是将这n条记录逐条取出，然后判断n遍exists条件 。

　　2.关于in子查询的检索过程：

　　MySQL先将子查询结果存入临时表T(可能在内存中，也可能磁盘中)，确保子查询只执行一次，该表不记录重复数据且采用哈希索引遍历数据，然后通过T表的数据去遍历外表，通过关联关系得到外表的需要的数据，in查询相当于多个or条件的叠加，这个比较好理解，比如下面的查询

　　select * from user where userId in (1, 2, 3);

　　等效于

　　select * from user where userId = 1 or userId = 2 or userId = 3;

　　not in与in相反，如下

　　select * from user where userId not in (1, 2, 3);

　　等效于

　　select * from user where userId != 1 and userId != 2 and userId != 3;

　　总的来说，in查询就是先将子查询条件的记录全都查出来，假设结果集为B，共有m条记录，

　　然后在将子查询条件的结果集分解成m个，再进行m次主查询，值得一提的是，in查询的子条件返回结果必须只有一个字段，例如

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

　　而不能是

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

　　而exists就没有这个限制

　　exists和in的性能

　　select * from user a where name='liuwenhe' and exists (select stuid from department b where depname='yunwei' and a.stuid =b.stuid );

　　select * from user where name='liuwenhe' and stuid in (select stuid from department where depname='yunwei');

　　1)根据前面介绍的检索数据的过程，可以知道，针对上面的两条sql中exists这种方式，是需要遍历user表name='liuwenhe'的所有数据行N，并且判断exists条件N次;并且如果department表的stuid 有索引，exists子查询可以使用连接关系(也就是stuid)上的索引;所以exists方式适合 user表的结果集小，子查询的结果集大的情况; 子查询可以使用关联关系列上的索引，所以效率高，故内表大的适合使用exists;

　　2)not exists类似于exists的遍历方式，也是loop外表，然后判断exists条件

　　3)in是把外表user结果集和内表department结果集做hash连接(应该说类似hash join，因为MySQL不支持hash join的方式)，先查询内表department结果集，再把内表结果集与外表结果集匹配，对外表可以使用关系索引(也就是stuid列上的索引)，而内表结果集多大都需要查询，也就是说department where depname='yunwei'的结果集D多大，都得遍历全部的D，不可避免，故外表大的使用in，可加快效率。 主查询可以使用关联关系列上的索引，所以效率高，故外表结果集合大的适合使用in;

　　3)如果用not in ，和in一样，内表结果集需要全部扫描，由于not in ，所以外表的结果集也需要权标扫描，都无法使用关系列上的索引(这种!=的范围查询无法使用任何索引)，效率低，可考虑使用not exists，也可使用A left join B on A.id=B.id where B.id is null 进行优化。

　　总结：

　　exists先对外表结果集loop循环再对内表结果集进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。如果查询的两个表大小相当，那么用in和exists差别不大。 如果两个表中一个较小，一个是大表，则子查询表结果集大的用exists，如果外表结果集大的则适合使用in，然后就是网络中说的外表的和内表大的说法也不准确，应该是外表结果集和内表结果集合的大小，至于结果集前面已经解释过了。

看完上述内容，你们对mysql中exists 和in的区别是什么有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。