数据结构
IN和EXISTS是SQL中常见的复杂条件，在将SQL（存储过程）转换成库外计算获取高性能时也会面对这些问题。本文将以TPC-H定义的模型为基础，介绍如何用集算器的语法实现IN、EXISTS并做优化。
TPC-H是TPC事务处理性能委员会制定的用于OLAP数据库管理系统的测试标准，模拟真实商业应用环境，以评估商业分析中决策支持系统的性能。TPC-H模型定义了8张表，表结构和表关系如下图：
IN常数集合
SQL示例（1）：
select
P_SIZE, P_TYPE, P_BRAND, count(1) as P_COUNT
from
PART
where
P_SIZE in (2, 3, 8, 15, 17, 25, 27, 28, 30, 38, 41, 44, 45)
and P_TYPE in (‘SMALL BRUSHED NICKEL’, ‘SMALL POLISHED STEEL’)
and P_BRAND not in (‘Brand#12’, ‘Brand#13’)
group by
P_SIZE, P_TYPE, P_BRAND
优化思路：
如果常数集合元素数少于3个则可以翻译成(f == v1 || f == v2)这种样式，NOT IN对应的就是（f != v1 && f != v2）。较多的时候可以在外层把常数集合定义成序列，然后用A.contain(f)来判断字段是否在序列中，通常元素个数超过13个可以先把序列排序，然后用A.contain@b(f)来进行有序查找，NOT IN对应的就是! A.contain(f)。注意一定要把序列定义在循环函数外，否则会被多次执行。
如果常数集合元素数量特别多可以用连接过滤，具体请参照下图代码。
集算器实现：
| A | B | |
| 1 | =[28, 30, 38,2, 3, 8, 15, 17, 25, 27,50 , 41, 44, 45].sort() | /对常数集合进行排序，这样就可以用序列的有序查找，通常序列元素数超过13个用有序查找会比遍历快 |
| 2 | =file(PART).cursor@b(P_SIZE, P_TYPE, P_BRAND) | /在PART表所对应的集文件上定义游标，参数为选出列 |
| 3 | =A2.select(A1.contain@b(P_SIZE)
&& (P_TYPE == “SMALL BRUSHED NICKEL” || P_TYPE == “SMALL POLISHED STEEL”) && (P_BRAND != “Brand#12” && P_BRAND != “Brand#13”)) | /对游标附加过滤操作，注意常数序列要定义在过滤函数外面否则会被重复运算 |
| 4 | =A3.groups(P_SIZE, P_TYPE, P_BRAND; count(1): P_COUNT) | /对游标计算分组得到最终结果 |
如果A1的元素数量特别多，则可以使用哈希连接的方法来过滤，把第3行代码替换如下：
| 3 | =A2.select((P_TYPE == “SMALL BRUSHED NICKEL” || P_TYPE == “SMALL POLISHED STEEL”)
&& (P_BRAND != “Brand#12” && P_BRAND != “Brand#13”)) .join@i(P_SIZE, A1:~) | //对游标附加过滤操作后再附加连接过滤操作 |
IN子查询
子查询选出字段是主键
SQL示例（2）：
select
PS_SUPPKEY, count(1) as S_COUNT
from
PARTSUPP
where
PS_PARTKEY in (
select
P_PARTKEY
from
PART
where
P_NAME like ‘ bisque%%’
)
group by
PS_SUPPKEY
优化思路：
子查询过滤后读入内存，然后外层表与先读入的内存表（子查询）做哈希连接进行过滤。集算器提供了switch@i()、join@i()两个函数用来做哈希连接过滤，switch是外键式连接，用来把外键字段变成指引字段，这样就可以通过外键字段直接引用指向表的字段，join函数不会改变外键字段的值，可用于只过滤。
集算器实现：
| A | B | |
| 1 | =file(PART).cursor@b(P_PARTKEY, P_NAME) | /在PART表所对应的集文件上定义游标，参数为选出列 |
| 2 | =A1.select(like(P_NAME, “bisque*”)).fetch() | /对游标附加过滤操作并取数 |
| 3 | =file(PARTSUPP).cursor@b(PS_SUPPKEY, PS_PARTKEY) | /在PARTSUPP表所对应的集文件上定义游标，参数为选出列 |
| 4 | =A3.join@i(PS_PARTKEY, A2:P_PARTKEY) | /对PARTSUPP游标进行连接过滤,@i选项表示内连接 |
| 5 | =A4.groups(PS_SUPPKEY; count(1):S_COUNT) | /对游标计算分组得到最终结果 |
子查询选出字段不是主键
SQL示例（3）：
select
O_ORDERPRIORITY, count(*) as O_COUNT
from
ORDERS
where
O_ORDERDATE >= date ‘1995-10-01’
and O_ORDERDATE < date ‘1995-10-01’ + interval ‘3’ month
and O_ORDERKEY in (
select
L_ORDERKEY
from
LINEITEM
where
L_COMMITDATE< L_RECEIPTDATE
)
group by
O_ORDERPRIORITY
优化思路：
子查询过滤后按关联字段去重读入内存，然后就变成类似于主键的情况了，可以继续用上面说的switch@i()、join@i()两个函数用来做哈希连接过滤。
集算器实现：
| A | B | |
| 1 | 1995-10-01 | =after@m(A1,3) |
| 2 | =file(LINEITEM).cursor@b(L_ORDERKEY,L_COMMITDATE,L_RECEIPTDATE) | /在LINEITEM表所对应的集文件上定义游标，参数为选出列 |
| 3 | =A2.select(L_COMMITDATE < L_RECEIPTDATE) | /对游标附加过滤操作 |
| 4 | =A3.groups(L_ORDERKEY) | /用groups对L_ORDERKEY去重 |
| 5 | =file(ORDERS).cursor@b(O_ORDERKEY,O_ORDERDATE,O_ORDERPRIORITY) | /在ORDER表所对应的集文件上定义游标，参数为选出列 |
| 6 | =A5.select(O_ORDERDATE>=A1 && O_ORDERDATE < B1) | /对游标附加过滤操作 |
| 7 | =A6.join@i(O_ORDERKEY, A4:L_ORDERKEY) | /对ORDERS游标进行连接过滤,@i选项表示内连接 |
| 8 | =A7.groups(O_ORDERPRIORITY; count(1):O_COUNT) | /对游标计算分组得到最终结果 |
子查询结果集内存放不下
SQL示例（3）：
select
O_ORDERPRIORITY, count(*) as O_COUNT
from
ORDERS
where
O_ORDERDATE >= date ‘1995-10-01’
and O_ORDERDATE < date ‘1995-10-01’ + interval ‘3’ month
and O_ORDERKEY in (
select
L_ORDERKEY
from
LINEITEM
where
L_COMMITDATE< L_RECEIPTDATE
)
group by
O_ORDERPRIORITY
优化思路：
IN子查询相当于对子查询结果集去重然后跟外层表做内连接，而做连接效率较好的就是哈希连接和有序归并连接，所以这个问题就变成了怎么把IN翻译成高效的连接，下面我们来分析在不同的数据分布下如何把IN转成连接。
(1)外层表数据量比较小可以装入内存：
先读入外层表，如果外层表关联字段不是逻辑主键则去重，再拿上一步算出来的关联字段的值对子查询做哈希连接过滤，最后拿算出来的子查询关联字段的值对外层表做哈希连接过滤。
(2)外层表和内层表按关联字段有序：
此时可以利用函数joinx()来做有序游标的归并连接，如果内层表关联字段不是逻辑主键则需要先去重。此例中的ORDERS表和LINEITEM表是按照ORDERKEY同序存放，可以利用此方法来做优化。
(3)内层表是大维表并且按主键有序存放：
集算器提供了针对有序大维表文件做连接的函数A.joinx，其它方法跟内存能放下时的处理类似在此不再描述。
集算器实现（1）：
| A | B | |
| 1 | 1995-10-01 | =after@m(A1,3) |
| 2 | =file(ORDERS).cursor@b(O_ORDERKEY,O_ORDERDATE,O_ORDERPRIORITY) | /在ORDER表所对应的集文件上定义游标，参数为选出列 |
| 3 | =A2.select(O_ORDERDATE>=A1 && O_ORDERDATE < B1).fetch() | /对游标附加过滤操作并取数 |
| 4 | =file(LINEITEM).cursor@b(L_ORDERKEY,L_COMMITDATE,L_RECEIPTDATE) | /在LINEITEM表所对应的集文件上定义游标，参数为选出列 |
| 5 | =A4.select(L_COMMITDATE < L_RECEIPTDATE).join@i(L_ORDERKEY,A3:O_ORDERKEY) | /对游标附加过滤操作和链接过滤操作 |
| 6 | =A5.groups(L_ORDERKEY) | /对L_ORDERKEY去重 |
| 7 | =A3.join@i(O_ORDERKEY, A6:L_ORDERKEY) | /对排列执行链接过滤操作 |
| 8 | =A7.groups(O_ORDERPRIORITY;count(1):O_COUNT) | /对排列计算分组得到最终结果 |
集算器实现（2）：
| A | B | |
| 1 | “1995-10-01 | =after@m(A1,3) |
| 2 | =file(ORDERS).cursor@b(O_ORDERKEY,O_ORDERDATE,O_ORDERPRIORITY) | /在ORDER表所对应的集文件上定义游标，参数为选出列 |
| 3 | =A2.select(O_ORDERDATE>=A1 && O_ORDERDATE < B1) | /对游标附加过滤操作 |
| 4 | =file(LINEITEM).cursor@b(L_ORDERKEY,L_COMMITDATE,L_RECEIPTDATE) | /在LINEITEM表所对应的集文件上定义游标，参数为选出列 |
| 5 | =A4.select(L_COMMITDATE < L_RECEIPTDATE) | /对游标附加过滤操作 |
| 6 | =A5.group@1(L_ORDERKEY) | /按L_ORDERKEY去重 |
| 7 | =joinx(A3:ORDER, O_ORDERKEY; A6, L_ORDERKEY) | /对有序游标执行内连接 |
| 8 | =A7.groups(ORDER.O_ORDERPRIORITY:O_ORDERPRIORITY;count(1):O_COUNT) | /对游标计算分组得到最终结果 |
EXISTS等值条件
此章节的优化思路和IN子查询的优化思路是相同的，事实上这种EXISTS也都可以用IN写出来（或者倒过来，把IN用EXISTS写出来）。
子查询关联字段是主键
SQL示例（4）：
select
PS_SUPPKEY, count(1) as S_COUNT
from
PARTSUPP
where
exists (
select
*
from
PART
where
P_PARTKEY = PS_PARTKEY
and P_NAME like ‘ bisque%%’
)
group by
PS_SUPPKEY
优化思路：
子查询过滤后读入内存，然后外层表与先读入的内存表（子查询）做哈希连接进行过滤。集算器提供了switch@i()、join@i()两个函数用来做哈希连接过滤，switch是外键式连接，用来把外键字段变成指引字段，这样就可以通过外键字段直接引用指向表的字段，join函数不会改变外键字段的值，可用于只过滤。
集算器实现：
| A | B | |
| 1 | =file(PART).cursor@b(P_PARTKEY, P_NAME) | /在PART表所对应的集文件上定义游标，参数为选出列 |
| 2 | =A1.select(like(P_NAME, “bisque*”)).fetch() | /对游标附加过滤操作并取数 |
| 3 | =file(PARTSUPP).cursor@b(PS_SUPPKEY, PS_PARTKEY) | /在PARTSUPP表所对应的集文件上定义游标，参数为选出列 |
| 4 | =A3.join@i(PS_PARTKEY, A2:P_PARTKEY) | /对PARTSUPP游标进行连接过滤,@i选项表示内连接 |
| 5 | =A4.groups(PS_SUPPKEY; count(1):S_COUNT) | /对游标计算分组得到最终结果 |
子查询关联字段不是主键
SQL示例（5）：
select
O_ORDERPRIORITY, count(*) as O_COUNT
from
ORDERS
where
O_ORDERDATE >= date ‘1995-10-01’
and O_ORDERDATE < date ‘1995-10-01’ + interval ‘3’ month
and exists (
select
*
from
LINEITEM
where
L_ORDERKEY = O_ORDERKEY
and L_COMMITDATE < L_RECEIPTDATE
)
group by
O_ORDERPRIORITY
优化思路：
子查询过滤后按关联字段去重读入内存，然后就变成类似于主键的情况了，可以继续用上面说的switch@i()、join@i()两个函数用来做哈希连接过滤。
集算器实现：
| A | B | |
| 1 | 1995-10-01 | =after@m(A1,3) |
| 2 | =file(LINEITEM).cursor@b(L_ORDERKEY,L_COMMITDATE,L_RECEIPTDATE) | /在LINEITEM表所对应的集文件上定义游标，参数为选出列 |
| 3 | =A2.select(L_COMMITDATE < L_RECEIPTDATE) | /对游标附加过滤操作 |
| 4 | =A3.groups(L_ORDERKEY) | /对L_ORDERKEY去重 |
| 5 | =file(ORDERS).cursor@b(O_ORDERKEY,O_ORDERDATE,O_ORDERPRIORITY) | /在ORDER表所对应的集文件上定义游标，参数为选出列 |
| 6 | =A5.select(O_ORDERDATE>=A1 && O_ORDERDATE < B1) | /对游标附加过滤操作 |
| 7 | =A6.join@i(O_ORDERKEY, A4:L_ORDERKEY) | /对ORDERS游标进行连接过滤,@i选项表示内连接 |
| 8 | =A7.groups(O_ORDERPRIORITY; count(1):O_COUNT) | /对游标计算分组得到最终结果 |
子查询结果集内存放不下
SQL示例（5）：
select
O_ORDERPRIORITY, count(*) as O_COUNT
from
ORDERS
where
O_ORDERDATE >= date ‘1995-10-01’
and O_ORDERDATE < date ‘1995-10-01’ + interval ‘3’ month
and exists (
select
*
from
LINEITEM
where
L_ORDERKEY = O_ORDERKEY
and L_COMMITDATE < L_RECEIPTDATE
)
group by
O_ORDERPRIORITY
优化思路：
等值EXISTS相当于对内部表关联字段去重然后跟外层表做内连接，而做连接效率较好的就是哈希连接和有序归并连接，所以这个问题就变成了怎么把EXISTS翻译成高效的连接，下面我们来分析在不同的数据分布下如何把EXISTS转成连接。
1、外层表数据量比较小可以装入内存：
先读入外层表，如果外层表关联字段不是逻辑主键则去重，再拿上一步算出来的关联字段的值对子查询做哈希连接过滤，最后拿算出来的子查询关联字段的值对外层表做哈希连接过滤。
2、外层表和内层表按关联字段有序：
此时可以利用函数joinx()来做有序游标的归并连接，如果内层表关联字段不是逻辑主键则需要先去重。此例中的ORDERS表和LINEITEM表是按照ORDERKEY同序存放，可以利用此方法来做优化。
3、内层表是大维表并且按主键有序存放：
集算器提供了针对有序大维表文件做连接的函数A.joinx，其它方法跟内存能放下时的处理类似在此不再描述。
集算器实现（1）：
| A | B | |
| 1 | 1995-10-01 | =after@m(A1,3) |
| 2 | =file(ORDERS).cursor@b(O_ORDERKEY,O_ORDERDATE,O_ORDERPRIORITY) | /在ORDER表所对应的集文件上定义游标，参数为选出列 |
| 3 | =A2.select(O_ORDERDATE>=A1 && O_ORDERDATE < B1).fetch() | /对游标附加过滤操作并取数 |
| 4 | =file(LINEITEM).cursor@b(L_ORDERKEY,L_COMMITDATE,L_RECEIPTDATE) | /在LINEITEM表所对应的集文件上定义游标，参数为选出列 |
| 5 | =A4.select(L_COMMITDATE < L_RECEIPTDATE).join@i(L_ORDERKEY,A3:O_ORDERKEY) | /对游标附加过滤操作和链接过滤操作 |
| 6 | =A5.groups(L_ORDERKEY) | /对L_ORDERKEY去重 |
| 7 | =A3.join@i(O_ORDERKEY, A6:L_ORDERKEY) | /对排列执行链接过滤操作 |
| 8 | =A7.groups(O_ORDERPRIORITY;count(1):O_COUNT) | /对排列计算分组得到最终结果 |
集算器实现（2）：
| A | B | |
| 1 | 1995-10-01 | =after@m(A1,3) |
| 2 | =file(ORDERS).cursor@b(O_ORDERKEY,O_ORDERDATE,O_ORDERPRIORITY) | /在ORDER表所对应的集文件上定义游标，参数为选出列 |
| 3 | =A2.select(O_ORDERDATE>=A1 && O_ORDERDATE < B1) | /对游标附加过滤操作 |
| 4 | =file(LINEITEM).cursor@b(L_ORDERKEY,L_COMMITDATE,L_RECEIPTDATE) | /在LINEITEM表所对应的集文件上定义游标，参数为选出列 |
| 5 | =A4.select(L_COMMITDATE < L_RECEIPTDATE) | /对游标附加过滤操作 |
| 6 | =A5.group@1(L_ORDERKEY) | /按L_ORDERKEY去重 |
| 7 | =joinx(A3:ORDER, O_ORDERKEY; A6, L_ORDERKEY) | /对有序游标执行内连接 |
| 8 | =A7.groups(ORDER.O_ORDERPRIORITY:O_ORDERPRIORITY;count(1):O_COUNT) | /对游标计算分组得到最终结果 |
EXISTS非等值条件
同表关联
SQL示例（6）：
select
L_SUPPKEY, count(*) as numwait
from
LINEITEM L1,
where
L1.L_RECEIPTDATE > L1.L_COMMITDATE
and exists (
select
*
from
LINEITEM L2
where
L2.L_ORDERKEY = L1.L_ORDERKEY
and L2.L_SUPPKEY <> L1.L_SUPPKEY
)
and not exists (
select
*
from
LINEITEM L3
where
L3.L_ORDERKEY = L1.L_ORDERKEY
and L3.L_SUPPKEY <> L1.L_SUPPKEY
and L3.L_RECEIPTDATE > L3.L_COMMITDATE
)
group by
L_SUPPKEY
优化思路：
我们先来看一下LINEITEM表的数据特点，LINEITEM表的主键是L_ORDERKEY、L_LINENUMBER，一个订单对应LINEITEM里的多条记录，这些记录的L_ORDERKEY是相同的并且在数据文件中是相邻的。知道这些信息后再来分析上面的SQL，其条件是为了找出有多个供应商供货并且有且仅有一个供应商没有按时交货的订单，因为数据是按订单顺序存放的，这样我们就可以按订单有序分组，然后循环每组订单判断是否有没按时交货的订单项，是否有多个供货商，并且是不是只有一个供应商没有按时交货。
集算器实现：
| A | B | |
| 1 | =file(LINEITEM).cursor@b(L_ORDERKEY,L_SUPPKEY,L_RECEIPTDATE,L_COMMITDATE) | /在LINEITEM表所对应的集文件上定义游标，参数为选出列 |
| 2 | =A1.group(L_ORDERKEY) | /对有序游标附加分组,结果为排列构成的游标 |
| 3 | =A2.conj((t=~.select(L_RECEIPTDATE > L_COMMITDATE),
if(t.len() > 0 && t.select@1(t(1).L_SUPPKEY!=L_SUPPKEY) == null && ~.select@1(t(1).L_SUPPKEY!=L_SUPPKEY) != null,t,null))) | /选出每一组中没有按时发货的订单赋值给临时变量t，如果t长度大于0并且t中的供应商只有一个并且此组中供应商有多个则返回t，否则返回null，conj相当于group的逆操作 |
| 4 | =A3.groups@u(L_SUPPKEY;count(1):numwait) | /对游标计算分组得到最终结果 |
总结
在没有空值的时候带子查询的IN都可以用EXISTS描述，同一个查询需求用IN描述和用EXISTS描述翻译成的集算器代码是相同的，所以我们只要弄清楚EXISTS怎么翻译和优化就知道IN怎么处理了。
等值exist本质上是做连接，两个表做连接效率较好的两种方式是哈希连接和有序归并连接，对于翻译select *** from A where exists (select *** from B where ***)样式的SQL，我们首先要弄清楚下列信息：
（1）关联字段是否是各表的主键或者逻辑主键
（2）A、B表的规模，执行其它过滤条件后是否能载入内存
（3）如果没有某个表能装入内存则要考察两个表是否按关联字段有序
如果有一个表能载入内存则可以选用哈希连接的方式来实现，相关的集算器函数有两个cs.switch()、cs.join()，这两个函数有两个可用的选项@i、@d分别对应exists和not exists，参数里的表要求按关联字段值唯一，如果不是逻辑主键则要先去重，可用A.groups()去重。如果两个表都很大不能载入内存则要考察两个表是否按关联字段有序，如果无序可以用cs.sortx()排序，对于有序的两个表就可以用joinx()来做连接了。
非等值运算则要分析其中的运算逻辑看能否转成分组后再计算，如果不能则只能使用嵌套循环连接的方式了，对应的函数是xjoin()。
知道这些信息并熟练掌握集算器相关的几个函数后我们就能够写出高效的代码。
569
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
