★ FEATURED ARTICLE 上周线上一个报表查询把MySQL打满了应用层超时告警刷了一屏。我拉出慢日志SQL本身不复杂三张表JOIN带两个过滤条件索引也建在关键列上照理说不该出问题。业务同学找过来的时候很笃定说“条件都写上去了是不是该加服务器了”。我看了执行计划才发现问题既不在索引也不在机器而在最基础的一条优化原理上——过滤条件压根没有被优化器“推”到它该待的位置。这就是连接条件下推要解决的事情。这条技术算是SQL性能优化里最容易被忽略、但收益又最立竿见影的一类。它不要求你改表结构不要求你换硬件大多数时候只需要你换一种写SQL的姿势或者搞清楚优化器为什么“犯懒”。这篇文章我会从一次真实故障讲起把连接条件下推的原理、边界、实测数据以及我在MySQL、数仓、MPP体系里踩过的坑和总结的经验一次讲透。1. 从一次线上事故说起过滤条件写全了执行计划却不走捷径1.1 事故现场一条三表JOIN慢查询那个报表查询大概长这样SELECT a.user_id, b.order_id, c.product_name FROM users a JOIN orders b ON a.user_id b.user_id JOIN order_items c ON b.order_id c.order_id WHERE a.register_date 2024-01-01 AND c.sku_status active;表规模大概是users 200万行orders 5000万行order_items 1.2亿行。索引方面初始状态是orders(user_id)有索引order_items(order_id)有索引users(register_date)也建了索引。这个配置在大多数开发眼里已经算达标了——关联键都有索引过滤字段也有索引。但实际跑出来的执行时间是多少47秒。这是把超时阈值直接打穿的级别。1.2 第一反应加索引实际不是索引的锅按惯性思维第一件事肯定是继续加索引。我试过在order_items(sku_status)上建索引在users(register_date, user_id)上建联合索引重启之后跑了两次执行时间从47秒降到44秒。可以说毫无本质变化。这时候才意识到瓶颈根本不在“能不能用索引”而在于数据到底是在什么时候被过滤掉的。如果你跟优化器说“先JOIN完了再把sku_statusactive的数据筛出来”那不管建多少索引扫描和JOIN的成本都已经实打实发生了。索引只能帮你少扫一些块但没法帮你把JOIN中间结果集从千万级降到万级。1.3 执行计划暴露真相过滤发生在JOIN之后用EXPLAIN ANALYZE看真实执行路径问题非常清晰。优化器把order_items作为第一张被扫描的表走了全表扫描然后在嵌套循环JOIN的过程中对每一行判断sku_statusactive。也就是说1.2亿行order_items几乎全部参与了哈希构建Hash Join场景或者嵌套循环Nested Loop场景而过滤是等JOIN产生结果之后才做的。这在执行计划里的样子就是Filter谓词出现在JOIN节点的输出端而不是扫描节点的输入端。连接条件下推想解决的问题简单说就一句话把过滤条件下推到尽可能靠近数据源的地方在扫描阶段就把该扔的行扔掉不要让没用的数据陪着JOIN跑完整个生命周期。2. 连接条件下推的底层逻辑为什么“先过滤再连接”几乎总是更优2.1 关系代数里的那张“交换律”王牌数据库优化器做下推数学基础其实是一张关系代数里的交换律如果谓词p只涉及表R的列那么σp(R ⋈ S) (σp R) ⋈ S翻译成人话“先过滤R再连接”和“先连接再过滤R”在逻辑上完全等价但前者明显更省。R如果从1000万行变成1万行那么JOIN的代价、中间结果的存储、网络传输分布式场景下全部会跟着降好几个量级。连接条件下推就是把这条交换律落到实处。WHERE子句里的条件能推到哪张表ON子句里的条件能推到哪一侧的扫描层本质都是在问这个谓词引用的列属于哪张表安全吗推过去之后语义会不会变2.2 连接条件下推的三个层次扫描过滤、驱动顺序、连接化简我在实际调优中习惯把连接条件下推拆成三个层次去看第一层单表扫描层的下推。这是最基础的一层。比如WHERE a.register_date 2024-01-01这个条件只引用users表的列那么优化器可以直接把它下推到users表的索引扫描条件里。体现出来就是Extra列出现“Using index condition”或者“Using where”扫描的rows从200万变成大几十万。第二层JOIN顺序里的下推。一张表过滤完之后它作为驱动表还是被驱动表直接影响下推的效果。如果过滤后的users只有20万行而orders有5000万行聪明的优化器会用20万行去驱动5000万行的索引探测而不是反过来。这个决策背后依赖统计信息也依赖下推后每个分支的预估行数。第三层连接化简。比如IN子查询转成半连接semi-join或者把多余的JOIN直接消掉。举例来说LEFT JOIN中如果右表的主键有唯一约束且WHERE条件过滤掉了右表所有NULL优化器可以把这个外连接改写成内连接从而在语义不变的前提下获得内连接的全部优化空间。2.3 为什么优化器明明能推却不推统计信息与代价模型很多人会问既然下推这么好优化器是傻吗为什么不总是推其实优化器不推通常有四个原因一是统计信息过期它以为过滤后的行数依然很大下推收益不足以改变计划二是语义上不安全涉及外连接、NULL语义、子查询边界时优化器会保守处理三是谓词形式太复杂函数包裹、隐式类型转换、OR条件让优化器无法判断是否等价四是某些场景下推反而更差比如过滤条件选择性极低过滤完还是几乎全表下推只会增加额外的计算开销优化器根据代价模型选择不下推。这就引出一个重要观点连接条件下推不是一条“永远正确”的铁律而是一条基于代价评估的启发式规则。我们写SQL要做的是尽量让优化器的代价评估往正确方向走而不是盲目追求“每条WHERE都提前”。3. 下推的边界四个高危场景改了写法才能保语义连接条件下推最大的敌人不是性能而是语义错误。很多开发在“优化”SQL时把条件从WHERE挪到ON结果数据就悄悄变了。这其实不是下推本身的问题而是对下推边界不熟。以下四个场景是我在评审SQL时几乎必查的。3.1 外连接陷阱WHERE右表条件悄悄改写语义这是最经典也最容易踩的一个坑。看这段SQLSELECT a.user_id, b.order_id FROM users a LEFT JOIN orders b ON a.user_id b.user_id WHERE b.order_status completed;表面上看是外连接保留users所有行。但WHERE里b.order_status completed这个条件一旦b没有匹配行b.order_status就是NULL永远不可能等于completed。于是这些行在WHERE阶段会被整体剔除。你以为自己在做LEFT JOIN实际执行语义已经等同于INNER JOIN。这种写法下优化器有两种可能一种是把外连接改写为内连接MySQL 8.0能识别这种情况那性能问题不大但结果可能和业务预期不符另一种是优化器保守处理不重写于是先做外连接产生大量NULL行再在JOIN之后过滤——性能炸裂。正确的做法是如果业务确实要“既保留users所有行又只看completed的订单”过滤条件应该放到ON里SELECT a.user_id, b.order_id FROM users a LEFT JOIN orders b ON a.user_id b.user_id AND b.order_status completed;这两条SQL结果集完全不同但很多业务方根本没意识到。在LEFT JOIN里WHERE指向右表的条件不是“下推”而是“降级”它会先把外连接变成内连接严格说是过滤掉NULL扩展行。这是语义层面的结构性变化。3.2 NULL与NOT IN下推后结果对不上NULL是三值逻辑里最麻烦的东西。看这个例子SELECT * FROM a WHERE a.id NOT IN (SELECT b.id FROM b);如果b.id存在NULL值NOT IN的语义是只要子查询结果集里出现一个NULL整个NOT IN条件就变成“既不成立也不不成立”最终结果集为空。这种条件下优化器如果按照常规的半连接反连接anti-join方式去下推结果可能和你预期的不一样——它可能会把NULL行正常处理导致返回了本不该返回的数据。这类问题在MySQL 8.0和PostgreSQL里处理方式不同但结论一样凡是IN/NOT IN/EXISTS/NOT EXISTS混用NULL时先确认业务语义再谈优化。我自己写SQL的习惯是业务上明确“不会出现NULL”的字段在表结构里直接设NOT NULL约束这样既保护数据也让优化器敢放手做反连接下推。3.3 包裹函数的过滤列物理下推了索引还是用不上连接条件下推管的是“过滤发生的阶段”但它管不了“索引能不能用”这件事。比如WHERE DATE(b.created_at) 2024-06-01如果b是内表这个条件虽然可以被下推到b的扫描阶段——好的一面是JOIN之前就过滤完了但坏的一面是DATE()函数让优化器无法直接使用created_at列上的索引只能退化成全表扫描之后逐行计算DATE再比对。下推成功只是第一步要让它产生最大收益还得配合改写函数形式。上面的写法如果业务只关心当天可以改成范围条件WHERE b.created_at 2024-06-01 00:00:00 AND b.created_at 2024-06-02 00:00:00同样的条件同样的下推位置后者能吃上索引性能可能差几十倍。这个坑在慢查询优化里出现频率极高尤其是报表场景的日期过滤。3.4 聚合边界GROUP BY上方与下方HAVING和WHERE的下推边界也很微妙。WHERE是在分组前过滤HAVING是在分组后过滤逻辑上下推不能越过GROUP BY这一层。比如SELECT user_id, COUNT(*) FROM orders WHERE order_amount 100 GROUP BY user_id HAVING COUNT(*) 5;WHERE order_amount 100可以被安全下推到orders扫描层但HAVING COUNT(*) 5是聚合完成之后的谓词它依赖COUNT的计算结果下推不了一点。有些数据库比如某些MPP可以部分下推HAVING里的无聚合条件但绝大多数情况下HAVING的条件要等到GROUP BY做完才能过滤。这类SQL优化空间在另一个方向能不能在子查询里先聚合、再JOIN从而让外层JOIN的输入行数大幅下降。这是“先聚合后关联”策略和连接条件下推互补。4. 用EXPLAIN实测同样的SQL下推前后的成本差了量级讲再多原理不如跑一组实测。我构造了一套可复现的数据用同一台MySQL 8.0实例验证连接条件下推前后的执行计划差异。4.1 测试环境与数据构造两张表故意做成“过滤后数据量差异巨大”的场景表a100万行字段id、group_id、typetype分布为90%的old和10%的new。表b500万行字段id、a_id、amountamount有5%的行大于1000。a_id上建了索引。目标查询是SELECT COUNT(*) FROM a JOIN b ON a.id b.a_id WHERE a.type new AND b.amount 1000;逻辑上最优做法先扫a中typenew的10万行再通过索引去b里定位同时b的amount1000这个条件在索引扫描时就参与过滤。这样真正参与连接的b表数据大概只有500万×5%约25万行而且其中绝大多数在扫描阶段就能通过索引条件排除。4.2 失败案例WHERE在JOIN后才过滤的计划我没建type上的索引并且写了一个会让优化器走向坏计划的版本SELECT COUNT(*) FROM a JOIN b ON a.id b.a_id WHERE a.type new AND b.amount 1000;执行计划关键输出简化如下idtabletypepossible_keysrowsExtra1bALLNULL500万Using where1arefPRIMARY约25万Using where看到这个计划的第一眼就知道完了b走了全表扫描500万行全部读出来然后对每一行做amount1000的过滤之后再去a表做ref探测。这相当于把过滤条件挂在扫描之后、连接过程之中下推完全没有生效。之所以会这样是因为优化器基于统计信息认为a.typenew过于普通当时统计信息里type分布没更新它以为能滤掉一半所以选择了从b驱动把b表整个铺开。这段查询跑了20秒。4.3 改造后条件在扫描节点就被吸收我把type索引补上同时更新统计信息并等价改写查询让过滤条件都贴合索引SELECT COUNT(*) FROM b JOIN a ON a.id b.a_id WHERE a.type new AND b.amount 1000;这次执行计划变成idtabletypepossible_keysrowsExtra1arefidx_type10万Using index condition1brefidx_a_id5000Using index conditiona表先通过idx_type直接读到10万行new数据然后作为驱动表每行去b表用idx_a_id做索引探测探测过程还能同时用上amount1000这个索引条件。最终查询耗时降到0.3秒左右。同一个业务问题同一个结果集性能差了六十多倍。4.4 一组直观的数字对比我一直觉得下推的收益不能只看执行时间要看JOIN两端的输入规模变化指标下推失败下推成功a表参与扫描行数约25万10万b表参与扫描行数500万约50万索引探测行实际JOIN结果行数约25万约5000查询耗时20s0.3s注意这次改造里我没有删任何列没有改表结构只补了一个type索引也没有换SQL骨架只是让过滤条件真正落到了扫描层。连接条件下推带来的不是百分之几十的优化而是量级的跳跃前提是优化器真的把条件推下去了。5. 出了单机数据库数仓和MPP体系怎么玩下推连接条件下推不止存在于MySQL、PostgreSQL这类单机数据库。在数据仓库和MPP架构里这个概念被玩得更开放而且表现形式完全不同。很多从单机数据库转过来的开发第一次接触数仓执行计划会懵——因为这里的“下推”已经不只是下推到扫描层而是下推到文件格式底层、存储节点甚至远端存储。5.1 存储层裁剪分区与列存的统计信息过滤这是数仓体系里最显著的下推升级。传统数据库下推最多到“扫描时用索引”而数仓里的下推可以直接到文件级别。拿Hive/Spark体系举例一张表按日期分区查询里写WHERE dt2024-06-01优化器做的不是“扫描每行再过滤”而是直接做分区裁剪把其它分区的文件整个跳过。如果表还用了Parquet或ORC列存格式下推还能更狠——Parquet文件自带min/max统计信息和Bloom FilterSpark在读取时会把过滤条件下推到文件读取层先看文件统计信息不满足条件的数据块直接跳过。这叫Predicate Pushdown to Storage本质上是连接条件下推思想在存储层的延伸。这也是为什么我建议数仓表设计时分区键和过滤条件强绑定。分区键选不好下推能力直接打五折。5.2 ClickHouse的PREWHERE和StarRocks的延迟物化ClickHouse里有个很典型的优化器功能叫PREWHERE很多人用不好。它的逻辑是把WHERE条件里代价最高、过滤性最强的条件单独提取出来在读取列数据之前先做过滤。这同时解决了两个问题一是减少解压和物化的数据量二是让过滤尽早发生。举个实际感受ClickHouse默认是先读取所有需要的列再统一做过滤。如果有一列是嵌套结构或大字符串物化成本极高而你的过滤条件明明只依赖另一列默认行为就会白白物化大量用不到的数据。用PREWHERE把过滤条件前置之后过滤性强的数据会在物化前就被扔掉查询可以从几百毫秒降到几十毫秒。StarRocks这边的思路叫延迟物化Late Materialization。它在Scan阶段先只读取过滤列确定哪些行真正满足条件之后再回过头读取SELECT需要的其它列。在高基数、宽表场景下收益特别大因为很多列根本不需要提前读出来。5.3 分布式JOIN策略对下推的影响MPP数据库里下推还涉及一个单机数据库没有的维度数据要不要跨节点传输。以StarRocks、Doris、ClickHouse分布式版本为例两张表JOIN时优化器先决定用什么策略如果小表足够小走Broadcast Join——把驱动表广播到每个节点被驱动表不用shuffle如果两个表都很大走Shuffle Join——按JOIN键哈希分布每台节点处理一部分数据。无论哪种策略WHERE条件下推都必须在JOIN策略确定之前完成。因为只有先确认过滤后的每张表大概多少行优化器才能决定小表能不能广播、分区裁剪能不能生效。如果条件没有下推统计信息虚高优化器会误判表大小选错JOIN策略于是一张本可以广播的小表被当成大表做shuffle整个集群的网络开销直接爆炸。这也是我在实际生产环境见过最多的数仓性能事故原因之一。6. 我调慢查询时的固定动作让SQL更匹配优化器的下推路线回到日常开发。很多人问我在不改变业务逻辑的前提下能不能有一套“无脑流程”让连接条件下推更好地生效。我这里总结了几条我自己的固定动作每次排查慢查询基本都会过一遍。6.1 看执行计划时先找Filter落在哪个节点很多开发看EXPLAIN只盯着rows和possible_keys我个人的第一反应却是先找所有Filter节点在什么位置。如果Filter出现在扫描节点的Extra列里说明谓词下推生效这是健康状态。如果Filter出现在Hash Join或Nested Loop的输出节点上说明过滤发生在JOIN之后那基本就是下推失败——这时候先别急着加索引把SQL结构打开理解为什么优化器不敢推。最常见的原因就是我在第三节里写的那些语义边界。MySQL里还可以打开优化器跟踪optimizer trace看具体决策过程SET optimizer_traceenabledon; SELECT * FROM your_query; SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;里面会记录每个谓词的“condition processing”环节包括是否被附加到表条件、是否被用于索引条件等。在MySQL 8.0里这是排查下推失败的第一利器。6.2 能下放的过滤不要后置写法的惯用手法代码评审时我常看到这类SQL模式先JOIN完整结果再在外面包一层WHERE过滤。像这样SELECT t.user_id FROM ( SELECT a.user_id, b.amount FROM a JOIN b ON a.id b.a_id ) t WHERE t.amount 1000;子查询把JOIN结果先完全算出来外层WHERE再过滤。如果数据库优化器没有做视图合并view merging这个amount1000就永远不会下推到b的扫描层中间结果集被白白放大。正确做法是把条件写进内层SELECT t.user_id FROM ( SELECT a.user_id, b.amount FROM a JOIN b ON a.id b.a_id WHERE b.amount 1000 ) t;只要语义等价尽量让WHERE贴着它引用的表而不是隔着子查询、视图或CTE。CTE在很多数据库里默认是物化边界如果一张CTE被多次引用优化器可能不敢把外层谓词推进去。6.3 和索引设计协同下推配合覆盖索引下推把过滤提前了但如果扫描层没准备好即使提前过滤也还是要回表。所以我的习惯是先确定哪些列会被下推成过滤条件再设计联合索引。比如前面那个例子一旦WHERE a.typenew注定要下推到a表扫描层那么索引最好直接定义成(type, id)或者(type, id, 其他必要列)。这样扫描时既能快速定位满足typenew的行又能顺手把JOIN需要的id列从索引里取出来省掉回表。最近遇到一个更细的场景MySQL 8.0的索引条件下推ICPIndex Condition Pushdown会在索引扫描时就把部分WHERE条件下推到存储引擎层。但ICP有一个前提就是谓词引用的列必须在索引里。如果你的过滤条件字段不在索引里ICP就帮不上忙——这进一步说明下推和索引是互相成全的关系不是二选一。6.4 参数与优化器开关的调整经验最后提一句参数层面。MySQL里控制JOIN行为的主要开关是optimizer_switch里面和连接顺序相关的有block_nested_loop、batched_key_access、hash_join等。在8.0里hash join默认开启但对小表驱动大表的场景会有额外开销如果某些复杂查询因为hash join选错路径可以尝试调低join_buffer_size或者临时禁用hash join观察下推是否登上了更好的计划。但老实说参数调整只能在小范围内救场根子还是在SQL写法和统计信息维护上。我处理过的下推失效案例里超过一半是因为统计信息明显过期导致优化器基于错误行数判断下推收益。定期跑ANALYZE TABLE并让慢查询监控覆盖到“计划变化”比研究任何开关都有效。我自己处理慢查询时养成了一个习惯拿到一条新SQL先不读索引先看执行计划里的Filter出现在哪一层。这个习惯救过我很多次——很多看起来需要重新设计表结构的查询最后改几行SQL就降了一个量级。连接条件下推不是什么高深魔法它就是优化器替我们表达的一个朴素原则越早扔掉的数据越不需要陪着JOIN跑完全程。理解它的边界和触发条件你就能在绝大多数性能问题面前先人一步找到答案。
阅读完成 · 觉得有帮助？
