当前位置：首页 > news >正文

怎样用 esProc 为大主子表关联提速

news 来源：原创 2025/7/15 2:37:17

类似订单和明细表这样的主子表关联比较常见，在 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 » esProc Download - esProc SPL Official Website，用标准版就可以了。

安装 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 =3or 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 !=2and 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 有序存储可以有效加速，而且实施起来非常方便。

相关文章：