MySQL 中 NOT IN 與 MINUS 查詢優化實戰

優化背景與問題現象

在處理大數據量的差集查詢時，NOT IN 子查詢常因執行計劃退化為全表掃描而導致效能極差。本文記錄一個真實案例：原查詢耗時 35 分鐘，優化後僅需 19 秒，邏輯讀從 176 萬次暴跌至 2240 次。

優化前 SQL 與執行計劃分析

SELECT COUNT(t.id) 
FROM test t 
WHERE t.status = 1 
  AND t.id NOT IN (
    SELECT DISTINCT a.app_id 
    FROM test2 a 
    WHERE a.type = 1 
      AND a.rule_id IN (152, 153, 154)
  );

執行計劃顯示：

• 操作 FILTER 使用 LNNVL 函數處理 NOT IN 的 NULL 值邏輯
• 外層表 test 全表掃描 1141 行
• 內層表 test2 每行皆需全表掃描，導致嵌套循環極其低效
• 統計資訊：consistent gets 1,762,169，耗時 00:35:23

關鍵點：LNNVL 函數的作用

Oracle 將 NOT IN 轉換為 NOT EXISTS 並引入 LNNVL 處理三值邏輯：

• 當條件為 FALSE 或 UNKNOWN（含 NULL）時，LNNVL 返回 TRUE
• 當條件為 TRUE 時，返回 FALSE

此機制導致無法有效利用索引，形成「逐行過濾」的惡性循環。

優化後 SQL：使用 MINUS 集合運算

SELECT COUNT(*) 
FROM (
  SELECT t.id FROM test t WHERE t.status = 1
  MINUS
  SELECT DISTINCT a.app_id 
  FROM test2 a 
  WHERE a.type = 1 
    AND a.rule_id IN (152, 153, 154)
);

優化後執行計劃解讀

• 引入 MINUS 操作，觸發 SORT UNIQUE 去重
• 兩張表各自獨立全掃描後排序合併，避免嵌套循環
• 使用臨時表空間 TempSpc 3632K 進行排序運算
• 統計資訊：consistent gets 2,240，耗時 00:00:19，效能提升 99.9%

核心心得

1. 思維轉換：將「逐行過濾」改寫為「集合差集運算」
2. 等價改寫：MINUS（Oracle）/ EXCEPT（PostgreSQL/SQL Server）語義清晰，優化器易生成高效計劃
3. 適用場景：子查詢結果集較大、存在 NULL 值、外層表無合適索引支援反向連接時

同樣邏輯在 MySQL 8.0+ 可用 EXCEPT，MariaDB 10.3+ 支援 EXCEPT 與 MINUS 別名，舊版本可改用 LEFT JOIN ... WHERE IS NULL 或 NOT EXISTS 配合索引優化。