咕咕咕的小破站
《收获，不止Oracle》读书笔记上篇-表连接
咕咕咕 fishing
  2024-08-28 09:35:14   2024-08-28 09:35:14      3.7k 字  14 分钟  

第六章 - 经典，表的连接学以致用

三种连接类型

嵌套循环连接（Nested Loop Join）、哈希连接（Hash Join）和排序合并连接（Sort Merge Join）是Oracle数据库中最常用的三种连接算法。

嵌套循环连接（Nested Loop Join）

嵌套循环连接是最简单的连接方法之一，它通过遍历一个表（外部表），对于外部表中的每一行，再遍历另一个表（内部表）来查找匹配的行。

  1. 选择一个较小的表作为外部表。通常，外部表是较小的表或已经被索引访问的表。
  2. 遍历外部表的每一行。
  3. 对于外部表中的每一行，遍历内部表以查找匹配的行。如果找到匹配的行，则将这两行组合在一起作为结果的一部分。

当一个表很小或者已经被索引访问时，嵌套循环连接可以非常高效。
如果内部表很大，则嵌套循环连接可能会非常慢。对于大数据量的表，嵌套循环连接可能会导致性能问题。

哈希连接（Hash Join）

哈希连接是一种高效的连接算法，它通过在内存中创建哈希表来查找匹配的行。

  1. 选择一个较大的表作为内部表。对于内部表中的每一行，根据连接键计算哈希值，并将该行存储在一个哈希表中。
  2. 遍历外部表。对于外部表中的每一行，根据连接键计算哈希值，并在哈希表中查找匹配的行。如果找到匹配的行，则将这两行组合在一起作为结果的一部分。

当内部表可以完全装入内存时很高效，可以处理较大的数据集。
但如果内部表太大以至于无法完全装入内存，则性能可能会受到影响。哈希表的构建可能会消耗较多的内存资源。

排序合并连接（Sort Merge Join）

排序合并连接是一种连接算法，它通过先对两个表进行排序，然后通过比较排序后的键值来查找匹配的行。

  1. 对两个表按照连接键进行排序。如果表已经排序或可以通过索引访问，则可以跳过此步骤。
  2. 遍历两个排序后的表。比较两个表中当前行的连接键值。如果键值相同，则将这两行组合在一起作为结果的一部分。

当连接键的值有序时，排序合并连接非常高效。可以处理大数据量。
但如果表未排序，则需要额外的排序步骤，这可能会消耗时间和磁盘空间。当表很大时，排序可能会很慢。

各类连接访问次数差异

可以使用alter session set statistics_level = all进行跟踪。
这条命令用于设置会话级别的统计信息级别。作用是开启尽可能多的统计信息收集，以便于诊断和性能调优。
可以知道表的访问次数。

嵌套循环的表访问次数

准备表和数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
execute dbms_random.seed(0);
create table t1 as
SELECT rownum                      id,
       rownum                      n,
       dbms_random.string('a', 50) contents
FROM dual
CONNECT BY level <= 100
ORDER BY dbms_random.random;
create table t2 as
SELECT rownum                      id,
       rownum                      t1_id,
       rownum                      n,
       dbms_random.string('b', 50) contents
FROM dual
CONNECT BY level <= 100000
ORDER BY dbms_random.random;

查看嵌套循环的连接次数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SQL> set linesize 1000
SQL> alter session set statistics_level = all;

Session altered.

