【SQL开发实战技巧】系列(六):从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率,记住内外关联条件不要乱放

这篇具有很好参考价值的文章主要介绍了【SQL开发实战技巧】系列(六):从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率,记住内外关联条件不要乱放。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

系列文章目录

【SQL开发实战技巧】系列(一):关于SQL不得不说的那些事
【SQL开发实战技巧】系列(二):简单单表查询
【SQL开发实战技巧】系列(三):SQL排序的那些事
【SQL开发实战技巧】系列(四):从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列(五):从执行计划看IN、EXISTS 和 INNER JOIN效率,我们要分场景不要死记网上结论
【SQL开发实战技巧】系列(六):从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率,记住内外关联条件不要乱放
【SQL开发实战技巧】系列(七):从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列(八):聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列(九):一个update误把其他列数据更新成空了?Merge改写update!给你五种删除重复数据的写法!
【SQL开发实战技巧】系列(十):从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列(十一):拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列(十二):三问(如何对字符串字母去重后按字母顺序排列字符串?如何识别哪些字符串中包含数字?如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列(十三):讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列(十四):计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列(十五):查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列(十六):数据仓库中时间类型操作(初级)日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列(十七):数据仓库中时间类型操作(初级)确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列(十八):数据仓库中时间类型操作(进阶)INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列(十九):数据仓库中时间类型操作(进阶)如何一个SQL打印当月或一年的日历?如何确定某月内第一个和最后—个周内某天的日期?
【SQL开发实战技巧】系列(二十):数据仓库中时间类型操作(进阶)获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列(二十一):数据仓库中时间类型操作(进阶)识别重叠的日期范围,按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列(二十二):数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列(二十三):数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列(二十四):数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列(二十五):数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列(二十六):数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行



前言

本篇文章讲解的主要内容是:从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率,还是那就话,别死记网上结论、在使用内外关联时,特别是简写方式时记住关联条件不要乱放!
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。


一、从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率

有些单位的部门(如40)中一个员工也没有,只是设了一个部门名字,如下列语句:

select count(*) from dept where deptno=40;

如何通过关联查询把这些信息查出来?
同样有三种写法:NOT IN、NOT EXISTS 和LEFT JOIN
语句及PLAN如下(版本为11.2.0.4.0 )。
环境:

alter table dept add constraints pk_dept primary key (deptno); --如果你有就不用建了
  • NOT IN用法
EXPLAIN PLAN FOR select *
FROM dept
WHERE deptno NOT IN (SELECT emp.deptno FROM emp WHERE emp.deptno IS NOT NULL);
SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1353548327
--------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    23 |     6  (17)| 00
|   1 |  MERGE JOIN ANTI             |         |     1 |    23 |     6  (17)| 00
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00
|*  4 |   SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00
|*  5 |    TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("DEPTNO"="EMP"."DEPTNO")
       filter("DEPTNO"="EMP"."DEPTNO")
   5 - filter("EMP"."DEPTNO" IS NOT NULL)

19 rows selected
  • NOT EXISTS 用法
EXPLAIN PLAN FOR SELECT*
FROM dept
WHERE NOT EXISTS ( SELECT NULL FROM emp WHERE emp.deptno  =  dept.deptno) ; 
SELECT * FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1353548327
--------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    23 |     6  (17)| 00
|   1 |  MERGE JOIN ANTI             |         |     1 |    23 |     6  (17)| 00
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00
|*  4 |   SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00
|*  5 |    TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
   5 - filter("EMP"."DEPTNO" IS NOT NULL)

19 rows selected
  • LEFT JOIN 用法

根据前面介绍过的左联知识,LEFT JOIN 取出的是左表中所有的数据,其中与右表不匹配的就表示左表NOT IN右表。
所以这里LEFT JOIN加上条件TS NULL,就是LEFT JOIN的写法:

EXPLAIN PLAN FOR
SELECT dept.*
FROM dept
LEFT JOIN emp ON emp.deptno = dept.deptno WHERE emp.deptno IS NULL;

SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1353548327
--------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    23 |     6  (17)| 00
|   1 |  MERGE JOIN ANTI             |         |     1 |    23 |     6  (17)| 00
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00
|*  4 |   SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00
|*  5 |    TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
   5 - filter("EMP"."DEPTNO" IS NOT NULL)

19 rows selected

通过看上面的执行计划,三个SQL用的都是 MERGE JOIN ANTI, 说明这三种方法的效率一样。
如果想改写,就要对比改写前后的PLAN,根据PLAN来判断并测试哪种方法的效率高,一定要记住不能凭借某些结论来碰运气。

二、外连接中的条件不要乱放,建议大家使用join而非(+)

对于系列三博客介绍的左联语句,见下面的数据。

SELECT l.str AS left_str, r.str AS right_str,r.status FROM l
LEFT JOIN r	ON l.v = r.v
ORDER BY 1 , 2 ;
LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_1             
left_2             
left_3   right_3            1
left_4   right_4            0

那现在有这么一个需求:对于其中的L表,四条数据都返回。而对于R表,我们需要只显示其中的status=1的数据,也就是下面这样的结果:

LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_1             
left_2             
left_3   right_3            1
left_4             

对于这个需求,可能有些人会加一个where条件!然后结果就变成了下面这样了:
left join写法:

SELECT l.str AS left_str, r.str AS right_str,r.status 
 FROM l
LEFT JOIN r  ON (l.v = r.v)
where  r.status=1
ORDER BY 1 , 2;
LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_3   right_3            1

(+)写法:

SELECT l.str AS left_str, r.str AS right_str, r.status
  FROM l, r
 where l.v = r.v(+)
   and r.status = 1
 ORDER BY 1, 2;
LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_3   right_3            1

而此时的执行计划:

SQL> EXPLAIN PLAN FOR
  2  SELECT l.str AS left_str, r.str AS right_str,r.status
  3   FROM l
  4  LEFT JOIN r  ON (l.v = r.v)
  5  where  r.status=1
  6  ORDER BY 1 , 2;

Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 688663707
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     2 |    42 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |     2 |    42 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     2 |    42 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| R    |     2 |    24 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| L    |     4 |    36 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("L"."V"="R"."V")
   3 - filter("R"."STATUS"=1)

17 rows selected

很明显,结果以及执行计划(HASH JOIN)与我们期望得到的结果都不一致!!!这是很多人在写查询或更改查询时常遇到的一种错误。问题就在于所加条件的位置及写法,正确的写法分别如下:

SQL> SELECT l.str AS left_str, r.str AS right_str, r.status
  2    FROM l
  3    LEFT JOIN r
  4      ON (l.v = r.v and r.status = 1)
  5   ORDER BY 1, 2;

LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_1             
left_2             
left_3   right_3            1
left_4             



SQL> SELECT l.str AS left_str, r.str AS right_str, r.status
  2    FROM l, r
  3   where l.v = r.v(+)
  4     and r.status(+) = 1
  5   ORDER BY 1, 2;

LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_1             
left_2             
left_3   right_3            1
left_4             

看一下这时候的执行计划:

SQL> EXPLAIN PLAN FOR
  2  SELECT l.str AS left_str, r.str AS right_str, r.status
  3    FROM l
  4    LEFT JOIN r
  5      ON (l.v = r.v and r.status = 1)
  6   ORDER BY 1, 2;

Explained


SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2310059642
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     4 |    84 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |     4 |    84 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |      |     4 |    84 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| L    |     4 |    36 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| R    |     2 |    24 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("L"."V"="R"."V"(+))
   4 - filter("R"."STATUS"(+)=1)

17 rows selected

以上两种写法结果均正确,且根据执行计划HASH JOIN OUTER明确走的是外连接。而且根据上面查询我们能够看出来JOIN的方式明显更容易辨别,这也是我反复建议使用JOIN的原因。
对于上面SQL我们还可以使用先过滤再关联的方式,即R表先过滤:

(select * from r where status=1) r

总结

同上一篇博客所说,在使用in exists或则NOT IN、NOT EXISTS 和 LEFT JOIN时候,不要想当然的认为in和not in效率极其低下,在本章案例中通过执行计划能够直观的看到,三者效率竟然一致了!!所以,读万卷书不如行万里路,网上别人做的总结再好,也不如自己实践一把来的真实。还有就是,在使用关联查询时候,关联条件和过滤条件一定要想好放哪里,不然你会想当然的错了!文章来源地址https://www.toymoban.com/news/detail-810600.html

到了这里,关于【SQL开发实战技巧】系列(六):从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率,记住内外关联条件不要乱放的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Junit + Mockito 测试回调(callback) 方法

    语言:Java JDK: Java8 及以上。 Java8推出函数式接口后,就经常使用此特性开发。 在使用Junit + Mockito 时遇到有回调(callback)的函数方法时,遇到几种情况,这里进行记录。 对于Mockito中打桩个人理解:测试的方法在正常环境需要引入其他类中的方法来 支撑(实) ,但是在

    2024年01月17日
    浏览(40)
  • Java 中的 void 和 Kotlin 的 Unit

    1 Java 中的 void 和 Kotlin 中的 Unit 在 Java 中,如果定义的函数没有返回值,就需要用 void 来修饰,即 void 不可以省略。这也在 Java 中不能说函数调用皆是表达式的原因,因为有些方法不具有返回值和类型信息,就不能算作是表达式。 Java 在语言层设计一个 Void 类。java.lang.Void 类

    2024年03月24日
    浏览(40)
  • IT涨知识:CPU、内存、磁盘

    CPU: CPU是电子计算机的主要设备之一,电脑中的核心配件。其功能主要是解释计算机指令以及处理计算机软件中的数据。CPU是计算机中负责读取指令,对指令译码并执行指令的核心部件。 CPU使用率过高会怎样:CPU使用率过高会影响处理速度,从而使电脑运行缓慢。如果CPU达

    2024年02月02日
    浏览(30)
  • Systrace系列11 —— Triple Buffer 解读

    本文主要是对 Systrace 中的 Triple Buffer 进行简单介绍,简单介绍了如何在 Systrace 中判断卡顿情况的发生,进行初步的定位和分析,以及介绍 Triple Buffer 的引入对性能的影响。 Systrace 中可以看到应用的掉帧情况,我们经常看到说主线程超过 16.6 ms 就会掉帧,其实不然,这和我们

    2024年02月06日
    浏览(38)
  • 计算机毕业设计开题报告如何答辩---毕设开题答辩稿

    以下是万能的开题答辩稿,按照这个顺序既可以清楚的表达自己做了什么,又可以让答辩老师对你挑不出毛病,赶快收藏吧。 尊敬的评委老师们: 大家好!我是XXX,XXX学院XXX专业的一名学生。今天非常荣幸能够在这里向各位展示我的毕业设计开题报告。我的毕业设计题目是

    2024年02月11日
    浏览(41)
  • 无人船毕设进展

    无人平台控制系统的研究现状 飞行控制器(飞控)作为无人船的控制大脑,是无人船能够保持稳定的航行的核心硬件[1]。主要用于无人机[2][3]的在起飞、巡航、降落等阶段对其他系统进行的协同辅助控制的元件,通常有集成有惯性测量单元(IMU)、气压计、加速度计、磁罗盘

    2023年04月08日
    浏览(21)
  • Mac电脑解决npm权限不足问题,每次需要加sudo赋权限

    在Mac电脑上,在执行 npm run start 时需要使用 sudo 可能是因为您的项目需要以超级用户权限来执行某些操作。然而,为了避免每次都要输入密码,您可以尝试以下方法来解决该问题: 在终端中,进入您的项目文件夹,并将其所有者更改为您当前的用户。使用以下命令: 这将使

    2024年02月16日
    浏览(39)
  • client-go初级篇,从操作kubernetes到编写单元测试

    欢迎访问我的GitHub 这里分类和汇总了欣宸的全部原创(含配套源码):https://github.com/zq2599/blog_demos 本篇概览 尽管长篇系列《client-go实战》的内容足够丰富,然而内容太多每个知识点也有一定深度,对于打算快速学习并开始kubernetes开发的新手并不友好,因此本篇的目标读者就是

    2024年02月12日
    浏览(40)
  • Ceph的介绍与部署

    目录 存储基础 单机存储设备 DAS(直接附加存储,是直接接到计算机的主板总线上去的存储) NAS(网络附加存储,是通过网络附加到当前主机文件系统之上的存储) SAN(存储区域网络) 单机存储的问题 存储处理能力不足 存储空间能力不足 单点故障问题 商业存储解决方案

    2024年01月16日
    浏览(27)
  • ceph安装部署

    Ceph 简介 存储基础 单机存储设备 单机存储的问题 分布式存储的类型 分布式存储(软件定义的存储 SDS) Ceph 架构 Ceph 核心组件 ​编辑 Pool中数据保存方式支持两种类型 OSD 存储后端 Ceph 数据的存储过程 Ceph 集群部署 基于 ceph-deploy 部署 Ceph 集群 1、关闭 selinux 与防火墙 2、根据

    2024年02月16日
    浏览(29)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包