MySQL 跨庫分頁/ 分表分頁/ 跨庫分頁，為什麼這麼難？

當業務資料達到一定量級(比如：mysql單表記錄量>1千萬)後，通常會考慮“分庫分表”将資料分散到不同的庫或表中，這樣可以大大提高讀/寫性能。但是問題來了，對于 select * from table limit offset , pagesize 這種分頁方式，原來一條語句就可以簡單搞定的事情會變得很複雜，本文将與大家一起探讨分庫分表後"分頁"面臨的新問題。

一、分表對分頁的影響

比如有一張表，裡面有8條記錄(為簡單起見，假設該表上隻有1個自增ID），數學上可以抽象成1個(有序)數列(注：為友善讨論，不加特殊說明的情況下，文本中數列的順序，均指升序)

(1，2，3，4，5，6，7，8)

如果要取出上面紅色辨別的2,3這二條記錄，limit 1,2 就行了。

現在假如分成2張表（即：原來的數列，拆分成2個非空子數列），一般來講，有二種常用分法：

1.1 分段法（比如：有時間屬性的資料，類似訂單這種，可以按下單時間拆分，每個月1張表）

(1，2，3，4)

(5，6，7，8)

沿用之前的limit x,y的思路，每個分表上 limit 1,2，會得到如下2個子數列：

(2，3)

(6，7)

然後在記憶體中合并排序，再取前2條 (2，3，6，7) => (2，3) ，貌似看上去也符合預期（這個思路也稱為歸并），但這隻是假象。當要取的分頁資料落在不同的子數列上時，就能發現問題：

(1，2，3，4，5，6，7，8) 比如，我們要從4個位置開始，連續取2個元素，即: limit 3,2

(1，2，3，4) => limit 3,2 =>(4)

(5，6，7，8) => limit 3,2 =>(8)

最後合并出來的結果是(4，8) 與正确結果 (4，5)相比，顯然不對。

1.2 模餘均攤法（比如：字段值對2取模求餘數，根據餘數決定分到哪個表，該方法也簡稱為取餘法）

(1，3，5，7)

(2，4，6，8)

歸并排序的思路在分段法上行不通，對于取模均攤同樣也不行，仍以 limit 1,2為例，原始序列取出來的結果是(2，3)，如果用歸并的思路：

(1，3，5，7)=> limit 1,2 =>(3 ,5)

(2，4，6，8)=> limit 1,2 =>(4, 6)

記憶體合并排序後，取前2個，最終結果為(3 , 4)

結論：不管分庫分表采用什麼分法，簡單歸并的思路，都無法正确解決分頁問題。

二、全局法(limit x+y)

反思一下剛才的歸并思路，本質上我們在每個子數列（即：分表）上limit x,y 時，取出來的資料就有可能已經産生缺失了。網上有一篇廣為流轉的文章"業界難題-跨庫分頁”，作者在文中提出了一個方案：把範圍擴大，分表sql上的limit x,y 變成 limit 0, x+y ，這樣改寫後，相當于分表中把"每頁最後一條資料"之前的所有資料全都取出來了（當然：這裡面可能會有不需要的多餘資料)，然後記憶體中合并在一起，再取x偏移量後的y條資料。

用前面的例子驗證一下：

原序列：(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)

将子數列合并排序=> { 1，2，3，5，6，7} => 按原始偏移量 limit 1,2 =>{2，3} 正确

如果原數列中要取的資料，正好落在2個子數列上(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)

将子數列合并排序=> (1，2，3，4，5，6，7，8) => 按原始偏移量 limit 3,2 => (4，5) 也符合預期。

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 遇到海量資料，mysql中查詢有可能直接逾時，這麼多資料從db傳到應用層，網絡開銷也很大，更不用說如果是java應用，大量資料放到List或Map中，容易出現OOM。(注：一般情況下，需要用分庫分表的場景，資料量必然很大，是以這個方法，實際中基本上沒法用)

另外，MySQL 系列面試題和答案全部整理好了，微信搜尋Java技術棧，在背景發送：面試，可以線上閱讀。

三、二次查詢法

這也是"業界難題-跨庫分頁”一文中提到的一個方法，大緻思路如下：在某1頁的資料均攤到各分表的前提下(注：這個前提很重要，也就是說不會有一個分表的資料特别多或特别少)，換句話說：這個方案不适用分段法，按如下步驟操作：

1）原sql中的limit offset,pagesize 改寫成 limit offset/n ,pagesize (注：n為分表個數，如果offset/n除不盡，向下取整，避免最後的結果丢資料）-- 這個的意思，其實就是假設原表這一頁的資料，會均分到各個分表(是以，我一再強調，前提是資料是均攤的，如果某個分表的記錄很少，極端情況下，甚至是空的，這個就不對了，最終結果會少資料)

2）分表上，執行改寫後的sql，得到一堆結果集，然後找出這堆結果中的最小id (假設id是關鍵的排序字段)，記為min_id -- 這一步的目的，是為了找出最小的起始點，保證第1頁資料起點正确。

3）各分表上的sql，where條件部分改寫成 id between min_id and origin_max_id (注：origin_max_id為上一步，每個分表查詢結果集中的最大值，顯然min_id=自身最小id的那張分表，不用再重複查詢) -- 這一步的目的在于，因為步驟1）查出來的結果，通常會比原表上該頁的資料少，是以這裡重新将起始點設定到正确的位置，即：min_id，再查1次，相當于範圍擴大了，以保證資料不會丢。不過，這裡有一個可優化的地方，仔細想想，這1次查詢出來的結果，跟步驟1）中的查出來的結果，必然有一部分是重複的，是以改寫部分，隻需要 id between min_id and origin_min_id就可以了(origin_min_id 即為原來分表結果上的最小id)

4）将上一步查詢出來的結果，在記憶體中合并排序去重（注：如果上一步采用了優化方案，就應該是把1）與3）這二次查詢的結果全取出來合并排序去重），然後從開始連續取pagesize條資料即可（注：offset/n除不盡的話，向下取整了，也就是起始點可能向前多移了，是以有可能開始的第1條記錄，其實是上1頁的最後1條記錄，要追求精确的話，可以在應用層記錄上一頁最後1條記錄的id，然後跟本次查詢結果前1條記錄對比，如果發現是一樣的，開始取資料的位置，就要向後移1位，如果考慮id有重複的話，就要根據情況多移幾位）

驗證一下看看效果：

場景1（前提：取餘法）

原序列：(1，2，3，4，5，6，7，8)，需要取出limit 2,2 ，即：(3，4)

第1次查詢

(1，3，5，7) -> limit 2,2 -> 改寫成 limit 1,2 -> (3，5)

(2，4，6，8) -> limit 2,2 -> 改寫成 limit 1,2 -> (4，6)

最小值為3

第2次查詢

(1，3，5，7) -> between 3 and 5 -> (3，5)

(2，4，6，8) -> between 3 and 6 -> (4，6)

将第2次查詢的結果合并：

(3，4，5，6) ->取頭開始，取pageSize即2個元素 -> (3，4) 正确

-----------------------------------------------------

場景2（前提：取餘法）

(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

(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個數字開始取pagesize條資料)

--------------------------------------------------------

場景3（前提：分段法）

為什麼說分段法，這個方案不适用，可以看下面的分析：

(1，2，3，4) -> limit 2,2 -> limit 1,2 -> {2，3} --注：這裡就已經把正确的資料給丢掉了

(5，6，7，8) -> limit 2,2 -> limit 1,2 -> {5，6} --注：這一段裡根本就沒有這一頁的資料

最小值2

(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) 這個跟預期結果就對不上了。

-------------------------------------------------------

場景4（前提：取餘法）

取餘法的前提下，如果某個分表的資料，被清掉一部分，也就是某個分表資料偏少，會發生什麼？

比如下面這個，按奇數、偶數分成2個子序列，但是偶數故意删除了幾個（相當于現實業務中，這個分表上的資料被幹掉了一部分）

原序列：(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) 這跟預期結果也對不上。

四、禁止跳頁

相當于隻允許向上或向下翻頁，原理很簡單，比如：下一頁，先記錄上一頁的最大id，然後下一頁時，隻需要 where id > 上一頁最大id limit pagesize， 每次隻翻1頁。顯然，這是一個犧牲使用者體驗的做法。

結論：分表分頁不存在一個通用的解決方案，要麼性能有問題（比如:全局法 limit x+y），要麼必須具備一定的前提條件(比如：二次查詢)，或者産品設計上犧牲使用者體驗，仍然是一個業内難題。

參考文章：

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

本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利。