当前位置： 首页 > article >正文

Oracle 表连接原理与优化

article 2024/12/22 3:25:45

        oralce支持3个连接，Nested Loops Join （嵌套循环连接），Sort Merge Join（排序合并连接），Hash Join（哈希连接）,Mysql只支持Nested Loops Join （嵌套循环连接）。

1. Nested Loops Join （嵌套循环连接）

nestloop 适用于大小表关联 小表做外表（驱动表，放内存中），外表（被驱动表）每返回一行数据，内表需要做一次全表扫描，该场景下适合再内表的关联键上建立索引，避免内表的多次全表扫描

SELECT  e.职员名 , c.客户名 , c.电话号
FROM    职员 e , 客户 c 
WHERE   e.入职日期 >= '20170101'
AND     e.职员编号 = c.管理职员编号

使用 NL连接基本原理的代码表示

< C , JAVA >
FOR (i = 0; i<100; i++){    -- outer loop
FOR (j = 0; j<100; j++) { -- inner loop
// Do Anything ...
}
}

< PL/SQL >
FOR OUTER IN 1..100 LOOP 
FOR INNER IN 1..100 LOOP 
dbms_output.put_line(OUTER || ' : ' || INNER);
END LOOP;
END LOOP;

PL/SQL代码表示

BEGIN
FOR OUTER IN (SELECT 职员编号 , 职员名 FROM 职员 
WHERE 入职日期 >= '20170101')
LOOP    -- outer loop 外循环
FOR INNER IN (SELECT 客户名 , 电话号 FROM 客户
WHERE 管理职员编号 = OUTER.职员编号)
LOOP   -- inner loop 内循环
dbms_output.put_line(
outer.职员名 || ' : ' || INNER.客户名 || ' : ' ||  INNER.电话号);
END LOOP; 
END LOOP;
END;

一般情况下，NL连接会对Outer与Inner两边表都会使用索引。如果，Outer的表（上面的职员）不是很大的话，有可能不使用索引。因为，即使使用全表扫描（Table Full Scan），也只是扫描一次。相反，Inner的表（上面的客户表）必须要使用索引。因为，以上面的PL/SQL为例，如果在Inner Loop里用管理职员编号检索客户表里的数据时，不使用索引的话，Outer Loop里读取到多少行数据，Inner 的表就要反复全表扫描（Table Full Scan）多少次。

1.1. NL连接基本原理

①.在【职员_X1】的索引里，找到入职日期 >= ‘20170101’的第一条记录。

②.通过【职员_X1】索引里的ROWID，查找【职员表】里的记录。

③.通过在【职员表】里读取的职员编号‘0001’，检索【客户_X1】的索引。

④.通过【客户_X1】索引里读取的ROWID，查找【客户表】里的记录。

⑤.在【客户_X1】索引里继续扫描，又找到一条记录管理职员编号为‘0001’。

⑥.通过【客户_X1】索引里读取的ROWID，查找【客户表】里的记录。（在【客户_X1】索引里继续扫描一条记录，发现管理职员编号大于‘0001’，在此处终止索引扫描。）

⑦.回到【职员_X1】索引里继续扫描一条记录，读取入职日期为‘20170822’的记录。

⑧.通过【职员_X1】索引的ROWID，查找【职员表】里的记录。

⑨.通过在【职员表】里读取的职员编号‘0005’，检索【客户_X1】的索引。

⑩.通过【客户_X1】索引里读取的ROWID，查找【客户表】里的记录。（在【客户_X1】索引里继续扫描一条记录，确认管理职员编号大于‘0005’，在此处终止索引扫描。）

SQL 文本

SELECT  /*+ ordered use_nl(c) */    -- ordered的意思就是按照书写的顺序连接，c是被驱动表，只写被驱动表就可以
e.职员名 , c.客户名 , c.电话号
FROM    职员 e , 客户 c 
WHERE   e.入职日期 >= '20170101'
AND     e.职员编号 = c.管理职员编号

执行计划

--如果是多表连接，可以安装下面的写法写
SELECT  /*+ ordered use_nl(B) use_nl(C)  use_nl(D) */    ---把被驱动表写进去就可以
FROM    A, B, C, D
WHERE   ...

SELECT  /*+ leading(C,A,D,B) use_nl(A) use_nl(D)  use_nl(B) */  --需要指定执行顺序的话

SQL 文本

SELECT /*+ ordered use_nl(c) index(e) index(c) */
e.职员编号, e.职员姓名, e.入职日期
,c.客户编号, c.客户姓名, c.电话号码, c.金额
FROM   职员 e , 客户 c
WHERE  c.管理职员编号 = e.职员编号        ------------------①
AND    e.入职日期  >=  '20170101'           ------------------②
AND    e.部门代码   =  'MCS'                     ------------------③
AND    c.金额  >= 2000                             ------------------④

--[索引信息]
PK_职员     ：职员编号
IDX_职员_01 ：入职日期
PK_客户     ：客户编号
IDX_客户_01 ：管理职员编号
IDX_客户_02 ：金额

