"结构化数据的计算处理一直以 SQL 为主。不过随着技术的不断发展，现在经常为了满足扩展性的需要而使用 Java 来处理数据。这样可以显著降低数据库与应用的耦合性（数据库仅用于存储），整体架构更 .."

lisongbo
乾学院 29 号会员
1 回帖 • 2,977 浏览 • 1 年前

Java 和 SQL 做数据运算哪个更快？

计算＆AI

结构化数据的计算处理一直以 SQL 为主。不过随着技术的不断发展，现在经常为了满足扩展性的需要而使用 Java 来处理数据。这样可以显著降低数据库与应用的耦合性（数据库仅用于存储），整体架构更加灵活，应用扩展和维护都比较方便。

而在大数据时代，计算性能又是避不开的话题，一项技术如果跑得太慢会把其他所有好处都抵消掉。那么，Java 和 SQL 做数据运算哪个更快呢？

SQL

从数据处理方面来看，SQL 不仅使用更加广泛，一般在性能上也更有优势。SQL 的高性能主要源于以下两方面：

优化引擎。SQL 作为声明式语言，通常用户只需要描述需要完成的任务，而不需要关心具体的实现细节。数据库会根据 SQL 语句的描述自动优化查询计划和执行方式，从而提高查询效率，这就是优化引擎的作用。数据库对于常规运算都有很成熟的算法，很多计算在优化引擎的帮助下可以快速完成。
数据存储。计算和数据存储密不可分，而数据库集二者于一身。有了存储就可以在工程上实施很多提速手段，如索引、缓存、分区、冗余等。特别是以 AP 为主的数据仓库还可以针对计算而设计专门的存储（如列存），存储与算法相互配合就可以实现高效率。

不过，SQL 也有力有不逮的时候。由于 SQL 描述能力的局限，很多复杂查询要采取迂回的方法，写出来很繁琐。更重要的是，一旦 SQL 语句的复杂度上来，优化引擎就很难发挥作用了（猜不出目标只能按照字面表达去执行，性能很差），因此优化引擎仅对简单情况有效。而能让优化器失效的 SQL 复杂度其实很低。

另一方面，SQL 数据类型和算法不够全面，如果要用到超出范围的算法就需要自己实现，如编写自定义函数（UDF）。但当基础数据类型不支持，或需要根据计算特征设计存储时 UDF 也无能为力。

这里我们举个例子，TopN 运算时：

SELECT TOP 10 x FROM T ORDER BY x DESC

大部分数据库都会优化，不会真排序。但是改成组内 TopN 以后：

select * from
 (select y,*,row_number() over (partition by y order by x desc) rn from T)
where rn<=10

复杂度虽然没有提升很多，但优化引擎就会犯晕，猜不出这句 SQL 的真正目的，很可能按照 SQL 表达的逻辑进行大排序，而大数据排序是非常慢的动作，最后的结果就是性能很差。

SQL 由于缺乏显式的集合数据类型，数据库也不会提供把 TopN 当作聚合运算的算法，就只能寄希望于优化器足够强大，但结果却常常不尽人意。

再比如，电商计算用户流失率的漏斗分析，由于涉及多步和相互依赖的顺序，使用 SQL 要借助子查询实现：

with e1 as (
 select uid,1 as step1,min(etime) as t1
 from event
 where etime>= to_date('2021-01-10') and etime<to_date('2021-01-25')
 and eventtype='eventtype1' and …
 group by 1),
e2 as (
 select uid,1 as step2,min(e1.t1) as t1,min(e2.etime) as t2
 from event as e2
 inner join e1 on e2.uid = e1.uid
 where e2.etime>= to_date('2021-01-10') and e2.etime<to_date('2021-01-25')
 and e2.etime > t1 and e2.etime < t1 + 7
 and eventtype='eventtype2' and …
 group by 1),
e3 as (
 select uid,1 as step3,min(e2.t1) as t1,min(e3.etime) as t3
 from event as e3
 inner join e2 on e3.uid = e2.uid
 where e3.etime>= to_date('2021-01-10') and e3.etime<to_date('2021-01-25')
 and e3.etime > t2 and e3.etime < t1 + 7
 and eventtype='eventtype3' and …
 group by 1)
select
 sum(step1) as step1,
 sum(step2) as step2,
 sum(step3) as step3
from
 e1
 left join e2 on e1.uid = e2.uid
 left join e3 on e2.uid = e3.uid

这里只是三步漏斗，可以看到还是十分复杂的。不仅代码复杂，而且性能也低，这个计算在用户实际场景中 4 亿行数据使用 Snowflake 的 Medium 服务器（32 核相当）3 分钟没跑出来。SQL 对有序计算的支持不足，数据库内部自然不会提供针对有序计算的高效算法，最后只能写成这种自己 JOIN 自己的形式，最后导致低效率。

