ToB企服应用市场:ToB评测及商务社交产业平台

标题: 【Java开源数据库语言】基于SPL如何提高SQL优化效率 [打印本页]

作者: 用多少眼泪才能让你相信 时间: 2022-6-24 13:16
标题: 【Java开源数据库语言】基于SPL如何提高SQL优化效率
很多大数据计算都是用SQL实现的，跑得慢时就要去优化SQL，但常常碰到让人干瞪眼的情况。
比如，存储过程中有三条大概形如这样的语句执行得很慢：

select a,b,sum(x) from T group by a,b where …;
select c,d,max(y) from T group by c,d where …;
select a,c,avg(y),min(z) from T group by a,c where …;

复制代码

这里的T是个有数亿行的巨大表，要分别按三种方式分组，分组的结果集都不大。
分组运算要遍历数据表，这三句SQL就要把这个大表遍历三次，对数亿行数据遍历一次的时间就不短，何况三遍。
这种分组运算中，相对于遍历硬盘的时间，CPU计算时间几乎可以忽略。如果可以在一次遍历中把多种分组汇总都计算出来，虽然CPU计算量并没有变少，但能大幅减少硬盘读取数据量，就能成倍提速了。
如果SQL支持类似这样的语法：

from T --数据来自T表
select a,b,sum(x) group by a,b where … --遍历中的第一种分组
select c,d,max(y) group by c,d where … --遍历中的第二种分组
select a,c,avg(y),min(z) group by a,c where …; --遍历中的第三种分组

复制代码

能一次返回多个结果集，那就可以大幅提高性能了。
可惜， SQL没有这种语法，写不出这样的语句，只能用个变通的办法，就是用group a,b,c,d的写法先算出更细致的分组结果集，但要先存成一个临时表，才能进一步用SQL计算出目标结果。SQL大致如下：

create table T\_temp as select a,b,c,d,
sum(case when … then x else 0 end) sumx,
max(case when … then y else null end) maxy,
sum(case when … then y else 0 end) sumy,
count(case when … then 1 else null end) county,
min(case when … then z else null end) minz
group by a,b,c,d;
select a,b,sum(sumx) from T\_temp group by a,b where …;
select c,d,max(maxy) from T\_temp group by c,d where …;
select a,c,sum(sumy)/sum(county),min(minz) from T\_temp group by a,c where …;

复制代码

这样只要遍历一次了，但要把不同的WHERE条件转到前面的case when里，代码复杂很多，也会加大计算量。而且，计算临时表时分组字段的个数变得很多，结果集就有可能很大，最后还对这个临时表做多次遍历，计算性能也快不了。大结果集分组计算还要硬盘缓存，本身性能也很差。
还可以用存储过程的数据库游标把数据一条一条fetch出来计算，但这要全自己实现一遍WHERE和GROUP的动作了，写起来太繁琐不说，数据库游标遍历数据的性能只会更差！
只能干瞪眼！
TopN运算同样会遇到这种无奈。举个例子，用Oracle的SQL写top5大致是这样的：
[code] select \* from (select x from T order by x desc) where rownum=date(“2021-01-10”) && tdate

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)