1. 一个慢查询逼着我搞懂了优化器的“下潜”动作先说个我踩过的真实坑。上个月排查一个统计报表接口超时的问题SQL 长这样SELECT o.order_no, o.amount, p.pay_count, p.total_amount FROM orders o LEFT JOIN ( SELECT customer_id, COUNT(*) AS pay_count, SUM(amount) AS total_amount FROM payments WHERE pay_time 2024-01-01 GROUP BY customer_id ) p ON o.customer_id p.customer_id WHERE p.pay_count 3 AND o.order_time 2024-06-01;orders 表大概 1200 万行payments 表 6000 多万行。接口超时阈值设定 2 秒这条 SQL 跑了将近 11 秒前端直接 504。我当时第一反应是“子查询物化后和 orders 做哈希连接太慢了”于是尝试把WHERE p.pay_count 3这个条件想办法塞进子查询里让它在分组前就把不满足的用户过滤掉——结果手工改写以后的 SQL 跑了不到 0.5 秒。有意思的地方来了那个我手工做的“搬条件”动作其实就是查询优化器每天都在做的事情——谓词下推Predicate Pushdown。可它既然会做为什么没把我的原始 SQL 自动改写成最优形态带着这个疑问我翻执行计划、查优化器跟踪日志、对比不同版本数据库的行为才把“优化器把 JOIN 条件下潜到子查询深处”这件事的真正逻辑摸清楚。这篇文章就把整个排查过程、原理拆解、代价决策机制以及哪些场景下下推会帮倒忙完整写出来。适合所有被慢 JOIN 折磨过的后端研发和数据开发尤其是写复杂报表 SQL 时习惯性把子查询当临时表用的人。2. 优化器凭什么改我的 SQL谓词下推的两条改写路径数据库优化器拿到一条 SQL 之后做的第一件事并不是你想的那样“直接执行”而是把 SQL 转化成一棵逻辑执行树然后在上面做各种等价改写。在我们这个场景里真正起作用的是两个动作子查询上拉Subquery Flattening和谓词下推Predicate Pushdown。它俩经常配合工作配合的结果就是标题里说的那种“JOIN 条件下潜到子查询深处”。2.1 第一条路径把子查询先“拆平”再下推对于非相关子查询——也就是子查询不引用外层表的列——优化器有大概率把它从“独立物化单元”变成普通的派生表Derived Table这个过程叫 subquery flattening或者直白点叫子查询展平。展平之后原来的外层 JOIN 和子查询内部的 GROUP BY 操作就变成了一棵可以整体规划的树。在这个基础上WHERE 条件里针对子查询结果列的过滤条件比如p.pay_count 3会被优化器尝试往下压。如果这个条件能改写成针对源表列payments.customer_id的过滤就能在 GROUP BY 之前先把大量行排除掉。这个改写在优化器内部被称为“GROUP BY 之前的谓词下推”它是简化聚合子查询最有效的一招。但这里有个关键限制条件能不能下推取决于子查询内部的聚合粒度能不能匹配这个条件。假设子查询是按 customer_id 分组的外层 WHERE 写的是p.pay_count 3pay_count 是 COUNT(*) 的结果它是聚合值不是源表列这个条件本身没法直接下推到 payments 表上。那 MySQL 为什么还能跑到 0.5 秒因为它走了另一条路。2.2 第二条路径JOIN 自身条件下的“半连接”转化其实我的原始 SQL 里WHERE p.pay_count 3这个条件MySQL 5.7 的优化器有一个更激进的改写它会把LEFT JOIN 子查询 WHERE 子查询结果列条件这种形态识别出来转换成半连接Semi-join——前提是这里 LEFT JOIN 实际上没有真正使用“保留左表全部行”的语义。什么情况下 LEFT JOIN 会退化成半连接当 WHERE 条件里出现了对被驱动表payments 聚合结果的过滤且过滤条件是 3这样的非空判断时LEFT JOIN 中本该保留的 NULL 扩展行全部被过滤掉了。优化器识别到这一点后会把 LEFT JOIN 语义转换成 inner join semi-join 的混合形态进而对整个查询结构做大幅度的执行计划重排。重排之后“过滤掉支付次数小于等于 3 的用户”这个动作不再必然发生在聚合完成之后。优化器有一种叫做“聚合子查询的半连接优化”的手段先把满足条件的 customer_id 集合算出来再反过来限定 payments 的扫描范围或者在外层先做半连接探测把不必要的分组计算跳过去。执行计划里你可能看不到一个肉眼可见的“下推条件”但实际效果等同于条件被“下潜”到了子查询内部。2.3 优化器不是翻译机它是重写器很多人对优化器有个误解以为它只是把 SQL 语法树翻译成执行计划就完事了。实际不是。基于代价的优化器是一座“SQL 重写工厂”它手里攒着几十种等价改写规则并在每次执行前做一轮“改写后的代价估算 VS 不改写”的比较只保留估算更优的那条路径。这就引出一个灵魂问题**它的估算依据是什么如果估算不准确呢**这就进入我们这篇文章的核心——代价决策。优化器在下推一个 JOIN 条件之前内心戏码非常复杂它至少要在下面三点之间做权衡需要考虑的指标下推前外层过滤后再回表下推后子查询内部提前过滤子查询内扫描行数6000 万行全量参与聚合可能降到几百万行聚合结果集大小几百万人客户维度更小甚至十几万人哈希连接探测成本外层 1200 万 × 大哈希表外层 1200 万 × 极小哈希表索引命中可能性子查询内部按原条件扫下推后可能走覆盖索引这张表看起来全是对下推有利的但真实世界不总是这样——下一节我就来拆解优化器“不敢下推”或者“下推失败”的那些决策盲区。3. 代价决策的博弈点基数估计和连接方式的互相纠缠别被“优化器自动改 SQL”这几个字唬住——它不是神它是靠统计信息和一堆启发式规则做概率判断的钟表匠。它每次改写之前会做一次“心理博弈”博弈的核心筹码是基数估计Cardinality Estimation也就是“这一步操作后大概会剩下多少行”。估计错了下推决策就会跟着错。3.1 下推决策的真正计算逻辑三种计划的PK还是用刚才那条 SQL。优化器在决定是否把外层过滤条件压进子查询时实际上是拿三条执行路径的估算成本在 PK路径 A不展平、不重写直接物化子查询再做 JOIN成本 扫描 6000 万行payments 全表过滤 pay_time成本 分组聚合产生约 800 万行物化成本 外层 1200 万行挨个探测这个 800 万行哈希表总成本大得离谱估算下来接近“全表 × 全表”级路径 B展平后把p.pay_count 3转换成对子查询内部的某种再过滤这里真正的优化动作是先算出每个 customer_id 的支付次数再过滤但优化器会尝试把“先算全部”改成“先按特定条件缩小范围”如果 payments 表在 customer_id 上有索引且外层表 orders 可以提供一批热点 customer_id优化器会把“JOIN 条件中从外层带来的等值条件”传递给子查询内部的扫描上使 payments 的扫描从 6000 万变成“只扫外层传入的那批 customer_id”路径 C完全展开成等价 JOIN 形式把聚合子查询拆成两张表之间的聚合连接让 GROUP BY 在 JOIN 之后进行通常情况下成本高因为 JOIN 会把行数膨胀之后再聚合很亏优化器把这三条路都算出估算成本选最低者。路径 B 就是“JOIN 条件下潜”的典型形态。3.2 有“条件下潜能力”却不下潜常见卡点我在实际生产环境里发现几种情况优化器明显有能力下推、但最终没有下推或者下推效果很差第一外层表基数被严重低估。如果 orders 表在 order_time 上的过滤用了order_time 2024-06-01而统计信息里这段数据占全表的比例被低估成 0.1%实际占比 8%优化器就会把外层结果集当成特别小的驱动集合于是觉得“让子查询全局聚合一次也就那样没必要额外做下推改写”。一旦遇到这种判断执行计划就变成大聚合 大哈希连接性能直接崩盘。第二子查询内部的过滤选择性太差下推得不偿失。举个例子如果子查询内部本来已经通过 pay_time 过滤掉了 99% 的行剩余结果集本身只有 60 万行你再从外层把 customer_id 的等值条件下推进去大概率还需要走一次反弹索引查找。这反而增加了索引随机访问 I/O。优化器会算一笔账全量顺序扫 聚合可能比大量随机点查更便宜。第三聚合键和过滤键没有相关性。比如子查询按DATE_FORMAT(create_time, %Y-%m)分组外层想过滤一个和 create_time 无关的字段。这种时候条件没法下推到扫描层只能停留在聚合之后优化器会直接放弃改写空间。你可以用生活化的方式理解这件事下推就像“把房间里的垃圾提前分类再倒掉”但是分类动作本身是有成本的。如果你家垃圾本来就没多少分类器还要一台机器空转那不如直接一袋全扔楼下。优化器做的决策就是“站在楼下闻一闻估算垃圾量再决定要不要在家里装分类器”。3.3 统计信息新鲜度是“幕后黑手”前面已经强调了基数估计的重要性驱动基数估计的原材料就是统计信息Statistics。MySQL InnoDB 的索引统计信息是通过随机采样 few leaf pages 估算出来的特点是快但不一定准。尤其遇到下面这几种情况表数据大涨大跌之后没有及时 ANALYZE TABLE直方图缺失多个条件同时过滤优化器按独立分布假设去乘选择性实际列间强相关导致估算严重漂移子查询内部的 GROUP BY 列有大量重复值但统计信息里的 distinct 值个数明显偏小这三点任何一点踩中代价决策就变成盲人摸象。这也是为什么我遇到慢 SQL 的第一反应不是“改表结构”而是“先看统计信息和执行计划里的预估行数有没有明显失真的地方”。4. 从执行计划看“下潜”的真实面貌EXPLAIN 都告诉你什么这一节我们来实操。光讲理论没用我把前面那条慢 SQL 放到测试库用 EXPLAIN 和 OPTIMIZER_TRACE 把优化器的内部决策过程拉出来看。有些东西写出来比你看十篇优化器原理文章都顶用。4.1 观察下推效果EXPLAIN 对比先看优化器自己选的执行计划简化版- Nested loop left join (cost1734.47 rows3421) - Table scan on o (cost217.24 rows11230) - Filter: (p.pay_count 3) (cost0.48 rows0.30) - Hash join (cost14.20 rows29) - Table scan on tmp_table (cost5.35 rows29) - Hash - Table scan on payments_grouped这个计划里你能明显看到两个阶段子查询被物化成临时表 tmp_table然后在哈希连接之后才做p.pay_count 3的过滤。也就是说优化器在这个统计信息状态下选择了“路径 A”——并没有把条件下潜进子查询。为什么因为它估算 payments_grouped 只有 29 行物化和哈希连接成本很低没必要做额外的改写。但实际线上这个分组结果有 248 万行这 29 和 248 万的差距就是统计信息失真带来的决策偏差。再看我手工改写的 SQL 的执行计划把p.pay_count 3换成 在子查询里直接HAVING COUNT(*) 3- Nested loop left join (cost284.11 rows312) - Table scan on o (cost217.24 rows11230) - Index lookup on p_group using customer_id (cost0.78 rows1) - Filter: (pay_count 3)改动之后子查询内部的聚合结果明显更小外层连接变成了每行做一次快速索引探测不再需要构造巨大的哈希表。两种计划的成本差距肉眼可见。这里最关键的一点是HAVING 条件下推的难度远低于 WHERE 条件因为它直接锁定了子查询输出行的上限。4.2 打开 OPTIMIZER_TRACE 看优化器的“翻牌过程”如果你只想看结论EXPLAIN 够了。但如果你想了解“它为什么没下推”就得抓优化器内部决策过程。MySQL 提供了一条命令SET optimizer_trace enabledon; SET optimizer_trace_max_mem_size 1000000; SELECT ... -- 目标慢SQL SELECT * FROM information_schema.OPTIMIZER_TRACE;这段跟踪日志里值得注意的字段有两个considered_execution_plans和refine_plan里面的成本估算。日志会明确写出“是否考虑使用 semi-join”“是否尝试 flattening subquery”这些关键判断开关。我扒到的那份日志里优化器确实写了“subquery_to_derived, flattening not applicable”或者“too many different values”之类的结论——而这些结论背后的真正原因是payments 表在 customer_id 上的统计信息 distinct 值数量被采样低估优化器认为“把外层过滤条件传进去会导致大量回表随机访问不划算”。这个发现让我彻底明白了一个道理你写的每条 SQL 的最终执行路径都是统计信息、索引分布、存储引擎估算函数三方博弈的产物。你不可能完全预测优化器每一步但你可以通过 ANALYZE TABLE 和调整 join buffer 配置去影响它的估算。4.3 实战核查清单我每次排查可疑下推效果的固定动作与其盲猜不如做一套固定检查。我现在处理这类问题的标准步骤是这样先EXPLAIN ANALYZEMySQL 8.0.18看真实执行时间分布哪个节点耗时最离谱再开OPTIMIZER_TRACE重点看子查询有没有被 flatten、有没有 cross 掉下推改写查mysql.innodb_table_stats里对应的 n_rows 和 clustered_index_size判断统计信息新鲜度对比改写 SQL 和原 SQL 的 EXPLAIN 成本值差距大于 20% 就动手改若统计信息没过期但优化器还是不下推考虑手工重写 SQL 或者调整 sql_mode 影响改写规则这套流程帮我解决了不少超时问题比在配置文件里盲调 join_buffer_size 之类的参数管用得多。5. 两种“下潜”不了的硬场景跨库 JOIN 和 UPDATE 子查询聊完正常的“下潜”机制得说说两个让优化器直接认怂的经典场景。这两个场景是从最近的热搜词里看到大家持续在问的我干脆一次性讲透。5.1 跨库 JOIN统计信息到达不了的对岸跨数据库实例的 JOIN是指你要关联的表分别在不同的 schema、不同实例、甚至不同物理机上。这种查询一旦出现子查询优化器的下推能力基本归零。原因非常直接优化器只能在自己掌控的统计信息范围内做代价决策它无法获得远端表的数据分布信息更不可能把本地的 JOIN 条件发到远端子查询里完成等价改写。举个例子SELECT a.id, b.score FROM local_db.users a JOIN ( SELECT user_id, MAX(score) AS score FROM remote_db.rankings WHERE event_id 1001 GROUP BY user_id ) b ON a.id b.user_id WHERE a.status 1;这种 SQL 在 MySQL 里通常会走FEDERATED存储引擎或者业务层手动获取性能如何全看远端子查询的物化速度和网络传输。JOIN 条件a.id b.user_id无法传导到远端执行因为本地优化器对远端表的索引和统计完全不可见。实际操作中我见过不少人把这种查询误认为只是慢花了大量时间调本地参数结果一点用没有。跨库 JOIN 的正解是分步处理先查本地表拿到 id 集合再通过 IN 或者临时表传给远端让远端利用索引收敛结果集最后在业务代码里做匹配。过程不优雅但有可预期的性能。尤其是子查询内部结果集很大的时候远端的物化结果先压缩再传输比单条大 JOIN 快一个数量级。5.2 更新语句里的子查询语义限制让下推寸步难行另一个高频踩坑场景是UPDATE ... WHERE (子查询)。比如UPDATE payments p SET p.refund_flag 1 WHERE p.customer_id IN ( SELECT customer_id FROM orders o WHERE o.order_time 2024-06-01 AND o.amount 1000 );MySQL 对 UPDATE/DELETE 语句中子查询的支持有明确限制不能对目标表进行二次读取后直接在同一语句内修改且优化器对这种“目标表参与子查询”的语句默认禁用部分改写规则。MySQL 官方文档里写得很清楚你甚至不能在一个 UPDATE 里直接查询并更新同一张表除非套一层派生表。更麻烦的是即使子查询来自另一张表更新子查询因为要对目标表加锁、逐行更新优化器没法安全地下推过滤条件到子查询内部——因为下推改变的是读取路径而更新语句的读取路径和锁定策略紧密耦合。一旦尝试下推行锁的范围和加锁顺序就不可控可能引发死锁或间隙锁被意外扩大的风险。这类场景我的建议是一条条来先 SELECT 取出需要更新的主键集合小批量 UPDATE分批提交。永远不要试图在一个 UPDATE 里让优化器帮你完成所有灵巧改写。数据库对写入操作保守是故意的不是 bug是保护机制。5.3 跨库和更新场景的共同本质优化器不敢跨过的语义边界把两个场景放在一起看你会发现它们的共同点优化器的改写能力受到语义边界的约束。跨库跨越了统计信息的边界更新跨越了事务安全语义的边界。在这两种边界上优化器宁可保留保守策略也不激进下推因为改写失误的代价是数据错误或锁等待风暴远超一次慢查询的代价。理解这个边界意识对写 SQL 的人非常重要。你写的每条 SQL 都在跟优化器做博弈你要学会辨认哪些地方它能帮你、哪些地方它必须说“不”。能帮的地方你要给它足够的统计信息支撑不能说“不”的地方你要自己动手把逻辑拆开。6. 下推帮倒忙的几种反模式我踩过的三个真坑前面基本都在讲下推的好处但有一说一优化器“下潜”动作做过头或者做错位置同样能制造灾难。这里分享三个我实际遇到的反模式也算给大家打个预防针。6.1 下推导致外层驱动表判断错误某个报表场景里我原先期待的表连接顺序是“小表驱动大表”结果优化器通过下推把一个外层过滤条件压进了子查询后估算出子查询结果集变得极小于是翻转了嵌套循环的顺序把原本应该驱动外层的大表变成了驱动表反而产生了每行探测一次小表的“大驱动 × 小探测”模式。如果大表上的过滤条件没有索引这就变成灾难级别的全表反复扫描。这种坑很难从 SQL 文本里看出来只有在 EXPLAIN 里盯着“驱动表是谁”和“每行代价”才能发现问题。我的经验是一旦你的连接顺序和你手工设计的相悖而性能又明显劣化优先怀疑“下推改写改变了基数估算”不要一味认为是索引缺失。6.2 下推条件里有函数或隐式转换索引直接失效下推下得好不好还要看压下去的条件长什么样。假如外层过滤是DATE_FORMAT(o.order_time, %Y-%m-%d) 2024-06-01优化器把这个表达式整体下推到子查询内部那子查询内部的索引查找就全废了因为索引上根本没有“函数处理后的值”。更隐蔽的是隐式类型转换比如字符串列和数字列比较会让索引上的等值条件无法走 ref 访问。这种情况下优化器表面上下推了实际效果却是“把原来的高效扫描改成了低效全表扫描”。这提醒我们一个通用原则要下推就要下推裸列上可索引的条件。任何包装过的表达式在下推之后都可能制造伪下推假象。6.3 下推放大了临时表和文件排序的开销最后一个反模式发生在分组子查询里。假设子查询内部本来就有 GROUP BY 多个字段外层下推一个条件进来确实减少了行数但如果这个条件导致子查询内部的执行计划从一个覆盖索引扫描变成了回表临时表排序那下推省掉的计算量可能还比不上临时表落盘消耗的资源。我实际测过一个案例下推之后子查询的行扫描数从 6000 万降到 800 万看似赢了但执行计划里冒出Using temporary; Using filesort临时表大小远超内存落盘导致耗时反而翻倍。这种情况要做的反而不是继续下推而是调整索引让 GROUP BY 走索引有序性彻底避免临时表。6.4 如何看待这些反模式优化器的默认值不等于正确值把这三个坑放一起总结一下优化器的下推开关是启发式的它在绝大多数 OLTP 场景下都表现良好但在复杂的统计报表、多级嵌套、批量更新这类场景里启发式判断往往会失效。这时候你要有足够的能力去识别“优化器默认行为是否适合当前数据分布”然后在 SQL 层手动引导。实际工作中我见过很多人对抗优化器的方式是滥用 STRAIGHT_JOIN或者到处加 FORCE INDEX。这东西短期有效长期会埋雷——数据量一变化原来的强制路径可能就是最差路径。我更推荐的方式是重写 SQL 结构、维护统计信息、让索引设计和查询模式对齐。用温和的方式引导优化器做正确决策而不是武力打断它。7. 事后反思关于条件下潜我最想留在最后的几条实操心法这条 SQL 的排查前前后后折腾了快两天把执行计划拆到表格里把 OPTIMIZER_TRACE 日志逐行翻过数据集改了又改、索引加了又删。最后归纳下来真正让我觉得值钱的不是某个具体的改写技巧而是下面这几条思维习惯。第一先诊断优化器“看到了什么”再谈它“做了什么”。慢 SQL 的病因往往不在 SQL 本身而在优化器看到的统计信息、索引信息和内存设置。你不开 OPTIMIZER_TRACE 永远不知道它内心在纠结什么。很多看似“优化器不聪明”的问题根源其实是统计信息过于陈旧。第二子查询不是临时表它是优化器手里的积木。写完一条 JOIN 子查询的 SQL不要觉得执行计划是固定形态。同一段 SQL在数据分布变化后可能自动走出完全不同的计划。你要做的是保证“无论它怎么规划都有一两条优质路径可走”——核心手段就是关键连接列和过滤列上必须有合适的二级索引。第三手工改写时坚持“一次只改一个语义点”。我见过有人手工下推条件时顺手把 LEFT JOIN 改成了 INNER JOIN、把两个等值条件合并、把 GROUP BY 字段换掉结果跑快了却不知道为什么跑快的。优化这件事最怕的就是“组合拳式修改”因为你无法确认是哪个改动生效将来换个数据集也无法复现。可靠的实践是基线执行计划先保存好一次只改一个条件用 EXPLAIN 的 cost 变化确认每一步的收益。第四“下潜”没有银弹取舍永远存在。优化器把条件压到子查询深处本质上是用更复杂的改写换取更小的中间结果集。这种交换并非在所有场景都划算尤其当你遇见外部连接语义、嵌套聚合、窗口函数叠加的时候优化器会选择保守策略。你越是理解它的取舍逻辑越不容易写出那种“表面优雅、实际慢性自杀”的复杂 SQL。回头看整个事件那条 11 秒变成 0.5 秒的 SQL 其实只是冰山一角。真正有价值的是把“JOIN 条件下潜”这件事的内功练好以后再遇到五花八门的慢 SQL都能第一时间判断这一步到底是索引问题、统计信息问题还是优化器不敢下推、下推得不彻底、或者下推了反而帮倒忙。希望这篇文章也能帮你少走几步弯路看执行计划的时候多一分笃定。