查询优化器概念：关于优化器组件

Yunjie Ge

发布于 2022-04-24 09:39:14

2.3K0

文章被收录于专栏：数据库与编程数据库与编程

本篇是如何调优 Oracle SQL系列文章第五篇：查询优化器概念之关于优化器组件。

优化器包含：查询转换器（Query Transformer）、估算器（Estimator）和执行计划生成器（Plan Generator）三个组件。

一组查询块表示已分析的查询，它是优化器的输入。下表描述了优化器操作。

序号	操作	描述
1	Query Transformer	优化程序确定更改查询形式是否有帮助，以便优化程序可以生成更好的执行计划。
2	Estimator	优化程序根据数据字典中的统计信息估算每个计划的成本。
3	Plan Generator	优化器比较计划的成本，并选择最低成本计划（称为执行计划）传递给行源生成器。

1、查询转换器（Query Transformer）

对于某些语句，查询转换器确定将原始SQL语句重写为具有较低成本的语义等效SQL语句是否更有利。

当存在可行的替代方案时，数据库将单独计算替代方案的成本，并选择成本最低的替代方案。下图显示了查询转换器将使用OR的输入查询重写为使用UNION ALL的输出查询。

2、估算器（Estimator）

估算器用于确定给定执行计划的总体成本。

估算器使用三种不同的度量来确定成本：

Selectivity（选择性） 查询选择的行集中的行百分比，0表示没有行，1表示所有行。选择性与查询谓词相关联，例如WHERE last_name LIKE'A％'或谓词组合。当值接近1时，谓词变得更具选择性，因为选择性值接近0且选择性更低（或更不具有选择性）。 注：Selectivity在执行计划中不可见的内部计算。
Cardinality（基数） 基数是执行计划中每个操作返回的行数。此输入对于获得最佳计划至关重要，对所有成本函数都是通用的。估算器可以从DBMS_STATS收集的表统计信息中导出基数，或者在考虑谓词（过滤器，连接等），DISTINCT或GROUP BY操作等的影响后派生基数。执行计划中的Rows列显示估计的基数。
Cost（成本） 该度量表示使用的工作单元或资源。查询优化器使用磁盘I/O，CPU使用率和内存使用量作为工作单元。

如下图所示，如果统计数据可用，那么估算器使用它们来计算度量值。统计数据提高了措施的准确性。

对于示例4-1中显示的查询，估算器使用选择性，估计基数（总返回10行）和成本来生成其总成本估计值3：

--------------------------------------------------------------------------------
| Id| Operation                    |Name             |Rows|Bytes|Cost %CPU|Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                 | 10| 250| 3 (0)| 00:00:01|
| 1 |  NESTED LOOPS                |                 |   |    |      |         |
| 2 |   NESTED LOOPS               |                 | 10| 250| 3 (0)| 00:00:01|
|*3 |    TABLE ACCESS FULL         |DEPARTMENTS      |  1|   7| 2 (0)| 00:00:01|
|*4 |    INDEX RANGE SCAN          |EMP_DEPARTMENT_IX| 10|    | 0 (0)| 00:00:01|
| 5 |   TABLE ACCESS BY INDEX ROWID|EMPLOYEES        | 10| 180| 1 (0)| 00:00:01|
--------------------------------------------------------------------------------

2.1 Selectivity（选择性）

选择性表示行集中的一小部分行。

行集可以是基表、视图或连接的结果。选择性与查询谓词相关联，例如：last_name = 'Smith'，或谓词的组合，例如：last_name = 'Smith' AND job_id = 'SH_CLERK'。

谓词过滤行集中的特定行数。因此，谓词的选择性表示通过谓词测试的行数。选择性范围从0.0到1.0。选择性0.0表示没有从行集中选择行，而选择性1.0表示选择所有行。当值接近0.0时，谓词的选择性会增强，而当值接近1.0时，谓词的选择性会减弱(或变得更加非选择性)。

优化器根据统计数据是否可用来估计选择性:

统计数据不可用 根据 OPTIMIZER_DYNAMIC_SAMPLING 初始化参数的值，优化器要么使用动态统计信息，要么使用内部默认值。数据库根据谓词类型使用不同的内部默认值。例如，相等谓词（last_name = 'Smith'）的内部缺省值低于范围谓词（last_name > 'Smith'），因为相等谓词预期返回的行数更少。
统计数据可用 当统计数据可用时，估算器使用它们来估计选择性。假设有150个不同的员工姓。对于相等谓词 last_name = 'Smith'，选择性是 last_name 非重复值n个数，在本例中是.006，因为查询选择包含150个非重复值中的1个的行。如果 last_name 列上存在直方图，那么估算器将使用直方图而不是非重复值的数量。柱状图捕捉了列中非重复值的分布，因此可以得到更好的选择性估计，特别是对于具有数据倾斜的列。

2.2 Cardinality（基数）

基数是执行计划中每个操作返回的行数。

例如，如果优化器估计全表扫描返回的行数为100，则此操作的基数估计为100。基数估计出现在执行计划的Rows列中。

优化器根据一组复杂的公式确定每个操作的基数，这些公式同时使用表和列级统计信息或动态统计信息作为输入。当单表查询中出现一个没有直方图的等式谓词时，优化器使用最简单的公式之一。在本例中，优化器假设一个统一的分布，并通过将表中的总行数除以WHERE子句谓词中使用的列中不同值的数量来计算查询的基数。

例如，user hr查询employees表如下:

SELECT first_name, last_name
FROM   employees
WHERE  salary='10200';

