上亿数据怎么玩深度分页?兼容MySQL + ES + MongoDB

本文涉及的产品
云数据库 MongoDB,独享型 2核8GB
推荐场景:
构建全方位客户视图
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 没工夫分库分表,如何快速实现深度分页~

面试题 & 真实经历

面试题:在数据量很大的情况下,怎么实现深度分页?

大家在面试时,或者准备面试中可能会遇到上述的问题,大多的回答基本上是分库分表建索引,这是一种很标准的正确回答,但现实总是很骨感,所以面试官一般会追问你一句,现在工期不足,人员不足,该怎么实现深度分页?

这个时候没有实际经验的同学基本麻爪,So,请听我娓娓道来。

惨痛的教训

首先必须明确一点:深度分页可以做,但是深度随机跳页绝对需要禁止。

上一张图:

你们猜,我点一下第142360页,服务会不会爆炸?

MySQLMongoDB数据库还好,本身就是专业的数据库,处理的不好,最多就是慢,但如果涉及到ES,性质就不一样了,我们不得不利用 SearchAfter Api,去循环获取数据,这就牵扯到内存占用的问题,如果当时代码写的不优雅,直接就可能导致内存溢出。

为什么不能允许随机深度跳页

从技术的角度浅显的聊一聊为什么不能允许随机深度跳页,或者说为什么不建议深度分页

MySQL

分页的基本原理:

SELECT * FROM test ORDER BY id DESC LIMIT 10000, 20;

LIMIT 10000 , 20的意思扫描满足条件的10020行,扔掉前面的10000行,返回最后的20行。如果是LIMIT 1000000 , 100,需要扫描1000100 行,在一个高并发的应用里,每次查询需要扫描超过100W行,不炸才怪。

MongoDB

分页的基本原理:

db.t_data.find().limit(5).skip(5);

同样的,随着页码的增大,skip 跳过的条目也会随之变大,而这个操作是通过 cursor 的迭代器来实现的,对于cpu的消耗会非常明显,当页码非常大时且频繁时,必然爆炸。

ElasticSearch

从业务的角度来说,ElasticSearch不是典型的数据库,它是一个搜索引擎,如果在筛选条件下没有搜索出想要的数据,继续深度分页也不会找到想要的数据,退一步讲,假如我们把ES作为数据库来使用进行查询,在进行分页的时候一定会遇到max_result_window 的限制,看到没,官方都告诉你最大偏移量限制是一万。

查询流程:

  1. 如查询第501页,每页10条,客户端发送请求到某节点
  2. 此节点将数据广播到各个分片,各分片各自查询前 5010 条数据
  3. 查询结果返回至该节点,然后对数据进行整合,取出前 5010 条数据
  4. 返回给客户端

由此可以看出为什么要限制偏移量,另外,如果使用 Search After 这种滚动式API进行深度跳页查询,也是一样需要每次滚动几千条,可能一共需要滚动上百万,千万条数据,就为了最后的20条数据,效率可想而知。

再次和产品对线

俗话说的好,技术解决不了的问题,就由业务来解决!

在实习的时候信了产品的邪,必须实现深度分页 + 跳页,如今必须拨乱反正,业务上必须有如下更改:

  • 尽可能的增加默认的筛选条件,如:时间周期,目的是为了减少数据量的展示
  • 修改跳页的展现方式,改为滚动显示,或小范围跳页

滚动显示参考图:

小规模跳页参考图:

通用解决方案

短时间内快速解决的方案主要是以下几点:

  • 必备:对排序字段,筛选条件务必设置好索引
  • 核心:利用小范围页码的已知数据,或者滚动加载的已知数据,减少偏移量
  • 额外:如果遇到不好处理的情况,也可以获取多余的数据,进行一定的截取,性能影响并不大

MySQL

原分页SQL:

# 第一页
SELECT * FROM `year_score` where `year` = 2017 ORDER BY id limit 0, 20;

# 第N页
SELECT * FROM `year_score` where `year` = 2017 ORDER BY id limit (N - 1) * 20, 20; 

通过上下文关系,改写为:

# XXXX 代表已知的数据
SELECT * FROM `year_score` where `year` = 2017 and id > XXXX ORDER BY id limit 20;

没内鬼,来点干货!SQL优化和诊断 一文中提到过,LIMIT会在满足条件下停止查询,因此该方案的扫描总量会急剧减少,效率提升Max!

ES

方案和MySQL相同,此时我们就可以随用所欲的使用 FROM-TO Api,而且不用考虑最大限制的问题。

MongoDB

方案基本类似,基本代码如下:

相关性能测试:

如果非要深度随机跳页

如果你没有杠过产品经理,又该怎么办呢,没关系,还有一丝丝的机会。

SQL优化 一文中还提到过MySQL深度分页的处理技巧,代码如下:

# 反例(耗时129.570s)
select * from task_result LIMIT 20000000, 10;

# 正例(耗时5.114s)
SELECT a.* FROM task_result a, (select id from task_result LIMIT 20000000, 10) b where a.id = b.id;