很显然，天底下没有什么都好的事情。SQL（数据库）的“固有”优化算法和“自动”优化能力有时让我们使用起来的确很轻松，不必关心执行步骤，写好 SQL 扔过去就好了。但当我们想要干预其执行路径的时候会发现非常困难，更别提让数据库执行我们设计的算法了。这种不灵活加上架构上的缺点（耦合紧、扩展性差）经常会抵消掉 SQL 在性能上的一些优势，让使用者望而却步。

Java

相比之下，Java 作为一个功能完善的编程语言，其灵活性就要强很多了。我们不仅可以使用 Java 实现复杂计算，还可以编写各种高性能算法来提升计算性能，毕竟没有什么是代码解决不了的。像前面提到的组内 TopN 使用 Java 实现时就可以不用大排序（借助一个分组长度的集合，保持集合成员是该分组最大的 N 个成员即可），遍历一次就可以完成计算。漏斗分析也没问题，每次读出一个用户的行为数据再按次序统计漏斗即可。

但是，这里还有一个关键，算法不仅要想到还要能实现，而这些算法写起来并不容易，很多应用程序员缺乏这种能力。像两表连接运算，最容易想到也最容易实现的就是两层循环，这样的计算复杂度是 n*m。如果想快，可以用 HASH JOIN，复杂度会降低 K 倍（如果 HASH 能平均分成 K 份），但这个算法很多应用程序员就不会写。更进一步，如果知道数据有序，还可以使用有序归并，复杂度会降到 n+m，但实现的难度依然很高。这还只是两个表，如果是多个表就会会更困难，最后通常会采用好写的低性能算法，导致用 Java 实现的常规运算跑不过 SQL 的情况。

Java 实现这些算法之所以很难写，一方面是因为这些算法本身有难度，另一方面也是因为 Java 过于原生，没有提供足够丰富的结构化计算类库，要实现这些数据计算几乎都要从头来做，实现起来很繁琐，对人员要求很高，很多人不会，即使会也可能懒得去写。比如常见的分组汇总用 Java 实现：

Map<Integer, Double> sellerAmountMap = new HashMap<>();
Iterator<Order> iterator = orders.iterator();

while (iterator.hasNext()) {
 Order order = iterator.next();
 int sellerId = order.getSellerId();
 double amount = order.getAmount();
 if (sellerAmountMap.containsKey(sellerId)) {
   double currentAmount = sellerAmountMap.get(sellerId);
   amount += currentAmount;
 }
 sellerAmountMap.put(sellerId, amount);
}

for (Map.Entry<Integer, Double> entry : sellerAmountMap.entrySet()) {
 System.out.println("Seller ID: " + entry.getKey() + ", Total Amount: " + entry.getValue());
 }

这只是个内存中的单字段分组后的单字段聚合运算，如果涉及到多字段以及分组键和聚合式都可能是表达式的情况，代码要还要再复杂很多倍，难度也会增加很多。

Java8 以后的 Stream 提供了一些计算类库：

Map<Object, DoubleSummaryStatistics> c=Orders.collect(Collectors.groupingBy(r->r.SellerId,Collectors.summarizingDouble(r->r.Amount)));

for(Object sellerid:c.keySet()){
  DoubleSummaryStatistics r =c.get(sellerid);
  System.out.println("group is："+sellerid+" sum is："+r.getSum());
}

虽然用到了 groupingBy、collect、Collectors、summarizingDouble、DoubleSummaryStatistics 等多个类和函数，代码仍略显复杂。

多字段分组汇总（按年份和 Client 分组）：

Calendar cal=Calendar.getInstance();
Map<Object, DoubleSummaryStatistics> c=Orders.collect(Collectors.groupingBy(
r->{
cal.setTime(r.OrderDate);
return cal.get(Calendar.YEAR)+"_"+r.SellerId;
},
Collectors.summarizingDouble(r->{
return r.Amount;
})));

for(Object sellerid:c.keySet()){
DoubleSummaryStatistics r =c.get(sellerid);
String year_sellerid[]=((String)sellerid).split("_");

System.out.println("group is (year):"+year_sellerid[0]+"\t (sellerid):"+year_sellerid[1]+"\t sum is："+r.getSum());
}

明显又复杂了很多，这跟 SQL 针对单字段或多字段分组以及是否用到计算式的代码难度几乎一致的情况完全不同。这还只是简单的分组汇总，如果要实现其他高性能算法的复杂度可想而知。Java 类库还只有内存算法，很难处理大数据，相比 SQL 又差了很多。这些计算用 Java 写出来的难度就已经很高了，更别提写完以后还要保证性能了。

