場景：查找存在於表 A 但不存在於表 B 的記錄

業務常見需求：有兩張結構相似的表 t_appinfo（主表）和 g_appinfo（備表），均含 package_name 字段。需要找出 在 t_appinfo 存在、但 g_appinfo 缺失 的記錄。

方案一：NOT EXISTS 子查詢（推薦）

SELECT a.name, a.package_name
FROM t_appinfo a
WHERE NOT EXISTS (
    SELECT 1 FROM g_appinfo g WHERE a.package_name = g.p_packagename
)
LIMIT 10000;

優勢分析

• 語義清晰：直觀表達「不存在」邏輯
• NULL 安全：子查詢返回 NULL 時不影響結果正確性
• 優化器友好：MySQL 可轉換為 Anti-Join，配合索引效率極高

方案二：NOT IN 子查詢（需謹慎）

SELECT a.name, a.package_name
FROM t_appinfo a
WHERE a.package_name NOT IN (
    SELECT g.p_packagename FROM g_appinfo g
)
LIMIT 50000;

潛在陷阱

⚠ 當子查詢結果集包含 NULL 時，NOT IN 將返回空結果集！
原理：value NOT IN (1, 2, NULL) 等價於 value != 1 AND value != 2 AND value != NULL，最後一個條件永遠為 UNKNOWN，導致整體為 FALSE。

適用前提

• 被查詢字段 明確定義為 NOT NULL
• 或子查詢加過濾：WHERE p_packagename IS NOT NULL

大數據量場景的索引優化策略

當兩表均超 10 萬行，必須為 package_name / p_packagename 建立索引：

ALTER TABLE t_appinfo ADD INDEX idx_pkg_name (package_name);
ALTER TABLE g_appinfo ADD INDEX idx_p_pkg_name (p_packagename);

執行計劃驗證

EXPLAIN SELECT ... WHERE NOT EXISTS (...);

關鍵指標：

• type 列顯示 ref 或 eq_ref 表示索引生效
• rows 估算掃描行數應遠小於全表行數
• Extra 出現 Using where; Not exists 為最佳

進階替代方案：LEFT JOIN + IS NULL

SELECT a.name, a.package_name
FROM t_appinfo a
LEFT JOIN g_appinfo g ON a.package_name = g.p_packagename
WHERE g.p_packagename IS NULL;

在某些舊版本 MySQL 中，此寫法優化器處理更穩定；新版本（5.7+）三種寫法經優化器重寫後性能幾乎一致。

總結建議

1. 首選 NOT EXISTS：語義明確、NULL 安全、性能穩健
2. 字段加 NOT NULL 約束 + 索引：從源頭消除 NOT IN 隱患並提速
3. 避免 SELECT *：只查詢業務需要的列，減少 I/O
4. 分批處理：超大結果集配合 LIMIT 分頁導出，避免長事務阻塞