先说一个我实际遇到的场景。线上一个报表查询六张表做连接WHERE 里带了一串看起来很普通的过滤条件结果这个 SQL 每次跑都要 120 多秒把从库 CPU 直接打满。当时第一反应就是“是不是索引没用上”结果检查执行计划索引全走对了问题不在索引而在于优化器把几个复杂连接条件和过滤条件放到了连接完成之后才处理中间结果膨胀到上千万行。这个事让我重新把“连接条件下推”这个看着很基础的概念翻出来认真研究了一遍也是今天想跟你聊的主题。复杂查询里的连接条件下推说白了就是优化器把 WHERE 或 ON 子句里的过滤条件下沉到表扫描阶段执行让数据在源头就被剪掉而不是等表连接完再过滤。这个机制本身不复杂复杂就复杂在“什么时候该下推、什么时候不能下推”这件事上。尤其是连接条件里带了函数运算、JSON 取值、子查询这类复杂表达式时盲目下推可能比不下推更慢。真正靠谱的做法是让优化器基于代价模型去决策而不是靠几条静态规则。这篇文章适合正在折腾 SQL 查询性能、或者自己写 SQL 引擎/优化器玩的人。我会把连接条件下推的前因后果、代价模型的构建方式、以及一个可落地的基于代价的决策实现完整展开最后再分享几个我踩过的坑。1. 连接条件下推到底在解决什么问题1.1 一个让数据库卡死的查询先看一个简化版的问题 SQL它很典型包含了多表连接和复杂过滤条件SELECT o.order_id, u.user_name, p.product_name, pay.amount FROM orders o JOIN users u ON o.user_id u.user_id JOIN products p ON o.product_id p.product_id JOIN payments pay ON o.order_id pay.order_id WHERE o.status PAID AND pay.pay_channel IN (ALIPAY, WECHAT) AND EXISTS ( SELECT 1 FROM order_items oi WHERE oi.order_id o.order_id AND oi.quantity 2 ) AND u.user_level 3;这类查询的麻烦在于EXISTS子查询、IN列表、等值连接条件都混在一起。如果优化器只在连接完成之后才应用过滤那么四个表会先生成笛卡尔积级别的中间结果再逐行去判断那些复杂条件代价非常恐怖。下推的核心思路很朴素把能提前过滤的条件尽量挪到读表的时候就执行。o.status PAID可以在扫描 orders 表时就过滤掉不需要的行u.user_level 3可以在扫描 users 表时就过滤。但问题来了EXISTS子查询能不能下推到order_items扫描阶段如果EXISTS中的条件被下推成了对order_items表的内部过滤那么每个订单号都要去order_items里查一遍这时要不要给order_items建索引、用什么方式访问就变成了一个代价问题。如果子查询本身过滤性很差下推之后反而会让内层执行很多次无效的索引探测消耗比不下推更大。1.2 启发式下推为什么不够用传统优化器的谓词下推是启发式的规则大概长这样只要条件引用的列都在某个表上就把条件推到那个表的扫描节点下方。这条规则对简单条件很有效比如t1.a 5这种推到哪个表都不会有问题只有好处没有坏处。但遇到复杂条件启发式规则就开始失灵了原因主要有两类。第一类是选择性误判。一个条件能不能尽早过滤取决于它的过滤性。比如t1.a IN (SELECT id FROM huge_table WHERE status 1)这种子查询条件如果子查询返回十万行下推到t1的扫描层后相当于每读一行t1都要去判断是否命中十万行中的一个这个判断本身的成本可能远超不下推时的一次 Hash Semi Join。简单规则看不到这层成本因为它只关心“条件引用哪些列”不关心“条件执行一次要多贵”。第二类是执行代价放大。有些复杂表达式比如 JSON 字段取值、正则匹配、自定义函数每次执行可能消耗几十微秒甚至几毫秒。同一条件下推到扫描层后可能在每个被扫描的行上都执行一次不下推的话它只在连接结果集上执行一次。中间结果的行数决定了两种方案的总执行次数差异。如果过滤性不好、行数降不下来下推后总执行次数反而更多性能直接倒退。我看过不少执行计划最典型的一个反例是优化器把一个WHERE regexp_like(user_name, ^张.*)条件推到了 users 表扫描层。这个条件本身选择性还行能过滤掉 80% 的行但因为 users 表上扫了 500 万行正则函数被执行了 500 万次比不下推时只计算 10 万行的总 CPU 时间还多一倍。这就是启发式规则看不见代价导致的倒忙。所以要解决这个问题就不能只看“可不可以下推”必须看“下推之后的总代价是变大还是变小”。这就是基于代价的连接条件下推优化的出发点。2. 代价模型的构建与关键参数2.1 代价从何而来CPU、IO 与内存的取舍要让优化器“权衡”首先得有一个统一的度量单位。几乎所有主流数据库的代价模型都把成本折算成一个无量纲的“代价单位”通常是结合了 IO 时间和 CPU 时间的加权值。PostgreSQL 的代价公式很有代表性也是我比较熟悉的核心大概是这样扫描代价 顺序扫描页数 × seq_page_cost 随机扫描页数 × random_page_cost 扫描行数 × cpu_tuple_cost 表达式计算次数 × cpu_operator_cost 索引访问附加代价其中seq_page_cost和random_page_cost是可调的参数默认随机读比顺序读贵 4 倍这就是为什么优化器宁可多扫一些顺序页也不愿意做大量的随机 IO。连接代价则在上面的基础上累加连接总代价 左输入代价 右输入代价 连接时的行数 × cpu_operator_cost 连接的 Probe/构建 附加代价你注意看一个关键点cpu_operator_cost是按“行数 × 次数”累加的。这意味着一个表达式只要被执行一次代价就会随着它的执行次数线性增长。这就为“基于代价的下推决策”提供了清晰的计算依据把一个条件下推到扫描层如果它导致表达式的执行次数从 10 万次涨到 1000 万次那么即便过滤后行数减少整体代价也可能上升。实际引擎里没这么简单但思路就是这样。做下推决策时不是去算“哪条规则更符合常识”而是去比较两种执行计划的估计代价方案 A将条件下推到扫描层扫描层先过滤再进入连接方案 B保持条件在连接上层执行先完成连接再统一过滤。谁的总代价低谁就是更优策略。2.2 基数估计下推决策的地基代价模型里最敏感、也最容易出错的参数是基数估计。所谓基数就是某个操作会产生多少行。基数估计错了代价模型的整个计算就跟着错优化器很可能做出完全相反的下推决策。PostgreSQL 用pg_class.reltuples和pg_statistic里存储的直方图、最常见值列表、NULL 比例来做单列选择性估计。前提很简单就是列上的数据分布近似均匀。比如status PAID如果统计信息显示status PAID占 60%那么过滤性就是 0.6扫描订单表后从 1000 万行变成 600 万行。但做连接条件下推时经常遇到的是多列条件组合。这时候单列统计信息就hold不住了。比如WHERE o.status PAID AND pay.pay_channel IN (ALIPAY, WECHAT)这两列分别都有不错的过滤性但两列的实际联合分布可能高度相关。假设 90% 的支付宝支付订单都是已支付状态那么先按status过滤再按pay_channel过滤的行数远没有两个独立选择性相乘那么小。优化器如果按独立假设去算就会高估下推的收益把条件推下去之后发现实际过滤效果远不如预期。这就是为什么现代数据库都在做扩展统计信息比如 PostgreSQL 的CREATE STATISTICS能收集多列相关性和函数依赖信息。你在做下推决策时如果发现基数估计老是不准优先怀疑统计信息的覆盖度而不是优化器逻辑本身。还有一类更隐蔽的基数估计问题来自连接条件下的表达式。比如WHERE lower(o.coupon_code) lower(u.promo_code)这种连接条件两列本身的直方图是有的但lower()处理后的分布完全无法直接从原始直方图推算。优化器只能用一个默认的选择性猜测通常是 0.005 或千分之几。如果实际数据的匹配率非常高优化器会严重低估该条件下推后的过滤效果导致该下推的条件没有下推查询性能白白差了一个数量级。2.3 连接顺序与计划空间下推的舞台连接条件下推不是一个独立的动作它跟连接顺序强耦合。同一个 SQL不同的连接顺序会生成完全不同的中间结果集大小同样的条件下推在不同连接顺序下的收益天差地别。传统优化器枚举连接顺序的方式有三种左深树、右深树、浓密树。左深树每次只有一个连接作为下一步的输入右深树则把多个表先构建成一个大哈希表浓密树则允许任何形状。复杂查询的计划空间非常大例如 6 个表全排列加括号法则有上百种连接顺序。基于代价的优化器会在这几百种计划里选择一个总代价最小的。下推决策在连接顺序枚举过程中不是孤立的。一个条件下推到某个底层表后会改变那个表参与连接的输入行数进而改变后续所有连接步骤的代价评估。所以实际引擎里下推决策往往嵌入在动态规划的每一层连接尝试中。举个例子orders表和users表连接时如果把u.user_level 3下推到 users 扫描层users 输入从 500 万行变成 20 万行那么后面所有跟 users 的连接代价都会大幅降低这可能会改变最优连接顺序的选择。反过来说如果优化器先固定了连接顺序再去决定下推很多潜在收益就已经错过了。这就引出了一个工程实现的关键点下推决策要在计划搜索的过程中做而不是计划搜索结束后做。我见过一些自研引擎的实现先用鸡蛋原则把条件推到叶子节点再去枚举连接顺序看起来省事但实际上会严重限制计划空间。正确做法是在动态规划的每一层都保留一个“可下推条件池”根据当前子树的行数估计去评估每个条件是否值得下沉。3. 实战一个基于代价的下推决策实现3.1 把下推问题建模成代价比较聊完原理我们进入实战。假设你要在一个已有的 SQL 引擎里实现“基于代价的连接条件下推”第一步是把决策过程建模成代价比较。对任意一个条件p它可能会被下推到某个连接输入节点S可能是一张表也可能是一棵已连接好的子树。我们需要计算两个计划变体的代价C_above条件留在连接节点上方执行时的总代价C_below条件下推到S扫描层执行时的总代价如果C_below C_above下推可行否则保持原样。代价的差异主要来自两部分。上行执行时条件会对连接结果的所有行计算一次下行执行时条件会对S的扫描行计算但过滤掉一部分行后连接节点处理的输入行数会减少进而影响连接算法的选择和总消耗。我整理了一个简化对比假设某个条件计算一次的成本是op_cost它如果下推能过滤掉filter_rate的行场景一不下推 - 连接输入行数以此为条件计算基数N - 条件计算总开销N × op_cost - 连接处理行数N - 总代价N × op_cost join_cost(N) 场景二下推到扫描层 - 扫描行数MM N因为扫描层能看到更多原始行 - 条件计算总开销M × op_cost - 扫描层过滤后行数M × (1 - filter_rate) - 连接处理行数约 M × (1 - filter_rate) - 总代价M × op_cost join_cost(M × (1 - filter_rate))乍一看下推会导致条件计算次数从 N 变成 M似乎一定更贵。但别忘了join_cost通常是输入行数的超线性函数尤其是嵌套循环连接成本会随着输入行数平方增长。如果join_cost(M × (1 - filter_rate))远小于join_cost(N)那么即便条件多算了几次总量的节省依然十分可观。实际做决策的时候优化器不会真的把两个变体的完整执行计划都生成出来再比较那样计算量太大了。常用的做法是贪心式评估对每个候选条件估算它在当前节点下方执行和上方执行的代价差值把差值最大的几个条件下沉然后更新节点的基数估计继续迭代。下面是用 Python 风格的伪代码写的一个决策函数方便你理解这个过程def should_pushdown(join_node, predicate, stats): 判断一个谓词是否应该下推到 join_node 的某个输入侧。 返回 (是否下推, 预估收益) # 获取连接节点当前输入行数的估计 left_rows stats.estimate_rows(join_node.left) right_rows stats.estimate_rows(join_node.right) output_rows stats.estimate_join_output(join_node.left, join_node.right, join_node.condition) # 条件留在上方执行所有连接输出行都要算一次 cost_above output_rows * stats.cpu_operator_cost default_join_cost(left_rows, right_rows) # 假设把条件下推到左侧叶子节点 target_rows stats.estimate_rows(join_node.left) filtered_rows stats.estimate_filtered_rows(target_rows, predicate) cost_below target_rows * stats.cpu_operator_cost # 扫描层多算的条件代价 cost_below default_join_cost(filtered_rows, right_rows) # 注意如果下推后需要补偿谓词还要加上补偿代价 if predicate.is_partial_pushdown: cost_below filtered_rows * predicate.compensation_cost gain cost_above - cost_below return gain 0, gain我特意在伪代码里加了一个is_partial_pushdown的判断。实际上很多复杂条件下推是不彻底的比如条件里同时引用了两个表的列就不能直接下沉给任何一个单独表。这时优化器会把条件拆成两部分一部分下沉到表 A另一部分作为补偿谓词保留在连接上方。补偿谓词虽然多了一次计算但如果下沉的部分能过滤掉大量行整体依然划算。3.2 实现步骤从统计信息到最终执行计划在实际引擎里实现这个流程工程上要处理的细节比我上面伪代码多得多。我把整个流程拆成五步每一步都有明确的输入和输出。第一步从查询解析树里提取可下推条件池。这一步要做的是把 WHERE 和 ON 子句拆分成一个个原子的谓词比如o.status PAID是一个原子谓词EXISTS (SELECT...)也是一个原子谓词。注意拆分时要遵守逻辑等价原则AND 连接的条件可以自由拆分OR 连接的条件不能随便拆否则会破坏语义。第二步对每个候选条件做“可下推性分析”。条件引用的列必须全部来自同一个连接输入侧才能整体下推如果涉及双侧列尝试拆分出单侧部分。这一步还要识别表达式里有没有非确定函数比如random()、now()这种条件下推会改变执行结果必须禁止下推。第三步估算每个条件下推前后的行数变化。调用统计信息管理器用直方图和最常见值来算选择性。如果条件里包含表达式比如lower(coupon_code) abc就要在表达式派生列上做估算或者回退到默认选择性。第四步对每个候选条件执行代价比较。用上一节的should_pushdown逻辑把每条候选条件的收益算出来。收益排序后优先下推收益大的条件每下推一个条件就更新节点行数估计再评估下一个条件因为前面条件的下推会改变后续条件的基准行数。第五步生成最终的执行计划。将下推后的条件封装成扫描节点上的 filter 表达式同时更新连接节点的预估输出行数把新的计划送入执行器。这五个步骤说起来简单但每一步都有很多边界情况。比如第四步如果两个条件之间存在相关性比如a 100 AND a 200分别独立评估收益会高估总体收益因为第二个条件基于第一个条件过滤后的行数来计算实际收益。我在实现时加了简单的“条件相关度检测”如果两个条件引用同一列就合并成联合条件统一评估。3.3 实测对比三套方案的结果差异为了验证基于代价的下推到底值不值得做我做了一个对照实验。模拟了类似文章开头那个多表连接查询数据规模是订单表 1000 万行、商品表 500 万行、用户表 500 万行、支付表 1200 万行连接条件里有一个EXISTS子查询、一个 JSON 筛选、一个IN列表。实验分三组方案执行策略执行时间中间结果峰值行数说明方案一不下推所有条件在连接完成后统一过滤126.4s约 3400 万行CPU 全部耗在关联中间结果上方案二启发式全下推凡是能下推的条件无条件推到扫描层198.7s约 4.8 万行中间结果小了但代价反而更高方案三基于代价下推只下推收益为正的条件其余保留在上层48.2s约 150 万行均衡了过滤收益与重复计算开销方案二是最有意思的反例。它把所有条件都推到了扫描层中间结果确实从 3400 万行降到了 4.8 万行但执行时间反而比不下推还慢了 70 多秒。原因是我故意选了一个 JSON 筛选条件和正则匹配条件这些表达式在扫描层要对几百万原始行重复计算总 CPU 开销远大于减少连接行数带来的收益。方案三则只下推了过滤性强的status、pay_channel两个简单条件把 JSON 和正则相关条件留在上层计算整个查询的执行时间降到 50 秒以内。这个实验给了我很深的印象下推优化追求的不只是“过滤得越早越好”而是“总成本最低”。理论上的代价模型和实际执行时间的相关性非常高。4. 常见问题与排查技巧实录4.1 统计信息过期导致下推误判真实环境中最常见的问题就是统计信息没跟上数据变化。之前我维护的一个订单表每天流水新增上百万行但自动分析任务只配置了凌晨两点跑一次。白天做报表查询时优化器拿到的还是昨天的行数估计以为某张表只有 500 万行实际上已经涨到 2000 万行。这个误差直接导致下推误判优化器认为条件下推后扫描层能过滤掉 90% 的数据实际上因为表行数爆炸扫描层真实过滤效果不到 30%执行计划走了糟糕的嵌套循环连接。排查这类问题第一步就看执行计划里每个节点的rows和actual rows是否差异巨大。如果估计行数和实际行数差了好几倍先不要怀疑优化器算法果断重新收集统计信息。在 PostgreSQL 里可以针对单表手动ANALYZE TABLE在 MySQL 里可以ANALYZE TABLE在自研引擎里则需要检查统计信息的采集周期和采样比例。多列相关性的统计信息尤其容易被忽略我建议对经常联合过滤的列组显式创建扩展统计信息。4.2 复杂表达式的选择性估算偏差复杂条件下推最让优化器头疼的是对函数表达式选择性的估算。o.created_at now() - interval 7 days这种条件还能靠数据分布猜一猜regexp_like(user_name, ^[张王李])这种就完全没法从直方图推出来了。很多优化器碰到这种情况会回退到一个硬编码的默认选择性比如 0.005 或者 0.01。这个默认值的选择非常讲究。取值太低优化器会高估下推收益把收益不大的条件也推到扫描层导致重复计算暴增取值太高会低估下推收益让本该下推的高过滤性条件停留在上层导致中间结果膨胀。我自己实践下来的经验是表达式条件如果没法估算优先保持不下推让它留在连接完成之后执行。因为复杂表达式的单次计算成本通常很高下推后如果执行次数翻几十倍性能损失非常明显。宁可让中间结果大一点也别让扫描层疯狂跑复杂函数。等到侧面数据证明某个表达式过滤性真的很强时再通过 hint 或者自定义统计信息干预。4.3 下推后的执行计划“反直觉”即使基于代价模型算出来该下推实际执行时也可能因为执行器行为跟想象不一样而翻车。最典型是我遇到的一次优化器把一个IN (SELECT...)条件下推到了右表扫描层理论上应该减少连接输入但实际执行时右表扫描层对每一行都执行了一次子查询子查询又触发了索引回表。结果扫描层耗时从原来的 2 秒变成 20 秒。这种情况不是代价模型算错了而是代价模型对“子查询执行一次的真实开销”估计得太乐观。索引探测看似只增加了几个 IO实际在缓冲池未命中的情况下一次随机探测可能要走两三次磁盘 IO比全表扫描顺序读还要慢。排查手段很简单打开EXPLAIN ANALYZE看执行计划里每个 filter 节点的实际执行次数和平均耗时。如果发现某个下推条件的执行次数远超预估最简单的补救是关闭该条件下的下推让它回到上层做 hash 半连接。PostgreSQL 里可以SET LOCAL enable_hashjoin off来强迫执行器用另一种计划形态但这只是临时手段最终还是要修正代价模型中索引探测的开销参数。4.4 快速排查速查表这几个月调试下来我整理了一张速查表遇到连接条件下推相关的性能问题按这个顺序排查基本能定位现象可能根因排查手段执行计划的估计行数与实际行数差距巨大统计信息过期或采样不足重新 ANALYZE检查采样比例下推后扫描层 CPU 显著升高复杂表达式在扫描层被重复计算查看 filter 节点实际执行次数考虑取消该条件下推连接选择被错误地切换为嵌套循环单侧输入基数被严重高估检查连接顺序枚举必要时固定 join 顺序多列条件联合过滤效果差列间相关性导致独立假设失真创建多列扩展统计信息子查询条件下推后回表次数暴增索引探测成本被低估调整随机 IO 成本参数或用 semi-join 替代下推后结果集正确但性能无提升条件本身过滤性太弱用真实数据跑一次不同计划的对比实验最后分享一个我自己一直沿用的习惯。每做一个复杂查询的性能优化我会将优化前的执行计划、优化后的执行计划、以及每一步条件的预估行数和实际行数存档。下次再遇到类似的慢查询直接翻历史案例对照比重新调优快得多。基于代价的下推优化不是一次性能解决所有问题它是一个持续根据真实执行反馈修正估计模型的过程。优化器没有万能药但只要你把代价模型的每个关键数字都搞透复杂查询也能稳定跑出好成绩。
连接条件下推的代价决策：从启发式规则到基于代价的SQL优化实践
先说一个我实际遇到的场景。线上一个报表查询六张表做连接WHERE 里带了一串看起来很普通的过滤条件结果这个 SQL 每次跑都要 120 多秒把从库 CPU 直接打满。当时第一反应就是“是不是索引没用上”结果检查执行计划索引全走对了问题不在索引而在于优化器把几个复杂连接条件和过滤条件放到了连接完成之后才处理中间结果膨胀到上千万行。这个事让我重新把“连接条件下推”这个看着很基础的概念翻出来认真研究了一遍也是今天想跟你聊的主题。复杂查询里的连接条件下推说白了就是优化器把 WHERE 或 ON 子句里的过滤条件下沉到表扫描阶段执行让数据在源头就被剪掉而不是等表连接完再过滤。这个机制本身不复杂复杂就复杂在“什么时候该下推、什么时候不能下推”这件事上。尤其是连接条件里带了函数运算、JSON 取值、子查询这类复杂表达式时盲目下推可能比不下推更慢。真正靠谱的做法是让优化器基于代价模型去决策而不是靠几条静态规则。这篇文章适合正在折腾 SQL 查询性能、或者自己写 SQL 引擎/优化器玩的人。我会把连接条件下推的前因后果、代价模型的构建方式、以及一个可落地的基于代价的决策实现完整展开最后再分享几个我踩过的坑。1. 连接条件下推到底在解决什么问题1.1 一个让数据库卡死的查询先看一个简化版的问题 SQL它很典型包含了多表连接和复杂过滤条件SELECT o.order_id, u.user_name, p.product_name, pay.amount FROM orders o JOIN users u ON o.user_id u.user_id JOIN products p ON o.product_id p.product_id JOIN payments pay ON o.order_id pay.order_id WHERE o.status PAID AND pay.pay_channel IN (ALIPAY, WECHAT) AND EXISTS ( SELECT 1 FROM order_items oi WHERE oi.order_id o.order_id AND oi.quantity 2 ) AND u.user_level 3;这类查询的麻烦在于EXISTS子查询、IN列表、等值连接条件都混在一起。如果优化器只在连接完成之后才应用过滤那么四个表会先生成笛卡尔积级别的中间结果再逐行去判断那些复杂条件代价非常恐怖。下推的核心思路很朴素把能提前过滤的条件尽量挪到读表的时候就执行。o.status PAID可以在扫描 orders 表时就过滤掉不需要的行u.user_level 3可以在扫描 users 表时就过滤。但问题来了EXISTS子查询能不能下推到order_items扫描阶段如果EXISTS中的条件被下推成了对order_items表的内部过滤那么每个订单号都要去order_items里查一遍这时要不要给order_items建索引、用什么方式访问就变成了一个代价问题。如果子查询本身过滤性很差下推之后反而会让内层执行很多次无效的索引探测消耗比不下推更大。1.2 启发式下推为什么不够用传统优化器的谓词下推是启发式的规则大概长这样只要条件引用的列都在某个表上就把条件推到那个表的扫描节点下方。这条规则对简单条件很有效比如t1.a 5这种推到哪个表都不会有问题只有好处没有坏处。但遇到复杂条件启发式规则就开始失灵了原因主要有两类。第一类是选择性误判。一个条件能不能尽早过滤取决于它的过滤性。比如t1.a IN (SELECT id FROM huge_table WHERE status 1)这种子查询条件如果子查询返回十万行下推到t1的扫描层后相当于每读一行t1都要去判断是否命中十万行中的一个这个判断本身的成本可能远超不下推时的一次 Hash Semi Join。简单规则看不到这层成本因为它只关心“条件引用哪些列”不关心“条件执行一次要多贵”。第二类是执行代价放大。有些复杂表达式比如 JSON 字段取值、正则匹配、自定义函数每次执行可能消耗几十微秒甚至几毫秒。同一条件下推到扫描层后可能在每个被扫描的行上都执行一次不下推的话它只在连接结果集上执行一次。中间结果的行数决定了两种方案的总执行次数差异。如果过滤性不好、行数降不下来下推后总执行次数反而更多性能直接倒退。我看过不少执行计划最典型的一个反例是优化器把一个WHERE regexp_like(user_name, ^张.*)条件推到了 users 表扫描层。这个条件本身选择性还行能过滤掉 80% 的行但因为 users 表上扫了 500 万行正则函数被执行了 500 万次比不下推时只计算 10 万行的总 CPU 时间还多一倍。这就是启发式规则看不见代价导致的倒忙。所以要解决这个问题就不能只看“可不可以下推”必须看“下推之后的总代价是变大还是变小”。这就是基于代价的连接条件下推优化的出发点。2. 代价模型的构建与关键参数2.1 代价从何而来CPU、IO 与内存的取舍要让优化器“权衡”首先得有一个统一的度量单位。几乎所有主流数据库的代价模型都把成本折算成一个无量纲的“代价单位”通常是结合了 IO 时间和 CPU 时间的加权值。PostgreSQL 的代价公式很有代表性也是我比较熟悉的核心大概是这样扫描代价 顺序扫描页数 × seq_page_cost 随机扫描页数 × random_page_cost 扫描行数 × cpu_tuple_cost 表达式计算次数 × cpu_operator_cost 索引访问附加代价其中seq_page_cost和random_page_cost是可调的参数默认随机读比顺序读贵 4 倍这就是为什么优化器宁可多扫一些顺序页也不愿意做大量的随机 IO。连接代价则在上面的基础上累加连接总代价 左输入代价 右输入代价 连接时的行数 × cpu_operator_cost 连接的 Probe/构建 附加代价你注意看一个关键点cpu_operator_cost是按“行数 × 次数”累加的。这意味着一个表达式只要被执行一次代价就会随着它的执行次数线性增长。这就为“基于代价的下推决策”提供了清晰的计算依据把一个条件下推到扫描层如果它导致表达式的执行次数从 10 万次涨到 1000 万次那么即便过滤后行数减少整体代价也可能上升。实际引擎里没这么简单但思路就是这样。做下推决策时不是去算“哪条规则更符合常识”而是去比较两种执行计划的估计代价方案 A将条件下推到扫描层扫描层先过滤再进入连接方案 B保持条件在连接上层执行先完成连接再统一过滤。谁的总代价低谁就是更优策略。2.2 基数估计下推决策的地基代价模型里最敏感、也最容易出错的参数是基数估计。所谓基数就是某个操作会产生多少行。基数估计错了代价模型的整个计算就跟着错优化器很可能做出完全相反的下推决策。PostgreSQL 用pg_class.reltuples和pg_statistic里存储的直方图、最常见值列表、NULL 比例来做单列选择性估计。前提很简单就是列上的数据分布近似均匀。比如status PAID如果统计信息显示status PAID占 60%那么过滤性就是 0.6扫描订单表后从 1000 万行变成 600 万行。但做连接条件下推时经常遇到的是多列条件组合。这时候单列统计信息就hold不住了。比如WHERE o.status PAID AND pay.pay_channel IN (ALIPAY, WECHAT)这两列分别都有不错的过滤性但两列的实际联合分布可能高度相关。假设 90% 的支付宝支付订单都是已支付状态那么先按status过滤再按pay_channel过滤的行数远没有两个独立选择性相乘那么小。优化器如果按独立假设去算就会高估下推的收益把条件推下去之后发现实际过滤效果远不如预期。这就是为什么现代数据库都在做扩展统计信息比如 PostgreSQL 的CREATE STATISTICS能收集多列相关性和函数依赖信息。你在做下推决策时如果发现基数估计老是不准优先怀疑统计信息的覆盖度而不是优化器逻辑本身。还有一类更隐蔽的基数估计问题来自连接条件下的表达式。比如WHERE lower(o.coupon_code) lower(u.promo_code)这种连接条件两列本身的直方图是有的但lower()处理后的分布完全无法直接从原始直方图推算。优化器只能用一个默认的选择性猜测通常是 0.005 或千分之几。如果实际数据的匹配率非常高优化器会严重低估该条件下推后的过滤效果导致该下推的条件没有下推查询性能白白差了一个数量级。2.3 连接顺序与计划空间下推的舞台连接条件下推不是一个独立的动作它跟连接顺序强耦合。同一个 SQL不同的连接顺序会生成完全不同的中间结果集大小同样的条件下推在不同连接顺序下的收益天差地别。传统优化器枚举连接顺序的方式有三种左深树、右深树、浓密树。左深树每次只有一个连接作为下一步的输入右深树则把多个表先构建成一个大哈希表浓密树则允许任何形状。复杂查询的计划空间非常大例如 6 个表全排列加括号法则有上百种连接顺序。基于代价的优化器会在这几百种计划里选择一个总代价最小的。下推决策在连接顺序枚举过程中不是孤立的。一个条件下推到某个底层表后会改变那个表参与连接的输入行数进而改变后续所有连接步骤的代价评估。所以实际引擎里下推决策往往嵌入在动态规划的每一层连接尝试中。举个例子orders表和users表连接时如果把u.user_level 3下推到 users 扫描层users 输入从 500 万行变成 20 万行那么后面所有跟 users 的连接代价都会大幅降低这可能会改变最优连接顺序的选择。反过来说如果优化器先固定了连接顺序再去决定下推很多潜在收益就已经错过了。这就引出了一个工程实现的关键点下推决策要在计划搜索的过程中做而不是计划搜索结束后做。我见过一些自研引擎的实现先用鸡蛋原则把条件推到叶子节点再去枚举连接顺序看起来省事但实际上会严重限制计划空间。正确做法是在动态规划的每一层都保留一个“可下推条件池”根据当前子树的行数估计去评估每个条件是否值得下沉。3. 实战一个基于代价的下推决策实现3.1 把下推问题建模成代价比较聊完原理我们进入实战。假设你要在一个已有的 SQL 引擎里实现“基于代价的连接条件下推”第一步是把决策过程建模成代价比较。对任意一个条件p它可能会被下推到某个连接输入节点S可能是一张表也可能是一棵已连接好的子树。我们需要计算两个计划变体的代价C_above条件留在连接节点上方执行时的总代价C_below条件下推到S扫描层执行时的总代价如果C_below C_above下推可行否则保持原样。代价的差异主要来自两部分。上行执行时条件会对连接结果的所有行计算一次下行执行时条件会对S的扫描行计算但过滤掉一部分行后连接节点处理的输入行数会减少进而影响连接算法的选择和总消耗。我整理了一个简化对比假设某个条件计算一次的成本是op_cost它如果下推能过滤掉filter_rate的行场景一不下推 - 连接输入行数以此为条件计算基数N - 条件计算总开销N × op_cost - 连接处理行数N - 总代价N × op_cost join_cost(N) 场景二下推到扫描层 - 扫描行数MM N因为扫描层能看到更多原始行 - 条件计算总开销M × op_cost - 扫描层过滤后行数M × (1 - filter_rate) - 连接处理行数约 M × (1 - filter_rate) - 总代价M × op_cost join_cost(M × (1 - filter_rate))乍一看下推会导致条件计算次数从 N 变成 M似乎一定更贵。但别忘了join_cost通常是输入行数的超线性函数尤其是嵌套循环连接成本会随着输入行数平方增长。如果join_cost(M × (1 - filter_rate))远小于join_cost(N)那么即便条件多算了几次总量的节省依然十分可观。实际做决策的时候优化器不会真的把两个变体的完整执行计划都生成出来再比较那样计算量太大了。常用的做法是贪心式评估对每个候选条件估算它在当前节点下方执行和上方执行的代价差值把差值最大的几个条件下沉然后更新节点的基数估计继续迭代。下面是用 Python 风格的伪代码写的一个决策函数方便你理解这个过程def should_pushdown(join_node, predicate, stats): 判断一个谓词是否应该下推到 join_node 的某个输入侧。 返回 (是否下推, 预估收益) # 获取连接节点当前输入行数的估计 left_rows stats.estimate_rows(join_node.left) right_rows stats.estimate_rows(join_node.right) output_rows stats.estimate_join_output(join_node.left, join_node.right, join_node.condition) # 条件留在上方执行所有连接输出行都要算一次 cost_above output_rows * stats.cpu_operator_cost default_join_cost(left_rows, right_rows) # 假设把条件下推到左侧叶子节点 target_rows stats.estimate_rows(join_node.left) filtered_rows stats.estimate_filtered_rows(target_rows, predicate) cost_below target_rows * stats.cpu_operator_cost # 扫描层多算的条件代价 cost_below default_join_cost(filtered_rows, right_rows) # 注意如果下推后需要补偿谓词还要加上补偿代价 if predicate.is_partial_pushdown: cost_below filtered_rows * predicate.compensation_cost gain cost_above - cost_below return gain 0, gain我特意在伪代码里加了一个is_partial_pushdown的判断。实际上很多复杂条件下推是不彻底的比如条件里同时引用了两个表的列就不能直接下沉给任何一个单独表。这时优化器会把条件拆成两部分一部分下沉到表 A另一部分作为补偿谓词保留在连接上方。补偿谓词虽然多了一次计算但如果下沉的部分能过滤掉大量行整体依然划算。3.2 实现步骤从统计信息到最终执行计划在实际引擎里实现这个流程工程上要处理的细节比我上面伪代码多得多。我把整个流程拆成五步每一步都有明确的输入和输出。第一步从查询解析树里提取可下推条件池。这一步要做的是把 WHERE 和 ON 子句拆分成一个个原子的谓词比如o.status PAID是一个原子谓词EXISTS (SELECT...)也是一个原子谓词。注意拆分时要遵守逻辑等价原则AND 连接的条件可以自由拆分OR 连接的条件不能随便拆否则会破坏语义。第二步对每个候选条件做“可下推性分析”。条件引用的列必须全部来自同一个连接输入侧才能整体下推如果涉及双侧列尝试拆分出单侧部分。这一步还要识别表达式里有没有非确定函数比如random()、now()这种条件下推会改变执行结果必须禁止下推。第三步估算每个条件下推前后的行数变化。调用统计信息管理器用直方图和最常见值来算选择性。如果条件里包含表达式比如lower(coupon_code) abc就要在表达式派生列上做估算或者回退到默认选择性。第四步对每个候选条件执行代价比较。用上一节的should_pushdown逻辑把每条候选条件的收益算出来。收益排序后优先下推收益大的条件每下推一个条件就更新节点行数估计再评估下一个条件因为前面条件的下推会改变后续条件的基准行数。第五步生成最终的执行计划。将下推后的条件封装成扫描节点上的 filter 表达式同时更新连接节点的预估输出行数把新的计划送入执行器。这五个步骤说起来简单但每一步都有很多边界情况。比如第四步如果两个条件之间存在相关性比如a 100 AND a 200分别独立评估收益会高估总体收益因为第二个条件基于第一个条件过滤后的行数来计算实际收益。我在实现时加了简单的“条件相关度检测”如果两个条件引用同一列就合并成联合条件统一评估。3.3 实测对比三套方案的结果差异为了验证基于代价的下推到底值不值得做我做了一个对照实验。模拟了类似文章开头那个多表连接查询数据规模是订单表 1000 万行、商品表 500 万行、用户表 500 万行、支付表 1200 万行连接条件里有一个EXISTS子查询、一个 JSON 筛选、一个IN列表。实验分三组方案执行策略执行时间中间结果峰值行数说明方案一不下推所有条件在连接完成后统一过滤126.4s约 3400 万行CPU 全部耗在关联中间结果上方案二启发式全下推凡是能下推的条件无条件推到扫描层198.7s约 4.8 万行中间结果小了但代价反而更高方案三基于代价下推只下推收益为正的条件其余保留在上层48.2s约 150 万行均衡了过滤收益与重复计算开销方案二是最有意思的反例。它把所有条件都推到了扫描层中间结果确实从 3400 万行降到了 4.8 万行但执行时间反而比不下推还慢了 70 多秒。原因是我故意选了一个 JSON 筛选条件和正则匹配条件这些表达式在扫描层要对几百万原始行重复计算总 CPU 开销远大于减少连接行数带来的收益。方案三则只下推了过滤性强的status、pay_channel两个简单条件把 JSON 和正则相关条件留在上层计算整个查询的执行时间降到 50 秒以内。这个实验给了我很深的印象下推优化追求的不只是“过滤得越早越好”而是“总成本最低”。理论上的代价模型和实际执行时间的相关性非常高。4. 常见问题与排查技巧实录4.1 统计信息过期导致下推误判真实环境中最常见的问题就是统计信息没跟上数据变化。之前我维护的一个订单表每天流水新增上百万行但自动分析任务只配置了凌晨两点跑一次。白天做报表查询时优化器拿到的还是昨天的行数估计以为某张表只有 500 万行实际上已经涨到 2000 万行。这个误差直接导致下推误判优化器认为条件下推后扫描层能过滤掉 90% 的数据实际上因为表行数爆炸扫描层真实过滤效果不到 30%执行计划走了糟糕的嵌套循环连接。排查这类问题第一步就看执行计划里每个节点的rows和actual rows是否差异巨大。如果估计行数和实际行数差了好几倍先不要怀疑优化器算法果断重新收集统计信息。在 PostgreSQL 里可以针对单表手动ANALYZE TABLE在 MySQL 里可以ANALYZE TABLE在自研引擎里则需要检查统计信息的采集周期和采样比例。多列相关性的统计信息尤其容易被忽略我建议对经常联合过滤的列组显式创建扩展统计信息。4.2 复杂表达式的选择性估算偏差复杂条件下推最让优化器头疼的是对函数表达式选择性的估算。o.created_at now() - interval 7 days这种条件还能靠数据分布猜一猜regexp_like(user_name, ^[张王李])这种就完全没法从直方图推出来了。很多优化器碰到这种情况会回退到一个硬编码的默认选择性比如 0.005 或者 0.01。这个默认值的选择非常讲究。取值太低优化器会高估下推收益把收益不大的条件也推到扫描层导致重复计算暴增取值太高会低估下推收益让本该下推的高过滤性条件停留在上层导致中间结果膨胀。我自己实践下来的经验是表达式条件如果没法估算优先保持不下推让它留在连接完成之后执行。因为复杂表达式的单次计算成本通常很高下推后如果执行次数翻几十倍性能损失非常明显。宁可让中间结果大一点也别让扫描层疯狂跑复杂函数。等到侧面数据证明某个表达式过滤性真的很强时再通过 hint 或者自定义统计信息干预。4.3 下推后的执行计划“反直觉”即使基于代价模型算出来该下推实际执行时也可能因为执行器行为跟想象不一样而翻车。最典型是我遇到的一次优化器把一个IN (SELECT...)条件下推到了右表扫描层理论上应该减少连接输入但实际执行时右表扫描层对每一行都执行了一次子查询子查询又触发了索引回表。结果扫描层耗时从原来的 2 秒变成 20 秒。这种情况不是代价模型算错了而是代价模型对“子查询执行一次的真实开销”估计得太乐观。索引探测看似只增加了几个 IO实际在缓冲池未命中的情况下一次随机探测可能要走两三次磁盘 IO比全表扫描顺序读还要慢。排查手段很简单打开EXPLAIN ANALYZE看执行计划里每个 filter 节点的实际执行次数和平均耗时。如果发现某个下推条件的执行次数远超预估最简单的补救是关闭该条件下的下推让它回到上层做 hash 半连接。PostgreSQL 里可以SET LOCAL enable_hashjoin off来强迫执行器用另一种计划形态但这只是临时手段最终还是要修正代价模型中索引探测的开销参数。4.4 快速排查速查表这几个月调试下来我整理了一张速查表遇到连接条件下推相关的性能问题按这个顺序排查基本能定位现象可能根因排查手段执行计划的估计行数与实际行数差距巨大统计信息过期或采样不足重新 ANALYZE检查采样比例下推后扫描层 CPU 显著升高复杂表达式在扫描层被重复计算查看 filter 节点实际执行次数考虑取消该条件下推连接选择被错误地切换为嵌套循环单侧输入基数被严重高估检查连接顺序枚举必要时固定 join 顺序多列条件联合过滤效果差列间相关性导致独立假设失真创建多列扩展统计信息子查询条件下推后回表次数暴增索引探测成本被低估调整随机 IO 成本参数或用 semi-join 替代下推后结果集正确但性能无提升条件本身过滤性太弱用真实数据跑一次不同计划的对比实验最后分享一个我自己一直沿用的习惯。每做一个复杂查询的性能优化我会将优化前的执行计划、优化后的执行计划、以及每一步条件的预估行数和实际行数存档。下次再遇到类似的慢查询直接翻历史案例对照比重新调优快得多。基于代价的下推优化不是一次性能解决所有问题它是一个持续根据真实执行反馈修正估计模型的过程。优化器没有万能药但只要你把代价模型的每个关键数字都搞透复杂查询也能稳定跑出好成绩。
