如何使用外部程序优化SQL语句中的IN和EXISTS

大家好，关于如何使用外部程序优化SQL语句中的IN和EXISTS很多朋友都还不太明白，不过没关系，因为今天小编就来为大家分享关于的知识点，相信应该可以解决大家的一些困惑和问题，如果碰巧可以解决您的问题，还望关注下本站哦，希望对各位有所帮助！

IN常数集

SQL示例（一）：

选择P_SIZE、P_TYPE、P_BRAND、count(1) 作为P_COUNTfrom PART，其中P_SIZE in (2, 3, 8, 15, 17, 25, 27, 28, 30, 38, 41, 44, 45) 和P_TYPE in (‘SMALL BRUSHED NICKEL’, ‘SMALL POLISHED STEEL’) 和P_BRAND 不在(‘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)判断该字段是否在序列中。经验表明，当元素数量超过10个时，二分查找会明显快于顺序查找。如果要使用二分查找，需要先对序列进行排序，然后使用A.contain@b(f) 进行有序查找。 NOT IN 对应于！ A.包含(f)。注意，序列必须定义在循环函数之外，否则会执行多次。

如果常量集合元素数量特别多，可以使用连接过滤。详情请参考下面的代码。

集算器实现：

如果A1中的元素数量特别多，可以使用哈希连接的方法进行过滤，将第三行代码替换为如下：

IN 子查询

子查询选择的字段为主键

SQL示例（2）：

select PS_SUPPKEY, count(1) as S_COUNTfrom PARTSUPPwhere PS_PARTKEY in ( select P_PARTKEY from PART where P_NAME like ‘bisque%%’ )group by PS_SUPPKEY 优化思路：

子查询经过过滤后读入内存，然后与先读取的内存表（子查询）进行哈希外表进行过滤。集算器提供了switch@i()和join@i()两个函数用于哈希连接过滤。 switch是外键连接，用于将外键字段转为索引字段，这样就可以通过哈希连接来过滤外键字段。键字段直接指的是指向表的字段。 join函数不会改变外键字段的值，只能用于过滤。

集算器实现：

子查询选择的字段不是主键

SQL示例（3）：

从ORDERS 中选择O_ORDERPRIORITY、count(*) 作为O_COUNT，其中O_ORDERDATE=日期’1995-10-01′ 和O_ORDERDATE 日期’1995-10-01′ + 间隔’3′ 个月和O_ORDERKEY ( select L_ORDERKEY from LINEITEM where L_COMMITDATE L_RECEIPTDATE )group通过O_ORDERPRIORITY优化思路：

子查询过滤后，根据相关字段重新读入内存，然后就变成了类似于主键的情况。可以继续使用上面提到的switch@i()和join@i()这两个函数进行哈希连接过滤。

集算器实现：

子查询结果集无法存储

SQL示例(3)：

选择O_ORDERPRIORITY、count(*) 作为O_COUNTfrom ORDERS，其中O_ORDERDATE=日期’1995-10-01′ 和O_ORDERDATE 日期’1995-10-01′ + 间隔’3′ 个月和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函数用于连接有序的大维表文件。其他方法与可分配内存时的处理类似，此处不再赘述。

集算器实现（一）：

集算器实现（二）：

EXISTS 等价条件

本章的优化思路与IN子查询相同。其实这种EXISTS也可以写成IN（或者反过来，IN写成EXISTS）。

子查询相关字段为主键

SQL示例（4）：

select PS_SUPPKEY, count(1) as S_COUNTfrom PARTSUPPwhere contains ( select * from PART where P_PARTKEY=PS_PARTKEY and P_NAME like ‘bisque%%’ )group by PS_SUPPKEY 优化思路：

集算器实现：

子查询相关字段不是主键

SQL示例（5）：

select O_ORDERPRIORITY, count(*) as O_COUNTfrom ORDERSwhere O_ORDERDATE=日期’1995-10-01′ 且O_ORDERDATE 日期’1995-10-01′ + 间隔’3′ 个月且存在（ select * from LINEITEM where L_ORDERKEY=O_ORDERKEY 和L_COMMITDATE L_RECEIPTDATE )group by O_ORDERPRIORITY 优化思路：

