前一阵子处理了一单现场慢SQL排查问题出在达梦数据库的一个子查询上。单看SQL并不复杂两层嵌套、几个索引都在可就是跑起来五六秒起步数据量一上来直接拖垮业务接口。后来把子查询单独拎出来分析、改写、再对比执行计划才真正意识到一个事实在达梦数据库里子查询优化往往不是语法对不对的问题而是优化器到底把它处理成了什么形状的问题。这篇文章是达梦数据库SQL优化系列的第四篇专门聊子查询优化。适合两类人看一类是刚接触达梦、习惯用MySQL或Oracle思维写SQL的开发同学另一类是已经在生产环境里被慢SQL折磨过、想系统梳理排查方法的运维或DBA。文章会围绕子查询在DM8中的典型执行形态、IN/EXISTS/JOIN的选型对比、标量子查询的隐藏代价、关联子查询的改写边界以及一套可复制的排查链路展开最后用一个500万行订单表的完整案例收尾。1. 为什么子查询在达梦里经常变成逐行执行1.1 优化器并不总是做子查询展开很多人写子查询的时候默认优化器会把子查询展开成JOIN来跑这个理解在大多数场景下成立但并不是绝对的。所谓子查询展开指的是优化器把WHERE a.id IN (SELECT b.id FROM b)这种结构改写成半连接或者普通连接计划。展开之后优化器可以把两个表的访问路径、连接顺序放在一起全局评估有机会选择HASH JOIN、排序合并连接等更高效的算子。但达梦优化器的处理逻辑里有一类子查询无法或不会展开最典型的就是关联子查询和带非等值过滤条件的标量子查询。当子查询需要引用外层表的列时它必须对外层每一行单独计算这时候执行计划里就会出现类似FILTER或SLCT的算子配合嵌套循环连接也就是业内常说的逐行执行。打个好懂的比方如果把两张表的连接比作把两份名单合并核对那么不展开的子查询就相当于每核对一个外层人员就把整个内层名单重新翻一遍。单看一遍可能只要几毫秒可外层如果我们有十万行就是十万次重复扫描累计时间非常恐怖。1.2 执行计划里出现这些算子要警惕在DM8里通过EXPLAIN查看执行计划时我通常会重点关注两类信号计划树中出现NEST LOOP且内层有SLCT或FILTER并且SLCT有对外的列引用子查询对应的子树上方带有逐行计算特征扫描次数记录在计划注释里明显高于预期。另外还有一种形态是优化器把子查询结果物化。物化相当于先把子查询结果算出来塞进一个临时结构再和外层做连接。这个策略本身没问题但如果子查询本身过滤性很差、结果集很大物化本身就会成为新的瓶颈。我在达梦上见过不少SQL慢不是慢在连接而是慢在物化阶段尤其当子查询内部还要做聚合时。1.3 统计信息会直接影响子查询改写决策同样一条SQL在开发库跑得飞快到生产库慢成蜗牛很多人第一反应是数据库版本不一样其实大多数时候是统计信息不一样。达梦优化器判断要不要展开子查询、展开后选什么连接顺序严重依赖表的行数、唯一值数量、过滤条件选择率。如果表没有收集统计信息或者数据发生过大量增删改而统计信息长时间不更新优化器就会拿默认值去猜猜错了自然容易选出一个灾难级计划。所以排查子查询慢SQL时我的第一个动作往往不是改写SQL而是先查统计信息。达梦里可以通过DBMS_STATS包收集也可以用管理工具手动更新。很多看起来需要改写的问题统计信息一刷新计划立刻正常。2. IN、EXISTS和JOIN到底怎么选一次实测对比2.1 测试环境和SQL设计先声明一下下面的对比是在DM8测试库上跑的两张表用户表T_USER约10万行订单表T_ORDER约500万行其中USER_ID上有索引。三个写法分别是-- 写法1IN SELECT * FROM T_ORDER o WHERE o.USER_ID IN (SELECT u.ID FROM T_USER u WHERE u.LEVEL 1); -- 写法2EXISTS SELECT * FROM T_ORDER o WHERE EXISTS (SELECT 1 FROM T_USER u WHERE u.ID o.USER_ID AND u.LEVEL 1); -- 写法3JOIN SELECT o.* FROM T_ORDER o JOIN (SELECT u.ID FROM T_USER u WHERE u.LEVEL 1) t ON t.ID o.USER_ID;从结果集语义上讲IN和EXISTS返回的都是订单表原始行不会重复而JOIN写法如果T_USER里同一个ID出现多条结果就会膨胀。因此在真实场景里JOIN改写需要在子查询里做去重或者接收者明确知道不会重复。2.2 执行计划差异半连接与普通连接实测下来三种写法在达梦优化器手里呈现出两种典型路线IN和EXISTS在子查询可以展开的情况下优化器都会倾向使用半连接SEMI JOIN语义。计划里会看到哈希半连接或排序半连接底层只需要判断是否存在匹配行一旦命中就停止扫描内层理论上比普通JOIN少做很多无用功。这两种写法在达梦里拿到的基本是同一类计划性能差异很小。JOIN改写后走的是普通连接计划形态通常是HASH JOIN或NEST LOOP JOIN。如果子查询没有去重我这里故意没去重优化器产生的结果行数可能偏大甚至影响后续算子的评估。很多线上事故就是这么来的——为了优化把IN改成JOIN结果多表连接导致结果集翻倍业务上数据错了性能也没救回来。注意IN和EXISTS并不是在所有情况下等价。如果子查询结果集中出现NULLIN的结果可能和EXISTS不同这个坑后面单独展开。2.3 NULL语义与去重的隐藏问题先看NULL对比较逻辑的影响。WHERE o.USER_ID IN (SELECT u.ID FROM ...)这条SQL在关系理论上等价于o.USER_ID u.ID这个表达式对至少一行返回TRUE但如果u.ID里有NULLNULL参与等值比较时结果是UNKNOWN它不会让条件变成TRUE却会让最终结果集变少。尤其当外层存在NULL值时表现会更反直觉NULL IN (子查询)既不返回TRUE也不返回FALSE最后这个行就被过滤掉了。EXISTS不一样它只关心子查询是否返回了行完全不关心那一行里具体值是不是NULL。所以严格来说把IN改写成EXISTS必须在确认子查询列不存在NULL或者业务上根本不关心NULL边界时才安全。JOIN就没有NULL语义问题吗也有。内连接会把NULL的匹配行直接丢弃用LEFT JOIN则可能把NULL行带出来而且如果子查询结果有重复JOIN结果行数会成倍增加。三种写法里JOIN是最需要人工约束的一种。我把选型经验整理成了下面这个表后续处理子查询时可以直接对照场景推荐写法原因子查询返回行数少、外层大表EXISTS/IN优化器可走半连接用小表驱动子查询需要去重后关联JOIN 子查询内DISTINCT或GROUP BY避免结果膨胀子查询对同一外表有多个关联条件JOIN比多个EXISTS更清晰易优化子查询列存在NULL谨慎用IN可改EXISTS但需确认业务语义目标是取大表分页后的前N条JOIN或预分组减少外层逐行扫描2.4 选型结论从纯达梦优化器的角度IN和EXISTS并没有绝对的谁快谁慢关键看子查询能不能被展开成半连接。如果优化器最终选择把子查询物化后再哈希连接两种写法也都能通吃。最怕的是子查询里有非等值关联条件、OR条件、或者自定义函数这些会让优化器放弃半连接退回逐行执行。遇到这类场景不要纠结IN还是EXISTS优先考虑手动拆成临时表或公共表表达式把复杂条件提前算好。3. 标量子查询耗时不长但量大的隐形杀手3.1 一个典型使用场景标量子查询就是出现在SELECT列表里的子查询形如SELECT o.ORDER_NO, o.AMOUNT, (SELECT MAX(p.PRICE) FROM T_PRODUCT_PRICE p WHERE p.PRODUCT_ID o.PRODUCT_ID) AS LATEST_PRICE FROM T_ORDER o;这类SQL在报表类业务里极其常见逻辑上确实好写但它有一个天然隐患子查询要跟外层每一行做一次关联计算。假定订单表T_ORDER有50万行子查询每次需要读取一次价格表。如果价格表上PRODUCT_ID的索引设计得不好每次子查询都会把整张价格表扫一遍——那就是50万次全表扫描数据量一大接口直接超时。3.2 为什么不能完全依赖优化器有人会问标量子查询也是子查询优化器为什么不把它展开成提前聚合一次答案是优化器可以展开一部分但展开需要满足严格的等价条件。如果子查询里引用的外层列和内部表的连接关系是一对多优化器必须保证展开后不会改变最终结果行的数量。一旦它判断不了最保守的选择就是逐行执行——因为逐行执行永远正确只是慢。另外还有一种情况更隐蔽子查询本身有排序比如取每个产品最新一条价格内部写成ROWNUM1或者ORDER BY配合FETCH FIRST。这类带排序的标量子查询展开难度更大优化器几乎一定会选择逐行执行。3.3 两种改写思路针对上面这段示例SQL我常用的改写方案有两种实测都能把执行时间从秒级压到毫秒级。思路一预聚合LEFT JOIN先把子查询里的聚合提前算出来形成一张临时结果集再跟大表做LEFT JOINWITH PRICE_AGG AS ( SELECT PRODUCT_ID, MAX(PRICE) AS LATEST_PRICE FROM T_PRODUCT_PRICE GROUP BY PRODUCT_ID ) SELECT o.ORDER_NO, o.AMOUNT, p.LATEST_PRICE FROM T_ORDER o LEFT JOIN PRICE_AGG p ON p.PRODUCT_ID o.PRODUCT_ID;这样做的好处是聚合只做一次而不是跟着外层行数重复做。代价是公共表表达式可能会物化如果PRICE_AGG结果集巨大需要同步检视物化内存和临时表空间。好在大多数产品价格这类维度表聚合后行数可控收益远远大于代价。思路二窗口函数如果子查询要的是每个分组里按条件排序后的某一条窗口函数通常比预聚合更优雅SELECT ORDER_NO, AMOUNT, LATEST_PRICE FROM ( SELECT o.ORDER_NO, o.AMOUNT, p.PRICE AS LATEST_PRICE, ROW_NUMBER() OVER(PARTITION BY o.PRODUCT_ID ORDER BY p.SNAPSHOT_TIME DESC) AS RN FROM T_ORDER o LEFT JOIN T_PRODUCT_PRICE p ON p.PRODUCT_ID o.PRODUCT_ID ) WHERE RN 1;达梦8的窗口函数已经比较完善ROW_NUMBER、RANK、SUM OVER这些都可以直接用。窗口函数让优化器一次性读取所有参与计算的行内部做一次排序或分区计算避免了子查询反复执行。实操提醒无论用哪种改写改写后用EXPLAIN比较一下扫描次数和排序次数这两个指标。如果原先计划里子查询子树出现在一个循环体内改写后子查询子树消失、变成一次聚合或窗口排序基本就说明优化对了。4. 关联子查询改写JOIN的边界能改、不能改、改错4.1 改写前的语义契约关联子查询指子查询的WHERE条件里引用了外层表的列比如SELECT * FROM T_ORDER o WHERE o.AMOUNT ( SELECT AVG(p.AMOUNT) FROM T_ORDER p WHERE p.USER_ID o.USER_ID );这条SQL的意思是找出订单金额高于自己所属用户平均订单金额的那些订单。很多优化建议会告诉你把它改成JOIN分组后的结果性能更好。这句话方向没问题但有一个前提改写前后结果集必须严格一致。改写成JOIN之前我建议先回答三个问题子查询是否保证每一行外层数据至多匹配一行内层数据如果子查询返回多行原SQL本身在达梦里会报单行子查询返回多行的错误改写时必须用聚合保证唯一。如果外层是LEFT JOIN子查询结果为NULL时原SQL的比较结果是什么NULL比较会让条件不成立外层行会被过滤掉改写时要用COALESCE或WHERE条件显式处理。子查询内部的聚合粒度与外层关联键是否完全一致差一个字段都会导致结果漂移。4.2 复杂聚合场景的改写示范上面那个订单金额高于用户平均值的SQL标准改写是SELECT o.* FROM T_ORDER o JOIN ( SELECT USER_ID, AVG(AMOUNT) AS AVG_AMOUNT FROM T_ORDER GROUP BY USER_ID ) a ON a.USER_ID o.USER_ID WHERE o.AMOUNT a.AVG_AMOUNT;注意这里我使用内连接而不是LEFT JOIN。原因是原SQL里如果用户没有任何订单那么AVG子查询返回NULLo.AMOUNT NULL在SQL里是UNKNOWN这个订单会被过滤掉所以内连接结果与原SQL一致。如果改用LEFT JOIN那些没有子查询匹配行、同时又满足AMOUNT COALESCE(NULL, 0)条件的数据会被错误保留结果就错了。这个例子很能说明问题改写的核心难点不是语法而是NULL和聚合粒度的语义对齐。4.3 什么时候硬改会变慢虽然JOIN改写大多数时候能提速但我必须诚实地说有些场景硬改JOIN反而更慢。第一种是子查询过滤性极强外层只有极少数行能命中。比如找出最近10分钟内下单且用户是VIP的前10个订单子查询会先精确筛选出一个很小的集合。此时优化器如果选择哈希连接代价是至少扫描一遍完整的外层大表而原来的关联子查询配合USER_ID索引可能只驱动少量行就完成计算。这种情况下保留关联子查询把精力放在内层索引上效果反而更好。第二种是子查询内部有很重的聚合并且聚合结果行数逼近外层行数。预聚合的成本可能比外层反复走索引还高尤其当外层本身很小、内层表巨大时。所以我的原则是改写前先估算两侧结果集规模再做决定。关联子查询慢不代表JOIN就一定快关键是让优化器拿到准确的规模信息。估算方法很简单可以用EXPLAIN看计划里估算行数也可以手动跑一下两个简单COUNT查询。5. 在DM8里快速定位子查询慢SQL的排查链路5.1 从全局监控到单条SQL真正到了生产环境你不会一开始就盯着一两条SQL而是要先把哪些SQL最消耗资源筛出来。达梦8的动态性能视图可以查看SQL执行历史结合执行次数、耗时、物理读等字段排序能快速锁定Top SQL。不同小版本视图命名可能略有差异可以通过达梦官方文档确认当前实例可用的视图名。筛出嫌疑SQL后我习惯先看三个维度执行次数 × 单次耗时识别累计耗时最高的SQL计划是否频繁变更同一SQL如果有时快有时慢多半是统计信息波动导致计划不稳定是否与定时任务或批量导入时段重合如果慢SQL集中在导入时段问题可能不是子查询本体而是表锁竞争。5.2 EXPLAIN的阅读顺序与关注算子拿到SQL后在达梦管理工具里选中SQL按执行计划按钮或使用EXPLAIN语句然后按从右往左、从下往上的顺序读计划树。我的关注点按优先级排列有没有NEST LOOP且内层带有SLCT/FILTER并引用外层列有没有MATERIALIZE或临时表算子如果有它物化的是哪一层有没有HASH JOIN但左右两侧行数估算明显失真比如估算几千行实际跑几百万行有没有SORT算子在子查询内部出现导致每次循环都要排序。如果第1条命中基本可以断定子查询在逐行执行。此时进一步看外层行的规模外层驱动行数是多少内层每次扫描走的是索引还是全表。对应优化手段分别是改写为半连接/JOIN和补索引。5.3 两个必要的检查点排查子查询慢SQL除了计划我每次都会顺手检查两件事80%的情况下都能发现额外问题第一相关列的索引是否真的能被使用。子查询里的关联列必须有合适的索引且索引列顺序要跟过滤条件匹配。比如WHERE USER_ID ? AND CREATE_TIME ?建(USER_ID, CREATE_TIME)这个顺序的联合索引才有用反过来的(CREATE_TIME, USER_ID)在等值范围混合条件下效果差很多。第二两表统计信息是否陈旧。重新收集统计信息后再看计划有时候什么都不用改问题就消失。实操中我会强制刷新相关表的统计信息然后重新EXPLAIN对比确认计划原地改善还是保持不变。注意达梦的统计信息和部分系统视图在权限控制上比普通开发库更严格使用前确认当前账号有对应的查询或执行权限否则排查会卡在第一步。6. 一个500万行订单表的子查询优化全程复盘6.1 原始SQL与症状最后用一个我印象很深的真实案例来收尾。业务方反馈一个订单查询页面越来越慢简单接口从原本的几百毫秒退化到5秒以上数据库CPU经常飙高。定位到的原始SQL大致是这样的SELECT * FROM T_ORDER o WHERE o.USER_ID IN ( SELECT u.ID FROM T_USER u WHERE u.LEVEL 1 ) AND o.CREATE_TIME DATE 2024-01-01 ORDER BY o.CREATE_TIME DESC LIMIT 20;当时从执行计划里看到的形态是先对T_ORDER做全表范围扫描然后对每一行执行子查询匹配子查询内部虽然走T_USER主键但外层驱动行数高达500万累计时间非常可观。6.2 逐层拆解第一个疑点为什么优化器选择了大表驱动小表这里的关键在于o.CREATE_TIME DATE 2024-01-01这个条件。当时这个条件下实际约350万行选择性并不高优化器会认为订单表过滤后仍有大量数据加上排序需求它倾向于以大表为驱动。第二个疑点IN子查询返回的VIP用户集合有多少行一查LEVEL1的用户只有几千行。明明子查询结果很小优化器却没有把小集合驱动大集合这个思路贯彻到底。6.3 改写与索引调整我做了两件事效果立竿见影。第一件事改写为EXISTS语义并改用JOIN预聚合。既然子查询结果集很小就手动让它成为驱动端SELECT o.* FROM T_ORDER o JOIN ( SELECT ID FROM T_USER WHERE LEVEL 1 ) u ON u.ID o.USER_ID WHERE o.CREATE_TIME DATE 2024-01-01 ORDER BY o.CREATE_TIME DESC LIMIT 20;这里子查询只取ID字段结果集几千行优化器可以把它作为哈希连接的左侧输入先算出一个很小的哈希表再来扫描订单表上350万行时每条只做一次哈希探测性能自然好了。第二件事索引调整。原有索引是T_ORDER(CREATE_TIME)只覆盖了时间过滤。新的高频访问模式是按用户ID 创建时间联合过滤和排序我补了一个联合索引T_ORDER(USER_ID, CREATE_TIME DESC)。这样即便是用户维度的快速入口也能在索引内完成排序避免排序算子。6.4 多轮验证改完之后我没有直接上线而是在测试库做了三步验证结果集核对改写前后查询结果完全一致重点核对了USER_ID为NULL的订单没有被错误过滤执行计划复检确认计划已经从大表驱动 逐行子查询变成小表哈希驱动 索引范围扫描压测对比用并发10线程跑20轮平均执行时间从5.1秒降到0.3秒数据库CPU占有率明显下降。6.5 复盘心得这个案例最值得记的不是SQL语法本身而是三个判断点第一子查询结果集只有几千行但优化器一开始完全没利用这个事实说明统计信息或代价模型给出的估算不可靠第二IN改JOIN不是盲目的这里子查询本身按ID聚合天然唯一不需要额外去重所以JOIN不会引起结果膨胀第三光有索引不够索引列顺序和排序需求要一起设计好否则ORDER BY还是会触发一次显式排序。现在回想子查询优化在达梦里就是一个优化器意图识别的过程。你写的SQL只是给优化器一个初始形状最终跑多快取决于优化器能不能把这个形状折叠成一个开销更小的计划。我们做优化的人能做的就是尽可能给出清晰的表达、准确的统计信息、合理的索引再在执行计划里确认优化器确实走对了路。如果你在自己的达梦库上遇到类似现象建议先按第5章那条排查链路走一遍先看计划里有没有逐行执行的算子再查统计信息最后才动手改写。很多时候问题并不在子查询本身而在于优化器基于错误信息做出的错误选择。