详解如何优雅实现先分组再组内排序取数据解决方案-51CTO.COM

1.背景

之前有一段时间，老碰到一个需求业务逻辑场景：先对数据进行分组，然后在每组数据内按某种规则排序，最后取出每组的第一条记录，当然也不一定就是取第一条数据，也有可能是取前两条数据等等，看具体业务逻辑需要，总的来说是一种在数据库查询中常见的业务需求。本文将详细从使用MySQL, elasticsearch等方式总结这种操作的实现方案、使用场景以及优化技巧。

2.准备工作

所谓万事俱备只欠东风，要研究这个查询场景就得先准备好数据，还是使用一贯的用户表User：

CREATE TABLE`tb_user` (
`id`bigint(20) NOTNULL AUTO_INCREMENT COMMENT'主键',
`user_no`varchar(255) NOTNULLCOMMENT'编号',
`name`varchar(255) DEFAULTNULLCOMMENT'昵称',
`email`varchar(255) DEFAULTNULLCOMMENT'邮箱',
`phone`varchar(255) NOTNULLCOMMENT'手机号',
`gender`tinyint(4) NOTNULLDEFAULT'0'COMMENT'性别  0：男生   1：女生',
`birthday`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'出生日期',
`is_delete`tinyint(4) NOTNULLDEFAULT'0'COMMENT'删除标志 0：否  1：是',
`create_time` datetime DEFAULTNULLCOMMENT'创建时间',
`update_time` datetime DEFAULTNULLCOMMENT'更新时间',
`create_by`bigint(20) DEFAULTNULLCOMMENT'创建人',
`update_by`bigint(20) DEFAULTNULLCOMMENT'更新人',
`address`varchar(1024) DEFAULTNULLCOMMENT'地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5201011DEFAULTCHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

这里我插入了500多万条数据，为啥是500w条数据？因为我有个小目标先挣个500w，哈哈，开个玩笑跑题了~~~，其实是我个人认为单表500w条数据不多也不少，正好可以验证一些SQL性能差异。

图片

插入都是比较真实的模拟数据，至于怎么插入这么多数据，你可以写写存储过程，或者使用代码脚本插入，想使用代码插入的，可以看看之前我们总结的文章：最近做百万级数据性能压测，来看看人家如何使用MyBatis 优雅批量插入数据，从80s优化到1s！！！

这里我使用的数据库配置是：阿里云的云数据库RDS-MySQL 2核4GB，阿里云上可以免费使用3个月，有需要的可以去申请

数据已到位，我们开始分析先分组、再组内排序取数据实现方式，基于上面的数据：我们根据名字查询出每个名字最近插入的个人信息

3.MySQL实现

数据库查询，一条SQL搞定，永远是我们在实现功能需求逻辑的第一追求，当然只是首选也不一定必选，要考虑性能问题，凡事不能一概而论，这就像算法的好坏需要从空间和时间两个维度去考量一个道理。言归正传，我们看看数据库MySQL查询层面有哪些实现方式

3.1 使用窗口函数 ROW_NUMBER()

窗口函数是解决该问题的首选方法，既简洁又高效

SELECT
 * 
FROM
 ( SELECTid, NAME, birthday, ROW_NUMBER () OVER ( PARTITIONBYNAMEORDERBYidDESC ) AS row_num FROM tb_user
   wherenamein('徐千云', '李亿石')
 ) AS u 
WHERE
 u.row_num=11.
2.
3.
4.
5.
6.
7.
8.

查询结果：

图片

耗时：1.547s 注意窗口函数要 MySQL 8.0 及以上版本才有哦。

3.2 使用子查询和JOIN关联查询

话不多说直接上SQL:

SELECT id, name, birthday from tb_user 
where id in (SELECT MAX(id) from tb_user where name in('徐千云', '李亿石') group by name)1.
2.

耗时：3.687s 明显比使用窗口函数要慢的多

换一种写法：

SELECT id, name, birthday from tb_user u
INNER JOIN (SELECT MAX(id) max_id from tb_user where name in('徐千云', '李亿石') group by name) as t
on u.id=t.max_id1.
2.
3.

耗时：1.418s 明显比子查询快很多，这就是大量数据下，不同的SQL查询性能差别是挺大的

3.3 优化点

上面的查询最快的都要1s多，这算是慢查询了肯定要优化，直接加索引

ALTER TABLE `db_test`.`tb_user` 
ADD INDEX `idx_name`(`name`) USING BTREE;1.
2.

500w条的数据表，加索引会需要小一会儿。加完索引再次分别执行上面的SQL语句，执行结果耗时如下：

窗口函数：0.026s 子查询：2.229s JOIN关联查询：0.014s

从结果上来看，执行速度明显变快了，效果可谓是立竿见影

如果我们查询不需要出生日期birthday，以最慢的子查询为例：

SELECT id,name from tb_user 
where id in (SELECT MAX(id) from tb_user where name in('徐千云', '李亿石') group by name)1.
2.

耗时：1.77s，也有算提升，因为这个可以使用覆盖索引，建设了回表的次数。

3.4 讨论

也不一定查询每个名字最新插入的数据，可能是查询每个名字年纪最小的第一条数据，这时候窗口函数查询如下：

SELECT
 id, name, birthday
FROM
 ( SELECTid, name,birthday, ROW_NUMBER () OVER ( PARTITIONBYNAMEORDERBY birthday DESC ) AS row_num FROM tb_user
   wherenamein('徐千云', '李亿石')
 ) AS u 
WHERE
 u.row_num =11.
2.
3.
4.
5.
6.
7.
8.

耗时：0.30s 挺快的

使用子查询：

SELECT id, name, birthday from tb_user u1
where birthday = (select max(birthday) from tb_user u2 where u2.name in('徐千云', '李亿石') and u1.`name`=u2.`name`)1.
2.

直接查询不出来~~~

项目推荐：基于SpringBoot2.x、SpringCloud和SpringCloudAlibaba企业级系统架构底层框架封装，解决业务开发时常见的非功能性需求，防止重复造轮子，方便业务快速开发和企业技术栈框架统一管理。引入组件化的思想实现高内聚低耦合并且高度可配置化，做到可插拔。严格控制包依赖和统一版本管理，做到最少化依赖。注重代码规范和注释，非常适合个人学习和企业使用

Github地址：https://github.com/plasticene/plasticene-boot-starter-parent

Gitee地址：https://gitee.com/plasticene3/plasticene-boot-starter-parent

4.elasticsearch实现

数据异构是解决数据量大的一大常见方式，我们经常使用elasticsearch来缓解MySQL数据库的查询压力，来应对海量数据的复杂查询，那接下来我们就来看看基于elasticsearch怎么实现先分组再组内排序取数据

在 Elasticsearch 中，可以使用 terms 聚合来实现分组，再结合 top_hits 聚合实现组内排序并取每组的第一条数据。terms 聚合用于对某个字段进行分组，而 top_hits 聚合用于在每个分组内获取指定数量的文档并按某种顺序排序。

查询每个名字年纪最小的第一条数据：

GET user_info/_search
{
"size": 0,
"query": {
    "bool": {
      "filter": [
        { "terms": { "name": ["徐千云", "李亿石"] }},
      ]
    }
  },
"aggs": {
    "group_by_name": {
      "terms": {
        "field": "name",
        "size": 1000
      },
      "aggs": {
        "latest_user": {
          "top_hits": {
            "sort": [
              { "birthday": { "order": "desc" }}
            ],
            "_source": ["id", "name", "org_id", "birthday"],
            "size": 1
          }
        }
      }
    }
  }
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.

size: 0：在根查询中设置 size 为 0，表示不返回顶层文档，只返回聚合结果。
query 部分：使用 bool 查询，限定 name 在["徐千云", "李亿石"]内。
terms 聚合 (group_by_name)：按 name 分组，size 设置为较大值（如 1000）以确保返回尽可能多的分组。
top_hits 聚合 (latest_user)：在每个 name 分组内，按 birthday 字段降序排序，并只返回每组的第一个文档（即年龄最小的的文档）。指定 _source 字段过滤以返回所需字段。

terms 聚合的 size 限制了返回分组的数量。若需要更多分组，可以增大 size 或使用 composite 聚合。对于较大数据集，此查询可能较慢，因为需要对分组中的每个文档进行排序，直接看代码：

public Integer aggregateResults(UserQueryDTO dto, Consumer<List<UserInfo>> consumer) {
        finalint pageSize = 500;
        Integer total = 0;
        Map<String, Object> afterKey = null;
        while (true) {
            // 创建 SearchRequest
            SearchRequest searchRequest = new SearchRequest(getAliasName());
            // 构建查询条件
            BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
            if (StringUtils.isNotBlank(dto.getTypes())) {
                boolQueryBuilder.filter(QueryBuilders.termsQuery("name", dto.getNames));
            }
            // Composite聚合实现并分页
            CompositeAggregationBuilder compositeAggregation = AggregationBuilders
                    .composite("group_by_name",
                            Lists.newArrayList( new TermsValuesSourceBuilder("name").field("name")))
                    .size(pageSize);
            // 设置分页的 afterKey
            compositeAggregation.aggregateAfter(afterKey);
            // 添加top_hits子聚合，size=1获取每个分组中的最新文档
            TopHitsAggregationBuilder topHitsAggregation = AggregationBuilders
                    .topHits("latest_user")
                    .sort("birthday", SortOrder.DESC)
                    .fetchSource(new String[]{"id", "name", "birthday"}, null)
                    .size(1);
            // 添加子聚合到 composite 聚合
            compositeAggregation.subAggregation(topHitsAggregation);
            // 构建 SearchSourceBuilder size=0不返回文档，只返回聚合结果
            SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder()
                    .size(0)
                    .query(boolQueryBuilder)
                    .aggregation(compositeAggregation);

            // 设置搜索请求，执行搜索
            searchRequest.source(searchSourceBuilder);
            SearchResponse response = search(searchRequest);

            // 处理分页聚合结果
            List<String> dataList = new ArrayList<>();
            ParsedComposite compositeAgg = response.getAggregations().get("group_by_name");
            // 遍历当前页的分组结果
            compositeAgg.getBuckets().forEach(bucket -> {
                ParsedTopHits topHits = bucket.getAggregations().get("latest_user");
                SearchHit[] hits = topHits.getHits().getHits();
                for (SearchHit hit : hits) {
                   dataList.add(hit.getSourceAsString());
                }
            });
            List<UserInfo> results = convert(dataList, UserInfo.class);
            // 回调
            consumer.accept(results);
            total = total + results.size();
            // 更新 afterKey，为下一页查询准备, 如果afterKey为空，说明已经查询到最后一页
            afterKey = compositeAgg.afterKey();
            if (afterKey == null) {
                break;
            }
        }
        return total;
    }1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.

afterKey 参数：在 CompositeAggregationBuilder 中的 aggregateAfter(afterKey) 设置为上一次查询的 afterKey，用于实现分页。

分页循环：每次查询一页数据，更新 afterKey，直到 afterKey 为 null，表示已达到最后一页。

TopHitsAggregationBuilder：按 id 降序排序并返回每组中的最新文档，用于获取分组的最新记录。

不知道你是否有注意到方法aggregateResults()有一个回调函数参数Consumer<List<UserInfo>> consumer，当我们在操作大批量数据时，不可能一次性查出所有数据来处理，性能扛不住，只能分批分批查询，如果分页查询出来放到一个集合最后在处理，内存也支撑不住，这时候我们只在分页查询数据的同时处理相关数据，比如分页每页200条查询出200个名字年纪最小的人，并把他的更新时间更新为当前，这样就可以写一个更新更新时间的方法，传入aggregateResults()，等分页查询出用户之后，根据用户id回调更新即可。

5.总结

分组后组内排序取第一条记录的操作，是 SQL 查询中的高频需求。推荐优先使用窗口函数 ROW_NUMBER()，其次是子查询方法，并根据实际业务场景选择合适的实现方式。同时，结合索引和字段优化，可以显著提升查询性能。当然这是针对大批量查询的情况下，如果你这是查询一两个姓名的最小年纪这种，你完全可以查询出所有数据，在Java代码逻辑中利用内存分组之后再排序，取相应数据即可。