MySQL：取得每個群組中的最新條目

Question

有一個表格messages，其中包含如下所示的資料：

Id   Name   Other_Columns
-------------------------
1    A       A_data_1
2    A       A_data_2
3    A       A_data_3
4    B       B_data_1
5    B       B_data_2
6    C       C_data_1

如果我執行查詢 select * from messages group by name，我將得到的結果為：

1    A       A_data_1
4    B       B_data_1
6    C       C_data_1

什麼查詢將傳回以下結果？

3    A       A_data_3
5    B       B_data_2
6    C       C_data_1

也就是說，應該傳回每組中的最後一筆記錄。

目前，這是我使用的查詢：

SELECT
  *
FROM (SELECT
  *
FROM messages
ORDER BY id DESC) AS x
GROUP BY name

但這看起來效率很低。有其他方法可以達到相同的結果嗎？

Answer 1

UPD：2017-03-31，版本5.7.5 MySQL 預設啟用 ONLY_FULL_GROUP_BY 開關（因此，非確定性 GROUP BY 查詢已停用）。此外，他們更新了 GROUP BY 實現，即使禁用了開關，該解決方案也可能無法按預期工作。需要檢查一下。

當組內的項目數相當小時，Bill Karwin 的上述解決方案工作正常，但當組相當大時，查詢的性能會變得很差，因為該解決方案需要大約n*n/2 n/ 2 僅進行IS NULL比較。

我在包含 18684446 行和 1182 群組的 InnoDB 表上進行了測試。此表包含功能測試的測試結果，並以 (test_id, request_id) 作為主鍵。因此，test_id 是一個群組，我正在為每個test_id 搜尋最後一個request_id。

Bill 的解決方案已經在我的 Dell e4310 上運行了幾個小時，儘管它在覆蓋索引上運行（因此在 EXPLAIN 中使用索引），但我不知道它什麼時候會完成。

我有幾個基於相同想法的其他解決方案：

• 如果基礎索引是BTREE 索引（通常是這種情況），則最大的(group_id, item_value) 對是每個group_id 中的最後一個值，即如果我們按降序遍歷索引，則為每個group_id 的第一個；
• 如果我們讀取索引覆蓋的值，則按照索引的順序讀取值；
• 每個索引都隱含包含附加到該索引的主鍵列（即主鍵位於覆蓋索引中）。在下面的解決方案中，我直接對主鍵進行操作，在您的情況下，您只需要在結果中添加主鍵列。
• 在許多情況下，在子查詢中按所需的順序收集所需的行 ID 並將子查詢的結果連接到 ID 上要便宜得多。由於對於子查詢結果中的每一行，MySQL 將需要基於主鍵進行一次獲取，因此子查詢將首先放在連接中，並且行將按照子查詢中id 的順序輸出（如果我們省略顯式ORDER BY用於連接）

MySQL 使用索引的 3 種方式 是一篇很棒的文章，可以幫助您了解一些細節。

解決方案1

#

這個速度快得令人難以置信，在我的 18M 行上大約需要 0.8 秒：

SELECT test_id, MAX(request_id) AS request_id
FROM testresults
GROUP BY test_id DESC;

如果您想將順序變更為 ASC，請將其放入子查詢中，僅傳回 ids 並將其用作子查詢來連接其餘列：

SELECT test_id, request_id
FROM (
    SELECT test_id, MAX(request_id) AS request_id
    FROM testresults
    GROUP BY test_id DESC) as ids
ORDER BY test_id;

這對我的數據來說大約需要 1.2 秒。

解決方案2

#

這是另一個解決方案，對於我的表來說大約需要 19 秒：

SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC

它也按降序返回測試。它要慢得多，因為它執行完整索引掃描，但它可以讓您了解如何為每個群組輸出 N 個最大行。

該查詢的缺點是查詢快取無法快取其結果。

Answer 2

MySQL 8.0 現在支援視窗函數，例如幾乎所有流行的 SQL 實作。使用這個標準語法，我們可以寫出每組最大n個查詢：

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

