mysql 执行计划代价_(db)数据库语句执行(5)-物理查询的代价估计与选择

最新推荐文章于 2024-09-12 23:30:00 发布

金融先生-Frank

最新推荐文章于 2024-09-12 23:30:00 发布

阅读量686

点赞数

文章标签： mysql 执行计划代价

本文链接：https://blog.csdn.net/weixin_35639680/article/details/113162342

版权

在计算出逻辑计划并优化后，我们将派生出多个不同的物理执行计划。这些不同的物理计划会产生不同的执行代价，因此，一般在执行之前会进行代价估计，然后执行引擎将选择代价最小的物理计划。根据代价，将确定从逻辑计划到物理计划的选择，包括：对于可结合与可分配的运算的次序与分组，如连接，并，交；

逻辑计划中每一个运算符的算法，如，使用嵌套循环链接或散列连接或索引连接；

其他运算符，如扫描，排序等；

参数从一个运算符传送到下一个运算符的方式，如将中间结果保存到硬盘还是在内存缓冲区；

本文首先将介绍执行计划的估计方法，然后将介绍基于代价的选择。

1.执行计划的估计

本章节将介绍执行计划中各种运算符的估计方法。

1.1 投影运算大小的估计

通常，投影时元组会消除成分，是减小关系的；对于投影产生新成分的，可能会增加关系。但是都不增加元组的数量。

1.2 选择运算大小的估计对于S = σA=c(R)，估算T(S) = T(R)/V(R,A)，即T的元组数量除于T的A列的不同值的数量；

对于S = σa< c(R)，估算T(S) = T(R)/3；

对于S = σ C1 or C2(R)，如果R有n个元组，满足C1和C2的元组分别为m1和m2，则假定S的个数为： n(1-(1-m1/n)(1-m2/n))。

1.3 连接运算大小的估计

对于连接 R(X,Y) JOIN S(Y,Z)：如果两个关系有不相交的Y值，那么连接后元组个数为0；

如果Y是S的键，且为R的外键，那么连接后元组个数与R相同；

如果S和R都具有相同的Y值，那么连接后的元组个数为T(R)T(S)；

在实际中，一般用下面公式计算连接的元组个数：T(R JOIN S) = T(R)T(S)/max(V(R,Y),V(S,Y))

1.4 多连接属性的自然连接估计

对于多连接属性，与上面类似，采用下面方法进行估算：即通过计算T(R)T(S)，然后除了Y中所有公共属性y中，V(R,y)和V(S,y)的较大值；

1.5 多个关系的连接

对于多个连接关系的连接，S=R1 JOIN R2 JOIN R3 JOIN R4 ... JOIN RN；

那么可以通过以下方式进行计算：首先计算所有关系元组数量的积，然后对于每个出现超过两次的属性A，除于除了V(R,A)最小值以外的其他值；

1.6 其他运算大小的估计

对于其他的运算，则可以利用下面方式进行计算：

1) 并

采用两数之和的一半；

2) 交

较小者的一半；

3) 差

对于R-S，取T(R)-T(S)/2;

4) 消除重复

取T(R)/2与所有属性V(R, a)之积较小的一个；

5) 分组与聚集

取T(R)/2与所有属性V(R, a)之积较小的一个；

2. 执行计划的选择

在以上的基础上，本章节将介绍基于以上估计的计划选择方法，首先介绍估计值的一些方法。

2.1 估计值的获取与记录

对于用于估计的统计量，可以采用定期进行计算。这是因为，这些统计量一半不会突变；且如果进行实时统计，那么会影响性能，浪费计算资源。

常用于记录每个属性统计量可以采用直方图进行记录。常见的直方图有：等宽，即选定宽度，高度表示在这个范围内出现的次数；

等高，公共的百分点，即比最小值多p，2p...等对应的属性值；

最频值，记录最经常出现的值和频数，对于其他不经常出现的，可以用总和进行记录；

根据这些统计量，可以更加精确地计算估计值。

2.2 减少逻辑查询计划代价

