07 初始 Oracle 优化器

查询优化器，简称优化器，是数据库最核心的组件之一。我们在这个系列的第一篇文章中已经给大家介绍了，优化器会参与到SQL语句的解析过程中，用来生成SQL语句的执行计划，直接决定SQL语句执行性能的优劣。

什么是执行计划

在具体介绍优化器之前，我们先普及一下执行计划的概念。

所谓执行计划，是数据库在接收到SQL语句执行请求后，由优化器根据数据库表结构、索引信息以及其他必要信息生成的一个内部数据访问策略，这个策略详细描述了数据库引擎如何将SQL语句转化为一系列的操作步骤，从数据库中获取到最终的执行结果。通过执行计划，我们可以了解到数据库引擎如何访问表、如何使用索引，多表之间如何关联以及相关的其他操作，是数据库性能优化的重要分析工具。

执行计划概览

完整的执行计划主要包括完整的SQL语句、SQL执行计划和谓词信息等几个部分。

SQL_ID  g9xaqjktdhbcd, child number 0
-------------------------------------
SELECT employee_id, last_name, first_name, department_name from
employees e, departments d WHERE e.department_id = d.department_id and
last_name like 'T%' ORDER BY last_namePlan hash value: 1219589317
----------------------------------------------------------------------------------------
| Id | Operation                    | Name        |Rows | Bytes |Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |             |     |       |    5 (100)|          |
|  1 |  NESTED LOOPS                |             |   5 |   190 |    5   (0)| 00:00:01 |
|  2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |   5 |   110 |    2   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN          | EMP_NAME_IX |   5 |       |    1   (0)| 00:00:01 |
|* 4 |   TABLE ACCESS FULL          | DEPARTMENTS |   1 |    16 |    1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------3 - access("LAST_NAME" LIKE 'T%')filter("LAST_NAME" LIKE 'T%')4 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

以上是一个基础的SQL执行计划，主要包括三个部分：

SQL语句部分：主要包含SQL_ID，子游标信息和完整的SQL语句；
详细的执行计划部分：包括每个步骤所执行的操作方式和操作对象，返回的行数、成本以及所消耗的时间等，当然这些信息是基于统计信息估算出来的；
谓词信息：这部分表示的是数据访问时所使用到的关联条件或者过滤条件。细心的同学可能看出来了，上面的执行计划 Id 3 和 Id 4 两个步骤前面有个*号，对应的谓词信息部分有两个步骤所使用到的关联和过滤信息。其中 access 表示数据的访问路径，用于定位数据；filter 表示过滤条件，对上一个操作返回的数据进行筛选。上例 Id 3 的操作对象是EMP_NAME_IX，表示通过索引EMP_NAME_IX访问EMPLOYEES，将满足条件的数据过滤出来；
其他部分：对于某些执行计划，还有一些额外的提示内容，如是否使用SQL Profile绑定了执行计划、是否使用了Feedback等等。

如何阅读执行计划

复杂SQL的执行计划也很复杂，通过执行计划来分析数据的访问逻辑并不是一件很轻松的工作，只有掌握了其中的原理才能化繁为简，找出真正的问题所在。

语句的执行顺序

阅读Oracle的执行计划，最基础的原则：执行顺序是从右向左，从上到下。按照这个原则，上面的执行计划中最右边的是 3 INDEX RANGE SCAN，因此这一步最先执行，之后是 2 TABLE ACCESS BY INDEX ROWID，又因为 2 和 4 是平级的，但是 4 在下面，所以 2 执行后接着是 4，二者的数据关联执行 1 NESTED LOOPS。

上述过程用树形图来表示，会更加直观。

在这里插入图片描述

表的访问方式

Oracle支持多种方式的单表访问，其中典型访问方式有以下几种类型：

全表扫描：数据库引擎扫描表的所有数据块来查找所需要的数据。如果查询的表很大，而返回的结果集又很小，全表扫描是一种很低效的数据获取方式，在OLTP等要求快速响应的系统中，应该尽量减少不必要的全表扫描；
索引访问：索引相当于是书本的目录，通过扫描目录获取到详细数据所在的位置，快速获取到所需要的数据。这种方式非常适合从大表中精确定位小的结果集，比全表扫描的效率高。根据索引信息和查询条件的不同，索引扫描又可以分为索引范围扫描、索引快速全扫描、索引跳跃扫描等等，这里限于篇幅的原因，不再展开细说；
ROWID访问：Oracle数据库中的每一行数据都有精确的家庭住址 – ROWID，这种方式精确定位数据效率是最高的，索引中记录的就是键值和ROWID的对应关系。但是ROWID是一个18位的数字，对于人来说直接使用ROWID并不友好，因此这种方式通常都是系统内部使用。

表的连接方法

如果SQL语句涉及到多张表，需要对多张表进行关联查询，最后返回满足要求的结果集。Oracle主要支持三种类型的连接方法：

在这里插入图片描述

