尧图网页设计 
1. 一个从线上慢查询说起的故事上个月排查一个线上业务问题时碰到一条让我印象深刻的慢查询一条关联了六张表的复杂统计查询在凌晨的数据对账任务里跑出了将近二十分钟的执行时长。业务方已经做过分区裁剪和索引优化但这张查询涉及多个大表的连接过滤条件分散在最外层的WHERE里内层连接过程中产生的中间结果被大量无效数据撑得非常大导致Memory和Spill都压到了极限。这条查询让我想起一个优化器里非常经典、但容易被忽略的问题连接条件下的推Join Predicate Pushdown以及支撑它的决策机制——代价模型。连接条件下推这件事情字面意思就是把WHERE条件尽量往下推推过JOIN节点让它在数据进入连接之前就把不该参与计算的行过滤掉。但难点从来不在能不能推而在推了到底划不划算。举个最直观的例子一张订单表ORDERS有1000万行一张订单明细表LINEITEM有6000万行最外层的查询条件是只统计2024年Q1的数据。这种情况下把时间过滤条件下推到JOIN之前几乎是零风险的因为时间字段直接落在ORDERS表上扫描阶段就能过滤掉大量数据。但如果这个过滤条件涉及的是JOIN之后计算出来的表达式或者过滤条件的字段在两张表上都存在但语义不同盲目下推反而可能破坏等价变换甚至因为选择率估算偏差导致优化器选错执行计划。所以真正有价值的问题有两个第一连接条件下推在代价模型里到底如何计算收益第二实际工程中这套机制在水深火热的复杂查询场景里是怎么落地、怎么调参的这篇文章不打算泛泛地讲下推很好、能下就下而是想把连接条件下推的代价模型设计过程完整铺开从收益计算、搜索策略、工程实现到性能回测把我实际踩过的坑和验证过的数据都放出来。适合正在做查询优化器、或者负责复杂数据平台SQL性能优化的同学参考。2. 为什么能下推不等于该下推收益计算的底层逻辑2.1 先把代价模型的地基打好要讨论连接条件下推的代价模型得先明确一件事数据库优化器里几乎所有决策都围绕着一个核心问题——让整个执行计划的总代价最小。总代价一般分成两块CPU代价和IO代价。在内存足够、数据落不下磁盘的OLAP场景里CPU代价是主要矛盾在数据量大、频繁发生落盘或网络传输的分布式场景里IO和网络代价反而更敏感。代价模型里最经典的计算方式是每个算子都有对应的代价函数优化器枚举不同的执行计划形态累加所有算子代价取最小值。对于连接算子Hash Join来说一次连接的总代价大致可以写成一个简化公式C(join) C(build) C(probe)其中C(build)代表构建哈希表的开销和参与连接的内表行数N_build正相关C(probe)代表探测开销和外表行数N_probe以及哈希冲突次数正相关。这个公式虽然粗糙但抓住了连接代价的本质——费用主要由两侧输入的行数决定。所以优化器的基本直觉就是如果能减小连接两侧任意一侧的行数连接代价就会下降。连接条件下推的收益来源本质上就是这个让过滤发生在连接之前从而减少JOIN两侧输入的行数。但是问题来了如果每个条件下推都能减少行数那优化器为什么不把所有条件都推到最底下图省事的全下推策略在简单查询上是不会出问题的但在复杂查询里会遇到两个致命情况一是下推可能导致条件失效。如果过滤条件涉及的是JOIN之后才能生成的表达式比如WHERE col1 col2 100其中col1来自表A、col2来自表B这个条件本质上不能直接推到任何一侧的扫描节点之前强行下推反而会造成语义错误。二是下推顺序影响收益大小。一个条件可能同时适用于多条下推路径比如表A JOIN 表B JOIN 表C过滤条件涉及A和B。是把它下推到A-B连接之前还是让它留在A-B连接之后两种方案的收益截然不同需要代价模型来算。所以连接条件下推真正要做的事情是在每一个候选位置上计算出推下去和不推的代价差异选择收益最大的那个位置同时保证语义永远正确。2.2 构建收益函数一个可以量化的下推决策公式我用一个相对通用的方法来设计连接条件下推的收益函数。假设有一条过滤谓词P它当前所处的位置在一棵连接树的某个节点N之上P的输入依赖集合是S(P)。如果把P从当前位置下推到某个更底层的节点M前提条件就是S(P)中的全部字段在M的输出Schema中都能找到而且在下推路径上不经过任何会改变行语义的算子比如DISTINCT、LIMIT等。在满足前提的基础上我们定义这个下推动作的收益值Gain(S(P), M, N)Gain RowCount(输入到N的行数) - RowCount(输入到M的行数) ΔCost(连接)这个公式想表达的意思其实很简单下推的真正价值等于在更高的位置过滤掉的行数和因为过滤提前而减少的连接代价之和。直接计算过滤掉的行数需要知道谓词P的选择率Selectivity(P)通常优化器通过直方图、采样统计来估算。如果估算不准确整个收益计算就会失真。实际操作中我更习惯用另一个更贴近物理执行的方式来估算收益。假设P下推前它要面对的数据规模是R_old在节点N之上计算时的输入行数下推后它要面对的数据规模是R_new在节点M之上计算时的输入行数。那么下推动作的实际收益可以近似为Gain ≈ (R_old - R_old × Selectivity(P)) × UnitCostFilter (R_old - R_new) × UnitCostPropagate这里UnitCostFilter代表每处理一行数据执行谓词判断的CPU代价UnitCostPropagate代表每行数据在连接树中向上传递的平均代价。这个公式的价值在于它把下推收益拆解成了两部分——过滤本身节省的计算量以及由于数据行数减少而节省的传递和连接代价。举一个具体的数字。假设某条查询中一个连接节点之上有1000万行数据需要处理谓词P的选择率是0.05UnitCostFilter大概是每条500nsUnitCostPropagate大概是每条200ns。那么过滤本身节省的代价(1000万 - 50万) × 500ns ≈ 4.75秒传递节省的代价(1000万 - R_new) × 200ns如果R_new是200万行则节省约1.6秒合起来就是6.35秒的收益。这个收益足以让优化器在这次下推中获得正收益。2.3 收益之外必须盯住的三条约束但代价模型不能只看收益还得看约束条件。我在实际设计中总结了三条必须满足的硬约束任何一条不满足下推动作都会被否决约束1等价性约束。下推前后查询结果集必须完全一致。这听起来像废话但实际很容易出问题。比如当连接类型是LEFT OUTER JOIN时把右表字段的过滤条件下推到右表扫描节点之前可能改变连接语义。因为右表的行被过滤掉之后LEFT JOIN的结果中会出现NULL填充行而如果过滤发生在连接之后那些行会被整体排除结果集就完全不同。所以对于外连接过滤条件下推要特别谨慎对待。约束2基数估算可信度约束。收益函数高度依赖选择率Selectivity(P)的准确性。如果统计信息缺失或者数据严重倾斜估算出的选择率可能和真实值相差一个数量级。这种情况下一个理论上高收益的下推动作实际执行时可能几乎没收益甚至负收益。所以我在设计时给下推动作设置了一个收益阈值只有当Gain预估超过某个绝对阈值比如1秒时才允许下推低于阈值就保守处理避免因为估算误差导致执行计划恶化。约束3路径长度约束。下推路径越长中间经过的连接树层级越多不确定性越大。尤其是当P的字段依赖分布在多张表上时下推动作可能要穿过多个连接节点每穿过一个节点就多一分依赖RBO基于规则的优化Rule-Based Optimization安全性的风险。我的经验是优先支持单侧下推谓词只依赖单表字段对于跨表的多表谓词先做等价改写再评估下推路径不要硬推。这三条约束是我在做连接条件下推代价模型时踩过坑后总结出来的。可以说没有这三条收益函数再精巧也只是纸上谈兵。3. 代价模型如何与优化器搜索框架配合3.1 搜索空间枚举不是所有位置都要算一遍代价代价模型不会凭空工作它需要在优化器的搜索框架里找到自己的位置。常见的优化器框架有两种流派一种是基于动态规划的自底向上枚举典型代表是System R的DP算法以及Volcano/Cascades框架另一种是启发式的规则匹配典型的像很多商业数据库优化器里的RBOCBO混合机制。连接条件下推属于典型的规则触发代价抉择场景。更准确地说它的搜索空间是一个谓词P在当前连接树中所有合法的落脚点的集合。理论上如果连接树有N个节点P的下推候选位置最多有N个但实际上因为字段依赖约束和等价性约束真正合法的候选位置往往远小于N。以一条三表连接T1 JOIN T2 ON T1.aT2.a JOIN T3 ON T2.bT3.b为例假设WHERE条件是T1.c 100。这个条件依赖T1.c那么它的合法落脚点包括紧贴在T1扫描节点之上的位置即以T1作为叶子节点时最靠近扫描的位置T1和T2连接之后的位置全连接之后的最顶层从逻辑上说条件放在这三个位置都不改变语义但代价完全不同。放在T1扫描之上可以在扫描阶段就过滤掉大量行T1参与连接的数据量最小化放在T1-T2连接之后意味着T1和T2的完整连接结果都要算出来过滤只影响T3连接时的输入。代价模型的工作就是对三个合法位置分别估算执行代价选择代价最小的那个。这个过程在Cascades框架里通常体现为优化器在探索Explore阶段对物理属性进行枚举并在优化Optimize阶段对每个候选物理计划调用代价函数打分。3.2 我用过的两种实用策略纯代价驱动与代价规则混合纯代价驱动的做法是把所有合法下推位置全部展开逐一进行代价计算最后选择全局最优。这个方案理论最优但代价是搜索开销很大。尤其是当一个谓词能穿过多个连接节点时候选位置爆炸式增长优化器本身的开销可能超过下推带来的收益。对于OLTP型数据库来说这是不可接受的因为OLTP的优化器必须在毫秒级别返回执行计划。所以我在实际项目中更倾向于推荐代价规则混合策略这也是很多商业数据库真正的落地方式。思路是先用RBO rules做安全性和基础形态的裁剪。比如字段不满足依赖约束的谓词直接结束搜索外连接下的高危谓词直接排除。对于通过RBO裁剪后的候选位置按从底向上的顺序依次评估收益函数Gain。评估过程中维护当前最优候选一旦发现某个位置的计算结果使得后续所有位置的收益都不可能超越当前最优利用收益率递减的单调性做剪枝就提前终止搜索。这个混合策略有一个非常实用的效果对于90%以上的常规查询优化器只需要评估一两个候选位置就能给出结论对于极少数复杂查询才会进入多位置的全面搜索。实际线上运行结果也证明这个策略不仅能保证执行计划的质量优化器本身的开销平均只增加了2%左右完全在可接受范围内。3.3 和Join Reorder连接重排的联调问题连接条件下推不是一个孤立的优化规则它和连接重排Join Reorder之间的交互非常密切很多优化器Bug都出在这个交界处。举例T1 JOIN T2的WHERE条件T1.c 100如果优化器先把连接顺序调整为T2 JOIN T1因为T2的选择率更低排在前面做驱动表那么原来针对T1的下推路径不变但如果T1被调整到了Probe侧右侧下推位置仍然应该贴在T1扫描之上只是重新计算收益时要注意行数方向的差异。如果不关心这一点可能下推位置是对的但收益算错了。另一个更隐蔽的问题是下推可能改变原始连接树的输出Schema顺序进而影响上层算子的物理属性选择。比如一个Sort节点的排序列依赖所有参与连接的字段当下推导致某个字段被提前过滤并标记为空值时Sort可能失去排序价值。这种情况在纯理论推导中很容易被忽略但在工程实现中需要为上下层属性传递建立完备的接口。我的联调策略很简单也很有效在代价模型统一出口处维护一个全局映射表记录每个谓词的来源、当前下推层级、以及它依赖的字段集合。任何Join Reorder后的候选计划都必须先经过这个映射表校验确保谓词落脚点仍然合法再进入代价计算流程。这样就把两个优化规则的耦合关系降到了最低。4. 工程落地的核心模块与三个容易被忽略的实现细节4.1 下推候选生成器的设计我在实现连接条件下推模块时把核心分成三个组件谓词分析器、候选位置枚举器、收益计算器。谓词分析器的输入是一棵逻辑执行计划树输出是一组可以被潜在下推的谓词集合。这个阶段主要做的是从每个过滤算子Filter中提取出谓词子句将其拆分成原子谓词然后标记每个原子谓词依赖的字段集合。一个重点处理是对于复合谓词比如A.x 10 AND B.y 20这种跨表的AND组合我会先尝试做拆分让每个子谓词分别走独立的下推决策。因为它们的收益和风险是完全不同的绑定在一起处理只会让代价计算变得模糊。候选位置枚举器负责为每个原子谓词枚举合法的落脚点。我这里的实现逻辑是从谓词当前的最高位置出发沿着连接树向下遍历。每遇到一个节点就检查该节点的输出Schema是否包含谓词依赖的全部字段同时检查连接类型是否允许下推穿过该节点。满足条件的节点加入候选列表直到叶子节点为止。这里有一个非常关键的点候选列表的生成必须保持自上而下的顺序因为后序的收益计算需要依赖位置之间的层级关系。收益计算器就是前面提到的收益公式的工程实现。它需要访问统计信息管理器、连接代价估计器、以及基数估算器。在现代数据仓库场景中这三者的数据来源通常是直方图、HyperLogLog统计、或者采样统计。4.2 容易被忽略的细节一谓词合并与去重来看一个实际场景。用户SQL里写了WHERE T1.c 100而连接条件里恰好有T1.c T2.d。这种情况下如果优化器对两个谓词分别做处理可能会产生重复的下推动作比如先把T1.c 100推到T1上又把T1.c T2.d推到T1上而这两个谓词在同一层其实是等价的重复下推只是在浪费搜索时间。处理方法是在谓词分析阶段做去重和合并。当发现两个谓词语义等价比如通过等价类推导时只保留一个作为下推候选。同时如果新谓词可以通过已有谓词推导出来例如AB且B5可以推导出A5则合并为一个更强的谓词。这一步很大程度上决定了优化器的解析效率和最终执行效率。4.3 容易被忽略的细节二下推时对分区裁剪和索引裁剪的配合连接条件下推的收益在小表上不明显但在大分区表上可能会产生指数级的收益——因为过滤条件的下推意味着分区裁剪提前生效。假设一张按月分区的订单表查询条件WHERE order_date 2024-03-01本来就在表扫描层那没问题。但如果是JOIN之后产生的过滤条件比如内表的分区键需要通过连接条件与外表关联才会被确定那么把等价条件推回到内表扫描层后优化器在物理扫描阶段可以直接跳过一大部分分区文件IO代价瞬间降到几乎为零。这在分布式查询引擎里尤其重要因为每个分区的扫描都可能对应一个远程任务。我在这类场景中强调一个原则连接条件下推的代价模型不能只盯着CPU代价必须把分区裁剪产生的IO收益换算进位成本模型之中。我的做法是在代价函数中对具有分区裁剪潜力的扫描算子设置一个裁剪因子一旦下推动作使裁剪因子生效就直接让该算子的IO代价乘以一个极小的权重系数。4.4 容易被忽略的细节三选择率校准和统计信息失效代价模型的准确性极度依赖选择率的估算。在复杂查询场景中统计信息失效是常态。比如一张表在业务高峰期数据量突然翻倍但统计信息还是几小时前的那么优化器估算出的选择率和真实执行值之间就会出现严重偏差。我在工程中引入了执行期反馈校准机制在实际执行结束之后把真实扫描行数和中间结果行数回写到统计信息缓存中下一次优化器做代价计算时优先使用最近一次真实执行的数据。这种反馈机制虽然简单但能明显缓解统计信息滞后带来的代价估算失真问题。简单查询可能体会不到差别复杂查询场景下它往往是稳定性的救命稻草。5. 实测案例一张宽表关联查询的下推优化复盘5.1 场景描述和原始执行计划为了更好地解释代价模型在真实场景中的表现我构造一个业务上很常见的分析类查询。场景是一个电商平台的数据团队需要统计每个用户在2024年2月份的下单行为和用户基本画像关联输出按年龄段分组的下单汇总。涉及的表如下用户表USERS约500万行包含年龄、性别、注册时间等。订单表ORDERS约1.2亿行包含下单时间、用户ID、订单金额按下单时间做月度分区。订单明细表ORDER_LINEITEMS约3亿行包含订单ID、商品ID、商品数量按订单ID哈希分片。商品表PRODUCTS约50万行包含商品ID、类目ID、供应商ID。这条查询本身并不算极端但连接层级不少USERS和ORDERS先做用户粒度关联再关联LINEITEMS做明细展开最后关联PRODUCTS得到类目信息SUM聚合后按年龄分组。最原始的执行计划长这样简化版Aggregate(group by 年龄) Join(ORDER_LINEITEMS.id ORDER.id, PRODUCTS.id ORDER_LINEITEMS.product_id) Join(USERS.id ORDERS.user_id) Filter(order_time 2024-02-01 AND order_time 2024-03-01) Scan(ORDERS) Scan(USERS) Join(ORDER_LINEITEMS.product_id PRODUCTS.id) Scan(ORDER_LINEITEMS) Scan(PRODUCTS)注意看这个计划里存在一个明显的问题order_time过滤条件已经贴着ORDERS扫描层了但优化器还是没有把订单明细表LINEITEMS和商品表PRODUCTS的数据量过滤下来——因为WHERE条件里没有直接涉及这两张表的谓词。整个查询的中间结果体量由ORDER_LINEITEMS表决定也就是3亿行。这意味着JOIN之后向上传递的数据是巨大的聚合阶段的压力全集中在这里。5.2 代价模型介入后的下推决策针对这个计划连接条件下推的代价模型做了什么核心是发现了一个隐含等价条件下的推机会通过连接条件ORDERS.id ORDER_LINEITEMS.order_id可以把order_time过滤条件在语义上传播到ORDER_LINEITEMS侧。 但直接推order_time是不行的因为ORDER_LINEITEMS没有该字段。正确做法是利用等价类推导把ORDER_LINEITEMS的扫描范围缩小到那些订单属于2024年2月的明细行。在底层实现中这需要把order_time过滤条件下推转化为一个子查询ORDER_LINEITEMS.order_id IN (SELECT id FROM ORDERS WHERE order_time IN 2024-02)。这种改写后ORDER_LINEITEMS扫描可以从3亿行降到大约4500万行假设平均每单3行明细2月份订单量1500万单。代价模型做这个判断的过程是什么呢首先它识别到原始计划的ORDER_LINEITEMS扫描行数是3亿行连接后输出行数近似3亿行。推入子查询改写后扫描行数降到4500万行。收益计算如下扫描成本降低3亿 - 4500万 2.55亿行按单行扫描100ns算节省约25.5秒JOIN探测成本降低JOIN的Probe侧输入从3亿行降到4500万行按每行200ns算节省约51秒后续聚合输入降低聚合算子的输入行数从3亿行降到4500万行按每行150ns算节省约38.25秒这三项相加总收益约为114.75秒远超一开始定下的1秒收益阈值所以优化器毫不犹豫地选择执行这次下推改写。5.3 实测结果和调参过程优化前这条查询在测试环境执行耗时217秒优化后同一查询只需要83秒整体提速2.6倍。最直观的变化是ORDER_LINEITEMS的扫描行数从3亿行降到约4600万行Spill量减少了70%以上。验证过后我又做了几组调参实验希望观察收益阈值对执行计划的影响收益阈值设为1秒时下推动作触发得积极适合查询本身步态复杂、中间结果膨胀严重的场景。 收益阈值设为10秒时下推动作变保守适合查询本身较短、不想为优化本身增加额外开销的场景。 收益阈值设为100秒时几乎所有下推动作都被压制查询表现回到了优化前水平。这个实验告诉我一个很关键的工程结论收益阈值其实应该按查询粒度动态调整而不是一个全局静态值。同一套系统里既有几十毫秒的轻查询也有几十秒的重分析如果统一用同一个阈值必然会伤害某一类查询。比较合理的做法是让代价模型根据优化器估算的查询总代价动态计算阈值比如总代价的1%作为下推触发门槛。6. 调参之外我在多次实战中总结出的三条经验6.1 代价模型的计算粒度宏观收益比微观细节重要很多团队一上手做代价模型就想着把CPU流水线、Cache命中率、指令级并行都建模进去。这个方向不是不对但在连接条件下推这个优化规则上过于精细的建模经常适得其反。原因是下推决策面对的核心变量是行数的数量级变化比如1亿行降到1000万行这是十倍量级的差距此时CPU微架构层面那些10%以内的差异根本不构成决策依据。所以我实际编码时会把UnitCost参数做得很粗糙——先按统一平均行宽估算再按是否有索引或分区裁剪做一次量级修正这足够了。真正值得精细建模的恰恰是选择率和基数估算因为它们直接决定了行数级的变化方向。6.2 统计信息的收集频率必须跟上数据变化连接条件下推依赖选择率选择率依赖统计信息。在离线数仓场景中晚间的ETL任务对同一张表反复写入如果统计信息不随之更新第二天白天分析查询的代价估算就会全偏。我这里有一个很惨痛的线上教训一张大表的统计信息三天没刷新期间行数从2000万涨到了8000万代价模型在多个查询里都低估了扫描代价导致优化器推出来的执行计划普遍比手写SQL更慢业务方一度以为是优化器坏了。后来我做了两件事一是把大表的统计信息收集安排在ETL任务完成后自动触发二是引入前面提到的执行期反馈校准。这套组合实施后统计信息失效导致的性能波动基本消失了。6.3 复杂查询里代价模型要给安全兜底留后门再好的代价模型也免不了估算失误。所以在工程实现中我一直保留一个逃生通道每个下推改写后的执行计划都可以通过优化器开关一键关闭比如设置disable_predicate_pushdowntrue。线上如果出现某条查询因为下推导致性能异常不需要改代码、不用发版直接通过参数关闭这个规则就能恢复。这个后门设计看起来没什么含金量但在大规模集群运维中非常管用。另外我建议团队在监控系统中专门加一条指标每个执行计划中下推谓词的数量和总收益预估。一旦发现某类查询的下推收益预估和实际执行时间明显背离就要启动针对性和选择率校准流程。这是我多次排障下来认为性价比最高的一项监控指标。7. 后续演进方向从连接条件下推到更广泛的算子下推连接条件下推做到一定成熟度后很自然会延伸出两个更有挑战的方向第一个方向是聚合下推。如果连接树下方存在一个可以提前做部分聚合的节点把聚合操作下推到JOIN之前往往能获得比过滤下推更极致的收益。典型的场景是星型模型中的事实表与维度表关联后做GROUP BY如果把GROUP BY的聚合下推到事实表扫描后、JOIN之前可以用Bloom Filter或半连接技术大幅削减维度表关联的数据量。当然聚合下推的语义安全性验证要比过滤下推复杂得多需要处理分组键的等价性等一系列问题。第二个方向是物化视图与下推的结合。当一条查询涉及的连接树正好与某个物化视图匹配时连接条件下推机制可以辅助优化器判断是否应该直接扫描物化视图还是在基表上动态计算。这个方向的代价模型需要补充物化视图的维护代价和查询改写收益是另一个值得深入的课题。我个人认为连接条件下推算是查询优化器里性价比极高的一类优化规则实现难度适中收益直观可见而且几乎没有副作用——只要语义校验和代价计算做得足够稳健。如果你正好在做数据库内核、查询引擎或者数据平台性能优化从这条规则入手打磨代价模型会是投入产出比很高的一条路径。 连接条件下推的代价模型设计与工程实践
1. 一个从线上慢查询说起的故事上个月排查一个线上业务问题时碰到一条让我印象深刻的慢查询一条关联了六张表的复杂统计查询在凌晨的数据对账任务里跑出了将近二十分钟的执行时长。业务方已经做过分区裁剪和索引优化但这张查询涉及多个大表的连接过滤条件分散在最外层的WHERE里内层连接过程中产生的中间结果被大量无效数据撑得非常大导致Memory和Spill都压到了极限。这条查询让我想起一个优化器里非常经典、但容易被忽略的问题连接条件下的推Join Predicate Pushdown以及支撑它的决策机制——代价模型。连接条件下推这件事情字面意思就是把WHERE条件尽量往下推推过JOIN节点让它在数据进入连接之前就把不该参与计算的行过滤掉。但难点从来不在能不能推而在推了到底划不划算。举个最直观的例子一张订单表ORDERS有1000万行一张订单明细表LINEITEM有6000万行最外层的查询条件是只统计2024年Q1的数据。这种情况下把时间过滤条件下推到JOIN之前几乎是零风险的因为时间字段直接落在ORDERS表上扫描阶段就能过滤掉大量数据。但如果这个过滤条件涉及的是JOIN之后计算出来的表达式或者过滤条件的字段在两张表上都存在但语义不同盲目下推反而可能破坏等价变换甚至因为选择率估算偏差导致优化器选错执行计划。所以真正有价值的问题有两个第一连接条件下推在代价模型里到底如何计算收益第二实际工程中这套机制在水深火热的复杂查询场景里是怎么落地、怎么调参的这篇文章不打算泛泛地讲下推很好、能下就下而是想把连接条件下推的代价模型设计过程完整铺开从收益计算、搜索策略、工程实现到性能回测把我实际踩过的坑和验证过的数据都放出来。适合正在做查询优化器、或者负责复杂数据平台SQL性能优化的同学参考。2. 为什么能下推不等于该下推收益计算的底层逻辑2.1 先把代价模型的地基打好要讨论连接条件下推的代价模型得先明确一件事数据库优化器里几乎所有决策都围绕着一个核心问题——让整个执行计划的总代价最小。总代价一般分成两块CPU代价和IO代价。在内存足够、数据落不下磁盘的OLAP场景里CPU代价是主要矛盾在数据量大、频繁发生落盘或网络传输的分布式场景里IO和网络代价反而更敏感。代价模型里最经典的计算方式是每个算子都有对应的代价函数优化器枚举不同的执行计划形态累加所有算子代价取最小值。对于连接算子Hash Join来说一次连接的总代价大致可以写成一个简化公式C(join) C(build) C(probe)其中C(build)代表构建哈希表的开销和参与连接的内表行数N_build正相关C(probe)代表探测开销和外表行数N_probe以及哈希冲突次数正相关。这个公式虽然粗糙但抓住了连接代价的本质——费用主要由两侧输入的行数决定。所以优化器的基本直觉就是如果能减小连接两侧任意一侧的行数连接代价就会下降。连接条件下推的收益来源本质上就是这个让过滤发生在连接之前从而减少JOIN两侧输入的行数。但是问题来了如果每个条件下推都能减少行数那优化器为什么不把所有条件都推到最底下图省事的全下推策略在简单查询上是不会出问题的但在复杂查询里会遇到两个致命情况一是下推可能导致条件失效。如果过滤条件涉及的是JOIN之后才能生成的表达式比如WHERE col1 col2 100其中col1来自表A、col2来自表B这个条件本质上不能直接推到任何一侧的扫描节点之前强行下推反而会造成语义错误。二是下推顺序影响收益大小。一个条件可能同时适用于多条下推路径比如表A JOIN 表B JOIN 表C过滤条件涉及A和B。是把它下推到A-B连接之前还是让它留在A-B连接之后两种方案的收益截然不同需要代价模型来算。所以连接条件下推真正要做的事情是在每一个候选位置上计算出推下去和不推的代价差异选择收益最大的那个位置同时保证语义永远正确。2.2 构建收益函数一个可以量化的下推决策公式我用一个相对通用的方法来设计连接条件下推的收益函数。假设有一条过滤谓词P它当前所处的位置在一棵连接树的某个节点N之上P的输入依赖集合是S(P)。如果把P从当前位置下推到某个更底层的节点M前提条件就是S(P)中的全部字段在M的输出Schema中都能找到而且在下推路径上不经过任何会改变行语义的算子比如DISTINCT、LIMIT等。在满足前提的基础上我们定义这个下推动作的收益值Gain(S(P), M, N)Gain RowCount(输入到N的行数) - RowCount(输入到M的行数) ΔCost(连接)这个公式想表达的意思其实很简单下推的真正价值等于在更高的位置过滤掉的行数和因为过滤提前而减少的连接代价之和。直接计算过滤掉的行数需要知道谓词P的选择率Selectivity(P)通常优化器通过直方图、采样统计来估算。如果估算不准确整个收益计算就会失真。实际操作中我更习惯用另一个更贴近物理执行的方式来估算收益。假设P下推前它要面对的数据规模是R_old在节点N之上计算时的输入行数下推后它要面对的数据规模是R_new在节点M之上计算时的输入行数。那么下推动作的实际收益可以近似为Gain ≈ (R_old - R_old × Selectivity(P)) × UnitCostFilter (R_old - R_new) × UnitCostPropagate这里UnitCostFilter代表每处理一行数据执行谓词判断的CPU代价UnitCostPropagate代表每行数据在连接树中向上传递的平均代价。这个公式的价值在于它把下推收益拆解成了两部分——过滤本身节省的计算量以及由于数据行数减少而节省的传递和连接代价。举一个具体的数字。假设某条查询中一个连接节点之上有1000万行数据需要处理谓词P的选择率是0.05UnitCostFilter大概是每条500nsUnitCostPropagate大概是每条200ns。那么过滤本身节省的代价(1000万 - 50万) × 500ns ≈ 4.75秒传递节省的代价(1000万 - R_new) × 200ns如果R_new是200万行则节省约1.6秒合起来就是6.35秒的收益。这个收益足以让优化器在这次下推中获得正收益。2.3 收益之外必须盯住的三条约束但代价模型不能只看收益还得看约束条件。我在实际设计中总结了三条必须满足的硬约束任何一条不满足下推动作都会被否决约束1等价性约束。下推前后查询结果集必须完全一致。这听起来像废话但实际很容易出问题。比如当连接类型是LEFT OUTER JOIN时把右表字段的过滤条件下推到右表扫描节点之前可能改变连接语义。因为右表的行被过滤掉之后LEFT JOIN的结果中会出现NULL填充行而如果过滤发生在连接之后那些行会被整体排除结果集就完全不同。所以对于外连接过滤条件下推要特别谨慎对待。约束2基数估算可信度约束。收益函数高度依赖选择率Selectivity(P)的准确性。如果统计信息缺失或者数据严重倾斜估算出的选择率可能和真实值相差一个数量级。这种情况下一个理论上高收益的下推动作实际执行时可能几乎没收益甚至负收益。所以我在设计时给下推动作设置了一个收益阈值只有当Gain预估超过某个绝对阈值比如1秒时才允许下推低于阈值就保守处理避免因为估算误差导致执行计划恶化。约束3路径长度约束。下推路径越长中间经过的连接树层级越多不确定性越大。尤其是当P的字段依赖分布在多张表上时下推动作可能要穿过多个连接节点每穿过一个节点就多一分依赖RBO基于规则的优化Rule-Based Optimization安全性的风险。我的经验是优先支持单侧下推谓词只依赖单表字段对于跨表的多表谓词先做等价改写再评估下推路径不要硬推。这三条约束是我在做连接条件下推代价模型时踩过坑后总结出来的。可以说没有这三条收益函数再精巧也只是纸上谈兵。3. 代价模型如何与优化器搜索框架配合3.1 搜索空间枚举不是所有位置都要算一遍代价代价模型不会凭空工作它需要在优化器的搜索框架里找到自己的位置。常见的优化器框架有两种流派一种是基于动态规划的自底向上枚举典型代表是System R的DP算法以及Volcano/Cascades框架另一种是启发式的规则匹配典型的像很多商业数据库优化器里的RBOCBO混合机制。连接条件下推属于典型的规则触发代价抉择场景。更准确地说它的搜索空间是一个谓词P在当前连接树中所有合法的落脚点的集合。理论上如果连接树有N个节点P的下推候选位置最多有N个但实际上因为字段依赖约束和等价性约束真正合法的候选位置往往远小于N。以一条三表连接T1 JOIN T2 ON T1.aT2.a JOIN T3 ON T2.bT3.b为例假设WHERE条件是T1.c 100。这个条件依赖T1.c那么它的合法落脚点包括紧贴在T1扫描节点之上的位置即以T1作为叶子节点时最靠近扫描的位置T1和T2连接之后的位置全连接之后的最顶层从逻辑上说条件放在这三个位置都不改变语义但代价完全不同。放在T1扫描之上可以在扫描阶段就过滤掉大量行T1参与连接的数据量最小化放在T1-T2连接之后意味着T1和T2的完整连接结果都要算出来过滤只影响T3连接时的输入。代价模型的工作就是对三个合法位置分别估算执行代价选择代价最小的那个。这个过程在Cascades框架里通常体现为优化器在探索Explore阶段对物理属性进行枚举并在优化Optimize阶段对每个候选物理计划调用代价函数打分。3.2 我用过的两种实用策略纯代价驱动与代价规则混合纯代价驱动的做法是把所有合法下推位置全部展开逐一进行代价计算最后选择全局最优。这个方案理论最优但代价是搜索开销很大。尤其是当一个谓词能穿过多个连接节点时候选位置爆炸式增长优化器本身的开销可能超过下推带来的收益。对于OLTP型数据库来说这是不可接受的因为OLTP的优化器必须在毫秒级别返回执行计划。所以我在实际项目中更倾向于推荐代价规则混合策略这也是很多商业数据库真正的落地方式。思路是先用RBO rules做安全性和基础形态的裁剪。比如字段不满足依赖约束的谓词直接结束搜索外连接下的高危谓词直接排除。对于通过RBO裁剪后的候选位置按从底向上的顺序依次评估收益函数Gain。评估过程中维护当前最优候选一旦发现某个位置的计算结果使得后续所有位置的收益都不可能超越当前最优利用收益率递减的单调性做剪枝就提前终止搜索。这个混合策略有一个非常实用的效果对于90%以上的常规查询优化器只需要评估一两个候选位置就能给出结论对于极少数复杂查询才会进入多位置的全面搜索。实际线上运行结果也证明这个策略不仅能保证执行计划的质量优化器本身的开销平均只增加了2%左右完全在可接受范围内。3.3 和Join Reorder连接重排的联调问题连接条件下推不是一个孤立的优化规则它和连接重排Join Reorder之间的交互非常密切很多优化器Bug都出在这个交界处。举例T1 JOIN T2的WHERE条件T1.c 100如果优化器先把连接顺序调整为T2 JOIN T1因为T2的选择率更低排在前面做驱动表那么原来针对T1的下推路径不变但如果T1被调整到了Probe侧右侧下推位置仍然应该贴在T1扫描之上只是重新计算收益时要注意行数方向的差异。如果不关心这一点可能下推位置是对的但收益算错了。另一个更隐蔽的问题是下推可能改变原始连接树的输出Schema顺序进而影响上层算子的物理属性选择。比如一个Sort节点的排序列依赖所有参与连接的字段当下推导致某个字段被提前过滤并标记为空值时Sort可能失去排序价值。这种情况在纯理论推导中很容易被忽略但在工程实现中需要为上下层属性传递建立完备的接口。我的联调策略很简单也很有效在代价模型统一出口处维护一个全局映射表记录每个谓词的来源、当前下推层级、以及它依赖的字段集合。任何Join Reorder后的候选计划都必须先经过这个映射表校验确保谓词落脚点仍然合法再进入代价计算流程。这样就把两个优化规则的耦合关系降到了最低。4. 工程落地的核心模块与三个容易被忽略的实现细节4.1 下推候选生成器的设计我在实现连接条件下推模块时把核心分成三个组件谓词分析器、候选位置枚举器、收益计算器。谓词分析器的输入是一棵逻辑执行计划树输出是一组可以被潜在下推的谓词集合。这个阶段主要做的是从每个过滤算子Filter中提取出谓词子句将其拆分成原子谓词然后标记每个原子谓词依赖的字段集合。一个重点处理是对于复合谓词比如A.x 10 AND B.y 20这种跨表的AND组合我会先尝试做拆分让每个子谓词分别走独立的下推决策。因为它们的收益和风险是完全不同的绑定在一起处理只会让代价计算变得模糊。候选位置枚举器负责为每个原子谓词枚举合法的落脚点。我这里的实现逻辑是从谓词当前的最高位置出发沿着连接树向下遍历。每遇到一个节点就检查该节点的输出Schema是否包含谓词依赖的全部字段同时检查连接类型是否允许下推穿过该节点。满足条件的节点加入候选列表直到叶子节点为止。这里有一个非常关键的点候选列表的生成必须保持自上而下的顺序因为后序的收益计算需要依赖位置之间的层级关系。收益计算器就是前面提到的收益公式的工程实现。它需要访问统计信息管理器、连接代价估计器、以及基数估算器。在现代数据仓库场景中这三者的数据来源通常是直方图、HyperLogLog统计、或者采样统计。4.2 容易被忽略的细节一谓词合并与去重来看一个实际场景。用户SQL里写了WHERE T1.c 100而连接条件里恰好有T1.c T2.d。这种情况下如果优化器对两个谓词分别做处理可能会产生重复的下推动作比如先把T1.c 100推到T1上又把T1.c T2.d推到T1上而这两个谓词在同一层其实是等价的重复下推只是在浪费搜索时间。处理方法是在谓词分析阶段做去重和合并。当发现两个谓词语义等价比如通过等价类推导时只保留一个作为下推候选。同时如果新谓词可以通过已有谓词推导出来例如AB且B5可以推导出A5则合并为一个更强的谓词。这一步很大程度上决定了优化器的解析效率和最终执行效率。4.3 容易被忽略的细节二下推时对分区裁剪和索引裁剪的配合连接条件下推的收益在小表上不明显但在大分区表上可能会产生指数级的收益——因为过滤条件的下推意味着分区裁剪提前生效。假设一张按月分区的订单表查询条件WHERE order_date 2024-03-01本来就在表扫描层那没问题。但如果是JOIN之后产生的过滤条件比如内表的分区键需要通过连接条件与外表关联才会被确定那么把等价条件推回到内表扫描层后优化器在物理扫描阶段可以直接跳过一大部分分区文件IO代价瞬间降到几乎为零。这在分布式查询引擎里尤其重要因为每个分区的扫描都可能对应一个远程任务。我在这类场景中强调一个原则连接条件下推的代价模型不能只盯着CPU代价必须把分区裁剪产生的IO收益换算进位成本模型之中。我的做法是在代价函数中对具有分区裁剪潜力的扫描算子设置一个裁剪因子一旦下推动作使裁剪因子生效就直接让该算子的IO代价乘以一个极小的权重系数。4.4 容易被忽略的细节三选择率校准和统计信息失效代价模型的准确性极度依赖选择率的估算。在复杂查询场景中统计信息失效是常态。比如一张表在业务高峰期数据量突然翻倍但统计信息还是几小时前的那么优化器估算出的选择率和真实执行值之间就会出现严重偏差。我在工程中引入了执行期反馈校准机制在实际执行结束之后把真实扫描行数和中间结果行数回写到统计信息缓存中下一次优化器做代价计算时优先使用最近一次真实执行的数据。这种反馈机制虽然简单但能明显缓解统计信息滞后带来的代价估算失真问题。简单查询可能体会不到差别复杂查询场景下它往往是稳定性的救命稻草。5. 实测案例一张宽表关联查询的下推优化复盘5.1 场景描述和原始执行计划为了更好地解释代价模型在真实场景中的表现我构造一个业务上很常见的分析类查询。场景是一个电商平台的数据团队需要统计每个用户在2024年2月份的下单行为和用户基本画像关联输出按年龄段分组的下单汇总。涉及的表如下用户表USERS约500万行包含年龄、性别、注册时间等。订单表ORDERS约1.2亿行包含下单时间、用户ID、订单金额按下单时间做月度分区。订单明细表ORDER_LINEITEMS约3亿行包含订单ID、商品ID、商品数量按订单ID哈希分片。商品表PRODUCTS约50万行包含商品ID、类目ID、供应商ID。这条查询本身并不算极端但连接层级不少USERS和ORDERS先做用户粒度关联再关联LINEITEMS做明细展开最后关联PRODUCTS得到类目信息SUM聚合后按年龄分组。最原始的执行计划长这样简化版Aggregate(group by 年龄) Join(ORDER_LINEITEMS.id ORDER.id, PRODUCTS.id ORDER_LINEITEMS.product_id) Join(USERS.id ORDERS.user_id) Filter(order_time 2024-02-01 AND order_time 2024-03-01) Scan(ORDERS) Scan(USERS) Join(ORDER_LINEITEMS.product_id PRODUCTS.id) Scan(ORDER_LINEITEMS) Scan(PRODUCTS)注意看这个计划里存在一个明显的问题order_time过滤条件已经贴着ORDERS扫描层了但优化器还是没有把订单明细表LINEITEMS和商品表PRODUCTS的数据量过滤下来——因为WHERE条件里没有直接涉及这两张表的谓词。整个查询的中间结果体量由ORDER_LINEITEMS表决定也就是3亿行。这意味着JOIN之后向上传递的数据是巨大的聚合阶段的压力全集中在这里。5.2 代价模型介入后的下推决策针对这个计划连接条件下推的代价模型做了什么核心是发现了一个隐含等价条件下的推机会通过连接条件ORDERS.id ORDER_LINEITEMS.order_id可以把order_time过滤条件在语义上传播到ORDER_LINEITEMS侧。 但直接推order_time是不行的因为ORDER_LINEITEMS没有该字段。正确做法是利用等价类推导把ORDER_LINEITEMS的扫描范围缩小到那些订单属于2024年2月的明细行。在底层实现中这需要把order_time过滤条件下推转化为一个子查询ORDER_LINEITEMS.order_id IN (SELECT id FROM ORDERS WHERE order_time IN 2024-02)。这种改写后ORDER_LINEITEMS扫描可以从3亿行降到大约4500万行假设平均每单3行明细2月份订单量1500万单。代价模型做这个判断的过程是什么呢首先它识别到原始计划的ORDER_LINEITEMS扫描行数是3亿行连接后输出行数近似3亿行。推入子查询改写后扫描行数降到4500万行。收益计算如下扫描成本降低3亿 - 4500万 2.55亿行按单行扫描100ns算节省约25.5秒JOIN探测成本降低JOIN的Probe侧输入从3亿行降到4500万行按每行200ns算节省约51秒后续聚合输入降低聚合算子的输入行数从3亿行降到4500万行按每行150ns算节省约38.25秒这三项相加总收益约为114.75秒远超一开始定下的1秒收益阈值所以优化器毫不犹豫地选择执行这次下推改写。5.3 实测结果和调参过程优化前这条查询在测试环境执行耗时217秒优化后同一查询只需要83秒整体提速2.6倍。最直观的变化是ORDER_LINEITEMS的扫描行数从3亿行降到约4600万行Spill量减少了70%以上。验证过后我又做了几组调参实验希望观察收益阈值对执行计划的影响收益阈值设为1秒时下推动作触发得积极适合查询本身步态复杂、中间结果膨胀严重的场景。 收益阈值设为10秒时下推动作变保守适合查询本身较短、不想为优化本身增加额外开销的场景。 收益阈值设为100秒时几乎所有下推动作都被压制查询表现回到了优化前水平。这个实验告诉我一个很关键的工程结论收益阈值其实应该按查询粒度动态调整而不是一个全局静态值。同一套系统里既有几十毫秒的轻查询也有几十秒的重分析如果统一用同一个阈值必然会伤害某一类查询。比较合理的做法是让代价模型根据优化器估算的查询总代价动态计算阈值比如总代价的1%作为下推触发门槛。6. 调参之外我在多次实战中总结出的三条经验6.1 代价模型的计算粒度宏观收益比微观细节重要很多团队一上手做代价模型就想着把CPU流水线、Cache命中率、指令级并行都建模进去。这个方向不是不对但在连接条件下推这个优化规则上过于精细的建模经常适得其反。原因是下推决策面对的核心变量是行数的数量级变化比如1亿行降到1000万行这是十倍量级的差距此时CPU微架构层面那些10%以内的差异根本不构成决策依据。所以我实际编码时会把UnitCost参数做得很粗糙——先按统一平均行宽估算再按是否有索引或分区裁剪做一次量级修正这足够了。真正值得精细建模的恰恰是选择率和基数估算因为它们直接决定了行数级的变化方向。6.2 统计信息的收集频率必须跟上数据变化连接条件下推依赖选择率选择率依赖统计信息。在离线数仓场景中晚间的ETL任务对同一张表反复写入如果统计信息不随之更新第二天白天分析查询的代价估算就会全偏。我这里有一个很惨痛的线上教训一张大表的统计信息三天没刷新期间行数从2000万涨到了8000万代价模型在多个查询里都低估了扫描代价导致优化器推出来的执行计划普遍比手写SQL更慢业务方一度以为是优化器坏了。后来我做了两件事一是把大表的统计信息收集安排在ETL任务完成后自动触发二是引入前面提到的执行期反馈校准。这套组合实施后统计信息失效导致的性能波动基本消失了。6.3 复杂查询里代价模型要给安全兜底留后门再好的代价模型也免不了估算失误。所以在工程实现中我一直保留一个逃生通道每个下推改写后的执行计划都可以通过优化器开关一键关闭比如设置disable_predicate_pushdowntrue。线上如果出现某条查询因为下推导致性能异常不需要改代码、不用发版直接通过参数关闭这个规则就能恢复。这个后门设计看起来没什么含金量但在大规模集群运维中非常管用。另外我建议团队在监控系统中专门加一条指标每个执行计划中下推谓词的数量和总收益预估。一旦发现某类查询的下推收益预估和实际执行时间明显背离就要启动针对性和选择率校准流程。这是我多次排障下来认为性价比最高的一项监控指标。7. 后续演进方向从连接条件下推到更广泛的算子下推连接条件下推做到一定成熟度后很自然会延伸出两个更有挑战的方向第一个方向是聚合下推。如果连接树下方存在一个可以提前做部分聚合的节点把聚合操作下推到JOIN之前往往能获得比过滤下推更极致的收益。典型的场景是星型模型中的事实表与维度表关联后做GROUP BY如果把GROUP BY的聚合下推到事实表扫描后、JOIN之前可以用Bloom Filter或半连接技术大幅削减维度表关联的数据量。当然聚合下推的语义安全性验证要比过滤下推复杂得多需要处理分组键的等价性等一系列问题。第二个方向是物化视图与下推的结合。当一条查询涉及的连接树正好与某个物化视图匹配时连接条件下推机制可以辅助优化器判断是否应该直接扫描物化视图还是在基表上动态计算。这个方向的代价模型需要补充物化视图的维护代价和查询改写收益是另一个值得深入的课题。我个人认为连接条件下推算是查询优化器里性价比极高的一类优化规则实现难度适中收益直观可见而且几乎没有副作用——只要语义校验和代价计算做得足够稳健。如果你正好在做数据库内核、查询引擎或者数据平台性能优化从这条规则入手打磨代价模型会是投入产出比很高的一条路径。