SQL优化之子查询-IN和EXISTS哪个快点

最新推荐文章于 2023-01-29 15:33:02 发布

原创

最新推荐文章于 2023-01-29 15:33:02 发布 · 685 阅读

0 ·

CC 4.0 BY-SA版权

本文探讨了SQL优化中IN和EXISTS的使用，指出选择哪个取决于执行计划和执行时间。通常认为，EXISTS在某些情况下性能更优，但作者通过实例表明，IN在特定场景下（如大表且子查询使用UNION ALL等）可能表现更好。建议根据执行计划来决定使用哪种方式，并提到了Oracle中的hint unnest/no_unnest对FILTER的影响。

关注我的微信公众号：pythonislover,领取python,大数据，SQL优化相关视频资料！~

Python大数据与SQL优化笔 QQ群：771686295

对SQL优化，有一定理解的人都会知道，SQL优化的核心是减少物理IO的次数，说的通俗点，我们要尽量减少表的扫描次数，这里的表主要是大表。

今天说的子查询，我们可以理解为SQL包含IN, NOT IN, EXISTS, NOT EXISTS的语句，以前经常有人会问IN和EXISTS到底怎么选，也有说EXISTS的性能更好，或者根据内表和外表的数据量来选择IN和EXISTS，其实在我看来，这些比较片面。哪个好，我们还是要看执行计划和执行时间。

当SQL中含有IN, NOT IN, EXISTS, NOT EXISTS的时候，优化器会尝试改写，为什么要改写呢？因为这些东西会导致一种叫Filter的东西，是不是很熟悉，前面的文章写到过哦，不知道的可以翻翻我前面的文章。

下面看看例子：

SQL> explain plan forselect EMPNO,ENAMEfrom empwhere exists(select DEPTNO from deptwhereemp.DEPTNO = dept.DEPTNOand dept.loc = 'BOSTON'union allselect DEPTNO from deptwhereemp.DEPTNO = dept.DEPTNOand dept.loc = 'DALLAS');  2    3    4    5    6    7    8    9   10   11   12   13   14Explained.
SQL> set linesize 400SELECT * FROM TABLE(dbms_xplan.display);SQL>
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1230022885
-----------------------------------------------------------------------------------------| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |         |     5 |    65 |     9   (0)| 00:00:01 ||*  1 |  FILTER                       |         |       |       |            |          ||   2 |   TABLE ACCESS FULL           | EMP     |    14 |   182 |     3   (0)| 00:00:01 ||   3 |   UNION-ALL                   |         |       |       |            |          ||*  4 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 ||*  5 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|*  6 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 ||*  7 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   1 - filter( EXISTS ( (SELECT "DEPTNO" FROM "DEPT" "DEPT" WHERE              "DEPT"."DEPTNO"=:B1 AND "DEPT"."LOC"='BOSTON') UNION ALL  (SELECT "DEPTNO" FROM              "DEPT" "DEPT" WHERE "DEPT"."DEPTNO"=:B2 AND "DEPT"."LOC"='DALLAS')))   4 - filter("DEPT"."LOC"='BOSTON')
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   5 - access("DEPT"."DEPTNO"=:B1)   6 - filter("DEPT"."LOC"='DALLAS')   7 - access("DEPT"."DEPTNO"=:B1)



SQL> explain plan forselect EMPNO,ENAMEfrom empwhere DEPTNO in(select DEPTNO from deptwheredept.loc = 'BOSTON'union all