MySQL 跨庫分頁/ 分表分頁/ 跨庫分頁，為什么這么難？
點擊關(guān)注公眾號，Java干貨及時送達(dá)
作者：菩提樹下的楊過
出處：www.cnblogs.com/yjmyzz/p/12149737.html
當(dāng)業(yè)務(wù)數(shù)據(jù)達(dá)到一定量級(比如：mysql單表記錄量>1千萬)后，通常會考慮“分庫分表”將數(shù)據(jù)分散到不同的庫或表中，這樣可以大大提高讀/寫性能。
但是問題來了，對于 select * from table limit offset , pagesize 這種分頁方式，原來一條語句就可以簡單搞定的事情會變得很復(fù)雜，本文將與大家一起探討分庫分表后"分頁"面臨的新問題。
一、分表對分頁的影響
比如有一張表，里面有8條記錄(為簡單起見，假設(shè)該表上只有1個自增ID），數(shù)學(xué)上可以抽象成1個(有序)數(shù)列(注：為方便討論，不加特殊說明的情況下，文本中數(shù)列的順序，均指升序)
(1，2，3，4，5，6，7，8)
如果要取出上面紅色標(biāo)識的2,3這二條記錄，limit 1,2 就行了。
現(xiàn)在假如分成2張表（即：原來的數(shù)列，拆分成2個非空子數(shù)列），一般來講，有二種常用分法：
1.1 分段法（比如：有時間屬性的數(shù)據(jù)，類似訂單這種，可以按下單時間拆分，每個月1張表）
(1，2，3，4)
(5，6，7，8)
沿用之前的limit x,y的思路，每個分表上 limit 1,2，會得到如下2個子數(shù)列：
(2，3)
(6，7)
然后在內(nèi)存中合并排序，再取前2條 (2，3，6，7) => (2，3) ，貌似看上去也符合預(yù)期（這個思路也稱為歸并），但這只是假象。當(dāng)要取的分頁數(shù)據(jù)落在不同的子數(shù)列上時，就能發(fā)現(xiàn)問題：
(1，2，3，4，5，6，7，8)
比如，我們要從4個位置開始，連續(xù)取2個元素，即: limit 3,2
(1，2，3，4) => limit 3,2 =>(4)
(5，6，7，8) => limit 3,2 =>(8)
最后合并出來的結(jié)果是(4，8) 與正確結(jié)果 (4，5)相比，顯然不對。
1.2 模余均攤法（比如：字段值對2取模求余數(shù)，根據(jù)余數(shù)決定分到哪個表，該方法也簡稱為取余法）
(1，3，5，7)
(2，4，6，8)
歸并排序的思路在分段法上行不通，對于取模均攤同樣也不行，仍以 limit 1,2為例，原始序列取出來的結(jié)果是(2，3)，如果用歸并的思路：
(1，3，5，7)=> limit 1,2 =>(3 ,5)
(2，4，6，8)=> limit 1,2 =>(4, 6)
內(nèi)存合并排序后，取前2個，最終結(jié)果為(3 , 4)
結(jié)論：不管分庫分表采用什么分法，簡單歸并的思路，都無法正確解決分頁問題。另外，MySQL 系列面試題和答案全部整理好了，微信搜索Java技術(shù)棧，在后臺發(fā)送：面試，可以在線閱讀。
二、全局法(limit x+y)
反思一下剛才的歸并思路，本質(zhì)上我們在每個子數(shù)列（即：分表）上limit x,y 時，取出來的數(shù)據(jù)就有可能已經(jīng)產(chǎn)生缺失了。
推薦一個 Spring Boot 基礎(chǔ)教程及實戰(zhàn)示例：https://github.com/javastacks/spring-boot-best-practice
網(wǎng)上有一篇廣為流轉(zhuǎn)的文章"業(yè)界難題-跨庫分頁”，作者在文中提出了一個方案：把范圍擴大，分表sql上的limit x,y 變成 limit 0, x+y ，這樣改寫后，相當(dāng)于分表中把"每頁最后一條數(shù)據(jù)"之前的所有數(shù)據(jù)全都取出來了（當(dāng)然：這里面可能會有不需要的多余數(shù)據(jù))，然后內(nèi)存中合并在一起，再取x偏移量后的y條數(shù)據(jù)。
用前面的例子驗證一下：
原序列：(1，2，3，4，5，6，7，8)，需要取出limit 1,2 ，即：(2，3)
2.1 按分段法拆成2段：
(1 , 2 , 3 , 4) => limit 1,2 =>改寫成 limit 0, 1+2 => (1，2，3)
(5 , 6 , 7 , 8) => limit 1,2 =>改寫成 limit 0, 1+2 => (5，6，7)
將子數(shù)列合并排序=> { 1，2，3，5，6，7} => 按原始偏移量 limit 1,2 =>{2，3} 正確
如果原數(shù)列中要取的數(shù)據(jù)，正好落在2個子數(shù)列上(1，2，3，4，5，6，7，8)，需要取出limit 3,2 ，即：(4，5)
(1 , 2 , 3 , 4) => limit 3,2 =>改寫成 limit 0, 3+2 => (1，2，3，4)
(5 , 6 , 7 , 8) => limit 3,2 =>改寫成 limit 0, 3+2 => (5，6，7，8)
將子數(shù)列合并排序=> (1，2，3，4，5，6，7，8) => 按原始偏移量 limit 3,2 => (4，5) 也符合預(yù)期。
2.2 取模均攤拆成2段
(1，3，5，7) => limit 1,2 ->改寫成 limit 0, 1+2 => (1，3 ，5)
(2，4，6，8) => limit 1,2 ->改寫成 limit 0, 1+2=> (2，4，6)
將子序列合并=> (1，2，3，4，5，6) => 按原始偏移量 limit 1,2 =>(2，3) 正確
該方法缺點也很明顯：取出的記錄太多了，比如 limit 10000000,10 -> 改寫后變成 limit 0, 10000010 遇到海量數(shù)據(jù)，mysql中查詢有可能直接超時，這么多數(shù)據(jù)從db傳到應(yīng)用層，網(wǎng)絡(luò)開銷也很大，更不用說如果是java應(yīng)用，大量數(shù)據(jù)放到List或Map中，容易出現(xiàn)OOM。
注：一般情況下，需要用分庫分表的場景，數(shù)據(jù)量必然很大，所以這個方法，實際中基本上沒法用。
三、二次查詢法
這也是"業(yè)界難題-跨庫分頁”一文中提到的一個方法，大致思路如下：在某1頁的數(shù)據(jù)均攤到各分表的前提下(注：這個前提很重要，也就是說不會有一個分表的數(shù)據(jù)特別多或特別少)，換句話說：這個方案不適用分段法，按如下步驟操作：
點擊關(guān)注公眾號，Java干貨及時送達(dá)
驗證一下看看效果：
原序列：(1，2，3，4，5，6，7，8)，需要取出limit 2,2 ，即：(3，4)
(1，3，5，7) -> limit 2,2 -> 改寫成 limit 1,2 -> (3，5)
(2，4，6，8) -> limit 2,2 -> 改寫成 limit 1,2 -> (4，6)
(1，3，5，7) -> between 3 and 5 -> (3，5)
(2，4，6，8) -> between 3 and 6 -> (4，6)
(3，4，5，6) ->取頭開始，取pageSize即2個元素 -> (3，4) 正確
-----------------------------------------------------
原序列：(1，2，3，4，5，6，7，8)，需要取出limit 1,2 ，即：(2，3)
(1，3，5，7) -> limit 1,2 -> 改寫成 limit 0,2 -> (1，3) --注：因為1/2除不盡，這里向下取整了
(2，4，6，8) -> limit 1,2 -> 改寫成 limit 0,2 -> (2，4)
(1，3，5，7) -> between 1 and 3 -> (1，3)
(2，4，6，8) -> between 1 and 4 -> (2，4)
(1，2，3，4) -> (2，3) (注：起始點，第1次查詢改寫時，向下取整了，所以這里要向移1位，從第2個數(shù)字開始取pagesize條數(shù)據(jù))
--------------------------------------------------------
原序列：(1，2，3，4，5，6，7，8)，需要取出limit 2,2 ，即：(3，4)
(1，2，3，4) -> limit 2,2 -> limit 1,2 -> {2，3} --注：這里就已經(jīng)把正確的數(shù)據(jù)給丟掉了
(5，6，7，8) -> limit 2,2 -> limit 1,2 -> {5，6} --注：這一段里根本就沒有這一頁的數(shù)據(jù)
(1，2，3，4) -> between 2 and 3 -> {2，3}
(5，6，7，8) -> between 2 and 6 -> {5，6}
(2，3，5，6) -> (2，3) 這個跟預(yù)期結(jié)果就對不上了。
-------------------------------------------------------
取余法的前提下，如果某個分表的數(shù)據(jù)，被清掉一部分，也就是某個分表數(shù)據(jù)偏少，會發(fā)生什么？
原序列：(1，3，5，6，7，8，9，11)，需要取出limit 2,2 ，即：(5，6)
(1，3，5，7，9，11) -> limit 2,2 -> 改寫成limit 1,2 -> (3，5)
(6，8) -> limit 2,2 -> 改寫成limit 1,2 -> (8)
(1，3，5，7，9，11) -> between 3 and 5 -> (3，5)
(6，8) -> between 3 and 8 -> (6，8)
(3，5，6，8) -> (3，5) 這跟預(yù)期結(jié)果也對不上。
四、禁止跳頁
相當(dāng)于只允許向上或向下翻頁，原理很簡單，比如：下一頁，先記錄上一頁的最大id，然后下一頁時，只需要 where id > 上一頁最大id limit pagesize， 每次只翻1頁。顯然，這是一個犧牲用戶體驗的做法。
結(jié)論：
分表分頁不存在一個通用的解決方案，要么性能有問題（比如:全局法 limit x+y），要么必須具備一定的前提條件(比如：二次查詢)，或者產(chǎn)品設(shè)計上犧牲用戶體驗，仍然是一個業(yè)內(nèi)難題。另外，關(guān)注公眾號Java技術(shù)棧，在后臺回復(fù)：面試，可以獲取我整理的 MySQL 系列面試題和答案，非常齊全。
參考文章：
https://juejin.im/post/5d1f52e46fb9a07eb3099bbf
https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/pagination/
https://stackoverflow.com/questions/3927537/how-do-you-implement-sorting-and-paging-on-distributed-data
http://kmiku7.github.io/2019/08/01/Do-Pagination-With-Table-Database-Sharding/
https://segmentfault.com/a/1190000013225860
https://mp.weixin.qq.com/s/h99sXP4mvVFsJw6Oh3aU5A
本文版權(quán)歸作者和博客園共有，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接，否則保留追究法律責(zé)任的權(quán)利。
關(guān)注Java技術(shù)?？锤喔韶?/strong>
