MySQL 8.0.17版本引入了一個antijoin的優化，這個優化能夠將where條件中的not in(subquery)， not exists(subquery)，in(subquery) is not true，exists(subquery) is not true，在內部轉化成一個antijoin(反連接），以便移除裡面的子查詢subquery，這個優化在某些場景下，能夠將性能提升20%左右。
原文地址：mytecdb.com/blogDetail.php?id=108
1. antijoin使用場景
antijoin使用的場景案例通常如下：
找出在集合A且不在集合B中的數據找出在當前季度裡沒有購買商品的客戶找出今年沒有通過考試的學生找出過去3年，某個醫生的病人中沒有進行醫學檢查的部分上面這些場景，以第4個為例，轉換成一個SQL，通常如下：
如果SQL按照這種形式去寫，通常沒有太多的優化空間。我們需要從patients表讀取每條記錄，對於每條記錄，帶到子查詢中，檢查是否滿足條件。因為子查詢的where子句依賴patients.patient_id，patients 表的每一條記錄遍歷，都會導致子查詢被重複執行，嚴重影響性能。
優化這種SQL的第一步就是打破上層查詢與子查詢之間的邊界，將後者也就是子查詢合併到上層查詢裡面。
看一下優化之後的SQL，如下：
優化後的SQL使用了antijoin關鍵字，它和join操作類似，join通常尋找滿足匹配條件的記錄，而antijoin尋找不匹配的記錄。更準確地說，它從左邊表選擇記錄，然後檢查右邊表，根據on條件，檢查是否沒有記錄能夠匹配上，如果沒有記錄匹配，那麼左邊的這條記錄就可以作為結果返回。
2. antijoin內部優化策略
MySQL有兩種策略用於執行antijoin。
First MatchMaterialization2.1 First Match策略
First Match 策略，從patients表中讀取一條記錄，然後在exams表中尋找匹配，如果沒有匹配上，則將這條記錄作為結果返回。這種方式與使用子查詢並沒有太大的區別。
2.2 Materialization策略
Materialization策略，對於上述SQL例子，ON子句有3個子條件，分別是
exams.type='check-up'exams.date>=date_sub(now(), interval 3 year)patients.patient_id=exams.patient_id3個條件中只有一個依賴patients表，所以MySQL可以創建一個臨時表tmp，這個臨時表由exams表按照前兩個條件過濾後的數據組成，就像下面這樣：
MySQL優化器會自動在tmp的patient_id欄位上添加索引，然後從patients表讀取記錄，使用索引匹配tmp表中的記錄，如果沒有匹配上，則返回這條記錄。
相對於First Match策略，Materialization策略主要有以下優勢：
exams表只讀取一次，用於創建tmp表。tmp表相對於exams表，有更少的記錄，訪問tmp表要比exams表更快。tmp表上由於創建了索引，訪問起來更快，而原始表exams很可能沒有索引。當然，這種策略創建臨時表，也有一些前期的成本消耗，比如需要申請內存來存儲臨時表數據，比如臨時表非常大，需要將臨時表存儲在磁碟上。因此兩種策略哪一種更好，依賴於具體的場景。幸運的是，MySQL有一個基於成本的優化器，通過計算兩種策略基於數據行數的成本，條件的選擇性，索引的使用，最終選擇成本最低的那個策略。
3. 總結
antijoin優化適用於not exists(subquery), not in(subquery)這類查詢，在某些場景下，能夠對這類SQL進行很好的優化和性能提高。antijoin有兩種執行策略，First Match和Materialization，優化器根據成本模型進行選擇。