SQL调优之三：执行计划的连接方式

什么是连接？

连接是指在SQL的where或者from后面跟着多个行源（表，视图，或者数据集等）的时候，数据库对这些行源进行联合的操作。连接的条件则是数据库要来判断不同数据集之间关系的依据，如果没有提供正确的关联关系，那么数据库就会对数据集做笛卡尔连接，把内表（N行）和外表（M行）的每一行数据进行匹配，结果集是N*M行。

优化器怎么执行一条Join语句？

一次join的操作有以下4个部分：

• access path

　　对于简单的语句来说，优化器必须决定以什么方式来获取数据，比如说通过索引，还是全表扫描。索引是范围扫描，跳跃范围扫描，还是唯一扫描。

• 连接方式

　　把每一对行集给连接起来的方式，就是join methods。比如说嵌套循环连接，哈希连接，排序合并连接。笛卡尔连接必须依赖其中一种连接方式。

• 连接类型

　　连接的条件决定连接的类型。比如说内部连接，只返回匹配条件的行源。外连接则会获取不匹配条件的数据。

• 连接顺序

　　在执行一条需要连接超过两个表的语句，Oracle会先连接其中的而两个表，然后将连接的结果集，再和下一个表连接，一直到所有的表都连接完成。

优化器怎么选定连接的执行计划？

Oracle在决定连接的顺序和连接方式的时候，目标是尽早减少数据集的行数（基本方针），这样子SQL在运行过程中的工作量就会少一点。

在这个过程中，优化器会根据可能的连接顺序，连接方式以及access path来生成一个执行计划的集合。然后再对这群执行计划进行成本估算，最后选择一个成本最低的执行计划。

当选择执行计划的时候，优化器会考虑以下因素：

• 优化器会根据唯一索引或者主键约束，判断是不是有哪个行源（row source）里的几个表的连接结果里只包含了最多一行数据。如果是的话，优化器在考虑连接顺序的时候，会先把这些表放在最前面。然后再对剩下的其他表进行连接。
• 连接语句里面如果使用了外连接（outer join）的话，那么外部表会被排在连接顺序里面的最后。一般情况下，优化器不会违背这个准则，虽然在某些环境优化器会覆写这个排序条件。类似的，如果有个子查询已经被转换成了antijoin或者semijoin，那么这个子查询里的表必须被放到其相关的外部查询模块的表的后面。但是，哈希antijoin以及hash semijoin在某些环境下也可以覆写这个排序条件。

PS: 

Anti join, 反连接，是指返回不匹配右侧的子查询的行集的连接方式。比方说列出没有员工的部门，结构是not exists或者not in

semi join, 半连接，是指返回第一个表里面至少在第二个表里能匹配到一次的数据行。比如说列出至少有一个员工的部门。和常规的连接不同的地方在于，第一个表里的行最多被返回一次。半连接使用的结构是EXISTS或者IN

优化器根据什么估算成本？

 

优化器通过预估的I/O和CPU来计算成本，其中无论是I/O或者CPU都有一些相关的度量值。比如说I/O会分单块读或者多块读。CPU则会因为不同的函数或者表达式而不同。

因此，常见的三种连接方式在成本估算上的主要影响因素也不同：

• 嵌套循环连接的成本主要是将外部表被选中的行以及内部表所有匹配的行读到内存的消耗。优化器会使用数据字典里的统计信息来预估这个成本。
• 排序合并操作的成本，很大程度上是把数据集读入内存并排序。
• hash连接的成本则主要是用在了构建hash表，以及使用另一个表的数据集来探查它。

另外，这些过程中的度量值也会受很多初始化参数或者编译时的会话设置所影响，比如说DB_FILE_MULTI_BLOCK_READ_COUNT的设置，系统统计信息等等。

连接方式

连接方式是指两个数据集之间连接的机制，在生成执行计划的过程中，数据库会基于统计信息，选择一个预估成本最低的方式。

我们所熟悉的嵌套循环连接（Nested Loops），哈希连接（Hash Join）,以及排序合并连接（Sort Merge），就是连接方式。

每个连接方式都会有两个子部分，一个是驱动表（外部表），另一个则是被驱动表（内部表），注意的是，虽然习惯性说是“表”，实际上应该说是数据集。