乐趣区

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

jiezi

面试题 & 实在经验

面试题：在数据量很大的状况下，怎么实现深度分页？

大家在面试时，或者筹备面试中可能会遇到上述的问题，大多的答复基本上是 分库分表建索引 ，这是一种很 规范的正确答复，但事实总是很骨感，所以面试官个别会诘问你一句，<font color=”red”> 当初工期有余，人员不足，该怎么实现深度分页？</font>

这个时候没有理论教训的同学根本麻爪，So，请听我娓娓道来。

惨痛的教训

首先必须明确一点：深度分页能够做，然而 <font color=”red”> 深度随机跳页相对须要禁止。</font>

上一张图：

你们猜，我点一下第 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 的限度，看到没，官网都通知你最大偏移量限度是一万。

查问流程：

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

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

再次和产品对线

<font color=”red”> 俗话说的好，技术解决不了的问题，就由业务来解决！</font>

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

  • <font color=”red”> 尽可能的减少默认的筛选条件，如：工夫周期 </font>，目标是为了缩小数据量的展现
  • <font color=”red”> 批改跳页的展示形式，改为滚动显示，或小范畴跳页 </font>

滚动显示参考图：

小规模跳页参考图：

通用解决方案

短时间内疾速解决的计划次要是以下几点：

  • 必备：对排序字段，筛选条件务必设置好索引
  • 外围：<font color=”red”> 利用小范畴页码的已知数据，或者滚动加载的已知数据，缩小偏移量 </font>
  • 额定：如果遇到不好解决的状况，也能够获取多余的数据，进行肯定的截取，性能影响并不大

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

因而咱们在解决 MySQL，ES，MongoDB 时，也能够采纳一样的方法：

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

瑕疵：当偏移量十分大时，耗时较长，如文中的 5s

最初

参考文章：MongoDB 中文社区

感激 @程大设计师 为我倾情设计的二维码????

如果感觉对你有用的话，不要遗记点个赞啊~

退出移动版