提问

  • 1、上面的SQL文本如果按照Hint执行，请问WHERE条件里的执行顺序是什么？请用后面的编号①~④回答。
  • 2、请问索引信息中会用到哪个索引？

执行顺序是②→③→①→④，使用到的索引是IDX_职员_01与IDX_客户_01。

编号②：为了找到满足e.入职日期>=‘20170101’的条件，对索引IDX_职员_01进行索引范围扫描。（执行计划ID=3）

编号③：通过索引IDX_职员_01读取到的ROWID，对职员表进行回表扫描，并用条件e.部门代码='MCS'进行过滤。（执行计划ID=2）

编号①：通过职员表读取到的职员编号，根据连接条件，扫描客户表的索引IDX_客户_01 。（执行计划ID=5）

编号④：通过索引IDX_客户_01读取到的ROWID，对客户表进行回表扫描，并用条件c.金额>= 2000进行过滤。（执行计划ID=4）

--职员表e驱动表，客户表c被驱动表

1.2. NL连接的优化要点

1、确认驱动表回表以后是否被过滤过多记录 --解决：把表里面的过滤字段加到索引中去
2、确认驱动表返回的记录是否过多，这将影响被驱动表索引访问次数。

3、确认被驱动表回表以后是否被过滤过多记录。

4、驱动表的索引返回数据量决定着后面的工作量。

1.3. NL连接的特点

1、NL连接是随机扫描为主连接方式。

  • 海量数据进行连接的时候，NL连接比较不利。

2、NL连接是一条一条记录逐行进行的连接方式。

  • 即使是海量数据也能很快的返回结果集。
  • 返回海量数据的时候，NL连接很慢。

3、NL连接中，索引的构成策略特别重要。

  • 被驱动表的连接字段必须要有索引。
  • 驱动表需要有好的过滤索引。

4、NL连接适合用在OLTP系统。

  • 不适合在OLAP或数据仓库系统使用。

1.4. NL连接的优化练习

3中通过索引扫描返回2680行，但是回表后只返回3行，这个是有问题的，

通过改变索引，索引扫描返回3行了。

Buffers 逻辑读下降了很多。cr逻辑读，pr物理读，pw物理写

----如果返回的都是2680行呢？

1.5. NL连接的扩展

把客户回表的操作从4到1了

把io攒起来，一起回表

如果NL连接中没有产生物理I/O 的话，这三种查询性能是一样的，

如果产生了物理I/O ，后面两种方式稍微好点，

如果产生大量的I/O ，系统自动选择Hash连接了

2. Sort Merge Join（排序合并连接）

  1. 什么时候会用SortMerge连接？
  • 连接字段没有索引的时候。
  • 海量数据连接的时，索引效率不高的时候。
  1. 上面两种情况，会选择hash连接或者SortMerge连接
  • 不能使用 Hash 连接的时候 --【不是= 的时候】
  • 使用SortMerge连接

2.1. SGA和PGA

GPA中读取的数据比SGA中的要快。

2.2. 排序合并连接的基本原理

排序合并连接分为两个阶段

  • 1、Sort 阶段：两边集合按照连接字段进行排序。
  • 2、Merge 阶段：排序好的两边集合进行相互合并（Merge）操作。

--这个排序是在pga中完成的，pga不够，再到临时表空间

Merge阶段 PL/SQL 代码表示

BEGIN 
FOR OUTER IN (SELECT * FROM PGA里排序好的职员)
LOOP  -- outer loop
FOR INNER IN (SELECT * FROM PGA里排序好的客户
WHERE 管理职员编号 = OUTER.职员编号)
LOOP  -- inner loop
dbms_output.put_line( ... );
END LOOP;
END LOOP;
END;

上面是1对多，如果是多对多，也不是从头再扫描，还是从第一个出现0003的位置开始的

2.3. 排序合并连接为什么快

--【NL连接在返回少量的数据时，是最快的，排序合并连接数据是在PGA中排序后的，比NL连接在SGA中要快，而且还只扫描表一次】

2.4. 排序合并连接的主要作用

--Cross join 笛卡尔连接

--1，2条是主要的，3，4可能会用到哈希连接

2.5. 排序合并连接的执行计划控制

SELECT /*+ ordered use_merge(c) */
e.职员编号, e.职员姓名, e.入职日期
,c.客户编号, c.客户姓名, c.电话号码, c.金额
FROM   职员 e , 客户 c
WHERE  c.管理职员编号 = e.职员编号
AND    e.入职日期  >=  '20170101'
AND    e.部门代码   =  'MCS'
AND    c.金额  >= 2000

--不可以这么写，可以用leading来调一下

2.6. 排序合并连接的特点

  1. 主要特点
  • 1、使用PGA里排序好的数据。
  • 2、连接条件没有索引也可以使用。
  • 3、使用扫描为主的数据访问方式。
  1. 尴尬处境
  • 返回少量数据：NL连接更快
  • 返回大量数据：大部分情况Hash连接比排序合并连接快

