萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 分組取每組 Top N 記錄:多種寫法效能對比與最佳實踐

問題描述

表 Article(Id, Category, InsertDate),求**每個分類下最新的 N 篇文章**(按 InsertDate 降序)。

方案一:關聯子查詢 + 計數(通用、MySQL 所有版本)

SELECT A1.* 
FROM Article A1
INNER JOIN (
    SELECT A.Category, A.InsertDate
    FROM Article A
    LEFT JOIN Article B 
      ON A.Category = B.Category 
     AND A.InsertDate <= B.InsertDate
    GROUP BY A.Category, A.InsertDate
    HAVING COUNT(B.InsertDate) <= 3  -- N=3
) B1 ON A1.Category = B1.Category AND A1.InsertDate = B1.InsertDate
ORDER BY A1.Category, A1.InsertDate DESC;
  • 原理:自連接計算每行在分組中的「排名」(有多少行日期 >= 它)。
  • 缺點:INSERTDATE 有重複值時會多返回行;大表自連接極慢 (O(N²))。

方案二:用戶變量模擬 ROW_NUMBER()(MySQL 5.7/8.0 前)

SELECT Id, Category, InsertDate
FROM (
    SELECT 
        Id, Category, InsertDate,
        @rn := IF(@prev_cat = Category, @rn + 1, 1) AS rn,
        @prev_cat := Category
    FROM Article
    CROSS JOIN (SELECT @rn := 0, @prev_cat := '') vars
    ORDER BY Category, InsertDate DESC
) t
WHERE rn <= 3;
  • 單掃描 O(N log N),效能遠優於方案一。
  • ⚠️ 依賴變量賦值順序,MySQL 8.0+ 不保證行處理順序,**僅適用 5.7 及以下**。

方案三:CTE + ROW_NUMBER() 窗口函數(MySQL 8.0+ 標準寫法,強烈推薦)

WITH ranked AS (
    SELECT 
        Id, Category, InsertDate,
        ROW_NUMBER() OVER (PARTITION BY Category ORDER BY InsertDate DESC) AS rn
    FROM Article
)
SELECT Id, Category, InsertDate
FROM ranked
WHERE rn <= 3
ORDER BY Category, InsertDate DESC;
  • 語義清晰、標準 SQL、優化器支援最好、正確處理並列排名(RANK()/DENSE_RANK() 可選)。
  • 可擴展:PARTITION BY Category, Author 多維分組。

方案四:關聯子查詢 + LIMIT(相關子查詢,小表適用)

SELECT * FROM Article a1
WHERE (
    SELECT COUNT(*) FROM Article a2 
    WHERE a2.Category = a1.Category 
      AND a2.InsertDate >= a1.InsertDate
) <= 3
ORDER BY Category, InsertDate DESC;
  • 每行執行一次子查詢,大表極慢,僅適合小資料量或臨時查詢。

方案五:應用層分頁查詢(極大資料量、高併發)

-- 1. 先查所有分類
SELECT DISTINCT Category FROM Article;

-- 2. 應用層循環每個分類查 Top N(可並行)
SELECT * FROM Article WHERE Category = ? ORDER BY InsertDate DESC LIMIT 3;
  • 將壓力分散到應用層,避免單條大 SQL 阻塞。
  • 配合分類表緩存,效能極佳。

效能對比實測建議

資料量MySQL 版本推薦方案預估耗時
< 10 萬任意方案三 (CTE) / 方案二< 100ms
10 萬 ~ 1000 萬5.7方案二 (變量)~ 1-5s
10 萬 ~ 1000 萬8.0+方案三 (CTE+窗口)~ 500ms-2s
> 1000 萬8.0+方案五 (應用層分批) + 覆蓋索引分批並行 < 1s

關鍵索引設計

-- 覆蓋索引:WHERE + ORDER BY + SELECT 欄位全覆蓋
CREATE INDEX idx_cat_date_id ON Article (Category, InsertDate DESC, Id);
  • Category 先行,滿足分組與篩選。
  • InsertDate DESC 避免 filesort,直接按索引順序取 Top N。
  • Id 覆蓋查詢,回表為零。

總結

**MySQL 8.0+ 首選 CTE + ROW_NUMBER()**,標準、高效、可維護。5.7 用戶變量模擬次之。大表務必建立 (Category, InsertDate DESC, Id) 覆蓋索引。極大資料量下,應用層分批查詢配合緩存是終極方案。

copyright © 萬盛學電腦網 all rights reserved