启发式逻辑优化是利用启发式(如下推选择，去重下推)对逻辑算子进行优化，然后对优化后的树进行代价估计，如果代价有优化，那么则进行对应的优化。

2.3 物理计划估算方法

在将逻辑计划转成物理计划时存在多种组合方法。最简单的方法就是***穷尽法***，即穷尽所有的物理计划组合，找到代价最小的物理计划。

除此之外，还存在其他求解物理计划的方法，大致上可以分成两类：自顶向下：从逻辑树的树根开始向下进行，计算参数的所有可能组合的代价，选择最优的一个；

自底向上：对于每一次字表达式，计算子表达式的所有方法的代价，并按所有可能方式与父节点运算符相结合。

2.4 自底向上的选择方法

这里介绍将逻辑计划转成物理计划的自底向上的算法。

2.5 启发式选择

定义一些启发式的选择规则，例如：如果逻辑计划中选择A=c属性，且关系在A上有索引，则执行一个索引查找；

如果逻辑计划中有选择A=c与其他条件，可以先执行一个索引查找，然后用filter进行过滤；

如果连接的一个参数在连接属性上有索引，那么采用索引连接；

如果一个参数时排序的，那么采用排序连接比散列连接好；

当计算三个/多个关系的并或交时，先执行最小关系；

2.6 分支界定计划枚举

类似于决策树中的剪枝法。令一个物理计划的代价为C，然后考虑这个子查询的的其他计划时，然后去除那些代价大于C的子查询计划。如果构造出代价小于C的完整计划，则用该计划代替原有计划。该方法可以判定何时中断搜索，即已经搜索的到的物理计划已经超过了C，那么就终止搜索。

2.7 爬山法

根据启发式选定的物理计划开始，然后不断搜索。即通过对物理计划的简单修改，如果找到更优的，则替换原有的计划。也可以通过结合律和交换律对连接进行排序，找到相邻的计划。

2.8 动态规划

对于每一个子表达式，只保留最小代价的计划，不断向上进行归约。

2.9 Selinger风格优化

对动态规划进行修改，即每一个子表达式不仅保留最优的计划，还保留较优代价且可能对上层表达式有用的计划。这类物理计划的结果按以下属性进行排序：在上层结点排序运算符中说明的属性；

分组运算符的分组属性；

连接运算的连接属性；

3. 连接顺序的选择

在连接中，常将连接的左右参数进行区分，例如：嵌套循环链接中，认为左参数是外部循环关系；

索引连接中，认为右参数是有索引的；

当有多个关系进行连接时，不同的连接顺序具有不同的代价，也会构成不同的连接树。例如，对于R，S，T，U进行连接时，可以构成 4! = 24 颗不同的连接树。

其中，如下面三图所示。最上边的图中所有右子结点均为叶子结点，称为左深连接树；最下边的所有左子结点均为叶子结点的称为右深连接树，其他的称为浓密树，如中间。

一般情况下，我们会选用左深树，这是因为：对于给定树叶的可能左深树的数量是很大(n!)，但不会比所有树的数量那样大[T(n) * n]，其中T(n)为树结构的数量，即T(1) = 1, T (n) = Sum(T(i) * T(n-i)), i为1到n；

左深树可以和一般的连接算法很好地交互，尤其是嵌套循环链接和一趟连接。左深树相对于其他树更加有效。

3.1 动态规划选择连接顺序和分组

动态规划的思想如下：基础：对于单一关系R的表象包含R的大小，代价为0，和R的表达式；对于一对关系{R1，R2}，代价为0(因为没有中间结果)，本身的估计可以用前面提到的方式进行估计(即R1和R2的乘积除于每一公共属性中较大的值)；

归纳：对大小大于2的子集建立表格，直至得到大小为n的子集的表项；计算时基于更小的子集进行计算。

下面用一个例子进行简单说明：假如R，S，T，U关系的大小为1000；每个属性上不同值的个数如下所示。

1)基础步骤：计算每个独立关系的代价的记录数。

2)根据1)可以直接计算两个关系的代价和记录数。例如，对于{R,S}，大小为 1000 * 1000 / MAX(V(R,b),V(S,b)) = 1000000/200 = 5000，其他也可以类似地计算出。