Java 还有一个问题，没有通行的高效存储，经常还要借助数据库作为存储介质，这也会导致计算性能低下。Java 通过 JDBC 将数据读出来才能算，但大部分数据库提供的 JDBC 效率并不高，当数据量较大时 IO 成本就很高，即使最后计算很快，整体时间仍然很长（IO 成本大于计算成本）。

如果有一套不依赖数据库的高效存储（如文件系统）机制，就可以避免数据库的 IO 效率问题了。

即使有些场景下数据源或目标必须使用数据库，必须忍受数据库的低效 IO，高效的自有存储仍然对提高性能仍有意义。大数据计算有时会涉及写缓存（如大排序），还有一些复杂业务在计算过程中要将中间结果落地，这些缓存或中间数据如果还写入数据库成本就太高了，数据库写入比读取要慢得多，写完还要读一来一回时间开销非常大，借助独立存储来写缓存或存储中间结果就可以有效解决数据库频繁读写带来的问题。此外，一些数据库尤其是为 AP 服务的数据仓库，历史冷数据通常可以从数据库复制一份到外部存储以获得更高计算性能，如果总是要从数据库中读取大量历史数据参与计算，那就会常常发生 IO 时间远远超过计算时间的尴尬局面。

采用文件来做缓存是否可行？毕竟 Java 读写文件也并不费劲。文件的读写效率也远高于数据库，理论上是没有问题的，但实现一个高效的文件存储机制依然像上面的算法一样，又难又繁。

最简单的办法是使用文本，这很通用，但读写效率却很低。原因在于数据类型解析上，各种数据要解析成对应的类型非常耗时，其中又以字符串和日期最慢。

那么把对象序列化后写成二进制的是不是就可以了？也没那么简单。虽然这解决了数据类型解析的问题，但未必比文本高效，其占用的空间有可能比文本更大（比如 long 的 1 会占 8 个字节，比文本的 1 还长），虽然减少了数据类型解析时间，但又多了硬盘读写时间，很难说是否能提升性能。一个高效存储还要考虑压缩编码、列存甚至索引等一切可以提升使用效率的手段，综合这些方面以后开发工作可以预见到非常困难。

SPL

我们看到，SQL 和 Java 的优缺点基本是对着的，SQL 的优点恰恰是 Java 的缺点，反之亦然。但二者都面临性能问题，SQL 性能低时我们没法干预，Java 虽然可以干预但过于困难，也相当于没法做了。

那有没有办法同时兼顾 SQL 和 Java 的优点呢？既能享受 SQL 的简单，又能拥有 Java 的灵活，同时还能获得高性能。

SPL（Structured Process Language）提供了这样的能力。作为专门用于结构化数据处理的编程语言，SPL 提供了丰富的结构化数据计算类库，封装了众多高性能算法可以直接使用，充分保持了 SQL 优势；由于 SPL 语法简单灵活，可以比 Java 更方便实现各类复杂计算。SPL 提供了自有的文件存储，可以有效克服数据库 IO 效率问题。同时，SPL 具备良好的集成性，可以嵌入在应用内实施数据处理，获得与 Java 一样的灵活性。

计算类库

SPL 提供了专业的结构化数据对象序表，并在序表的基础上提供了丰富的计算类库。包括常规的过滤、分组、排序、去重、连接等计算，比如一般的：

Orders.sort(Amount) // 排序
Orders.select(Amount*Quantity>3000 && like(Client,"*S*")) // 过滤
Orders.groups(Client; sum(Amount)) // 分组
Orders.id(Client) // 去重
join(Orders:o,SellerId ; Employees:e,EId) // 连接
……

这些类库中也都类似 SQL 一样采用了业界的成熟算法，可以高效地运算。

有了这些丰富的数据类型和计算类库，SPL 实施计算不仅代码简洁，性能也高。像前面提到的 TopN 问题，可以这样写：

T.groups(y;top(-5;x))

SPL 把 TopN 这种运算认为是和 SUM 和 COUNT 一样的聚合运算，只不过返回值是个集合而已。这时候写出来的取前 N 名的语句中并没有排序动作，执行效率更高。

再比如前面的漏斗计算，用 SPL 来做：

	A
