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) NOTNULLDEFAULT0COMMENT性别 0：男生 1：女生,
`birthday`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT出生日期,
`is_delete`tinyint(4) NOTNULLDEFAULT0COMMENT删除标志 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代码逻辑中利用内存分组之后再排序，取相应数据即可。
