★ FEATURED ARTICLE 数据库线上慢查询的工单摆在我面前的时候SQL 本身看起来并不复杂一张 5 亿行的订单明细表一张百万行的商户表等值连接后带两个过滤条件。按说这种查询优化器闭着眼睛也能选对执行计划可它偏偏让 JOIN 阶段跑出了接近半分钟。把执行计划翻出来看问题就出在条件下推这个环节——过滤条件没有压到连接内部JOIN 两侧的输入集被喂得又肥又大。慢 SQL 治理做得久了你会发现连接条件下推Join Predicate Pushdown不是一条可以拍脑袋决定的优化规则它背后是一整套代价评估逻辑。什么时候该下推、推到哪个基表、下推后会不会引出新问题每一步都要靠代价模型给出答案。这篇文章我会从一次真实排查讲起把基于代价的连接条件下推技术从原理、实现路径到落地踩坑完整过一遍适合数据库内核开发、负责慢查询治理的 DBA以及想知道执行计划为什么长成这个样子的后端同学参考。1. 现场还原一条慢 SQL 把条件下推逼到了台前1.1 现象连接键过滤性很强执行计划却不领情先看当时那条被投诉的 SQL我做了脱敏处理SELECT od.order_id, od.amount, m.merchant_name FROM order_detail od JOIN merchant m ON od.merchant_id m.merchant_id WHERE od.order_time 2025-01-01 AND od.order_time 2025-02-01 AND m.merchant_level high;从语义上看这张查询想要的是1 月份下单记录里那些高等级商户的订单信息。order_detail 按天分区理论上order_time条件可以裁剪到 31 个分区merchant_level high在选择率上通常也只有 1%~2%。两条过滤条件都具备很强的裁剪能力。但执行计划告诉我一件反直觉的事优化器先完成了 order_detail 和 merchant 的全量等值连接生成了巨大中间结果之后才应用merchant_level high这个过滤条件。换句话说过滤条件在下推决策上出现了重大失误JOIN 的输入集没有提前瘦身资源全砸在了无效连接上。慢 SQL 治理里这类问题非常典型。很多 DBA 第一反应是加索引但当时连接键上已经有索引真正的问题是优化器对下推路径的代价判断失误。查询本身不复杂复杂的是让优化器在所有下推组合中找到代价最优的那个分支。1.2 本质连接条件下推的三个层次排查过程中我把下推这个概念重新梳理了一遍。业界谈论谓词下推时往往把几个层次混在一起实际上它们对执行计划的影响完全不同基础谓词下推把基表上的过滤条件下推到表扫描阶段。比如把od.order_time范围条件下推到 order_detail 的扫描层扫描时直接跳过无关分区。这类下推在绝大多数优化器里已经是标配通常由规则驱动。连接传递推导下推利用等值连接的可传递性把一个表上的谓词推导到另一个表上。比如从od.merchant_id m.merchant_id和m.merchant_level high推导出新的可下推谓词。这类下推需要额外判断推导成本是代价模型真正发挥作用的地方。存储层条件下推把连接条件本身下推到分区裁剪、索引扫描或列存 Zone Map 过滤。典型场景是在分布式数据库里连接条件可以直接裁剪远端分片减少跨节点数据传输量。当时那条 SQL 的问题出在第二层。优化器没有利用等值连接做谓词传递推导也没有把merchant_level下推到 merchant 表扫描阶段而是选择了一种代价极高的执行路径。要理解为什么优化器会做出这种错误选择就要先看懂代价模型是怎么给下推算账的。2. 代价账本怎么算先搞懂优化器在为什么买单2.1 四个代价维度与它们的权重代价模型本质上是对执行计划物理开销的量化打分。不同数据库的计分公式千差万别但核心维度基本一致代价维度含义典型影响因素优化目标IO 代价磁盘/内存扫描数据页的开销扫描行数、分区数、页大小减少无效页读取CPU 代价元组处理、表达式计算的开销过滤表达式成本、JOIN 比较次数降低每行处理成本内存代价哈希表、排序缓冲等运行时占用参与 JOIN 的输入基数控制哈希表/排序区大小网络代价数据在节点间的传输开销Shuffle 数据量、Broadcast 次数缩小跨节点传输规模在单机数据库里IO 和 CPU 是主导因素在分布式/MPP 数据库里网络代价往往成为决定性变量。基于代价的连接条件下推需要把下推前后的这四个维度全部重算一遍而不是只看是否少扫描了几行。我们回到案例。order_detail 全表 5 亿行merchant 表 100 万行。如果不做下推JOIN 输入是「5 亿 100 万」哈希连接选择小表 merchant 建哈希表大表 od 做探测表面上看也能跑。但中间结果的膨胀点不在这里——merchant_level high如果在 JOIN 之后才过滤那么哈希探测阶段会对 5 亿行全部做一次无意义的连接匹配之后才在结果集上丢掉 98% 的行。这笔账IO 和 CPU 双重亏损。下推后的面貌完全不同merchant 表先过滤到 1 万行order_detail 按时间分区裁剪后剩 2000 万行JOIN 输入直接从「5 亿 100 万」变成「2000 万 1 万」。代价差异通常在 20 倍以上这个量级用直觉都能判断谁优谁劣。2.2 代价对比的简化公式与决策逻辑为了把问题说透我给代价计算做个简化建模。假设某次 JOIN 的总体代价可以近似表达为总代价 扫描代价(左) 扫描代价(右) JOIIN阶段代价 后续处理代价 JOIN阶段代价 ≈ α × 输入行数左 β × 输入行数右 × 哈希探测系数 内存占用折算α 和 β 是数据库根据硬件环境预设的权重常数。关键是下推决策是通过对比不同方案的 JOIN 输入基数进而对比总代价后做出的。用 Python 模拟当时的代价计算逻辑可以长这样def estimate_join_cost(probe_rows, build_rows): # 简化模型哈希连接的成本主要取决于探测行数 scan_cost (probe_rows build_rows) * 0.01 hash_cost probe_rows * 0.05 memory_cost build_rows * 0.002 return scan_cost hash_cost memory_cost # 方案A不下推merchant全表参加JOIN plan_a estimate_join_cost(probe_rows500_000_000, build_rows1_000_000) # 方案Bmerchant_level下推到merchant扫描层假设选择率1% plan_b estimate_join_cost(probe_rows500_000_000, build_rows10_000) # 方案C再叠加order_time分区裁剪selectivity约4% plan_c estimate_join_cost(probe_rows20_000_000, build_rows10_000) print(f方案A: {plan_a:.0f} 方案B: {plan_b:.0f} 方案C: {plan_c:.0f})输出结果会直观地显示方案 C 的代价最低。但要注意这套逻辑成立的前提是基数估计准确。一旦统计信息给出的选择率偏离实际整个代价对比就会失真最终选出和执行计划就会偏离最优路径。这也就解释了为什么简单的规则下推能推就推会失效。某些场景下推后反而更差比如过滤条件极其宽松、下推后不仅裁不掉多少行还因为函数包裹列导致索引失效——这种事情我在后面踩坑部分详细讲。代价模型的意义就是给每个下推候选方案一条独立的算账路径让优化器在真实代价面前做取舍。3. 落地实现从规则校验到代价驱动的完整链路3.1 可下推性校验安全边界比性能更重要实现基于代价的连接条件下推第一步不是算代价而是做可下推性校验。这一层的目的是把语义错误的风险提前拦掉。优化器再怎么追求性能也不能让执行计划改变 SQL 的语义。校验清单主要有这么几条非确定性表达式禁止下推。rand()、now()这类函数每次调用结果不同如果下推到表扫描阶段下推前后的结果存在不可复现的差异这种谓词必须在上层固定位置计算。外连接场景要格外小心。对于 LEFT JOIN 或 RIGHT JOIN内表上的过滤条件下推到扫描层时一定要注意 NULL 扩展语义。WHERE inner.col 1和ON inner.col 1对结果行数的影响完全不同前者会把不匹配的右表行过滤掉后者保留 NULL 扩展行。搞错这个结果集就错了。子查询和视图边界的穿透性判断。外层条件能否穿越子查询/视图下推到内部表需要先判断条件涉及的列是否经过变换。列被聚合函数包裹时不能直接下推列在关联子查询中被引用时下推可能改变子查询的关联语义。函数包裹列的场景。WHERE CAST(order_time AS DATE) 2025-01-01这类谓词如果强制下推可能导致索引扫描退化成全表扫描代价模型需要结合物理访问路径综合判断。我的经验是可下推性校验宁可保守不要激进。激进的下推规则可能在 TP 场景下带来巨大收益但一旦遇到语义边界就会引发数据正确性事故。正确性风险的优先级永远高于性能收益。3.2 候选计划枚举下推位置和下推顺序的排列组合校验通过后进入候选计划枚举阶段。这一步决定优化器在多大的解空间里搜索下推方案。对于一个包含多表 JOIN 和多个候选谓词的查询枚举的核心问题有两个下推到哪个基表按什么顺序下推举个例子。假设查询涉及三个表 A、B、CA 和 B 在 a_id 上等值连接B 和 C 在 b_id 上等值连接外层 WHERE 条件同时作用在 A 和 C 上。那么候选方案可能有方案一WHERE 条件分别下推到 A 和 C 的扫描层JOIN 顺序保持不变。方案二先利用传递推导从 A 的条件推导出 B 上的谓词再把谓词下推到 B接着调整 JOIN 顺序使 B 在连接中处于更早的位置。方案三部分条件下推到存储层做分区裁剪部分条件留在 JOIN 上层做过滤。枚举空间呈指数级增长因此工程实现上需要引入剪枝策略。常见做法是分步搜索先做局部下推优化每个基表独立判断再做全局下推优化考虑推导谓词和 JOIN 顺序联合调整。局部下推的代价计算较快能快速锁定明显有利的方案全局下推只对局部方案中收益不明确的场景展开控制优化器开销。3.3 代价估算与择优如何给候选计划打分候选计划生成之后进入代价估算环节。这里需要对每一个下推方案重新计算输入基数再套用代价公式打分。基数估算依赖统计信息统计信息的核心是直方图。直方图记录列值的分布大致情况帮助优化器判断某个谓词的选择率。比如merchant_level high如果直方图显示 high 值出现频率是 1%那么过滤后基数为 1 万左右如果统计信息过期原本 1% 的选择率被算成 50%下推决策就会被彻底带偏。在工程上我倾向用两级估算粗略估算使用列级统计信息NDV、空值率、直方图桶数据快速算出谓词选择率过滤明显不合算的下推方案。精确估算对入围方案做表达式代价计算包括索引访问代价、JOIN 算法适配代价、内存开销加权求和后排序。这个过程往往需要迭代。下推方案改变 JOIN 输入后原本最优的连接算法可能发生变化。比如基数降到某个阈值后哈希连接可能让位于嵌套循环连接代价公式里的权重系数会随之变化。这就是为什么基于代价的下推不能只算一次需要在计划搜索循环里反复评估。4. 真实翻车统计信息失真与下推反噬4.1 统计信息过期导致代价误判的完整链路原理说完了讲几个我实际踩过的坑。最典型的就是统计信息过期。我遇到过一张商户表merchant_level列原本有 7 个等级业务在某个版本里把等级收敛到 3 个大量原来silver级商户被批量改成highhigh的选择率直接从 1% 飙升到 60%。但优化器的统计信息没有及时刷新依然认为high是低频值。这条链路走下来非常隐蔽下推枚举阶段优化器认为merchant_level high能把 100 万行裁到 1 万行于是把条件下推到了 merchant 扫描层并选择 merchant 作为驱动表。实际运行时过滤后剩 60 万行驱动表膨胀了 60 倍。哈希连接被迫对 60 万行建哈希表同时大表探测阶段做了大量无效匹配整体执行时间反而比不下推时更差。排查这类问题的关键是对比下推决策前后的基数预估值和实际值。数据库基本都提供了执行计划统计信息接口看 plan 里的 rows 和 actual rows 差异如果偏差超过一个数量级统计信息基本就是元凶。解决方案不复杂修正统计信息刷新策略对高频变更列设置自动更新阈值同时对下推决策本身增加「选择率置信度」判断——预估值太模糊时不要强行做激进下推。4.2 函数包裹列下推导致索引失效第二个坑出现在函数包裹列场景这也是代价模型最容易失真的地方。某条 SQL 业务侧为了忽略时间部分写了这样的条件SELECT ... FROM order_detail od JOIN merchant m ON od.merchant_id m.merchant_id WHERE DATE(od.order_time) 2025-06-01DATE 函数包裹 order_time 列后order_time 上的索引大概率失效。优化器如果机械地把这个条件下推到 order_detail 扫描层扫描层面对的就是全分区扫描代价极高。而如果保持条件不下推先做 JOINJOIN 本身有 merchant_id 索引支撑可能整体代价反而更低。这类场景的本质是下推后过滤行数虽然提前了但物理访问路径恶化IO 代价显著上升。代价模型需要把「谓词是否 sargable可搜索」纳入考量。sargable 的谓词可以安全下推并配合索引访问非 sargable 的谓词即使下推也只能做全表过滤收益可能被物理扫描开销吞噬。处理手段有两类。一是优化器层面做表达式改写把DATE(od.order_time) 2025-06-01改写成od.order_time 2025-06-01 00:00:00 AND od.order_time 2025-06-02 00:00:00恢复 sargable 属性二是如果改写不可行就要求代价模型在估算时将索引失效的惩罚项加进去逼着优化器重新考虑下推的性价比。两种思路配合使用效果最好。4.3 参数化查询场景的下推僵局第三个坑发生在预处理语句和参数化查询上这类问题在 ORM 框架广泛使用的今天特别常见。PREPARE query_plan FROM SELECT od.order_id, m.merchant_name FROM order_detail od JOIN merchant m ON od.merchant_id m.merchant_id WHERE od.order_time ? AND od.order_time ? AND m.merchant_level ?;优化器在解析预处理语句时面临一个选择是等参数绑定后再做代价决策还是在解析阶段就生成一个通用的执行计划前者能做到每次执行都贴合实际参数值但优化开销高后者执行计划稳定但参数值变化可能让下推决策严重失配。不同数据库对这个问题的处理方式不同大致有几种思路参数嗅探首次执行时根据实际参数值生成计划后续参数如果变化过大计划可能不是最优。业界通常配合计划缓存失效机制缓解。自适应执行计划执行过程中动态感知实际基数如果发现和预期偏差太大在算子执行前调整策略比如运行时切换连接算法。基数推断对条件用直方图高频值做推断对范围条件用默认选择率兜底尽可能让通用计划不至于太差。我在实际项目中踩到的坑是绑定变量条件下优化器无法预知merchant_level的具体值只能按平均选择率估算导致下推决策偏向「中间值」。当实际绑定值恰是高频值时下推收益被浪费。解决方案是应用层对高频值单独走非参数化 SQL或者依赖数据库的自动计划矫正机制让高频参数值触发计划重生成。5. 验证与收尾经验上线前必须做的回归比对5.1 回归比对方法别只看两个计划要看一整条性能曲线基于代价的下推特性进入生产环境前我坚持做一套回归比对流程。目的很简单确认下推逻辑在真实数据分布上确实带来收益而不是在测试集上碰巧表现良好。具体方法是收集线上的 TOP 慢查询样本最好涵盖不同连接数、不同过滤条件下推组合的典型查询。对每个查询分别在开启和关闭下推特性的两种模式下执行记录执行时间和执行计划形状。比对时要特别注意几个点不能只跑一次。数据库的缓存、并发状态会影响单次执行时间至少要跑三轮取中位数。观察执行计划形状的变化。不是所有计划变化都意味着性能变化要结合基数估算对比。关注极端场景。把参数值取到边界比如选择率最高和最低的情况观察下推决策是否依然稳健。我用一张表来整理某次回归的观察记录查询编号下推前耗时(ms)下推后耗时(ms)执行计划变化结论Q01284311822过滤条件压到扫描层驱动表变更显著优化Q025821043下推后索引失效分区扫描回退Q031256712503计划形状基本不变持平Q0490875721推导谓词下推成功优化Q04 这类场景最值得留意——连接传递推导下推的收益往往比简单谓词下推更大因为它能同时裁剪多张表的输入数据把原本只能在 JOIN 之后才能做的过滤提前到扫描层。这也是我调整优化器的重点观察方向。5.2 调试手段和两个实用技巧说到调试手段多数主流数据库都支持输出优化器决策过程的 trace 信息。我在排查下推问题时优先做一件事把 optimizer trace 打开定位优化器在哪个候选方案上做了错误取舍。有一次我发现下推决策选择了一个次优方案trace 里显示原因是对某个直方图的桶边界理解有误。优化器认为某个高频值落到桶 3实际落在了桶 2导致选择率估算偏差。这种问题光看执行计划是发现不了的一定要看 trace 里基数估算的中间结果。最后分享两个实用技巧。第一个是统计信息健康度巡检。做下推优化前先检查参与 JOIN 的所有列是否有过期统计信息。可以按查询频次和写入频率排序把容易出现统计信息滞后的列找出来单独设置刷新策略。第二个是动态关闭局部下推的逃生通道。上线新下推逻辑时保留一个配置开关例如只关闭「推导谓词下推」这一条规则路径这样如果生产环境出现回归可以在分钟内恢复原状而不需要重启整个优化器特性。实际运维中我觉得对优化器始终保持「怀疑 验证」的态度最重要。它给出的计划永远是基于代价模型的最优猜测但模型本身有近似、有误差最终决策要靠线上数据不断校准。连接条件下推这项技术打磨到位对复杂 SQL 的提速效果是立竿见影的实测下来 30 秒到 2 秒的查询大多就是这一层优化带来的。
阅读完成 · 觉得有帮助？
