上亿数据怎么玩深度分页？兼容MySQL + ES + MongoDB
<h2 id="面试题--真实经历">面试题 & 真实经历</h2><blockquote>
<p><em><strong>面试题：</strong></em>在数据量很大的情况下，怎么实现深度分页？</p>
</blockquote>
<p>大家在面试时，或者准备面试中可能会遇到上述的问题，大多的回答基本上是<code>分库分表建索引</code>，这是一种很<code>标准的正确回答</code>，但现实总是很骨感，所以面试官一般会追问你一句，<font color="red">现在工期不足，人员不足，该怎么实现深度分页？</font></p>
<p>这个时候没有实际经验的同学基本麻爪，So，请听我娓娓道来。</p>
<h2 id="惨痛的教训">惨痛的教训</h2>
<p><em><strong>首先必须明确一点：</strong></em>深度分页可以做，但是<font color="red">深度随机跳页绝对需要禁止。</font></p>
<p>上一张图：</p>
<p><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAyMC83LzE1LzE3MzRlNDA4MzAxMzA5NWY?x-oss-process=image/format,png" alt="" loading="lazy"></p>
<p>你们猜，我点一下第<code>142360</code>页，服务会不会爆炸？</p>
<p>像<code>MySQL</code>，<code>MongoDB</code>数据库还好，本身就是专业的数据库，处理的不好，最多就是慢，但如果涉及到<code>ES</code>，性质就不一样了，我们不得不利用 <code>SearchAfter</code> Api，去循环获取数据，这就牵扯到内存占用的问题，如果当时代码写的不优雅，直接就可能导致内存溢出。</p>
<h2 id="为什么不能允许随机深度跳页">为什么不能允许随机深度跳页</h2>
<p>从技术的角度浅显的聊一聊为什么不能允许随机深度跳页，或者说为什么不建议深度分页</p>
<h3 id="mysql">MySQL</h3>
<p>分页的基本原理：</p>
<pre><code class="language-mysql">SELECT * FROM test ORDER BY id DESC LIMIT 10000, 20;
</code></pre>
<p>LIMIT 10000 , 20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行。如果是LIMIT 1000000 , 100，需要扫描1000100 行，在一个高并发的应用里，每次查询需要扫描超过100W行，不炸才怪。</p>
<h3 id="mongodb">MongoDB</h3>
<p>分页的基本原理：</p>
<pre><code class="language-mysql">db.t_data.find().limit(5).skip(5);
</code></pre>
<p>同样的，随着页码的增大，skip 跳过的条目也会随之变大，而这个操作是通过 cursor 的迭代器来实现的，对于cpu的消耗会非常明显，当页码非常大时且频繁时，必然爆炸。</p>
<h3 id="elasticsearch">ElasticSearch</h3>
<p>从业务的角度来说，<code>ElasticSearch</code>不是典型的数据库，它是一个搜索引擎，如果在筛选条件下没有搜索出想要的数据，继续深度分页也不会找到想要的数据，退一步讲，假如我们把<code>ES</code>作为数据库来使用进行查询，在进行分页的时候一定会遇到<code>max_result_window </code>的限制，看到没，官方都告诉你最大偏移量限制是一万。</p>
<p>查询流程：</p>
<ol>
<li>如查询第501页，每页10条，客户端发送请求到某节点</li>
<li>此节点将数据广播到各个分片，各分片各自查询前 5010 条数据</li>
<li>查询结果返回至该节点，然后对数据进行整合，取出前 5010 条数据</li>
<li>返回给客户端</li>
</ol>
<p>由此可以看出为什么要限制偏移量，另外，如果使用 <code>Search After</code> 这种滚动式API进行深度跳页查询，也是一样需要每次滚动几千条，可能一共需要滚动上百万，千万条数据，就为了最后的20条数据，效率可想而知。</p>
<h2 id="再次和产品对线">再次和产品对线</h2>
<p><font color="red">俗话说的好，技术解决不了的问题，就由业务来解决！</font></p>
<p>在实习的时候信了产品的邪，必须实现深度分页 + 跳页，如今必须<code>拨乱反正</code>，业务上必须有如下更改：</p>
<ul>
<li><font color="red">尽可能的增加默认的筛选条件，如：时间周期</font>，目的是为了减少数据量的展示</li>
<li><font color="red">修改跳页的展现方式，改为滚动显示，或小范围跳页</font></li>
</ul>
<p>滚动显示参考图：</p>
<p><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAyMC83LzE1LzE3MzRlNDBjNWIyMTNmMTc?x-oss-process=image/format,png" alt="" loading="lazy"></p>
<p>小规模跳页参考图：</p>
<p><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAyMC83LzE1LzE3MzRlNDBlY2M2MzQ1NzA?x-oss-process=image/format,png" alt="" loading="lazy"></p>
<h2 id="通用解决方案">通用解决方案</h2>
<p>短时间内快速解决的方案主要是以下几点：</p>
<ul>
<li>必备：对排序字段，筛选条件务必设置好索引</li>
<li>核心：<font color="red">利用小范围页码的已知数据，或者滚动加载的已知数据，减少偏移量</font></li>
<li>额外：如果遇到不好处理的情况，也可以获取多余的数据，进行一定的截取，性能影响并不大</li>
</ul>
<h3 id="mysql-1">MySQL</h3>
<p>原分页SQL：</p>
<pre><code class="language-mysql"># 第一页
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;
</code></pre>
<p>通过上下文关系，改写为：</p>
<pre><code class="language-mysql"># XXXX 代表已知的数据
SELECT * FROM `year_score` where `year` = 2017 and id > XXXX ORDER BY id limit 20;
</code></pre>
<p>在 没内鬼，来点干货！SQL优化和诊断 一文中提到过，LIMIT会在满足条件下停止查询，因此该方案的扫描总量会急剧减少，效率提升Max！</p>
<h3 id="es">ES</h3>
<p>方案和<code>MySQL</code>相同，此时我们就可以随用所欲的使用 <code>FROM-TO</code> Api，而且不用考虑最大限制的问题。</p>
<h3 id="mongodb-1">MongoDB</h3>
<p>方案基本类似，基本代码如下：</p>
<p><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAyMC83LzE1LzE3MzRlNDExOWVhNjcxMDU?x-oss-process=image/format,png" alt="" loading="lazy"></p>
<p>相关性能测试：</p>
<p><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAyMC83LzE1LzE3MzRlNDEzOGUwMTQ2YjE?x-oss-process=image/format,png" alt="" loading="lazy"></p>
<h2 id="如果非要深度随机跳页">如果非要深度随机跳页</h2>
<p>如果你没有杠过产品经理，又该怎么办呢，没关系，还有一丝丝的机会。</p>
<p>在 SQL优化 一文中还提到过<code>MySQL</code>深度分页的处理技巧，代码如下：</p>
<pre><code class="language-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万
</code></pre>
<p>该方案的核心逻辑即基于<code>聚簇索引</code>，在不通过<code>回表</code>的情况下，快速拿到指定偏移量数据的主键ID，然后利用<code>聚簇索引</code>进行回表查询，此时总量仅为10条，效率很高。</p>
<p>因此我们在处理<code>MySQL</code>，<code>ES</code>，<code>MongoDB</code>时，也可以采用一样的办法：</p>
<ol>
<li>限制获取的字段，只通过筛选条件，深度分页获取主键ID</li>
<li>通过主键ID定向查询需要的数据</li>
</ol>
<p>瑕疵：当偏移量非常大时，耗时较长，如文中的 5s</p>
<h2 id="最后">最后</h2>
<p>参考文章：MongoDB中文社区</p>
<p>感谢 @程大设计师 为我倾情设计的二维码😜</p>
<p>如果觉得对你有用的话，不要忘记点个赞啊~</p>
<p><img src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAyMC83LzE1LzE3MzRlNDE2YTRkMDRjZTQ?x-oss-process=image/format,png" alt="" loading="lazy"></p><br><br>
来源：https://www.cnblogs.com/kkzhilu/p/13311250.html
頁:
[1]