1.背景
之前有一段时间，老碰到一个需求业务逻辑场景：先对数据进行分组，然后在每组数据内按某种规则排序，最后取出每组的第一条记录，当然也不一定就是取第一条数据，也有可能是取前两条数据等等，看具体业务逻辑需要，总的来说是一种在数据库查询中常见的业务需求。本文将详细从使用MySQL, elasticsearch等方式总结这种操作的实现方案、使用场景以及优化技巧。
2.准备工作
所谓万事俱备只欠东风，要研究这个查询场景就得先准备好数据，还是使用一贯的用户表User：
CREATE TABLE `tb_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_no` varchar(255) NOT NULL COMMENT '编号',
`name` varchar(255) DEFAULT NULL COMMENT '昵称',
`email` varchar(255) DEFAULT NULL COMMENT '邮箱',
`phone` varchar(255) NOT NULL COMMENT '手机号',
`gender` tinyint(4) NOT NULL DEFAULT '0' COMMENT '性别 0：男生 1：女生',
`birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '出生日期',
`is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标志 0：否 1：是',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
`update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
`address` varchar(1024) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5201011 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
这里我插入了500多万条数据，为啥是500w条数据？因为我有个小目标先挣个500w，哈哈，开个玩笑跑题了~~~，其实是我个人认为单表500w条数据不多也不少，正好可以验证一些SQL性能差异。
插入都是比较真实的模拟数据，至于怎么插入这么多数据，你可以写写存储过程，或者使用代码脚本插入，想使用代码插入的，可以看看之前我们总结的文章：最近做百万级数据性能压测，来看看人家如何使用MyBatis 优雅批量插入数据，从80s优化到1s！！！
这里我使用的数据库配置是：阿里云的云数据库RDS-MySQL 2核4GB，阿里云上可以免费使用3个月，有需要的可以去申请
数据已到位，我们开始分析先分组、再组内排序取数据实现方式，基于上面的数据：我们根据名字查询出每个名字最近插入的个人信息
3.MySQL实现
数据库查询，一条SQL搞定，永远是我们在实现功能需求逻辑的第一追求，当然只是首选也不一定必选，要考虑性能问题，凡事不能一概而论，这就像算法的好坏需要从空间和时间两个维度去考量一个道理。言归正传，我们看看数据库MySQL查询层面有哪些实现方式
3.1 使用窗口函数 ROW_NUMBER()
窗口函数是解决该问题的首选方法，既简洁又高效
SELECT
*
FROM
( SELECT id, NAME, birthday, ROW_NUMBER () OVER ( PARTITION BY NAME ORDER BY id DESC ) AS row_num FROM tb_user
where name in('徐千云', '李亿石')
) AS u
WHERE
u.row_num=1
查询结果：
耗时：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)
耗时：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_id
耗时：1.418s 明显比子查询快很多，这就是大量数据下，不同的SQL查询性能差别是挺大的
3.3 优化点
上面的查询最快的都要1s多，这算是慢查询了肯定要优化，直接加索引
ALTER TABLE `db_test`.`tb_user`
ADD INDEX `idx_name`(`name`) USING BTREE;
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.77s，也有算提升，因为这个可以使用覆盖索引，建设了回表的次数。
3.4 讨论
也不一定查询每个名字最新插入的数据，可能是查询每个名字年纪最小的第一条数据，这时候窗口函数查询如下：
SELECT
id, name, birthday
FROM
( SELECT id, name,birthday, ROW_NUMBER () OVER ( PARTITION BY NAME ORDER BY birthday DESC ) AS row_num FROM tb_user
where name in('徐千云', '李亿石')
) AS u
WHERE
u.row_num =1
耗时：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`)
直接查询不出来~~~
项目推荐：基于SpringBoot2.x、SpringCloud和SpringCloudAlibaba企业级系统架构底层框架封装，解决业务开发时常见的非功能性需求，防止重复造轮子，方便业务快速开发和企业技术栈框架统一管理。引入组件化的思想实现高内聚低耦合并且高度可配置化，做到可插拔。严格控制包依赖和统一版本管理，做到最少化依赖。注重代码规范和注释，非常适合个人学习和企业使用
Github地址：https://github.com/plasticene/plasticene-boot-starter-parent
Gitee地址：https://gitee.com/plasticene3/plasticene-boot-starter-parent
微信公众号：Shepherd进阶笔记
交流探讨qun：Shepherd_126
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
}
}
}
}
}
}
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) {
final int 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;
}
afterKey 参数：在 CompositeAggregationBuilder 中的 aggregateAfter(afterKey) 设置为上一次查询的 afterKey，用于实现分页。
分页循环：每次查询一页数据，更新 afterKey，直到 afterKey 为 null，表示已达到最后一页。
TopHitsAggregationBuilder：按 id 降序排序并返回每组中的最新文档，用于获取分组的最新记录。
不知道你是否有注意到方法aggregateResults()有一个回调函数参数Consumer<List<UserInfo>> consumer，当我们在操作大批量数据时，不可能一次性查出所有数据来处理，性能扛不住，只能分批分批查询，如果分页查询出来放到一个集合最后在处理，内存也支撑不住，这时候我们只在分页查询数据的同时处理相关数据，比如分页每页200条查询出200个名字年纪最小的人，并把他的更新时间更新为当前，这样就可以写一个更新更新时间的方法，传入aggregateResults()，等分页查询出用户之后，根据用户id回调更新即可。
关于优雅回调的编程代码技巧请看之前总结的：拒绝一直写CRUD！！！使用回调机制Callback和函数式编程码出优雅结构化代码
5.总结
分组后组内排序取第一条记录的操作，是 SQL 查询中的高频需求。推荐优先使用窗口函数 ROW_NUMBER()，其次是子查询方法，并根据实际业务场景选择合适的实现方式。同时，结合索引和字段优化，可以显著提升查询性能。当然这是针对大批量查询的情况下，如果你这是查询一两个姓名的最小年纪这种，你完全可以查询出所有数据，在Java代码逻辑中利用内存分组之后再排序，取相应数据即可。如果本文对你有帮助的话，麻烦给个一键三连（点赞、在看、转发分享）支持一下，感谢Thanks♪(･ω･)ﾉ
