摘要：本文針對8.1.2版本中的NOT IN場景的Mixed-HashJoin新技術進行介紹。該技術在GaussDB(DWS)與招商銀行的聯創項目中落地，為招商銀行的批量作業帶來了總體15%的性能提升。
本文分享自華為雲社群《排他分析場景400倍性能提升-GaussDB(DWS) 獨家NOT IN優化技術解密【這次高斯不是數學家】》，作者：兩杯咖啡。
本文針對8.1.2版本中的NOT IN場景的Mixed-HashJoin新技術進行介紹。該技術在GaussDB(DWS)與招商銀行的聯創項目中落地，為招商銀行的批量作業帶來了總體15%的性能提升。同時，該檔案也同時介紹了NOT IN的使用場景和在GaussDB(DWS)中的調優手段，希望各位讀者試用該技術。
對于金融類客戶業務來說，經常會出現類似基于某些條件排他的查找，例如：基于客戶ID、客戶ID和業務ID的組合，查找不在某個特征範圍内的使用者集合等等。此類查詢特定記錄的使用場景，可以使用NOT IN的文法來實作。NOT IN場景在分析型資料庫中被廣泛使用，例如：GaussDB(DWS)的大客戶：工商銀行、招商銀行、光大銀行在業務場景中都有數量衆多的NOT IN語句。這類語句一般在進行集合排他比較時使用，例如如下語句：
select * from t1 where a not in (select a from t2); 該語句的語義為：查找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的形式，例如：
select * from t where a = NULL; --錯誤
select * from t where a 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)。對于語句
select * from t1 where (a,b) not in (select a,b from t2); 在進行NOT IN運算時，NULL值可以看成和任意值比對，隻有兩列中存在不比對的列時，NOT IN傳回true。t1各元組和t2目标表的比對情況（如箭頭所示）及輸出結果result如下圖所示。
三. NOT IN與NOT EXISTS的差別
某些資料庫的使用者還知道NOT EXISTS的用法，和NOT IN很相似，但是有一定差別。例如：上例的NOT IN語句可以改寫成下面的語句：
select * from t1 where (a,b) not in (select a,b from t2);
->
select * from t1 where not exists (select 1 from t2 where t1.a=t2.a and t1.b=t2.b); 同樣是擷取不滿足在某個範圍内的元組，對于上例的輸入，輸出結果為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來進行優化。通用改寫方法為：
… WHERE … (col1, col2, …, coln) NOT IN (SELECT c1, c2, …, cn FROM …) …
改寫為：
… WHERE NOT EXISTS(SELECT 1 FROM … WHERE col1=c1 AND col2=c2 AND … AND coln=cn ...) … - 為基表列增加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)目前正着力解決其它棘手的性能問題，期待在其它場景中，也可以給使用者帶來極緻的性能體驗，減少使用者調優的成本。