MySQL 利用 GROUP BY 分組取每組前 N 筆資料的實戰技巧

在業務開發中，「分組取前 N 筆」是極高頻需求：如每個用戶最新 5 筆訂單、每個分類點擊量前 10 的文章、每位銷售業績前 3 的客戶。MySQL 8.0 引入視窗函數（ROW_NUMBER()、RANK()、DENSE_RANK()）後，這類問題迎來了最優雅的解法。本文將從傳統寫法演進到現代最佳實踐，並對比效能差異。

場景定義

CREATE TABLE `orders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用戶ID',
  `amount` DECIMAL(10,2) NOT NULL COMMENT '訂單金額',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_created` (`user_id`, `created_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

需求：查詢每位用戶最新下單的 3 筆訂單（按 created_at 降序）。

方法一：MySQL 8.0+ 視窗函數（推薦，語法簡潔、效能最佳）

WITH ranked_orders AS (
  SELECT
    id, user_id, amount, created_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT id, user_id, amount, created_at
FROM ranked_orders
WHERE rn <= 3
ORDER BY user_id, created_at DESC;

• PARTITION BY user_id：以用戶分組
• ORDER BY created_at DESC：組內按時間倒序
• ROW_NUMBER()：同時間不並列，嚴格 1,2,3…；若需並列名次用 RANK() 或 DENSE_RANK()
• 優化器可下推 WHERE rn <= 3，配合索引 (user_id, created_at DESC) 只掃描必要行

方法二：用戶變數模擬視窗函數（MySQL 5.7 相容寫法）

SELECT id, user_id, amount, created_at
FROM (
  SELECT
    id, user_id, amount, created_at,
    @rn := IF(@prev_user = user_id, @rn + 1, 1) AS rn,
    @prev_user := user_id
  FROM orders
  CROSS JOIN (SELECT @rn := 0, @prev_user := 0) var_init
  ORDER BY user_id, created_at DESC
) t
WHERE rn <= 3;

缺點：依賴特定執行順序，MySQL 8.0+ 已棄用用戶變數此用法，易產生非確定性結果，不建議新專案使用。

方法三：關聯子查詢 + LIMIT（傳統寫法，小資料量可用）

SELECT o1.*
FROM orders o1
WHERE (
  SELECT COUNT(*)
  FROM orders o2
  WHERE o2.user_id = o1.user_id
    AND o2.created_at > o1.created_at
) < 3
ORDER BY o1.user_id, o1.created_at DESC;

效能陷阱：相關子查詢對外層每行都要掃描內層，複雜度 O(N²)，資料量超過 1 萬行明顯變慢，不適合生產環境大表。

方法四：UNION ALL 分組查詢（用戶數極少時可考慮）

(SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 3)
UNION ALL
(SELECT * FROM orders WHERE user_id = 2 ORDER BY created_at DESC LIMIT 3)
UNION ALL
...;

僅適用於「已知且固定的少數分組」，動態分組不可用。

效能對比實測（100 萬筆訂單、1 萬用戶、索引 (user_id, created_at DESC)）

進階變體：取「每組最大/最小值」或「並列名次」

每組最新 1 筆（等價 rn=1）

SELECT o1.*
FROM orders o1
JOIN (
  SELECT user_id, MAX(created_at) AS max_dt
  FROM orders
  GROUP BY user_id
) o2 ON o1.user_id = o2.user_id AND o1.created_at = o2.max_dt;

允許並列名次（同時間同名次）

WITH ranked AS (
  SELECT *,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS dr
  FROM orders
)
SELECT * FROM ranked WHERE dr <= 3;

索引設計黃金法則

無論哪種寫法，複合索引 (分組欄位, 排序欄位 DESC) 都是核心：

ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at DESC);

MySQL 8.0 支援降序索引，優化器可直接按索引順序讀取，避免 Filesort。

常見坑點提醒

• 不要 在視窗函數外層直接用 WHERE ROW_NUMBER()... <= 3，必須用 CTE 或子查詢包裹
• GROUP BY + GROUP_CONCAT 取前 N 筆 ID 再 IN 查詢，受限於 group_concat_max_len（預設 1024），大分組會截斷，不推薦
• 分頁需求（如每組第 2 頁、每頁 3 筆）可改 WHERE rn BETWEEN 4 AND 6

總結

MySQL 8.0+ 請直接用 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)，語法標準、效能最優、可維護性最強。舊版本若無法升級，可暫時用用戶變數方案，但務必加上測試用例防止非確定性結果。索引 (group_col, sort_col DESC) 是所有方案的效能基石。