集算器实现：

子查询结果集无法存储

SQL示例（5）：

选择

O_ORDERPRIORITY，计数(*) 为O_COUNT

从

订单

在哪里

O_ORDERDATE=日期’1995-10-01′

和O_ORDERDATE 日期’1995-10-01′ + 间隔’3′ 个月

并且存在（

选择

从

行项目

在哪里

L_ORDERKEY=O_ORDERKEY

和L_COMMITDATE L_RECEIPTDATE

）

分组依据

O_ORDERPRIORITY

优化思路：

等价的EXISTS相当于对内表的关联字段进行去重，然后与外表进行inner join。比较高效的连接是哈希连接和有序合并连接，那么问题就变成了如何将EXISTS转换为高效连接，我们来分析一下在不同数据分布下如何将EXISTS转换为连接。

1、外表的数据量比较小，可以加载到内存中：

2、外表和内表按相关字段排序：

这时，可以使用函数joinx()来合并有序游标。如果内表关联字段不是逻辑主键，则需要先进行去重。在此示例中，ORDERS 表和LINEITEM 表的存储顺序与ORDERKEY 相同。该方法可用于优化。

3、内表是大维表，按照主键顺序存储：

集算器提供了A.joinx函数用于连接有序的大维表文件。其他方法与可分配内存时的处理类似，此处不再赘述。

集算器实现（一）：

集算器实现（二）：

EXISTS 非等效条件

与同一个表相关

SQL示例（6）：

选择

L_SUPPKEY, count(*) 作为numwait

从

线路项目L1,

在哪里

L1.L_RECEIPTDATE L1.L_COMMITDATE

并且存在（

选择

从

线路项目L2

在哪里

L2.L_ORDERKEY=L1.L_ORDERKEY

和L2.L_SUPPKEY L1.L_SUPPKEY

）

并且不存在（

选择

从

线路项目L3

在哪里

L3.L_ORDERKEY=L1.L_ORDERKEY

和L3.L_SUPPKEY L1.L_SUPPKEY

和L3.L_RECEIPTDATE L3.L_COMMITDATE

）

分组依据

L_SUPPKEY

优化思路：

我们首先看一下LINEITEM表的数据特征。 LINEITEM 表的主键是L_ORDERKEY 和L_LINENUMBER。一张订单对应LINEITEM中的多条记录。这些记录的L_ORDERKEY相同并且在数据文件中相邻。知道了这些信息之后，我们来分析一下上面的SQL。条件是找出多个供应商供货而只有一个供应商未按时交货的订单，因为数据是按订单顺序存储的，所以我们可以将订单按顺序分组，然后对每一组进行循环订单数量，以确定是否有订单项目未按时交货、是否有多个供应商、以及是否只有一个供应商未按时交货。

集算器实现：

总结

当没有空值时，带有子查询的IN 可以用EXISTS 来描述。 IN和EXISTS翻译的相同查询需求，翻译成相同的集算器代码，所以我们只需要弄清楚如何翻译和优化EXISTS即可。如何应对IN。

等价存在本质上是一个连接。连接两个表的两种更有效的方法是散列连接和有序合并连接。对于翻译，select *** from A where isn’t（select *** from B where * **）风格的SQL，我们首先要明确以下信息：

(1)关联字段是否为各表的主键或逻辑主键

(2)表A和B的大小，以及执行其他过滤条件后能否加载到内存中。

(3) 如果没有表可以加载到内存中，请检查两个表是否按关联字段排序。

如果有一个可以加载到内存中的表，可以使用哈希连接来实现。集算器相关函数为cs.switch()和cs.join()。这两个功能有两个可用选项。 @i和@d分别对应存在和不存在。根据关联字段值要求参数中的表是唯一的。如果不是逻辑主键，则必须先进行去重。 A.groups() 可用于重复数据删除。如果两个表太大而无法加载到内存中，则需要检查两个表是否按关联字段排序。如果它们乱序，您可以使用cs.sortx() 对它们进行排序。对于两个有序表，可以使用joinx() 进行连接。

对于不等价的运算，需要先分析运算逻辑，看是否可以进行分组后再进行计算。如果没有，则只能使用嵌套循环连接。对应的函数是xjoin()。