很多做后端或数仓的朋友可能都遇到过这种场景一条SQL单表查很快一旦JOIN三张以上的大表响应时间从几百毫秒涨到几十秒甚至直接把数据库CPU打满。我这几年的工作基本都花在查询引擎的优化器上“复杂查询性能优化”里有很大一部分精力是在跟多表连接和中间结果膨胀作斗争。今天想聊的“连接条件下推 代价模型”就是这类问题里性价比最高的一套解法。先解释一下这两个词。连接条件下推简单说就是把WHERE过滤条件和JOIN的ON条件尽量挪到连接执行之前让每张表先把自己不需要的数据砍掉再进入连接。代价模型则是优化器用来判断“哪种执行方案更便宜”的计算体系行数估计、CPU开销、内存占用、IO成本都会被折算成一个可比较的数值。两者结合起来优化器就不再靠拍脑袋决定“要不要下推”而是先枚举出候选计划算清楚下推前后的代价差最后挑一个真正快、还不会改变查询语义的执行计划。这篇文章的重点不是堆概念而是把代价模型怎么设计、连接条件下推怎么落地、实测中会踩哪些坑讲透。适合正在做查询引擎开发、大数据平台优化或者玩过几天EXPLAIN后想更进一步的人。下面我会用真实项目里最容易遇到的三表连接场景来拆解。1. 连接条件下推要解决的根本问题中间结果膨胀1.1 一个典型的慢查询长什么样我先用一个很常见的业务SQL来开场三张表分别是客户表customer、订单表orders和订单明细表order_items目标是查出最近一年高价值客户的订单总金额和商品名称。SELECT c.customer_no, SUM(o.amount) AS total_amount, i.item_name FROM customer c JOIN orders o ON c.customer_id o.customer_id JOIN order_items i ON o.order_id i.order_id WHERE c.customer_level VIP AND o.order_time DATE 2024-01-01 AND i.category 3C数码;很多数据平台为了省事会先把三张表JOIN出一个大宽表再在结果上做WHERE过滤。假设customer有1000万行orders有8000万行order_items有1.2亿行三个过滤条件的真实选择率分别是5%、25%和10%。如果不做下推优化器大概率会先按连接键把三张表拼起来生成一个行数可能膨胀到数亿的中间结果然后才在最外层执行三条件过滤这个查询在默认执行计划下能跑到几十秒甚至几分钟。1.2 中间结果膨胀为什么这么致命连接本身的复杂度表面上看是O(NM)但真实执行时每一行都需要做哈希计算、内存操作、可能的随机IO中间结果的行数和字节数直接决定了三个层面上的开销。第一是CPU开销行数越多哈希探测、投影、比较执行的次数越多。第二是内存开销哈希连接需要把内表构建为哈希表中间结果行数大意味着哈希表占用内存大超过内存阈值后就要走落盘路径磁盘读写一介入性能立刻掉一个数量级。第三是后续算子开销中间结果越大传给排序、聚合、窗口函数等算子的输入就越大。以1亿行哈希表为例每个哈希条目占用64字节左右光这个表就要6.4GB内存在常见的内存配额下几乎必然落盘。中间结果膨胀是整个执行计划性能恶化的根源优化器最核心的任务之一就是尽早缩减这个膨胀量。1.3 连接条件下推的本质把过滤动作提前连接条件下推要做的事情并不神秘。上面SQL里三个过滤条件中c.customer_levelVIP只依赖customer表o.order_time 2024-01-01只依赖orders表i.category3C数码只依赖order_items表。它们都应该在各自表扫描完成之后、进入连接之前先执行过滤。需要特别说明的是连接条件下推并不等同于数据库里常说的“谓词下推”。谓词下推通常指把WHERE里的过滤条件挪到扫描层而连接条件下推的范围更宽还包括把JOIN的ON条件中那些“只依赖单侧表”的过滤条件也一并前移。比如ON t1.id t2.id AND t2.status 1这里的t2.status 1完全可以推到t2表扫描之后执行。把这两个动作都纳入下推范畴之后收益才会完整。1.4 一个带数字的收益对比回到上面的场景我们算一下下推前后的连接输入规模。不下推时三张表的原始行数都要进入连接网络参与哈希构建和探测的总行数大约是1.2亿行订单明细加上8000万行订单和1000万行客户全部要在连接算子间流转。而下推之后customer先过滤到50万行orders过滤到2000万行order_items过滤到1200万行。三表连接的顺序可以变成order_items和orders先JOIN输入是1200万 2000万得到中间结果后再和customer JOIN输入大约是1200万 50万。前后一对比参与连接的核心行数从数亿级别降到了几千万级别哈希表内存占用可能从数GB降到几百MB。我见过一个线上案例把i.category下推到订单明细表扫描阶段后中间结果从1.2亿行骤降到300万行左右同样的查询从80秒降到了6秒。这个收益不是靠某个复杂算子实现的只是让数据在源头先瘦了身。2. 代价模型设计优化器如何“算这笔账”2.1 代价不只是一个数字优化器在做“是否下推”决策时不能简单写成“能下推就一定下推”。因为有些条件下推之后反而更慢比如过滤率本来就很低下推引入的额外扫描成本比省下的连接开销还大又比如下推导致原本能用的索引失效。所以我们必须把问题转化成“下推后的计划总代价”和“不下推的计划总代价”之间的比较两者都用同一个代价模型来量化。代价模型通常把查询执行的开销拆成CPU、IO、内存和网络四类。最朴素但实用的公式是这样TotalCost ScanCost JoinCost PredicateCost OutputCost其中ScanCost与扫描的表行数、访问方式有关JoinCost与连接输入行数、连接算法有关PredicateCost是过滤条件的执行开销OutputCost是最终输出和传输的代价。每一类内部再细分比如IO代价分顺序读和随机读CPU代价分每行处理、哈希计算、比较运算。2.2 把代价参数落到可计算的数值上我习惯把代价模型拆成一张参数表每项都对应一个可调整的常数。这里的数值不要求绝对准确但相对关系要对否则优化器会选出反直觉的计划。代价组件常用参数典型初始值说明顺序扫描每页seq_page_cost1.0以一次顺序读页为基准单位随机扫描每页random_page_cost4.0机械盘/SSD参数差异很大CPU每行处理cpu_tuple_cost0.01处理一行基础开销CPU每条件判断cpu_operator_cost0.0025谓词/表达式求值开销哈希构建每行hash_build_cost0.01哈希表的构建哈希探测每行hash_probe_cost0.005探测哈希表开销网络传输每行network_cost0.1分布式/MPP场景才有意义这些数值用好了计划选择就能区分出“扫描后再过滤”和“先JOIN再过滤”的差异。以“过滤一行”为例如果一行在连接阶段要被处理3次每次处理成本是cpu_tuple_cost cpu_operator_cost那么早过滤一行节省的代价大约是3×(0.010.0025)0.0375。看起来很小但乘以千万行级别就非常可观。2.3 基数估计才是代价模型的“地基”代价公式里的所有大项几乎都跟行数成正比行数估计错后面算得再精细也没有意义。这就是为什么“连接条件下推的代价模型”绕不开基数估计。基数估计最常用的输入是直方图、采样统计和唯一值数量。每张表的每列最好都有统计信息包括NULL比例、不同值数量、高频值。过滤条件的选择率可以用满足条件行数占总行数的比例来衡量。比如orders表有8000万行order_time 2024-01-01在直方图里覆盖最近两个季度占比约0.25那么过滤后的行数就是2000万行。这里有个常见的坑如果统计信息缺失优化器往往采用默认选择率比如0.1甚至0.01。我曾遇到一个场景一张表实际过滤率是0.6因为统计信息过期优化器按0.01算误以为条件下推能从1亿行降到100万行实际只能降到6000万行。下推后检查发现效果远不及预期但不推又明显更差最终靠手动更新统计信息才恢复正常。2.4 统计信息不可靠时的兜底策略相关列是另一个容易踩的坑。比如customer表里customer_level和customer_city高度相关如果优化器分别按两个过滤条件的选择率相乘算出先过滤customer_level再到customer_city会只剩0.25%行实际可能还剩30%。对连接条件下推来说这种过度低估会诱使优化器选择非常激进的连接顺序最终执行时行数暴涨计划直接崩掉。兜底策略我建议分三层。第一层是保证统计信息新鲜定task持续更新直方图和高频值。第二层是引入多维统计或者轻量级采样对高相关的过滤列组合做联合估计避免选择率连乘。第三层是在代价模型里加“保守因子”当谓词下推预估的过滤率低于某个阈值时用最坏情况做二次校验。这里的阈值我们内部一般取0.1过滤率预测低于10%时强制回退成更保守的估算。3. 连接条件下推的关键实现环节3.1 先判断条件下推是否合法代价模型决定“划不划算”但在算这笔账之前必须确保条件下推不改变查询语义。这是整个实现里最容易出错的一步。一个谓词能下推到某个连接子树之前至少要满足两个条件它只引用该子树内的表并且把它的执行位置提前不会影响NULL值的产生和过滤语义。后者在LEFT JOIN、RIGHT JOIN、FULL JOIN面前尤其危险。举个例子在LEFT JOIN场景下右表可能产生NULL补充行。如果把这个条件下推到右表扫描之后、连接之前就会把那些本该被补充为NULL的行提前过滤掉最终结果少行语义就错了。判断逻辑上我会先做依赖分析收集谓词里涉及的所有列再检查这些列是否都属于当前连接一侧的所有表。这个检查可以递归完成伪代码大致是这样function canPushdown(predicate, children): cols allReferencedColumns(predicate) for child in children: if cols ⊆ child.schema().columns(): return true return false对于外连接还需要额外检查谓词不是来自“连接下推禁止区”。规则上我的经验是内连接里的谓词几乎都能下推LEFT JOIN右表的谓词不能下推到右表侧WHERE子句中对右表列的过滤可以转化为连接后再过滤但一般不适合直接下推到右表扫描。3.2 生成候选下推计划并计算代价合法性的判断通过之后优化器要做的是把“下推”当成一个可以枚举的物理变换。以我们实现的类Cascades优化器为例连接条件下推的流程分四步。第一步遍历逻辑计划树找出所有连接节点和连接树上方的Filter节点。第二步为每个Filter谓词判断哪些子表满足下推条件生成候选计划即把Filter节点下移并拆分到对应表的扫描节点之上。第三步用代价模型分别计算原计划和候选计划的总代价。第四步保留代价更低的一方如果多种下推组合都存在就选代价最低的那个。这个过程听起来简单但真正的复杂度在于组合爆炸三表连接有3!种连接顺序每个谓词又有多种下推位置可选。因此实际工程里不会把每个组合都完整展开而是用启发式规则先剪枝。比如过滤率低于某个阈值才考虑下推或者只有谓词的列上有索引时才生成下推候选。3.3 代价计算的下推收益公式在实现代价模型时我把下推收益拆成一个可以直接对比的式子。假设原计划中谓词P的执行位置在连接树上方的N个节点之后满足P的行数比例为s那么P下推之前P在每行上的开销要计算一遍同时这N个节点的输入还要包含被过滤掉的行。定义FilterCost为单行谓词判断成本RowCost为每行经过一个连接/投影节点的平均成本则下推省下的总代价约等于Savings ≈ TotalRows × (1 - s) × (N × RowCost FilterCost)同时下推也会引入额外代价比如因为过滤条件可能改变访问路径导致原本的索引扫描变成全表扫描这部分要单独计算线性级或指数级代价差。只有当Savings大于AdditionalCost才值得下推。上面的示例里orders表8000万行s0.25N2RowCost按0.015算FilterCost按0.0025算下推省下的代价大约是8000万×0.75×(2×0.0150.0025)19.5万。而下推带来的额外扫描代价如果只有3万那净收益就很明显。3.4 下推后还要联动连接顺序重排连接条件下推不能孤立运行它和连接顺序的枚举是强耦合的。原因很简单过滤后的表大小不同了内表、外表的取舍也应该重新做。还是上面那个例子如果order_items被过滤到1200万行orders被过滤到2000万行那么让1200万行做哈希构建、2000万行做探测显然比反过来更省内存、更省CPU。如果优化器在生成候选计划时没有把“过滤后的基数变化”回传给连接顺序枚举下推收益就会被连接顺序优化抵消掉不少。我在工程上的做法是先做谓词下推再在Memo结构里重新触发连接顺序相关规则确保下推后的行数估计能实时参与后续枚举。实测中这样联合处理后典型的星型查询能再快10%到20%。4. 实测中的问题与排查技巧实录4.1 下推之后反而变慢的三种情况理论上连接条件下推收益很大实际项目里我却踩过不少“好心办坏事”的坑。第一种是过滤率估计过度乐观。统计信息缺失或过期时优化器按默认选择率0.01甚至更低去算误以为下推能把中间结果砍到很小实际过滤率只有0.7下推省下的代价还没有多出来的扫描代价多。第二种是下推导致访问路径退化。一个谓词下推后本该走二级索引但因为谓词里包含函数或者类型转换索引失效只能全表扫描慢到无法接受。举个典型例子把WHERE date(order_time) 2024-01-01下推后date函数包裹导致无法直接比较原始列索引自然就废了。第三种是下推出来的候选计划过多优化时间本身爆炸。我调试过一个20表连接的大查询光是枚举下推组合就花了40多秒再快的执行计划也被优化时间拖垮了。这类问题在大宽表结构、多星型模型的数仓场景里尤其常见。4.2 排查慢计划时的三条经验遇到慢查询我一般不急着调参数先做三件事。第一看EXPLAIN的行数预估和最终执行行数是否一致偏差超过一个数量级先去查统计信息。第二对比下推前后两版计划的代价总和确认优化器选下推到底是因为“真的省”还是“参数设置导致假省钱”。第三把谓词涉及的列和索引信息打印出来检查是否有类型隐式转换、函数包裹导致索引失效。这三步做完八成的问题原因都能定位到。我还习惯在代码里给代价模型的每个模块加一个debug日志输出类似“谓词P下推到orders扫描估算行数8000万→2000万节省成本19.5万额外成本3万选择下推”这样的记录。这样分析线上问题的时候不再是黑盒每一步决策都有据可查。4.3 用EXPLAIN ANALYZE验证下推效果在开发验证阶段我最常用EXPLAIN ANALYZE来对比下推前后的实际执行。关注三个指标启动时间、执行总时间、以及最内层节点的actual rows和estimated rows。当EST ROWS与ACTUAL ROWS差异明显时说明基数估计有问题。当某一个Scan节点的rows removed by filter数值很大说明过滤确实在扫描层生效了。当HashJoin节点的hashtree维护时间下降说明连接输入变小带来的收益是真实的。把这些指标对比起来看就可以判断下推的收益到底是来自行数减少还是来自索引命中等其他因素。4.4 常见问题速查表现象可能原因排查方法下推后行数减少但执行更慢索引失效扫描路径退化检查谓词列索引、隐式转换预估行数与实际行数差一个量级统计信息过期/缺失更新统计信息检查直方图大查询优化时间过长下推组合爆炸增加启发式剪枝限制候选数量查询结果行数变少外连接谓词被错误下推检查LEFT JOIN下推合法性下推对性能没有提升过滤率太高净收益为负查看实际过滤率提高下推阈值多个高相关过滤列时计划突变统计信息未建模相关列引入联合统计或保守因子这类问题在开发环境很难暴露因为测试数据量小过滤率又往往很整齐。我建议上线前准备一套专门用来“欺负优化器”的回归用例包含低过滤率谓词、外连接过滤、无统计信息表等场景。每次改动代价模型或下推规则先在这套用例上跑一遍能少踩很多坑。5. 实践之后我对代价模型的一点私人体会多轮项目做下来我最大的体会是代价模型的参数宁可欠调也不要一次调太多。连接条件下推的收益高度依赖基数估计而基数估计在真实数据分布下总会有误差。把每个参数的物理含义写清楚比给公式配一堆神秘系数重要得多。另一个体会是统计信息要当成一等公民来维护而不是上线之后想起来才跑一次。凡是出现“同一个查询昨天快今天慢”的情况先检查统计信息刷新再看执行计划变更大概率比调参数管用。最后说下后续扩展。连接条件下推这条路走到后面自然会遇到分布式查询里的Shuffle代价问题那时代价模型还要再加一项网络传输与数据重分布的估算公式体系不变但参数和组合评估的复杂度会明显上升。如果你也在实际项目里遇到过下推决策导致计划劣化的情况欢迎来聊聊具体场景很多坑往往是类似的。