# 说明
# task_result表为生产环境的一个表,总数据量为3400万,id为主键,偏移量达到2000万

该方案的核心逻辑即基于聚簇索引,在不通过回表的情况下,快速拿到指定偏移量数据的主键ID,然后利用聚簇索引进行回表查询,此时总量仅为10条,效率很高。

因此我们在处理MySQLESMongoDB时,也可以采用一样的办法:

  1. 限制获取的字段,只通过筛选条件,深度分页获取主键ID
  2. 通过主键ID定向查询需要的数据

瑕疵:当偏移量非常大时,耗时较长,如文中的 5s

最后

参考文章:MongoDB中文社区

如果觉得对你有用的话,不要忘记点个赞啊~

相关实践学习
MongoDB数据库入门
MongoDB数据库入门实验。
快速掌握 MongoDB 数据库
本课程主要讲解MongoDB数据库的基本知识,包括MongoDB数据库的安装、配置、服务的启动、数据的CRUD操作函数使用、MongoDB索引的使用(唯一索引、地理索引、过期索引、全文索引等)、MapReduce操作实现、用户管理、Java对MongoDB的操作支持(基于2.x驱动与3.x驱动的完全讲解)。 通过学习此课程,读者将具备MongoDB数据库的开发能力,并且能够使用MongoDB进行项目开发。   相关的阿里云产品:云数据库 MongoDB版 云数据库MongoDB版支持ReplicaSet和Sharding两种部署架构,具备安全审计,时间点备份等多项企业能力。在互联网、物联网、游戏、金融等领域被广泛采用。 云数据库MongoDB版(ApsaraDB for MongoDB)完全兼容MongoDB协议,基于飞天分布式系统和高可靠存储引擎,提供多节点高可用架构、弹性扩容、容灾、备份回滚、性能优化等解决方案。 产品详情: https://www.aliyun.com/product/mongodb
相关文章
|
7天前
|
存储 关系型数据库 MySQL
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
36 6
|
1月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
141 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
19天前
|
SQL 关系型数据库 MySQL
mysql分页读取数据重复问题
在服务端开发中,与MySQL数据库进行数据交互时,常因数据量大、网络延迟等因素需分页读取数据。文章介绍了使用`limit`和`offset`参数实现分页的方法,并针对分页过程中可能出现的数据重复问题进行了详细分析,提出了利用时间戳或确保排序规则绝对性等解决方案。
|
24天前
|
关系型数据库 MySQL 数据库
GBase 数据库如何像MYSQL一样存放多行数据
GBase 数据库如何像MYSQL一样存放多行数据
|
1月前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
在项目中,为了解决Redis与Mysql的数据一致性问题,我们采用了多种策略:对于低一致性要求的数据,不做特别处理;时效性数据通过设置缓存过期时间来减少不一致风险;高一致性但时效性要求不高的数据,利用MQ异步同步确保最终一致性;而对一致性和时效性都有高要求的数据,则采用分布式事务(如Seata TCC模式)来保障。
64 14
|
26天前
|
搜索推荐 关系型数据库 MySQL
MySQL中的模糊匹配技巧:无需ES的高效实现
在数据库应用中,模糊匹配是一个常见的需求,尤其在处理搜索功能时。虽然Elasticsearch(ES)等搜索引擎在处理文本搜索方面表现出色,但在一些场景下,直接使用MySQL数据库实现模糊匹配也是一个经济且高效的选择。本文将分享如何在不引入ES的情况下,利用MySQL实现模糊匹配的五大步骤和十个实战案例。
107 1
|
26天前
|
自然语言处理 监控 关系型数据库
MySQL模糊匹配技巧:无需ES的高效实现
在数据库应用中,模糊匹配是一个常见的需求,尤其是在不引入Elasticsearch(ES)等外部搜索引擎的情况下。MySQL作为强大的关系型数据库,提供了多种实现模糊匹配的方法。本文将分享如何在MySQL中实现模糊匹配,并提供五大步骤和十个实战案例,帮助你提升查询效率和性能。
145 1
|
1月前
|
消息中间件 JSON NoSQL
从 ES Kafka Mongodb Restful ... 取到 json 之后
JSON 是一种广泛使用的数据交换格式,但其计算和处理能力有限。esProc SPL 是一款强大的开源计算引擎,能够高效解析 JSON 数据,并支持复杂的过滤、分组、连接等操作。它不仅兼容多种数据源,如 RESTful、ElasticSearch、MongoDB 和 Kafka,还提供了游标对象处理大数据流,支持与 Java 应用无缝集成,实现灵活的业务逻辑处理。
|
分布式计算 关系型数据库 MySQL
E-Mapreduce如何处理RDS的数据
目前网站的一些业务数据存在了数据库中,这些数据往往需要做进一步的分析,如:需要跟一些日志数据关联分析,或者需要进行一些如机器学习的分析。在阿里云上,目前E-Mapreduce可以满足这类进一步分析的需求。
4979 0
|
11天前
|
关系型数据库 MySQL 数据库
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
81 15
下一篇
DataWorks