ITPub博客

首页 > 数据治理 > 数据治理 > mysql 关于exists 和in分析

mysql 关于exists 和in分析

原创 数据治理 作者:贺子_DBA时代 时间:2018-07-15 21:36:36 0 删除 编辑

今天来学习下, 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,然后就是网络中说的外表的和内表大的说法也不准确,应该是外表结果集和内表结果集合的大小,至于结果集前面已经解释过了。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29654823/viewspace-2157873/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-05-12

  • 博文量
    219
  • 访问量
    1515990