query optimizer 简介-4

最新推荐文章于 2022-03-07 10:11:28 发布

congshi8042

最新推荐文章于 2022-03-07 10:11:28 发布

阅读量148

点赞数

继续[@more@]

选择访问路径

对于SQL中涉及的表, 优化器选择一个或者多个读取数据的访问路径.

访问路径是Oracle获取数据的方法, 这些方法其实不是很多, 包括:

Ø Full Table Scans

Ø Rowid Scans

Ø Index Scans (索引扫描包括多种不同的类型)

Ø Cluster Access

Ø Hash Access

Ø Sample Table Scans

为了选择合适的访问路径, 优化器首先现确定哪些访问路径是可用的, 然后根据所有的可用路径组成多个执行计划, 针对每个执行计划计算cost并最终会选用cost最小的执行计划. 在这个过程中, 我们可以通过给SQL添加hint来影响优化器的决策, 而另一个对优化器影响比较大的因素则是统计信息, 不正确的统计信息往往是SQL性能底下的罪恶之源.

了解各种访问路径的特点和使用情况对优化SQL是很有用的, 这部分内容比较多, 我们会放到单独的部分介绍.

1.2.选择连接顺序

在第4步的介绍中, 我漏掉了对最终执行计划影响比较大的因素,就是表的连接方式和连接顺序.

表于表之间可以以多种方式进行连接操作, 每种不同的方式都采用不同的算法, 因此cost也不一样.

另外, 因为同时只能给2张表进行连接操作, 所以当我们需要对2张以上表进行连接操作的时候, 就需要先对两张表做连接, 得出的中间结果再和剩下的表当中的一个进行, 以此类推. 这时候, 我们就必需认真考虑这些表的连接顺序, 不同的连接顺序会导致不同的cost.

对于连接方式, Oracle官方文档中列出的包括:

Ø Nested Loop Joins

Ø Hash Joins

Ø Sort Merge Joins

Ø Cartesian Joins

Ø Outer Joins

另外, 不严谨的说, 应该还有两种连接: SEMI-JOIN和ANTI-JOIN

假如我们有两张表t1, t2

Ø Semi-join的意思是, 从t1返回在t2中存在的行. 逻辑上, Semi-join和SQL里的exist表示的意思差不多. 不过由于in往往可以转化为exists，所有当你使用in的时候oracle也可以进行semi-join

Ø Anti-join是Semi-join的反操作, 是从t1返回t2不存在的行, 逻辑上和not exist差不多. 请注意，not exists和not in是不等价的，我们将在下一部分介绍这一点。

在oracle的执行计划里是没有叫做Semi-join或着anti-join的连接操作, 一般他们是和其他连接方式结合在一起的, 如hash join semi.

当有多于2张表进行连接的时候, 优化器还需要考虑不同连接顺序. 一般来说, 越能有效减少结果集行数的连接应该越放在前面, 应为这样会减少后面的连接的cost.

这里有一个问题, 如果我们需要连接的表的数量比较多的时候, 连接顺序会达到一个可怕的数量级,

如果n是我们需要连接的表的数量, 我们就会有(2(n-1))! / (n-1)! 个不同的连接顺序.

当n=3 这个数是12; 当n=5, 这个数是1680; 当n=7, 这个数是665280; 当n=10, 这个数是大于176亿!

**有种说法认为公式应该是n的阶乘(n!), 我觉得这种说法应该是混淆了(A-B)-C和C-(A-B)的区别. (表示连接的符号找不到, 用横线代替一下)

这种情况下, 再多的资源也不够优化器挥霍. 针对这种情况, Oracle存在多个机制来帮助优化器,

来看看官方文档的一些描述

(摘自oracle官方文档—Oracle Database Performance Tuning Guide 10g Release2):

Ø The optimizer first determines whether joining two or more tables definitely results in a row source containing at most one row. The optimizer recognizes such situations based on UNIQUE and PRIMARY KEY constraints on the tables. If such a situation exists, then the optimizer places these tables first in the join order. The optimizer then optimizes the join of the emaining set of tables.

如果优化器通过唯一或主键约束发现两个表的连接只会返回一行, 那么这两个表将会先进行join.

Ø For join statements with outer join conditions, the table with the outer join operator must come after the other table in the condition in the join order. The optimizer does not consider join orders that violate this rule. Similarly, when a subquery has been converted into an antijoin or semijoin, the tables from the subquery must come after those tables in the outer query block to which they were connected or correlated. However, hash antijoins and semijoins are able to override this ordering condition in certain circumstances.

如果Join涉及了外连接, 那么做外连接的表(或者说保留表)将会放到连接顺序的最后.

类似的, 如果子查询被转化成了antijoin或semijoin, 那么在连接顺序上, 子查询里面的表将会放在父查询涉及的表的后面. 但是, 在有些情况下, hash antijoin和hash semijoin可能违反这一规则.

Ø The plan generator uses an internal cutoff to reduce the number of plans it tries when finding the one with the lowest cost. The cutoff is based on the cost of the current best plan. If the current best cost is large, then the plan generator tries harder (in other words, explores more alternate plans) to find a better plan with lower cost. If the current best cost is small, then the plan generator ends the search swiftly, because further cost improvement will not be significant.

The cutoff works well if the plan generator starts with an initial join order that produces a plan with cost close to optimal. Finding a good initial join order is a difficult problem.

Plan generator(优化器个三个组件之一, 负责生成执行计划, 其他两个分别是负责语句转化的Query Transformer和负责估算cost的estimator) 使用内部的中止机制来减少它计算cost的计划的个数. 这种中止机制基于当前得到的最好的计划的cost, 如果当前最好计划的cost很大, 那么Plan generator就继续寻找更好的计划, 如果认为当前最好的计划的cost已经足够小了, 那么Plan Generator也就没必要继续尝试下去了.

Oracle内部有一个内置的函数来判断当前最好计划的cost是否足够小, 这个函数考虑当前已经考虑了的连接的数目以及非单行表的数目来算出一个值, 作为Plan generator的评判标准. 随着Plan generator尝试的方案越多, 这个内置函数得出的值会增大.

此外, Oracle还通过一些隐含参数来硬性的限制优化器将会考虑的计划的最大个数:

_optimizer_max_permutations (default=2000), 限制了优化器将会尝试的表排列顺序的上限.
_optimizer_search_limit (default=5), 按照这个默认值, 如果有5个表要做连接, 那么优化器是允许尝试所有的可能的(1680种可能), 当需要进行连接的表的个数超过5的时候, 优化器不会尝试所有的可能.