怎样用 esProc 为大主子表关联提速
类似订单和明细表这样的主子表关联比较常见，在 SQL 中，这种关联用 JOIN 实现，在两个表都很大的情况下，常常出现计算速度非常慢的现象。
如果预先将主子表都按照主键有序存储，就可以使用归并算法实现关联。这种算法只需要对两个表依次遍历，不必借助外存缓存，可以大幅降低计算量和 IO 量。
esProc 支持有序归并算法，可以大幅提升主子表关联计算性能。下面通过订单和订单明细的例子，比较一下 esProc SPL 和 MYSQL 数据库计算主子表关联的性能。
MYSQL 数据库中有订单表 orders，主键是订单号 oid，字段有客户号 cid，日期 odate，数据量一千万。订单明细表 details，主键是 oid 和明细号 did，字段有数量 quantity、单价 price，产品号 pid，数据量 3 千万。
测试环境：VMWARE 虚拟机，8 核 CPU，8G 内存，SSD 硬盘。操作系统是 Win11，MYSQL 版本是 8.0。
先下载 esProc https://www.esproc.com/download-esproc/，用标准版就可以了。
安装 esProc 后，试一下 IDE 是否可以正常访问数据库。先把 MYSQL 数据库的 JDBC 放到目录 "[安装目录]\common\jdbc"，这是 esProc 的类路径之一：
在 esProc 中建立 MYSQL 数据源：
返回到数据源界面并连接刚才配置的数据源，如果数据源名变成粉色，说明配置成功。
在 IDE 中新建脚本，编写 SPL 语句，连接数据库，执行 SQL 读入 orders 表部分数据：
A |
B |
|
1 |
=connect("mysql16") |
|
2 |
=A1.query("select * from orders limit 100") |
|
按 ctrl-F9 执行，可以在 IDE 右边看到 A2 的执行结果，很方便。
接下来完成数据准备，把历史数据从数据库导出到 esProc 的高性能文件：
A |
|
1 |
=connect("mysql16") |
2 |
=A1.cursor("select oid,cid,eid from orders order by oid") |
3 |
=file("orders.ctx").create(#oid,cid,eid) |
4 |
=A3.append(A1) |
5 |
=A1.query("select oid,did,quantity,price from details order by oid") |
6 |
=file("details.ctx").create@p(#oid,did,quantity,price) |
7 |
=A6.append(A5) |
8 |
>A1.close(),A3.close(),A6.close() |
A2 生成 orders 表的数据库游标。
A3 建立组表对象，# 表示组表对 oid 有序。A4 将数据库游标数据追加到组表中。
A5 到 A7 照样生成明细表 details。注意 A6 中 create 函数增加了 @p 选项，这是因为子表 details 中的关联字段 oid 值并不是唯一的，@p 表示第一个字段 oid 是分段键，这样可以防止分段读取时，把一个 oid 的明细记录拆分开。
数据准备完成后，就可以利用 esProc 实现主子表计算提速了。
例一， 指定日期范围，按照客户分组统计订单金额：
select
o.cid,sum(d.quantity * d.price)
from
orders o
join
details d on o.oid = d.oid
where o.odate>='2025-01-15'
and o.odate<='2025-03-15'
group by o.cid;
这两个大表关联，MYSQL 执行了 10 分钟没有出来结果。
esProc 采用有序归并：
A |
|
1 |
=file("orders.ctx").open().cursor@m(oid,cid;odate>=date(2025,1,15) && odate<=date(2025,3,15)) |
2 |
=file("details.ctx").open().cursor(oid,quantity,price;;A1) |
3 |
=joinx(A1:o,oid;A2:d,oid) |
4 |
=A3.groups(o.cid;sum(d.quantity*d.price)) |
特别注意的是，A2 中游标的最后一个参数是 A1，表示多线程并行时，details 表会跟随 orders 表分段，保证后面两个表有序归并的正确性。
A3 中 orders 和 details 有序关联归并。A4 对归并的结果分组汇总。
执行时间：1.7 秒。
例题二，客户号是 3 或 9 的订单，按照产品号分组统计订单金额。
select
d.pid,sum(d.quantity * d.price)
from
orders o
join
details d on o.oid = d.oid
where o.cid =3
or o.cid=9
group by d.pid;
MYSQL 也是执行了 10 分钟没有出来结果。
esProc 的写法：
A |
|
1 |
=file("orders.ctx").open().cursor@m(oid;cid==3 || cid==9) |
2 |
=file("details.ctx").open().cursor(oid,quantity,price,pid;;A1) |
3 |
=joinx(A1:o,oid;A2:d,oid) |
4 |
=A3.groups(d.pid;sum(d.quantity*d.price)) |
执行时间：1.8 秒
例题三，产品号不是 2、8 的订单明细，按照日期分组，求平均每个订单的金额。
select
o.odate,sum(d.quantity * d.price)/count(distinct o.oid)
from
orders o
join
details d on o.oid = d.oid
where d.pid !=2
and d.pid !=8
group by o.odate;
MYSQL 仍然执行了 10 分钟没有出来结果。
这里是在主子表关联后计算去重计数，这两种计算 SQL 的性能都很差。esProc 的有序去重计数性能要好的多，而且有序去重计数正好也需要数据对 oid 有序，可以在有序归并后顺利实施。
SPL 的写法：
A |
|
1 |
=file("orders.ctx").open().cursor@m(oid,odate) |
2 |
=file("details.ctx").open().cursor(oid,quantity,price;pid!=2 && pid!=8;A1) |
3 |
=joinx(A1:o,oid;A2:d,oid) |
4 |
=A3.groups(o.odate;sum(d.quantity*d.price)/icount@o(o.oid)) |
A4 中的 icount@o 就是有序去重计数。
执行时间：4 秒。
esProc 计算大主子表的性能，完胜 MYSQL。
esProc 提速方案的前提是主子表数据都按照主键 oid 有序存储。如果有新增数据的话，一般来说都是新的主键 oid，直接在已有的组表基础上追加就可以了。
如果历史数据需要变动，比如修改、插入或者删除，就要麻烦一些。变动的数据量不大时，可以写到一个单独的补区。在读取时补区与正常数据一起归并计算，这样访问时可以感觉不到补区的存在。
变动的数据量较大时，要重新生成全量有序数据，但是这个过程耗时较长，不能频繁进行。
实际上，针对固定不变的历史数据的计算场景就非常多了，也有不少大主子表关联计算亟需加速。这些场景采用 SPL 有序存储可以有效加速，而且实施起来非常方便。
英文版