数据库太慢跑崩的另一罪魁

没错，就是著名的 JOIN。
JOIN 一直是数据库计算的老大难问题，业界想了很多办法来计算它。如果不做任何优化，那就是两个关联表循环遍历，这是个乘法级的复杂度，数据量稍大一点就受不了。成熟的数据库当然不会这么傻，对于最常见的等值 JOIN（关联条件为键值相等），通常会采用 HASH JOIN 的办法，能将计算量下降 K 倍（K 是 HASH 空间长度，这里不赘述细节了，资料很多）。
HASH JOIN 算法用在内存上还好。对于数据量大到内存装不下的时候，很可能会涉及 HASH 分堆缓存，运气不好时还要二次 HASH，性能不可控。分布式就更麻烦，数据库界发明了 broadcast join 和 shuffle join 等办法换成单机 JOIN（这里也不赘述了），很麻烦，性能也会陡降，以至于会发生集群节点更多时反而速度更慢的现象。有些数据库为了图快， 只实现了内存算法，这样数据量一大就崩掉也不奇怪了。

JOIN 真有这么难对付吗？
如果严格按 SQL 定义的 JOIN 来实现，那确实是挺难的。然而，大家在努力解决 JOIN 性能问题时，却几乎从来没想过这个定义是不是合理。
事实上，现实中有业务意义的等值 JOIN 绝大多数都会有主键（或逻辑主键）参与，而不是乱来的（没主键时大概率是代码写错了），利用这个特点就有办法优化 JOIN 的性能。但遗憾的是，SQL 对 JOIN 的定义中完全没有涉及这一点。

我们可以把等值 JOIN 分成两类：一类是事实表的普通字段和维表主键之间的外键关联，是多对一的关系；另一个是主表主键和子表部分主键之间的主键关联，是一对多的关系（有可能退化成一对一的同维表关系）；然后分别利用各自的特征来优化性能。
维表通常比较小，可以全读内存。这样只要遍历事实表就可以，无论多大的事实表，以及无论有多少层维表，都不需要做 HASH 分堆缓存，更不可能有二次 HASH。把维表区分出来后，直接在集群节点中复制，也不需要计算时再去做 broadcast 或 shuffle。
如果维表特别大不能读入内存，那可以将其 按主键有序存储（分布）；事实表较小时，JOIN 运算会变成（对维表的）查找运算，也不存在 HASH 分堆缓存和二次 HASH。事实表也很大时，可以只将事实表按维表的区段分堆缓存，一方面只需要单边分堆（减少缓存量），另一方面也不可能发生二次 HASH，这时虽然也不会太快，但比 HASH JOIN 优势还是大很多。分布式时，还可以把维表装入集群节点的内存中，再只要在各节点分别遍历事实表就可以了。
主子（同维）表情况就更简单，都按主键有序存储后，可以使用复杂度很低的归并算法，只要一点点内容一次遍历就完成 JOIN，完全不涉及 HASH 分堆和二次 HASH，更没有 shuffle 动作，甚至连 HASH 都不用算，多大数据量都不可能跑崩。

可惜，关系数据库无法实现这些算法，它要忠实地实现关系代数的定义。好些的数据库可能在工程上做些优化，比如发现数据有序时会用 merge join，但因为关系代数的无序集合基础，不能保证数据的物理有序性（仅仅逻辑有序时不能避免硬盘跳动的巨大成本），绝大多数情况都无法采用这些算法。

嗯，esProc SPL 可以。
esProc SPL 严格地说并不是一个数据库，而是个专业的计算引擎。它对等值 JOIN 的定义就是像上面那样分成两类，并明确定义了事实表、维表、主表、子表这些概念（虽然我们谈论数据库时经常说这些术语，但关系代数并没有严格定义它们）。esProc SPL 不再采用关系代数和 SQL，而是自创了以有序集合为基础的离散数据集理论并发明了新的程序语言 SPL，天然支持物理有序的存储（内存和外存都可以），可以实现上面说的高效算法；SPL 中也针对不同类别的 JOIN 提供了相应的函数，程序员就可以方便地利用这些算法实现高性能。

