第五篇 - 大主子表关联时的 EXISTS
大主子表之间进行 EXISTS 计算往往会导致较差的性能。这样的计算本质上是在做主键关联，如果能预先将主子表都按照主键有序存储，也可以使用有序归并算法有效提速。
esProc SPL 可以把主子表的 EXISTS 转化为有序归并，从而提升计算性能。
下面通过订单表和订单明细表的例子，介绍这种情况的外置提速方法。
先完成数据准备，可以直接使用第四篇生成的 CTX 文件。
例 5.1 对包含 7 号产品的订单，按照客户号分组统计订单个数。
select o.customer_id, count(o.order_id)
from orders o
where o.order_date >= '2024-12-31'
and o.order_date < '2025-01-01'
and exists
(select *
from details d
where d.order_id = o.order_id
and d.product_id=7)
group by o.customer_id;
执行时间 41 秒
SPL 代码 23：
A |
|
1 |
=now() |
2 |
=file("ordersQ4.ctx").open().cursor@m(order_id,customer_id;order_date>=date(2024,12,31)&&order_date<date(2025,01,01)) |
3 |
=file("detailsQ4.ctx").open().cursor(order_id;product_id==7;A2) |
4 |
=A3.group@1(order_id) |
5 |
=joinx(A2:o,order_id;A4:d,order_id) |
6 |
=A5.groups(o.customer_id;count(o.order_id)) |
7 |
>output("query cost:"/interval@ms(A1,now())/"ms") |
A4 把 7 号产品的订单明细，按照 order_id 有序分组，每组只保留第一个 order_id，这样不用生成分组子集，性能更好。
分组结果在 A5 中和订单表有序归并，再在 A5 中分组统计订单数量。
A3 中游标的最后一个参数是 A2，表示多线程并行时，details 表会跟随 orders 表分段，保证后面两个表有序归并的正确性。
执行时间：0.2 秒
例 5.2 产品号为 6 的订单明细，有多少条在订单表中找不到记录。
select count(d.order_id)
from details d
where d.product_id = 6
and not exists
(select *
from orders o
where o.order_id = d.order_id);
执行时间：16 秒
SPL 代码 24：
A |
|
1 |
=now() |
2 |
=file(“ordersQ4.ctx”).open().cursor@m(order_id) |
3 |
=file("detailsQ4.ctx").open().cursor(order_id;product_id==6;A2) |
4 |
=joinx@d(A3,order_id;A2,order_id) |
5 |
=A4.skip() |
6 |
>output("query cost:"/interval@ms(A1,now())/"ms") |
7 |
=now() |
A4 中 joinx 的选项 @d，表示用订单表过滤明细表，只保留在订单表中找不到的 order_id。
A5 对 A4 游标计数，就是想要的结果了。
执行时间：0.2 秒
例 5.3 找出明细不止一条的订单，要求订单不包含 9 号产品，按照日期分组统计订单数量。
select o.order_date,count(distinct o.order_id)
from orders o
where
exists (
select 1
from details d
where d.order_id = o.order_id
group by d.order_id
having count(*) > 1
)
and not exists (
select 1
from details d
where d.order_id = o.order_id and d.product_id = 9
)
MYSQL 跑了 5 分钟没出结果。
SPL 代码 25：
A |
|
1 |
=now() |
2 |
=file("ordersQ4.ctx").open().cursor@m(order_id,order_date) |
3 |
=file("detailsQ4.ctx").open().cursor(order_id,product_id;;A2) |
4 |
=A3.group(order_id) |
5 |
=A4.select(~.count(order_id)>1 && !~.pselect(product_id==9)) |
6 |
=joinx(A2:o,order_id;A5:d,order_id) |
=A6.groups(o.order_date;count(o.order_id)) |
|
=now() |
A3 把明细表按照 order_id 有序分组。
A4 循环过滤每一组，~ 表示当前组。只保留 order_ id 个数大于 1 且不包含 product_id 为 9 的组。
执行时间：0.9 秒
小结一下性能（单位 - 秒）：
MYSQL |
SPL |
|
例 5.1 |
41 |
0.2 |
例 5.2 |
16 |
0.2 |
例 5.3 |
超过 5 分钟 |
0.9 |
请动手练习一下：
1、对包含 8 号产品的订单，按照日期分组统计订单个数。
2、思考：在自己熟悉的数据库中有没有大主子表关联计算 EXISTS？是否可以用有序归并方法提速？
