`

EXISTS/NOT EXISTS一定比IN/NOT IN的效率高吗？

SQLAccess数据结构 
阅读更多

经常别人说EXISTS比IN快！NOT EXISTS比NOT 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适合于外表小而内表大的情况。
    如果你对上述说法表示怀疑，请看以下测试：
********************************************************************************
SQL> create table big as select * from all_objects;

表已创建。

SQL> insert /*+ append */ into big select * from big;

已创建26872行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into big select * from big;

已创建53744行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into big select * from big;

已创建107488行。

SQL> commit;

提交完成。

SQL> create index big_idx on big(object_id);

索引已创建。

SQL> create table small as select * from all_objects where rownum < 100;

表已创建。

SQL> create index small_idx on small(object_id);

索引已创建。
********************************************************************************
运行SQL并设置EVENT=10046，用TKPROF格式化TRACE文件，结果如下。

大表在外，小表在内的测试：
********************************************************************************
select count(subobject_name)
from big
where object_id in ( select object_id from small )

call       count         cpu      elapsed         disk        query      current          rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse          1        0.00         0.01            0            0            0             0
Execute        1        0.00         0.00            0            0            0             0
Fetch          2        0.00         0.14           29          900            0             1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          4        0.00         0.15           29          900            0             1
Rows       Execution Plan
-------  ---------------------------------------------------
        0  SELECT STATEMENT     GOAL: CHOOSE
        1     SORT (AGGREGATE)
      792      TABLE ACCESS (BY INDEX ROWID) OF 'BIG'
      892       NESTED LOOPS
       99        VIEW OF 'VW_NSO_1'
       99         SORT (UNIQUE)
       99          TABLE ACCESS (FULL) OF 'SMALL'
      792        INDEX (RANGE SCAN) OF 'BIG_IDX' (NON-UNIQUE)

select count(subobject_name)
from big
where exists ( select null from small where small.object_id = big.object_id )

call       count         cpu      elapsed         disk        query      current          rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse          1        0.00         0.00            0            0            0             0
Execute        1        0.00         0.00            0            0            0             0
Fetch          2        1.90         2.72         2917       216125            0             1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          4        1.90         2.72         2917       216125            0             1
Rows       Execution Plan
-------  ---------------------------------------------------
        0  SELECT STATEMENT     GOAL: CHOOSE
        1     SORT (AGGREGATE)
      792      FILTER
 214976       TABLE ACCESS (FULL) OF 'BIG'
      225       INDEX (RANGE SCAN) OF 'SMALL_IDX' (NON-UNIQUE)
********************************************************************************
用IN的性能数据：
cpu=0.00      elapsed=0.15      query=900      current=0      disk=29
用EXISTS的性能数据：
cpu=1.90      elapsed=2.72      query=216125      current=0      disk=2917
——在CPU的消耗和LIO、PIO上的对比十分明显，IN的效率高得多！

大表在内，小表在外的测试：
********************************************************************************
select count(subobject_name)
from small
where object_id in ( select object_id from big )

call       count         cpu      elapsed         disk        query      current          rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse          1        0.00         0.00            0            0            0             0
Execute        1        0.00         0.00            0            0            0             0
Fetch          2        0.41         1.71         2917         2982            0             1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          4        0.41         1.72         2917         2982            0             1
Rows       Execution Plan
-------  ---------------------------------------------------
        0  SELECT STATEMENT     GOAL: CHOOSE
        1     SORT (AGGREGATE)
       99      TABLE ACCESS (BY INDEX ROWID) OF 'SMALL'
  26972       NESTED LOOPS
  26872        VIEW OF 'VW_NSO_1'
  26872         SORT (UNIQUE)
 214976          TABLE ACCESS (FULL) OF 'BIG'
       99        INDEX (RANGE SCAN) OF 'SMALL_IDX' (NON-UNIQUE)

select count(subobject_name)
from small
where exists ( select null from big where small.object_id = big.object_id )

call       count         cpu      elapsed         disk        query      current          rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse          1        0.00         0.00            0            0            0             0
Execute        1        0.00         0.00            0            0            0             0
Fetch          2        0.00         0.00            0          202            0             1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          4        0.00         0.00            0          202            0             1
Rows       Execution Plan
-------  ---------------------------------------------------
        0  SELECT STATEMENT     GOAL: CHOOSE
        1     SORT (AGGREGATE)
       99      FILTER
       99       TABLE ACCESS (FULL) OF 'SMALL'
       99       INDEX (RANGE SCAN) OF 'BIG_IDX' (NON-UNIQUE)
********************************************************************************
用IN的性能数据：
cpu=0.41      elapsed=1.72      query=2982      current=26      disk=2917
用EXISTS的性能数据：
cpu=0.00      elapsed=0.00      query=202      current=0      disk=0
——在CPU的消耗和PIO、LIO上的对比十分明显，EXISTS效率高得多！

    有些遗憾的是我这个测试是在RBO下进行的，RBO是个死板的只根据优先级来确定执行计划的优化器，RBO不会评估实际的执行计划对系统造成的影响。在RBO中NESTED LOOP的优先级要远远大于MERGE JOIN，只要能走NESTED LOOP RBO就绝不会走MERGE JOIN。如果你用的是CBO，并且对表、索引做过统计分析，上面IN的测试一定会选择走MERGE JOIN。我们用HINTS在RBO下强制走MERGE JOIN对比一下这个SQL分别走MJ和NL的性能：
********************************************************************************
select count(subobject_name)
from small
where object_id in ( select/*+ use_merge(small big) */ object_id from big )

call       count         cpu      elapsed         disk        query      current          rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse          1        0.01         0.17            0            0            0             0
Execute        1        0.00         0.00            0            0            0             0
Fetch          2        0.09         0.27          187          473            0             1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          4        0.10         0.44          187          473            0             1
Rows       Execution Plan
-------  ---------------------------------------------------
        0  SELECT STATEMENT     GOAL: CHOOSE
        1     SORT (AGGREGATE)
       99      MERGE JOIN
  26872       SORT (UNIQUE)
 214976        INDEX (FULL SCAN) OF 'BIG_IDX' (NON-UNIQUE)
       99       SORT (JOIN)
       99        TABLE ACCESS (FULL) OF 'SMALL'
********************************************************************************
可以看到：
NESTED LOOP：cpu=0.41      elapsed=1.72      query=2982      current=26      disk=2917
MERGE JOIN：cpu=0.10      elapsed=0.44      query=437      current=2      disk=187
——这也证实了我上面的说法。很多人不敢让自己的SQL走merge join，其实对于两个已经具有排序结构的表merge join是最佳选择。

    下面我们讨论NOT IN和NOT EXISTS，我把它们放在一起讨论实属被逼无奈，因为很多人喜欢拿它们比较。其实NOT IN/NOT EXISTS与IN/EXISTS不一样，IN/EXISTS是完全可以作为等价替换结构的，而NOT IN/NOT EXISTS则不同，它们并不是等价替换结构！只有当子查询中不可能返回空值时，NOT IN/NOT EXISTS才可以等价替换。
    为什么？请看：
********************************************************************************
SQL> conn scott/tiger@tdb;
已连接。
SQL> select count(*)
2   from emp
3 where mgr is null;

COUNT(*)
----------------
          1

SQL> select count(*)
2   from emp
3 where empno not in ( select mgr from emp );

COUNT(*)
----------------
          0

SQL> select count(*)
2   from emp t1
3 where not exists ( select null
4                 from emp t2
5                where t2.mgr = t1.empno );

COUNT(*)
----------------
          7
********************************************************************************
    如果子查询中返回的结果集含有空值NOT IN永远是0，因为NULL代表“未知”，任何值和NULL比较永远是false。
    现在我们基于假设——子查询中不返回空值，来比较NOT IN和NOT EXISTS。
    在RBO中如果不使用HINTS来改变NOT IN的执行计划，几乎任何时候NOT IN都比NOT EXISTS慢得多，在CBO中如果具有准确的统计信息NOT IN的效率和NOT EXISTS的一样，甚至会比NOT EXISTS快得多。
    调整NOT IN性能的基本原则是：如果想让NOT IN跑得快就必须走合适的连接。
    select * from t1 where x not in ( select y from t2 )
    以这个句子为例（y无空值）
    这个句子可以等价替换为：
    a) select * from t1 where not exists ( select null from t2 where t2.y=t1.x)
    或
    b) select t1.* from t1, t2 where t1.x=t2.y(+) and t2.y is null
测试如下：
********************************************************************************
SQL> create table t1 as select * from all_objects where rownum <= 5000;

表已创建。

SQL> create table t2 as select * from all_objects where rownum <= 4950;

表已创建。

SQL> create index t2_idx on t2(object_id);

索引已创建。
********************************************************************************
RBO下的测试：
********************************************************************************
select count(*)
from t1 rbo
where object_id not in ( select object_id from t2 )

call       count         cpu      elapsed         disk        query      current          rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse          1        0.00         0.00            0            0            0             0
Execute        1        0.00         0.00            0            0            0             0
Fetch          2        6.13        19.12       127487       197502            0             1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          4        6.13        19.13       127487       197502            0             1
Rows       Execution Plan
-------  ---------------------------------------------------
        0  SELECT STATEMENT     GOAL: CHOOSE
        1     SORT (AGGREGATE)
       50      FILTER
     5000       TABLE ACCESS (FULL) OF 'T1'
     4950       TABLE ACCESS (FULL) OF 'T2'

select count(*)
from t1 rbo
where not exists ( select null from t2 where t2.object_id = rbo.object_id)

call       count         cpu      elapsed         disk        query      current          rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse          1        0.01         0.00            0            0            0             0
Execute        1        0.00         0.00            0            0            0             0
Fetch          2        0.01         0.12           83        10075            0             1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          4        0.02         0.12           83        10075            0             1
Rows       Execution Plan
-------  ---------------------------------------------------
        0  SELECT STATEMENT     GOAL: CHOOSE
        1     SORT (AGGREGATE)
       50      FILTER
     5000       TABLE ACCESS (FULL) OF 'T1'
     4950       INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE)

select count(*)
from t1, t2 rbo
where t1.object_id = rbo.object_id(+) and rbo.object_id is null

call       count         cpu      elapsed         disk        query      current          rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse          1        0.00         0.00            0            0            0             0
Execute        1        0.00         0.00            0            0            0             0
Fetch          2        0.00         0.05           72         5087            0             1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          4        0.00         0.06           72         5087            0             1
Rows       Execution Plan
-------  ---------------------------------------------------
        0  SELECT STATEMENT     GOAL: CHOOSE
        1     SORT (AGGREGATE)
       50      FILTER
     5000       NESTED LOOPS (OUTER)
     5000        TABLE ACCESS (FULL) OF 'T1'
     4950        INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE)
********************************************************************************
RBO自己选择的执行计划，性能数据：
NOT IN：cpu=6.13     elapsed=19.13     query=197502     current=0     disk=127487
NOT EXISTS：cpu=0.02     elapsed=0.12     query=10075     current=0     disk=83
OUTER JOIN：cpu=0.00     elapsed=0.06     query=5087      current=0     disk=72
——NOT EXISTS的效率比NOT IN好很多，但与OUTER JOIN相比NOT EXISTS的效率略低。

RBO，用HINTS改变NOT IN的执行计划：
********************************************************************************
select count(*)
from t1 rbo
where object_id not in ( select/*+ hash_aj(rbo t2) */ object_id from t2 )

call       count         cpu      elapsed         disk        query      current          rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse          1        0.04         0.45            0            3            0             0
Execute        1        0.00         0.00            0            0            0             0
Fetch          2        0.01         0.09           48          191            0             1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          4        0.05         0.55           48          194            0             1
Rows       Execution Plan
-------  ---------------------------------------------------
        0  SELECT STATEMENT     GOAL: CHOOSE
        0     SORT (AGGREGATE)
        0      HASH JOIN (ANTI)
        0       TABLE ACCESS (FULL) OF 'T1'
        0       INDEX (FAST FULL SCAN) OF 'T2_IDX' (NON-UNIQUE)
********************************************************************************
在只有t2.object_id有索引的情况下，hash join-anti性能数据如下：
HJ-ANTI：cpu=0.05     elapsed=0.55     query=194     current=0     disk=48
——性能好了很多！

在t1.object_id上建立索引，使用merge join-anti：
********************************************************************************
select count(*)
from t1 rbo
where object_id not in ( select /*+ merge_aj(rbo t2) */ object_id from t2 )

call       count         cpu      elapsed         disk        query      current          rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse          1        0.00         0.00            0            0            0             0
Execute        1        0.00         0.00            0            0            0             0
Fetch          2        0.00         0.00            0           28            0             1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          4        0.00         0.00            0           28            0             1
Rows       Execution Plan
-------  ---------------------------------------------------
        0  SELECT STATEMENT     GOAL: CHOOSE
        1     SORT (AGGREGATE)
       50      MERGE JOIN (ANTI)
     5000       INDEX (FULL SCAN) OF 'T1_IDX' (NON-UNIQUE)
     4950       SORT (UNIQUE)
     4950        INDEX (FAST FULL SCAN) OF 'T2_IDX' (NON-UNIQUE)
********************************************************************************
在t1.object_id上建立索引，merge join-anti的性能数据如下：
MJ-ANTI：cpu=0.00     elapsed=0.00     query=28     current=0     disk=0
——这个NOT IN语句在t1.object_id、t2.object_id都有索引的情况下，merge join-anti的效率高于上面的任何SQL。

    综上，只要NOT IN走合适的连接，其效率很高甚至高于NOT EXISTS和OUTER JOIN。

分享到：
| Hibernate createCriteria查询
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

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

    ### 经典SQL查询总结关于Exists, not Exists, IN, not IN 效率的说明 在数据库查询操作中，存在着多种方法来实现相似的功能，但不同的实现方式在性能上可能会有显著差异。本文将深入探讨 SQL 中 `EXISTS`, `NOT ...

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

    很明显使用NOT EXISTS的效率高多了。 使用EXISTS和NOT EXISTS的优点 使用EXISTS和NOT EXISTS可以提高查询的效率，避免了使用NOT IN和IN的低效率。同时，EXISTS和NOT EXISTS也可以使查询语句变得更加简洁和易于理解...

    sql　exists和not exists用法

    相较于`IN`、`NOT IN`等操作，`EXISTS`与`NOT EXISTS`具有更高的效率，尤其是在处理大型数据集时。 #### EXISTS 介绍 `EXISTS`关键字用于检查子查询是否至少返回一行数据。如果子查询返回至少一行数据，则`EXISTS`...

    exists SQL用法详解、exists和not exists的常用示例

    `NOT EXISTS`在这里的作用是，对于每个`t1.id`，如果子查询找不到比`t1.c_date`更晚的记录，则返回该记录。 **示例4**： ```sql SELECT distinct a.id, a.name FROM a, b WHERE a.id = b.a_id ``` 与： ```sql ...

    mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录

    MySQL中的`NOT IN`, `LEFT JOIN`, `IS NULL`, 和 `NOT EXISTS` 是四种不同的SQL查询方式，它们在特定情况下可以实现相似的功能，但实际执行效率可能会有很大差异。本文主要探讨这四种方法在处理大数据量时的性能表现...

    in exist not_in

    因此，在大多数情况下，NOT EXISTS 语句的效率比 NOT IN 语句高。 例如，如果我们有两个表 A 和 B，我们可以使用以下语句： SELECT * FROM A WHERE cc NOT IN (SELECT cc FROM B) 这条语句的效率可能不高，因为它...

    oracle数据库关于exists使用

    - 当主表（T1）数据量远小于子表（T2）时，使用 `EXISTS` 效率更高。这是因为 `EXISTS` 只需找到一个匹配项即可停止处理，而 `IN` 需要处理整个子查询结果集。 - 当主表数据量远大于子表时，使用 `IN` 更高效。此时...

    搞懂mysql的exists

    3. 优先考虑`EXISTS`：在比较多个潜在查询方法时，`EXISTS`往往比其他方法（如`IN`、`NOT IN`）更快，特别是在大型数据集上。 六、实际应用示例 假设我们有两个表，`employees`和`departments`，我们想找出没有部门...

    关于in and inner/outer join and exists

    在SQL查询中，`IN`、`INNER JOIN`、`OUTER JOIN` 和 `EXISTS` 是四个重要的关键字，它们用于处理数据表之间的关联和筛选。这些概念在数据库设计和数据检索中至关重要，理解并熟练运用它们能显著提高查询效率。 1. *...

    mysql insert if not exists防止插入重复记录的方法

    在MySQL中，当我们需要向...总结，使用`NOT EXISTS`子句可以在确保不插入重复记录的同时，保持较高的查询效率。在实际应用中，应根据具体需求和数据结构选择最合适的防止插入重复记录的方法，并注意优化查询性能。

    SQL SERVER中关于exists 和 in的简单分析

    `NOT EXISTS` 可以利用索引进行优化，因为它仅需知道子查询是否有匹配项，一旦找到，就不再继续检查其他行，因此在大多数情况下，对于大数据集，`NOT EXISTS` 的效率较高。 2. **IN 子句** `IN` 子句用于判断某个...

    exists的使用(查询选择了所有课程的学生信息)归纳.pdf

    Exists 语句的效率取决于主查询和子查询的大小，如果主查询的结果集小于子查询的结果集，那么 EXISTS 语句的效率将很高。反之，如果主查询的结果集大于子查询的结果集，那么 IN 语句的效率将更高。 在实际应用中，...

    SQLServer 优化SQL语句 in 和not in的替代方案

    在处理大量数据时，尤其需要注意避免使用效率较低的操作符，如`IN`和`NOT IN`。这些操作符虽然在编写时提供了简洁和易读性，但在执行效率上往往不如其他替代方法。本文将深入探讨`IN`和`NOT IN`的替代方案，并通过...

    oracle中not exists对外层查询的影响详解

    在10g中，这个查询的`buffer get`（缓冲区获取）较低，而在12c中较高，这表明了`NOT EXISTS`子句对外层查询性能的影响。 在10g中，由于`t2`中有`dep_id`为'mm'的记录，`NOT EXISTS`子句在匹配过程中可以更快地确定...

    SQL查询中in和exists的区别分析

    反之，如果B表的记录数远大于A表，`EXISTS` 的效率更高，因为它避免了大量不必要的比较。然而，这并不是绝对的，实际性能还取决于表结构、索引的存在以及数据库的优化策略。 在插入记录时，为了避免插入重复数据，...

    影响sql语句查询效率的因素

    通过合理处理NULL值、精确使用比较运算符、明智选择LIKE语句、谨慎使用`ORDER BY`子句以及优选`NOT EXISTS`而非`NOT IN`，可以显著提升查询效率，从而改善整体系统性能。在实践中，持续监控和调整查询策略，结合索引...

    简述Oracle中in和exists的不同

    而`NOT EXISTS`仍然可以利用索引来优化查询，因此在大多数情况下，`NOT EXISTS`的效率更高。 4. **特殊情况** - **第一种情况**：如果外层表有索引，且内层表较小，`IN`使用外层表的索引，效率较高。 - **第二种...

    oracle 和mysql1

    - 结合使用`IN`和`NOT IN`可以精确控制查询条件，如`SELECT * FROM tb_name WHERE id IN (10,12,15,16) AND NOT id IN (21,22,23)`，这将返回id在第一个列表但不在第二个列表的记录。 2. `EXISTS/NOT EXISTS`： -...

Magicbox
Global site tag (gtag.js) - Google Analytics