面試題 & 真實經(jīng)歷

面試題：在數(shù)據(jù)量很大的情況下，怎么實現(xiàn)深度分頁？

大家在面試時，或者準(zhǔn)備面試中可能會遇到上述的問題，大多的回答基本上是分庫分表建索引，這是一種很標(biāo)準(zhǔn)的正確回答，但現(xiàn)實總是很骨感，所以面試官一般會追問你一句，現(xiàn)在工期不足，人員不足，該怎么實現(xiàn)深度分頁？

這個時候沒有實際經(jīng)驗的同學(xué)基本麻爪，So，請聽我娓娓道來。

慘痛的教訓(xùn)

首先必須明確一點：深度分頁可以做，但是深度隨機跳頁絕對需要禁止。

上一張圖：

上億數(shù)據(jù)怎么玩深度分頁？兼容MySQL + ES + MongoDB

你們猜，我點一下第142360頁，服務(wù)會不會爆炸？

像MySQL，MongoDB數(shù)據(jù)庫還好，本身就是專業(yè)的數(shù)據(jù)庫，處理的不好，最多就是慢，但如果涉及到ES，性質(zhì)就不一樣了，我們不得不利用 SearchAfter Api，去循環(huán)獲取數(shù)據(jù)，這就牽扯到內(nèi)存占用的問題，如果當(dāng)時代碼寫的不優(yōu)雅，直接就可能導(dǎo)致內(nèi)存溢出。

為什么不能允許隨機深度跳頁

從技術(shù)的角度淺顯的聊一聊為什么不能允許隨機深度跳頁，或者說為什么不建議深度分頁

MySQL

分頁的基本原理：

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

LIMIT 10000 , 20的意思掃描滿足條件的10020行，扔掉前面的10000行，返回最后的20行。如果是LIMIT 1000000 , 100，需要掃描1000100 行，在一個高并發(fā)的應(yīng)用里，每次查詢需要掃描超過100W行，不炸才怪。

MongoDB

分頁的基本原理：

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

同樣的，隨著頁碼的增大，skip 跳過的條目也會隨之變大，而這個操作是通過 cursor 的迭代器來實現(xiàn)的，對于cpu的消耗會非常明顯，當(dāng)頁碼非常大時且頻繁時，必然爆炸。

ElasticSearch

從業(yè)務(wù)的角度來說，ElasticSearch不是典型的數(shù)據(jù)庫，它是一個搜索引擎，如果在篩選條件下沒有搜索出想要的數(shù)據(jù)，繼續(xù)深度分頁也不會找到想要的數(shù)據(jù)，退一步講，假如我們把ES作為數(shù)據(jù)庫來使用進行查詢，在進行分頁的時候一定會遇到max_result_window的限制，看到?jīng)]，官方都告訴你最大偏移量限制是一萬。

查詢流程：

如查詢第501頁，每頁10條，客戶端發(fā)送請求到某節(jié)點
此節(jié)點將數(shù)據(jù)廣播到各個分片，各分片各自查詢前 5010 條數(shù)據(jù)
查詢結(jié)果返回至該節(jié)點，然后對數(shù)據(jù)進行整合，取出前 5010 條數(shù)據(jù)
返回給客戶端

由此可以看出為什么要限制偏移量，另外，如果使用 Search After 這種滾動式API進行深度跳頁查詢，也是一樣需要每次滾動幾千條，可能一共需要滾動上百萬，千萬條數(shù)據(jù)，就為了最后的20條數(shù)據(jù)，效率可想而知。

再次和產(chǎn)品對線

俗話說的好，技術(shù)解決不了的問題，就由業(yè)務(wù)來解決！

在實習(xí)的時候信了產(chǎn)品的邪，必須實現(xiàn)深度分頁 + 跳頁，如今必須撥亂反正，業(yè)務(wù)上必須有如下更改：

盡可能的增加默認(rèn)的篩選條件，如：時間周期，目的是為了減少數(shù)據(jù)量的展示
修改跳頁的展現(xiàn)方式，改為滾動顯示，或小范圍跳頁

滾動顯示參考圖：

小規(guī)模跳頁參考圖：

通用解決方案

短時間內(nèi)快速解決的方案主要是以下幾點：

必備：對排序字段，篩選條件務(wù)必設(shè)置好索引
核心：利用小范圍頁碼的已知數(shù)據(jù)，或者滾動加載的已知數(shù)據(jù)，減少偏移量
額外：如果遇到不好處理的情況，也可以獲取多余的數(shù)據(jù)，進行一定的截取，性能影響并不大

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;

通過上下文關(guān)系，改寫為：

# XXXX 代表已知的數(shù)據(jù)
SELECT * FROM `year_score` where `year` = 2017 and id > XXXX ORDER BY id limit 20;

在 沒內(nèi)鬼，來點干貨！SQL優(yōu)化和診斷 一文中提到過，LIMIT會在滿足條件下停止查詢，因此該方案的掃描總量會急劇減少，效率提升Max！

ES

方案和MySQL相同，此時我們就可以隨心所欲的使用 FROM-TO Api，而且不用考慮最大限制的問題。

MongoDB

方案基本類似，基本代碼如下：

相關(guān)性能測試：

如果非要深度隨機跳頁

如果你沒有杠過產(chǎn)品經(jīng)理，又該怎么辦呢，沒關(guān)系，還有一絲絲的機會。

在 SQL優(yōu)化 一文中還提到過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表為生產(chǎn)環(huán)境的一個表，總數(shù)據(jù)量為3400萬，id為主鍵，偏移量達到2000萬

該方案的核心邏輯即基于聚簇索引，在不通過回表的情況下，快速拿到指定偏移量數(shù)據(jù)的主鍵ID，然后利用聚簇索引進行回表查詢，此時總量僅為10條，效率很高。

因此我們在處理MySQL，ES，MongoDB時，也可以采用一樣的辦法：