MySQL 一對多關聯查詢優化：子查詢篩選最新日誌 vs 連接查詢性能對比

場景：users (一) 關聯 auth_token_log (多)，需查詢每個用戶**最新一條**日誌記錄。本文對比「原始子查詢法」與「優化分組子查詢法」的執行計劃差異，揭示 MySQL 依賴子查詢去關聯時的性能陷阱，給出標準化的 JOIN + 派生表 最佳實踐。

表結構與數據量假設

• users：10 萬行，PK id。
• auth_token_log：500 萬行，PK id，索引 user_id、created_date。

方案一：原始相關子查詢 (性能差)

SELECT
    u.first_name, u.email_address, u.tp_user_id, u.tp_username,
    l.module_access, l.created_date
FROM users u
INNER JOIN auth_token_log l ON u.id = l.user_id
WHERE l.id IN (
    SELECT MAX(id)
    FROM auth_token_log
    WHERE user_id = u.id  -- 相關子查詢，外部引用 u.id
);

執行邏輯與缺陷

1. 掃描 users (10萬行)。
2. 對每行 u，進入子查詢：SELECT MAX(id) FROM auth_token_log WHERE user_id = u.id。
3. 子查詢雖用上 user_id 索引，但**執行 10 萬次**，上下文切換、索引查找開銷巨大。
4. 主查詢 JOIN 時 l.id IN (...) 再次檢查。

複雜度：O(N_users × log M_logs) → 全量用戶場景不可接受。

方案二：優化分組子查詢 (仍有隱患)

SELECT
    u.first_name, u.email_address, u.tp_user_id, u.tp_username,
    l.module_access, l.created_date
FROM users u
INNER JOIN auth_token_log l ON u.id = l.user_id
WHERE l.id IN (
    SELECT MAX(l2.id)
    FROM auth_token_log l2
    JOIN users u2 ON l2.user_id = u2.id  -- 顯式 JOIN 替換相關引用
    GROUP BY u2.id
);

改進點

• 子查詢不再相關，改為獨立聚合：GROUP BY u2.id 一次性產出每用戶最大 ID。
• 但 IN 子查詢在 MySQL 5.7/8.0 優化器中可能仍物化為臨時表，再做 JOIN，中間物化開銷存在。

方案三：標準派生表 JOIN (最佳實踐，MySQL 8.0+ CTE 更優)

MySQL 5.7 寫法

SELECT
    u.first_name, u.email_address, u.tp_user_id, u.tp_username,
    latest.module_access, latest.created_date
FROM users u
JOIN (
    SELECT l.user_id, l.module_access, l.created_date
    FROM auth_token_log l
    JOIN (
        SELECT user_id, MAX(id) AS max_id
        FROM auth_token_log
        GROUP BY user_id
    ) m ON l.user_id = m.user_id AND l.id = m.max_id
) latest ON u.id = latest.user_id;

MySQL 8.0+ CTE 寫法 (可讀性最強)

WITH latest_log AS (
    SELECT l.user_id, l.module_access, l.created_date
    FROM auth_token_log l
    JOIN (
        SELECT user_id, MAX(id) AS max_id
        FROM auth_token_log
        GROUP BY user_id
    ) m ON l.user_id = m.user_id AND l.id = m.max_id
)
SELECT
    u.first_name, u.email_address, u.tp_user_id, u.tp_username,
    ll.module_access, ll.created_date
FROM users u
JOIN latest_log ll ON u.id = ll.user_id;

性能對比實測 (模擬數據)

進階：窗口函數 (MySQL 8.0+ 終極簡潔)

WITH ranked AS (
    SELECT
        u.first_name, u.email_address, u.tp_user_id, u.tp_username,
        l.module_access, l.created_date,
        ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY l.id DESC) AS rn
    FROM users u
    JOIN auth_token_log l ON u.id = l.user_id
)
SELECT first_name, email_address, tp_user_id, tp_username, module_access, created_date
FROM ranked
WHERE rn = 1;

優勢：單次掃描、無派生表、優化器可下推 predicate、代碼最乾淨。

應用層替代方案 (原文作者採用)：分兩次查詢 + 內存組裝

# 1. 查用戶列表
users = SELECT id, first_name, email_address, tp_user_id, tp_username FROM users WHERE ...;

# 2. 批量查最新日誌 (IN 列表或臨時表)
user_ids = [u.id for u in users]
logs = SELECT user_id, module_access, created_date
       FROM auth_token_log
       WHERE user_id IN (user_ids)
         AND id IN (SELECT MAX(id) FROM auth_token_log GROUP BY user_id)
       ORDER BY user_id, id DESC;

# 3. 應用層 Map 組裝
log_map = {log.user_id: log for log in logs}  # 取每用戶第一條(已按id DESC)
for u in users:
    u.latest_log = log_map.get(u.id)

適用場景**：用戶列表已分頁 (如 20 行/頁)、日誌表極大、允許應用層額外一次查詢。
避坑：IN 列表過長 (>1000) 需分批或用臨時表 JOIN。

總結建議

1. 單條 SQL 首選：MySQL 8.0 用 ROW_NUMBER() 窗口函數；5.7 用 派生表 JOIN 最大ID。
2. 嚴禁相關子查詢：WHERE col IN (SELECT ... WHERE outer_col = ...) 在大表上是性能殺手。
3. 應用層分兩次查詢：僅在分頁列表、極大關聯表、團隊 SQL 能力有限時作權宜，並控制 IN 列表長度。
4. 索引必備：auth_token_log(user_id, id DESC) 覆蓋聚合與排序。