嵌套循环(Nested Loop)：这种方法从外部表结果集中的每一行记录，检索内部表中满足连接谓词的所有数据，由于使用外部表的所有结果集数据和内部表进行匹配，因此外部表也被称为驱动表。这种连接方法适合于用一个小的结果集去驱动大的结果集，对于驱动表数据集较小、而被驱动表相对较大的场景，这种连接方法效率很高；
哈希连接(Hash Join)：优化器使用两个数据集中较小的一个，基于连接条件列在内存中构建哈希表，然后扫描较大的数据集，探测哈希表以找到符合连接条件的记录。这种方法适用于两个大的结果集之间的等值连接，当较小的结果集能够全部保存在内存中时，这种连接方式的效率非常高，查询成本仅限于对两个数据集进行一次读取。如果数据集不能完全放入内存，优化器会对结果集进行分区，然后逐个分区进行连接匹配；
排序合并连接(Sort Merge Join)：排序合并连接可以看作是嵌套循环的变种，假如连接中的两个结果集没有排序，优化器会对其进行排序(Sort)；之后再基于第一个结果集中的每一行，和第二个结果集进行关联匹配(Merge)，得到最终的结果集。这种方法适合于大结果集之间的非等值连接。

三种类型的连接方法各有自己的适用场景和优缺点，需要结合实际运行状态进行选择。如果是小的结果集驱动大结果集，嵌套循环会更适合；如果两个结果集都很大并且是等值连接，使用哈希连接效率更高，因为排序的成本很高；两个大结果集之间的非等值连接使用排序合并，此外，如果结果集大到无法一次性放入内存，使用排序合并的效率也会比哈希连接更高。

前面说的三种方法都是有关联条件的，如果没有关联条件则会使用笛卡尔积连接，这种方法产生的结果集是两个数据集的乘积，如果两个结果集都很大，将会消耗大量的内存和CPU资源。更重要的是由于没有关联条件，这种结果集通常都是无意义的，因此在结果之间要尽量带上关联条件，避免使用笛卡尔积关联。

优化器的分类

Oracle数据库优化器主要分为两大类型：基于规则的优化器(RBO)和基于成本的优化器(CBO)。

RBO，Rule Base Optimizer

RBO 优化器基于一系列设定好的规则来决定 SQL 语句的执行计划。

仍然以前述的 SQL 语句为例，由于在 FROM 子句中，employees 表出现在 departments 表的前面，因此固定由 employees 表来驱动 departments 表。这样做的好处是不需要额外的决策成本，缺点是需要开发人员对于优化器的规则非常了解，才能保证 SQL 语句的执行效率。此外还有一个问题是 RBO 模式下无法解决的，数据总是在不断变化的，当前适合做驱动表不一定意味着永远适合做驱动表，假如某一天 departments 表扩张的比 employees 表还多呢？(不太现实，纯属举例)

CBO，Cost Base Optimizer

因为数据的持续变化，当前的最优未来可能会变成次优。为了让优化器能够生成更准确的执行计划，现代数据库普遍采用 CBO，基于成本的优化器。这种优化器为每种操作设定了数据模型，将表行数、索引页块数量、查询条件的过滤度等值作为变量代入模型中，算出每种执行计划的成本，选择其中成本最低的作为最终执行计划。

CBO 最大的好处是考虑了数据量的变化情况，优化器总是能够根据数据的变化选择最优的执行计划。但成本的计算也是有代价的，对于涉及表和索引非常多的复杂 SQL 来说，决策成本非常的高。(具体可参见本专栏的第一篇文章 01 Oracle基础架构：一条SQL查询语句是如何执行的_在oracle中一条sql是怎么执行的,从硬件层次-CSDN博客 )

此外对于某些 SQL 会由于计算出来的成本非常接近导致执行计划频繁变化，从而影响 SQL 语句的执行性能。针对这些问题 Oracle 引入了一系列的工具，在和执行计划稳定性做着持续不懈的斗争，这个话题以后我们再慢慢展开。

优化器的设置

为了满足不同应用场景的需要，Oracle 的优化器支持多种运行模式。优化器模式受控于 OPTIMIZER_MODE 参数，支持的值有 RULE, CHOOSE, FIRST_ROWS_n(N=1、10、100、1000), FIRST_ROWS 或 ALL_ROWS。每种运行模式的含义概述如下：

RULE

表示优化器使用 RBO 来解析目标 SQL 语句，这种模式下 SQL 所涉及的对象统计信息对于执行计划的决策不会起到任何作用。虽然 Oracle 不推荐这种优化器，但是仍然有少数希望 SQL 语句执行绝对稳定的系统在使用这种模式，Ta 们不奢望 SQL 始终选择最好的执行计划，而是期望 SQL 语句能够在可接受的性能范围内稳定的执行；
CHOOSE

表示优化器解析 SQL 语句时，选择何种模式取决于相关对象是否有统计信息，只要相关对象含有统计信息，则使用 CBO，否则使用 RBO。这种模式是 9i 的默认设置；
FIRST_ROWS_n (n=1、10、100、1000)

表示优化器解析 SQL 语句时，使用 CBO 来解析目标 SQL，优化器在选择执行计划时倾向于以最快的速度返回前 n 条数据。这种模式下优化器会更多的选择 Nested Loop 方式进行表之间关联，对于数据量较大的环境容易出现性能问题，因此除非经过严格的测试，不建议轻易使用该配置；
ALL_ROWS

这种模式是 10g 及之后版本的默认值，表示优化器使用 CBO 来解析 SQL 语句，优化器在选择执行计划时倾向于最佳的吞吐量。除非有特别的需求，不建议修改默认的优化器模式。

总结

这篇文章和大家探讨了优化器和执行计划的关系，执行计划是如何工作的，又该如何阅读执行计划。当然，对于 SQL 优化来说，这些知识连入门都谈不上，仅仅是想通过一篇短文来帮助大家建立基本的概念，后续还将推出更多和优化相关的文章，有兴趣的朋友可以持续关注。日常学习和工作中有遇到优化相关的问题，也欢迎随时留言讨论！