往期热门文章：
众所周知，很多大数据计算都是用 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 计算量并没有变少，但能大幅减少硬盘读取数据量，就能成倍提速了。from 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) minzgroup 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 大致是这样的：
select * from (select x from T order by x desc) where rownum<=5
表 T 有 10 亿条数据，从 SQL 语句来看，是将全部数据大排序后取出前 5 名，剩下的排序结果就没用了！大排序成本很高，数据量很大内存装不下，会出现多次硬盘数据倒换，计算性能会非常差！避免大排序并不难，在内存中保持一个 5 条记录的小集合，遍历数据时，将已经计算过的数据前 5 名保存在这个小集合中，取到的新数据如果比当前的第 5 名大，则插入进去并丢掉现在的第 5 名，如果比当前的第 5 名要小，则不做动作。这样做，只要对 10 亿条数据遍历一次即可，而且内存占用很小，运算性能会大幅提升。这种算法本质上是把 TopN 也看作与求和、计数一样的聚合运算了，只不过返回的是集合而不是单值。SQL 要是能写成这样：select top(x,5) from T 就能避免大排序了。然而非常遗憾，SQL 没有显式的集合数据类型，聚合函数只能返回单值，写不出这种语句！
不过好在全集的 TopN 比较简单，虽然 SQL 写成那样，数据库却通常会在工程上做优化，采用上述方法而避免大排序。所以 Oracle 算那条 SQL 并不慢。但是，如果 TopN 的情况复杂了，用到子查询中或者和 JOIN 混到一起的时候，优化引擎通常就不管用了。比如要在分组后计算每组的 TopN，用 SQL 写出来都有点困难。Oracle 的 SQL 写出来是这样：select * from (select y,x,row_number() over (partition by y order by x desc) rn from T)where rn<=5
这时候，数据库的优化引擎就晕了，不会再采用上面说的把 TopN 理解成聚合运算的办法。只能去做排序了，结果运算速度陡降！select y,top(x,5) from T group by y
把 top 看成和 sum 一样的聚合函数，这不仅更易读，而且也很容易高速运算。
关联计算也是很常见的情况。以订单和多个表关联后做过滤计算为例，SQL 大体是这个样子：select o.oid,o.orderdate,o.amount from orders o left join city ci on o.cityid = ci.cityid left join shipper sh on o.shid=sh.shid left join employee e on o.eid=e.eid left join supplier su on o.suid=su.suid where ci.state='New York' and e.title = 'manager' and ...
订单表有几千万数据，城市、运货商、雇员、供应商等表数据量都不大。过滤条件字段可能会来自于这些表，而且是前端传参数到后台的，会动态变化。
SQL 一般采用 HASH JOIN 算法实现这些关联，要计算 HASH 值并做比较。每次只能解析一个 JOIN，有 N 个 JOIN 要执行 N 遍动作，每次关联后都需要保持中间结果供下一轮使用，计算过程复杂，数据也会被遍历多次，计算性能不好。
通常，这些关联的代码表都很小，可以先读入内存。如果将订单表中的各个关联字段预先做序号化处理，比如将雇员编号字段值转换为对应雇员表记录的序号。那么计算时，就可以用雇员编号字段值（也就是雇员表序号），直接取内存中雇员表对应位置的记录，性能比 HASH JOIN 快很多，而且只需将订单表遍历一次即可，速度提升会非常明显！select o.oid,o.orderdate,o.amount from orders o left join city c on o.cid = c. left join shipper sh on o.shid=sh. left join employee e on o.eid=e. left join supplier su on o.suid=su. where ci.state='New York' and e.title = 'manager' and ...
可惜的是，SQL 使用了无序集合概念，即使这些编号已经序号化了，数据库也无法利用这个特点，不能在对应的关联表这些无序集合上使用序号快速定位的机制，只能使用索引查找，而且数据库并不知道编号被序号化了，仍然会去计算 HASH 值和比对，性能还是很差！
select id,amt,tdate,… from T where id='10100' and tdate>= to_date('2021-01-10', 'yyyy-MM-dd') and tdate<to_date('2021-01-25', 'yyyy-MM-dd') and …
在 T 表的几亿条历史数据中，快速找到某个帐户的几条到几千条明细，SQL 写出来并不复杂，难点是大并发时响应速度要达到秒级甚至更快。为了提高查询响应速度，一般都会对 T 表的 id 字段建索引：create index index_T_1 on T(id)
在数据库中，用索引查找单个帐户的速度很快，但并发很多时就会明显变慢。原因还是上面提到的 SQL 无序理论基础，总数据量很大，无法全读入内存，而数据库不能保证同一帐户的数据在物理上是连续存放的。硬盘有最小读取单位，在读不连续数据时，会取出很多无关内容，查询就会变慢。高并发访问的每个查询都慢一点，总体性能就会很差了。在非常重视体验的当下，谁敢让用户等待十秒以上？！容易想到的办法是，把几亿数据预先按照帐户排序，保证同一帐户的数据连续存储，查询时从硬盘上读出的数据块几乎都是目标值，性能就会得到大幅提升。但是，采用 SQL 体系的关系数据库并没有这个意识，不会强制保证数据存储的物理次序！这个问题不是 SQL 语法造成的，但也和 SQL 的理论基础相关，在关系数据库中还是没法实现这些算法。
不能再用 SQL 和关系数据库了，要使用别的计算引擎。开源的集算器 SPL 基于创新的理论基础，支持更多的数据类型和运算，能够描述上述场景中的新算法。用简单便捷的 SPL 写代码，在短时间内能大幅提高计算性能！
| | |
| A1=file("T.ctx").open().cursor(a,b,c,d,x,y,z) |
| | =A2.select(…).groups(a,b;sum(x)) |
|
| |
| | =A4.select(…).groups(c,d;max(y)) |
|
| |
| | =A6.select(…).groupx(a,c;avg(y),min(z)) |
|
| |
| | |
| |
| |
| =A1.cursor@m(x).total(top(-5,x), top(5,x)) |
| // top(-5,x)计算出 x 最大的前 5 名，top(5,x) 是 x 最小的前 5 名。 |
| |
| |
| =A1.cursor@m(x,y).groups(y;top(-5,x), top(5,x)) |
| |
| >env(city,file("city.btx").import@b()),env(employee,file("employee.btx").import@b()),... |
| |
| |
| =file("orders.ctx").open().cursor(cid,eid,…).switch(cid,city:#;eid,employee:#;…) |
| =A1.select(cid.state='New York' && eid.title=="manager"…) |
| |
| | |
| =file("T-original.ctx").open().cursor(id,tdate,amt,…) |
| | |
| =B2.create@r(#id,tdate,amt,…).append@i(A2) |
| =B2.open().index(index_id;id) |
|
| |
| | |
| =T.icursor(;id==10100 && tdate>=date("2021-01-10") && tdate |
| |
除了这些简单例子，SPL 还能实现更多高性能算法，比如有序归并实现订单和明细之间的关联、预关联技术实现多维分析中的多层维表关联、位存储技术实现上千个标签统计、布尔集合技术实现多个枚举值过滤条件的查询提速、时序分组技术实现复杂的漏斗分析等等。
1、《历史文章分类导读列表！精选优秀博文都在这里了！》
2、程序员裸辞全职接单一个月的感触
3、Java8 Stream：2万字20个实例，玩转集合的筛选、归约、分组、聚合
4、字节终面：两个文件的公共URL怎么找？
5、留在一线，逃离一线？我从上海举家回成都的生活经历告诉你
6、公司规定所有接口都用 POST请求，这是为什么？
7、我被这个浏览了 746000 次的问题惊住了！
8、腾讯三面：40亿个QQ号码如何去重？
9、自从用完Gradle后，有点嫌弃Maven了！速度贼快！
10、一个员工的离职成本有多恐怖！
