filter执行先后问题_执行计划10：猜想

译者  林锦森 · 沃趣科技数据库技术专家

出品  沃趣科技

第9部分中我们看了一个简单的嵌套循环连接的例子，执行计划中基本解释数字的方法也因为优化器使用的算法对于成本的计算与运行时执行路径的不相符而出现问题。在本部分中，我们将查看一个示例，其中有些数字是由猜测生成的，有些是由计划中不可见的估计生成的。

Example

我创建了一张自定义的emp表，包括6个部门总共20000个员工，我打算使用SQL语句列出每个部门里超过平均工资的员工。下面是SQL语句以及执行计划。

explain plan for
select
outer.*
from
emp outer
where
outer.sal > (
select /*+ no_unnest */
avg(inner.sal)
  from
emp inner
where
inner.dept_no = outer.dept_no
)
;

select * from table(dbms_xplan.display);
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   167 | 12024 |   334  (12)| 00:00:02 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP  | 20000 |  1406K|    49  (15)| 00:00:01 |
|   3 |   SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  4 |    TABLE ACCESS FULL| EMP  |  3333 | 26664 |    48  (13)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */ AVG("INNER"."SAL")
               FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
   4 - filter("INNER"."DEPT_NO"=:B1)

你注意到了我在语句中使用了/*+no_unnest*/ hint，这不是一个高效率的执行路径，我之所以使用它，是因为它将生成一个特定的执行计划，该计划将演示我想讨论的几个要点。

执行计划的操作顺序是2，4，3，1，0：第2行对EMP表做全表扫描，并把结果集传递给第1行，原则上第1行会对传递来的结果集中的每一行进行调用一次第3行的操作；第3行会调用第4行做表扫描，它会提供每个部门里所有员工的工资，第3行根据结果集计算平均工资并将包含单行的结果集传递给第1行，第1行比较工资的平均值与当前行，如果大于则会输出到客户端。

Filling the gaps

你马上可以察觉到执行计划中存在一些空缺。Filter操作并没有任何数字计量方面的信息，但是这个例子中我们也可以接受，因为它是第0行select操作唯一的子操作，所以它的值应该跟第0行的值相匹配。

但是第3行的sort aggregate操作没有成本，这样我们就不知道优化器是如何为第1行计算成本的。因为sort aggregate操作仅仅是运行聚合运算，所以我们可以假设它的增量成本是很小的(事实上，这个查询的10053跟踪文件似乎表明，优化器甚至不需要做任何与排序相关的计算，这可能解释了为什么这一行是空的)。所以我们假设第3行的成本跟它的唯一子操作的成本是一样的，也是48。

Guessing execution counts

我们怎么证明第1行的cost是334？在允许四舍五入的误差情况下(10053跟踪文件报告47.61和48.75时分别为48，49)，简单的计算为:6*48+49=337，这跟结果比较接近，我们也可以推断优化器假设这个子查询会执行6次。

注意：第2行跟第4行中同样是对emp表进行扫描，为什么会有不一样的成本？因为它们处理不同的列，第2行处理的列多于第4行，会导致消耗更多的CPU。 对6次执行的假设将我们带进了一个解释执行计划很重要的阶段—优化器也不知道子查询可能会执行几次。选择6到20000之间任意的数字，我可以构造一个数据集(在6个部门的基础上)，使查询运行那么多次。实际上，在这个例子中，子查询很可能就会执行6次，但是通过一些额外的实验，生成随机的部门代码，你会最终会得到执行的次数是几千次的例子。优化器选择6这个倍数，是因为它通过对象的信息知道表里有6个部门，计算的理由是一种称为标量子查询缓存的机制。

所以在评估执行计划是需要注意这点：执行计划是用来告诉你Oracle在运行时会执行哪些步骤，它并不会告诉你这些步骤执行了多少次，这些执行的次数对于查询的执行有着重要的影响。

Guessing Volume

在子查询中引入/*+push_shbq*/ hint，我们可以使用相同的例子来证明评估执行计划时另一个很重要的点。下面的该执行计划----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  1000 | 72000 |    96  (13)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL  | EMP  |  1000 | 72000 |    48  (13)| 00:00:01 |
|   2 |   SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  3 |    TABLE ACCESS FULL| EMP  |  3333 | 26664 |    48  (13)| 00:00:01 |----------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------1 - filter("OUTER"."SAL"> (SELECT /*+ PUSH_SUBQ NO_UNNEST */AVG("INNER"."SAL") FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))3 - filter("INNER"."DEPT_NO"=:B1)

