MySQL 去重查詢優化：用 INNER JOIN 替代 IN 子查詢，效能提升 6 倍+

業務常見需求：「按用戶取最新一條記錄」。直覺寫法用 IN (SELECT MAX(...))，大數據下極慢。本文實測對比 IN vs INNER JOIN 兩種去重寫法，揭示優化器行為差異，給出索引建議與最佳實踐模板。

一、 業務場景與數據樣本

TABLE messages
id  uid  username  message  dateline
1   6    a         111      1284240714
2   6    a         222      1268840565
3   8    b         444      1266724527
4   9    c         555      1266723391

目標：每個 uid 保留 dateline 最大的一行，按時間倒序輸出。

二、 寫法對比

寫法 A：IN 子查詢 (直覺但慢)

SELECT * 
FROM messages 
WHERE dateline IN (
    SELECT MAX(dateline) FROM messages GROUP BY uid
)
ORDER BY dateline DESC;

寫法 B：INNER JOIN 派生表 (推薦)

SELECT a.* 
FROM messages a
INNER JOIN (
    SELECT MAX(dateline) AS dateline 
    FROM messages 
    GROUP BY uid
) b ON a.dateline = b.dateline
GROUP BY a.id
ORDER BY a.dateline DESC;

三、 實測效能差異 (百萬級測試)

四、 優化器原理深度解析

• IN (子查詢)： MySQL 5.6 前將其轉為 EXISTS 關聯，外表每行都執行一次內查詢 → DEPENDENT SUBQUERY，隨外表行數線性增長。
• INNER JOIN 派生表： 子查詢先獨立執行 → 物化為臨時表 (含索引) → 外表通過 REF 關聯查找 → 複雜度 O(N+M)。
• MySQL 8.0+： 引入「子查詢轉半連接」優化，IN 也能自動轉為 JOIN，但顯式 JOIN 仍更可控、可移植。

五、 索引設計建議

-- 覆蓋索引：滿足 GROUP BY + MAX + 覆蓋查詢
ALTER TABLE messages ADD INDEX idx_uid_dateline (uid, dateline);

• 派生表 SELECT MAX(dateline) ... GROUP BY uid 可直接走索引掃描 (Loose Index Scan)，無需回表。
• 外表關聯 a.dateline = b.dateline 若 dateline 非唯一，建議加上 uid 聯合關聯：ON a.uid = b.uid AND a.dateline = b.dateline。

六、 通用最佳實踐模板

場景 1：按分組取 Top-N (最新/最高/最低)

SELECT a.* 
FROM `table` a
INNER JOIN (
    SELECT group_col, MAX(sort_col) AS max_sort
    FROM `table` 
    GROUP BY group_col
) b ON a.group_col = b.group_col AND a.sort_col = b.max_sort
ORDER BY a.sort_col DESC;

場景 2：去重保留首行 (按自增 ID)

SELECT a.* 
FROM `table` a
INNER JOIN (
    SELECT MIN(id) AS min_id FROM `table` GROUP BY dedup_col
) b ON a.id = b.min_id;

場景 3：Window Function (MySQL 8.0+ 首選)

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY dateline DESC) AS rn
    FROM messages
) t WHERE rn = 1;

優勢： 單次掃描、無臨時表、語義清晰、易維護。生產環境 8.0+ 強烈推薦。

七、 總結

去重查詢 拒絕 IN 子查詢，擁抱 INNER JOIN / Window Function。理解優化器對 DEPENDENT SUBQUERY 与 DERIVED 的不同處理路徑，配合覆蓋索引，即可將秒級查詢壓入毫秒級。遷移至 MySQL 8.0 後，優先重寫為 ROW_NUMBER() 窗口函數，一勞永逸。