半个月前我接到一条慢查询工单一个订单聚合报表SQL平均耗时1200ms高峰期甚至到1.5秒。业务方已经想把报表接口改成异步任务我拦住他们说先别急。翻出执行计划一看问题根本不是缺索引也不是业务逻辑复杂而是连接条件下推没有生效过滤条件在JOIN之后才开始作用导致百万级中间结果白白生成又白白丢弃。我把查询改了一版把过滤条件从WHERE移到JOIN的ON子句里同一个查询瞬间降到0.4ms。这一条SQL从千毫秒到亚毫秒没有加机器没有改业务逻辑靠的就是让优化器把连接条件推得更彻底。如果你也在被复杂SQL折磨动不动几百毫秒上千毫秒执行计划看不懂加索引也没效果这篇文章就是写给你看的。我会从执行计划的信号讲起聊清楚什么是连接条件下推再给你一个可以照抄的实战案例最后把那些“为什么我的SQL没有被下推”的坑一起填掉。1. 复杂SQL性能瓶颈别被千毫秒骗了1.1 先从执行计划看起慢在哪一步一条SQL在数据库里并不是直接面向表执行的。它要经过语法解析、逻辑优化、物理优化最后生成一棵执行计划树然后由执行引擎按照这棵树去读表、连接、分组、排序。执行计划就是优化器给你的一张“施工图”每一张表怎么读、按什么顺序连接、过滤条件在哪个节点被应用全部写在这张图里。大多数慢查询问题都出在这张图上而不是SQL本身。我见过太多人遇到慢SQL就条件反射地加索引但索引只是其中一个变量。更常见的坑是过滤条件没有被下推到表的扫描阶段而是在JOIN完成之后才被应用。这好比炒菜的时候不是先把菜洗干净再切而是把所有食材混在一起炒完之后再从锅里往外挑坏叶子。优化器如果选择了这种计划哪怕索引再多也救不了。我在定位复杂SQL问题时第一件事就是看执行计划里的三个信号扫描行数、连接顺序、过滤位置。尤其要留意Extra里有没有出现类似Using where; Using join buffer的标记或者某个节点的rows大于最终返回行数几个数量级。一旦出现这种信号十有八九是下推失效。比如下面这种简化的执行计划形态- Nested loop inner join (cost253000 rows3628000) - Table scan on orders (cost120000 rows3627800) - Filter: (users.level VIP) (cost1.1 rows356) - Single-row index lookup on users using PRIMARY KEY (idorders.user_id) - Filter: (orders.status 1) AND (orders.created_at ...)这里的Filter: users.level VIP出现在连接操作的下一层意味着数据库先把订单表每一行拿去找用户再判断用户是不是VIP最后才判断订单状态。中间产出了几百万行临时结果绝大多数行都被丢弃了。连接条件下推如果生效这个Filter应该在读取users表的那一刻就发生甚至可以直接走索引把VIP用户先筛成一张小表。1.2 连接顺序与数据量为什么连接是重灾区数据库里最消耗资源的操作JOIN通常排在第一位。原因很简单连接会把多个表的数据按某种方式组合组合过程中数据量会被放大。100万行的订单表JOIN 10万行的用户表即使连接键有索引需要处理的数据量也可能达到百万级甚至更高。如果Join之前不做过滤这些数据就会全部涌进连接运算。以嵌套循环连接为例成本大致可以理解成“外层表行数 x 内层表探测成本”。外层表100万行内层表哪怕每次探测只要0.1毫秒总耗时也要100秒。而哈希连接虽然不回表探测但需要先构建哈希表构建表的行数直接决定内存消耗和构建耗时。如果是多表连接中间结果还会继续向后传递形成放大效应。所以我会在调优时反复强调一个理念SQL性能天花板不在于连接算法有多高级而在于数据是什么时候第一次被减下来的。连接条件下推的核心就是让过滤尽可能发生在“数据进入连接之前”而不是“连接完成之后”。这也是为什么同样一条SQL改写前后能从千毫秒到亚毫秒。2. 连接条件下推的核心思路与适用场景2.1 下推到底在推什么很多人把连接条件下推和谓词下推混为一谈其实两者有区别但又有很强的关联。谓词下推是指把一个过滤条件比如status1、created_at 2024-01-01从上层节点推到下面的表扫描节点让数据在读取阶段就被筛掉。连接条件下推则更聚焦于JOIN过程把连接条件里针对某一张表的过滤部分推给那张表的扫描或者索引查找。举个例子。假设有下面这段SQLSELECT * FROM orders o JOIN users u ON o.user_id u.id WHERE u.level VIP;优化器可以把u.levelVIP这个条件下推到users表扫描阶段。从语义上说它和下面这种写法在内连接场景下是等价的SELECT * FROM orders o JOIN users u ON o.user_id u.id AND u.level VIP;区别在于把条件放到ON子句里它就成了连接条件的一部分。很多优化器会在生成执行计划时更自然地把它应用到users表访问阶段甚至配合索引直接产出过滤后的数据集。而如果只放在WHERE里某些优化器可能先去做连接然后再回头做过滤导致性能雪崩。连接条件下推不只是针对ON子句里的等值条件。它还包括子查询展开后的下推、分区裁剪、以及存储引擎层的条件下推。适用场景通常有两个共同特征第一连接中有高选择性的过滤条件第二当前连接顺序导致大表在连接前没有机会减量。数据仓库和报表类SQL里这种情况尤其多。2.2 为什么能快到亚毫秒收益计算收益可以用一个非常粗糙的公式来感受一下。假设订单表有1000万行其中真正业务需要的数据只占1%即10万行用户表100万行VIP用户占1%即1万行。如果不下推优化器选了订单表作为驱动表扫描1000万行再逐行去用户表探测。哪怕用户表主键查找只要0.01毫秒1000万次探测也要10万毫秒也就是100秒。当然实际执行计划不会这么蠢索引和过滤条件会带来一些优化但量级差别是实实在在的。如果下推生效订单表扫描前先按状态、时间过滤到10万行用户表也先按VIP标志过滤到1万行。让1万行的用户表当驱动表去探测10万行的订单表每次探测同样是0.01毫秒总耗时不过1000毫秒。如果再加一层联合索引让探测变成索引覆盖扫描耗时可以直接掉到个位数毫秒。我那个案例里原始SQL扫描了约360万行订单表实际满足条件的订单只有几万行。下推后驱动表变成只有几千行的VIP用户表订单表侧通过用户ID加过滤条件的联合索引去探测每次探测返回的行数非常少整个连接过程只在内存里完成。从1200ms到0.4ms靠的就是减少参与连接的数据量而不是把CPU频率调高。3. 实战优化从1200ms到0.4ms的完整过程3.1 原始SQL与表结构案例背景是一个订单中心的聚合统计接口需求是统计某个月份VIP用户的未支付订单金额。表结构大致如下CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, status TINYINT NOT NULL, amount DECIMAL(12,2) NOT NULL, created_at DATETIME NOT NULL, KEY idx_status_time (status, created_at) ); CREATE TABLE users ( id BIGINT PRIMARY KEY, level VARCHAR(16) NOT NULL, KEY idx_level (level) );orders表大概有360万行users表有10万行。业务侧统计某个月的未支付订单满足条件的订单只有几万行VIP用户大约5000人。原始SQL长这样SELECT SUM(o.amount) AS total_amount, COUNT(*) AS cnt FROM orders o JOIN users u ON o.user_id u.id WHERE u.level VIP AND o.status 1 AND o.created_at 2024-01-01 AND o.created_at 2024-02-01;从需求角度看不复杂索引好像也有。但这条SQL在测试环境跑一次就是1200ms生产环境高峰期更差。业务方一度以为是订单表太大提出要分表。我没急着回先看执行计划。3.2 定位问题执行计划中的关键信号执行计划里最扎眼的几列我摘了出来tabletypepossible_keysrowsfilteredExtraordersALLidx_status_time3627800100.0Using whereuserseq_refPRIMARY110.0Using whereorders表的访问方式是全表扫描优化器预估要扫362万行。它宁肯全表扫也不走idx_status_time因为status1这个条件区分度太差在优化器看来按状态过滤后可能还要扫很大一部分数据倒不如全表扫。问题在于这个全表扫描产生的每一行都要去users表做一次主键查找然后再去判断u.levelVIP。filtered10%表示只有10%的用户最终满足VIP条件但这10%的过滤发生在users被查出来之后。可以粗略估算一下成本360万次驱动表行扫描每次都要走一次主键探测即使每次探测极快总耗时也下不来。更关键的是orders上的status和created_at条件在Extra里只是Using where不是Using index condition也就是说这些过滤发生在回表之后。执行计划里没有出现任何“先过滤再连接”的信号。这就是典型的过滤时机错误订单表的过滤没在读取阶段生效用户表的VIP条件也没在读取阶段生效。两个条件都被留到了连接之后。连接条件下推完全没起作用。3.3 改写SQL显式传达下推意图定位到问题后我做了第一版改写把u.levelVIP从WHERE挪到JOIN的ON子句让它变成连接条件的一部分。SELECT SUM(o.amount) AS total_amount, COUNT(*) AS cnt FROM orders o JOIN users u ON o.user_id u.id AND u.level VIP WHERE o.status 1 AND o.created_at 2024-01-01 AND o.created_at 2024-02-01;这个改写对inner join来说语义没有变化但给优化器重新评估连接顺序的机会。原来优化器觉得反正最后都要过滤VIP那先用谁当驱动表都差不多于是按SQL从左到右选了orders。现在VIP条件被塞进连接条件users表的访问阶段就可能变成一个独立的过滤节点优化器会重新计算两个表过滤后的行数。同时我调整了订单表的索引把原来没什么用的idx_status_time改成连接键和过滤键的联合索引ALTER TABLE orders ADD KEY idx_user_status_time (user_id, status, created_at);这一步非常关键。下推让users表有机会成为驱动表但orders表侧的探测效率也得跟上。联合索引(user_id, status, created_at)可以把“按用户ID定位订单”和“按状态、时间过滤订单”合并成一次索引范围内的操作避免探测后大量回表。第二版改写我也顺手测了把订单表先过滤成临时结果再用CTE和users连接WITH filtered_orders AS ( SELECT user_id, amount FROM orders WHERE status 1 AND created_at 2024-01-01 AND created_at 2024-02-01 ) SELECT SUM(o.amount) AS total_amount, COUNT(*) AS cnt FROM filtered_orders o JOIN users u ON o.user_id u.id WHERE u.level VIP;CTE写法在某些版本里会把过滤后的订单表物化成临时结果这等于手动做了一次下推。但它不一定比ON改写更好因为临时结果仍然可能很大而且多了一次临时表读写。具体选择哪个得看实际执行计划和耗时。3.4 验证效果复测与执行计划对比优化后的执行计划大概变成这样tabletypekeyrowsfilteredExtrausersrefidx_level5012100.0Using index conditionordersrefidx_user_status_time490.0Using index conditionusers表扫描量从原来的360万次主键探测变成了5012行级别orders表通过(user_id, status, created_at)索引去探测每次返回的订单数量只有几行而且过滤都在索引层完成。中间结果从百万级降到了几百行。实测数据如下版本扫描行数中间结果行数平均耗时原始SQL3627800约38万1207msON改写 联合索引5012 约2万约8200.4msCTE改写 联合索引约2万 5012约8208.1ms为什么CTE版本反而不如ON改写因为CTE强制物化订单表额外产生了一次临时表写入和读取。虽然业务上也能接受8毫秒但跟0.4ms比还是有差距。这个对比也说明一个问题改写SQL不是目的目的是让优化器做出正确的下推决策有时候手动“替优化器做决定”反而画蛇添足。验证过程中要注意清缓存、多跑几次取中位数。我第一次测试时直接连着跑后面的结果明显偏快因为热点数据已经进了缓存。调整后我用SELECT SLEEP(1)间隔开了每次查询才拿到稳定数据。4. 连接条件下推的进阶玩法与避坑指南4.1 三种值得关注的下推形态第一类是普通连接条件折叠进索引。JOIN条件里的等值关系如果能和过滤条件放到同一个索引里性能收益最大。比如我之前加的那个联合索引本质上就是把“连接键 两张表的过滤条件”塞进了一个索引结构让表访问阶段能一次性完成定位和过滤。第二类是子查询半连接展开。很多业务SQL喜欢写IN子查询例如SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE level VIP) AND status 1;执行时如果逐行去执行子查询性能通常惨不忍睹。支持半连接的优化器会把IN子查询展开成内连接然后再做连接条件下推SELECT o.* FROM orders o JOIN users u ON o.user_id u.id AND u.level VIP WHERE o.status 1;这种改写等于把相关子查询变成了普通JOIN再利用小表驱动。很多“为什么子查询这么慢”的问题本质上就是半连接没有展开或者展开后没有下推。第三类是分区裁剪。当连接条件里有分区键时优化器会在连接前直接把不需要的分区排除掉。比如事实表按月份分区JOIN ... ON fact.month dim.month AND dim.month 2024-01它不会去读2月、3月的分区文件。这类下推在数据量极大的场景下价值比索引更明显因为它减少的是IO开销最底层的文件读取量。4.2 不要盲写下推三大约束连接条件下推不是无脑把WHERE条件全塞进ON子句。最容易踩的坑是外连接语义变化。比如统计所有用户以及他们的未支付订单SELECT u.id, o.amount FROM users u LEFT JOIN orders o ON o.user_id u.id AND o.status 1;这个SQL会保留没有订单的用户。如果把o.status 1从ON里挪到WHERESELECT u.id, o.amount FROM users u LEFT JOIN orders o ON o.user_id u.id WHERE o.status 1;结果就变成了“只有未支付订单的用户”那些没有订单的用户会被过滤掉。两种写法返回的数据完全不一样。所以对于外连接ON子句里的下推和WHERE里的下推不能随意互换。第二个约束是非确定性函数。NOW()、RAND()、UUID()这类函数如果被下推到表的扫描阶段每一行扫描时都可能得到不同的值结果无法保持一致性。优化器通常不会下推这类条件你硬写在ON里也可能无法利用索引。第三个约束是表达式和函数包裹。DATE(created_at)、amount * 0.9 100这类写法因为不是裸列数据库很难把它直接转成索引范围扫描下推效果大打折扣。优化器它“想推也推不下去”。遇到这种SQL与其改连接顺序不如先考虑把表达式规范化比如改成created_at 2024-01-01 AND created_at 2024-01-02。4.3 统计信息与索引的配合下推能否成功依赖优化器对数据分布的判断而判断依据是统计信息。如果users表还没做过统计信息更新优化器可能以为VIP用户有8万人而实际上只有5000人它就不愿意让users当驱动表还是会选择大表驱动。很多下推失效案例root cause不是SQL写法而是统计信息陈旧。所以拿到一个慢查询第一步不该是立刻改SQL而是查一下表的统计信息是否新鲜。批量导数据之后没跑ANALYZE就会发生这种情况。更新统计信息后再看执行计划可能什么都不用改SQL自己就变快了。索引配合下推有一个细节容易被忽略联合索引的字段顺序要按“等值条件优先、范围条件最后”来设计。像(user_id, status, created_at)user_id和status是等值created_at是范围。如果把created_at放在前面user_id的过滤作用就发挥不出来连接探测仍然精准不了。直方图也很关键如果某个低区分度字段上有数据倾斜优化器看直方图会比普通统计信息更准确地判断过滤比例从而敢于选择下推路径。5. 高频问题排查为什么我的SQL没有被下推5.1 下推失败的五个常见原因原因典型信号解决方向字段类型隐式转换JOIN列类型不一致索引没生效统一字段类型去掉隐式转换过滤条件被函数包裹执行计划出现全表扫描filtered偏高改写成裸列比较或建立函数索引统计信息过期rows估算严重偏离真实值更新统计信息必要时添加直方图优化器版本/开关限制半连接没有展开子查询逐行执行升级版本打开半连接优化开关非确定性函数参与过滤过滤条件无法下推执行计划变复杂先把函数结果算出来再传入SQL第一种情况非常普遍。订单表user_id字段用了VARCHAR用户表id是BIGINT两列类型不一致JOIN时数据库要把一侧做隐式转换。隐式转换一出现索引基本就废了下推也无从谈起。这种问题执行计划里看不出明显报错但你会发现possible_keys有索引key却是空的。第二种情况我上面提过DATE(created_at)这类写法会挡住下推路径。很多报表SQL习惯写WHERE DATE(created_at) CURDATE()看起来很简洁实际上优化器没法把它转为索引范围扫描。改成created_at 2024-01-01 00:00:00和created_at 2024-01-02 00:00:00之后下推立刻生效。5.2 排查工具与定位技巧先看执行计划的估算。重点关注type是不是ALL、rows是不是大得离谱、filtered是不是很低、Extra里有没有Using join buffer。这四个信号组合出现基本锁定下推失效。再看实际执行时长。用EXPLAIN ANALYZE或查询运行时监控来获取每个节点真实耗时。估算值是优化器给的真实耗时是执行引擎给的。我遇到过估算500行实际50万行的情况优化器因为统计信息错误做出了错误连接顺序。定位这类问题真实耗时比估算值可靠得多。然后可以开优化器跟踪。很多数据库都有类似 optimizer trace 的机制你可以看到优化器在候选连接顺序之间是如何计算代价的。我自己调优时会重点看两个时间点过滤条件是在扫描节点上应用还是在JOIN节点之后应用驱动表的候选方案里有没有一个“过滤后行数更小”的选项。一旦发现优化器因为某个不合理的估算没有选小表驱动大半问题就找到了。最后是最小复现法。把慢SQL里的聚合函数去掉只保留JOIN和过滤条件观察执行计划是否依旧有问题。如果JOIN层已经正常再一层层加回聚合、排序、窗口函数判断性能瓶颈究竟在哪一段。这个方法比对着完整SQL猜测快得多。5.3 兜底方案不改业务逻辑的调优手段如果优化器死活不配合还有几条不需要改业务逻辑的路可走。第一手动拆SQL。把过滤后的数据先落到临时表再对临时表做JOIN。这相当于人肉下推。适合那些优化器版本太老、半连接能力太弱的场景。注意临时表也要建索引否则只是把慢从主查询挪到临时表。第二调整索引设计。如果过滤列和连接键分布在两个索引里数据库只能选其中一个。把过滤列和连接键组合成一个联合索引等于把下推路径铺好。第三建立物化视图或结果表。报表类SQL如果重复跑同一套聚合逻辑预计算结果表带来的收益远大于继续调优SQL。这个方案治本但需要关注数据刷新延迟适合非实时场景。第四在应用层手动控制连接顺序。有些数据库支持从FROM或JOIN的书写顺序影响优化器也支持查询提示。这类手段不如优化器自动决策稳定但作为兜底仍然有效。用了提示之后一定要在生产环境做回归因为统计信息变化后固定的连接顺序可能不再是最优。6. 一次实践后的经验沉淀优化这么久我的感受是连接条件下推不是某个数据库独有的话题而是一种该被写进SQL直觉里的思考方式。每次写连接查询之前先问自己一句每张表在参与JOIN之前到底能不能再小一点。1200ms到0.4ms不是数据库变强了只是让每一行数据都晚一点进入连接、早一点被过滤。最后再分享一个小技巧不要只看总耗时看执行计划里第一张表的扫描行数。如果它是百万级而最终结果只有几百行那这个SQL大概率还能再快。拿这个标准去审视你的慢查询会少走很多弯路。
半个月前我接到一条慢查询工单一个订单聚合报表SQL平均耗时1200ms高峰期甚至到1.5秒。业务方已经想把报表接口改成异步任务我拦住他们说先别急。翻出执行计划一看问题根本不是缺索引也不是业务逻辑复杂而是连接条件下推没有生效过滤条件在JOIN之后才开始作用导致百万级中间结果白白生成又白白丢弃。我把查询改了一版把过滤条件从WHERE移到JOIN的ON子句里同一个查询瞬间降到0.4ms。这一条SQL从千毫秒到亚毫秒没有加机器没有改业务逻辑靠的就是让优化器把连接条件推得更彻底。如果你也在被复杂SQL折磨动不动几百毫秒上千毫秒执行计划看不懂加索引也没效果这篇文章就是写给你看的。我会从执行计划的信号讲起聊清楚什么是连接条件下推再给你一个可以照抄的实战案例最后把那些“为什么我的SQL没有被下推”的坑一起填掉。1. 复杂SQL性能瓶颈别被千毫秒骗了1.1 先从执行计划看起慢在哪一步一条SQL在数据库里并不是直接面向表执行的。它要经过语法解析、逻辑优化、物理优化最后生成一棵执行计划树然后由执行引擎按照这棵树去读表、连接、分组、排序。执行计划就是优化器给你的一张“施工图”每一张表怎么读、按什么顺序连接、过滤条件在哪个节点被应用全部写在这张图里。大多数慢查询问题都出在这张图上而不是SQL本身。我见过太多人遇到慢SQL就条件反射地加索引但索引只是其中一个变量。更常见的坑是过滤条件没有被下推到表的扫描阶段而是在JOIN完成之后才被应用。这好比炒菜的时候不是先把菜洗干净再切而是把所有食材混在一起炒完之后再从锅里往外挑坏叶子。优化器如果选择了这种计划哪怕索引再多也救不了。我在定位复杂SQL问题时第一件事就是看执行计划里的三个信号扫描行数、连接顺序、过滤位置。尤其要留意Extra里有没有出现类似Using where; Using join buffer的标记或者某个节点的rows大于最终返回行数几个数量级。一旦出现这种信号十有八九是下推失效。比如下面这种简化的执行计划形态- Nested loop inner join (cost253000 rows3628000) - Table scan on orders (cost120000 rows3627800) - Filter: (users.level VIP) (cost1.1 rows356) - Single-row index lookup on users using PRIMARY KEY (idorders.user_id) - Filter: (orders.status 1) AND (orders.created_at ...)这里的Filter: users.level VIP出现在连接操作的下一层意味着数据库先把订单表每一行拿去找用户再判断用户是不是VIP最后才判断订单状态。中间产出了几百万行临时结果绝大多数行都被丢弃了。连接条件下推如果生效这个Filter应该在读取users表的那一刻就发生甚至可以直接走索引把VIP用户先筛成一张小表。1.2 连接顺序与数据量为什么连接是重灾区数据库里最消耗资源的操作JOIN通常排在第一位。原因很简单连接会把多个表的数据按某种方式组合组合过程中数据量会被放大。100万行的订单表JOIN 10万行的用户表即使连接键有索引需要处理的数据量也可能达到百万级甚至更高。如果Join之前不做过滤这些数据就会全部涌进连接运算。以嵌套循环连接为例成本大致可以理解成“外层表行数 x 内层表探测成本”。外层表100万行内层表哪怕每次探测只要0.1毫秒总耗时也要100秒。而哈希连接虽然不回表探测但需要先构建哈希表构建表的行数直接决定内存消耗和构建耗时。如果是多表连接中间结果还会继续向后传递形成放大效应。所以我会在调优时反复强调一个理念SQL性能天花板不在于连接算法有多高级而在于数据是什么时候第一次被减下来的。连接条件下推的核心就是让过滤尽可能发生在“数据进入连接之前”而不是“连接完成之后”。这也是为什么同样一条SQL改写前后能从千毫秒到亚毫秒。2. 连接条件下推的核心思路与适用场景2.1 下推到底在推什么很多人把连接条件下推和谓词下推混为一谈其实两者有区别但又有很强的关联。谓词下推是指把一个过滤条件比如status1、created_at 2024-01-01从上层节点推到下面的表扫描节点让数据在读取阶段就被筛掉。连接条件下推则更聚焦于JOIN过程把连接条件里针对某一张表的过滤部分推给那张表的扫描或者索引查找。举个例子。假设有下面这段SQLSELECT * FROM orders o JOIN users u ON o.user_id u.id WHERE u.level VIP;优化器可以把u.levelVIP这个条件下推到users表扫描阶段。从语义上说它和下面这种写法在内连接场景下是等价的SELECT * FROM orders o JOIN users u ON o.user_id u.id AND u.level VIP;区别在于把条件放到ON子句里它就成了连接条件的一部分。很多优化器会在生成执行计划时更自然地把它应用到users表访问阶段甚至配合索引直接产出过滤后的数据集。而如果只放在WHERE里某些优化器可能先去做连接然后再回头做过滤导致性能雪崩。连接条件下推不只是针对ON子句里的等值条件。它还包括子查询展开后的下推、分区裁剪、以及存储引擎层的条件下推。适用场景通常有两个共同特征第一连接中有高选择性的过滤条件第二当前连接顺序导致大表在连接前没有机会减量。数据仓库和报表类SQL里这种情况尤其多。2.2 为什么能快到亚毫秒收益计算收益可以用一个非常粗糙的公式来感受一下。假设订单表有1000万行其中真正业务需要的数据只占1%即10万行用户表100万行VIP用户占1%即1万行。如果不下推优化器选了订单表作为驱动表扫描1000万行再逐行去用户表探测。哪怕用户表主键查找只要0.01毫秒1000万次探测也要10万毫秒也就是100秒。当然实际执行计划不会这么蠢索引和过滤条件会带来一些优化但量级差别是实实在在的。如果下推生效订单表扫描前先按状态、时间过滤到10万行用户表也先按VIP标志过滤到1万行。让1万行的用户表当驱动表去探测10万行的订单表每次探测同样是0.01毫秒总耗时不过1000毫秒。如果再加一层联合索引让探测变成索引覆盖扫描耗时可以直接掉到个位数毫秒。我那个案例里原始SQL扫描了约360万行订单表实际满足条件的订单只有几万行。下推后驱动表变成只有几千行的VIP用户表订单表侧通过用户ID加过滤条件的联合索引去探测每次探测返回的行数非常少整个连接过程只在内存里完成。从1200ms到0.4ms靠的就是减少参与连接的数据量而不是把CPU频率调高。3. 实战优化从1200ms到0.4ms的完整过程3.1 原始SQL与表结构案例背景是一个订单中心的聚合统计接口需求是统计某个月份VIP用户的未支付订单金额。表结构大致如下CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, status TINYINT NOT NULL, amount DECIMAL(12,2) NOT NULL, created_at DATETIME NOT NULL, KEY idx_status_time (status, created_at) ); CREATE TABLE users ( id BIGINT PRIMARY KEY, level VARCHAR(16) NOT NULL, KEY idx_level (level) );orders表大概有360万行users表有10万行。业务侧统计某个月的未支付订单满足条件的订单只有几万行VIP用户大约5000人。原始SQL长这样SELECT SUM(o.amount) AS total_amount, COUNT(*) AS cnt FROM orders o JOIN users u ON o.user_id u.id WHERE u.level VIP AND o.status 1 AND o.created_at 2024-01-01 AND o.created_at 2024-02-01;从需求角度看不复杂索引好像也有。但这条SQL在测试环境跑一次就是1200ms生产环境高峰期更差。业务方一度以为是订单表太大提出要分表。我没急着回先看执行计划。3.2 定位问题执行计划中的关键信号执行计划里最扎眼的几列我摘了出来tabletypepossible_keysrowsfilteredExtraordersALLidx_status_time3627800100.0Using whereuserseq_refPRIMARY110.0Using whereorders表的访问方式是全表扫描优化器预估要扫362万行。它宁肯全表扫也不走idx_status_time因为status1这个条件区分度太差在优化器看来按状态过滤后可能还要扫很大一部分数据倒不如全表扫。问题在于这个全表扫描产生的每一行都要去users表做一次主键查找然后再去判断u.levelVIP。filtered10%表示只有10%的用户最终满足VIP条件但这10%的过滤发生在users被查出来之后。可以粗略估算一下成本360万次驱动表行扫描每次都要走一次主键探测即使每次探测极快总耗时也下不来。更关键的是orders上的status和created_at条件在Extra里只是Using where不是Using index condition也就是说这些过滤发生在回表之后。执行计划里没有出现任何“先过滤再连接”的信号。这就是典型的过滤时机错误订单表的过滤没在读取阶段生效用户表的VIP条件也没在读取阶段生效。两个条件都被留到了连接之后。连接条件下推完全没起作用。3.3 改写SQL显式传达下推意图定位到问题后我做了第一版改写把u.levelVIP从WHERE挪到JOIN的ON子句让它变成连接条件的一部分。SELECT SUM(o.amount) AS total_amount, COUNT(*) AS cnt FROM orders o JOIN users u ON o.user_id u.id AND u.level VIP WHERE o.status 1 AND o.created_at 2024-01-01 AND o.created_at 2024-02-01;这个改写对inner join来说语义没有变化但给优化器重新评估连接顺序的机会。原来优化器觉得反正最后都要过滤VIP那先用谁当驱动表都差不多于是按SQL从左到右选了orders。现在VIP条件被塞进连接条件users表的访问阶段就可能变成一个独立的过滤节点优化器会重新计算两个表过滤后的行数。同时我调整了订单表的索引把原来没什么用的idx_status_time改成连接键和过滤键的联合索引ALTER TABLE orders ADD KEY idx_user_status_time (user_id, status, created_at);这一步非常关键。下推让users表有机会成为驱动表但orders表侧的探测效率也得跟上。联合索引(user_id, status, created_at)可以把“按用户ID定位订单”和“按状态、时间过滤订单”合并成一次索引范围内的操作避免探测后大量回表。第二版改写我也顺手测了把订单表先过滤成临时结果再用CTE和users连接WITH filtered_orders AS ( SELECT user_id, amount FROM orders WHERE status 1 AND created_at 2024-01-01 AND created_at 2024-02-01 ) SELECT SUM(o.amount) AS total_amount, COUNT(*) AS cnt FROM filtered_orders o JOIN users u ON o.user_id u.id WHERE u.level VIP;CTE写法在某些版本里会把过滤后的订单表物化成临时结果这等于手动做了一次下推。但它不一定比ON改写更好因为临时结果仍然可能很大而且多了一次临时表读写。具体选择哪个得看实际执行计划和耗时。3.4 验证效果复测与执行计划对比优化后的执行计划大概变成这样tabletypekeyrowsfilteredExtrausersrefidx_level5012100.0Using index conditionordersrefidx_user_status_time490.0Using index conditionusers表扫描量从原来的360万次主键探测变成了5012行级别orders表通过(user_id, status, created_at)索引去探测每次返回的订单数量只有几行而且过滤都在索引层完成。中间结果从百万级降到了几百行。实测数据如下版本扫描行数中间结果行数平均耗时原始SQL3627800约38万1207msON改写 联合索引5012 约2万约8200.4msCTE改写 联合索引约2万 5012约8208.1ms为什么CTE版本反而不如ON改写因为CTE强制物化订单表额外产生了一次临时表写入和读取。虽然业务上也能接受8毫秒但跟0.4ms比还是有差距。这个对比也说明一个问题改写SQL不是目的目的是让优化器做出正确的下推决策有时候手动“替优化器做决定”反而画蛇添足。验证过程中要注意清缓存、多跑几次取中位数。我第一次测试时直接连着跑后面的结果明显偏快因为热点数据已经进了缓存。调整后我用SELECT SLEEP(1)间隔开了每次查询才拿到稳定数据。4. 连接条件下推的进阶玩法与避坑指南4.1 三种值得关注的下推形态第一类是普通连接条件折叠进索引。JOIN条件里的等值关系如果能和过滤条件放到同一个索引里性能收益最大。比如我之前加的那个联合索引本质上就是把“连接键 两张表的过滤条件”塞进了一个索引结构让表访问阶段能一次性完成定位和过滤。第二类是子查询半连接展开。很多业务SQL喜欢写IN子查询例如SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE level VIP) AND status 1;执行时如果逐行去执行子查询性能通常惨不忍睹。支持半连接的优化器会把IN子查询展开成内连接然后再做连接条件下推SELECT o.* FROM orders o JOIN users u ON o.user_id u.id AND u.level VIP WHERE o.status 1;这种改写等于把相关子查询变成了普通JOIN再利用小表驱动。很多“为什么子查询这么慢”的问题本质上就是半连接没有展开或者展开后没有下推。第三类是分区裁剪。当连接条件里有分区键时优化器会在连接前直接把不需要的分区排除掉。比如事实表按月份分区JOIN ... ON fact.month dim.month AND dim.month 2024-01它不会去读2月、3月的分区文件。这类下推在数据量极大的场景下价值比索引更明显因为它减少的是IO开销最底层的文件读取量。4.2 不要盲写下推三大约束连接条件下推不是无脑把WHERE条件全塞进ON子句。最容易踩的坑是外连接语义变化。比如统计所有用户以及他们的未支付订单SELECT u.id, o.amount FROM users u LEFT JOIN orders o ON o.user_id u.id AND o.status 1;这个SQL会保留没有订单的用户。如果把o.status 1从ON里挪到WHERESELECT u.id, o.amount FROM users u LEFT JOIN orders o ON o.user_id u.id WHERE o.status 1;结果就变成了“只有未支付订单的用户”那些没有订单的用户会被过滤掉。两种写法返回的数据完全不一样。所以对于外连接ON子句里的下推和WHERE里的下推不能随意互换。第二个约束是非确定性函数。NOW()、RAND()、UUID()这类函数如果被下推到表的扫描阶段每一行扫描时都可能得到不同的值结果无法保持一致性。优化器通常不会下推这类条件你硬写在ON里也可能无法利用索引。第三个约束是表达式和函数包裹。DATE(created_at)、amount * 0.9 100这类写法因为不是裸列数据库很难把它直接转成索引范围扫描下推效果大打折扣。优化器它“想推也推不下去”。遇到这种SQL与其改连接顺序不如先考虑把表达式规范化比如改成created_at 2024-01-01 AND created_at 2024-01-02。4.3 统计信息与索引的配合下推能否成功依赖优化器对数据分布的判断而判断依据是统计信息。如果users表还没做过统计信息更新优化器可能以为VIP用户有8万人而实际上只有5000人它就不愿意让users当驱动表还是会选择大表驱动。很多下推失效案例root cause不是SQL写法而是统计信息陈旧。所以拿到一个慢查询第一步不该是立刻改SQL而是查一下表的统计信息是否新鲜。批量导数据之后没跑ANALYZE就会发生这种情况。更新统计信息后再看执行计划可能什么都不用改SQL自己就变快了。索引配合下推有一个细节容易被忽略联合索引的字段顺序要按“等值条件优先、范围条件最后”来设计。像(user_id, status, created_at)user_id和status是等值created_at是范围。如果把created_at放在前面user_id的过滤作用就发挥不出来连接探测仍然精准不了。直方图也很关键如果某个低区分度字段上有数据倾斜优化器看直方图会比普通统计信息更准确地判断过滤比例从而敢于选择下推路径。5. 高频问题排查为什么我的SQL没有被下推5.1 下推失败的五个常见原因原因典型信号解决方向字段类型隐式转换JOIN列类型不一致索引没生效统一字段类型去掉隐式转换过滤条件被函数包裹执行计划出现全表扫描filtered偏高改写成裸列比较或建立函数索引统计信息过期rows估算严重偏离真实值更新统计信息必要时添加直方图优化器版本/开关限制半连接没有展开子查询逐行执行升级版本打开半连接优化开关非确定性函数参与过滤过滤条件无法下推执行计划变复杂先把函数结果算出来再传入SQL第一种情况非常普遍。订单表user_id字段用了VARCHAR用户表id是BIGINT两列类型不一致JOIN时数据库要把一侧做隐式转换。隐式转换一出现索引基本就废了下推也无从谈起。这种问题执行计划里看不出明显报错但你会发现possible_keys有索引key却是空的。第二种情况我上面提过DATE(created_at)这类写法会挡住下推路径。很多报表SQL习惯写WHERE DATE(created_at) CURDATE()看起来很简洁实际上优化器没法把它转为索引范围扫描。改成created_at 2024-01-01 00:00:00和created_at 2024-01-02 00:00:00之后下推立刻生效。5.2 排查工具与定位技巧先看执行计划的估算。重点关注type是不是ALL、rows是不是大得离谱、filtered是不是很低、Extra里有没有Using join buffer。这四个信号组合出现基本锁定下推失效。再看实际执行时长。用EXPLAIN ANALYZE或查询运行时监控来获取每个节点真实耗时。估算值是优化器给的真实耗时是执行引擎给的。我遇到过估算500行实际50万行的情况优化器因为统计信息错误做出了错误连接顺序。定位这类问题真实耗时比估算值可靠得多。然后可以开优化器跟踪。很多数据库都有类似 optimizer trace 的机制你可以看到优化器在候选连接顺序之间是如何计算代价的。我自己调优时会重点看两个时间点过滤条件是在扫描节点上应用还是在JOIN节点之后应用驱动表的候选方案里有没有一个“过滤后行数更小”的选项。一旦发现优化器因为某个不合理的估算没有选小表驱动大半问题就找到了。最后是最小复现法。把慢SQL里的聚合函数去掉只保留JOIN和过滤条件观察执行计划是否依旧有问题。如果JOIN层已经正常再一层层加回聚合、排序、窗口函数判断性能瓶颈究竟在哪一段。这个方法比对着完整SQL猜测快得多。5.3 兜底方案不改业务逻辑的调优手段如果优化器死活不配合还有几条不需要改业务逻辑的路可走。第一手动拆SQL。把过滤后的数据先落到临时表再对临时表做JOIN。这相当于人肉下推。适合那些优化器版本太老、半连接能力太弱的场景。注意临时表也要建索引否则只是把慢从主查询挪到临时表。第二调整索引设计。如果过滤列和连接键分布在两个索引里数据库只能选其中一个。把过滤列和连接键组合成一个联合索引等于把下推路径铺好。第三建立物化视图或结果表。报表类SQL如果重复跑同一套聚合逻辑预计算结果表带来的收益远大于继续调优SQL。这个方案治本但需要关注数据刷新延迟适合非实时场景。第四在应用层手动控制连接顺序。有些数据库支持从FROM或JOIN的书写顺序影响优化器也支持查询提示。这类手段不如优化器自动决策稳定但作为兜底仍然有效。用了提示之后一定要在生产环境做回归因为统计信息变化后固定的连接顺序可能不再是最优。6. 一次实践后的经验沉淀优化这么久我的感受是连接条件下推不是某个数据库独有的话题而是一种该被写进SQL直觉里的思考方式。每次写连接查询之前先问自己一句每张表在参与JOIN之前到底能不能再小一点。1200ms到0.4ms不是数据库变强了只是让每一行数据都晚一点进入连接、早一点被过滤。最后再分享一个小技巧不要只看总耗时看执行计划里第一张表的扫描行数。如果它是百万级而最终结果只有几百行那这个SQL大概率还能再快。拿这个标准去审视你的慢查询会少走很多弯路。
