使用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的目的是向您展示如何进行软件开发工作，而不是为您完成所有工作。