协慌网

 登录 贡献 社区

检索每个组中的最后一条记录 - MySQL

有一个包含数据的表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运行查询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

但这看起来非常低效。还有其他方法可以达到相同的效果吗？

答案

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;

以下是我在 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 August 数据转储的副本。我会用它来进行基准测试。 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 row in set (1 min 17.89 sec)

甚至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 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;

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比较。

我在一个包含1182组的18684446行的 InnoDB 表上进行了测试。该表包含功能测试的测试结果，并以(test_id, request_id)为主键。因此， test_id是一个组，我正在为每个test_id搜索最后一个request_id 。

Bill 的解决方案已经在我的 dell e4310 上运行了几个小时，我不知道它什么时候会完成，即使它在覆盖索引上运行（因此在 EXPLAIN 中using index ）。

我有几个基于相同想法的其他解决方案：

  • 如果底层索引是 BTREE 索引（通常是这种情况），则最大(group_id, item_value)对是每个group_id的最后一个值，如果我们按降序遍历索引，则每个group_id的第一个值;
  • 如果我们读取索引覆盖的值，则按索引的顺序读取值;
  • 每个索引隐式包含附加到其上的主键列（即主键位于 coverage 索引中）。在下面的解决方案中，我直接在主键上操作，在这种情况下，您只需要在结果中添加主键列。
  • 在许多情况下，在子查询中以所需顺序收集所需的行 ID 并在 id 上加入子查询的结果要便宜得多。因为对于子查询结果中的每一行，MySQL 将需要基于主键的单个提取，子查询将首先放在连接中，并且将按子查询中的 id 的顺序输出行（如果我们省略显式的 ORDER BY 为了加入）

MySQL 使用索引的 3 种方法是理解一些细节的好文章。

解决方案 1

这个速度非常快，我的 18M + 行需要大约 0.8 秒： 

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

如果要将顺序更改为 ASC，请将其放在子查询中，仅返回 id 并将其用作子查询以连接到其余列： 

SELECT test_id, request_id
FROM (
    SELECT test_id, MAX(request_id), 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 max 行。

查询的缺点是查询缓存无法缓存其结果。

使用子查询返回正确的分组，因为你已经到了一半。

试试这个： 

select
    a.*
from
    messages a
    inner join 
        (select name, max(id) as maxid from messages group by name) as b on
        a.id = b.maxid

如果它不是id你想要的最大值： 

select
    a.*
from
    messages a
    inner join 
        (select name, max(other_col) as other_col 
         from messages group by name) as b on
        a.name = b.name
        and a.other_col = b.other_col

这样，您可以避免子查询中的相关子查询和 / 或排序，这些子查询往往非常慢 / 效率低。