SQL> SELECT /*+leading(t1) use_nl(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id;
-- 省略结果集

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  f7haq9xwppqqf, child number 0
-------------------------------------
SELECT /*+leading(t1) use_nl(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id

Plan hash value: 1967407726

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100 |00:00:00.33 |   98515 |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |    100 |    100 |00:00:00.33 |   98515 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       9 |
|*  3 |   TABLE ACCESS FULL| T2   |    100 |      1 |    100 |00:00:00.33 |   98506 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."ID"="T2"."T1_ID")


21 rows selected.

Starts 这列表示表访问的次数。这里 t1 被访问了1次，t2 被访问了 100 次。

下面还有三条不同i情况的语句，就不一一贴详细执行过程，直接说结果了。
SELECT /*+leading(t1) use_nl(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id and t1.n in (17,19); t2 表被访问2次
SELECT /*+leading(t1) use_nl(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id and t1.n 19; t2 表被访问1次
SELECT /*+leading(t1) use_nl(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id and t1.n = 999999999; t2 表被访问0次

原因也很简单，和 t1 表中返回的数据数量有关。
在嵌套循环连接中，驱动表返回多少条记录，被驱动表就访问多少次。

此外，这里使用 /*+leading(t1) use_nl(t1 t2)*/ 这个 HINT。
其中use_nl表示强制用嵌套循环连接方式。leading(t1)表示强制先访问 t1 表，也就是 t1 表作为驱动表。

哈希连接的表访问次数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SQL> SELECT /*+leading(t1) use_hash(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id;
-- 省略结果集

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  bgn80s3r4xwq9, child number 0
-------------------------------------
SELECT /*+leading(t1) use_hash(t1 t2)*/ * FROM t1,t2 WHERE t1.id =
t2.t1_id

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100 |00:00:00.02 |     994 |       |       |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:00.02 |     994 |  1000K|  1000K| 1329K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       2 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.01 |     992 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID"="T2"."T1_ID")


21 rows selected.

在HASH连接中，驱动表和被驱动表都只会访问0次或者1次。

哈希连接中，只有构建哈希表这一步需遍历驱动表一次。哈希表构建完成后，后续的查找阶段不再需要访问驱动表，而是直接在哈希表中查找匹配项。被驱动表同理。
当驱动表中没有符合条件的数据时，便不会访问被驱动表。当查询条件始终不成立时（比如 where 1 = 2），也不会访问驱动表。

