尧图网页设计 
我接手过不少慢查询优化其中印象最深的一次问题不是出在索引缺失也不是SQL写得太烂而是优化器在“连接条件下推”这件事上做了一次代价博弈——它认为“不该推”结果查询跑了整整37秒。这条SQL本身一点不复杂三张表关联加两个过滤条件任何程序员一看都知道该先过滤再关联。但数据库偏不。深入研究执行计划之后我才把“基于代价的连接条件下推”这条优化链路彻底吃透什么时候优化器会推、为什么有时候宁可不推、统计信息怎么影响决策、以及我们DBA能干预的空间有多大。这篇文章我打算把这些经验完整写出来用实际案例加执行计划拆解的方式帮你在下次遇到慢查询时能一眼判断出是不是条件下推的决策出了问题也知道该怎么改。1. 一张订单明细表引发的慢查询问题的表象与本质先还原一下那个案例。线上库是PostgreSQL 12订单主表orders大约2600万行订单明细order_details约1.2亿行客户表customers约340万行。业务要查最近30天内已完成订单的客户名、订单号和商品明细SQL长这样SELECT c.customer_name, o.order_id, od.product_id, od.quantity FROM orders o JOIN customers c ON o.customer_id c.customer_id JOIN order_details od ON o.order_id od.order_id WHERE o.order_status COMPLETED AND o.order_time NOW() - INTERVAL 30 days;你本能的想法是order_status和order_time这两个过滤条件应该先作用于orders表把参与关联的数据压缩到几万行再和customers、order_details去JOIN。但EXPLAIN ANALYZE出来优化器把orders当驱动表先全表扫了orders再和customers做Hash Join然后才做order_details的Join最后在Join结果上应用过滤条件。这导致order_details有大量根本无关的行参与了哈希构建和探测。1.1 原始SQL与执行计划里的“反常现象”看当时的执行计划摘要Hash Join (cost48213.42..2821931.45 rows229876 width48) Hash Cond: (od.order_id o.order_id) - Seq Scan on order_details od (cost0.00..2116769.80 rows119876290 width24) - Hash (cost21067.31..21067.31 rows874532 width32) - Hash Join (cost3421.98..21067.31 rows874532 width32) Hash Cond: (o.customer_id c.customer_id) - Seq Scan on orders o (cost0.00..11368.24 rows874532 width22) Filter: ((order_status COMPLETED::text) AND (order_time (now() - 30 days::interval))) - Hash (cost2193.91..2193.91 rows339991 width14) - Seq Scan on customers c (cost0.00..2193.91 rows339991 width14)注意看orders表上的Filter是有的也就是说过滤条件作用在了orders上但它是作为Hash Join的内侧输入先算出来的给自己这一层用。真正反常的是最后那个Hash Joinorder_details被整个Seq Scan扫了1.19亿行直到Join结束、输出最终结果前过滤条件并没有在order_details这一侧产生任何提前裁剪。这里其实暴露了一个关键认知所谓“连接条件下推”不是简单看WHERE条件出现在哪个表上而是要看条件被下推到哪棵执行计划树的什么位置。优化器内部经过了RBO基于规则的优化和CBO基于代价的优化两个阶段RBO阶段会尝试把谓词下推到基表扫描节点但CBO阶段会基于代价重新评估如果强行下推导致执行计划形状变化后代价更高优化器有权放弃下推。1.2 为什么“先过滤再关联”反而不一定最优大部分开发同学默认“过滤条件越早执行越好”这个直觉大概率正确但对优化器来说不是无条件成立。优化器最终目标不是让某个算子提前而是在所有候选执行计划里挑代价总和最小的那个。代价总和涉及CPU、IO、内存、网络传输还涉及Join顺序变化带来的中间结果集变化。“先过滤orders再关联”这条路径orders表被过滤后大约87万行和customers340万行做Join得到87万行中间结果再和order_details1.2亿行做Join。表面上看顺序没问题。但数据库还要考虑另一个问题order_details作为最大的表如果不过滤直接Hash Join左表探测1.19亿行右表87万行可以放进内存总代价未必比“先对order_details用order_id过滤”更高。因为order_id过滤条件本质上是半连接语义需要依赖orders表的结果才知道哪些order_id有用。这个依赖导致它不是简单的静态谓词不能独立下推到order_details扫描层。换句话说order_details这条“过滤”只能以动态方式实现比如改成子查询、改成Join条件下推甚至用semi-join重写。而每种方式都有额外代价。优化器真正在算的是“下推带来的选择性收益”和“下推带来的执行结构复杂度代价”之间的差值。理解这一点是看懂整个基于代价下推机制的前提。2. 连接条件下推到底在推什么关系代数下的“提前过滤”逻辑连接条件下推Join Predicate Pushdown从关系代数角度看是利用了选择操作对连接操作的分配律在满足一定语义条件时先对关系做选择再连接等价于先连接再选择。用符号表达就是若p只涉及关系R的属性则 σ_p(R ⋈ S) ≡ σ_p(R) ⋈ S若p涉及R和S两侧属性则需要把连接条件下的选择转换成连接条件的一部分来处理或者引入新的连接算子上面案例里order_status和order_time只属于orders表属于第一种情况所以理论上完全可以把条件推到orders表扫描后立即执行。实际执行计划也确实在orders的Seq Scan节点上有Filter。但order_details侧的裁剪做不到因为order_id的匹配依赖另一个关系的值属于连接语义本身不能简单地“提前”。2.1 语义等价变换下推合法性的数学基础把谓词拆成三类理解会清晰很多第一类只涉及单表列的过滤条件比如order_status、order_time、customer_level。这类条件只要不违反外连接语义几乎总是可以推到基表侧。第二类涉及两表列的等值条件连接条件如o.customer_id c.customer_id。这类条件决定Join本身不存在“推不推”的问题但可以影响Join顺序和Hash Join的左右输入选择。第三类涉及两表列的非等值条件如o.total_amount od.unit_price * od.quantity。这类条件既不是纯过滤也不是纯等值连接优化器通常会把它们作为Join的附加过滤条件放在Join执行之后很少能安全下推。理解这三类的价值在于大多数人以为“下推”是单一动作实际上同一个SQL的多个条件有的被推了有的被留在Join节点上有的被重写成了新的连接顺序。执行计划就是你看到的结果。2.2 不带代价的“无条件下推”会踩的坑既然RBO阶段已经有“谓词下推”规则为什么优化器还要用CBO重新评估因为无条件下推有时会让计划更差。我总结过几类典型情况选择性差的过滤条件比如一个字段99%的值都是Y过滤后仍剩余大量行。把这种条件下推到驱动表侧可能让驱动表扫描路径从索引扫描变成全表扫描反而增加IO。下推导致索引选择失误条件本身能利用某个二级索引但下推后优化器评估发现组合条件无法用索引选了一条顺序扫描路径代价反而上升。物化视图/CTE场景如果过滤条件下推到CTE内部导致CTE无法被物化复用CTE被执行多次代价翻倍。外连接场景把WHERE里的右表过滤条件“推”到下推位置可能改变外连接语义这个后面实战部分细说。这些坑恰恰说明“尽早过滤”只是启发式经验不是硬道理。优化器一旦发现下推后整体代价增加就会选择不下推。代价估算的准确性决定了优化器在这件事上是否可信。3. 代价模型如何计算“推”还是“不推”优化器的心算过程如果你打开了数据库的trace日志会看到优化器几乎把所有候选计划全枚举一遍每组计划都有一套cost数字。以PostgreSQL为例执行计划的cost值不是时间单位是一个无量纲的“代价点数”由启动代价加总代价构成总代价又细分为IO代价和CPU代价。3.1 代价函数里藏着哪些参数读行数、算子代价系数PostgreSQL的代价公式核心可以简化为total_cost seq_page_cost * pages cpu_tuple_cost * tuples cpu_operator_cost * tuples_processed其中seq_page_cost和cpu_tuple_cost是全局配置参数默认分别是1.0和0.01。pages是表占用的数据页数tuples是估计要读取的行数。优化器先用统计信息估算每个表、每个过滤条件的选择率算出每个算子输入输出的tuple数量再套代价系数累加。放到案例里看orders表2600万行假设占用约11万数据页全表扫描代价大约11万IO 2600万×0.01CPU处理每一行 37万左右。过滤后的预估行数是874532行这个数字是通过直方图计算order_time和order_status组合选择率得出来的。可以看到优化器给orders Seq Scan节点的cost是11368.24这个值远小于全表扫描37万说明PostgreSQL实际上已经用了filter来估算后置代价尽管节点类型仍是Seq Scan。关键点在于这个11368.24是“扫描并过滤”的总代价不是单纯IO代价。filter被执行在扫描过程中每一行都要经过条件判断所以CPU代价全部计入。3.2 直方图与基数估计代价模型最“敏感”的输入执行计划里所有rows字段都是估计值估计的源头是统计信息。PostgreSQL对每列维护高频值MCVMost Common Values和直方图用于估算等值条件和范围条件的选择率。order_time范围条件的选择率靠的是直方图桶之间的比例order_statusCOMPLETED的选择率靠MCV里COMPLETED出现的频率。假设orders表里PENDING状态的记录占了历史数据的70%但最近一个月COMPLETED比例实际很高。如果统计信息过期优化器会以为过滤条件能把数据压到很小但实际过滤后仍然有大几百万行。反过来如果统计信息显示order_status分布均匀优化器可能认为过滤条件选择性差从而低估下推收益选择不下推。这也是为什么很多“奇怪”的执行计划最后查根因都落在统计信息不准上。我见过一个案例一张1亿行的流水表查询最近7天数据优化器估成返4000万行选择全表扫描加Hash Join实际只返回800行。ANALYZE之后执行计划立刻变成索引扫描加Nested Loop查询从8秒降到40毫秒。执行计划的变化根子全在基数估计上。3.3 一个手动复算代价的例子拿刚才那个执行计划里的Hash Join节点做简化复算帮助你建立直观感受。Hash Join的代价大致由两部分组成构建侧build side通常是右表建立哈希表的代价加上探测侧probe side通常是左表逐行探测哈希表的代价。案例中右侧orders过滤后估算874532行构建哈希表成本约874532 × cpu_operator_cost(0.0025) ≈ 2186加上输入行扫描成本11368合计约21067。这些数字和计划里的cost基本对得上。左侧order_details1.19亿行全表扫描IO成本约211万按每个页块若干行反推CPU处理成本1.19亿×0.01119万合计约212万。这个数字正好对应Seq Scan on order_details那行的cost2116769.80。最终Hash Join节点总代价280万但注意它是在扫描完所有order_details行后做的汇总。如果优化器能想办法把order_details的扫描量降下来这个总代价会显著下降。但怎么降取决于能否找到一条更低代价的路径——比如反过来用order_details作为驱动表先做semi-join减少探测量。优化器穷举搜索时会评估这些计划但还要考虑内存溢出的风险、临时文件写入磁盘的代价。有时候估算出来的代价里已经包含了work_mem不足导致的“下溢到磁盘”惩罚所以它宁愿选择全表扫描也不选择理论上有选择性收益但需要大内存的路径。4. 实战三种复杂查询场景下基于代价的下推取舍与执行计划观察理论说了一堆实操才见真章。我挑三个在业务里经常遇到的复杂查询场景分别看一下优化器在“基于代价的连接条件下推”上如何决策。每个场景我都给出了实际可复现的判断方法和观察点。4.1 子查询条件下推EXISTS改写背后的语义与代价博弈第一个场景是带EXISTS子查询的查询。例如查最近30天内有已完成订单的客户列表SELECT c.customer_id, c.customer_name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id AND o.order_status COMPLETED AND o.order_time NOW() - INTERVAL 30 days );这里有一个很有意思的点子查询里的过滤条件o.order_status和o.order_time都只涉及orders表。理论上PostgreSQL可以把子查询转换成semijoin然后把orders表的过滤条件下推到orders扫描层。执行计划确实会显示orders表上有Filter。但代价博弈发生在另一个维度优化器需要决定semijoin的驱动侧。如果customers表只有340万行orders表过滤后是87万行用customers做驱动、orders表构建哈希集合一共探测340万次代价可控。如果反过来orders表过滤后是8700万行统计信息把订单状态和时间的组合选择性估高了优化器可能选择让orders表做驱动把87万行客户id构建成哈希集合去探测orders表。两种计划的代价完全不一样。真正容易出错的是当你把EXISTS改写成IN子查询或改写成JOIN时语义可能等价但优化器进入的优化路径不同代价估算结果也可能不同。我在生产环境就见过EXISTS写法耗时120ms改成JOIN写法后优化器选择了一个坏的Join顺序耗时变成6秒。原因不是优化器变笨了而是JOIN写法引入了新的等价变换空间搜索空间变大后启发式剪枝反而选了一条坏路径。实操建议遇到子查询慢不要只盯着子查询内部先看整体Join顺序再看子查询有没有被转成semi join或anti join。如果执行计划里出现了“Hash Semi Join”说明优化器完成了子查询条件的下推和连接语义改写如果看到“InitPlan”或“SubPlan”说明子查询被当作相关子查询逐行执行了这种通常是代价模型低估了逐行执行的放大效应。手动改写时尽量保留语义清晰的EXISTS写法不要盲目改成JOIN。4.2 外连接条件下推留在ON里还是挪到WHERE里结果完全不同外连接是个重灾区很多“结果集变少”的Bug都源于此。以这个查询为例SELECT c.customer_name, o.total_amount FROM customers c LEFT JOIN orders o ON o.customer_id c.customer_id WHERE o.total_amount 1000;如果只从“过滤条件要提前”的角度看很多人会以为优化器会把o.total_amount 1000推到orders表扫描后执行。但一旦真的下推到Join之前语义就变了LEFT JOIN会先保留所有customers行Join后再过滤掉不符合条件的orders行最终结果是那些没有大额订单的客户也会消失。也就是说加了WHERE条件后LEFT JOIN的外连接特性被“中和”成了类似INNER JOIN的语义。而如果条件写在ON子句里SELECT c.customer_name, o.total_amount FROM customers c LEFT JOIN orders o ON o.customer_id c.customer_id AND o.total_amount 1000;语义完全不一样所有客户都会保留没有大额订单的客户在结果里o.total_amount是NULL。这个条件下推是安全的因为ON条件不会减少左表的行数。优化器在执行条件下推时区分WHERE和ON的语义比我们想象得严格。PostgreSQL在谓词下推阶段会保留外连接的语义信息WHERE条件作用于外连接的输出如果把它下推到内部必须确认不会改变结果中左表的行保留情况。代价模型会在“下推后减少探测行数”和“下推后引入NULL扩展或语义错误风险”之间权衡。多数时候SQL语义本身决定了能不能推代价模型反而不是主角。实操建议如果你发现LEFT JOIN执行计划里右表扫描缺少本该有的过滤条件先查这个条件是在WHERE里还是ON里。在WHERE里的右表条件即使执行计划显示它在Join之后才生效这个行为反而是正确的。如果你确实想保留左表所有行且过滤右表就直接把条件挪到ON子句里。这种改写带来的性能提升往往非常明显因为它允许优化器在右表侧做真正的条件下推。4.3 分区裁剪与条件下推静态剪枝之外的代价红利第三个场景带分区表。假设订单表orders按order_time做了范围分区每月一个分区SELECT o.order_id, od.product_id FROM orders o JOIN order_details od ON o.order_id od.order_id WHERE o.order_time 2024-01-01 AND o.order_time 2024-02-01;分区裁剪Partition Pruning能在扫描orders时直接跳过无关分区只扫1月这一个分区。这本身就是条件下推的一种收益过滤条件被用在了表访问路径选择阶段。但代价模型还有一层考量如果order_details也按order_time做了分区且order_details.order_time与o.order_time有对应关系优化器甚至可以做分区级连接裁剪partition-wise join把orders 1月分区只跟order_details的1月分区做连接。这种优化的代价收益远超普通条件下推因为它既减少了扫描量又减少了连接时的哈希表构建量。我遇到过的问题是order_details表没有保留order_time字段只能通过order_id关联。这种情况下分区裁剪只对orders表生效order_details仍然要全表扫描。优化器会基于代价决定是全表扫order_details还是依赖order_id索引做nest loop join。此时条件下推对order_details这一侧已经是无效的唯一能做的是通过order_id索引把探测过程变高效。所以如果你的设计允许在事实表上同时维护分区键和关联键往往比事后调SQL更有效。如果优化器没有做分区级连接裁剪先检查两个表的Join键是否都包含分区键以及有没有启用enable_partition_wise_join参数。这个参数在PostgreSQL默认是off因为分区级join在某些场景会显著增加计划节点数量内存占用也大代价模型必须算得过收益才会开启。手动开启前最好用小数据集测试一下避免计划膨胀反而变慢。5. 优化器不推的时候我们还能做什么手动改写与执行计划干预当优化器基于代价模型决定不下推而我们从业务知识判断应该下推时第一步不是改SQL而是先确认代价模型的输入准不准。很多时候优化器“决策错误”是因为基数估计失真。5.1 先查统计信息再动手80%的下推问题出在基数估计不准我处理慢查询有一套固定动作看EXPLAIN里的预估行数和实际行数EXPLAIN ANALYZE差异。如果差异超过10倍优先刷新统计信息PostgreSQL执行ANALYZE或更细粒度的ANALYZE TABLE。检查是否有表达式索引或函数调用导致条件无法匹配统计信息。比如WHERE date(order_time) 2024-01-01这种函数包裹会让统计信息无法直接估算选择性优化器只能猜。改写为order_time 2024-01-01 AND order_time 2024-01-02统计信息才能充分发挥作用。在刷新统计信息之后很多“需要手动hint”的执行计划会自动恢复正常。我的经验是80%的异常执行计划通过更新统计信息就能解决。真正需要手动干预的通常是统计信息本身无法表达的表间数据相关性。比如order_time和order_status强相关——近30天绝大多数订单是COMPLETED但Mcv和直方图分别看单列时无法体现这种相关性优化器会把两个条件的选择率相乘导致严重低估返回行数。这时候可以考虑扩展统计信息PostgreSQL的CREATE STATISTICS可以跨列收集依赖关系和联合分布或者干脆手动改写SQL把条件组合放进一个派生表里让优化器先物化过滤结果再参与Join。5.2 SQL等价改写与优化器提示的适用边界如果统计信息已经准确优化器仍然不下推我再考虑改写。改写方向有几个用CTE把过滤逻辑前置把带过滤条件的大表查询包进WITH子句并加上MATERIALIZED提示强制物化中间结果再参与后续Join。这会改变执行计划形状中间结果被物化到临时存储。调整Join顺序把小表放在FROM左侧利用优化器对从左到右的启发式规则影响Join顺序。但不保证所有数据库都遵守书写顺序。用数据库专有hintPostgreSQL自带pg_hint_plan扩展可以指定Leading、HashJoin、SeqScan等。MySQL有optimizer_switch和index hint但控制Join顺序的能力弱一些。Oracle的hint体系最丰富/* LEADING */可以直接指定Join顺序。但我要泼一盆冷水hint是双刃剑。它让执行计划固定下来但数据量持续增长后原本合适的计划会变坏。我建议只在以下几种情况使用hint优化器在统计信息准确时仍然做出明显反直觉的选择。查询频率极高对执行时间敏感且经过压测确认hint后的计划稳定高效。代码评审能够跟上数据库版本升级确保hint在新版本中仍被支持。否则与其依赖hint不如调整索引设计、更新统计信息、改写SQL语义让优化器“自然”走上正确路径。6. 从代价模型到工程实践我踩过的坑与建议最后分享一些从实际项目中沉淀下来的经验。这些不算高深理论但每一个都真实影响过线上查询性能。第一不要在SELECT列表里放大字段。很多人以为条件下推和SELECT列无关但在真实执行计划里宽列会导致临时文件更大、物化更慢、哈希表更大进而让代价模型选择不下推。我有一次优化一个报表查询把SELECT里的一个JSONB大字段去掉后Hash Join的代价降了四成优化器自动选了新的Join顺序查询快了三倍。执行计划里即使过滤条件位置没变代价估算的变化已经足够让优化器“改主意”。第二警惕OR条件下推的陷阱。WHERE里有OR条件时很多优化器无法把OR拆分成可下推的形式导致整个过滤留在Join节点上。比如WHERE o.statusA OR c.level 3这种跨表OR条件下推会破坏单表扫描的索引选择。我的做法是尽量拆成UNION ALL让每一边都能独立利用索引和过滤条件下推。但要注意如果两个分支结果集大量重叠UNION ALL会出现重复数据需要业务确认或再加DISTINCT这又是一个代价权衡。第三建立执行计划基线。我在团队里定了一条规矩任何核心SQL在版本发布前都要记录EXPLAIN ANALYZE的关键节点rows和total_cost并纳入压测流程。数据库升级、统计信息变化、数据量增长都可能让优化器改变下推决策没有基线根本发现不了计划回归。很多慢查询问题不是某一天突然发生而是优化器悄悄换了一条代价更低但实际更慢的路径。第四理解业务数据的“形状”比理解SQL语法更重要。优化器的代价模型是把统计信息映射到代价估算它不知道你的业务逻辑不知道order_status和order_time的强相关关系不知道这个月订单量暴涨是促销活动造成的。这些业务知识只有你掌握。所以在最终决策时不要盲目相信执行计划也不要盲目推翻优化器。先用真实数据验证两条路径的执行时间差别再决定是要调统计信息、改索引、改SQL还是加hint。我个人的体会是数据库复杂查询优化从来不是一条命令就能解决的事它本质上是一个“最小代价路径”的搜索问题。从代价模型角度看连接条件下推只是优化器工具箱里的一件工具它有适用边界有失效条件也有值得手动干预的灰色地带。搞懂它背后的计算逻辑你才算真正拥有了和优化器“对话”的能力。下次再遇到一个莫名其妙的慢查询别急着加索引先看看执行计划里的cost和rows问问自己这个条件下推代价模型算对了吗 连接条件下推的代价博弈：慢查询优化实战与执行计划解析
我接手过不少慢查询优化其中印象最深的一次问题不是出在索引缺失也不是SQL写得太烂而是优化器在“连接条件下推”这件事上做了一次代价博弈——它认为“不该推”结果查询跑了整整37秒。这条SQL本身一点不复杂三张表关联加两个过滤条件任何程序员一看都知道该先过滤再关联。但数据库偏不。深入研究执行计划之后我才把“基于代价的连接条件下推”这条优化链路彻底吃透什么时候优化器会推、为什么有时候宁可不推、统计信息怎么影响决策、以及我们DBA能干预的空间有多大。这篇文章我打算把这些经验完整写出来用实际案例加执行计划拆解的方式帮你在下次遇到慢查询时能一眼判断出是不是条件下推的决策出了问题也知道该怎么改。1. 一张订单明细表引发的慢查询问题的表象与本质先还原一下那个案例。线上库是PostgreSQL 12订单主表orders大约2600万行订单明细order_details约1.2亿行客户表customers约340万行。业务要查最近30天内已完成订单的客户名、订单号和商品明细SQL长这样SELECT c.customer_name, o.order_id, od.product_id, od.quantity FROM orders o JOIN customers c ON o.customer_id c.customer_id JOIN order_details od ON o.order_id od.order_id WHERE o.order_status COMPLETED AND o.order_time NOW() - INTERVAL 30 days;你本能的想法是order_status和order_time这两个过滤条件应该先作用于orders表把参与关联的数据压缩到几万行再和customers、order_details去JOIN。但EXPLAIN ANALYZE出来优化器把orders当驱动表先全表扫了orders再和customers做Hash Join然后才做order_details的Join最后在Join结果上应用过滤条件。这导致order_details有大量根本无关的行参与了哈希构建和探测。1.1 原始SQL与执行计划里的“反常现象”看当时的执行计划摘要Hash Join (cost48213.42..2821931.45 rows229876 width48) Hash Cond: (od.order_id o.order_id) - Seq Scan on order_details od (cost0.00..2116769.80 rows119876290 width24) - Hash (cost21067.31..21067.31 rows874532 width32) - Hash Join (cost3421.98..21067.31 rows874532 width32) Hash Cond: (o.customer_id c.customer_id) - Seq Scan on orders o (cost0.00..11368.24 rows874532 width22) Filter: ((order_status COMPLETED::text) AND (order_time (now() - 30 days::interval))) - Hash (cost2193.91..2193.91 rows339991 width14) - Seq Scan on customers c (cost0.00..2193.91 rows339991 width14)注意看orders表上的Filter是有的也就是说过滤条件作用在了orders上但它是作为Hash Join的内侧输入先算出来的给自己这一层用。真正反常的是最后那个Hash Joinorder_details被整个Seq Scan扫了1.19亿行直到Join结束、输出最终结果前过滤条件并没有在order_details这一侧产生任何提前裁剪。这里其实暴露了一个关键认知所谓“连接条件下推”不是简单看WHERE条件出现在哪个表上而是要看条件被下推到哪棵执行计划树的什么位置。优化器内部经过了RBO基于规则的优化和CBO基于代价的优化两个阶段RBO阶段会尝试把谓词下推到基表扫描节点但CBO阶段会基于代价重新评估如果强行下推导致执行计划形状变化后代价更高优化器有权放弃下推。1.2 为什么“先过滤再关联”反而不一定最优大部分开发同学默认“过滤条件越早执行越好”这个直觉大概率正确但对优化器来说不是无条件成立。优化器最终目标不是让某个算子提前而是在所有候选执行计划里挑代价总和最小的那个。代价总和涉及CPU、IO、内存、网络传输还涉及Join顺序变化带来的中间结果集变化。“先过滤orders再关联”这条路径orders表被过滤后大约87万行和customers340万行做Join得到87万行中间结果再和order_details1.2亿行做Join。表面上看顺序没问题。但数据库还要考虑另一个问题order_details作为最大的表如果不过滤直接Hash Join左表探测1.19亿行右表87万行可以放进内存总代价未必比“先对order_details用order_id过滤”更高。因为order_id过滤条件本质上是半连接语义需要依赖orders表的结果才知道哪些order_id有用。这个依赖导致它不是简单的静态谓词不能独立下推到order_details扫描层。换句话说order_details这条“过滤”只能以动态方式实现比如改成子查询、改成Join条件下推甚至用semi-join重写。而每种方式都有额外代价。优化器真正在算的是“下推带来的选择性收益”和“下推带来的执行结构复杂度代价”之间的差值。理解这一点是看懂整个基于代价下推机制的前提。2. 连接条件下推到底在推什么关系代数下的“提前过滤”逻辑连接条件下推Join Predicate Pushdown从关系代数角度看是利用了选择操作对连接操作的分配律在满足一定语义条件时先对关系做选择再连接等价于先连接再选择。用符号表达就是若p只涉及关系R的属性则 σ_p(R ⋈ S) ≡ σ_p(R) ⋈ S若p涉及R和S两侧属性则需要把连接条件下的选择转换成连接条件的一部分来处理或者引入新的连接算子上面案例里order_status和order_time只属于orders表属于第一种情况所以理论上完全可以把条件推到orders表扫描后立即执行。实际执行计划也确实在orders的Seq Scan节点上有Filter。但order_details侧的裁剪做不到因为order_id的匹配依赖另一个关系的值属于连接语义本身不能简单地“提前”。2.1 语义等价变换下推合法性的数学基础把谓词拆成三类理解会清晰很多第一类只涉及单表列的过滤条件比如order_status、order_time、customer_level。这类条件只要不违反外连接语义几乎总是可以推到基表侧。第二类涉及两表列的等值条件连接条件如o.customer_id c.customer_id。这类条件决定Join本身不存在“推不推”的问题但可以影响Join顺序和Hash Join的左右输入选择。第三类涉及两表列的非等值条件如o.total_amount od.unit_price * od.quantity。这类条件既不是纯过滤也不是纯等值连接优化器通常会把它们作为Join的附加过滤条件放在Join执行之后很少能安全下推。理解这三类的价值在于大多数人以为“下推”是单一动作实际上同一个SQL的多个条件有的被推了有的被留在Join节点上有的被重写成了新的连接顺序。执行计划就是你看到的结果。2.2 不带代价的“无条件下推”会踩的坑既然RBO阶段已经有“谓词下推”规则为什么优化器还要用CBO重新评估因为无条件下推有时会让计划更差。我总结过几类典型情况选择性差的过滤条件比如一个字段99%的值都是Y过滤后仍剩余大量行。把这种条件下推到驱动表侧可能让驱动表扫描路径从索引扫描变成全表扫描反而增加IO。下推导致索引选择失误条件本身能利用某个二级索引但下推后优化器评估发现组合条件无法用索引选了一条顺序扫描路径代价反而上升。物化视图/CTE场景如果过滤条件下推到CTE内部导致CTE无法被物化复用CTE被执行多次代价翻倍。外连接场景把WHERE里的右表过滤条件“推”到下推位置可能改变外连接语义这个后面实战部分细说。这些坑恰恰说明“尽早过滤”只是启发式经验不是硬道理。优化器一旦发现下推后整体代价增加就会选择不下推。代价估算的准确性决定了优化器在这件事上是否可信。3. 代价模型如何计算“推”还是“不推”优化器的心算过程如果你打开了数据库的trace日志会看到优化器几乎把所有候选计划全枚举一遍每组计划都有一套cost数字。以PostgreSQL为例执行计划的cost值不是时间单位是一个无量纲的“代价点数”由启动代价加总代价构成总代价又细分为IO代价和CPU代价。3.1 代价函数里藏着哪些参数读行数、算子代价系数PostgreSQL的代价公式核心可以简化为total_cost seq_page_cost * pages cpu_tuple_cost * tuples cpu_operator_cost * tuples_processed其中seq_page_cost和cpu_tuple_cost是全局配置参数默认分别是1.0和0.01。pages是表占用的数据页数tuples是估计要读取的行数。优化器先用统计信息估算每个表、每个过滤条件的选择率算出每个算子输入输出的tuple数量再套代价系数累加。放到案例里看orders表2600万行假设占用约11万数据页全表扫描代价大约11万IO 2600万×0.01CPU处理每一行 37万左右。过滤后的预估行数是874532行这个数字是通过直方图计算order_time和order_status组合选择率得出来的。可以看到优化器给orders Seq Scan节点的cost是11368.24这个值远小于全表扫描37万说明PostgreSQL实际上已经用了filter来估算后置代价尽管节点类型仍是Seq Scan。关键点在于这个11368.24是“扫描并过滤”的总代价不是单纯IO代价。filter被执行在扫描过程中每一行都要经过条件判断所以CPU代价全部计入。3.2 直方图与基数估计代价模型最“敏感”的输入执行计划里所有rows字段都是估计值估计的源头是统计信息。PostgreSQL对每列维护高频值MCVMost Common Values和直方图用于估算等值条件和范围条件的选择率。order_time范围条件的选择率靠的是直方图桶之间的比例order_statusCOMPLETED的选择率靠MCV里COMPLETED出现的频率。假设orders表里PENDING状态的记录占了历史数据的70%但最近一个月COMPLETED比例实际很高。如果统计信息过期优化器会以为过滤条件能把数据压到很小但实际过滤后仍然有大几百万行。反过来如果统计信息显示order_status分布均匀优化器可能认为过滤条件选择性差从而低估下推收益选择不下推。这也是为什么很多“奇怪”的执行计划最后查根因都落在统计信息不准上。我见过一个案例一张1亿行的流水表查询最近7天数据优化器估成返4000万行选择全表扫描加Hash Join实际只返回800行。ANALYZE之后执行计划立刻变成索引扫描加Nested Loop查询从8秒降到40毫秒。执行计划的变化根子全在基数估计上。3.3 一个手动复算代价的例子拿刚才那个执行计划里的Hash Join节点做简化复算帮助你建立直观感受。Hash Join的代价大致由两部分组成构建侧build side通常是右表建立哈希表的代价加上探测侧probe side通常是左表逐行探测哈希表的代价。案例中右侧orders过滤后估算874532行构建哈希表成本约874532 × cpu_operator_cost(0.0025) ≈ 2186加上输入行扫描成本11368合计约21067。这些数字和计划里的cost基本对得上。左侧order_details1.19亿行全表扫描IO成本约211万按每个页块若干行反推CPU处理成本1.19亿×0.01119万合计约212万。这个数字正好对应Seq Scan on order_details那行的cost2116769.80。最终Hash Join节点总代价280万但注意它是在扫描完所有order_details行后做的汇总。如果优化器能想办法把order_details的扫描量降下来这个总代价会显著下降。但怎么降取决于能否找到一条更低代价的路径——比如反过来用order_details作为驱动表先做semi-join减少探测量。优化器穷举搜索时会评估这些计划但还要考虑内存溢出的风险、临时文件写入磁盘的代价。有时候估算出来的代价里已经包含了work_mem不足导致的“下溢到磁盘”惩罚所以它宁愿选择全表扫描也不选择理论上有选择性收益但需要大内存的路径。4. 实战三种复杂查询场景下基于代价的下推取舍与执行计划观察理论说了一堆实操才见真章。我挑三个在业务里经常遇到的复杂查询场景分别看一下优化器在“基于代价的连接条件下推”上如何决策。每个场景我都给出了实际可复现的判断方法和观察点。4.1 子查询条件下推EXISTS改写背后的语义与代价博弈第一个场景是带EXISTS子查询的查询。例如查最近30天内有已完成订单的客户列表SELECT c.customer_id, c.customer_name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id AND o.order_status COMPLETED AND o.order_time NOW() - INTERVAL 30 days );这里有一个很有意思的点子查询里的过滤条件o.order_status和o.order_time都只涉及orders表。理论上PostgreSQL可以把子查询转换成semijoin然后把orders表的过滤条件下推到orders扫描层。执行计划确实会显示orders表上有Filter。但代价博弈发生在另一个维度优化器需要决定semijoin的驱动侧。如果customers表只有340万行orders表过滤后是87万行用customers做驱动、orders表构建哈希集合一共探测340万次代价可控。如果反过来orders表过滤后是8700万行统计信息把订单状态和时间的组合选择性估高了优化器可能选择让orders表做驱动把87万行客户id构建成哈希集合去探测orders表。两种计划的代价完全不一样。真正容易出错的是当你把EXISTS改写成IN子查询或改写成JOIN时语义可能等价但优化器进入的优化路径不同代价估算结果也可能不同。我在生产环境就见过EXISTS写法耗时120ms改成JOIN写法后优化器选择了一个坏的Join顺序耗时变成6秒。原因不是优化器变笨了而是JOIN写法引入了新的等价变换空间搜索空间变大后启发式剪枝反而选了一条坏路径。实操建议遇到子查询慢不要只盯着子查询内部先看整体Join顺序再看子查询有没有被转成semi join或anti join。如果执行计划里出现了“Hash Semi Join”说明优化器完成了子查询条件的下推和连接语义改写如果看到“InitPlan”或“SubPlan”说明子查询被当作相关子查询逐行执行了这种通常是代价模型低估了逐行执行的放大效应。手动改写时尽量保留语义清晰的EXISTS写法不要盲目改成JOIN。4.2 外连接条件下推留在ON里还是挪到WHERE里结果完全不同外连接是个重灾区很多“结果集变少”的Bug都源于此。以这个查询为例SELECT c.customer_name, o.total_amount FROM customers c LEFT JOIN orders o ON o.customer_id c.customer_id WHERE o.total_amount 1000;如果只从“过滤条件要提前”的角度看很多人会以为优化器会把o.total_amount 1000推到orders表扫描后执行。但一旦真的下推到Join之前语义就变了LEFT JOIN会先保留所有customers行Join后再过滤掉不符合条件的orders行最终结果是那些没有大额订单的客户也会消失。也就是说加了WHERE条件后LEFT JOIN的外连接特性被“中和”成了类似INNER JOIN的语义。而如果条件写在ON子句里SELECT c.customer_name, o.total_amount FROM customers c LEFT JOIN orders o ON o.customer_id c.customer_id AND o.total_amount 1000;语义完全不一样所有客户都会保留没有大额订单的客户在结果里o.total_amount是NULL。这个条件下推是安全的因为ON条件不会减少左表的行数。优化器在执行条件下推时区分WHERE和ON的语义比我们想象得严格。PostgreSQL在谓词下推阶段会保留外连接的语义信息WHERE条件作用于外连接的输出如果把它下推到内部必须确认不会改变结果中左表的行保留情况。代价模型会在“下推后减少探测行数”和“下推后引入NULL扩展或语义错误风险”之间权衡。多数时候SQL语义本身决定了能不能推代价模型反而不是主角。实操建议如果你发现LEFT JOIN执行计划里右表扫描缺少本该有的过滤条件先查这个条件是在WHERE里还是ON里。在WHERE里的右表条件即使执行计划显示它在Join之后才生效这个行为反而是正确的。如果你确实想保留左表所有行且过滤右表就直接把条件挪到ON子句里。这种改写带来的性能提升往往非常明显因为它允许优化器在右表侧做真正的条件下推。4.3 分区裁剪与条件下推静态剪枝之外的代价红利第三个场景带分区表。假设订单表orders按order_time做了范围分区每月一个分区SELECT o.order_id, od.product_id FROM orders o JOIN order_details od ON o.order_id od.order_id WHERE o.order_time 2024-01-01 AND o.order_time 2024-02-01;分区裁剪Partition Pruning能在扫描orders时直接跳过无关分区只扫1月这一个分区。这本身就是条件下推的一种收益过滤条件被用在了表访问路径选择阶段。但代价模型还有一层考量如果order_details也按order_time做了分区且order_details.order_time与o.order_time有对应关系优化器甚至可以做分区级连接裁剪partition-wise join把orders 1月分区只跟order_details的1月分区做连接。这种优化的代价收益远超普通条件下推因为它既减少了扫描量又减少了连接时的哈希表构建量。我遇到过的问题是order_details表没有保留order_time字段只能通过order_id关联。这种情况下分区裁剪只对orders表生效order_details仍然要全表扫描。优化器会基于代价决定是全表扫order_details还是依赖order_id索引做nest loop join。此时条件下推对order_details这一侧已经是无效的唯一能做的是通过order_id索引把探测过程变高效。所以如果你的设计允许在事实表上同时维护分区键和关联键往往比事后调SQL更有效。如果优化器没有做分区级连接裁剪先检查两个表的Join键是否都包含分区键以及有没有启用enable_partition_wise_join参数。这个参数在PostgreSQL默认是off因为分区级join在某些场景会显著增加计划节点数量内存占用也大代价模型必须算得过收益才会开启。手动开启前最好用小数据集测试一下避免计划膨胀反而变慢。5. 优化器不推的时候我们还能做什么手动改写与执行计划干预当优化器基于代价模型决定不下推而我们从业务知识判断应该下推时第一步不是改SQL而是先确认代价模型的输入准不准。很多时候优化器“决策错误”是因为基数估计失真。5.1 先查统计信息再动手80%的下推问题出在基数估计不准我处理慢查询有一套固定动作看EXPLAIN里的预估行数和实际行数EXPLAIN ANALYZE差异。如果差异超过10倍优先刷新统计信息PostgreSQL执行ANALYZE或更细粒度的ANALYZE TABLE。检查是否有表达式索引或函数调用导致条件无法匹配统计信息。比如WHERE date(order_time) 2024-01-01这种函数包裹会让统计信息无法直接估算选择性优化器只能猜。改写为order_time 2024-01-01 AND order_time 2024-01-02统计信息才能充分发挥作用。在刷新统计信息之后很多“需要手动hint”的执行计划会自动恢复正常。我的经验是80%的异常执行计划通过更新统计信息就能解决。真正需要手动干预的通常是统计信息本身无法表达的表间数据相关性。比如order_time和order_status强相关——近30天绝大多数订单是COMPLETED但Mcv和直方图分别看单列时无法体现这种相关性优化器会把两个条件的选择率相乘导致严重低估返回行数。这时候可以考虑扩展统计信息PostgreSQL的CREATE STATISTICS可以跨列收集依赖关系和联合分布或者干脆手动改写SQL把条件组合放进一个派生表里让优化器先物化过滤结果再参与Join。5.2 SQL等价改写与优化器提示的适用边界如果统计信息已经准确优化器仍然不下推我再考虑改写。改写方向有几个用CTE把过滤逻辑前置把带过滤条件的大表查询包进WITH子句并加上MATERIALIZED提示强制物化中间结果再参与后续Join。这会改变执行计划形状中间结果被物化到临时存储。调整Join顺序把小表放在FROM左侧利用优化器对从左到右的启发式规则影响Join顺序。但不保证所有数据库都遵守书写顺序。用数据库专有hintPostgreSQL自带pg_hint_plan扩展可以指定Leading、HashJoin、SeqScan等。MySQL有optimizer_switch和index hint但控制Join顺序的能力弱一些。Oracle的hint体系最丰富/* LEADING */可以直接指定Join顺序。但我要泼一盆冷水hint是双刃剑。它让执行计划固定下来但数据量持续增长后原本合适的计划会变坏。我建议只在以下几种情况使用hint优化器在统计信息准确时仍然做出明显反直觉的选择。查询频率极高对执行时间敏感且经过压测确认hint后的计划稳定高效。代码评审能够跟上数据库版本升级确保hint在新版本中仍被支持。否则与其依赖hint不如调整索引设计、更新统计信息、改写SQL语义让优化器“自然”走上正确路径。6. 从代价模型到工程实践我踩过的坑与建议最后分享一些从实际项目中沉淀下来的经验。这些不算高深理论但每一个都真实影响过线上查询性能。第一不要在SELECT列表里放大字段。很多人以为条件下推和SELECT列无关但在真实执行计划里宽列会导致临时文件更大、物化更慢、哈希表更大进而让代价模型选择不下推。我有一次优化一个报表查询把SELECT里的一个JSONB大字段去掉后Hash Join的代价降了四成优化器自动选了新的Join顺序查询快了三倍。执行计划里即使过滤条件位置没变代价估算的变化已经足够让优化器“改主意”。第二警惕OR条件下推的陷阱。WHERE里有OR条件时很多优化器无法把OR拆分成可下推的形式导致整个过滤留在Join节点上。比如WHERE o.statusA OR c.level 3这种跨表OR条件下推会破坏单表扫描的索引选择。我的做法是尽量拆成UNION ALL让每一边都能独立利用索引和过滤条件下推。但要注意如果两个分支结果集大量重叠UNION ALL会出现重复数据需要业务确认或再加DISTINCT这又是一个代价权衡。第三建立执行计划基线。我在团队里定了一条规矩任何核心SQL在版本发布前都要记录EXPLAIN ANALYZE的关键节点rows和total_cost并纳入压测流程。数据库升级、统计信息变化、数据量增长都可能让优化器改变下推决策没有基线根本发现不了计划回归。很多慢查询问题不是某一天突然发生而是优化器悄悄换了一条代价更低但实际更慢的路径。第四理解业务数据的“形状”比理解SQL语法更重要。优化器的代价模型是把统计信息映射到代价估算它不知道你的业务逻辑不知道order_status和order_time的强相关关系不知道这个月订单量暴涨是促销活动造成的。这些业务知识只有你掌握。所以在最终决策时不要盲目相信执行计划也不要盲目推翻优化器。先用真实数据验证两条路径的执行时间差别再决定是要调统计信息、改索引、改SQL还是加hint。我个人的体会是数据库复杂查询优化从来不是一条命令就能解决的事它本质上是一个“最小代价路径”的搜索问题。从代价模型角度看连接条件下推只是优化器工具箱里的一件工具它有适用边界有失效条件也有值得手动干预的灰色地带。搞懂它背后的计算逻辑你才算真正拥有了和优化器“对话”的能力。下次再遇到一个莫名其妙的慢查询别急着加索引先看看执行计划里的cost和rows问问自己这个条件下推代价模型算对了吗