如何兼容 MySQL + ES + MongoDB 实现上亿数据的深度分页?

如何兼容 MySQL + ES + MongoDB 实现上亿数据的深度分页?

面试题 & 真实经历

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

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

惨痛的教训

首先必须明确一点:深度分页可以做,但是<font color="red">深度随机跳页绝对需要禁止。</font>

上一张图:

Snipaste_2020-07-27_17-11-45.png

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

MySQL,MongoDB数据库还好,本身就是专业的数据库,处理的不好,最多就是慢,但如果涉及到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 的限制,看到没,官方都告诉你最大偏移量限制是一万。

查询流程:

  • 如查询第501页,每页10条,客户端发送请求到某节点

  • 此节点将数据广播到各个分片,各分片各自查询前 5010 条数据

  • 查询结果返回至该节点,然后对数据进行整合,取出前 5010 条数据

  • 返回给客户端

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

再次和产品对线

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

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

尽可能的增加默认的筛选条件,如:时间周期,目的是为了减少数据量的展示

修改跳页的展现方式,改为滚动显示,或小范围跳页

滚动显示参考图:

Snipaste_2020-07-27_17-13-49.png

小规模跳页参考图:

Snipaste_2020-07-27_17-13-59.png

通用解决方案

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

  • 必备:对排序字段,筛选条件务必设置好索引

  • 核心:利用小范围页码的已知数据,或者滚动加载的已知数据,减少偏移量

  • 额外:如果遇到不好处理的情况,也可以获取多余的数据,进行一定的截取,性能影响并不大

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

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

Snipaste_2020-07-27_17-15-20.png

相关性能测试:

Snipaste_2020-07-27_17-15-29.png

如果非要深度随机跳页

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

在 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条,效率很高。

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

  • 限制获取的字段,只通过筛选条件,深度分页获取主键ID

  • 通过主键ID定向查询需要的数据

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

文章来源:https://juejin.im/post/5f0de4d06fb9a07e8a19a641

关于如何兼容 MySQL + ES + MongoDB 实现上亿数据的深度分页?的文章就分享到这,如果对你有帮助欢迎继续关注我们哦

本文来自投稿,不代表重蔚自留地立场,如若转载,请注明出处https://www.cwhello.com/40405.html

如有侵犯您的合法权益请发邮件951076433@qq.com联系删除

(0)
php学习php学习订阅用户
上一篇 2022年6月12日 22:56
下一篇 2022年6月12日 22:57

相关推荐

  • 教你mysql删除数据库。

    MySQL删除数据库的命令是`DROP DATABASE`,其基本语法如下: DROP DATABASE database_name; `database_name`是你想要删除的数据库的名称,这个命令会立即删除指定的数据库,并且不会将其数据复制到其他地方,在执行…

    2024年6月20日
    00
  • MySQL中如何定位慢查询?

    做压测的时候有的接口非常的慢,接口的响应时间超过了2秒以上,在运维的监控系统Skvwalking中,在展示的报表中可以看到是哪一个接口比较慢,分析这个接口,可以看到哪部分比较慢,从而知道SQL的具体的执行时间,定…

    2023年8月29日
    00
  • 小编教你删除mysql。

    删除MySQL数据库的步骤如下: 1. 登录到MySQL服务器,在命令行中输入以下命令,然后输入密码: mysql -u root -p 2. 选择要删除的数据库,在MySQL命令行中输入以下命令,将`database_name`替换为要删除的数据库名称…

    2024年6月19日
    00
  • 小编教你ubuntu启动mysql。

    在Ubuntu系统中,启动MySQL数据库服务器非常简单,下面我将详细解释如何进行操作。 我们需要确保已经安装了MySQL,如果还未安装,可以通过以下命令进行安装: sudo apt-get update sudo apt-get install mysql-serv…

    2024年6月16日
    00
  • 经验分享mysql中两张表如何建立连接。

    在MySQL中,分页是一种常见的操作,它可以帮助我们在处理大量数据时更加高效地获取所需信息,两表分页是指在查询过程中涉及到两个表的连接操作,这种情况下实现分页可能会相对复杂一些,本文将详细介绍如何在MySQL…

    2024年6月20日
    00
  • 教你MySQL如何使用一对多关系创建XML文件。

    在MySQL中,我们可以使用一对多关系创建XML文件,一对多关系是指一个表中的记录可以与另一个表中的多个记录相关联,在这种情况下,我们需要创建一个包含主表和从表数据的XML文件,以下是如何使用一对多关系创建XML…

    2024年6月20日
    00
  • mysql中删除表记录delete from和truncate table的用法区别

    昨天开发的时候遇到的问题,整理了一下,分享给大家,可以看看,有需要的话可以利用一下。 mysql中删除表记录delete from和truncate table的用法区别: MySQL中有两种删除表中记录的方法,一种是delete from语句,另…

    2018年4月13日
    0260
  • 说说如何打开mysql数据库,mysql workbench怎么打开数据库。

    在计算机科学中,MySQL是一个广泛使用的开源关系数据库管理系统,它被设计用于处理大量数据,并且提供了许多高级功能,如事务处理、多用户访问和复制等,MySQL数据库可以通过多种方式打开和操作,其中最常用的工具…

    2024年6月15日
    00

联系我们

QQ:951076433

在线咨询:点击这里给我发消息邮件:951076433@qq.com工作时间:周一至周五,9:30-18:30,节假日休息