排序合并的表访问次数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SQL> SELECT /*+ordered use_merge(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id;
-- 省略结果集

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID  bgs49pws4vu8d, child number 0
-------------------------------------
SELECT /*+ordered use_merge(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id

Plan hash value: 412793182

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |    100 |00:00:00.07 |     987 |    982 |       |       |          |
|   1 |  MERGE JOIN         |      |      1 |    100 |    100 |00:00:00.07 |     987 |    982 |       |       |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
|   2 |   SORT JOIN         |      |      1 |    100 |    100 |00:00:00.01 |       2 |      1 | 13312 | 13312 |12288  (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       2 |      1 |       |       |          |
|*  4 |   SORT JOIN         |      |    100 |    100K|    100 |00:00:00.07 |     985 |    981 |  9762K|  1209K| 8677K (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.05 |     985 |    981 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------


23 rows selected.

在访问次数上，排序合并连接和HASH连接是一样的，两表都只会访问0次或者1次。关于0次的试验这里就不再举例了。
排序合并连接根本就没有驱动和被驱动的概念，而嵌套循环和哈希连接要考虑驱动和被驱动情况。

排序合并连接，也只有在排序时才需要访问表，排序完成后，后续的合并阶段不需要访问表，只需要从排序结果中查找匹配项即可。

各类连接驱动顺序区别

观察两表的前后访问顺序对调后的性能差异。

嵌套循环的表驱动顺序

SELECT /*+leading(t1) use_nl(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id and t1.n = 19; t1 表被访问 1 次，t2 表被访问 1 次。使用 Buffers 989
SELECT /*+leading(t2) use_nl(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id and t1.n = 19; t1 表被访问 100k 次，t2 表被访问 1 次。使用 Buffers 200k

在嵌套循环连接中驱动表的顺序非常重要，性能差异十分明显。
嵌套循环连接要特别注意驱动表的顺序，小的结果集先访问，大的结果集后访问，才能保证被驱动表的访问次数降到最低，从而提升性能。

哈希连接的表驱动顺序

SELECT /*+leading(t1) use_hash(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id and t1.n = 19; t1 表被访问 1 次，t2 表被访问 1 次。使用 Buffers 988，Used-Mem 416k ，耗时 0.02s
SELECT /*+leading(t2) use_hash(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id and t1.n = 19; t1 表被访问 1 次，t2 表被访问 1 次。使用 Buffers 988，Used-Mem 13M ，耗时 0.03s

其中 Buffers 相同，但 Used-Mem 差异非常大，说明排序尺寸差异明显。时间也可以看出一些
哈希连接中驱动表的顺序非常重要，性能也差别明显。

排序合并的表驱动顺序

SELECT /*+leading(t1) use_merge(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id and t1.n = 19; t1 表被访问 1 次，t2 表被访问 100k 次。使用 Buffers 987，Used-Mem 分别是 2048 和 8677k ，耗时 0.04s
SELECT /*+leading(t2) use_merge(t2 t1)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id and t1.n = 19; t1 表被访问 1 次，t2 表被访问 100k 次。使用 Buffers 987，Used-Mem 分别是 2048 和 8677k ，耗时 0.05s

嵌套循环连接和哈希连接有驱动顺序，驱动表的顺序不同将影响表连接的性能，而排序合并连接没有驱动的概念，无论哪张表在前都无妨。

各类连接排序情况分析

嵌套循环和哈希连接无需排序

嵌套循环的执行计划中没有 Used-Mem 信息，说明没有排序。
哈希连接也没有排序，虽然执行计划中有 Used-Mem 信息。但消耗的内存是用于建立哈希表的。

排序只需取部分字段

SELECT /*+leading(t1) use_merge(t1 t2)*/ t1.id FROM t1,t2 WHERE t1.id = t2.t1_id and t1.n = 19;
SELECT /*+leading(t1) use_merge(t1 t2)*/ * FROM t1,t2 WHERE t1.id = t2.t1_id and t1.n = 19;

上面第一条sql只取部分字段，比第二条sql取全部字段所消耗的内存更小。
在PGA空间不足以容纳排序区，导致排序需要在磁盘上进行时，那么性能会出现数量级的下降。

各类连接限制场景对比

一般来说 HINT 是 Oracle 提供的用来强制走某执行计划的一个工具，比如你不想让 Oracle 的某查询走索引而要走全表扫描，你使用 full() 的提示就可以达成目的，反之亦是如此。
但是如果你用 HINT 将导致 Oracle 的运行结果有错，或者是 Oracle 在特定场景下无法支持这个 HINT 的执行计划，那就无法如你所愿。
比如之前的 COUNT(*) 的优化，如果你的索引列没有定义为非空属性，无论如何使用 INDEX() 的 HINT ，都不可能让 Oracle 走索引的，因为索引不能存储空值，用索引来统计将得到一个错误的结果，这是无法容忍的，所以 HINT 是无法生效的。
同样，这里与表连接相关的三个 HINT 分别是 use_nl、use_hash 和 use_merge ，如果在特定的写法下，用这些 HINT 也无法达成所愿。

哈希连接的限制

哈希连接不支持不等值连接<>，不支持>和<的连接方式，也不支持LIKE的连接方式。

排序合并的限制

排序合并连接不支持<>的连接条件，也不支持LIKE的连接条件，但是比起哈希连接，支持面要广一些，支持>之类的连接条件。

嵌套循环无限制

嵌套循环支持所有的sql连接条件写法，没有任何限制。

索引与各表连接经典优化

嵌套循环与索引

嵌套索引的原理前面有写过，先从驱动表过滤出符合条件的记录，再去被驱动表匹配符合条件的记录，最后将结果组合在一起返回。
从两个表中匹配数据的过程可以使用索引提高性能，在过滤条件或连接条件有索引时，可以大大提高查询效率。
一般在驱动表的过滤条件加索引、在被驱动表的连接条件加索引比较合理。