对于数据量较小都能全内存的外键关联，如果只做一次，esProc SPL 的方法和数据库的 HASH JOIN 区别不大，因为也要计算 HASH 值找到关联记录。但 SPL 计算出来的 HASH 值可以复用，下次再有同一个维表参与关联时（这是常有的事）就不用再计算了。特别地，如果事实表也可加载进内存，还可以事先关联好，再做 JOIN 时就不必做 HASH 计算和比对了，这些都要利用维表主键参与关联的特征，而不认可这个特征的 SQL 体系就无法实现。
利用 SPL 的有序性，还可以实现维表序号化。即把外键转换成序号，这样可以直接用序号定位维表记录，避免 HASH 计算和比对，提升关联计算性能。无序的 SQL 体系同样无法实施这个算法。
其它情况，如数据量大到内存装不下时，前面的分析已经能说明 SPL 算法的巨大优势了。限于篇幅，这里也不再详细解释 SPL 执行 JOIN 的原理，感兴趣的同学可以去乾学院寻找相关资料。

来看一个关联运算及宽表的测试结果（时间单位为秒）：

	4C16G			8C32G
	esProc SPL	StarRocks	ClickHouse	esProc SPL	StarRocks	ClickHouse
宽表	114.2	129.9	74.3	57.7	62.1	33.2
两表关联	21.5	78.8	204.1	11.5	35.1	89.3
七表关联	55.6	152.5	内存溢出	30.6	73.3	内存溢出

可以看出，esProc SPL 的宽表运算性能还赶不上 ClickHouse，但采用了这些新算法后（具体在这里使用了多层维表预关联、维表主键序号化以及主子表归并等技术），JOIN 性能就有了明显优势。而 ClickHouse 的 JOIN 性能表现差也是意料之中的，两表关联时只有小维表，只是慢但还能应对，七表关联涉及大的主子表，就会发生崩掉的现象了。
完整测试报告见 SPL 计算性能系列测试：关联表及宽表 。

还是看这个实际的时空碰撞案例：找出和某指定手机在同一时间段和同一地点出现过次数最多的前 20 个手机，数据规模约 250 亿行。SQL 写出来大概是这样：

WITH DT AS ( SELECT DISTINCT id, ROUND(tm/900)+1 as tn, loc FROM T WHERE tm<3*86400)
SELECT * FROM (
    SELECT B.id id, COUNT( DISINCT B.tn ) cnt
    FROM DT AS A JOIN DT AS B ON A.loc=B.loc AND A.tn=B.tn
    WHERE A.id=a AND B.id<>a
    GROUP BY id )
ORDER BY cnt DESC
LIMIT 20

这里有自关联 JOIN，单节点的 ClickHouse 直接崩掉，动用了 5 节点的集群用了 30 多分钟才跑出来。SPL 代码只用一个节点不到 6 分钟就跑完：

	A
1	=now()
2	>NL=100000,NT=3*96
3	=file("T.ctx").open()
4	=A3.cursor(tm,loc;id==a).fetch().align(NL*NT,(loc-1)*NT+tm\900+1)
5	=A3.cursor@mv(;id!=a && A4((loc-1)*NT+tm\900+1))
6	=A5.group@s(id;icount@o(tm\900):cnt).total(top(-20;cnt))
7	=interval@ms(A1,now())

从 SPL 代码中根本看不出来有 JOIN 的痕迹，这是因为 SPL 改变了 JOIN 定义后，能把 JOIN 融合到其它运算过程中。这里 A4 就是在建立维表，A5 中的 A4((loc-1)*NT+tm\900+1) 即相当于内连接过滤作用，其中还利用了前述的序号化机制，有效地提升了 JOIN 性能。

细心的读者可能会发现，esProc SPL 的算法有效性依赖于数据对 id 有序，而数据产生次序通常不会是 id，而是时间。那么，这个算法是不是只能应用于事先排序过的历史数据上，对来不及一起排序的新数据就无效了呢？
esProc 已经考虑到这一点，SPL 的复组表可以在数据进入时实现增量排序，实时保证数据在读出时对 id 有序，可以让这套有序计算方案应用到最新的数据上。而且，针对事实表的统计通常都会涉及时间区间，SPL 的虚表支持双维有序机制，可以迅速将时间区间外的数据过滤掉，进一步提升运算性能。

esProc 是个纯 Java 软件，能在任何有 JVM 的环境下运算，可以无缝地嵌入到 Java 程序中，非常轻量地将数据仓库的运算能力赋予给各种场景下的应用中。
esProc 提供了可视的开发环境，支持单步执行、设置断点、所见即所得的结果预览，开发调试要比 SQL 和存储过程方便得多。
SPL 还有完善的流程控制语句，像 for 循环，if 分支都不在话下，还支持子程序调用，拥有存储过程才有的过程化能力，可以全面取代 SQL 和存储过程。
…

最后，esProc SPL 是开源免费的，源码地址在这里