引言

在數(shù)據(jù)分析 或業(yè)務(wù)查詢中，我們經(jīng)常需要 “按某個字段分組，并取每組中時間最新的記錄”。例如：

• 按 data_time 分組，取每組 insert_time 最大的記錄。
• 按用戶 ID 分組，取每個用戶的最新訂單。

但 MySQL 的 GROUP BY 默認行為可能無法直接滿足需求，尤其是當查詢依賴 ORDER BY 時。本文將深入探討 一種常見的 hack 方案（依賴 LIMIT），并分析其問題，最終給出 更高效、更標準的解決方案。

1. 常見問題：如何獲取每個分組的最新記錄？

假設(shè)我們有一個表 spider.nbs_data，結(jié)構(gòu)如下：

CREATE TABLE spider.nbs_data (
    id BIGINT PRIMARY KEY,
    index_id VARCHAR(20),
    data_time DATE,
    insert_time DATETIME,
    -- 其他字段...
);

需求：查詢 index_id = '0000120460' 且 data_time >= '2020-12-31' 的數(shù)據(jù)，按 data_time 分組，并取每組 insert_time 最大的記錄。

2. 一種常見的 hack 方案：依賴 LIMIT 的 GROUP BY

2.1 原始 SQL

SELECT * FROM (
    SELECT * 
    FROM spider.nbs_data 
    WHERE index_id = '0000120460' AND data_time >= '2020-12-31' 
    ORDER BY `insert_time` DESC 
    LIMIT 10000000000  -- 超大 LIMIT 值
) t 
GROUP BY t.data_time  -- 按 data_time 分組
ORDER BY `data_time`;

2.2 為什么它能工作？

內(nèi)層查詢：

• 先篩選符合條件的數(shù)據(jù)。
• 按 insert_time DESC 排序，確保最新記錄排在前面。
• LIMIT 10000000000 強制 MySQL 先執(zhí)行 ORDER BY 再 GROUP BY，避免優(yōu)化器打亂順序。

外層 GROUP BY：

• MySQL 默認取每組的第一條記錄（即 insert_time 最大的那條）。
• 最終按 data_time 排序輸出。

2.3 問題與風(fēng)險

依賴 MySQL 的 GROUP BY 行為：

• 如果 SELECT * 包含非分組列，可能觸發(fā) ONLY_FULL_GROUP_BY 錯誤（MySQL 5.7+ 默認啟用）。
• 不同 MySQL 版本或 SQL 模式可能導(dǎo)致結(jié)果不一致。

性能問題：

• LIMIT 10000000000 相當于 全表掃描，數(shù)據(jù)量大時性能極差。
• 如果數(shù)據(jù)量超過 LIMIT 值，可能丟失數(shù)據(jù)。

代碼可讀性差：

LIMIT 10000000000 是一個 hack 行為，容易讓后續(xù)維護者困惑。

3. 更優(yōu)方案：標準 SQL 實現(xiàn)

方案 1：JOIN + 子查詢（兼容所有 MySQL 版本）

SELECT t1.*
FROM spider.nbs_data t1
JOIN (
    SELECT data_time, MAX(insert_time) AS max_insert_time
    FROM spider.nbs_data
    WHERE index_id = '0000120460' AND data_time >= '2020-12-31'
    GROUP BY data_time
) t2 ON t1.data_time = t2.data_time AND t1.insert_time = t2.max_insert_time
WHERE t1.index_id = '0000120460'
ORDER BY t1.data_time;

優(yōu)點：

• 清晰、標準，不依賴 GROUP BY 行為。
• 性能較好（子查詢先聚合，再 JOIN）。
• 兼容所有 MySQL 版本。

方案 2：ROW_NUMBER()（MySQL 8.0+）

WITH ranked_data AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY data_time ORDER BY insert_time DESC) AS rn
    FROM spider.nbs_data
    WHERE index_id = '0000120460' AND data_time >= '2020-12-31'
)
SELECT * FROM ranked_data WHERE rn = 1 ORDER BY data_time;

優(yōu)點：

• 語法簡潔，邏輯清晰。
• 性能優(yōu)秀（窗口函數(shù)優(yōu)化較好）。
• 適用于復(fù)雜分組排序場景。

缺點：

僅 MySQL 8.0+ 支持。

4. 性能對比

5. 總結(jié)

• 避免依賴 LIMIT 的 hack 方案：雖然能工作，但性能差、可讀性低、存在風(fēng)險。
• 推薦 JOIN + 子查詢：兼容性好，性能穩(wěn)定，適合大多數(shù)場景。
• MySQL 8.0+ 優(yōu)先用 ROW_NUMBER()：語法簡潔，性能更優(yōu)。

最終推薦 SQL：

-- MySQL 5.7 及以下版本
SELECT t1.*
FROM spider.nbs_data t1
JOIN (
    SELECT data_time, MAX(insert_time) AS max_insert_time
    FROM spider.nbs_data
    WHERE index_id = '0000120460' AND data_time >= '2020-12-31'
    GROUP BY data_time
) t2 ON t1.data_time = t2.data_time AND t1.insert_time = t2.max_insert_time
WHERE t1.index_id = '0000120460'
ORDER BY t1.data_time;

-- MySQL 8.0+
WITH ranked_data AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY data_time ORDER BY insert_time DESC) AS rn
    FROM spider.nbs_data
    WHERE index_id = '0000120460' AND data_time >= '2020-12-31'
)
SELECT * FROM ranked_data WHERE rn = 1 ORDER BY data_time;

結(jié)語

在 SQL 查詢中，清晰、標準、高效 是最重要的原則。依賴 LIMIT 的 hack 方案雖然能解決一時之需，但長期來看，使用 JOIN 或窗口函數(shù)才是更可靠的選擇。希望本文能幫助你優(yōu)化查詢，寫出更健壯的 SQL

到此這篇關(guān)于MySQL查詢優(yōu)化之高效獲取每個分組的最新記錄的文章就介紹到這了,更多相關(guān)MySQL查詢優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論