适合嵌套循环连接的场景

  1. 两表关联返回的记录不多，最佳情况是驱动表结果集仅返回1条或少量几条记录，而被驱动表仅匹配到1条或少量几条记录，这种情况即便T1表和T2表的记录奇大无比，也是非常迅速的。
  2. 遇到一些不等值查询导致哈希和排序合并连接被限制使用，不得不使用L连接。
  3. 驱动表的限制条件所在的列有索引。
  4. 被驱动表的连接条件所在的列有索引。

哈希连接与索引

哈希连接、排序合并连接和嵌套循环连接最大的差别在于，连接条件的索引对它们起不到传递的作用。
对于哈希连接和排序合并连接来说，索引的连接条件起不到快速检索的作用，但是限制条件列如果有适合的索引可以快速检索到少量记录，还是可以提升性能的。
因此关于哈希连接与索引的关系可以理解为单表索引的设置技巧，这在之前的索引章节中己经详细叙说过了。

此外两表关联等值查询，在没有任何索引的情况下，Oracle 倾向于走哈希连接这种算法，因为哈希连接的算法本身还是比较高效先进的。
哈希连接需要在 PGA 中的HASH AREA SIZE中完成，因此增大HASH AREA SIZE也是优化哈希连接的一种有效的途径，一般在内存自动管理的情况下，只要加大PGA区大小即可。

排序合并与索引

排序合并连接上的连接条件虽然没有检索的作用，却有消除排序的作用。
索引本身排序，可以有效地避免排序合并连接中的排序。
此外，还有一个和哈希连接类似的优化思路，就是增大内存排序区，避免在排序尺寸过大时在磁盘中排序。

《收获，不止Oracle》读书笔记上篇-表连接
读书笔记/《收获，不止Oracle》读书笔记上篇-表连接/
作者
咕咕咕
发布于
2024-08-28 09:35
许可
  1. 第六章 - 经典，表的连接学以致用
    1. 三种连接类型
      1. 嵌套循环连接（Nested Loop Join）
      2. 哈希连接（Hash Join）
      3. 排序合并连接（Sort Merge Join）
    2. 各类连接访问次数差异
      1. 嵌套循环的表访问次数
      2. 哈希连接的表访问次数
      3. 排序合并的表访问次数
    3. 各类连接驱动顺序区别
      1. 嵌套循环的表驱动顺序
      2. 哈希连接的表驱动顺序
      3. 排序合并的表驱动顺序
    4. 各类连接排序情况分析
      1. 嵌套循环和哈希连接无需排序
      2. 排序只需取部分字段
    5. 各类连接限制场景对比
      1. 哈希连接的限制
      2. 排序合并的限制
      3. 嵌套循环无限制
    6. 索引与各表连接经典优化
      1. 嵌套循环与索引
      2. 哈希连接与索引
      3. 排序合并与索引
  1. 第六章 - 经典，表的连接学以致用
    1. 三种连接类型
      1. 嵌套循环连接（Nested Loop Join）
      2. 哈希连接（Hash Join）
      3. 排序合并连接（Sort Merge Join）
    2. 各类连接访问次数差异
      1. 嵌套循环的表访问次数
      2. 哈希连接的表访问次数
      3. 排序合并的表访问次数
    3. 各类连接驱动顺序区别
      1. 嵌套循环的表驱动顺序
      2. 哈希连接的表驱动顺序
      3. 排序合并的表驱动顺序
    4. 各类连接排序情况分析
      1. 嵌套循环和哈希连接无需排序
      2. 排序只需取部分字段
    5. 各类连接限制场景对比
      1. 哈希连接的限制
      2. 排序合并的限制
      3. 嵌套循环无限制
    6. 索引与各表连接经典优化
      1. 嵌套循环与索引
      2. 哈希连接与索引
      3. 排序合并与索引