3)根据2)中的结果和每一个关系的数据，计算三个关系的连接代价

例如{R,S,T}可有三种计算方式：{R, S} + T; {R, T} + S; {S, T} + R;对于{R, S} + T：5000 * 1000 / MAX(V(S,c), V(T,c)) = 5000000/500 = 10000，代价为：5000；

对于{R, T} + S：1000000 * 1000 / (MAX(V(R,b),V(S,b)) * MAX(V(S,c),V(T,c))) = 1000000000 / (200 * 500) = 10000; 代价为：1000000；

对于{S, T} + R：2000 * 1000 / MAX(V(R,b), V(S,b)) = 2000000 / 200 = 10000; 代价为：2000；

所以对于{R,S,T}，最终选择{S, T} + R的方式，即先连接S和T，然后再连接R；其他的三个关系的子集也可以类似地计算出来。

4)根据2)和3)的结果，类似地计算四个关系的连接代价。例如对于 ((T U) S) R，代价为：2000+1000 = 3000，即历史代价总和。

以上的动态规划时从理论上进行计算的，而没有考虑到实际代价。例如，连接R(a,b) JOIN S(b,c)如果R只有一个元组，且S在b属性上有索引，那么连接是基本上不耗时的。但是如果两个关系的连接属性没有索引，那么就需要扫表，这样的性能是很差的。也就是说动态规划计算出来的结果可能会跟实际情况相差较大(过大或过小)。为了解决这个问题，在实际中可以将磁盘IO作为动态规划的代价度量。

对动态规划进行修改，即Selinger风格优化，每一次不仅仅返回最优连接方式，还返回一些比较重要的最小代价的连接方式和对应的代价。

3.2 贪心算法选择连接顺序和分组

动态规划对于关系数量较小的是有效的，但是对于具有较多关系进行连接时，动态规划也会消耗一些时候，有时候偏向于将时间花在实际运算上而不是在搜索组合的估算上。因此，贪心法也是一种常用的方法。例如，对于左深树的贪心算法：基础：以估算连接大小的最小关系开始；

归约：在没有包含进来的关系中选择与当前树进行连接能得到估计大小最小的关系，被选中的关系作为右参数。然后将新树不断进行归约，最终得到完整的左深树。

4. 其他运算的计划选择

本章节将介绍一些其他运算操作的计划选择，包括“选择运算”，连接方法等。

4.1 选择运算的选取

对于选择运算，需要为每一个运算符精选算法，假如，有多个选择条件， A AND B AND C，且三个条件均在索引上，那么这时候需要对这三个条件分别进行代价估计，选择最优的方法进行扫描物理操作；然后对扫描的结果进行过滤(Filter)。并假定在计算代价估计时，对于表R，B(R),T(R), V(R，a)分别为块数量，记录数量和关系R在属性a上不同值的数量。

那么，表扫描算法与一个过滤器结合的代价为：R聚集，为B(R)；

R非聚集，为T(R)；

如果选择一个等值选项，如c=20，先进行索引扫描，然后执行过滤器：索引是聚集的，则B(R)/V(R，a);

索引非聚集的，则T(R)/V(R，a);

如果选择的是一个非等值选项，如c>10，则有；索引聚集时，则B(R)/3；

索引非聚集时，则T(R)/3；

4.2 连接方法的选取

连接方法可以有：一趟连接，嵌套循环连接，排序连接，索引连接，散列连接；各个方法的适用场景如下：对于数量较小的，可以采用一趟连接，缓冲区管理器为其分配足够的缓冲区；

如果不能保证为左参数分配足够的缓冲区，但也不会被分解成太多片，那么可以用嵌套循环连接；

如果一个/两个参数已经在连接属性上有序，或者同样的属性有两个或多个连接(如 R(a,b) JOIN S(a,c) JOIN T(a,d))，那么这时可以用排序连接；

如果某个参数有索引，并且元组数量较小，那么可以采用索引连接；

如果没有机会可以用已经排序的关系或索引，并需要多遍连接，那么可以用散列连接；