如何在千万级数据中查询 10W 的数据并排序？都有什么方案？

2024-02-05 20:30
文章标签 数据 查询 排序 方案 千万级 10w

本文主要是介绍如何在千万级数据中查询 10W 的数据并排序？都有什么方案？，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

881eecab0a16b5a0de8e8eb2a2d40dce.jpeg

程序员的成长之路

互联网/程序员/技术/资料共享 

关注

阅读本文大概需要 7 分钟。

来自：https://juejin.cn/post/7104090532015505416

前言

在开发中遇到一个业务诉求，需要在千万量级的底池数据中筛选出不超过 10W 的数据，并根据配置的权重规则进行排序、打散（如同一个类目下的商品数据不能连续出现 3 次）。

下面对该业务诉求的实现，设计思路和方案优化进行介绍，对「千万量级数据中查询 10W 量级的数据」设计了如下方案

  1. 多线程 + CK 翻页方案

  2. ES scroll scan 深翻页方案

  3. ES + Hbase 组合方案

  4. RediSearch + RedisJSON 组合方案

初版设计方案

整体方案设计为

  1. 先根据配置的「筛选规则」，从底池表中筛选出「目标数据」

  2. 在根据配置的「排序规则」，对「目标数据」进行排序，得到「结果数据」

技术方案如下

  1. 每天运行导数任务，把现有的千万量级的底池数据（Hive 表）导入到 Clickhouse 中，后续使用 CK 表进行数据筛选。

  2. 将业务配置的筛选规则和排序规则，构建为一个「筛选 + 排序」对象 SelectionQueryCondition。

  3. 从 CK 底池表取「目标数据」时，开启多线程，进行分页筛选，将获取到的「目标数据」存放到 result 列表中。

//分页大小  默认 5000
int pageSize = this.getPageSize();
//页码数
int pageCnt = totalNum / this.getPageSize() + 1;List<Map<String, Object>> result = Lists.newArrayList();
List<Future<List<Map<String, Object>>>> futureList = new ArrayList<>(pageCnt);//开启多线程调用
for (int i = 1; i <= pageCnt; i++) {//将业务配置的筛选规则和排序规则 构建为 SelectionQueryCondition 对象SelectionQueryCondition selectionQueryCondition = buildSelectionQueryCondition(selectionQueryRuleData);selectionQueryCondition.setPageSize(pageSize);selectionQueryCondition.setPage(i);futureList.add(selectionQueryEventPool.submit(new QuerySelectionDataThread(selectionQueryCondition)));
}for (Future<List<Map<String, Object>>> future : futureList) {//RPC 调用List<Map<String, Object>> queryRes = future.get(20, TimeUnit.SECONDS);if (CollectionUtils.isNotEmpty(queryRes)) {// 将目标数据存放在 result 中result.addAll(queryRes);}
}

  1. 对目标数据 result 进行排序，得到最终的「结果数据」。

CK分页查询

在「初版设计方案」章节的第 3 步提到了「从 CK 底池表取目标数据时，开启多线程，进行分页筛选」。此处对 CK 分页查询进行介绍。

  1. 封装了 queryPoolSkuList 方法，负责从 CK 表中获得目标数据。该方法内部调用了 sqlSession.selectList 方法。

public List<Map<String, Object>> queryPoolSkuList( Map<String, Object> params ) {List<Map<String, Object>> resultMaps = new ArrayList<>();QueryCondition queryCondition = parseQueryCondition(params);List<Map<String, Object>> mapList = lianNuDao.queryPoolSkuList(getCkDt(),queryCondition);if (CollectionUtils.isNotEmpty(mapList)) {for (Map<String,Object> data : mapList) {resultMaps.add(camelKey(data));}}return resultMaps;
}
// lianNuDao.queryPoolSkuList@Autowired
@Qualifier("ckSqlNewSession")
private SqlSession sqlSession;public List<Map<String, Object>> queryPoolSkuList( String dt, QueryCondition queryCondition ) {queryCondition.setDt(dt);queryCondition.checkMultiQueryItems();return sqlSession.selectList("LianNu.queryPoolSkuList",queryCondition);
}

  1. sqlSession.selectList 方法中调用了和 CK 交互的 queryPoolSkuList 查询方法，部分代码如下。