1	=["etype1","etype2","etype3"]
2	=file("event.ctx").open()
3	=A2.cursor(id,etime,etype;etime>=date("2021-01-10") && etime<date("2021-01-25") && A1.contain(etype) && …)
4	=A3.group(uid).(~.sort(etime))
5	=A4.new(~.select@1(etype==A1(1)):first,~:all).select(first)
6	=A5.(A1.(t=if(#==1,t1=first.etime,if(t,all.select@1(etype==A1.~ && etime>t && etime<t1+7).etime, null))))
7	=A6.groups(;count(~(1)):STEP1,count(~(2)):STEP2,count(~(3)):STEP3)

SPL 也提供了过程化和有序计算的支持，但在计算类库和敏捷语法的支持下整体代码更短，并且这段代码能够处理任意步骤数的漏斗，更加通用（只要改变参数即可）。

这段 SPL 代码的运行性能也很出色。同样数据规模（4 亿行），SPL 在一个 12 核 1.7G 的低端服务器上仅用了不到 10 秒，比数据库快了至少 20 倍，如果算上硬件的差异这个差距还要更大。

类似的，SPL 还提供了很多这样的高性能算法。包括：

内存计算类的二分法、序号定位、位置索引、哈希索引、多层序号定位、……
外存查找类的二分法、哈希索引、排序索引、带值索引、全文检索、……
遍历计算类的延迟游标、遍历复用、多路并行游标、有序分组汇总、序号分组、……
外键关联类的外键地址化、外键序号化、索引复用、对位序列、单边分堆、……
归并与连接类的有序归并、分段归并、关联定位、附表、……
多维分析类的部分预汇总、时间段预汇总、冗余排序、布尔维序列、标签位维度、……
集群计算类的集群复组表、复写维表、分段维表、冗余与备胎容错、负载均衡、……

有了这些算法，我们根据不同的应用场景直接使用即可，不必像 Java 一样从头开发，而且 SPL 提供的算法比 SQL 更为丰富。比如像前面提到的连接运算，SPL 做了更细致的划分。对于多对一的外键关联可以使用外键地址化、临时地址化、外键序号化等方式，如果维表比较大则可以使用大维表查找技术完成连接，如果维表和事实表都很大则可以采用单边分堆算法；而对于一对一的同维关联和多对一的主子关联则可以采用有序归并，如果数据量过大还可以通过分段并行的方式进一步提升性能。

从计算类库的角度来看，SPL 有了这些比 Java 和 SQL 丰富的算法以后，数据处理的工作就是针对这些算法的选用和组合，简单方便的同时性能也更高。

自有存储

只有计算类库还不够，如果像 Java 一样使用数据库作为存储仍然会面临 IO 开销大于计算开销的问题。为此，SPL 设计了自有格式的二进制文件存储，不仅是简单的二进制化，而是集编码、压缩、列存、索引、分段等多种机制于一身的高效文件格式。

目前 SPL 提供了两种高性能文件类型：集文件和组表。集文件采用了压缩技术（占用空间更小读取更快），存储了数据类型（无需解析数据类型读取更快），支持可追加数据的倍增分段机制，利用分段策略很容易实现并行计算，保证计算性能。组表支持列式存储，在参与计算的列数（字段）较少时会有巨大优势。组表上还实现了 minmax 索引，同时支持倍增分段，这样不仅能享受到列存的优势，也更容易并行提升计算性能。

有了高性能存储，我们就可以把数据从数据库迁移到文件中以获得更高计算性能。即使数据无法从数据库迁出，使用 SPL 高性能存储用来写缓存或中间计算结果也很有意义。而历史冷数据也可以复制一份到文件存储中用于高效计算。

除了性能上的优势，使用文件存储还可以获得更低廉的成本，文件采用文件系统的树状目录也更方便管理。使用文件存储也更容易实现存算分离，针对存储或计算单独进行扩展，更符合现代应用架构的需要。

SPL 并不强制要求使用自有格式文件存储，SPL 具备良好的开放性可以对接多种数据源。在注重计算性能和数据实时性时，可以将冷数据固化到 SPL 存储，热数据仍然从数据库中读取，从而实现 T+0 查询以及 HTAP。

集成性

说到这里，我们已经探讨了 SPL 的高性能以及代码简洁性，而 Java 架构上的好处 SPL 是否可以继承呢？

答案是肯定的。

SPL 具备良好的集成性，可以将 SPL 嵌入集成到应用中作为计算引擎使用，获得与 Java 一样的灵活性，在微服务等应用场景中发挥作用。

SPL 提供了标准 JDBC 接口，可以像调用存储过程一样请求 SPL 计算结果。

JDBC 调用 SPL 代码示例：

Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
CallableStatement st = conn.prepareCall("{call splscript(?, ?)}");
st.setObject(1, 3000);
st.setObject(2, 5000);
ResultSet result=st.execute();

综合来看，SQL 的性能取决于优化引擎的表现，而优化引擎面对稍复杂的情况经常失效，而且 SQL 还会导致应用架构的臃肿（不灵活）；Java 具备良好的灵活性，可以应对复杂计算，但要实现高性能计算不仅需要考虑各种情况，还要从头开始，复杂度过高，再考虑到依赖数据库存储，Java 也同样不具备性能优势。相比之下，SPL 综合了 SQL 和 Java 的共同优点，不仅语法简洁，使用灵活，并内置了大量的高性能算法和高性能文件存储充分保障计算性能，更能满足当代应用的需要。