詳解如何優(yōu)雅實現(xiàn)先分組再組內(nèi)排序取數(shù)據(jù)解決方案
1.背景
之前有一段時間，老碰到一個需求業(yè)務(wù)邏輯場景：先對數(shù)據(jù)進行分組，然后在每組數(shù)據(jù)內(nèi)按某種規(guī)則排序，最后取出每組的第一條記錄，當(dāng)然也不一定就是取第一條數(shù)據(jù)，也有可能是取前兩條數(shù)據(jù)等等，看具體業(yè)務(wù)邏輯需要，總的來說是一種在數(shù)據(jù)庫查詢中常見的業(yè)務(wù)需求。本文將詳細(xì)從使用MySQL, elasticsearch等方式總結(jié)這種操作的實現(xiàn)方案、使用場景以及優(yōu)化技巧。
2.準(zhǔn)備工作
所謂萬事俱備只欠東風(fēng)，要研究這個查詢場景就得先準(zhǔn)備好數(shù)據(jù)，還是使用一貫的用戶表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'刪除標(biāo)志 0：否 1：是',
`create_time` datetime DEFAULTNULLCOMMENT'創(chuàng)建時間',
`update_time` datetime DEFAULTNULLCOMMENT'更新時間',
`create_by`bigint(20) DEFAULTNULLCOMMENT'創(chuàng)建人',
`update_by`bigint(20) DEFAULTNULLCOMMENT'更新人',
`address`varchar(1024) DEFAULTNULLCOMMENT'地址',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5201011DEFAULTCHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;這里我插入了500多萬條數(shù)據(jù)，為啥是500w條數(shù)據(jù)？因為我有個小目標(biāo)先掙個500w，哈哈，開個玩笑跑題了~~~，其實是我個人認(rèn)為單表500w條數(shù)據(jù)不多也不少，正好可以驗證一些SQL性能差異。
圖片
插入都是比較真實的模擬數(shù)據(jù)，至于怎么插入這么多數(shù)據(jù)，你可以寫寫存儲過程，或者使用代碼腳本插入，想使用代碼插入的，可以看看之前我們總結(jié)的文章：最近做百萬級數(shù)據(jù)性能壓測，來看看人家如何使用MyBatis 優(yōu)雅批量插入數(shù)據(jù)，從80s優(yōu)化到1s?。?！
這里我使用的數(shù)據(jù)庫配置是：阿里云的云數(shù)據(jù)庫RDS-MySQL 2核4GB，阿里云上可以免費使用3個月，有需要的可以去申請
數(shù)據(jù)已到位，我們開始分析先分組、再組內(nèi)排序取數(shù)據(jù)實現(xiàn)方式，基于上面的數(shù)據(jù)：我們根據(jù)名字查詢出每個名字最近插入的個人信息
3.MySQL實現(xiàn)
數(shù)據(jù)庫查詢，一條SQL搞定，永遠是我們在實現(xiàn)功能需求邏輯的第一追求，當(dāng)然只是首選也不一定必選，要考慮性能問題，凡事不能一概而論，這就像算法的好壞需要從空間和時間兩個維度去考量一個道理。言歸正傳，我們看看數(shù)據(jù)庫MySQL查詢層面有哪些實現(xiàn)方式
3.1 使用窗口函數(shù) ROW_NUMBER()
窗口函數(shù)是解決該問題的首選方法，既簡潔又高效
SELECT
*
FROM
( SELECTid, NAME, birthday, ROW_NUMBER () OVER ( PARTITIONBYNAMEORDERBYidDESC ) AS row_num FROM tb_user
wherenamein('徐千云', '李億石')
) AS u
WHERE
u.row_num=1查詢結(jié)果：
圖片
耗時：1.547s 注意窗口函數(shù)要 MySQL 8.0 及以上版本才有哦。
3.2 使用子查詢和JOIN關(guān)聯(lián)查詢
話不多說直接上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 明顯比使用窗口函數(shù)要慢的多
換一種寫法：
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 明顯比子查詢快很多，這就是大量數(shù)據(jù)下，不同的SQL查詢性能差別是挺大的
3.3 優(yōu)化點
上面的查詢最快的都要1s多，這算是慢查詢了肯定要優(yōu)化，直接加索引
ALTER TABLE `db_test`.`tb_user`
ADD INDEX `idx_name`(`name`) USING BTREE;500w條的數(shù)據(jù)表，加索引會需要小一會兒。加完索引再次分別執(zhí)行上面的SQL語句，執(zhí)行結(jié)果耗時如下：
窗口函數(shù)：0.026s 子查詢：2.229s JOIN關(guān)聯(lián)查詢：0.014s
從結(jié)果上來看，執(zhí)行速度明顯變快了，效果可謂是立竿見影
如果我們查詢不需要出生日期birthday，以最慢的子查詢?yōu)槔?/p>
SELECT id,name from tb_user
where id in (SELECT MAX(id) from tb_user where name in('徐千云', '李億石') group by name)耗時：1.77s，也有算提升，因為這個可以使用覆蓋索引，建設(shè)了回表的次數(shù)。
3.4 討論
也不一定查詢每個名字最新插入的數(shù)據(jù)，可能是查詢每個名字年紀(jì)最小的第一條數(shù)據(jù)，這時候窗口函數(shù)查詢?nèi)缦拢?/p>
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企業(yè)級系統(tǒng)架構(gòu)底層框架封裝，解決業(yè)務(wù)開發(fā)時常見的非功能性需求，防止重復(fù)造輪子，方便業(yè)務(wù)快速開發(fā)和企業(yè)技術(shù)棧框架統(tǒng)一管理。引入組件化的思想實現(xiàn)高內(nèi)聚低耦合并且高度可配置化，做到可插拔。嚴(yán)格控制包依賴和統(tǒng)一版本管理，做到最少化依賴。注重代碼規(guī)范和注釋，非常適合個人學(xué)習(xí)和企業(yè)使用
Github地址：https://github.com/plasticene/plasticene-boot-starter-parent
Gitee地址：https://gitee.com/plasticene3/plasticene-boot-starter-parent
4.elasticsearch實現(xiàn)
數(shù)據(jù)異構(gòu)是解決數(shù)據(jù)量大的一大常見方式，我們經(jīng)常使用elasticsearch來緩解MySQL數(shù)據(jù)庫的查詢壓力，來應(yīng)對海量數(shù)據(jù)的復(fù)雜查詢，那接下來我們就來看看基于elasticsearch怎么實現(xiàn)先分組再組內(nèi)排序取數(shù)據(jù)
在 Elasticsearch 中，可以使用 terms 聚合來實現(xiàn)分組，再結(jié)合 top_hits 聚合實現(xiàn)組內(nèi)排序并取每組的第一條數(shù)據(jù)。terms 聚合用于對某個字段進行分組，而 top_hits 聚合用于在每個分組內(nèi)獲取指定數(shù)量的文檔并按某種順序排序。
查詢每個名字年紀(jì)最小的第一條數(shù)據(jù)：
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：在根查詢中設(shè)置 size 為 0，表示不返回頂層文檔，只返回聚合結(jié)果。
- query 部分：使用 bool 查詢，限定 name 在["徐千云", "李億石"]內(nèi)。
- terms 聚合 (group_by_name)：按 name 分組，size 設(shè)置為較大值（如 1000）以確保返回盡可能多的分組。
- top_hits 聚合 (latest_user)：在每個 name 分組內(nèi)，按 birthday 字段降序排序，并只返回每組的第一個文檔（即年齡最小的 的文檔）。指定 _source 字段過濾以返回所需字段。
terms 聚合的 size 限制了返回分組的數(shù)量。若需要更多分組，可以增大 size 或使用 composite 聚合。對于較大數(shù)據(jù)集，此查詢可能較慢，因為需要對分組中的每個文檔進行排序，直接看代碼：
public Integer aggregateResults(UserQueryDTO dto, Consumer<List<UserInfo>> consumer) {
finalint pageSize = 500;
Integer total = 0;
Map<String, Object> afterKey = null;
while (true) {
// 創(chuàng)建 SearchRequest
SearchRequest searchRequest = new SearchRequest(getAliasName());
// 構(gòu)建查詢條件
BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
if (StringUtils.isNotBlank(dto.getTypes())) {
boolQueryBuilder.filter(QueryBuilders.termsQuery("name", dto.getNames));
}
// Composite聚合實現(xiàn)并分頁
CompositeAggregationBuilder compositeAggregation = AggregationBuilders
.composite("group_by_name",
Lists.newArrayList( new TermsValuesSourceBuilder("name").field("name")))
.size(pageSize);
// 設(shè)置分頁的 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);
// 構(gòu)建 SearchSourceBuilder size=0不返回文檔，只返回聚合結(jié)果
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder()
.size(0)
.query(boolQueryBuilder)
.aggregation(compositeAggregation);
// 設(shè)置搜索請求，執(zhí)行搜索
searchRequest.source(searchSourceBuilder);
SearchResponse response = search(searchRequest);
// 處理分頁聚合結(jié)果
List<String> dataList = new ArrayList<>();
ParsedComposite compositeAgg = response.getAggregations().get("group_by_name");
// 遍歷當(dāng)前頁的分組結(jié)果
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);
// 回調(diào)
consumer.accept(results);
total = total + results.size();
// 更新 afterKey，為下一頁查詢準(zhǔn)備, 如果afterKey為空，說明已經(jīng)查詢到最后一頁
afterKey = compositeAgg.afterKey();
if (afterKey == null) {
break;
}
}
return total;
}afterKey 參數(shù)：在 CompositeAggregationBuilder 中的 aggregateAfter(afterKey) 設(shè)置為上一次查詢的 afterKey，用于實現(xiàn)分頁。
分頁循環(huán)：每次查詢一頁數(shù)據(jù)，更新 afterKey，直到 afterKey 為 null，表示已達到最后一頁。
TopHitsAggregationBuilder：按 id 降序排序并返回每組中的最新文檔，用于獲取分組的最新記錄。
不知道你是否有注意到方法aggregateResults()有一個回調(diào)函數(shù)參數(shù)Consumer<List<UserInfo>> consumer，當(dāng)我們在操作大批量數(shù)據(jù)時，不可能一次性查出所有數(shù)據(jù)來處理，性能扛不住，只能分批分批查詢，如果分頁查詢出來放到一個集合最后在處理，內(nèi)存也支撐不住，這時候我們只在分頁查詢數(shù)據(jù)的同時處理相關(guān)數(shù)據(jù)，比如分頁每頁200條查詢出200個名字年紀(jì)最小的人，并把他的更新時間更新為當(dāng)前，這樣就可以寫一個更新更新時間的方法，傳入aggregateResults()，等分頁查詢出用戶之后，根據(jù)用戶id回調(diào)更新即可。
5.總結(jié)
分組后組內(nèi)排序取第一條記錄的操作，是 SQL 查詢中的高頻需求。推薦優(yōu)先使用窗口函數(shù) ROW_NUMBER()，其次是子查詢方法，并根據(jù)實際業(yè)務(wù)場景選擇合適的實現(xiàn)方式。同時，結(jié)合索引和字段優(yōu)化，可以顯著提升查詢性能。當(dāng)然這是針對大批量查詢的情況下，如果你這是查詢一兩個姓名的最小年紀(jì)這種，你完全可以查詢出所有數(shù)據(jù)，在Java代碼邏輯中利用內(nèi)存分組之后再排序，取相應(yīng)數(shù)據(jù)即可。