<select id="queryPoolSkuList" parameterType="com.jd.bigai.domain.liannu.QueryCondition" resultType="java.util.Map">select sku_pool_id,item_sku_id,skuPoolName,price,......businessTypefrom liannu_sku_pool_indicator_allwheredt=#{dt}and<foreach collection="queryItems" separator=" and " item="queryItem" open=" " close=" " ><choose><when test="queryItem.type == 'equal'">${queryItem.field} = #{queryItem.value}</when>......</choose></foreach><if test="orderBy == null">group by sku_pool_id,item_sku_id</if><if test="orderBy != null">group by sku_pool_id,item_sku_id,${orderBy} order by ${orderBy} ${orderAd}</if><if test="limitEnd != 0">limit #{limitStart},#{limitEnd}</if>
</select>

  1. 可以看到，在 CK 分页查询时，是通过 limit #{limitStart},#{limitEnd} 实现的分页。

limit 分页方案，在「深翻页」时会存在性能问题。初版方案上线后，在 1000W 量级的底池数据中筛选 10W 的数据，最坏耗时会达到 10s~18s 左右。

使用ES Scroll Scan 优化深翻页

对于 CK 深翻页时候的性能问题，进行了优化，使用 Elasticsearch 的 scroll scan 翻页方案进行优化。

ES的翻页方案

关于「ES 翻页方案」，详情参考

  • https://juejin.cn/post/7103848212154286087

ES 翻页，有下面几种方案

  1. from + size 翻页

  2. scroll 翻页

  3. scroll scan 翻页

  4. search after 翻页

d5ab8bf4b608255fd258fc275404a7b6.png

对上述几种翻页方案，查询不同数目的数据，耗时数据如下表。

bb8811df4c49afa0f6733005a50b3a87.png

耗时数据

此处，分别使用 Elasticsearch 的 scroll scan 翻页方案、初版中的 CK 翻页方案进行数据查询，对比其耗时数据。

a637e233766295f01dd3bbe758e18996.jpegceede4c7a541ddf4eac486c2c4015a83.jpeg

如上测试数据，可以发现，以十万，百万，千万量级的底池为例

  1. 底池量级越大，查询相同的数据量，耗时越大

  2. 查询结果 3W 以下时，ES 性能优；查询结果 5W 以上时，CK 多线程性能优

ES+Hbase组合查询方案

在「使用 ES Scroll Scan 优化深翻页」中，使用 Elasticsearch 的 scroll scan 翻页方案对深翻页问题进行了优化，但在实现时为单线程调用，所以最终测试耗时数据并不是特别理想，和 CK 翻页方案性能差不多。

在调研阶段发现，从底池中取出 10W 的目标数据时，一个商品包含多个字段的信息（CK 表中一行记录有 150 个字段信息），如价格、会员价、学生价、库存、好评率等。对于一行记录，当减少获取字段的个数时，查询耗时会有明显下降。如对 sku1的商品，从之前获取价格、会员价、学生价、亲友价、库存等 100 个字段信息，缩减到只获取价格、库存这两个字段信息。

如下图所示，使用 ES 查询方案，对查询同样条数的场景（从千万级底池中筛选出 7W+ 条数据），获取的每条记录的字段个数从 32 缩减到 17，再缩减到 1个（其实是两个字段，一个是商品唯一标识 sku_id，另一个是 ES 对每条文档记录的 doc_id）时，查询的耗时会从 9.3s 下降到 4.2s，再下降到 2.4s。

954591a69bd59b35a1c0e53a2106a7ee.jpeg

从中可以得出如下结论

  1. 一次 ES 查询中，若查询字段和信息较多，fetch 阶段的耗时，远大于 query 阶段的耗时。

  2. 一次 ES 查询中，若查询字段和信息较多，通过减少不必要的查询字段，可以显著缩短查询耗时。

下面对结论中涉及的 query 和 fetch 查询阶段进行补充说明。

ES查询的两个阶段：query和fetch

在 ES 中，搜索一般包括两个阶段，query 和 fetch 阶段

  1. query 阶段

  • 根据查询条件，确定要取哪些文档（doc），筛选出文档 ID（doc_id）

fetch 阶段

  • 根据 query 阶段返回的文档 ID（doc_id），取出具体的文档（doc）

ES的filesystem cache

  • ES 会将磁盘中的数据自动缓存到 filesystem cache，在内存中查找，提升了速度

  • 若 filesystem cache 无法容纳索引数据文件，则会基于磁盘查找，此时查询速度会明显变慢

  • 若数量两过大，基于「ES 查询的的 query 和 fetch 两个阶段」，可使用 ES + HBase 架构，保证 ES 的数据量小于 filesystem cache，保证查询速度

组合使用Hbase

  • ref 1-ES 亿级数据检索优化，三秒返回突破性能瓶颈：

  • https://www.infoq.cn/article/wymrl5h80sfawg8u7ede

