GaussDB(DWS) NOT IN 优化技术解密：排他分析场景 400 倍性能提升
本文分享自华为云社区《排他分析场景400倍性能提升-GaussDB(DWS) 独家NOT IN优化技术解密【这次高斯不是数学家】》，作者：两杯咖啡。
对于金融类客户业务来说，经常会出现类似基于某些条件排他的查找，例如：基于客户 ID、客户 ID 和业务 ID 的组合，查找不在某个特征范围内的用户集合等等。此类查询特定记录的使用场景，可以使用 NOT IN 的语法来实现。NOT IN 场景在分析型数据库中被广泛使用，例如：GaussDB(DWS)的大客户：工商银行、招商银行、光大银行在业务场景中都有数量众多的 NOT IN 语句。这类语句一般在进行集合排他比较时使用，例如如下语句：
该语句的语义为：查找 a 值不在 t2 表中的所有 t1 表中的记录。
由于 NOT IN 对于 NULL 值的特殊处理，导致这类语句无法使用高效的 HashJoin 进行高效处理，性能比较差，调优门槛比较高，成为困扰大多数客户的一大难题。Teradata、Oracle 等数据库友商也针对 NOT IN 问题进行了大量探索，但始终未能完美解决该场景的性能问题。GaussDB(DWS)在 8.1.2 最新版本实现了独家的分布式 Mixed-HashJoin 的 NOT IN 优化技术，在招商银行联合创新项目中得到了应用，共有近 900 个作业（占作业总数的 3%）中包含的 NOT IN 语句性能平均提升 400 倍，招行生产集群单日所有作业端到端性能提升 15%，效果明显，解决了客户的痛点问题。
这篇文章针对 NOT IN 的使用方法进行介绍，希望广大用户都可以尝试使用 GaussDB(DWS)的 NOT IN 优化高级特性。
一. 数据库中的三值逻辑
提到 NOT IN，就不得不提到数据库中的三值逻辑。在数理逻辑中，我们用 true 和 false 的二值逻辑表示真假，而在现实世界中，会存在一些数据，目前是未知的，因此存储在数据库中是用 NULL 来表示的，遇到 NULL 值运算时，我们也无法判断其真假，故引入了第三值逻辑，即 NULL。注意，NULL 值不同于空串，因为空串是一个固定的值。当然，在 Oracle 兼容的模式下，空串是被视为空值的，但在 TD 和 MYSQL 兼容模式下就是不等的。NULL 值与任意值的比较均未知，属于游离于 True 和 False 之外的第三值，通俗来说，NULL 值无法确定与任意值相等，但它可能是任意值，因此也无法确认 NULL 值与任意值不等。因此，如果需要查询 NULL 值，不能使用等值比较的方式，而应该使用 IS NULL 的形式，例如：
而 NOT IN 中的 IN 操作符，由于其与=的等价关系，导致 IN 和 NOT IN 操作符均不会包含 NULL 值，例如：
对于包含{1，-1，NULL}的数据表 t，对应以下的查询结果：
上述后两条语句返回 1，只有 1 符合条件。
上述两条语句返回 1，只有-1 符合条件；NULL 和 1 的等值比较为 NULL，取非后仍为 NULL，不为真。
上述语句返回 2，1 和-1 符合条件。
上述语句返回 0，任意值与 NULL 比较结果均为 NULL，非真。
返回 0，任意值比较结果 NULL 取非后仍为 NULL，非真。
二. NOT IN 的使用场景和示例
NOT IN 一般在特征提取时会有广泛使用。例如：我们需要查找不符合 A，B 两个属性中部分属性组成的相应条件的特定人群，可以将相应的条件插入一个表 t2 中，例如：满足 A=1 且 B=3，则将(1,3)插入表中；满足 B=4 时，A 值置为 NULL。然后将目标表和表 t2 进行 NOT IN 操作，含义为：查找(A,B)组合不为(1,3)、同时 B 不为 4 的元组。在金融行业中，经常出现基于某些条件组合进行用户的筛选的业务，条件组合中由于部分未知情况可能包含某些列的缺失，则业务逻辑就可以这样实现。
如上面例子所示，假如判断匹配的表 t2(a, b)中包含如上两条记录，而参与查找的表 t1(a,b)中包含 5 条记录：(1,3), (2,4), (2,6), (3,null), (null,5)。对于语句
在进行 NOT IN 运算时，NULL 值可以看成和任意值匹配，只有两列中存在不匹配的列时，NOT IN 返回 true。t1 各元组和 t2 目标表的匹配情况（如箭头所示）及输出结果 result 如下图所示。
三. NOT IN 与 NOT EXISTS 的区别
某些数据库的用户还知道 NOT EXISTS 的用法，和 NOT IN 很相似，但是有一定区别。例如：上例的 NOT IN 语句可以改写成下面的语句：
同样是获取不满足在某个范围内的元组，对于上例的输入，输出结果为 4 条元组，仅不包含(1,3)。为什么会这样呢？
通过上面的分析，我们可以知道，NOT IN 中 IN 使用等值（=）比较，而 NOT IN 则使用不等值（<>）比较。对比起来，EXISTS 同样使用等值（=）比较，而 NOT EXISTS 则等价于等值（=）比较取反，即 EXISTS 和 NOT EXISTS 是互补的。因此，IN 和 EXISTS 是等价的，而 NOT IN 和 NOT EXISTS 是不等价的，两者之间差了 NULL 的处理，如下图所示。
从另一个角度来理解，NOT IN 运算相当于 NULL 值的强过滤，均不输出。NOT EXISTS 运算则相当于不进行 NULL 值过滤，均输出。对比两个语句的执行计划，可以看出 Join 条件上的差别。由于 NOT IN 在内核使用 Anti Join（反连接运算）来实现，即元组不匹配才输出，因此条件上也增加了 NULL 值返回 true 的条件。
四. GaussDB(DWS) NOT IN 优化技术
NOT IN 性能问题是业务公认的技术难题，友商 Teradata 和 Oracle 均针对 NOT IN 进行了部分场景的优化，即 Null-aware 技术，针对单列的 NOT IN 问题进行了 NULL 干预，但对于多列 NOT IN 问题仍然存在各种已知问题。下图为友商在 NOT IN 场景下，分布式以及单列/多列 NOT IN 的调研结论。
GaussDB(DWS)也一直致力于该问题的求解，新版本针对 NOT IN 有两个优化技术。
NOT NULL 约束识别。
通过上面的分析我们可以看出，NOT IN 运算需要增加额外的 NULL 值判断，出现的 OR 条件导致必须通过低效的 NestLoop 计算。GaussDB(DWS)可以根据用户定义的 NOT NULL 约束来自动检测去掉 NULL 值判断，例如：上面的语句中，如果 t1 表的 a 列，及 t2 表的 a 列上均有 NOT NULL 约束，则条件 t1.a=t2.a 不再包含 NULL 值判断的 OR 条件，可以转化为高效的 HashJoin 来进行处理。同时，GaussDB(DWS)也支持部分表达式的 NULL 值推导，只要基表列上包含 NOT NULL 约束，参与 NOT IN 运算的表达式也可以由于推导出的 NOT NULL 约束进行计划层的优化。
分布式 Mixed-HashJoin 技术。
如果 NOT IN 运算的列值中包含 NULL 值，则必须采取对 NULL 值的单独处理来解决问题。GaussDB(DWS) 8.1.2 版本实现了分布式 Mixed-HashJoin 技术，在执行时各 DN 可以动态分离出包含 NULL 值的元组，进行 NestLoop 特殊匹配处理；而对于非 NULL 值，则可以使用高效的 HashJoin 来进行执行。由于业务中的 NULL 值为不确认因素，所占的比例较少，因此，该技术可以保证该类场景的性能优势最大化，在 NULL 值很少的场景，性能和 NOT EXISTS 持平。新版本执行计划如下所示：
该技术目前已经支持向量化引擎，后续将针对行引擎进行进一步的完善。同时，针对不同列的 NULL 值情况，也可以建多个 NULL 值的 hash 表加速匹配，避免 NULL 值过多时使用 NestLoop 仍然过慢。
同时，由于外表的 NULL 值可能匹配到内表的任意值，因此通常需要将内表进行广播（Broadcast）操作。如果内表较大，则占用较多网络资源且影响性能。此时，如果外表在 NOT In 的某列上有 NOT NULL 约束，内表可以在该列上对非 NULL 值进行重分布，仅广播 NULL 值，减少网络数据发送量。例如，如果上例中，t1 表的 a 列包含 NOT NULL 约束，则会生成如下的计划（t2 表对应的 a 列进行了重分布，同时将 NULL 值广播）：
五. NOT IN 场景的调优手段
如果使用 GaussDB(DWS)早期版本的用户也不必着急，本章介绍一些 NOT IN 场景的调优手段，供大家在实践中使用。
修改 NOT IN 为 NOT EXISTS
上文详细分析了 NOT IN 和 NOT EXISTS 的区别。因此，如果用户可以通过自身的业务逻辑，确认 NOT EXISTS 的语义也可以满足，通常可能是因为对于 NULL 值的处理不关心，或者数据中根本不存在 NULL 值，则可以通过等价改写将 NOT IN 改写为 NOT EXISTS 来进行优化。通用改写方法为：
为基表列增加 NOT NULL 约束
由于 GaussDB(DWS)在早期版本中即支持 NULL 值的推导逻辑，因此可以通过对 NOT IN 运算的基表列增加 NOT NULL 约束，将 OR 条件转化为等值条件进行优化。注意，对于多列的 NOT IN 场景，仅需要将内外表对应的一列均增加 NOT NULL 约束即可进行调优。例如上例，可以单独为 col1 和 c1 增加 NOT NULL 约束，也可以为 coln 和 cn 增加 NOT NULL 约束，以此类推。也可以在 SQL 语句里显式增加 IS NOT NULL 的条件来过滤掉无用的 NULL 值，或者提示优化器该列上的非空约束，例如：select * from t1 where (a,b) not in (select a, b from t2 where a is not null) and a is not null;
使用 Mixed-HashJoin 新技术
8.1.2 版本中，由于分布式 Mixed-HashJoin 技术仅支持向量化引擎，因此可以通过将语句中涉及的表均创建为列存表，并设置参数 rewrite_rule 包含’notinopt’值，即可使用新的技术。由于该参数为多值参数，因此，需要通过 show rewrite_rule 命令查看当前设置的值（如未设置则为默认值），通过在其后添加’notinopt’值进行设置。关于 rewrite_rule 的其它值，后续将在其它文章中介绍。
如果用户使用的表为行存表，GaussDB(DWS)还提供参数 enable_force_vector_engine 强制使用向量化引擎处理，同样可以使用新技术。该参数为 bool 值，默认为 off。以上两个参数均可以 session 级设置生效。
进一步地，为了减少内表广播带来的资源消耗，如果在外表某些 NOT IN 列理论上不为空的情况下，可以为其中某些列增加 NOT NULL 约束，或在语句中指定 IS NOT NULL 条件，则可以通过数据重分布减少网络发送量，进一步提升性能。
六. 结语
通过本文的分析，相信用户朋友已经充分了解了分析型业务排他操作-NOT IN 的使用场景、SQL 语法，以及 GaussDB(DWS)的 NOT IN 实现方式，可行的调优方法。希望广大用户能够通过深入的了解，对 GaussDB(DWS)的性能调优产生浓厚的兴趣并深度参与进来。如 NOT IN 问题的攻克一样，GaussDB(DWS)目前正着力解决其它棘手的性能问题，期待在其它场景中，也可以给用户带来极致的性能体验，减少用户调优的成本。
理论不如实践，那如何快速体验 DWS 呢？DWS 现推出了一项 Demo 体验活动。进入DWS首页，点击“Demo体验”，快速便捷体验一把！
体验过程中有任何建议和意见，可以去DWS社区论坛反馈哦；）
版权声明: 本文为 InfoQ 作者【华为云开发者社区】的原创文章。
原文链接:【http://xie.infoq.cn/article/c05ee5c2eaffd3ab450cc516b】。文章转载请联系作者。
提供全面深入的云计算技术干货 2020.07.14 加入
华为云开发者社区，提供全面深入的云计算前景分析、丰富的技术干货、程序样例，分享华为云前沿资讯动态，方便开发者快速成长与发展，欢迎提问、互动，多方位了解云计算！ 传送门：https://bbs.huaweicloud.com/
评论