`
sangei
  • 浏览: 336283 次
  • 性别: Icon_minigender_1
  • 来自: 西安
社区版块
存档分类
最新评论

(转)in和exists

阅读更多

分析一下exists真的就比in的效率高吗?

 

     我们先讨论IN和EXISTS。
     select * from t1 where x in ( select y from t2 )
     事实上可以理解为:
     select *
       from t1, ( select distinct y from t2 ) t2
      where t1.x = t2.y;
     ——如果你有一定的SQL优化经验,从这句很自然的可以想到t2绝对不能是个大表,因为需要对t2进行全表的“唯一排序”,如果t2很大这个排序的性能是不可忍受的。但是t1可以很大,为什么呢?最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想,如果t1.x和t2.y都有索引,我们知道索引是种有序的结构,因此t1和t2之间最佳的方案是走merge join。另外,如果t2.y上有索引,对t2的排序性能也有很大提高。
     select * from t1 where exists ( select null from t2 where y = x )
     可以理解为:
     for x in ( select * from t1 )
     loop
        if ( exists ( select null from t2 where y = x.x )
        then
           OUTPUT THE RECORD!
        end if
     end loop
     ——这个更容易理解,t1永远是个表扫描!因此t1绝对不能是个大表,而t2可以很大,因为y=x.x可以走t2.y的索引。
     综合以上对IN/EXISTS的讨论,我们可以得出一个基本通用的结论:IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

我们要根据实际的情况做相应的优化,不能绝对的说谁的效率高谁的效率低,所有的事都是相对的。

 

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

为空

 

2.NOT IN 与NOT EXISTS:     
NOT EXISTS的执行流程
select .....
  from rollup R
where not exists ( select 'Found' from title T
                            where R.source_id = T.Title_ID);
可以理解为:
for x in ( select * from rollup )
      loop
          if ( not exists ( that query ) ) then
                OUTPUT
          end if;
      end;

注意:NOT EXISTS 与 NOT IN 不能完全互相替换,看具体的需求。如果选择的列可以为空,则不能被替换。

例如下面语句,看他们的区别:
select x,y from t;
x              y
------        ------
1              3
3        1
1        2
1        1
3        1
5
select * from t where  x not in (select y from t t2  )
no rows
     
select * from t where  not exists (select null from t t2
                                                  where t2.y=t.x )
x      y
------  ------
5      NULL
所以要具体需求来决定

对于not in 和 not exists的性能区别:
  not in 只有当子查询中,select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大,子查询中的表小但是记录多,则应当使用not in,并使用anti hash join.
  如果主查询表中记录少,子查询表中记录多,并有索引,可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null
NOT IN 在基于成本的应用中较好

比如:
select .....
from rollup R
where not exists ( select 'Found' from title T
                          where R.source_id = T.Title_ID);

改成(佳)

select ......
from title T, rollup R
where R.source_id = T.Title_id(+)
    and T.Title_id is null;
                               
或者(佳)
sql> select /*+ HASH_AJ */ ...
        from rollup R
        where ource_id NOT IN ( select ource_id
                                              from title T
                                              where ource_id IS NOT NULL )

注意:上面只是从理论上提出了一些建议,最好的原则是大家在上面的基础上,能够使用执行计划来分析,得出最佳的语句的写法
希望大家提出异议 

 

 

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

          也就是说,in和exists需要具体情况具体分析,not in和not exists就不用分析了,尽量用not exists就好了。

分享到:
评论

相关推荐

    in和exists的区别

    在Oracle数据库中,"IN"和"EXISTS"都是用于查询某个集合的元素是否存在于另一个集合中的关键字。然而,它们在处理数据时的效率和适用场景有所不同,这主要取决于涉及的数据量以及表之间的关联。 首先,让我们来看看...

    sql in,exists,not in,not exists区别

    IN、EXISTS、NOT IN、NOT EXISTS 是 SQL 中四种常用的条件判断运算符,它们之间的区别主要体现在使用场景、执行效率和语法结构上。 IN IN 是一种条件判断运算符,用于判断某个值是否存在于一个列表中。其基本语法...

    in和exists性能解析

    在数据库查询语言中,`IN` 和 `EXISTS` 子句是两种常见的用于关联两个表的方法,它们各自有其独特的性能特点和适用场景。本文将深入解析Oracle中`IN`与`EXISTS`的性能差异,以及如何根据具体需求选择最合适的查询...

    in和exists的区别与执行效率问题解析

    标题和描述均聚焦于SQL查询语句中"IN"与"EXISTS"的区别及执行效率问题,这是一个在数据库操作中非常关键的话题,尤其对于优化查询性能有着不可忽视的作用。下面,我们将深入探讨这两种语句的不同之处及其对执行效率...

    oracle中exists_和in的效率问题详解

    Oracle 中 EXISTS 和 IN 的效率问题详解 EXISTS 和 IN 都是 Oracle 中的集合操作符,但它们在使用和执行效率上有所不同。本文将深入探讨 EXISTS 和 IN 的使用场景、执行机制和效率问题。 EXISTS 的使用场景和机制 ...

    SQL里的EXISTS与IN

    在SQL查询语言中,`EXISTS` 和 `IN` 子句都是非常常用且重要的操作符,它们被广泛应用于复杂的查询条件中,特别是当需要检查某个子查询是否返回结果时。根据给定的信息,本文将详细解析`EXISTS`与`IN`的区别以及如何...

    “exists”和“in”的效率问题

    ### "Exists"与"In"的效率问题详解 #### 引言 在数据库查询语言SQL中,“Exists”与“In”是两种常用的子查询方法,它们在实际应用中各有优势与局限。本文将深入探讨这两种方法的工作原理、应用场景以及性能差异,...

    PostgreSQL IN vs EXISTS vs ANYALL vs JOIN性能分析

    PostgreSQL作为一种强大的开源关系数据库系统,它支持多种SQL操作,其中包括IN、EXISTS、ANY/ALL和JOIN等操作符。这些操作符在不同的业务场景下有着不同的表现和性能影响。在实际的数据库操作中,选择合适的操作符是...

    sql case when exists not exists in not in

    在SQL查询中,`CASE WHEN`、`EXISTS`、`NOT EXISTS`以及`IN`和`NOT IN`是常用的操作符,它们用于处理复杂的条件判断和数据筛选。这些概念对于理解和编写高效的SQL语句至关重要,尤其是在数据分析和数据库管理中。 `...

    SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt

    SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt欢迎下载!

    经典SQL查询总结关于Exists,not Exists.in ,not in效率的说明。

    - 在某些情况下,可以考虑使用 `EXISTS` 或 `NOT EXISTS` 来替代 `IN` 和 `NOT IN`,以提高查询效率。 #### 三、左连接、右连接与全连接 除了上述几种查询方式之外,SQL 还提供了不同的连接类型来处理不同情况下...

    in与exists之争(11g).sql

    in与exists之争(11g).sql

    sql exists和not exists用法

    这是因为`IN`和`NOT IN`需要将主查询中的每一行都与子查询结果进行比较,而`EXISTS`和`NOT EXISTS`则只需判断子查询是否返回至少一行数据即可,无需获取所有数据行。 **性能对比:** - **IN 和 NOT IN**:需要将主...

    简述Oracle中in和exists的不同

    如果两张表大小差不多,那么exists和in的效率差不多。 例如: 一张大表为A,一张小表B 一、第一种情况 select * from A where mm in (select mm from B) 效率高,这里用到的是大表A上的索引 select * from B ...

    EXISTS_和_NOT_EXISTS

    - 效率:在某些情况下,`EXISTS` 和 `NOT EXISTS` 可能会提供相似的性能,但通常 `IN` 和 `NOT IN` 比较操作符在处理大量数据时更有效。然而,当涉及到子查询时,`EXISTS` 和 `NOT EXISTS` 可能更合适,因为它们只...

    sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句

    在查询中,我们可以使用EXISTS和NOT EXISTS来代替IN和NOT IN。例如,我们要查询Sendorder表中的冗余数据(没有和reg_person或worksite相连的数据): ```sql select Sendorder.id, Sendorder.reads, Sendorder....

    Oracle In和exists not in和not exists的比较分析

    在Oracle数据库中,`IN`、`EXISTS`、`NOT IN` 和 `NOT EXISTS` 是四个常用的子查询操作符,它们在SQL查询语句中扮演着不同的角色,且各有其性能特点。以下是对这些操作符的详细分析和比较。 1. `IN` 操作符: `IN` ...

    MySQL中的in,exists,not in,not exists查询过程对比及结论

    MySQL优化之in,exists,not in,not exists的区别in与existsin查询过程结论:exists查询过程:结论:not in与not existsnot in查询过程:结论:not exists查询过程:结论: 首先我们使用两个用户表作为实例 insert ...

Global site tag (gtag.js) - Google Analytics