3. Hash Join（哈希连接）

3.1. 哈希连接的基本原理

哈希连接分为两个阶段

  • 1、Build 阶段：读取小表（Build Input）生成Hash表。 ---构造阶段
  • 2、Probe 阶段：读取大表（Probe Input）探查Hash表并进行连接。 --探查阶段

小表作为构造表，大表为探查表

3.2. 哈希连接的基本原理

【Probe 阶段 PL/SQL 代码表示】

BEGIN 
FOR OUTER IN (SELECT 客户编号, 客户名, 电话号, 金额, 
管理职员编号
FROM   客户
WHERE  金额  >= 2000)
LOOP  -- outer loop
FOR INNER IN (SELECT 职员编号, 职员名, 入职日期 
FROM   职员的PGA里的生成的Hash表
WHERE  职员编号 = OUTER.管理职员编号)
LOOP  -- inner loop
dbms_output.put_line( ... );
END LOOP;
END LOOP;
END;

3.3. 哈希连接为什么快

--【sort merge连接是把两张表都放到PGA 中，如果PGA不够，就会使用临时表空间，会慢】

3.4. 海量Build Input处理

如下图，如果T1、T2表都是海量，即特别大，所以无法在PGA内存里完成生成Hash表的情况将如何处理？ --【肯定是先放到临时表空间，那怎么用呢】

会使用分割征服（Divide & Conquer）方式进行。分下面两个阶段进行。

  • 分区阶段
  • Join阶段

分区阶段：对两边集合的连接字段使用Hash函数，根据返回值进行动态分区，生成可以独立处理的子分区对（pair）。

--【根据hash返回的值，进行内部的分区，这些值会放到临时表空间，在临时表空间中进行join，小表作为构造表，大表为探查表】

Join阶段：各个子分区对互相进行Join，以小集合生成Hash表，进行Hash连接。

哈希连接的执行计划控制

SELECT /*+ ordered use_hash(c) */
e.职员编号, e.职员姓名, e.入职日期
,c.客户编号, c.客户姓名, c.电话号码, c.金额
FROM   职员 e , 客户 c
WHERE  c.管理职员编号 = e.职员编号
AND    e.入职日期  >=  '20170101'
AND    e.部门代码   =  'MCS'
AND    c.金额  >= 2000

SELECT /*+ leading(e) use_hash(c) */
e.职员编号, e.职员姓名, e.入职日期
,c.客户编号, c.客户姓名, c.电话号码, c.金额
FROM   职员 e , 客户 c
WHERE  c.管理职员编号 = e.职员编号
AND    e.入职日期  >=  '20170101'
AND    e.部门代码   =  'MCS'
AND    c.金额  >= 2000

SELECT /*+ use_hash(e c) swap_join_inputs(e) */    -- swap_join_inputs(e) 指定哪个表为构造表
e.职员编号, e.职员姓名, e.入职日期
,c.客户编号, c.客户姓名, c.电话号码, c.金额
FROM   职员 e , 客户 c
WHERE  c.管理职员编号 = e.职员编号
AND    e.入职日期  >=  '20170101'
AND    e.部门代码   =  'MCS'
AND    c.金额  >= 2000

--指定T2,T3是被驱动表

swap_join_inputs（T3）指定T3为构造表

4. 连接方式的选择基准

文章主要内容摘抄墨天轮恩墨大讲堂《Oracle 表连接原理与优化》。


http://www.kler.cn/a/445946.html

相关文章：

  • Qt之串口设计-线程实现（十二）
  • HBase、Hive、Redis 和 MongoDB的对比
  • [LeetCode-Python版] 定长滑动窗口1（1456 / 643 / 1343 / 2090 / 2379）
  • 单点登录平台Casdoor搭建与使用，集成gitlab同步创建删除账号
  • 【GCC】2015: draft-alvestrand-rmcat-congestion-03 机器翻译
  • InternVL简读
  • Qt 开发之蓝牙连接
  • hackme靶机保姆及攻略
  • elasticsearch Flattened 使用
  • JS面向对象及继承
  • 【第六节】Git Flow：分支管理模型与工作流程
  • nano编辑器怎么退出并保存
  • DeepFaceLab技术浅析（六）：后处理过程
  • Golang中什么是协程泄露(Goroutine Leak)
  • autok3s管理k3s单节点集群
  • [Unity] 【VR】【游戏开发】在VR中使用New Input System获取按键值的完整教程
  • 【Jenkins】pipeline 的基础语法以及快速构建一个 jenkinsfile
  • sql server索引优化语句
  • Tomcat10安装报错Unknown module: java.rmi specified to --add-opens
  • nginx-虚拟主机配置笔记
  • Python TXT文件按条件批量删除行工具
  • 静态socks5代理ip 帮助您找到最合适的ip代理服务
  • 通过阿里云 Milvus 与 PAI 搭建高效的检索增强对话系统
  • JVM基本机制
  • Java 网络编程 ①-TCP || UDP || Socket
  • [搜广推]王树森推荐系统——矩阵补充最近邻查找