上億數(shù)據(jù)怎么玩深度分頁(yè)？兼容MySQL + ES + MongoDB
面試題 & 真實(shí)經(jīng)歷
面試題：在數(shù)據(jù)量很大的情況下，怎么實(shí)現(xiàn)深度分頁(yè)?
大家在面試時(shí)，或者準(zhǔn)備面試中可能會(huì)遇到上述的問(wèn)題，大多的回答基本上是分庫(kù)分表建索引，這是一種很標(biāo)準(zhǔn)的正確回答，但現(xiàn)實(shí)總是很骨感，所以面試官一般會(huì)追問(wèn)你一句，現(xiàn)在工期不足，人員不足，該怎么實(shí)現(xiàn)深度分頁(yè)?
這個(gè)時(shí)候沒(méi)有實(shí)際經(jīng)驗(yàn)的同學(xué)基本麻爪，So，請(qǐng)聽(tīng)我娓娓道來(lái)。
慘痛的教訓(xùn)
首先必須明確一點(diǎn)：深度分頁(yè)可以做，但是深度隨機(jī)跳頁(yè)絕對(duì)需要禁止。
上一張圖：
你們猜，我點(diǎn)一下第142360頁(yè)，服務(wù)會(huì)不會(huì)爆炸?
像MySQL，MongoDB數(shù)據(jù)庫(kù)還好，本身就是專(zhuān)業(yè)的數(shù)據(jù)庫(kù)，處理的不好，最多就是慢，但如果涉及到ES，性質(zhì)就不一樣了，我們不得不利用 SearchAfter Api，去循環(huán)獲取數(shù)據(jù)，這就牽扯到內(nèi)存占用的問(wèn)題，如果當(dāng)時(shí)代碼寫(xiě)的不優(yōu)雅，直接就可能導(dǎo)致內(nèi)存溢出。
為什么不能允許隨機(jī)深度跳頁(yè)
從技術(shù)的角度淺顯的聊一聊為什么不能允許隨機(jī)深度跳頁(yè)，或者說(shuō)為什么不建議深度分頁(yè)
MySQL
分頁(yè)的基本原理：
- SELECT * FROM test ORDER BY id DESC LIMIT 10000, 20;
LIMIT 10000 , 20的意思掃描滿(mǎn)足條件的10020行，扔掉前面的10000行，返回最后的20行。如果是LIMIT 1000000 , 100，需要掃描1000100 行，在一個(gè)高并發(fā)的應(yīng)用里，每次查詢(xún)需要掃描超過(guò)100W行，不炸才怪。
MongoDB
分頁(yè)的基本原理：
- db.t_data.find().limit(5).skip(5);
同樣的，隨著頁(yè)碼的增大，skip 跳過(guò)的條目也會(huì)隨之變大，而這個(gè)操作是通過(guò) cursor 的迭代器來(lái)實(shí)現(xiàn)的，對(duì)于cpu的消耗會(huì)非常明顯，當(dāng)頁(yè)碼非常大時(shí)且頻繁時(shí)，必然爆炸。
ElasticSearch
從業(yè)務(wù)的角度來(lái)說(shuō)，ElasticSearch不是典型的數(shù)據(jù)庫(kù)，它是一個(gè)搜索引擎，如果在篩選條件下沒(méi)有搜索出想要的數(shù)據(jù)，繼續(xù)深度分頁(yè)也不會(huì)找到想要的數(shù)據(jù)，退一步講，假如我們把ES作為數(shù)據(jù)庫(kù)來(lái)使用進(jìn)行查詢(xún)，在進(jìn)行分頁(yè)的時(shí)候一定會(huì)遇到max_result_window的限制，看到?jīng)]，官方都告訴你最大偏移量限制是一萬(wàn)。
查詢(xún)流程：
- 如查詢(xún)第501頁(yè)，每頁(yè)10條，客戶(hù)端發(fā)送請(qǐng)求到某節(jié)點(diǎn)
- 此節(jié)點(diǎn)將數(shù)據(jù)廣播到各個(gè)分片，各分片各自查詢(xún)前 5010 條數(shù)據(jù)
- 查詢(xún)結(jié)果返回至該節(jié)點(diǎn)，然后對(duì)數(shù)據(jù)進(jìn)行整合，取出前 5010 條數(shù)據(jù)
- 返回給客戶(hù)端
由此可以看出為什么要限制偏移量，另外，如果使用 Search After 這種滾動(dòng)式API進(jìn)行深度跳頁(yè)查詢(xún)，也是一樣需要每次滾動(dòng)幾千條，可能一共需要滾動(dòng)上百萬(wàn)，千萬(wàn)條數(shù)據(jù)，就為了最后的20條數(shù)據(jù)，效率可想而知。
再次和產(chǎn)品對(duì)線
俗話說(shuō)的好，技術(shù)解決不了的問(wèn)題，就由業(yè)務(wù)來(lái)解決!
在實(shí)習(xí)的時(shí)候信了產(chǎn)品的邪，必須實(shí)現(xiàn)深度分頁(yè) + 跳頁(yè)，如今必須撥亂反正，業(yè)務(wù)上必須有如下更改：
- 盡可能的增加默認(rèn)的篩選條件，如：時(shí)間周期，目的是為了減少數(shù)據(jù)量的展示
- 修改跳頁(yè)的展現(xiàn)方式，改為滾動(dòng)顯示，或小范圍跳頁(yè)
滾動(dòng)顯示參考圖：
小規(guī)模跳頁(yè)參考圖：
通用解決方案
短時(shí)間內(nèi)快速解決的方案主要是以下幾點(diǎn)：
- 必備：對(duì)排序字段，篩選條件務(wù)必設(shè)置好索引
- 核心：利用小范圍頁(yè)碼的已知數(shù)據(jù)，或者滾動(dòng)加載的已知數(shù)據(jù)，減少偏移量
- 額外：如果遇到不好處理的情況，也可以獲取多余的數(shù)據(jù)，進(jìn)行一定的截取，性能影響并不大
MySQL
原分頁(yè)SQL：
- # 第一頁(yè)
- SELECT * FROM `year_score` where `year` = 2017 ORDER BY id limit 0, 20;
- # 第N頁(yè)
- SELECT * FROM `year_score` where `year` = 2017 ORDER BY id limit (N - 1) * 20, 20;
通過(guò)上下文關(guān)系，改寫(xiě)為：
- # XXXX 代表已知的數(shù)據(jù)
- SELECT * FROM `year_score` where `year` = 2017 and id > XXXX ORDER BY id limit 20;
在 沒(méi)內(nèi)鬼，來(lái)點(diǎn)干貨!SQL優(yōu)化和診斷 一文中提到過(guò)，LIMIT會(huì)在滿(mǎn)足條件下停止查詢(xún)，因此該方案的掃描總量會(huì)急劇減少，效率提升Max!
ES
方案和MySQL相同，此時(shí)我們就可以隨用所欲的使用 FROM-TO Api，而且不用考慮最大限制的問(wèn)題。
MongoDB
方案基本類(lèi)似，基本代碼如下：
相關(guān)性能測(cè)試：
如果非要深度隨機(jī)跳頁(yè)
如果你沒(méi)有杠過(guò)產(chǎn)品經(jīng)理，又該怎么辦呢，沒(méi)關(guān)系，還有一絲絲的機(jī)會(huì)。
在 SQL優(yōu)化 一文中還提到過(guò)MySQL深度分頁(yè)的處理技巧，代碼如下：
- # 反例（耗時(shí)129.570s）
- select * from task_result LIMIT 20000000, 10;
- # 正例（耗時(shí)5.114s）
- SELECT a.* FROM task_result a, (select id from task_result LIMIT 20000000, 10) b where a.id = b.id;
- # 說(shuō)明
- # task_result表為生產(chǎn)環(huán)境的一個(gè)表，總數(shù)據(jù)量為3400萬(wàn)，id為主鍵，偏移量達(dá)到2000萬(wàn)
該方案的核心邏輯即基于聚簇索引，在不通過(guò)回表的情況下，快速拿到指定偏移量數(shù)據(jù)的主鍵ID，然后利用聚簇索引進(jìn)行回表查詢(xún)，此時(shí)總量?jī)H為10條，效率很高。
因此我們?cè)谔幚鞰ySQL，ES，MongoDB時(shí)，也可以采用一樣的辦法：
- 限制獲取的字段，只通過(guò)篩選條件，深度分頁(yè)獲取主鍵ID
- 通過(guò)主鍵ID定向查詢(xún)需要的數(shù)據(jù)
瑕疵：當(dāng)偏移量非常大時(shí)，耗時(shí)較長(zhǎng)，如文中的 5s
本文轉(zhuǎn)載自微信公眾號(hào)「是Kerwin啊」，可以通過(guò)以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系是Kerwin啊公眾號(hào)。
