一對多查詢「只取最新一條」的三種模式

用戶表 users 1:N 日誌表 login_log，需求：列出每個用戶最新一條登錄記錄。直接 JOIN 會產生笛卡爾積，導致行數爆炸、排序緩慢。本文對比三種方案優劣。

場景建模

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50)
);

CREATE TABLE login_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    ip VARCHAR(45),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_time (user_id, created_at DESC)
);

方案一：子查詢 + IN（直觀但易慢）

SELECT u.*, l.ip, l.created_at
FROM users u
JOIN login_log l ON u.id = l.user_id
WHERE l.id IN (
    SELECT MAX(id) FROM login_log GROUP BY user_id
);

⚠️ 缺點：IN 子查詢在 MySQL 5.7 前優化差，大表極慢；即使優化後，仍需掃描全表聚合。

方案二：派生表 JOIN（推薦，穩定高效）

SELECT u.*, d.ip, d.created_at
FROM users u
JOIN (
    SELECT user_id, ip, created_at
    FROM login_log
    WHERE (user_id, id) IN (
        SELECT user_id, MAX(id) FROM login_log GROUP BY user_id
    )
) d ON u.id = d.user_id;

或用 ROW_NUMBER()（MySQL 8.0+）：

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) AS rn
    FROM login_log
)
SELECT u.*, r.ip, r.created_at
FROM users u
JOIN ranked r ON u.id = r.user_id AND r.rn = 1;

✅ 優點：利用覆蓋索引 (user_id, id DESC) 直接定位每組第一行，避免全表排序。

方案三：應用層分批查（極大數據量專用）

1. 先查用戶列表：SELECT id FROM users WHERE ... LIMIT 1000;
2. 應用層收集 user_ids，拼 IN 查日誌：
   SELECT * FROM login_log WHERE user_id IN (...) ORDER BY user_id, id DESC;
3. 應用層按 user_id 分組取每組第一條。

✅ 優點：將壓力分散到應用層，避免大 JOIN 阻塞資料庫；適合千萬級日誌表。
⚠️ 缺點：業務邏輯下沉應用層，代碼複雜度上升。

索引設計鐵律

無論哪種方案，日誌表必須建立 (user_id, created_at DESC) 或 (user_id, id DESC) 組合索引，否則全表掃描無解。

總結選型表

數據量	推薦方案	關鍵點
< 10 萬	派生表 JOIN / ROW_NUMBER	SQL 完結，維護簡單
10 萬 - 500 萬	派生表 JOIN + 組合索引	穩定、可解釋計劃
> 500 萬	應用層分批查 + 緩存	避免長事務、鎖升級

避坑提醒

• 不要用 GROUP BY user_id 直接 SELECT * —— 非聚合欄位值不確定（ONLY_FULL_GROUP_BY 模式會報錯）。
• MAX(created_at) 取最新不如 MAX(id) 穩，因時間可能重複、回撤。
• 若只需「是否有登錄記錄」，用 EXISTS 而非 JOIN。