此方法和其他尋找的方法分組最大行數在 MySQL 手冊中進行了說明。

以下是我在2009年針對這個問題寫的原始答案：

---

我這樣寫解決方案：

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

關於效能，一種解決方案可能會更好，具體取決於資料的性質。因此，您應該測試這兩個查詢，並根據您的資料庫使用效能更好的查詢。

例如，我有一份 StackOverflow 8 月資料轉儲 的副本。我將用它來進行基準測試。 Posts 表中有 1,114,357 行。它在我的 Macbook Pro 2.40GHz 上的 MySQL 5.0.75 上運行。

我將編寫一個查詢來尋找給定用戶 ID（我的）的最新帖子。

首先使用所示的技術< /a> by @Eric 在子查詢中使用 GROUP BY：

SELECT p1.postid
FROM Posts p1
INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
            FROM Posts pi GROUP BY pi.owneruserid) p2
  ON (p1.postid = p2.maxpostid)
WHERE p1.owneruserid = 20860;

1 row in set (1 min 17.89 sec)

甚至EXPLAIN分析< /a> 需要 16 秒以上：

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
|  1 | PRIMARY     |  | ALL    | NULL                       | NULL        | NULL    | NULL         |   76756 |             | 
|  1 | PRIMARY     | p1         | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY     | 8       | p2.maxpostid |       1 | Using where | 
|  2 | DERIVED     | pi         | index  | NULL                       | OwnerUserId | 8       | NULL         | 1151268 | Using index | 
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
3 rows in set (16.09 sec)

現在使用 我使用 LEFT JOIN 的技巧：

SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
  ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
WHERE p2.postid IS NULL AND p1.owneruserid = 20860;

1 row in set (0.28 sec)

EXPLAIN 分析顯示兩個表都能夠使用它們的索引：

+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| id | select_type | table | type | possible_keys              | key         | key_len | ref   | rows | Extra                                |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
|  1 | SIMPLE      | p1    | ref  | OwnerUserId                | OwnerUserId | 8       | const | 1384 | Using index                          | 
|  1 | SIMPLE      | p2    | ref  | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8       | const | 1384 | Using where; Using index; Not exists | 
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
2 rows in set (0.00 sec)

---

這是我的 Posts 表的 DDL：

CREATE TABLE `posts` (
  `PostId` bigint(20) unsigned NOT NULL auto_increment,
  `PostTypeId` bigint(20) unsigned NOT NULL,
  `AcceptedAnswerId` bigint(20) unsigned default NULL,
  `ParentId` bigint(20) unsigned default NULL,
  `CreationDate` datetime NOT NULL,
  `Score` int(11) NOT NULL default '0',
  `ViewCount` int(11) NOT NULL default '0',
  `Body` text NOT NULL,
  `OwnerUserId` bigint(20) unsigned NOT NULL,
  `OwnerDisplayName` varchar(40) default NULL,
  `LastEditorUserId` bigint(20) unsigned default NULL,
  `LastEditDate` datetime default NULL,
  `LastActivityDate` datetime default NULL,
  `Title` varchar(250) NOT NULL default '',
  `Tags` varchar(150) NOT NULL default '',
  `AnswerCount` int(11) NOT NULL default '0',
  `CommentCount` int(11) NOT NULL default '0',
  `FavoriteCount` int(11) NOT NULL default '0',
  `ClosedDate` datetime default NULL,
  PRIMARY KEY  (`PostId`),
  UNIQUE KEY `PostId` (`PostId`),
  KEY `PostTypeId` (`PostTypeId`),
  KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
  KEY `OwnerUserId` (`OwnerUserId`),
  KEY `LastEditorUserId` (`LastEditorUserId`),
  KEY `ParentId` (`ParentId`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
) ENGINE=InnoDB;

---

評論者請注意：如果您想要使用不同版本的 MySQL、不同的資料集或不同的表格設計進行另一個基準測試，請自行完成。我已經展示了上面的技術。 Stack Overflow 在這裡向您展示如何進行軟體開發工作，而不是為您完成所有工作。