exists的比较分析,Sql语句中IN和exists的区别及应用
分类:web前端

把这两个很普遍性的网友比较关心的问题总结回答一下。 in和exist的区别 从sql编程角度来说,in直观,exists不直观多一个select, in可以用于各种子查询,而exists好像只用于关联子查询 从性能上来看 exists是用loop的方式,循环的次数影响大,外表要记录数少,内表就无所谓了 in用的是hash join,所以内表如果小,整个查询的范围都会很小,如果内表很大,外表如果也很大就很慢了,这时候exists才真正的会快过in的方式。 not in和not exists的区别 not in内外表都进行全表扫描,没有用到索引; not extsts 的子查询能用到表上的索引。 所以推荐用not exists代替not in 不过如果是exists和in就要具体看情况了 有时间用具体的实例和执行计划来说明。

 表展示

    首先,查询中涉及到的两个表,一个user和一个order表,具体表的内容如下:

    user表:

    图片 1

    order表:

    图片 2

 

  in

    确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候,首先查询子查询的表,然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选。所以相对内表比较小的时候,in的速度较快。

    具体sql语句如下:

图片 3

 1 SELECT
 2     *
 3 FROM
 4     `user`
 5 WHERE
 6     `user`.id IN (
 7         SELECT
 8             `order`.user_id
 9         FROM
10             `order`
11     )

图片 4

    这条语句很简单,通过子查询查到的user_id 的数据,去匹配user表中的id然后得到结果。该语句执行结果如下:

    图片 5

    它的执行流程是什么样子的呢?让我们一起来看一下。

    首先,在数据库内部,查询子查询,执行如下代码:

        SELECT
            `order`.user_id
        FROM
            `order`

    执行完毕后,得到结果如下:

    图片 6

    此时,将查询到的结果和原有的user表做一个笛卡尔积,结果如下:

    图片 7

    此时,再根据我们的user.id IN order.user_id的条件,将结果进行筛选(既比较id列和user_id 列的值是否相等,将不相等的删除)。最后,得到两条符合条件的数据。
    图片 8

  exists

    指定一个子查询,检测行的存在。遍历循环外表,然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

    具体sql语句如下:

图片 9

 1 SELECT
 2     `user`.*
 3 FROM
 4     `user`
 5 WHERE
 6     EXISTS (
 7         SELECT
 8             `order`.user_id
 9         FROM
10             `order`
11         WHERE
12             `user`.id = `order`.user_id
13     )

图片 10

    这条sql语句的执行结果和上面的in的执行结果是一样的。

    图片 11

    但是,不一样的是它们的执行流程完全不一样:

    使用exists关键字进行查询的时候,首先,我们先查询的不是子查询的内容,而是查我们的主查询的表,也就是说,我们先执行的sql语句是:

     SELECT `user`.* FROM `user` 

    得到的结果如下:

    图片 12

    然后,根据表的每一条记录,执行以下语句,依次去判断where后面的条件是否成立:

图片 13

EXISTS (
        SELECT
            `order`.user_id
        FROM
            `order`
        WHERE
            `user`.id = `order`.user_id
    )

图片 14

    如果成立则返回true不成立则返回false。如果返回的是true的话,则该行结果保留,如果返回的是false的话,则删除该行,最后将得到的结果返回。

  区别及应用场景

    in 和 exists的区别: 如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了 ,另外IN时不对NULL进行处理。

    in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

  not in 和not exists

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

 

 

 

 

 


 

 

in 和exists

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 列的索引。

 

 

not in 和not exists

如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;

而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists 都比not in 要快。

 

本文由10bet手机官网发布于web前端,转载请注明出处:exists的比较分析,Sql语句中IN和exists的区别及应用

上一篇:PhpMyAdmin管理数据库和WordPress搭建博客,windows下9款一键快速搭建PHP本地运行环境的好工具 下一篇:没有了
猜你喜欢
热门排行
精彩图文