有一年我接到一个线上慢查询工单某电商系统的订单表和用户表做 JOIN一条 SQL 跑了快四十分钟。我把 EXPLAIN 翻了个底朝天发现最刺眼的问题不是缺索引而是优化器压根没把可以下推的连接条件送进表扫描阶段。连接条件下推这件事听起来像数据库领域的黑魔法其实说白了就是一句话在 JOIN 真正开始之前先让每一张参与连接的表把该过滤的数据过滤掉只把必须要参与连接的行送到连接环节。但如果你没系统学过优化器的工作方式这四个字背后藏着一整套代价计算、规则推导和反直觉的坑。这篇文章就按我在生产环境里排查和优化慢查询的顺序把连接条件下推是什么、优化器靠什么决策、哪些写法会悄悄让它失效、怎么确认下推真的生效一次性讲清楚。适合正在调 SQL、看执行计划看到头秃的开发者和 DBA。1. 先搞清楚连接条件下推到底推的是什么1.1 一个真实案例接近40分钟的 JOIN 是怎么被救回来的当时那条 SQL 长这样逻辑非常简单SELECT o.order_id, u.user_name, o.amount FROM orders o INNER JOIN users u ON o.user_id u.user_id WHERE u.region 华东 AND o.order_time BETWEEN 2024-01-01 AND 2024-03-31;orders 表大概 1 亿行users 表 5000 万行。第一版执行计划里优化器选择的是 Hash Join两个表的读取都是全量扫描连最基本的过滤都没压到扫描阶段。Hash Join 的流程是先扫描一侧建哈希表再扫描另一侧去探测如果两边的表都肥得流油内存和 IO 都会崩。当时这条 SQL 把临时文件都写出来了慢得毫无悬念。我做的第一件事不是改 SQL而是看执行计划里Filter出现在哪一层。找到问题之后只做了两件事第一确认region这列的基数和分布华东大约只占 users 表的 2%第二在users(region)上补了一个索引同时把统计信息刷新了一遍。再跑EXPLAIN执行计划里多了一行Filter: (region 华东)而且这个 Filter 已经贴在 users 表扫描节点上了不再是挂在 Hash Join 节点上的一个“兜底条件”。实测从接近 40 分钟降到 6 分钟左右。这个案例特别典型它说明一个很多人没意识到的事实连接条件下推不是一个独立的高级特性而是优化器每天在做的基础工作。让它失效的原因通常也很朴素——统计信息过期、缺少合适索引、条件写得太绕。只要执行计划里 Filter 的位置不对性能天花板基本就锁死了。1.2 别把“谓词下推”和“连接条件下推”混为一谈很多人一听到“下推”就统一叫谓词下推严格说起来这是两件有交集但不完全一样的事。谓词下推指的是把 WHERE 里的过滤条件下推到表扫描或者索引扫描阶段执行目标是让扫描阶段少读行。连接条件下推则以 JOIN 关系为出发点把由连接推导出来的过滤条件、或参与连接的表自身的条件反向压到数据读取阶段。常见的连接条件下推长这样SELECT o.order_id, u.user_name FROM orders o INNER JOIN users u ON o.user_id u.user_id WHERE u.user_level VIP;u.user_level VIP虽然写在 JOIN 后面的 WHERE 里但它只约束 users 表优化器完全可以把这条件压到 users 表的扫描节点先筛出 VIP 用户再参与连接。这就是典型的下推。而更进一步的比如子查询改半连接后内层条件被压进去、JOIN 产生动态分区裁剪、构建端小表生成 Bloom Filter 后传给探测端扫描这些本质上都是“由连接关系引出的下推”。类别典型场景推到哪个阶段能解决的问题谓词下推WHERE 过滤条件表扫描 / 索引扫描减少单表读取行数连接条件下推JOIN 推导出的条件驱动表或内表扫描、分区裁剪、存储引擎过滤减少连接参与者降低连接代价运行时下推JOIN 一端动态生成条件探测端扫描阶段在大表扫描前剔除大量无关行理解区别的好处是排查时能精准定位。看执行计划时先问自己一句这个条件是不是应该被压进某个 Scan 节点如果它挂在 Join 节点上没动那大概率就是下推失败。1.3 优化器凭什么决定推还是不推一切的根源是选择性下推并不是无条件发生的。优化器的所有决定都建立在代价估算上而代价估算的核心输入是行数和基数。下推一个条件意味着在扫描阶段多付出一次条件判断的开销换来的是进入连接阶段的数据量变小。如果这个条件的过滤能力只有 0.01%也就是几乎全表都满足那下推节约的连接成本微乎其微反而可能因为扫描层的额外判断增加 CPU 开销。判断过滤能力靠的是统计信息里的直方图、唯一值数量、null 值占比这些数据。优化器估算出一个条件的选择性再乘上表的总行数得到预估行数然后分别计算“下推版本”和“不下推版本”的整体代价选便宜的。所以统计信息过期对下推的影响非常大。我曾经遇到过一张分区表新分区数据大量写入但没跑 ANALYZE优化器按照旧统计信息觉得某个过滤条件没选择性放弃了分区裁剪后来刷新统计信息之后执行计划立刻变了。这也解释了一个反直觉现象同样的 SQL在测试环境跑得很好到生产环境就慢。两张表的统计信息状态不同优化器的判断就完全不同。所以排查下推问题时第一步永远是确认统计信息是不是新鲜的这比折腾 SQL 写法重要得多。2. 优化器眼中的“下推机会”执行计划里最常见的三种模式2.1 WHERE 条件直接压进单表扫描最朴素也最容易漏掉最基础的场景就是把只属于单表的过滤条件压到该表的扫描节点。以刚才的例子来说理想执行计划中users 表扫描节点下方应该挂着区域过滤条件Hash Join Hash Cond: (o.user_id u.user_id) - Seq Scan on orders o - Hash - Seq Scan on users u Filter: (region 华东)如果执行计划里 Filter 出现在 Hash Join 节点那一层说明优化器把条件留在了连接之后才做这时候 users 表全量行都参与了哈希表构建代价立刻上去。判断标准很直接条件在哪个节点下方被计算就代表它在哪个阶段生效。数据库里还有另一种更底层的下推学名叫 Index Condition Pushdown一般缩写成 ICP。MySQL 从 5.6 开始支持作用是把部分 WHERE 条件从服务层下推到存储引擎层在读取索引记录时就直接做判断减少回表次数。举个常见的例子表上有复合索引(last_name, first_name)查询写成SELECT * FROM customers WHERE last_name Smith AND first_name LIKE A%;如果没有 ICP引擎先用索引定位所有last_name Smith的记录然后把每条记录回表取出来再在服务层判断first_name是否满足。有 ICP 之后引擎在索引扫描过程中就把first_name LIKE A%这个条件下压到存储引擎里判断不满足的直接跳过回表次数大幅下降。MySQL 执行计划里Extra列出现Using index condition就是触发了 ICP。这类下推值得单独点一下它和 STATS、JOIN 顺序都没关系纯粹是存储引擎层的优化。很多 DBA 看到Using index condition反而疑惑其实那是好消息说明优化器已经尽量把工作往数据源头推了。2.2 JOIN 关系引出的动态分区裁剪等到运行时才动手分区裁剪大家都不陌生SQL 里明确写了分区键的等值条件优化器可以在计划阶段直接砍掉不相关的分区。但有一种情况优化器在计划阶段根本没法裁剪事实表的分区键不是通过 WHERE 直接给出的而是通过 JOIN 从另一个表推导出来的。举个例子某用户行为分析平台的事实表events按日期分区查询要把一个“节假日日期表” JOIN 上来筛选所有节假日产生的事件SELECT e.user_id, COUNT(*) FROM events e INNER JOIN dates d ON e.date_id d.date_id WHERE d.is_holiday true GROUP BY e.user_id;计划阶段优化器不知道 dates 表里哪些日期是节假日所以没法提前裁剪 events 的分区。这类场景的解法是运行时动态裁剪先执行 dates 表那一侧拿到date_id的实际集合再造出分区过滤条件压到 events 表扫描阶段。Spark 里把这个特性叫动态分区裁剪3.x 版本以后在大数据场景里相当普及Trino、部分 MPP 引擎也有类似机制叫动态过滤或动态分区过滤。动态裁剪的价值在于它把“由连接推导出来的条件”真正下推到了扫描阶段而且是运行时才生成的条件。我见过一个日活查询在加了几行is_holiday过滤之后事实表扫描的分区数从 90 个直接降到十来个查询时间缩小了一个数量级。这里能看出连接条件下推的一个很典型的收益JOIN 一端的数据会反过来决定另一端要读多少数据。2.3 子查询改半连接把 IN 子查询的下推玩明白另一种高频场景是IN子查询比如SELECT o.order_id, o.amount FROM orders o WHERE o.user_id IN (SELECT user_id FROM users WHERE user_level VIP);很多开发者把这条 SQL 理解为“先执行子查询拿到 VIP 用户列表再和订单表比较”。早期的数据库确实是这么执行的先物化子查询结果再一条一条探测。现代优化器基本都会把这种写法改写成半连接也就是 Semi Join然后选择不同的执行策略。MySQL 从 5.6 开始就有这套转换并且提供了多种策略包括 Duplicate Weedout、FirstMatch、LooseScan、MaterializeLookup、MaterializeScan 等。策略大致思路适用场景FirstMatch内表遇到第一个匹配行就返回外表行内表行多、外表有索引MaterializeLookup先把子查询物化成临时表再按主键查临时表子查询结果集小MaterializeScan物化后扫描临时表去探测外表外表有索引Duplicate Weedout临时表去重消除重复匹配需要严格去重无论选哪种策略子查询里的user_level VIP都会尽可能早地作用在 users 表读取阶段这就是半连接转换带来的下推效果。执行计划里如果看到 Semi Join 而不是单独的 Subquery Scan通常说明优化器已经帮你把下推路径打通了。需要留个心眼的是NOT IN和NOT EXISTS。NOT IN遇到 NULL 值时语义会变成未知很多优化器宁可保守执行也不愿意把它转换成 Anti Join 再下推。如果业务上确认子查询结果不存在 NULL优先写NOT EXISTS它做反连接转换时更干脆后续下推也更顺畅。3. 更激进的下推从存储引擎层到分布式执行3.1 Bloom Filter 下推让大表扫描先“猜一轮”前面讲的下推都是把确定的条件往扫描层压。还有一类下推推下去的不是确定条件而是一个概率过滤器——Bloom Filter。Bloom Filter 的原理不复杂一个位数组加上若干个哈希函数。构建端把每一行数据的连接键映射到位数组里把对应位置置 1。探测端扫描时对每一行同样计算哈希检查位数组里的位是否都为 1。如果任何一个位是 0说明这行一定不在构建端集合里可以直接跳过如果都是 1说明“可能在”但需要做真正的连接验证。这个结构有一个关键性质不会误判“存在”只会误判“可能存在”。也就是说用它做过滤永远不会把需要的数据漏掉最多是多保留一些无效行。正是这个性质让 Bloom Filter 可以安全地下推到探测端的扫描阶段。在分布式查询引擎里常见做法是先用小表构建 Bloom Filter然后把它发送到大表所在的各个节点在大表扫描时先过一遍过滤器把明显不可能匹配的行直接丢掉。Trino 的动态过滤、部分 MPP 引擎的运行时过滤底层都有这个思路。我调过一个案例探测端是一张 20 亿行的用户行为表构建端是一个只有几万行的黑名单表。JOIN 之前先在行为表扫描层挂了一个 Bloom Filter扫描节点跳过了差不多 85% 的行。这种收益在传统嵌套循环里根本不可能出现。不是所有引擎都默认开启这个特性很多时候需要打开开关或者用 hint 提示但理解它之后看到执行计划里出现类似Dynamic Filter或者Runtime Filter的节点你就知道下推已经到了更深的一层。3.2 分布式引擎里的本地化 JOIN把连接算到数据身边去分布式环境下JOIN 还有一个更宏观的“下推”维度把连接计算推到数据所在的节点上而不是把数据全部搬到某个中心节点算。假设两张表都按user_id做了分片每个分片各自存储一部分数据。如果两张表的分片键恰好一致并且 JOIN 条件就是分片键那么每个分片在本地就能完成 JOIN最后把结果汇总即可。数据不需要跨节点传输网络开销趋近于零这被称为共置 JOIN 或者本地化 JOIN。MPP 数据库里分区键设计得合理时这类 JOIN 的性能优势非常夸张。另一种常见形态是广播 JOIN一张大表和一张小表连接优化器把小表广播到所有节点每个节点拿完整的小表去和本地的大表分片做连接。这种做法本质上是把连接的执行下推到每个数据节点避免大表分片到处乱跑。看到这里你会发现“下推”的含义在分布式语境下发生了微妙的变化。单机数据库里推的是过滤条件分布式引擎里推的还有执行位置。能推下去的 JOIN网络传输少推不下去的 JOIN数据要重新洗牌。所以在大数据平台里调优第一步永远是看执行计划里有没有Shuffle或者Exchange节点节点数越少说明执行越贴近数据所在的位置。3.3 外连接转内连接一个被低估的“隐形下推”还有一种转换经常被忽略但它同样是连接条件下推的一种表现外连接转内连接。看一条 SQLSELECT o.order_id, u.user_name FROM orders o LEFT JOIN users u ON o.user_id u.user_id WHERE u.region 华东;表面上是 LEFT JOIN但 WHERE 条件里加了u.region 华东。这个条件有一个特点如果 users 表没有匹配行那么u.region就是 NULLNULL 华东的结果不是真。换句话说这个 WHERE 条件会把所有没有匹配用户的订单行全部过滤掉。既然保留不了不匹配行LEFT JOIN 的语义就退化成和 INNER JOIN 完全一致优化器可以放心地把它当成内连接来优化。这个转换为什么有帮助因为内连接的选择余地比外连接大得多。优化器可以随意选择驱动表、调整连接顺序、选择哈希连接或者嵌套循环而外连接对表顺序有严格限制还经常需要保留 NULL 扩展行。转换之后原本压不进去的条件可能就有机会往扫描层走了。判断这种转换是否发生主要看执行计划里的连接类型。如果左侧写着 LEFT JOIN右侧是较受限的访问路径一般没问题如果出现了 INNER JOIN 或者 Hash Join 而不是 Hash Left Join说明这个“隐形下推”已经生效。没有这个转换意识的人看完执行计划只会觉得“优化器换了种连接方式”不理解背后的规则下次遇到类似场景还是不会排查。4. 让下推失效的典型写法一份来自踩坑现场的笔记4.1 函数包住列优化器不会做反向推导最经典的下推杀手就是函数包裹列。写条件的时候人脑子能算出反函数优化器一般不会做这种反向推导。SELECT * FROM orders WHERE DATE(order_time) 2024-03-01;这个写法对大表来说是灾难。order_time被DATE()函数包住了索引没法用分区裁剪也没法做因为优化器无法在不知道函数内部语义的情况下把一个等值条件反推回原列的范围。执行计划通常会显示这列走了全分区扫描Extra 列里出现Using where但不带任何索引加速。解决办法不外乎三种。第一改成范围条件写成order_time 2024-03-01 AND order_time 2024-03-02让原始列保持裸状态第二如果数据库支持生成列就建一个order_date生成列并建索引把函数的计算结果物化出来第三部分数据库支持函数索引直接对DATE(order_time)建索引。无论哪种方案核心原则都一样让条件里的列保持裸奔状态优化器才敢放胆往下推。4.2 非确定性表达式每次计算结果都不一样怎么推还有一种条件下推不下去不是因为它选择性差而是因为它根本不稳定。这类表达式叫非确定性表达式典型如NOW()、RAND()、UUID()。举个例子SELECT * FROM events WHERE event_time NOW() - INTERVAL 1 hour;NOW()在每条数据被读取时执行结果都不同。分区裁剪要求条件在计划阶段是常量这个条件显然不满足。更麻烦的是如果优化器真把这个条件压到每个分片的扫描节点每个节点执行NOW()的时机不同拿到的结果可能不一致逻辑上都说不通。所以非确定性表达式通常只允许在数据已经读过之后在更高层执行下推没戏。实际业务中这类条件其实很常见调优手段主要是提前把时间边界算好。在应用层先查出start_time和end_time再用参数化条件写进去替代嵌入 SQL 里的NOW()优化器就能拿到明明白白的常量分区裁剪和下推全部恢复。有时候看似是 SQL 性能问题本质上是代码生成 SQL 的方式有问题。4.3 子查询里出现聚合、DISTINCT 和 LIMIT半连接转化被直接打断IN子查询能不能转换成半连接并下推还要看子查询本身的结构。如果子查询里带了聚合、去重、排序加 limit优化器的转化空间会急剧缩小。SELECT * FROM orders o WHERE o.user_id IN ( SELECT user_id FROM user_trade_stat GROUP BY user_id HAVING COUNT(*) 10 );这个子查询必须先做分组聚合才能得到“交易次数大于 10 的用户集合”。优化器没法把它拆成简单的半连接逐行探测更不可能把HAVING COUNT(*) 10这种聚合条件下推到单表扫描。执行计划里多半会出现物化节点先把聚合结果算出来放到临时表再做连接探测。遇到这类查询我的排查顺序是先看子查询的物化代价。物化后的结果集如果很大连接就变成了大结果集连接大表性能很难看。一个常见优化思路是分步处理先单独跑聚合得到中间结果表再和主表连接或者直接把聚合结果合理落成临时表刷新统计信息之后让优化器重新评估。对这类查询硬等优化器聪明起来不如主动拆解毕竟分组聚合本身的语义就不支持传统意义的下推。4.4 ON 与 WHERE 的位置还真的不能乱放外连接场景下条件写在 ON 里还是 WHERE 里不只是 SQL 风格问题而是直接决定结果集和优化空间。-- 写法 A条件放 ON SELECT o.order_id, u.user_name FROM orders o LEFT JOIN users u ON o.user_id u.user_id AND u.region 华东; -- 写法 B条件放 WHERE SELECT o.order_id, u.user_name FROM orders o LEFT JOIN users u ON o.user_id u.user_id WHERE u.region 华东;两张表各两行数据orders 里有 order_id 1user_id 101、order_id 2user_id 102users 里有 user_id 101region 华东、user_id 103region 华北。执行结果差异非常明显写法order_id 1order_id 2A条件在 ON返回 users 匹配行保留订单行user_name 为 NULLB条件在 WHERE返回 users 匹配行整行被过滤掉写法 A 保留所有订单行写法 B 只留下有华东用户的订单。所以你不能指望优化器帮你“修正”这个语义差异。从性能角度说写法 B 里的u.region 华东是一个 null 排斥条件优化器可以把外连接转内连接再做进一步下推写法 A 的条件没法用于这种转换但它也没有过滤订单行的副作用。这个案例提醒我写 SQL 之前先想清楚业务到底要什么结果再看执行计划。优化器的一切推导都建立在语义等价的基础上你写的语义本身决定了优化器的活动空间。把 WHERE 写错位置就算执行计划长得很漂亮结果错了一样是线上事故。5. 验证与调优如何确认下推真的发生在你的库上5.1 学会读执行计划里的 “Filter” 位置下推没有生效看执行计划就够了关键是知道看哪里。以 PostgreSQL 为例推下去的条件会挂在表扫描节点下面Seq Scan on users u Filter: (region 华东::text)没推下去的条件会挂在连接节点那一层Hash Join Hash Cond: ... Filter: (u.region 华东::text)注意这个细节区别。Filter 挂在 Join 节点上意味着全部行都进了连接才过滤挂在 Scan 节点下则是在扫描时就完成了过滤。MySQL 里看的是Extra列出现Using index condition说明 ICP 生效出现Using where则要结合前面的访问路径判断条件是不是真的压到了存储引擎层。Spark 看的是自定义指标那一栏有没有动态分区裁剪相关节点Trino 直接看 Logical 计划和最终执行计划里有没有动态过滤标志。我自己的习惯是拿到执行计划先答三个问题。第一每条 Scan 的 rows 估算值是不是远小于表总行数如果 Scan 输出等于全表行数过滤大概率没有下推。第二Filter 出现在什么节点层级第三有没有分区节点或者 Exchange 节点在扫描层被裁剪掉三个问题答完下推有没有生效基本就有结论了。5.2 用 EXPLAIN ANALYZE 实测收益执行计划是预演实际执行结果才作数。调优时我会在改动前后各跑一次带实际执行统计的计划对比几个关键指标。EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT o.order_id, u.user_name FROM orders o INNER JOIN users u ON o.user_id u.user_id WHERE u.region 华东;重点看这三项指标改动前改动后含义Scan 节点 rows全表行数条件过滤后行数真正参与连接的行数Join 节点 actual time高低连接阶段耗时Buffers共享命中数明显减少内存和 IO 压力变化这里有个容易踩的坑只看总耗时不看行数变化。总耗时受缓存、并发影响很大而rows removed by filter这类指标才是下推效果的直接证据。有的执行计划明明显示 Filter 没压到扫描层但因为缓存热了二次执行总耗时也降下来了误判成“没问题”。所以我会把实际时间和扫描行数一起记录二者同时变化才能说明优化真正落到数据读取上。5.3 优化器不干时的手工兜底什么时候值得自己动手优化器也是吃统计信息长大的它不干的时候先检查三件事统计信息是否新鲜、SQL 条件里的列是否被函数和隐式转换改过、连接条件是否写成了破坏语义等价的形式。这三件事都正常再考虑手工改写。一种常用兜底是把半连接显式拆成普通 JOIN 加去重或者反过来把 JOIN 改写为 EXISTS。两种写法在优化器眼里可能完全不同执行计划也会产生分化实际测试之后选便宜的。另一种做法是把子查询提前物化成一个 CTE因为物化会强制生成一份带统计信息的中间结果优化器重新估算时往往能打开新的下推路径。WITH vip_users AS ( SELECT user_id FROM users WHERE user_level VIP ) SELECT o.order_id FROM orders o JOIN vip_users v ON o.user_id v.user_id;但我不建议一上来就追求“绕开优化器”。每一条 hint、每一次手工物化都是在给未来埋维护包袱。等 SQL 升级、数据量变化、统计信息更新之后当初的手工优化可能反而变成阻碍。正确的姿势是先确认统计信息和执行计划再动手改写每改动一次就跑一次实测对比把改动前后执行计划和耗时记录归档。我自己吃过不少亏后来养成习惯所有优化动作都留一份对比笔记半年后回看哪些仍然有效、哪些已经过时一目了然。最后再提一个容易被忽略的小技巧下推生效与否和数据库版本关系很大。同一张表、同一条 SQL在旧版本上不触发 ICP 或者半连接转换升级之后可能自动就好了。所以遇到看起来“死都不下推”的情况先别急着改业务 SQL确认一下版本和默认优化器开关有时候一次小版本升级比改一百行 SQL 都管用。
