整个SQL语句的执行效率都靠它了...

作者：韩锋

优化器是数据库最核心的功能，也是最复杂的一部分。它负责将用户提交的SQL语句根据各种判断标准，制定出最优的执行计划，并交由执行器来最终执行。优化器算法的好坏、能力的强弱，直接决定了语句的执行效率。笔者也使用了其他诸如MySQL、PostgreSQL、SQLServer等关系型数据库。综合比较来说，Oracle的优化器是功能最强大的。学习SQL优化，从本质来讲就是学习从优化器的角度如何看待SQL，如何制定出更优的执行计划。当然，优化器本身是数据库系统中最复杂的一个部分，本书会就优化器的分类、工作原理等做简单介绍，不会深入细节。

成本是优化器（基于成本的优化器）中反映SQL语句执行代价的一个指标。优化器通过比较不同执行计划的成本，选择成本最小的作为最终的执行计划。如何理解成本、成本如何计算也就成为我们学习基于成本的优化器的关键所在。

优化器在整个SQL语句的执行过程中充当了非常重要的角色。图1是一个SQL语句从提交到最终得到结果的示意图，从中我们可以看到优化器充当的角色及其主要功能。

图1 SQL语句执行过程

Oracle的优化器也是在不断演变中的。在早期的版本中，Oracle使用一种基于规则的优化器。顾名思义，它是按照某种特定的规则来制定执行计划的。这种方式比较简单直观，但对数据库自身情况及SQL语句中对象本身的情况都没有考虑。在后期的Oracle版本中，又推出了另外一种优化器—基于成本的优化器。下面将对两种主要的优化器分别加以介绍，并对和优化器相关的数据库参数和提示进行说明。

01

基于规则的优化器

基于规则的优化器（Rule Based Optimizer，RBO）内部采用了一种规则列表，其中每一种规则代表一种执行路径并被赋予一个等级，不同的等级代表不同的优先级别。等级越高的规则越会被优先采用。Oracle会在代码里事先给各种类型的执行路径定一个等级，一共有15个等级，从等级1到等级15。Oracle会认为等级值低的执行路径的执行效率比等级值高的执行效率高。在决定目标SQL的执行计划时，如果可能的执行路径不止一条，则RBO就会从该SQL多种可能的执行路径中选择一条等级最低的执行路径来作为其执行计划。

RBO的具体规则

下面我们就来看看RBO的具体规则，如表1所示。

下面针对表1中所示的每一种规则的含义及其用法进行说明。

• Single Row by ROWID：根据ROWID，返回一条记录。这种规则发生在SQL语句的WHERE部分，指定了记录的ROWID或者使用了CURRENT OF CURSOR形式的SQL。
• Single Row by Cluster Join：根据聚簇连接，返回一条记录。这种规则发生在SQL语句中WHERE部分，包含了两表关联，且关联字段为一个聚簇，同时还存在一个过滤条件为一个表的唯一索引或主键。
• Single Row by Hash Cluster Key with Unique or Primary Key：根据哈希聚簇键，返回一条记录。这种规则发生在SQL语句的WHERE部分所包含的过滤条件中，字段是一个哈希聚簇键且这个字段为唯一或主键索引字段。
• Single Row by Unique or Primary Key：根据主键或唯一索引键值，返回一条记录。这种规则发生在SQL语句中WHERE部分，为唯一或主键所有字段的等值连接条件。
• Clustered Join：根据聚簇连接，返回一组记录。这种规则跟Path 2类似，只不过过滤条件中没有唯一限制，可以返回多条记录。
• Hash Cluster Key：根据哈希聚簇键值，返回一条记录。这种规则跟表2-1所示Path 3类似，只不过过滤条件中没有唯一限制，可以返回多条记录。
• Indexed Cluster Key：根据一个索引的聚簇键字段，返回一组记录。
• Composite Index：根据一个组合索引字段，返回一组记录。这种规则中WHERE部分需要指定组合索引字段且通过逻辑“与”运算符进行连接。
• Single-Column Indexes：根据单一索引字段，返回一组记录。
• Bounded Range Search on Indexed Columns：根据索引字段的有限范围搜索，返回一组记录。这里所说的有限范围搜索，包括字段的等值比较、大于等于和小于等于、BETWEEN...AND、LIKE等过滤条件。
• Unbounded Range Search on Indexed Columns：根据索引字段的无限范围搜索，返回一组记录。这里所说的无限范围搜索，包括字段的大于等于、小于等于过滤条件。
• Sort Merge Join：根据排序合并关联，返回一组记录。
• MAX or MIN of Indexed Column：获取一个索引字段的最大、最小值。这种规则需要遍历整个索引。
• ORDER BY on Indexed Column：根据一个索引字段，进行排序操作。
• Full Table Scan：通过全表扫描方式，获取一个结果集。