推入子查询的目的是想让它尽可能早的执行，但在我们的情况下不起作用，因为只有一个特定的时刻，子查询才可以运行，但即使如此，它将导致优化器采取不同的路线执行语句。在运行时，我们的查询所做的工作量不会改变，而且查询返回的数据量也不会改变，但是我们来看看这个执行计划中的数据：优化器预测会获得1000行而不是167行，总成本是96而不是344.通过cost值，我们可以推断优化器会预估将执行1次子查询而不是6次(我们同样也能看到，第1行中全表扫描预测行数为1000行而不是20000行，但这是优化器在执行子查询之后对行数的预测，而之前的计划是在执行子查询之前预测行数)。

具体的细节不是最重要的，重要的是我们可以很清楚的看到，不同的优化器代码路径会对cost以及数据集大小产生不同的预测。其中至少会有一个预测是错误的。实际数据量的变化会导致处理数据所需的实际工作量的变化，而且我们可以很容易地找到同一查询的不同执行计划预测不同数据集数量的情况，因此我们如何能够确信任何预测都将与最终发生的工作相一致？这个是第二个我们在评估执行计划时需要注意的点—对于基数的预估有很多是基于猜测的，基数对于执行计划的效率有着很重要的作用。优化器会告诉你将要执行扫描，但是只会猜测它将获取的数据集的大小，以及处理这些数据集的成本。

Conclusion

只看一个(精心设计的)例子，我们就可以看到优化器在多大程度上可以产生甚至不自洽的预测。这应该被视为一个警告，你在预测的执行计划中看到的基数(行)、成本和时间不应该被视为可靠的预测。

你从优化器的预测中得到的是一个用于操作查询的机制语句，以及每次执行操作时将生成的数据量的一些信息。如果你很了解你的数据，你就会知道对数据集数量的个别估计有多精确，每个操作可能执行多少次；正是这种优化器的猜测与你对数据的理解的比较，让你知道预测和现实之间的变化会出现在哪里。 在接下来的几部分中，我们将研究如何将预测与运行时所做的工作进行比较，并展示如何帮助我们找到提高查询性能的方法。

| 译者简介

林锦森·沃趣科技数据库技术专家

沃趣科技数据库工程师，多年从事Oracle数据库，较丰富的故障处理、性能调优、数据迁移及备份恢复经验。

相关链接

MySQL 一个让你怀疑人生的hang死现象

组复制常规操作-事务一致性保证 | 全方位认识 MySQL 8.0 Group Replication

组复制常规操作-在线配置组 | 全方位认识 MySQL 8.0 Group Replication

组复制监控 | 全方位认识 MySQL 8.0 Group Replication

组复制安装部署 | 全方位认识 MySQL 8.0 Group Replication

组复制背景 | 全方位认识 MySQL 8.0 Group Replication

再述mysqldump时域问题

揭秘 MySQL 主从环境中大事务的传奇事迹

MySQL 执行DDL语句 hang住了怎么办?

手把手教你认识OPTIMIZER_TRACE

MySQL行级别并行复制能并行应用多少个binlog group？

binlog server还是不可靠吗？

MySQL binlog基于时间点恢复数据失败是什么鬼？

MySQL高可用工具Orchestrator系列六：Orchestrator/raft一致性集群

MySQL高可用工具Orchestrator系列五：raft多节点模式安装

MySQL高可用工具Orchestrator系列四：拓扑恢复

MySQL高可用工具Orchestrator系列三：探测机制

select into outfile问题一则

开源监控系统Prometheus的前世今生

prometheus监控多个MySQL实例

prometheus配置MySQL邮件报警

MySQL问题两则

Kubernetes scheduler学习笔记

直方图系列1

执行计划-9：多倍操作

执行计划-8：成本、时间等

执行计划-7：查询块和内联视图

执行计划-6：推入子查询

执行计划-5：第一个子操作的变化

大数据量删除的思考(四)

大数据量删除的思考(三)

日志信息记录表|全方位认识 mysql 系统库

复制信息记录表|全方位认识 mysql 系统库

时区信息记录表|全方位认识 mysql 系统库

Oracle RAC Cache Fusion系列十八：Oracle RAC Statisticsand Wait Events

Oracle RAC Cache Fusion 系列十七：Oracle RAC DRM

Oracle RAC CacheFusion 系列十六：Oracle RAC CurrentBlock Server

更多干货，欢迎来撩~