使用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，MySQL的版本5.7.5預設啟用了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的解決方案已經在我的戴爾e4310上運行了幾個小時，我不知道它何時會完成，儘管它在覆蓋索引上操作（因此在EXPLAIN中顯示using index ）。

我還有幾個基於相同思路的解決方案：

• 如果底層索引是BTREE索引（通常情況下），每個group_id中的最大(group_id, item_value)對就是每個group_id的最後一個值，如果我們按降序遍歷索引，則是每個group_id的第一個值；
• 如果我們讀取由索引覆寫的值，這些值將按照索引的順序讀取；
• 每個索引隱含地包含附加的主鍵列（即主鍵在覆蓋索引中）。在下面的解決方案中，我直接操作主鍵，在你的情況下，你只需要在結果中加入主鍵列。
• 在許多情況下，較便宜的方法是在子查詢中按所需順序收集所需的行ID，並將子查詢的結果與ID進行連接。由於MySQL對子查詢結果中的每一行都需要基於主鍵進行單一獲取，因此子查詢將首先放置在連接中，並且行將按照子查詢中的ID順序輸出（如果我們省略連接的明確ORDER BY）

3 ways MySQL uses indexes是一篇很好的文章，可以了解一些細節。

解決方案1

#

這個解決方案非常快，對於我1800萬 行的數據，大約需要0.8秒：

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

如果要改變順序為升序，將其放入子查詢中，只傳回ID，並將其作為子查詢與其他列連接：

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-per-group查詢：

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八月資料轉儲的副本。我將用它進行基準測試。在Posts表中有1,114,357行資料。這是在我的Macbook Pro 2.40GHz上運行的MySQL 5.0.75。

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

首先使用了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行结果（1分17.89秒）

即使EXPLAIN分析也需要超過16秒：

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
|  1 | PRIMARY     | <derived2> | 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行结果（16.09秒）

現在使用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行结果（0.28秒）

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行结果（0.00秒）

---

這是我的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的目的是要向您展示如何進行軟體開發工作，而不是為您完成所有工作。