RBO在实际工作中的应用

在一般的工作场景中，很少会涉及使用RBO的情况。随着Oracle自身技术的发展，CBO优化器成为首选。只有在极个别的情况下，需要手工调整执行计划时，可采取指定优化器参数或引用相关的提示（参见后面的介绍）。需要注意的是，因为RBO技术出现比较早，很多新的技术不支持，所以在很多情况下即使手工指定使用RBO优化器，也可能会失效，Oracle仍然会使用CBO优化器。下面介绍一下失效的情况。

只要出现如下的情形之一（包括但不限于这些情形），那么即便修改了优化器模式或者使用了RULE Hint，Oracle依然不会使用RBO（而是强制使用CBO）。

• 目标SQL中涉及的对象有IOT。
• 目标SQL中涉及的对象有分区表。
• 使用了并行查询或者并行DML。
• 使用了星型连接。
• 使用了哈希连接。
• 使用了索引快速全扫描。
• 使用了函数索引。

02

基于成本的优化器

基于成本的优化器（Cost Based Optimizer， CBO）在坚持实事求是的基础上，通过对具有现实意义的诸多要素的分析和计算来完成最优路径的选择工作。这里的关键点在于对成本的理解，后面会有对成本的专门介绍。这里简单交代一句，成本可以理解为SQL执行的代价。成本越低，SQL执行的代价越小，CBO也就认为这是一个更优异的执行路径。

随着Oracle版本的不断演变，CBO优化器变得越来越智能，但需要注意的是，CBO仍然存在一些特殊情况，导致其可能产生较差的执行计划。这也是以后CBO发展需要弥补的弱点。CBO存在的问题主要有以下几个方面。

• 多列关联关系：在默认情况下，CBO认为WHERE条件中的各个字段之间是独立的，并据此计算其选择率，进而估计成本来选择执行计划。但如果各列之间有某种关系，则估算的结果与实际结果之间往往存在较大误差。可以通过动态采样或者多列统计信息的方法解决部分问题，但都不是完美的解决方案。
• SQL无关性：CBO认为SQL语句运行都是相对独立的，之间没有任何关系；但在实际运行中可能是有关联的。例如前一条语句访问某个索引，则相关数据块会被缓存到Data Buffer中，后续SQL如果也需要访问这个索引，则可以从Cache获得，这将大大减少读取成本，但这一点CBO是无法感知的。
• 直方图统计信息：一方面在12c之前，基于频率的直方图的桶的个数不能超过254，这可能导致一些精度的丢失。另一方面，对于文本型字段的直方图收集，Oracle只会提取前32字节（对于多字节字符集来说更加严重），这样获得的数据会失真，可能会导致优化器获得错误的执行计划。
• 复杂多表关联：对于复杂的多表关联，其可能的表间关联顺序组合随着表的数量增加呈几何级数增长。假设多表关联的目标SQL包含表的数量为n，则该SQL各表之间可能的连接顺序的总数就是n!。CBO在处理这个问题时，是有所取舍的。在11gR2的版本中，CBO在解析这种多表关联的目标SQL时，所考虑的各个表连接顺序的总和会受到隐含参数_OPTIMIZER_MAX_PERMUTATIONS的限制。这意味着不管目标SQL在理论上有多少种可能的连接顺序，CBO至多只会考虑其中根据_OPTIMIZER_MAX_PERMUTATIONS计算出来的有限种可能。这同时也意味着只要该目标SQL正确的执行计划不在上述有限种可能之中，则CBO一定会漏选最优的执行计划。

03

对比两种优化器

RBO和CBO的优缺点对比如表2所示。

在通常情况下，已经没有理由不选用CBO优化器了，这也是Oracle强大之所在。在极个别的情况下，也存在对CBO优化器不适合使用的情况，原因可能是BUG或者CBO设计问题。此时可以考虑使用RBO优化器，但即使是这种情况，也要严格限制特定范围，一般只在语句级使用RBO优化器。

本文摘编于《数据库高效优化：架构、规范与SQL技巧》。