先说一个让我头大的SQL上个月帮一个客户看性能问题。他们的系统跑了好几年最近数据量上来了有个报表页面打开要等四十多秒。我拿过来一看SQL大概长这样我简化过但结构没变select a.region_name, (select sum(sale_amount) from orders where orders.region_id a.region_id) as total_sale, (select count(*) from orders where orders.region_id a.region_id) as order_cnt, (select avg(sale_amount) from orders where orders.region_id a.region_id) as avg_sale from regions a;region表三百多行orders表两百多万行。按说不大啊怎么会这么慢我手工跑了一下explain analyze一看差点没把水喷屏幕上orders表被扫了三次每次都是全表扫描而且每扫描一次就对应region表的每一行不对仔细看执行计划——它是对region表的每一行分别执行orders的扫描。三百多行每行扫一次两百多万行的表那不就是三亿多次的扫描么。三个子查询就是三倍。我当时脑子里只有一个词离谱。为什么数据库会这么傻后来我翻了一些资料包括金仓数据库V009R002C014版本的优化器文档才明白这不是“傻”是历史上就这么设计的。传统优化器的逻辑很简单父查询先跑拿到一行结果然后把这行结果的值代入子查询执行一遍子查询。子查询返回一个值贴到结果上。然后父查询下一行重复。这就是所谓的“相关子查询”。它跟主查询的当前行相关比如region_id等于当前行的region_id。这种机制在逻辑上完全正确但在执行上就是嵌套循环。如果主查询返回N行子查询就执行N次。如果子查询里还做了聚合那聚合的代价还要再乘N。更坑的是如果你SELECT后面挂了三个标量子查询而且它们长得还很像比如上面那个例子都是查orders只是聚合函数不同数据库会老老实实分别执行三个子查询——也就说orders表被扫描了N×3次。N300orders200万那就是300×3900次全表扫描等等不对应该是300行×3个子查询900次orders表扫描也不对每个子查询在orders上的扫描是独立的但因为是全表扫描每次扫描都要读200万行。300×200万×3 18亿行的读取量。这能快才怪。我第一次手动改改出了bug客户催得紧我寻思自己手动把这SQL改了吧。用left join group by把三个子查询合并成一个内联视图。select a.region_name, coalesce(agg.total_sale, 0), coalesce(agg.order_cnt, 0), agg.avg_sale from regions a left join ( select region_id, sum(sale_amount) as total_sale, count(*) as order_cnt, avg(sale_amount) as avg_sale from orders group by region_id ) agg on a.region_id agg.region_id;自我感觉良好。结果客户第二天说“数字不对了”。我一看原来有个region在orders里完全没有记录。原始查询里三个子查询返回的是(null, 0, null) —— 第二个是count没数据时应该返回0。我改完之后left join没匹配上agg的三列全是null。我把total_sale和avg_sale用coalesce转成0了但count(*)本来就是0啊不对我coalesce(agg.order_cnt,0)那没问题。问题是avg_sale——原始返回null我coalesce后变成0了错了。还有total_sale原始也是null我变成了0也错了。所以不能这么简单粗暴地用coalesce。正确的做法是只有count类的聚合才转成0其他保持null。我得手工判断聚合类型再分别处理太麻烦了。后来我看了金仓优化器的做法才知道他们有一套“等价性判定”机制专门处理这种边界。不是说所有标量子查询都能消得先判断子查询返回类型是什么聚合函数是哪种有没有可能没匹配记录如果有消除后语义怎么保持比如说count消除后要用case when判断右表有没有匹配行没匹配就显式返回0。这不是一个简单的规则更像是一套逻辑推理。优化器先分析子查询的抽象语法树提取特征再匹配安全的变换模式。不安全的宁可不解。什么时候消除反而更差还有一个事让我纠结了很久是不是所有的标量子查询消除都能提升性能答案是否定的。考虑一个场景主表很小只有100行。子查询查的是一个非常大的表比如10亿行但子查询的连接条件用的是主表的主键而且大表上有一个唯一索引。如果消除子查询优化器可能会选择hash join。hash join需要先把大表全部读一遍或者至少扫描索引来构建哈希表代价是10亿行的读取。如果老老实实用嵌套循环对主表的每一行通过唯一索引去大表里取一条数据100次索引探针可能只需要几百个IO。这比扫10亿行快得多。金仓的优化器在做子查询消除之前会先问几个问题主表估算有多少行子查询的内表有多大有没有能用的索引消除后大概率走什么连接方式这些加起来算出一个总代价再和不消除的代价比一比。只有消除后代价更小才会真的去消除。这就是CBO基于代价的优化。不是见到就消而是“看情况”。我特意写了个测试来验证-- 小表驱动大表 唯一索引的场景 create table small_table (id int primary key, name text); insert into small_table select generate_series(1,100), test; create table big_table (id int primary key, value int); insert into big_table select generate_series(1,100000000), random()*1000; -- 这个标量子查询有唯一索引支持 explain analyze select (select value from big_table where big_table.id small_table.id) from small_table;我手动关掉标量子查询消除开关假设有和不关对比发现消除后反而慢了因为优化器选了hash join扫描了整张大表。而不消除时走了nestloop 索引扫描快了几十倍。所以一个聪明的优化器必须知道什么时候该出手什么时候该收手。在通过等价性校验后会进入子查询优化阶段对查询的目标列中存在的相关标量子查询进行处理将目标列的标量子查询转换为内联视图并和外部相关表进行左外连接从而应用后续的优化策略提查询性能。这一步解决的是“如何进行子查询消除”详细工作流程如下相似子查询合并这个是真的香说回客户那个SQL。三个子查询结构几乎一样只是聚合函数不同。金仓优化器有个功能叫“相似子查询合并”它会识别出这种模式自动改写成上面那种left join group by的形式但注意——它不会像我那样瞎用coalesce它会根据聚合函数类型精确地处理空值语义。合并后的执行计划orders只扫了一次或者一次索引扫描然后分组聚合计算出三个值再和regions表做左连接。我拿客户的数据量测了一下优化前40多秒优化后不到200毫秒。这个提升太直观了。金仓V009R002C014里到底做了什么我翻了一下他们的设计文档总结起来就是三步第一步能不能消优化器先检查子查询是不是“绝对安全”。比如子查询是不是真的只返回一行有没有聚合聚合函数是哪种有没有可能没匹配记录没匹配时原始语义是什么这些问题都回答清楚了才能进入下一步。这一步相当于“语义等价性判定”。第二步怎么消安全的子查询转成左连接内联视图的形式。这里有个细节count类型的聚合在生成左连接结果时要用case when判断右表有没有匹配行没匹配就返回0其他聚合函数返回null。第三步多个相似子查询合并成一个。这一步需要模式识别能力。优化器会看每个子查询的“签名”——涉及哪些表、连接条件是什么、过滤条件是什么、分组键是什么。签名相同就合并。代码案例集锦我把我测过的几个典型场景贴出来你们可以自己试试。案例1最简单的单子查询消除效果最明显-- 准备 drop table if exists t1, t2; create table t1(id int); create table t2(id int); insert into t1 select generate_series(1,10000); insert into t2 select generate_series(1,10000); -- 测试假设优化器开关可控制实际要看具体版本 explain analyze select (select sum(id) from t2 where t2.id t1.id) from t1; -- 消除前对t1的每行执行子查询t2被扫10000次耗时约30-40秒 -- 消除后t1和t2做一次hash join耗时20-30毫秒测试结果子查询未消除对 t1 的每一条记录都要对 t2 进行一次全表扫描。所以需要对 t2 表扫描 1 万次耗时 32 秒子查询消除后对表 t2 只需要执行一次扫描总执行时间约 24 毫秒性能提升数量级明显。案例2相似子查询合并三个变一个-- 和客户那个类似 select d.dname, (select count(*) from emp where emp.deptno dept.deptno) as cnt, (select sum(sal) from emp where emp.deptno dept.deptno) as total_sal, (select avg(sal) from emp where emp.deptno dept.deptno) as avg_sal from dept; -- 优化器自动合并为类似这样内部表示 /* select d.dname, agg.cnt, agg.total_sal, agg.avg_sal from dept d left join ( select deptno, count(*) cnt, sum(sal) total_sal, avg(sal) avg_sal from emp group by deptno ) agg on d.deptno agg.deptno; */ -- 效果emp表只扫描一次案例3不能消除的例子——子查询可能返回多行-- 这个子查询没有聚合也没有保证唯一性的条件标量子查询本身就会报错 select (select name from users where users.city cities.id) from cities; -- 优化器在等价性判定阶段会识别出这个风险拒绝消除。 -- 因为消除后变成left join不会报错但会返回重复行语义不一致。在实际的业务系统中SQL 往往会非常复杂。随着业务复杂度的提升CTE、多层子查询、窗口函数、聚集计算被大量用于组织逻辑。然而这类 SQL 在带来可读性的同时也给查询优化器带来了巨大的挑战尤其是 SQL 中存在多个复杂的子查询的场景下如果数据库不能智能的处理性能问题会显得尤为突出。总结几句标量子查询消除听起来就是个小优化但真做起来要考虑的东西一大堆语义对不对、空值怎么处理、什么时候该消什么时候不该消、多个相似的能不能合并。这些都不是简单的if-else能搞定的需要优化器有“推理”能力。金仓V009R002C014这个版本给我的感觉是他们不是在做规则的堆砌而是在搭一个能思考的框架。虽然还没到AI那个程度但思路已经往那个方向走了。最后说句实在的如果你的SQL里有很多标量子查询先别急着手动改看看数据库的版本支不支持自动消除。支持的话交给优化器去判断就行。但如果你用的是老版本那还是得像我一样手工改只是记得处理好null和0的区别别踩坑。