1.背景
之前有一段时间,老碰到一个需求业务逻辑场景:先对数据进行分组,然后在每组数据内按某种规则排序,最后取出每组的第一条记录,当然也不一定就是取第一条数据,也有可能是取前两条数据等等,看具体业务逻辑需要,总的来说是一种在数据库查询中常见的业务需求。本文将详细从使用MySQL, elasticsearch等方式总结这种操作的实现方案、使用场景以及优化技巧。
2.准备工作
所谓万事俱备只欠东风,要研究这个查询场景就得先准备好数据,还是使用一贯的用户表User:
这里我插入了500多万条数据,为啥是500w条数据?因为我有个小目标先挣个500w,哈哈,开个玩笑跑题了~~~,其实是我个人认为单表500w条数据不多也不少,正好可以验证一些SQL性能差异。
图片
插入都是比较真实的模拟数据,至于怎么插入这么多数据,你可以写写存储过程,或者使用代码脚本插入,想使用代码插入的,可以看看之前我们总结的文章:最近做百万级数据性能压测,来看看人家如何使用MyBatis 优雅批量插入数据,从80s优化到1s!!!
这里我使用的数据库配置是:阿里云的云数据库RDS-MySQL 2核4GB,阿里云上可以免费使用3个月,有需要的可以去申请
数据已到位,我们开始分析先分组、再组内排序取数据实现方式,基于上面的数据:我们根据名字查询出每个名字最近插入的个人信息
3.MySQL实现
数据库查询,一条SQL搞定,永远是我们在实现功能需求逻辑的第一追求,当然只是首选也不一定必选,要考虑性能问题,凡事不能一概而论,这就像算法的好坏需要从空间和时间两个维度去考量一个道理。言归正传,我们看看数据库MySQL查询层面有哪些实现方式
3.1 使用窗口函数 ROW_NUMBER()
窗口函数是解决该问题的首选方法,既简洁又高效
查询结果:
图片
耗时:1.547s 注意窗口函数要 MySQL 8.0 及以上版本才有哦。
3.2 使用子查询和JOIN关联查询
话不多说直接上SQL:
耗时:3.687s 明显比使用窗口函数要慢的多
换一种写法:
耗时:1.418s 明显比子查询快很多,这就是大量数据下,不同的SQL查询性能差别是挺大的
3.3 优化点
上面的查询最快的都要1s多,这算是慢查询了肯定要优化,直接加索引
500w条的数据表,加索引会需要小一会儿。加完索引再次分别执行上面的SQL语句,执行结果耗时如下:
窗口函数:0.026s 子查询:2.229s JOIN关联查询:0.014s
从结果上来看,执行速度明显变快了,效果可谓是立竿见影
如果我们查询不需要出生日期birthday,以最慢的子查询为例:
耗时:1.77s,也有算提升,因为这个可以使用覆盖索引,建设了回表的次数。
3.4 讨论
也不一定查询每个名字最新插入的数据,可能是查询每个名字年纪最小的第一条数据,这时候窗口函数查询如下:
耗时:0.30s 挺快的
使用子查询:
直接查询不出来~~~
项目推荐:基于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 聚合用于在每个分组内获取指定数量的文档并按某种顺序排序。
查询每个名字年纪最小的第一条数据:
- 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 聚合。对于较大数据集,此查询可能较慢,因为需要对分组中的每个文档进行排序,直接看代码:
afterKey 参数:在 CompositeAggregationBuilder 中的 aggregateAfter(afterKey) 设置为上一次查询的 afterKey,用于实现分页。
分页循环:每次查询一页数据,更新 afterKey,直到 afterKey 为 null,表示已达到最后一页。
TopHitsAggregationBuilder:按 id 降序排序并返回每组中的最新文档,用于获取分组的最新记录。
不知道你是否有注意到方法aggregateResults()有一个回调函数参数Consumer<List<UserInfo>> consumer,当我们在操作大批量数据时,不可能一次性查出所有数据来处理,性能扛不住,只能分批分批查询,如果分页查询出来放到一个集合最后在处理,内存也支撑不住,这时候我们只在分页查询数据的同时处理相关数据,比如分页每页200条查询出200个名字年纪最小的人,并把他的更新时间更新为当前,这样就可以写一个更新更新时间的方法,传入aggregateResults(),等分页查询出用户之后,根据用户id回调更新即可。
5.总结
分组后组内排序取第一条记录的操作,是 SQL 查询中的高频需求。推荐优先使用窗口函数 ROW_NUMBER(),其次是子查询方法,并根据实际业务场景选择合适的实现方式。同时,结合索引和字段优化,可以显著提升查询性能。当然这是针对大批量查询的情况下,如果你这是查询一两个姓名的最小年纪这种,你完全可以查询出所有数据,在Java代码逻辑中利用内存分组之后再排序,取相应数据即可。