在上文调研的基础上，发现「减少不必要的查询展示字段」可以明显缩短查询耗时。沿着这个优化思路，参照参考链接 _ref-1_，设计了一种新的查询方案

  1. ES 仅用于条件筛选，ES 的查询结果仅包含记录的唯一标识 sku_id（其实还包含 ES 为每条文档记录的 doc_id）

  2. Hbase 是列存储数据库，每列数据有一个 rowKey。利用 rowKey 筛选一条记录时，复杂度为 O(1)。（类似于从 HashMap 中根据 key 取 value）

  3. 根据 ES 查询返回的唯一标识 sku_id，作为 Hbase 查询中的 rowKey，在 O(1) 复杂度下获取其他信息字段，如价格，库存等。

79632ee3b9b1e885d08afaf36b20adf9.jpeg

使用 ES + Hbase 组合查询方案，在线上进行了小规模的灰度测试。在 1000W 量级的底池数据中筛选 10W 的数据，对比 CK 翻页方案，最坏耗时从 10~18s 优化到了 3~6s 左右。

也应该看到，使用 ES + Hbase 组合查询方案，会增加系统复杂度，同时数据也需要同时存储到 ES 和 Hbase。

RediSearch+RedisJSON优化方案

RediSearch 是基于 Redis 构建的分布式全文搜索和聚合引擎，能以极快的速度在 Redis 数据集上执行复杂的搜索查询。RedisJSON 是一个 Redis 模块，在 Redis 中提供 JSON 支持。RedisJSON 可以和 RediSearch 无缝配合，实现索引和查询 JSON 文档。

根据一些参考资料，RediSearch + RedisJSON 可以实现极高的性能，可谓碾压其他 NoSQL 方案。在后续版本迭代中，可考虑使用该方案来进一步优化。

下面给出 RediSearch + RedisJSON 的部分性能数据。

RediSearch 性能数据

在同等服务器配置下索引了 560 万个文档 (5.3GB)，RediSearch 构建索引的时间为 221 秒，而 Elasticsearch 为 349 秒。RediSearch 比 ES 快了 58%。

数据建立索引后，使用 32 个客户端对两个单词进行检索，RediSearch 的吞吐量达到 12.5K ops/sec，ES 的吞吐量为 3.1K ops/sec，RediSearch 比ES 要快 4 倍。同时，RediSearch 的延迟为 8ms，而 ES 为 10ms，RediSearch 延迟稍微低些。

ec0502e20382b5f1fc9e0a50c29c2bf8.png

RedisJSON 性能数据

根据官网的性能测试报告，RedisJson + RedisSearch 可谓碾压其他 NoSQL

  • 对于隔离写入（isolated writes），RedisJSON 比 MongoDB 快 5.4 倍，比 ES 快 200 倍以上

  • 对于隔离读取（isolated reads），RedisJSON 比 MongoDB 快 12.7 倍，比 ES 快 500 倍以上

在混合工作负载场景中，实时更新不会影响 RedisJSON 的搜索和读取性能，而 ES 会受到影响。

  • RedisJSON 支持的操作数/秒比 MongoDB 高约 50 倍，比 ES 高 7 倍/秒。

  • RedisJSON 的延迟比 MongoDB 低约 90 倍，比 ES 低 23.7 倍。

此外，RedisJSON 的读取、写入和负载搜索延迟，在更高的百分位数中远比 ES 和 MongoDB 稳定。当增加写入比率时，RedisJSON 还能处理越来越高的整体吞吐量。而当写入比率增加时，ES 会降低它可以处理的整体吞吐量。

总结

本文从一个业务诉求触发，对「千万量级数据中查询 10W 量级的数据」介绍了不同的设计方案。对于「在 1000W 量级的底池数据中筛选 10W 的数据」的场景，不同方案的耗时如下

  1. 多线程 + CK 翻页方案，最坏耗时为 10s~18s

  2. 单线程 + ES scroll scan 深翻页方案，相比 CK 方案，并未见到明显优化

  3. ES + Hbase 组合方案，最坏耗时优化到了 3s~6s

  4. RediSearch + RedisJSON 组合方案，后续会实测该方案的耗时

<END>

推荐阅读：

CTO：谁在项目中使用Arrays.asList、ArrayList.subList，就立马滚蛋！

阿里二面：为啥索引可以让查询变快？

互联网初中高级大厂面试题(9个G)
内容包含Java基础、JavaWeb、MySQL性能优化、JVM、锁、百万并发、消息队列、高性能缓存、反射、Spring全家桶原理、微服务、Zookeeper......等技术栈！
⬇戳阅读原文领取！                                  朕已阅

这篇关于如何在千万级数据中查询 10W 的数据并排序？都有什么方案？的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/682072。

相关文章

MySQL 删除数据详解(最新整理)

