连接条件下推这个术语在数据库优化圈子里已经不算新鲜但我这些年跟执行计划打交道发现真正能在复杂业务场景里把这条规则用明白的人其实不多。大家背得耳熟能详的一句话叫“过滤条件下推到扫描层能少读很多数据”可真到了多表关联、外连接、子查询、分区表凑一块儿的时候到底推不推、推到哪一侧、推了之后代价是涨是跌很多优化器自己都会犯迷糊。这篇文章想聊的就是这个“复杂场景下基于代价的连接条件下推”我尽量用自己的实践经验讲清楚优化器在背后是怎么算账的以及我们怎么提前判断它的选择。说直白一点连接条件下推就是在执行连接操作之前把一部分过滤条件下沉到更底层的表扫描或者索引扫描上让进入连接阶段的行数变少。这个动作听起来简单但一旦涉及多张表、多种连接方式、统计信息不准或者数据分布倾斜光靠“能推就推”的经验法则就完全不靠谱了。现代数据库优化器之所以要搞成基于代价的就是因为它需要在成千上万种执行计划里用模型估算出哪个计划真正花钱少。这篇文章适合三类人看一类是天天要调慢SQL的DBA一类是做数据库内核研发或优化器开发的工程师还有一类是面试前想把谓词下推这道题吃透的候选人。读完你至少能搞清楚为什么下推不是无脑最优、哪些场景容易翻车、以及怎样用手里的执行计划工具验证优化器的决策。1. 别被“下推”两个字骗了这其实是优化器的“换位思考”1.1 一次最简单、也最容易说的“搬家”先用一条SQL把概念固定下来SELECT o.order_id, u.user_name FROM orders o JOIN users u ON o.user_id u.id WHERE o.status PAID AND u.reg_date 2023-01-01;这条SQL涉及两张表一个连接条件两个过滤条件。优化器至少有四种候选思路先把orders表扫一遍过滤status‘PAID’然后跟users表做连接先把users表扫一遍过滤reg_date条件然后跟orders表连接两张表原始数据直接连接形成中间结果后再过滤掉不符合条件的行把两个条件分别下推到各自的表扫描做连接前裁剪。第4种就是我们常说的“连接条件下推”。它把过滤谓词从连接之后的Project/Filter节点挪到了表扫描或索引扫描的入口。这样做最直接的好处是减少进入连接节点的行数也意味着连接过程中需要比较、需要缓存、需要写临时文件的数据量都变小了。但注意我说的是“变小了”没说“一定更快”因为代价模型认为还要看驱动顺序、连接算法、数据分布。当年我第一次接触这个概念时觉得这就像打扫房间前先把垃圾装进垃圾袋再统一运出去怎么想都是划算的。直到碰到一个场景把条件下推到某个大表后优化器反而选择了一个挂着几十万行临时结果的执行计划我才意识到这事情没那么简单。1.2 为什么“能推就推”在复杂场景下会失灵假设有A、B两张表A有1000万行B有100万行。A表某个过滤条件把数据从1000万降到10万B表的过滤条件只把数据从100万降到99万。看起来A侧下推收益巨大B侧收益很小。但如果我们把A侧条件推到表扫描层结果A表的扫描路径变了——可能是全表扫描加滤波而不是原有索引范围扫描。如果这个条件列上的索引效果很差统计信息又显示过滤后仍有300万行那优化器给出的扫描代价可能反而高于不快分区扫描后直接做连接。再叠加连接算法的影响下推后A表数据量减少嵌套循环连接变便宜但如果B表原本可以用哈希连接一次性读入内存而A侧过滤后数据量少到驱动侧可以显著降低探测次数这时候下推又有利。所有这些连锁反应正是代价模型要解决的问题。简单规则“下推一定好”本质上只考虑了一个局部效应忽视了全局路径选择。所以在复杂场景下优化器不是机械地执行“遇到过滤条件就下推”而是把它作为搜索空间里的一个候选变换套入代价公式去算TotalCost ScanCost JoinCost ProbeCost ...然后选择最小代价。理解这一点才是理解整篇文章的钥匙。2. 复杂场景下的四大决策变量统计、选择率、Join形态、数据分布2.1 统计信息没有准确基数代价模型就是猜谜任何基于代价的优化前提都是统计信息足够准确。表的行数、列的唯一值数量、NULL值比例、平均值长度、直方图桶分布等等都是优化器做代价估算的输入。连接条件下推的收益估算尤其依赖过滤条件的选择率。比如u.reg_date 2023-01-01实际能过滤掉95%的数据但如果统计信息里这个字段的直方图长时间没更新优化器可能以为只能过滤掉30%的数据于是它就不愿意把条件下推到users表扫描层宁可选择先连接再过滤。我遇到过最典型的一个现场一张日增长量上百万的流水表另一张维度表只有几十万行。业务SQL按“流水的创建时间”做过滤按说应该把时间条件下推到大流水表侧大幅缩小扫描范围。但因为表的统计信息是七天前的优化器低估了数据量的变化导致采样预测出来的选择率严重偏差最终选择了重排连接顺序、先把维度表广播出去再做连接的糟糕计划。后来刷新统计信息执行计划立刻变成了先对流水表做分区裁剪并下推时间条件。这件事给我留下的印象是下推决策不是独立的它的前提是统计信息健全。所以调优第一步永远是ANALYZE或对应的统计信息更新命令而不是急着看执行计划。2.2 选择率估算三张表和三百张表的差别选择率指的是一个谓词预计能保留多少比例的行。单表情况下选择率常常用“唯一值数目的倒数”估算多表连接时还要考虑连接列之间的相关性、重叠情况。如果一张表的过滤条件是a.x 100 AND a.y 200而x和y本身有强相关性选择率不能简单相乘。优化器对这一点通常是依赖列统计和扩展统计信息的。连接条件下推特别怕的也是选择率估算离谱。想象一个查询连接了订单表、订单明细表、商品表、店铺表四张表。商品表上有个category_id 5的过滤条件。如果商品表统计信息准确它知道category_id5只占全部商品行的0.1%下推后驱动行数极少整个连接顺序都能改写。但如果统计信息丢失优化器可能认为这个条件只过滤掉一半数据商品表依然是大驱动表哪怕其它条件下推也救不回来。在多表场景里选择率错一点代价模型的误差会被连接链放大。一个条件的估算偏差是2倍经过三级连接可能变成8倍。所以当你在上百张表的复杂报表里发现优化器没做下推时先怀疑的不是优化器代码而是统计信息估算。我经常用“如果一个谓词的选择率估出两倍误差它可能影响三个连接节点最终代价差一个数量级”来向团队解释为什么DBA要盯着统计信息。2.3 Join形态决定下推方向内连接、外连接、半连接并非所有Join类型都允许把过滤条件无脑下推。内连接比较宽松因为内连接本身不保留未匹配行把任何一侧的等下推下去都不会改变最终语义。外连接麻烦一些尤其是左外连接时如果把右表上的过滤条件下推到右表扫描层可能会把本来要保留为NULL扩展行的右表记录提前过滤掉导致结果集少行语义就错了。所以优化器在这个场景必须谨慎。它只能做“安全下推”或者把这类条件下推到外连接的内侧保留外侧的前提下做某种补偿。半连接和反连接也有类似讲究比如EXISTS子查询、NOT EXISTS子查询。子查询里的过滤条件是否能够下推到子查询内部取决于它是否引用了外层表的列。如果完全不相关那它就是独立子查询可以放心下推如果相关通常需要用initplan或者延迟物化的方式处理。实践里最常用的一招是把相关子查询改写成显式的连接很多时候可以让连接条件下推有更大的施展空间。我在优化一个嵌套很深的报表SQL时把三个IN子查询改成临时表连接优化器一下就选择了正确下推执行时间从28秒降到1.6秒。2.4 数据倾斜与直方图最容易被忽略的一环基数和选择率没错不代表代价就算得准还有一个大坑是数据分布倾斜。假设订单表按用户ID连接用户表其中有一个超级用户占了全部订单的40%。过滤条件是用户等级VIP那个超级VIP用户的数据量远高于普通用户。如果优化器只看到用户级别过滤后总行数减少到20%它可能觉得下推之后驱动行数小于是选择用嵌套循环连接从驱动侧逐行去探测被驱动侧。等到驱动侧遇到那个超级VIP用户时对端匹配行可能有上百万行整个计划被拖垮。直方图就是用来缓解这个问题的。优化器会通过直方图记录高频值让代价模型知道某些值的选择率和平均情况不同。即便如此直方图桶的数量有限倾斜严重时依然可能出错。所以面对有热点数据的大表我会额外关注执行计划里有没有出现Nested Loop驱动大量数据的情况并且会用TOP-N或者HASH JOIN改写倾向来调控。连接条件下推在这里的争议点是下推可以让整体驱动行数变小但也可能放大倾斜值的影响因为它改变了连接顺序。基于代价的优化器就是在这个粒度上权衡而我们能做的是给它足够好的直方图和足够多的自由度。3. 典型复杂场景实践我踩过的坑与验证过的方法3.1 多表连接链上下推位置不是越早越好我见过不少同学拿着“过滤条件下推好”这句话看到执行计划里某个条件没在最底层扫描上就着急。举个实际业务例子简化后的查询是这样SELECT ... FROM dim_shop s JOIN fact_order o ON o.shop_id s.shop_id JOIN dim_sku k ON k.sku_id o.sku_id WHERE s.city 上海 AND k.category 数码;这个连接链是dim_shop - fact_order - dim_sku。直觉上应该把s.city上海下推到dim_shop扫描把k.category数码下推到dim_sku扫描让两个维度表先变小再去碰大的事实表。大多数情况下这确实是正确选择。但有一种情况不是dim_shop过滤后还剩下几万行而fact_order在shop_id上有索引如果以dim_shop为驱动表对fact_order做索引探测每行驱动行去fact_order上查一次总共几万次索引探测可能比先把fact_order和dim_sku做哈希连接再过滤更快。这种时候优化器基于代价会干脆放弃对s.city的提前下推把它保留在连接链上层先过滤dim_shop或者使用hash join把大表连接完再过滤。从执行计划形态上看过滤条件确实在上层但结果集可能是最小的。所以我们在看复杂连接链时不要只问“这个条件下推了吗”要问“下推之后连接顺序和连接算法发生了什么变化”。下推本身是手段不是目的。最终目的是让整体执行代价变小。3.2 外连接下推要小心“侧塌”有一次我在优化一个订单报表SQL里面有一个左外连接SELECT o.order_id, c.coupon_amount FROM orders o LEFT JOIN coupon_usage c ON o.order_id c.order_id WHERE c.coupon_type DISCOUNT;业务本意是找出用了折扣券的订单。但这条SQL的写法有个语义陷阱WHERE c.coupon_type DISCOUNT在左外连接之后过滤会把所有c为空的行删掉实际等价于内连接。当时优化器在PostgreSQL里给出的计划是先把coupon_usage过滤后再做Hash Join效果上把左外连接变成了逻辑内连接这是被允许的因为WHERE条件限定了右表字段非空。可是我们DBA团队里有人用“外连接不能下推右表条件”的教条去质疑这个计划其实是不对的。真正危险的是另一个方向如果业务要保留没有用券的订单正确写法应该是把条件放在ON子句里SELECT o.order_id, c.coupon_amount FROM orders o LEFT JOIN coupon_usage c ON o.order_id c.order_id AND c.coupon_type DISCOUNT;这种情况下优化器不能把c.coupon_typeDISCOUNT下推到coupon_usage的扫描层并直接过滤掉NULL扩展行因为它还要保留订单表的所有行。它只能把条件作为连接条件的一部分或者做其它补偿机制。如果优化器强行下推结果集就会缺少未用券的订单这是严重正确性错误。所以在外连接场景里下推正确性是第一位的。我看到的技术方案中处理办法是让条件保持在JOIN条件内部或者使用CASE WHEN表达式来保证外连接语义。实践里我一般会先确认业务字段是否允许为空再决定用WHERE还是ON。如果在ON里写下推条件SQL逻辑上是安全的优化器自身的代价估算也会更从容。3.3 谓词带函数、带子查询时下推失效的处理复杂场景里还有一大类过滤条件不是简单的列比较而是WHERE UPPER(s.name) ZHANG或者WHERE a.create_time INTERVAL 1 day NOW()。这种函数包裹列的情况很多数据库的索引和统计信息都无法直接估算导致谓词下推失效。因为优化器不确定函数的选择率甚至认为这个条件不能安全地下推到索引范围扫描层。有个技巧是函数改写为对列的直接比较比如把WHERE DATE(create_time) 2023-01-01改写成WHERE create_time 2023-01-01 AND create_time 2023-01-02。这一步不单是为了索引更是为了让优化器有机会把这个区间谓词下推到扫描层并参与分区裁剪。再比如子查询场景很多优化器对相关子查询里的谓词下推是保守的但如果你把子查询转换成JOIN往往就能重新获得下推空间。我自己在优化一个多天汇总SQL时最常用的就是先EXPLAIN看子查询是否被物化如果物化成本高我会手动把它拆成临时表并提前过滤效果立竿见影。还有一种方法是使用表达式索引如PostgreSQL的CREATE INDEX ON t (UPPER(name))或者生成列让函数表达式有独立的统计信息。有了这些统计信息优化器才能给下推后的节点算出一个靠谱的代价否则它宁可保守地延迟过滤。记住一个原则下推的前提是优化器能够估算收益估算收益的前提是有统计信息。没有统计信息宁可不推。3.4 分区表与下推的“化学反应”分区表是连接条件下推最容易放大收益的场景也是最容易翻车的场景。假设一个事实表按月份分区查询里带了order_date 2023-01-01 AND order_date 2023-04-01优化器如果能把条件下推到分区扫描层就能直接做分区裁剪只扫描三个月的数据。这个收益比单纯的行级过滤大得多。现代优化器确实会这么做而且把它视为“分区裁剪”和“谓词下推”的协同效果。但复杂场景里分区键上的条件往往藏在连接条件的另一侧。比如SELECT ... FROM fact_sales f JOIN dim_date d ON f.sale_date d.date_key WHERE d.year 2023 AND d.month BETWEEN 1 AND 3;如果优化器不能把d.year2023这样的条件下推到连接之前并映射到fact_sale的分区键那分区裁剪就失效了整个fact_sales全表都要读。我遇到过不少这类问题解决手段一般是两种一是改写SQL直接把日期条件也加到fact_sales表上作为冗余过滤二是依赖数据库的“连接条件下推”能力看它能不能把维度表的常量条件传递到事实表一侧。PostgreSQL等数据库有类似“join clause pushdown”的机制但并非总是有效。如果看到执行计划里事实表还是全分区扫描就别硬扛了在SQL里加冗余条件吧。只要业务上能保证两个条件等价就行。4. 用代价说话执行计划阅读与下推验证的实操方法4.1 下推前后的执行计划长什么样先看一个典型的PostgreSQL例子EXPLAIN (ANALYZE, BUFFERS) SELECT o.order_id, u.user_name FROM orders o JOIN users u ON o.user_id u.id WHERE o.status PAID AND u.reg_date 2023-01-01;假设优化器选择把条件下推到两侧表扫描计划会呈现类似这样的结构Hash Join (cost... rows...) Hash Cond: o.user_id u.id - Seq Scan on orders o (cost... rows...) Filter: (status PAID::text) - Hash - Seq Scan on users u (cost... rows...) Filter: (reg_date 2023-01-01)如果优化器没下推你可能会看到一个Filter: ((o.status PAID) AND (u.reg_date 2023-01-01))挂在Hash Join的上层而两个扫描节点下面没有任何Filter对应的扫描行数也会明显变大。看执行计划时重点关注每个扫描节点的rows估算和实际actual rows差异。如果上层Filter过滤效果很强而两个扫描节点估算行数都很大那么很可能这就是一个没做下推的计划。Oracle的DBMS_XPLAN输出也类似。注意看Predicate Information部分会显示access和filter两个类别。如果条件出现在filter而不是access说明它没有被有效地推入到索引访问路径。在多表连接中你还会看到类似PUSHED PREDICATE的注释比如在做嵌套循环连接时被驱动侧有access(A.ID... AND ...)说明连接条件下推成功了。4.2 手动干预Hint与优化器参数调整优化器不是永远正确的而且复杂场景下它的估算误差可能让它做出错误的下推选择。手动干预常见有三类强制下推Oracle里可以用/* PUSH_PRED */提示优化器把关联条件下的推到连接前用NO_PUSH_PRED禁掉。PostgreSQL没有直接对谓词下推的Hint但可以通过调整enable_hashjoin、enable_nestloop、enable_seqscan这些开关间接影响计划形态。控制连接顺序用/* LEADING(t1 t2) */Oracle或/* leading(t1 t2) */PostgreSQL若装有pg_hint_plan插件来强制驱动顺序。下推是否发生往往取决于驱动顺序因为优化器只对驱动侧的基表扫描做下推估算。关闭某种连接算法当你确认嵌套循环因为数据倾斜会被拖垮时可以临时关闭enable_nestloop观察计划是否选择哈希连接并借此验证下推收益。要强调一点这些干预手段不是生产环境的长期依赖它们的作用是帮我们验证“如果下推了会怎样”。我在定位慢查询时经常先用一个Hint强制出不推荐的计划观察两个计划的代价差如果差异巨大说明统计信息或者代价模型有问题我会返回去查统计信息而不是直接把这个Hint留在线上。4.3 实验对比三组SQL验证下推收益聊一个可复用的实验方法。拿到一条慢查询先做三组实验第一组是原SQL看当前计划。记下总耗时、扫描行数、连接中间行数。第二组是通过Hint或者改SQL强制下推某个关键过滤条件观察计划变化。比如把过滤条件从JOIN上层挪到子查询里或者用/* PUSH_PRED */强制下推。第三组是把统计信息更新到最新后什么都不改重新执行原SQL看优化器是否自己选回下推计划。具体操作上我会把三组SQL的执行计划截图、耗时、Buffers、actual rows记录到一张表格里。通过对比通常能得到一个结论下推失效的原因到底是统计信息过期还是优化器代价模型评估误差还是SQL本身无法安全下推。这样的实验做完你对系统的理解会比单纯看任何Documentation都深刻。比如我最近处理一个数据仓库同步脚本里的慢查询就是靠这三组实验定位到原因是左外连接的一个条件被业务放在了WHERE子句导致优化器把整个连接改写成内连接但因为语义刚好等价下推之后扫描数据量反而少了三倍。如果把条件改成ON子句业务语义能保留但下推后驱动行数变大整体变慢。这个案例告诉我们哪怕是同一个字段的过滤条件放在WHERE还是ON连接条件下推的代价空间完全不同。5. 常见问题与排查技巧实录结合我自己的踩坑经历把一些高频问题整理出来供参考。问题现象常见原因排查与建议过滤条件下推了但执行时间反而变长统计信息不准低估/高估过滤后行数下推改变了连接顺序更新统计信息查看驱动表实际行数必要时用Hint对比大表时间条件下推后还是全表扫描条件列没有索引或优化器认为索引选择性差确认是否建了合适的索引或改用分区表外连接右表条件下推后结果集少了条件被放到WHERE而非ON语义等价于内连接检查业务需求把条件移到ON子句子查询内部谓词没有下推相关子查询导致优化器无法安全下推尝试改写为JOIN或拆成临时表加过滤带函数谓词无法下推函数包裹列统计信息和索引失效改写为区间条件或建表达式索引分区表和维度表关联时无法裁剪分区键上的条件没有被传递到事实表检查执行计划的分区裁剪标记必要时冗余过滤条件5.1 统计信息更新了为什么计划还是老样子这里有一个很容易误判的点统计信息只是优化器做代价估算的输入不是唯一输入。有时候你把统计信息刷到最新但优化器还是会走老计划原因可能是连接顺序、内存参数、并行度设置把其他计划的代价拉高了。比如work_mem设置得过低哈希连接被估算成要用临时文件代价极高优化器宁可慢慢跑嵌套循环也不选哈希连接。这种时候下推了也没用真正的问题是资源配置不合理。我的排查顺序是先看两边的估算行数是否接近实际行数如果接近说明统计信息没问题再看连接节点后面的内存/临时文件估算如果临时文件代价吓人就去调对应参数最后才考虑用Hint做实验。不要一上来就怀疑优化器笨更多时候是我们的“环境参数”让优化器做出了合理但看似愚蠢的选择。5.2 怎么判断“下推失败”是故障还是正常选择有次一个同事跑过来问我“这个SQL明明可以把WHERE b.typeX下推到b表上为什么执行计划里没下推”我看了下数据分布b.type过滤后保留的行数超过b表的95%也就是几乎全保留下推不改变扫描行数但会改变连接顺序。优化器算了一下发现保持原连接顺序更便宜于是选择不下推。这种情况不是故障是代价模型给出的正确判断。判断标准很简单把条件下推后估算出来的总代价是否真的下降。不要只看扫描节点行数变没变。如果过滤之后还有90%的行那下推几乎不省IO反而可能因为改变了驱动顺序增加计算量。我在团队里会反复强调执行计划里有没有看到下推不重要执行计划的总代价和实际运行时间才是真正要盯住的东西。5.3 一个压箱底的小技巧用EXPLAIN (ANALYZE)验证“下推前”和“下推后”的行数很多DBA拿到慢SQL后只看执行计划的总耗时很少去看每个节点的actual rows。其实连接条件下推的收益完全反映在扫描节点的实际行数和上层连接节点的实际行数差值上。比如你在执行计划里看到users表Seq Scan实际扫了500万行但它在Hash Join之后只有一个Filter能把结果缩减到100行那说明过滤没有下推白白扫了500万行。这时你应该优先审视为什么条件下推失败是统计问题还是SQL写法问题。反过来如果扫描节点已经过滤到只剩100行但连接后的Filter又把这100行进一步缩到10行那么下推操作已经没有更多收益空间再纠结是不是全下推意义不大。这种“先看实际行数再决定要不要干预”的思路能帮我们避免在无效优化上浪费时间。我自己优化慢SQL时几乎全靠actual rows来判定优化点而不是靠猜。回到开头说的那层意思连接条件下推只是一场基于代价的“换位思考”它让过滤尽早把关但必须服从全局代价。复杂场景里真正重要的不是记住“下推好还是不好”而是搞清楚优化器拿什么数据来做决策以及我们怎么通过统计信息、SQL改写和执行计划验证来帮助它做对决策。我个人在实际操作中体会到几乎所有下推相关的“灵异事件”最后都能追溯到统计信息不新、数据分布倾斜或写法语义模糊这三件事上。所以下次再遇到奇怪计划先把这三个方向排查一遍大概率能绕开很多坑。
复杂场景下基于代价的连接条件下推：优化器的权衡之道
连接条件下推这个术语在数据库优化圈子里已经不算新鲜但我这些年跟执行计划打交道发现真正能在复杂业务场景里把这条规则用明白的人其实不多。大家背得耳熟能详的一句话叫“过滤条件下推到扫描层能少读很多数据”可真到了多表关联、外连接、子查询、分区表凑一块儿的时候到底推不推、推到哪一侧、推了之后代价是涨是跌很多优化器自己都会犯迷糊。这篇文章想聊的就是这个“复杂场景下基于代价的连接条件下推”我尽量用自己的实践经验讲清楚优化器在背后是怎么算账的以及我们怎么提前判断它的选择。说直白一点连接条件下推就是在执行连接操作之前把一部分过滤条件下沉到更底层的表扫描或者索引扫描上让进入连接阶段的行数变少。这个动作听起来简单但一旦涉及多张表、多种连接方式、统计信息不准或者数据分布倾斜光靠“能推就推”的经验法则就完全不靠谱了。现代数据库优化器之所以要搞成基于代价的就是因为它需要在成千上万种执行计划里用模型估算出哪个计划真正花钱少。这篇文章适合三类人看一类是天天要调慢SQL的DBA一类是做数据库内核研发或优化器开发的工程师还有一类是面试前想把谓词下推这道题吃透的候选人。读完你至少能搞清楚为什么下推不是无脑最优、哪些场景容易翻车、以及怎样用手里的执行计划工具验证优化器的决策。1. 别被“下推”两个字骗了这其实是优化器的“换位思考”1.1 一次最简单、也最容易说的“搬家”先用一条SQL把概念固定下来SELECT o.order_id, u.user_name FROM orders o JOIN users u ON o.user_id u.id WHERE o.status PAID AND u.reg_date 2023-01-01;这条SQL涉及两张表一个连接条件两个过滤条件。优化器至少有四种候选思路先把orders表扫一遍过滤status‘PAID’然后跟users表做连接先把users表扫一遍过滤reg_date条件然后跟orders表连接两张表原始数据直接连接形成中间结果后再过滤掉不符合条件的行把两个条件分别下推到各自的表扫描做连接前裁剪。第4种就是我们常说的“连接条件下推”。它把过滤谓词从连接之后的Project/Filter节点挪到了表扫描或索引扫描的入口。这样做最直接的好处是减少进入连接节点的行数也意味着连接过程中需要比较、需要缓存、需要写临时文件的数据量都变小了。但注意我说的是“变小了”没说“一定更快”因为代价模型认为还要看驱动顺序、连接算法、数据分布。当年我第一次接触这个概念时觉得这就像打扫房间前先把垃圾装进垃圾袋再统一运出去怎么想都是划算的。直到碰到一个场景把条件下推到某个大表后优化器反而选择了一个挂着几十万行临时结果的执行计划我才意识到这事情没那么简单。1.2 为什么“能推就推”在复杂场景下会失灵假设有A、B两张表A有1000万行B有100万行。A表某个过滤条件把数据从1000万降到10万B表的过滤条件只把数据从100万降到99万。看起来A侧下推收益巨大B侧收益很小。但如果我们把A侧条件推到表扫描层结果A表的扫描路径变了——可能是全表扫描加滤波而不是原有索引范围扫描。如果这个条件列上的索引效果很差统计信息又显示过滤后仍有300万行那优化器给出的扫描代价可能反而高于不快分区扫描后直接做连接。再叠加连接算法的影响下推后A表数据量减少嵌套循环连接变便宜但如果B表原本可以用哈希连接一次性读入内存而A侧过滤后数据量少到驱动侧可以显著降低探测次数这时候下推又有利。所有这些连锁反应正是代价模型要解决的问题。简单规则“下推一定好”本质上只考虑了一个局部效应忽视了全局路径选择。所以在复杂场景下优化器不是机械地执行“遇到过滤条件就下推”而是把它作为搜索空间里的一个候选变换套入代价公式去算TotalCost ScanCost JoinCost ProbeCost ...然后选择最小代价。理解这一点才是理解整篇文章的钥匙。2. 复杂场景下的四大决策变量统计、选择率、Join形态、数据分布2.1 统计信息没有准确基数代价模型就是猜谜任何基于代价的优化前提都是统计信息足够准确。表的行数、列的唯一值数量、NULL值比例、平均值长度、直方图桶分布等等都是优化器做代价估算的输入。连接条件下推的收益估算尤其依赖过滤条件的选择率。比如u.reg_date 2023-01-01实际能过滤掉95%的数据但如果统计信息里这个字段的直方图长时间没更新优化器可能以为只能过滤掉30%的数据于是它就不愿意把条件下推到users表扫描层宁可选择先连接再过滤。我遇到过最典型的一个现场一张日增长量上百万的流水表另一张维度表只有几十万行。业务SQL按“流水的创建时间”做过滤按说应该把时间条件下推到大流水表侧大幅缩小扫描范围。但因为表的统计信息是七天前的优化器低估了数据量的变化导致采样预测出来的选择率严重偏差最终选择了重排连接顺序、先把维度表广播出去再做连接的糟糕计划。后来刷新统计信息执行计划立刻变成了先对流水表做分区裁剪并下推时间条件。这件事给我留下的印象是下推决策不是独立的它的前提是统计信息健全。所以调优第一步永远是ANALYZE或对应的统计信息更新命令而不是急着看执行计划。2.2 选择率估算三张表和三百张表的差别选择率指的是一个谓词预计能保留多少比例的行。单表情况下选择率常常用“唯一值数目的倒数”估算多表连接时还要考虑连接列之间的相关性、重叠情况。如果一张表的过滤条件是a.x 100 AND a.y 200而x和y本身有强相关性选择率不能简单相乘。优化器对这一点通常是依赖列统计和扩展统计信息的。连接条件下推特别怕的也是选择率估算离谱。想象一个查询连接了订单表、订单明细表、商品表、店铺表四张表。商品表上有个category_id 5的过滤条件。如果商品表统计信息准确它知道category_id5只占全部商品行的0.1%下推后驱动行数极少整个连接顺序都能改写。但如果统计信息丢失优化器可能认为这个条件只过滤掉一半数据商品表依然是大驱动表哪怕其它条件下推也救不回来。在多表场景里选择率错一点代价模型的误差会被连接链放大。一个条件的估算偏差是2倍经过三级连接可能变成8倍。所以当你在上百张表的复杂报表里发现优化器没做下推时先怀疑的不是优化器代码而是统计信息估算。我经常用“如果一个谓词的选择率估出两倍误差它可能影响三个连接节点最终代价差一个数量级”来向团队解释为什么DBA要盯着统计信息。2.3 Join形态决定下推方向内连接、外连接、半连接并非所有Join类型都允许把过滤条件无脑下推。内连接比较宽松因为内连接本身不保留未匹配行把任何一侧的等下推下去都不会改变最终语义。外连接麻烦一些尤其是左外连接时如果把右表上的过滤条件下推到右表扫描层可能会把本来要保留为NULL扩展行的右表记录提前过滤掉导致结果集少行语义就错了。所以优化器在这个场景必须谨慎。它只能做“安全下推”或者把这类条件下推到外连接的内侧保留外侧的前提下做某种补偿。半连接和反连接也有类似讲究比如EXISTS子查询、NOT EXISTS子查询。子查询里的过滤条件是否能够下推到子查询内部取决于它是否引用了外层表的列。如果完全不相关那它就是独立子查询可以放心下推如果相关通常需要用initplan或者延迟物化的方式处理。实践里最常用的一招是把相关子查询改写成显式的连接很多时候可以让连接条件下推有更大的施展空间。我在优化一个嵌套很深的报表SQL时把三个IN子查询改成临时表连接优化器一下就选择了正确下推执行时间从28秒降到1.6秒。2.4 数据倾斜与直方图最容易被忽略的一环基数和选择率没错不代表代价就算得准还有一个大坑是数据分布倾斜。假设订单表按用户ID连接用户表其中有一个超级用户占了全部订单的40%。过滤条件是用户等级VIP那个超级VIP用户的数据量远高于普通用户。如果优化器只看到用户级别过滤后总行数减少到20%它可能觉得下推之后驱动行数小于是选择用嵌套循环连接从驱动侧逐行去探测被驱动侧。等到驱动侧遇到那个超级VIP用户时对端匹配行可能有上百万行整个计划被拖垮。直方图就是用来缓解这个问题的。优化器会通过直方图记录高频值让代价模型知道某些值的选择率和平均情况不同。即便如此直方图桶的数量有限倾斜严重时依然可能出错。所以面对有热点数据的大表我会额外关注执行计划里有没有出现Nested Loop驱动大量数据的情况并且会用TOP-N或者HASH JOIN改写倾向来调控。连接条件下推在这里的争议点是下推可以让整体驱动行数变小但也可能放大倾斜值的影响因为它改变了连接顺序。基于代价的优化器就是在这个粒度上权衡而我们能做的是给它足够好的直方图和足够多的自由度。3. 典型复杂场景实践我踩过的坑与验证过的方法3.1 多表连接链上下推位置不是越早越好我见过不少同学拿着“过滤条件下推好”这句话看到执行计划里某个条件没在最底层扫描上就着急。举个实际业务例子简化后的查询是这样SELECT ... FROM dim_shop s JOIN fact_order o ON o.shop_id s.shop_id JOIN dim_sku k ON k.sku_id o.sku_id WHERE s.city 上海 AND k.category 数码;这个连接链是dim_shop - fact_order - dim_sku。直觉上应该把s.city上海下推到dim_shop扫描把k.category数码下推到dim_sku扫描让两个维度表先变小再去碰大的事实表。大多数情况下这确实是正确选择。但有一种情况不是dim_shop过滤后还剩下几万行而fact_order在shop_id上有索引如果以dim_shop为驱动表对fact_order做索引探测每行驱动行去fact_order上查一次总共几万次索引探测可能比先把fact_order和dim_sku做哈希连接再过滤更快。这种时候优化器基于代价会干脆放弃对s.city的提前下推把它保留在连接链上层先过滤dim_shop或者使用hash join把大表连接完再过滤。从执行计划形态上看过滤条件确实在上层但结果集可能是最小的。所以我们在看复杂连接链时不要只问“这个条件下推了吗”要问“下推之后连接顺序和连接算法发生了什么变化”。下推本身是手段不是目的。最终目的是让整体执行代价变小。3.2 外连接下推要小心“侧塌”有一次我在优化一个订单报表SQL里面有一个左外连接SELECT o.order_id, c.coupon_amount FROM orders o LEFT JOIN coupon_usage c ON o.order_id c.order_id WHERE c.coupon_type DISCOUNT;业务本意是找出用了折扣券的订单。但这条SQL的写法有个语义陷阱WHERE c.coupon_type DISCOUNT在左外连接之后过滤会把所有c为空的行删掉实际等价于内连接。当时优化器在PostgreSQL里给出的计划是先把coupon_usage过滤后再做Hash Join效果上把左外连接变成了逻辑内连接这是被允许的因为WHERE条件限定了右表字段非空。可是我们DBA团队里有人用“外连接不能下推右表条件”的教条去质疑这个计划其实是不对的。真正危险的是另一个方向如果业务要保留没有用券的订单正确写法应该是把条件放在ON子句里SELECT o.order_id, c.coupon_amount FROM orders o LEFT JOIN coupon_usage c ON o.order_id c.order_id AND c.coupon_type DISCOUNT;这种情况下优化器不能把c.coupon_typeDISCOUNT下推到coupon_usage的扫描层并直接过滤掉NULL扩展行因为它还要保留订单表的所有行。它只能把条件作为连接条件的一部分或者做其它补偿机制。如果优化器强行下推结果集就会缺少未用券的订单这是严重正确性错误。所以在外连接场景里下推正确性是第一位的。我看到的技术方案中处理办法是让条件保持在JOIN条件内部或者使用CASE WHEN表达式来保证外连接语义。实践里我一般会先确认业务字段是否允许为空再决定用WHERE还是ON。如果在ON里写下推条件SQL逻辑上是安全的优化器自身的代价估算也会更从容。3.3 谓词带函数、带子查询时下推失效的处理复杂场景里还有一大类过滤条件不是简单的列比较而是WHERE UPPER(s.name) ZHANG或者WHERE a.create_time INTERVAL 1 day NOW()。这种函数包裹列的情况很多数据库的索引和统计信息都无法直接估算导致谓词下推失效。因为优化器不确定函数的选择率甚至认为这个条件不能安全地下推到索引范围扫描层。有个技巧是函数改写为对列的直接比较比如把WHERE DATE(create_time) 2023-01-01改写成WHERE create_time 2023-01-01 AND create_time 2023-01-02。这一步不单是为了索引更是为了让优化器有机会把这个区间谓词下推到扫描层并参与分区裁剪。再比如子查询场景很多优化器对相关子查询里的谓词下推是保守的但如果你把子查询转换成JOIN往往就能重新获得下推空间。我自己在优化一个多天汇总SQL时最常用的就是先EXPLAIN看子查询是否被物化如果物化成本高我会手动把它拆成临时表并提前过滤效果立竿见影。还有一种方法是使用表达式索引如PostgreSQL的CREATE INDEX ON t (UPPER(name))或者生成列让函数表达式有独立的统计信息。有了这些统计信息优化器才能给下推后的节点算出一个靠谱的代价否则它宁可保守地延迟过滤。记住一个原则下推的前提是优化器能够估算收益估算收益的前提是有统计信息。没有统计信息宁可不推。3.4 分区表与下推的“化学反应”分区表是连接条件下推最容易放大收益的场景也是最容易翻车的场景。假设一个事实表按月份分区查询里带了order_date 2023-01-01 AND order_date 2023-04-01优化器如果能把条件下推到分区扫描层就能直接做分区裁剪只扫描三个月的数据。这个收益比单纯的行级过滤大得多。现代优化器确实会这么做而且把它视为“分区裁剪”和“谓词下推”的协同效果。但复杂场景里分区键上的条件往往藏在连接条件的另一侧。比如SELECT ... FROM fact_sales f JOIN dim_date d ON f.sale_date d.date_key WHERE d.year 2023 AND d.month BETWEEN 1 AND 3;如果优化器不能把d.year2023这样的条件下推到连接之前并映射到fact_sale的分区键那分区裁剪就失效了整个fact_sales全表都要读。我遇到过不少这类问题解决手段一般是两种一是改写SQL直接把日期条件也加到fact_sales表上作为冗余过滤二是依赖数据库的“连接条件下推”能力看它能不能把维度表的常量条件传递到事实表一侧。PostgreSQL等数据库有类似“join clause pushdown”的机制但并非总是有效。如果看到执行计划里事实表还是全分区扫描就别硬扛了在SQL里加冗余条件吧。只要业务上能保证两个条件等价就行。4. 用代价说话执行计划阅读与下推验证的实操方法4.1 下推前后的执行计划长什么样先看一个典型的PostgreSQL例子EXPLAIN (ANALYZE, BUFFERS) SELECT o.order_id, u.user_name FROM orders o JOIN users u ON o.user_id u.id WHERE o.status PAID AND u.reg_date 2023-01-01;假设优化器选择把条件下推到两侧表扫描计划会呈现类似这样的结构Hash Join (cost... rows...) Hash Cond: o.user_id u.id - Seq Scan on orders o (cost... rows...) Filter: (status PAID::text) - Hash - Seq Scan on users u (cost... rows...) Filter: (reg_date 2023-01-01)如果优化器没下推你可能会看到一个Filter: ((o.status PAID) AND (u.reg_date 2023-01-01))挂在Hash Join的上层而两个扫描节点下面没有任何Filter对应的扫描行数也会明显变大。看执行计划时重点关注每个扫描节点的rows估算和实际actual rows差异。如果上层Filter过滤效果很强而两个扫描节点估算行数都很大那么很可能这就是一个没做下推的计划。Oracle的DBMS_XPLAN输出也类似。注意看Predicate Information部分会显示access和filter两个类别。如果条件出现在filter而不是access说明它没有被有效地推入到索引访问路径。在多表连接中你还会看到类似PUSHED PREDICATE的注释比如在做嵌套循环连接时被驱动侧有access(A.ID... AND ...)说明连接条件下推成功了。4.2 手动干预Hint与优化器参数调整优化器不是永远正确的而且复杂场景下它的估算误差可能让它做出错误的下推选择。手动干预常见有三类强制下推Oracle里可以用/* PUSH_PRED */提示优化器把关联条件下的推到连接前用NO_PUSH_PRED禁掉。PostgreSQL没有直接对谓词下推的Hint但可以通过调整enable_hashjoin、enable_nestloop、enable_seqscan这些开关间接影响计划形态。控制连接顺序用/* LEADING(t1 t2) */Oracle或/* leading(t1 t2) */PostgreSQL若装有pg_hint_plan插件来强制驱动顺序。下推是否发生往往取决于驱动顺序因为优化器只对驱动侧的基表扫描做下推估算。关闭某种连接算法当你确认嵌套循环因为数据倾斜会被拖垮时可以临时关闭enable_nestloop观察计划是否选择哈希连接并借此验证下推收益。要强调一点这些干预手段不是生产环境的长期依赖它们的作用是帮我们验证“如果下推了会怎样”。我在定位慢查询时经常先用一个Hint强制出不推荐的计划观察两个计划的代价差如果差异巨大说明统计信息或者代价模型有问题我会返回去查统计信息而不是直接把这个Hint留在线上。4.3 实验对比三组SQL验证下推收益聊一个可复用的实验方法。拿到一条慢查询先做三组实验第一组是原SQL看当前计划。记下总耗时、扫描行数、连接中间行数。第二组是通过Hint或者改SQL强制下推某个关键过滤条件观察计划变化。比如把过滤条件从JOIN上层挪到子查询里或者用/* PUSH_PRED */强制下推。第三组是把统计信息更新到最新后什么都不改重新执行原SQL看优化器是否自己选回下推计划。具体操作上我会把三组SQL的执行计划截图、耗时、Buffers、actual rows记录到一张表格里。通过对比通常能得到一个结论下推失效的原因到底是统计信息过期还是优化器代价模型评估误差还是SQL本身无法安全下推。这样的实验做完你对系统的理解会比单纯看任何Documentation都深刻。比如我最近处理一个数据仓库同步脚本里的慢查询就是靠这三组实验定位到原因是左外连接的一个条件被业务放在了WHERE子句导致优化器把整个连接改写成内连接但因为语义刚好等价下推之后扫描数据量反而少了三倍。如果把条件改成ON子句业务语义能保留但下推后驱动行数变大整体变慢。这个案例告诉我们哪怕是同一个字段的过滤条件放在WHERE还是ON连接条件下推的代价空间完全不同。5. 常见问题与排查技巧实录结合我自己的踩坑经历把一些高频问题整理出来供参考。问题现象常见原因排查与建议过滤条件下推了但执行时间反而变长统计信息不准低估/高估过滤后行数下推改变了连接顺序更新统计信息查看驱动表实际行数必要时用Hint对比大表时间条件下推后还是全表扫描条件列没有索引或优化器认为索引选择性差确认是否建了合适的索引或改用分区表外连接右表条件下推后结果集少了条件被放到WHERE而非ON语义等价于内连接检查业务需求把条件移到ON子句子查询内部谓词没有下推相关子查询导致优化器无法安全下推尝试改写为JOIN或拆成临时表加过滤带函数谓词无法下推函数包裹列统计信息和索引失效改写为区间条件或建表达式索引分区表和维度表关联时无法裁剪分区键上的条件没有被传递到事实表检查执行计划的分区裁剪标记必要时冗余过滤条件5.1 统计信息更新了为什么计划还是老样子这里有一个很容易误判的点统计信息只是优化器做代价估算的输入不是唯一输入。有时候你把统计信息刷到最新但优化器还是会走老计划原因可能是连接顺序、内存参数、并行度设置把其他计划的代价拉高了。比如work_mem设置得过低哈希连接被估算成要用临时文件代价极高优化器宁可慢慢跑嵌套循环也不选哈希连接。这种时候下推了也没用真正的问题是资源配置不合理。我的排查顺序是先看两边的估算行数是否接近实际行数如果接近说明统计信息没问题再看连接节点后面的内存/临时文件估算如果临时文件代价吓人就去调对应参数最后才考虑用Hint做实验。不要一上来就怀疑优化器笨更多时候是我们的“环境参数”让优化器做出了合理但看似愚蠢的选择。5.2 怎么判断“下推失败”是故障还是正常选择有次一个同事跑过来问我“这个SQL明明可以把WHERE b.typeX下推到b表上为什么执行计划里没下推”我看了下数据分布b.type过滤后保留的行数超过b表的95%也就是几乎全保留下推不改变扫描行数但会改变连接顺序。优化器算了一下发现保持原连接顺序更便宜于是选择不下推。这种情况不是故障是代价模型给出的正确判断。判断标准很简单把条件下推后估算出来的总代价是否真的下降。不要只看扫描节点行数变没变。如果过滤之后还有90%的行那下推几乎不省IO反而可能因为改变了驱动顺序增加计算量。我在团队里会反复强调执行计划里有没有看到下推不重要执行计划的总代价和实际运行时间才是真正要盯住的东西。5.3 一个压箱底的小技巧用EXPLAIN (ANALYZE)验证“下推前”和“下推后”的行数很多DBA拿到慢SQL后只看执行计划的总耗时很少去看每个节点的actual rows。其实连接条件下推的收益完全反映在扫描节点的实际行数和上层连接节点的实际行数差值上。比如你在执行计划里看到users表Seq Scan实际扫了500万行但它在Hash Join之后只有一个Filter能把结果缩减到100行那说明过滤没有下推白白扫了500万行。这时你应该优先审视为什么条件下推失败是统计问题还是SQL写法问题。反过来如果扫描节点已经过滤到只剩100行但连接后的Filter又把这100行进一步缩到10行那么下推操作已经没有更多收益空间再纠结是不是全下推意义不大。这种“先看实际行数再决定要不要干预”的思路能帮我们避免在无效优化上浪费时间。我自己优化慢SQL时几乎全靠actual rows来判定优化点而不是靠猜。回到开头说的那层意思连接条件下推只是一场基于代价的“换位思考”它让过滤尽早把关但必须服从全局代价。复杂场景里真正重要的不是记住“下推好还是不好”而是搞清楚优化器拿什么数据来做决策以及我们怎么通过统计信息、SQL改写和执行计划验证来帮助它做对决策。我个人在实际操作中体会到几乎所有下推相关的“灵异事件”最后都能追溯到统计信息不新、数据分布倾斜或写法语义模糊这三件事上。所以下次再遇到奇怪计划先把这三个方向排查一遍大概率能绕开很多坑。
