詳解如何優雅實現先分組再組內排序取數據解決方案
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;這里我插入了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=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
( SELECTid, name,birthday, ROW_NUMBER () OVER ( PARTITIONBYNAMEORDERBY birthday DESC ) AS row_num FROM tb_user
wherenamein('徐千云', '李億石')
) 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
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) {
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;
}afterKey 參數：在 CompositeAggregationBuilder 中的 aggregateAfter(afterKey) 設置為上一次查詢的 afterKey，用于實現分頁。
分頁循環：每次查詢一頁數據，更新 afterKey，直到 afterKey 為 null，表示已達到最后一頁。
TopHitsAggregationBuilder：按 id 降序排序并返回每組中的最新文檔，用于獲取分組的最新記錄。
不知道你是否有注意到方法aggregateResults()有一個回調函數參數Consumer<List<UserInfo>> consumer，當我們在操作大批量數據時，不可能一次性查出所有數據來處理，性能扛不住，只能分批分批查詢，如果分頁查詢出來放到一個集合最后在處理，內存也支撐不住，這時候我們只在分頁查詢數據的同時處理相關數據，比如分頁每頁200條查詢出200個名字年紀最小的人，并把他的更新時間更新為當前，這樣就可以寫一個更新更新時間的方法，傳入aggregateResults()，等分頁查詢出用戶之后，根據用戶id回調更新即可。
5.總結
分組后組內排序取第一條記錄的操作，是 SQL 查詢中的高頻需求。推薦優先使用窗口函數 ROW_NUMBER()，其次是子查詢方法，并根據實際業務場景選擇合適的實現方式。同時，結合索引和字段優化，可以顯著提升查詢性能。當然這是針對大批量查詢的情況下，如果你這是查詢一兩個姓名的最小年紀這種，你完全可以查詢出所有數據，在Java代碼邏輯中利用內存分組之后再排序，取相應數據即可。