employees表包含107行。目前的数据库统计数字表明，薪金栏中非重复值的数目为58。因此，优化器使用公式107/58=1.84估计结果集的基数为2。

基数估计必须尽可能准确，因为它们影响执行计划的所有方面。当优化器确定连接的成本时，基数非常重要。例如，在employee和departments表的嵌套循环连接中，employee中的行数决定了数据库必须探测departments表的频率。基数对于确定排序的成本也很重要。

2.3 Cost（成本）

优化器成本模型负责预测查询将使用的机器资源。

成本是一个内部数字度量，表示计划的估计资源使用量。成本是特定于优化器环境中的查询的。为了估计成本，优化器会考虑以下因素:

系统资源，包括估计的 I/O、CPU 和内存
估计返回的行数(基数)
初始数据集的大小
数据分布
访问结构

注：成本是优化器用于比较同一查询的不同计划的内部度量。您不能调整或更改成本。

执行时间是成本的函数，但成本并不直接等于时间。例如，如果查询A的计划成本低于查询B的计划，则可能出现以下结果:

A的执行速度比B快。
A的执行速度比B慢。
A执行的时间与B相同。

因此，您不能比较不同查询的成本。此外，您不能比较使用不同优化器模式的语义等价查询的成本。

执行计划显示了整个计划的成本(在第0行中表示)和每个单独的操作。例如，下面的计划显示了总成本为14。

EXPLAINED SQL STATEMENT:
------------------------
SELECT prod_category, AVG(amount_sold) FROM   sales s, products p WHERE
 p.prod_id = s.prod_id GROUP BY prod_category
 
Plan hash value: 4073170114
 
----------------------------------------------------------------------
| Id  | Operation                | Name                 | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |    14 (100)|
|   1 |  HASH GROUP BY           |                      |    14  (22)|
|   2 |   HASH JOIN              |                      |    13  (16)|
|   3 |    VIEW                  | index$_join$_002     |     7  (15)|
|   4 |     HASH JOIN            |                      |            |
|   5 |      INDEX FAST FULL SCAN| PRODUCTS_PK          |     4   (0)|
|   6 |      INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |     4   (0)|
|   7 |    PARTITION RANGE ALL   |                      |     5   (0)|
|   8 |     TABLE ACCESS FULL    | SALES                |     5   (0)|
----------------------------------------------------------------------

访问路径决定从基表获取数据所需的工作单元数。为了确定总体计划成本，优化器为每个访问路径分配一个成本:

表扫描或快速全索引扫描 在表扫描或快速全索引扫描期间，数据库在一个I/O中从磁盘读取多个块。扫描的成本取决于要扫描的块数和多块读取计数值。
索引扫描 索引扫描的成本取决于B-tree中的级别、要扫描的索引叶块的数量以及使用索引键中的rowid获取的行数。使用rowids获取行的成本取决于索引聚类因子。

连接成本表示要连接的两个行集的单独访问成本与连接操作的成本的组合。

3、执行计划生成器（Plan Generator）

执行计划生成器通过尝试不同的访问路径、连接方法和连接顺序来探索查询块的各种计划。

许多计划是可能的，因为数据库可以使用各种组合来产生相同的结果。优化器选择成本最低的计划。

下图显示优化器测试输入查询的不同计划。

下面的代码片段来自优化器跟踪文件，显示了优化器执行的一些计算:

GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  DEPARTMENTS[D]#0  EMPLOYEES[E]#1
 
***************
Now joining: EMPLOYEES[E]#1
***************
NL Join
  Outer table: Card: 27.00  Cost: 2.01  Resp: 2.01  Degree: 1  Bytes: 16
Access path analysis for EMPLOYEES
. . .
  Best NL cost: 13.17
. . .
SM Join
  SM cost: 6.08
     resc: 6.08 resc_io: 4.00 resc_cpu: 2501688
     resp: 6.08 resp_io: 4.00 resp_cpu: 2501688
. . .
SM Join (with index on outer)
  Access Path: index (FullScan)
. . .
HA Join
  HA cost: 4.57
     resc: 4.57 resc_io: 4.00 resc_cpu: 678154
     resp: 4.57 resp_io: 4.00 resp_cpu: 678154
Best:: JoinMethod: Hash
       Cost: 4.57  Degree: 1  Resp: 4.57  Card: 106.00 Bytes: 27
. . .

***********************
Join order[2]:  EMPLOYEES[E]#1  DEPARTMENTS[D]#0
. . .
 
***************
Now joining: DEPARTMENTS[D]#0
***************
. . .
HA Join
  HA cost: 4.58
     resc: 4.58 resc_io: 4.00 resc_cpu: 690054
     resp: 4.58 resp_io: 4.00 resp_cpu: 690054
Join order aborted: cost > best plan cost
***********************

跟踪文件显示优化器首先尝试将departments表作为连接中的外部表。优化器计算三种不同连接方法的成本：嵌套循环连接(NL)、排序合并(SM)和散列连接(HA)。优化器选择哈希连接作为最有效的方法：

Best:: JoinMethod: Hash
       Cost: 4.57  Degree: 1  Resp: 4.57  Card: 106.00 Bytes: 27

然后，优化器尝试不同的连接顺序，使用employees作为外部表。此连接顺序的成本比前一个连接顺序高，因此被放弃。

优化程序使用内部截止值来减少在查找成本最低计划时尝试的计划数量。截止值基于当前最佳计划的成本。如果当前最佳成本很高，那么优化器会探索替代计划以找到更低成本的计划。如果当前最佳成本很小，那么优化器会迅速结束搜索，因为进一步的成本改善并不重要。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-08-22，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql