一、问题是怎么来的写 SQL 写多了的人大概都有这种感觉——业务逻辑一复杂SELECT后面就跟了一堆子查询。每个子查询就返回一个值看着清楚、写着方便自己都觉得这 SQL 写得挺利索。可数据库引擎看到的完全是另一回事。1.1 先看一个真实场景比方说你手上有两张表客户表和订单表。老板要一份报表每位客户的累计消费多少、最后一次下单是什么时候。你顺手就写了这么一条SELECT(SELECTSUM(amount)FROMorders oWHEREo.customer_idc.id)AStotal_amount,(SELECTMAX(order_date)FROMorders oWHEREo.customer_idc.id)ASlast_orderFROMcustomers c;意思很明白——每个客户去订单表里算一笔总账再找一下最近下单时间。但你有没有注意到SELECT后面那两个子查询干的事儿几乎一模一样都是拿customer_id去订单表里捞数据就是一个取SUM、一个取MAX。写法没毛病结果也对。可问题就出在数据库怎么跑这条 SQL 上。1.2 逐行执行到底有多慢传统优化器拿到这条 SQL干的事情是这样的先把customers表从头到尾扫一遍拿到第一行跑一遍子查询拿到第二行再跑一遍拿到第三行又跑一遍……有两个子查询那就每个都这么来一轮。算一笔账客户表 10 万行两个子查询各跑 10 万次加起来 20 万次。问题是这 20 万次查的根本就是同一张表、用的同一个条件就输出列不一样——纯粹在做重复劳动。这种执行方式有个专门的叫法Row-by-row逐行处理。数据量小的时候感觉不出来等数据涨起来了开销跟着直线上升。更要命的是现在的数据库内核基本都用上了向量化执行引擎——这东西的设计思路是利用 CPU 的 SIMD 指令一批一批地处理数据。但标量子查询非得一行一行来等于硬把引擎拽回了最原始的工作方式现代硬件的并行能力全白瞎了。二、改成 JOIN 就行了没那么简单很多人第一反应都是子查询慢那就改成JOIN呗这有什么难的方向没问题。但真上手你就会发现这事儿有两道坎不好过。说到底就四个字的事儿语义等价。意思是改完之后的 SQL不管什么数据、什么边界条件结果必须跟原来一模一样差一行都不行。2.1 第一道坎多行返回——一个报错一个不报错标量子查询有个硬性规矩只能返回一行一列。要是跑出来多行了数据库直接给你甩个错误-- 假设某个客户有好几条订单记录-- 执行结果ERROR: more than one row returned by a subquery used as an expressionSELECT(SELECTnameFROMordersWHEREcustomer_idc.id)FROMcustomers c;但你要是把它改成LEFT JOIN-- 改成 JOIN 之后不报错了结果变成多行SELECTo.nameFROMcustomers cLEFTJOINorders oONo.customer_idc.id;看出来没同样的数据一种写法报错另一种写法悄悄多出好几行——语义根本不是一回事。优化器要是不管三七二十一就改写该报错的查询反而正常跑完了只是结果不对。这在生产环境里可是大忌。2.2 第二道坎COUNT 的小脾气COUNT跟SUM、MAX它们碰到没匹配到数据时的反应不一样。看个例子就懂了-- 假设订单表里压根没有 customer_id 999 的记录SELECT(SELECTCOUNT(*)FROMordersWHEREcustomer_id999)AScnt,(SELECTSUM(amount)FROMordersWHEREcustomer_id999)AStotal;跑出来的结果长这样cnttotal0NULL区别在这COUNT没匹配到时老老实实给你一个0而SUM返回的是NULL。那问题来了——优化器要是把子查询消掉改成LEFT JOIN右表没匹配上时就统一补NULL。SUM本来就该返回 NULL没毛病。但COUNT呢原来好好的一个 0现在变成 NULL 了这不就改错了吗所以结论很干脆不是所有标量子查询都能动得先老老实实做等价性判定通过检查的才能消除。三、三步走——怎么安全地把子查询干掉前面搞清楚了哪些能改、哪些不能改接下来的流程就顺畅了。一共三步。第一步能不能改先做等价性判定这一步的原则就一个宁可放过不可改错。优化器对每个标量子查询逐个体检三项检查一项不通过就直接跳过// 标量子查询消除 —— 等价性判定核心逻辑伪代码boolis_safe_to_eliminate(SubQuery*sq){// 检查一UNION、窗口函数、嵌套子查询// 这些太复杂别碰if(has_union(sq)||has_window_func(sq)||has_nested_subquery(sq))returnfalse;// 检查二能不能保证至多返回一行// SELECT 列全是聚合函数才行或者 GROUP BY 有唯一性保证if(!guarantees_single_row(sq))returnfalse;// 检查三有没有 COUNT(*)// 这个函数无匹配时返回 0LEFT JOIN 补 NULL 就不对了if(contains_count_star(sq)!has_group_by(sq))returnfalse;// 三项全过安全可以消除returntrue;}逻辑不复杂三项检查保底安全第一。第二步怎么改——子查询变成 LEFT JOIN过了检查的子查询接下来就动手改把它转成一个内联视图再跟外部查询做左外连接。还是拿前面那个客户订单的例子来说-- 原始 SQL SELECT(SELECTSUM(amount)FROMorders oWHEREo.customer_idc.id)AStotal_amount,(SELECTMAX(order_date)FROMorders oWHEREo.customer_idc.id)ASlast_orderFROMcustomers c;-- 改写后 SELECTv1.total_amount,v2.last_orderFROMcustomers cLEFTJOIN(SELECTcustomer_id,SUM(amount)AStotal_amountFROMordersGROUPBYcustomer_id)v1ONv1.customer_idc.idLEFTJOIN(SELECTcustomer_id,MAX(order_date)ASlast_orderFROMordersGROUPBYcustomer_id)v2ONv2.customer_idc.id;前后对比一眼就能看出差别对比项改写前改写后怎么执行每行触发一次子查询一次性 JOINorders 扫几次N 次N customers 行数2 次向量化引擎能用吗不行逐行调用的行批量处理第三步还能不能更快——把相似的子查询合并再看看第二步的结果——两个LEFT JOIN查的还是同一张orders表关联条件一模一样那干嘛要扫两遍合一起不就得了-- 合并后的最终形态 SELECTv.total_amount,v.last_orderFROMcustomers cLEFTJOIN(SELECTcustomer_id,SUM(amount)AStotal_amount,MAX(order_date)ASlast_orderFROMordersGROUPBYcustomer_id)vONv.customer_idc.id;这一下orders表只扫一遍SUM和MAX在同一次聚合里一起算完。从一行一行磨变成一把梭——这才叫集合处理。整个流程用伪代码串起来就是这样的// 标量子查询消除 —— 完整流程伪代码voideliminate_scalar_subqueries(Query*query){List*subqueriescollect_target_subqueries(query);// 第一步逐个体检判定能不能安全消除foreach(sq,subqueries){if(!is_safe_to_eliminate(sq))mark_unremovable(sq);}// 第二步通过检查的改写为 LEFT JOINforeach(sq,subqueries){if(is_removable(sq))rewrite_to_left_join(query,sq);}// 第三步长得像的合并成一个merge_similar_subqueries(query);}四、这事儿的意义不止于改个写法到这儿你可能觉得标量子查询消除不就是 SQL 层面换个写法嘛——子查询变JOIN完了。其实真不是。这项优化真正厉害的地方在于它把查询计划调成了现代硬件最喜欢的样子。4.1 向量化引擎就爱成批成批地吃数据老一代数据库用的是火山模型一行一行往上吐数据吐一行调一次函数。向量化引擎不这么干它一次吞一批Batch几百上千行打包处理函数调用次数直接砍到底。但标量子查询天然就是逐行的——来一行跑一次子查询引擎根本攒不出 Batch。消除以后变成JOIN操作数据成批成批地流过去向量化引擎才算真正派上了用场。拿代码对比一下更直观// 老办法火山模型一行来一次Tuplenext_tuple(){Tuple tchild-next();// 取一行returneval_subquery(t);// 跑一次子查询}// 新办法向量化模型一批来一次Batchnext_batch(){Batch bchild-next_batch();// 一次取一批比如 1024 行returneval_join_batch(b);// 一把梭整批 JOIN 全算完}4.2 SIMD——一条指令干八份活向量化引擎能跑得快底层靠的是 CPU 的SIMD 指令集。原理一句话说清一条指令同时对付好几个数据。比如 AVX2 指令一条就能同时给 8 个整数做加法#includeimmintrin.h// 用 SIMD 给两批数据做加法voidsimd_sum_batch(int*a,int*b,int*result,intcount){inti0;for(;i8count;i8){__m256i va_mm256_loadu_si256((__m256i*)(ai));// 一次装 8 个数__m256i vb_mm256_loadu_si256((__m256i*)(bi));// 再装 8 个数__m256i vr_mm256_add_epi32(va,vb);// 一条指令8 组加法同时搞定_mm256_storeu_si256((__m256i*)(resulti),vr);// 存回去}// 剩下凑不够 8 个的老老实实逐个算for(;icount;i)result[i]a[i]b[i];}子查询消除之后SUM、MAX、MIN这些聚合操作就可以在向量化引擎里用 SIMD 并行跑了。100 万行数据理论上只要 12.5 万次 SIMD 运算就能搞定100 万 ÷ 8比逐行快了将近一个数量级。所以你看标量子查询消除看起来只是 SQL 变了个花样实际上它是在帮查询计划铺一条能跑在 SIMD 快车道上的路。这才是这项优化的核心价值。五、实际跑一把——数据说话空口说白话没意思直接上测试-- 准备两张表各塞 10000 行数据CREATETABLEt1(idNUMERIC(10,1));CREATETABLEt2(idNUMERIC(10,1));INSERTINTOt1VALUES(generate_series(1,10000));INSERTINTOt2VALUES(generate_series(2,10000));-- 跑一条带标量子查询的 SQLSELECT(SELECTSUM(id)FROMt2WHEREt1.idt2.id)FROMt1;测试结果怎么跑的t2 表扫了几次花了多久子查询没消除10,000 次每行扫一遍32 秒子查询消除后1 次改写成 JOIN24 毫秒32 秒变 24 毫秒差了 1300 多倍。为啥差这么多没消除的时候t1每来一行都要跑一遍SELECT SUM(id) FROM t2 WHERE ...等于把t2从头到尾扫一遍。1 万行乘 1 万行光是比较操作就 1 亿次。消除之后优化器把它改成了一次LEFT JOINGROUP BYt2只扫一次配上哈希聚合直接出结果。六、收个尾标量子查询消除说到底就干了两件事砍掉重复执行。原来一行一跑的子查询改成只跑一次 JOIN冗余计算从根上就没了。合并相似结构。好几个子查询查同一张表、用同一个条件合成一个内联视图一遍扫描把所有聚合都算完。再往深了说这背后体现的是查询优化器一直遵循的原则语义不能变但执行计划越便宜越好。这项优化不仅解决了子查询跑得慢这个表层问题更关键的是把查询计划调成了向量化引擎和 SIMD 喜欢的形态让现代 CPU 的并行能力真正有了用武之地。对每天写 SQL 的开发同学和 DBA 来说好处很实在你该怎么写还怎么写用最顺手、最好读的方式组织 SQL 就行。优化器在底下默默帮你把逐行处理变成集合处理你不用操心。写法优雅和跑得快这俩不矛盾。
你的 SQL 里藏着多少重复计算？金仓数据库标量子查询消除实战解析
一、问题是怎么来的写 SQL 写多了的人大概都有这种感觉——业务逻辑一复杂SELECT后面就跟了一堆子查询。每个子查询就返回一个值看着清楚、写着方便自己都觉得这 SQL 写得挺利索。可数据库引擎看到的完全是另一回事。1.1 先看一个真实场景比方说你手上有两张表客户表和订单表。老板要一份报表每位客户的累计消费多少、最后一次下单是什么时候。你顺手就写了这么一条SELECT(SELECTSUM(amount)FROMorders oWHEREo.customer_idc.id)AStotal_amount,(SELECTMAX(order_date)FROMorders oWHEREo.customer_idc.id)ASlast_orderFROMcustomers c;意思很明白——每个客户去订单表里算一笔总账再找一下最近下单时间。但你有没有注意到SELECT后面那两个子查询干的事儿几乎一模一样都是拿customer_id去订单表里捞数据就是一个取SUM、一个取MAX。写法没毛病结果也对。可问题就出在数据库怎么跑这条 SQL 上。1.2 逐行执行到底有多慢传统优化器拿到这条 SQL干的事情是这样的先把customers表从头到尾扫一遍拿到第一行跑一遍子查询拿到第二行再跑一遍拿到第三行又跑一遍……有两个子查询那就每个都这么来一轮。算一笔账客户表 10 万行两个子查询各跑 10 万次加起来 20 万次。问题是这 20 万次查的根本就是同一张表、用的同一个条件就输出列不一样——纯粹在做重复劳动。这种执行方式有个专门的叫法Row-by-row逐行处理。数据量小的时候感觉不出来等数据涨起来了开销跟着直线上升。更要命的是现在的数据库内核基本都用上了向量化执行引擎——这东西的设计思路是利用 CPU 的 SIMD 指令一批一批地处理数据。但标量子查询非得一行一行来等于硬把引擎拽回了最原始的工作方式现代硬件的并行能力全白瞎了。二、改成 JOIN 就行了没那么简单很多人第一反应都是子查询慢那就改成JOIN呗这有什么难的方向没问题。但真上手你就会发现这事儿有两道坎不好过。说到底就四个字的事儿语义等价。意思是改完之后的 SQL不管什么数据、什么边界条件结果必须跟原来一模一样差一行都不行。2.1 第一道坎多行返回——一个报错一个不报错标量子查询有个硬性规矩只能返回一行一列。要是跑出来多行了数据库直接给你甩个错误-- 假设某个客户有好几条订单记录-- 执行结果ERROR: more than one row returned by a subquery used as an expressionSELECT(SELECTnameFROMordersWHEREcustomer_idc.id)FROMcustomers c;但你要是把它改成LEFT JOIN-- 改成 JOIN 之后不报错了结果变成多行SELECTo.nameFROMcustomers cLEFTJOINorders oONo.customer_idc.id;看出来没同样的数据一种写法报错另一种写法悄悄多出好几行——语义根本不是一回事。优化器要是不管三七二十一就改写该报错的查询反而正常跑完了只是结果不对。这在生产环境里可是大忌。2.2 第二道坎COUNT 的小脾气COUNT跟SUM、MAX它们碰到没匹配到数据时的反应不一样。看个例子就懂了-- 假设订单表里压根没有 customer_id 999 的记录SELECT(SELECTCOUNT(*)FROMordersWHEREcustomer_id999)AScnt,(SELECTSUM(amount)FROMordersWHEREcustomer_id999)AStotal;跑出来的结果长这样cnttotal0NULL区别在这COUNT没匹配到时老老实实给你一个0而SUM返回的是NULL。那问题来了——优化器要是把子查询消掉改成LEFT JOIN右表没匹配上时就统一补NULL。SUM本来就该返回 NULL没毛病。但COUNT呢原来好好的一个 0现在变成 NULL 了这不就改错了吗所以结论很干脆不是所有标量子查询都能动得先老老实实做等价性判定通过检查的才能消除。三、三步走——怎么安全地把子查询干掉前面搞清楚了哪些能改、哪些不能改接下来的流程就顺畅了。一共三步。第一步能不能改先做等价性判定这一步的原则就一个宁可放过不可改错。优化器对每个标量子查询逐个体检三项检查一项不通过就直接跳过// 标量子查询消除 —— 等价性判定核心逻辑伪代码boolis_safe_to_eliminate(SubQuery*sq){// 检查一UNION、窗口函数、嵌套子查询// 这些太复杂别碰if(has_union(sq)||has_window_func(sq)||has_nested_subquery(sq))returnfalse;// 检查二能不能保证至多返回一行// SELECT 列全是聚合函数才行或者 GROUP BY 有唯一性保证if(!guarantees_single_row(sq))returnfalse;// 检查三有没有 COUNT(*)// 这个函数无匹配时返回 0LEFT JOIN 补 NULL 就不对了if(contains_count_star(sq)!has_group_by(sq))returnfalse;// 三项全过安全可以消除returntrue;}逻辑不复杂三项检查保底安全第一。第二步怎么改——子查询变成 LEFT JOIN过了检查的子查询接下来就动手改把它转成一个内联视图再跟外部查询做左外连接。还是拿前面那个客户订单的例子来说-- 原始 SQL SELECT(SELECTSUM(amount)FROMorders oWHEREo.customer_idc.id)AStotal_amount,(SELECTMAX(order_date)FROMorders oWHEREo.customer_idc.id)ASlast_orderFROMcustomers c;-- 改写后 SELECTv1.total_amount,v2.last_orderFROMcustomers cLEFTJOIN(SELECTcustomer_id,SUM(amount)AStotal_amountFROMordersGROUPBYcustomer_id)v1ONv1.customer_idc.idLEFTJOIN(SELECTcustomer_id,MAX(order_date)ASlast_orderFROMordersGROUPBYcustomer_id)v2ONv2.customer_idc.id;前后对比一眼就能看出差别对比项改写前改写后怎么执行每行触发一次子查询一次性 JOINorders 扫几次N 次N customers 行数2 次向量化引擎能用吗不行逐行调用的行批量处理第三步还能不能更快——把相似的子查询合并再看看第二步的结果——两个LEFT JOIN查的还是同一张orders表关联条件一模一样那干嘛要扫两遍合一起不就得了-- 合并后的最终形态 SELECTv.total_amount,v.last_orderFROMcustomers cLEFTJOIN(SELECTcustomer_id,SUM(amount)AStotal_amount,MAX(order_date)ASlast_orderFROMordersGROUPBYcustomer_id)vONv.customer_idc.id;这一下orders表只扫一遍SUM和MAX在同一次聚合里一起算完。从一行一行磨变成一把梭——这才叫集合处理。整个流程用伪代码串起来就是这样的// 标量子查询消除 —— 完整流程伪代码voideliminate_scalar_subqueries(Query*query){List*subqueriescollect_target_subqueries(query);// 第一步逐个体检判定能不能安全消除foreach(sq,subqueries){if(!is_safe_to_eliminate(sq))mark_unremovable(sq);}// 第二步通过检查的改写为 LEFT JOINforeach(sq,subqueries){if(is_removable(sq))rewrite_to_left_join(query,sq);}// 第三步长得像的合并成一个merge_similar_subqueries(query);}四、这事儿的意义不止于改个写法到这儿你可能觉得标量子查询消除不就是 SQL 层面换个写法嘛——子查询变JOIN完了。其实真不是。这项优化真正厉害的地方在于它把查询计划调成了现代硬件最喜欢的样子。4.1 向量化引擎就爱成批成批地吃数据老一代数据库用的是火山模型一行一行往上吐数据吐一行调一次函数。向量化引擎不这么干它一次吞一批Batch几百上千行打包处理函数调用次数直接砍到底。但标量子查询天然就是逐行的——来一行跑一次子查询引擎根本攒不出 Batch。消除以后变成JOIN操作数据成批成批地流过去向量化引擎才算真正派上了用场。拿代码对比一下更直观// 老办法火山模型一行来一次Tuplenext_tuple(){Tuple tchild-next();// 取一行returneval_subquery(t);// 跑一次子查询}// 新办法向量化模型一批来一次Batchnext_batch(){Batch bchild-next_batch();// 一次取一批比如 1024 行returneval_join_batch(b);// 一把梭整批 JOIN 全算完}4.2 SIMD——一条指令干八份活向量化引擎能跑得快底层靠的是 CPU 的SIMD 指令集。原理一句话说清一条指令同时对付好几个数据。比如 AVX2 指令一条就能同时给 8 个整数做加法#includeimmintrin.h// 用 SIMD 给两批数据做加法voidsimd_sum_batch(int*a,int*b,int*result,intcount){inti0;for(;i8count;i8){__m256i va_mm256_loadu_si256((__m256i*)(ai));// 一次装 8 个数__m256i vb_mm256_loadu_si256((__m256i*)(bi));// 再装 8 个数__m256i vr_mm256_add_epi32(va,vb);// 一条指令8 组加法同时搞定_mm256_storeu_si256((__m256i*)(resulti),vr);// 存回去}// 剩下凑不够 8 个的老老实实逐个算for(;icount;i)result[i]a[i]b[i];}子查询消除之后SUM、MAX、MIN这些聚合操作就可以在向量化引擎里用 SIMD 并行跑了。100 万行数据理论上只要 12.5 万次 SIMD 运算就能搞定100 万 ÷ 8比逐行快了将近一个数量级。所以你看标量子查询消除看起来只是 SQL 变了个花样实际上它是在帮查询计划铺一条能跑在 SIMD 快车道上的路。这才是这项优化的核心价值。五、实际跑一把——数据说话空口说白话没意思直接上测试-- 准备两张表各塞 10000 行数据CREATETABLEt1(idNUMERIC(10,1));CREATETABLEt2(idNUMERIC(10,1));INSERTINTOt1VALUES(generate_series(1,10000));INSERTINTOt2VALUES(generate_series(2,10000));-- 跑一条带标量子查询的 SQLSELECT(SELECTSUM(id)FROMt2WHEREt1.idt2.id)FROMt1;测试结果怎么跑的t2 表扫了几次花了多久子查询没消除10,000 次每行扫一遍32 秒子查询消除后1 次改写成 JOIN24 毫秒32 秒变 24 毫秒差了 1300 多倍。为啥差这么多没消除的时候t1每来一行都要跑一遍SELECT SUM(id) FROM t2 WHERE ...等于把t2从头到尾扫一遍。1 万行乘 1 万行光是比较操作就 1 亿次。消除之后优化器把它改成了一次LEFT JOINGROUP BYt2只扫一次配上哈希聚合直接出结果。六、收个尾标量子查询消除说到底就干了两件事砍掉重复执行。原来一行一跑的子查询改成只跑一次 JOIN冗余计算从根上就没了。合并相似结构。好几个子查询查同一张表、用同一个条件合成一个内联视图一遍扫描把所有聚合都算完。再往深了说这背后体现的是查询优化器一直遵循的原则语义不能变但执行计划越便宜越好。这项优化不仅解决了子查询跑得慢这个表层问题更关键的是把查询计划调成了向量化引擎和 SIMD 喜欢的形态让现代 CPU 的并行能力真正有了用武之地。对每天写 SQL 的开发同学和 DBA 来说好处很实在你该怎么写还怎么写用最顺手、最好读的方式组织 SQL 就行。优化器在底下默默帮你把逐行处理变成集合处理你不用操心。写法优雅和跑得快这俩不矛盾。