MySQL 删除数据详解(最新整理)

《MySQL删除数据详解(最新整理)》：本文主要介绍MySQL删除数据的相关知识,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、前言二、mysql 中的三种删除方式1.DELETE语句✅ 基本语法： 示例：2.TRUNCATE语句✅ 基本语
阅读更多...
从入门到精通MySQL联合查询

从入门到精通MySQL联合查询

《从入门到精通MySQL联合查询》：本文主要介绍从入门到精通MySQL联合查询,本文通过实例代码给大家介绍的非常详细,需要的朋友可以参考下... 目录摘要1. 多表联合查询时mysql内部原理2. 内连接3. 外连接4. 自连接5. 子查询6. 合并查询7. 插入查询结果摘要前面我们学习了数据库设计时要满
阅读更多...
MySQL查询JSON数组字段包含特定字符串的方法

MySQL查询JSON数组字段包含特定字符串的方法

《MySQL查询JSON数组字段包含特定字符串的方法》在MySQL数据库中,当某个字段存储的是JSON数组,需要查询数组中包含特定字符串的记录时传统的LIKE语句无法直接使用,下面小编就为大家介绍两种... 目录问题背景解决方案对比1. 精确匹配方案（推荐）2. 模糊匹配方案参数化查询示例使用场景建议性能优
阅读更多...
mysql表操作与查询功能详解

mysql表操作与查询功能详解

《mysql表操作与查询功能详解》本文系统讲解MySQL表操作与查询,涵盖创建、修改、复制表语法,基本查询结构及WHERE、GROUPBY等子句,本文结合实例代码给大家介绍的非常详细,感兴趣的朋友跟随... 目录01.表的操作1.1表操作概览1.2创建表1.3修改表1.4复制表02.基本查询操作2.1 SE
阅读更多...
MyBatisPlus如何优化千万级数据的CRUD

MyBatisPlus如何优化千万级数据的CRUD

《MyBatisPlus如何优化千万级数据的CRUD》最近负责的一个项目,数据库表量级破千万,每次执行CRUD都像走钢丝,稍有不慎就引起数据库报警,本文就结合这个项目的实战经验,聊聊MyBatisPl... 目录背景一、MyBATis Plus 简介二、千万级数据的挑战三、优化 CRUD 的关键策略1. 查
阅读更多...
python实现对数据公钥加密与私钥解密

python实现对数据公钥加密与私钥解密

《python实现对数据公钥加密与私钥解密》这篇文章主要为大家详细介绍了如何使用python实现对数据公钥加密与私钥解密,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录公钥私钥的生成使用公钥加密使用私钥解密公钥私钥的生成这一部分，使用python生成公钥与私钥，然后保存在两个文
阅读更多...
mysql中的数据目录用法及说明

mysql中的数据目录用法及说明

《mysql中的数据目录用法及说明》：本文主要介绍mysql中的数据目录用法及说明,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、背景2、版本3、数据目录4、总结1、背景安装mysql之后，在安装目录下会有一个data目录，我们创建的数据库、创建的表、插入的
阅读更多...
SQLite3 在嵌入式C环境中存储音频/视频文件的最优方案

SQLite3 在嵌入式C环境中存储音频/视频文件的最优方案

《SQLite3在嵌入式C环境中存储音频/视频文件的最优方案》本文探讨了SQLite3在嵌入式C环境中存储音视频文件的优化方案,推荐采用文件路径存储结合元数据管理,兼顾效率与资源限制,小文件可使用B... 目录SQLite3 在嵌入式C环境中存储音频/视频文件的专业方案一、存储策略选择1. 直接存储 vs
阅读更多...
MySQL数据库的内嵌函数和联合查询实例代码

MySQL数据库的内嵌函数和联合查询实例代码

《MySQL数据库的内嵌函数和联合查询实例代码》联合查询是一种将多个查询结果组合在一起的方法,通常使用UNION、UNIONALL、INTERSECT和EXCEPT关键字,下面：本文主要介绍MyS... 目录一.数据库的内嵌函数1.1聚合函数COUNT([DISTINCT] expr)SUM([DISTIN
阅读更多...
Navicat数据表的数据添加,删除及使用sql完成数据的添加过程

Navicat数据表的数据添加,删除及使用sql完成数据的添加过程

《Navicat数据表的数据添加,删除及使用sql完成数据的添加过程》：本文主要介绍Navicat数据表的数据添加,删除及使用sql完成数据的添加过程,具有很好的参考价值,希望对大家有所帮助,如有... 目录Navicat数据表数据添加,删除及使用sql完成数据添加选中操作的表则出现如下界面，查看左下角从左
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2