网站建设广告素材,wordpress最炫主题,中国机械设备网官网,网络营销工具中最基本最重要的是最近在团队的OLAP引擎上做了一些SQL编译优化的工作#xff0c;整理到了语雀上#xff0c;也顺便发在博客上了。SQL编译优化理论并不复杂#xff0c;只需要掌握一些关系代数的基础就比较好理解#xff1b;比较困难的在于reorder算法部分。 文章目录 基础概念关系代数等价 j…最近在团队的OLAP引擎上做了一些SQL编译优化的工作整理到了语雀上也顺便发在博客上了。SQL编译优化理论并不复杂只需要掌握一些关系代数的基础就比较好理解比较困难的在于reorder算法部分。 文章目录 基础概念关系代数等价 join等价规则基数join算法的成本查询问题的分类连接树的可能数量搜索空间查询图、join树和问题复杂度 Calcite概念cascade/volcanoCalcite volcano递归优化器实现Join reorder基于连接次序优化的动态规划算法IKKBZ算法bushy-treeASI归一化 Calcite实践MultiJoinOptimizeBushyRule Join 算法选择关联子查询优化为什么要消除关联子查询基本消除规则project和filter去关联化Aggregate的去关联化集合运算的去关联化 基础概念
关系代数等价
参考《数据库系统概念》第七版 下面是第六版 注意自然连接和θ连接的交换律不能用于外连接
join等价规则
https://www.comp.nus.edu.sg/~chancy/sigmod18-reorder.pdf 基数
基数cardinality表示不同值的数量
、
join算法的成本
从上到下依次为嵌套循环连接、hash连接、排序合并连接
ASI相邻序列交换
查询问题的分类
按照查询图chain、cycle、star、clique 按照查询树结构left-deep、zig-zag、bushy tree 按照join结构有没有cross product 按照成本函数有没有ASI属性
连接树的可能数量搜索空间 查询图、join树和问题复杂度 Calcite概念
RelNodeplan/subplan relset关系表达式等价的plan集合 relsubset 关系表达式和物理属性等价的plan集合 transformationRulelogical plan变化的规则集合 converterplan将lp转化为pp的转化规则 RelOptRule 优化规则 RelOptNode 接口, 它代表的是能被 planner 操作的 expression node statement语句 reltrait 关系表达式特征 RelTraitDef 用于定义一类 RelTrait RelTrait RelTrait是一个表示查询计划特征的抽象类。它用于描述查询计划的一些特性是对应 TraitDef 的具体实例 Convention 是一种 RelTrait 用于表示 Rel 的调用约定(calling convention) rexnode 行表达式 schema逻辑模型 Program一个SQL查询解析和优化的过程集合可以将多个子过程组合在一起以便进行SQL查询的解析和优化
cascade/volcano
volcano是top-down的模块化可剪枝sql优化模型。 volcano生成两个代数模型logical and the physical algebras 分别优化lp和pppp主要选择执行算法 一个volcano优化器必须提供如下部分 (1) a set of logical operators, (2) algebraic transformation rules, possibly with condition code, (3) a set of algorithms and enforcers, (4) implementation rules, possibly with condition code, (5) an ADT “cost” with functions for basic arithmetic and comparison, (6) an ADT “logical properties,” (7) an ADT “physical property vector” including comparisons functions (equality and cover), (8) an applicability function for each algorithm and enforcer, (9) a cost function for each algorithm and enforcer, (10) a property function for each operator, algorithm, and enf volcano使用backward chaining的方式只探索实际参与更大表达式的子查询和计划。这种方法可以避免对无关的子查询和计划进行搜索从而提高查询优化的效率。
Calcite volcano递归优化器实现
RuleQueue 是一个优先队列包含当前所有可行的 RuleMatchfindBestExpr() 时每次循环中我们从中取出优先级最高的并 apply再根据 apply 的结果更新队列……如此往复直到满足终止条件。 RuleQueue并没有使用大顶堆仅仅保存了importance最大的节点。 我们想象我们现在有一组relnode匹配上了很多RuleMatch怎么决定先进行哪个match呢 RuleMatch的importance决定了先进行哪个matchrulematch的importance定义为以下两个中较大的一个
输入的 RelSubset 的 importance输出的 RelSubset 的 importance RelSubset的importance又该如何定义importance 定义为以下两个中比较大的一个 ● 该 RelSubset 本身的真实 importance ● 逻辑上相等的即位于同一个 RelSet 中任意一个 RelSubset 的真实 importance 除以 2 真实importance的计算规则如下
Join reorder
大部分的算法基于connectivity-heuristic也就是说只考虑equl-join
基于连接次序优化的动态规划算法
对于假设所有的连接都是自然连接的n个关系的集合动态规划算法的复杂度为3^n 归并连接可以产生有序的结果对于后面的排序可能有用interesting sort order。 目前我们用spark的连接算法这条暂时没用。
IKKBZ算法
left-deep tree和bushy tree left-deep tree 左深树 连接运算符的右侧输入都是具体关系右子树必和左子树的节点之一有共享谓词。
左深树适合一般场景的优化。System R优化器只考虑左深树的优化时间代价是n加入动态规划后可以在n*2^n时间内找到最佳连接次序。
bushy-tree
适合多路连接和并行优化但是很复杂。 不引入交叉乘的充要条件在于给定关系的父级必须已经得到。
ASI
简单来说就是等价谓词替换原则 定义rank函数
成本函数 谓词的选择性指的是谓词对查询结果的过滤能力 对于join来说可以有如下定义
我们将查询图视为一个有根树我们说H的选择性指的是F和H之间的选择性。 行数和选择性之间则有如下关系行数*选择性
成本函数定义如下
我们可以根据成本函数定义rank函数
下面是对于ASI的证明
归一化 Calcite实践
MultiJoinOptimizeBushyRule
第一部分进行初始化unusedEdges存放join过滤条件两个relnode之间
final MultiJoin multiJoinRel call.rel(0);final RexBuilder rexBuilder multiJoinRel.getCluster().getRexBuilder();final RelBuilder relBuilder call.builder();final RelMetadataQuery mq call.getMetadataQuery();final LoptMultiJoin multiJoin new LoptMultiJoin(multiJoinRel);final ListVertex vertexes new ArrayList();int x 0;for (int i 0; i multiJoin.getNumJoinFactors(); i) {final RelNode rel multiJoin.getJoinFactor(i);double cost mq.getRowCount(rel);vertexes.add(new LeafVertex(i, rel, cost, x));x rel.getRowType().getFieldCount();}assert x multiJoin.getNumTotalFields();final ListEdge unusedEdges new ArrayList();for (RexNode node : multiJoin.getJoinFilters()) {unusedEdges.add(multiJoin.createEdge(node));}第二步选出成本此处就是行数差异最大的过滤条件 选一个行数较小的vertex作为majorFactor另一个作为minorFactor // Comparator that chooses the best edge. A good edge is one that has// a large difference in the number of rows on LHS and RHS.final ComparatorLoptMultiJoin.Edge edgeComparator new ComparatorLoptMultiJoin.Edge() {Override public int compare(LoptMultiJoin.Edge e0, LoptMultiJoin.Edge e1) {return Double.compare(rowCountDiff(e0), rowCountDiff(e1));}private double rowCountDiff(LoptMultiJoin.Edge edge) {assert edge.factors.cardinality() 2 : edge.factors;final int factor0 edge.factors.nextSetBit(0);final int factor1 edge.factors.nextSetBit(factor0 1);return Math.abs(vertexes.get(factor0).cost- vertexes.get(factor1).cost);}};final ListEdge usedEdges new ArrayList();for (;;) {final int edgeOrdinal chooseBestEdge(unusedEdges, edgeComparator);if (pw ! null) {trace(vertexes, unusedEdges, usedEdges, edgeOrdinal, pw);}final int[] factors;if (edgeOrdinal -1) {// No more edges. Are there any un-joined vertexes?final Vertex lastVertex Util.last(vertexes);final int z lastVertex.factors.previousClearBit(lastVertex.id - 1);if (z 0) {break;}factors new int[] {z, lastVertex.id};} else {final LoptMultiJoin.Edge bestEdge unusedEdges.get(edgeOrdinal);// For now, assume that the edge is between precisely two factors.// 1-factor conditions have probably been pushed down,// and 3-or-more-factor conditions are advanced. (TODO:)// Therefore, for now, the factors that are merged are exactly the// factors on this edge.assert bestEdge.factors.cardinality() 2;factors bestEdge.factors.toArray();}// Determine which factor is to be on the LHS of the join.final int majorFactor;final int minorFactor;if (vertexes.get(factors[0]).cost vertexes.get(factors[1]).cost) {majorFactor factors[0];minorFactor factors[1];} else {majorFactor factors[1];minorFactor factors[0];}final Vertex majorVertex vertexes.get(majorFactor);final Vertex minorVertex vertexes.get(minorFactor);遍历unusedEdges加入newFactors对之前选出的majorVertex和minorVertex进行归一化并且加入vertexes // Find the join conditions. All conditions whose factors are now all in// the join can now be used.final int v vertexes.size();final ImmutableBitSet newFactors majorVertex.factors.rebuild().addAll(minorVertex.factors).set(v).build();final ListRexNode conditions new ArrayList();final IteratorLoptMultiJoin.Edge edgeIterator unusedEdges.iterator();while (edgeIterator.hasNext()) {LoptMultiJoin.Edge edge edgeIterator.next();if (newFactors.contains(edge.factors)) {conditions.add(edge.condition);edgeIterator.remove();usedEdges.add(edge);}}double cost majorVertex.cost* minorVertex.cost* RelMdUtil.guessSelectivity(RexUtil.composeConjunction(rexBuilder, conditions));final Vertex newVertex new JoinVertex(v, majorFactor, minorFactor, newFactors,cost, ImmutableList.copyOf(conditions));vertexes.add(newVertex);归一化之后进行选择性的重新计算之后进入下一轮
// Re-compute selectivity of edges above the one just chosen.// Suppose that we just chose the edge between product (10k rows) and// product_class (10 rows).// Both of those vertices are now replaced by a new vertex P-PC.// This vertex has fewer rows (1k rows) -- a fact that is critical to// decisions made later. (Hence greedy algorithm not simple.)// The adjacent edges are modified.final ImmutableBitSet merged ImmutableBitSet.of(minorFactor, majorFactor);for (int i 0; i unusedEdges.size(); i) {final LoptMultiJoin.Edge edge unusedEdges.get(i);if (edge.factors.intersects(merged)) {ImmutableBitSet newEdgeFactors edge.factors.rebuild().removeAll(newFactors).set(v).build();assert newEdgeFactors.cardinality() 2;final LoptMultiJoin.Edge newEdge new LoptMultiJoin.Edge(edge.condition, newEdgeFactors,edge.columns);unusedEdges.set(i, newEdge);}}最后一段根据新的vertexes次序建立relnode节点
// We have a winner!
ListPairRelNode, TargetMapping relNodes new ArrayList();
for (Vertex vertex : vertexes) {if (vertex instanceof LeafVertex) {LeafVertex leafVertex (LeafVertex) vertex;final Mappings.TargetMapping mapping Mappings.offsetSource(Mappings.createIdentity(leafVertex.rel.getRowType().getFieldCount()),leafVertex.fieldOffset,multiJoin.getNumTotalFields());relNodes.add(Pair.of(leafVertex.rel, mapping));} else {JoinVertex joinVertex (JoinVertex) vertex;final PairRelNode, Mappings.TargetMapping leftPair relNodes.get(joinVertex.leftFactor);RelNode left leftPair.left;final Mappings.TargetMapping leftMapping leftPair.right;final PairRelNode, Mappings.TargetMapping rightPair relNodes.get(joinVertex.rightFactor);RelNode right rightPair.left;final Mappings.TargetMapping rightMapping rightPair.right;final Mappings.TargetMapping mapping Mappings.merge(leftMapping,Mappings.offsetTarget(rightMapping,left.getRowType().getFieldCount()));if (pw ! null) {pw.println(left: leftMapping);pw.println(right: rightMapping);pw.println(combined: mapping);pw.println();}final RexVisitorRexNode shuttle new RexPermuteInputsShuttle(mapping, left, right);final RexNode condition RexUtil.composeConjunction(rexBuilder, joinVertex.conditions);final RelNode join relBuilder.push(left).push(right).join(JoinRelType.INNER, condition.accept(shuttle)).build();relNodes.add(Pair.of(join, mapping));}if (pw ! null) {pw.println(Util.last(relNodes));}Join 算法选择
关联子查询优化
我们将连接外部查询和子查询的算子叫做CorrelatedJoin(也被称之为lateral join, dependent join、apply算子等等。它的左子树我们称之为外部查询(input)右子树称之为子查询(subquery)。
注bag语义允许元素重复出现和set语义正交
为什么要消除关联子查询 CorrelatedJoin这个算子打破了以往对逻辑树自上而下的执行模式。普通的逻辑树都是从叶子节点往根结点执行的但是CorreltedJoin的右子树会被带入左子树的行的值反复的执行。
基本消除规则
如果 Apply 的右边不包含来自左边的参数(或者只包含filter参数)那它就和直接 Join 是等价的
project和filter去关联化
尽可能把 Apply 往下推、把 Apply 下面的算子向上提。
Aggregate的去关联化 SELECT c_custkey
FROM CUSTOMER
WHERE 1000000 (SELECT SUM(o_totalprice)FROM ORDERSWHERE o_custkey c_custkey
)
// 等价于
select sum(p_price) 1000000 from CUSTOMER.o_custkey left join ORDERS.c_custkey
on CUSTOMER.o_custkey ORDERS.c_custkey group by ORDERS.c_custkey集合运算的去关联化 这一组规则很少能派上用场。在 TPC-H 的 Schema 下甚至很难写出一个带有 Union All 的、有意义的子查询。 文章转载自： http://www.morning.dmsxd.cn.gov.cn.dmsxd.cn http://www.morning.stflb.cn.gov.cn.stflb.cn http://www.morning.kcypc.cn.gov.cn.kcypc.cn http://www.morning.zlsmx.cn.gov.cn.zlsmx.cn http://www.morning.ryfq.cn.gov.cn.ryfq.cn http://www.morning.glnmm.cn.gov.cn.glnmm.cn http://www.morning.rwfp.cn.gov.cn.rwfp.cn http://www.morning.gfqjf.cn.gov.cn.gfqjf.cn http://www.morning.yltyr.cn.gov.cn.yltyr.cn http://www.morning.dqxph.cn.gov.cn.dqxph.cn http://www.morning.newfeiya.com.cn.gov.cn.newfeiya.com.cn http://www.morning.bwqr.cn.gov.cn.bwqr.cn http://www.morning.xltdh.cn.gov.cn.xltdh.cn http://www.morning.tcxzn.cn.gov.cn.tcxzn.cn http://www.morning.hsrpr.cn.gov.cn.hsrpr.cn http://www.morning.rynqh.cn.gov.cn.rynqh.cn http://www.morning.zqkms.cn.gov.cn.zqkms.cn http://www.morning.zzhqs.cn.gov.cn.zzhqs.cn http://www.morning.mywnk.cn.gov.cn.mywnk.cn http://www.morning.mzgq.cn.gov.cn.mzgq.cn http://www.morning.hbjqn.cn.gov.cn.hbjqn.cn http://www.morning.lbfgq.cn.gov.cn.lbfgq.cn http://www.morning.pqwrg.cn.gov.cn.pqwrg.cn http://www.morning.cpwmj.cn.gov.cn.cpwmj.cn http://www.morning.ryywf.cn.gov.cn.ryywf.cn http://www.morning.kndyz.cn.gov.cn.kndyz.cn http://www.morning.njdtq.cn.gov.cn.njdtq.cn http://www.morning.rltsx.cn.gov.cn.rltsx.cn http://www.morning.brxzt.cn.gov.cn.brxzt.cn http://www.morning.chrbp.cn.gov.cn.chrbp.cn http://www.morning.smyxl.cn.gov.cn.smyxl.cn http://www.morning.pgzgy.cn.gov.cn.pgzgy.cn http://www.morning.chxsn.cn.gov.cn.chxsn.cn http://www.morning.rxcqt.cn.gov.cn.rxcqt.cn http://www.morning.glnfn.cn.gov.cn.glnfn.cn http://www.morning.kpcky.cn.gov.cn.kpcky.cn http://www.morning.qnjcx.cn.gov.cn.qnjcx.cn http://www.morning.fldsb.cn.gov.cn.fldsb.cn http://www.morning.jfymz.cn.gov.cn.jfymz.cn http://www.morning.sqhlx.cn.gov.cn.sqhlx.cn http://www.morning.jzlfq.cn.gov.cn.jzlfq.cn http://www.morning.wkrkb.cn.gov.cn.wkrkb.cn http://www.morning.qrqdr.cn.gov.cn.qrqdr.cn http://www.morning.dsxgc.cn.gov.cn.dsxgc.cn http://www.morning.dyxlm.cn.gov.cn.dyxlm.cn http://www.morning.kbdrq.cn.gov.cn.kbdrq.cn http://www.morning.thzgd.cn.gov.cn.thzgd.cn http://www.morning.cgtrz.cn.gov.cn.cgtrz.cn http://www.morning.ssjtr.cn.gov.cn.ssjtr.cn http://www.morning.mfmbn.cn.gov.cn.mfmbn.cn http://www.morning.nbqwt.cn.gov.cn.nbqwt.cn http://www.morning.rwfj.cn.gov.cn.rwfj.cn http://www.morning.zqbrw.cn.gov.cn.zqbrw.cn http://www.morning.mywmb.cn.gov.cn.mywmb.cn http://www.morning.wnbqy.cn.gov.cn.wnbqy.cn http://www.morning.czxrg.cn.gov.cn.czxrg.cn http://www.morning.tzjqm.cn.gov.cn.tzjqm.cn http://www.morning.rongxiaoman.com.gov.cn.rongxiaoman.com http://www.morning.wkcl.cn.gov.cn.wkcl.cn http://www.morning.ztcwp.cn.gov.cn.ztcwp.cn http://www.morning.ywtbk.cn.gov.cn.ywtbk.cn http://www.morning.hjsrl.cn.gov.cn.hjsrl.cn http://www.morning.pwdrc.cn.gov.cn.pwdrc.cn http://www.morning.ygrdb.cn.gov.cn.ygrdb.cn http://www.morning.bmbnc.cn.gov.cn.bmbnc.cn http://www.morning.nzmw.cn.gov.cn.nzmw.cn http://www.morning.flncd.cn.gov.cn.flncd.cn http://www.morning.skrh.cn.gov.cn.skrh.cn http://www.morning.rcyrm.cn.gov.cn.rcyrm.cn http://www.morning.xnhnl.cn.gov.cn.xnhnl.cn http://www.morning.zbmcz.cn.gov.cn.zbmcz.cn http://www.morning.hmpxn.cn.gov.cn.hmpxn.cn http://www.morning.ryxyz.cn.gov.cn.ryxyz.cn http://www.morning.gypcr.cn.gov.cn.gypcr.cn http://www.morning.xrsqb.cn.gov.cn.xrsqb.cn http://www.morning.zqwp.cn.gov.cn.zqwp.cn http://www.morning.xhklb.cn.gov.cn.xhklb.cn http://www.morning.qyfrd.cn.gov.cn.qyfrd.cn http://www.morning.lsqmb.cn.gov.cn.lsqmb.cn http://www.morning.cwrnr.cn.gov.cn.cwrnr.cn
