炸！業界難題，跨庫分頁的幾種常見方案

為什麼需要研究跨庫分頁？

網際網路很多業務都有分頁拉取資料的需求，例如：

（1）微信訊息過多時，拉取第N頁訊息；

（2）京東下單過多時，拉取第N頁訂單；

（3）瀏覽58同城，檢視第N頁帖子；

這些業務場景對應的訊息表，訂單表，帖子表分頁拉取需求，都有這樣一些共同的特點：

（1）有個業務主鍵id,  msg_id, order_id, tiezi_id；

（2）分頁按照非業務主鍵id來排序，業務中經常按照時間time來排序order by；

在資料量不大時，如何來實現跨庫分頁的需求呢？

（1）在排序欄位time上建立索引；

（2）利用SQL提供的offset/limit就能實現；

例如：

select * from t_msg order by time offset 200 limit 100;

select * from t_order order by time offset 200 limit 100; 

select * from t_tiezi order by time offset 200 limit 100;

畫外音：此處假設一頁資料為100條，均拉取第3頁資料。

為什麼會有分庫的需求？

高併發大流量的網際網路架構，一般透過服務層來訪問資料庫，隨著資料量的增大，資料庫需要進行水平切分，分庫後將資料分佈到不同的資料庫例項（甚至物理機器）上，以達到降低資料量，增加例項數的擴容目的。

一旦涉及分庫，逃不開“分庫依據” patition key，要使用哪一個欄位來水平切分資料庫呢？

大部分的業務場景，會使用業務主鍵id。

確定了分庫依據 patition key 後，接下來怎麼確定分庫演算法呢？

大部分的業務場景，會使用業務主鍵id取模的演算法來分庫，這樣的好處是：

（1）即能夠保證每個庫的資料分佈是均勻的；

（2）又能夠保證每個庫的請求分佈是均勻的；

實在是簡單實現負載均衡的好方法，此法在網際網路架構中應用頗多。

一個更具體的例子：

使用者庫user，水平切分後變為兩個庫：

（1）分庫依據patition key是uid；

（2）分庫演算法是uid取模：uid%2餘0的資料會落到db0，uid%2餘1的資料會落到db1；

資料庫進行了水平切分之後，如果業務要查詢“最近註冊的第3頁使用者”，即跨庫分頁查詢，該如何實現呢？

單庫上，可以

select * from t_user order by time offset 200 limit 100;

變成兩個庫後，分庫依據是uid，排序依據是time，資料庫層失去了time排序的全域性視野，資料分佈在兩個庫上，此時該怎麼辦呢？

如何滿足“跨越多個水平切分資料庫，且分庫依據與排序依據為不同屬性，並需要進行分頁”的查詢需求，實現：

select * from T order by time offset X limit Y;

這類跨庫分頁SQL，是後文將要討論的技術問題。

方案一：全域性視野法

如上圖所述，服務層透過uid取模將資料分佈到兩個庫上去之後，每個資料庫都失去了全域性視野，資料按照time區域性排序之後，不管哪個分庫的第3頁資料，都不一定是全域性排序的第3頁資料。

那到底哪些資料才是全域性排序的第3頁資料呢？

需要分三種情況討論。

（1）極端情況，兩個庫的資料完全一樣

如果兩個庫的資料完全相同，只需要每個庫offset一半，再取半頁，就是最終想要的資料（如上圖中粉色部分資料）。

（2）極端情況，結果資料來自一個庫

也可能兩個庫的資料分佈及其不均衡，例如db0的所有資料的time都大於db1的所有資料的time，則可能出現：一個庫的第3頁資料，就是全域性排序後的第3頁資料（如上圖中粉色部分資料）。

（3）一般情況，每個庫資料各包含一部分

正常情況下，全域性排序的第3頁資料，每個庫都會包含一部分（如上圖中粉色部分資料）。

由於不清楚到底是哪種情況，所以必須：

（1）每個庫都返回3頁資料；

（2）所得到的6頁資料在服務層進行記憶體排序，得到資料全域性視野；

（3）再取第3頁資料，便能夠得到想要的全域性分頁資料。

再總結一下這個方案的步驟：

（1）將SQL語句改寫，即

order by time offset X limit Y;

改寫成

order by time offset 0 limit X+Y;

（2）服務層將改寫後的SQL語句發往各個分庫；

（3）假設共分為N個庫，服務層將得到N*(X+Y)條資料；

（4）服務層對得到的N*(X+Y)條資料進行記憶體排序；

（5）記憶體排序後再取偏移量X後的Y條記錄，就是全域性視野所需的一頁資料；

全域性視野法有什麼優點？

透過服務層修改SQL語句，擴大資料召回量，能夠得到全域性視野，業務無損，精準返回所需資料。

全域性視野法的缺點呢？

缺點顯而易見：

（1）每個分庫需要返回更多的資料，增大了網路傳輸量（耗網路）；

（2）除了資料庫按照time進行排序，服務層還需要進行二次排序，增大了服務層的計算量（耗CPU）；

（3）最致命的，這個演算法隨著頁碼的增大，效能會急劇下降，這是因為SQL改寫後每個分庫要返回X+Y行資料：返回第3頁，offset中的X=200；假如要返回第100頁，offset中的X=9900，即每個分庫要返回100頁資料，資料量和排序量都將大增，效能平方級下降。

“全域性視野法”雖然效能較差，但其業務無損，資料精準，不失為一種方案，有沒有效能更優的方案呢？

“任何脫離業務的架構設計都是耍流氓”，技術方案需要折衷，在技術難度較大的情況下，業務需求的折衷能夠極大的簡化技術方案。

方案二：禁止跳頁查詢法

在資料量很大，翻頁數很多的時候，很多產品並不提供“直接跳到指定頁面”的功能，而只提供“下一頁”的功能，這一個小小的業務折衷，就能極大的降低技術方案的複雜度。

如上圖，不能跳頁，那麼第一次只能夠查第一頁：

（1）將查詢

order by time offset 0 limit 100;

改寫成

order by time where time>0 limit 100;

（2）上述改寫和offset 0 limit 100的效果相同，都是每個分庫返回了一頁資料（上圖中粉色部分）；

（3）服務層得到2頁資料，記憶體排序，取出前100條資料，作為最終的第一頁資料，這個全域性的第一頁資料，一般來說每個分庫都包含一部分資料（如上圖粉色部分）；

 

這個方案也需要伺服器記憶體排序，豈不是和“全域性視野法”一樣麼？第一頁資料的拉取確實一樣，但每一次“下一頁”拉取的方案就不一樣了。

 

點選“下一頁”時，需要拉取第二頁資料，在第一頁資料的基礎之上，能夠找到第一頁資料time的最大值：

這個上一頁記錄的time_max，會作為第二頁資料拉取的查詢條件：

（1）將查詢

order by time offset 100 limit 100;

改寫成

order by time where time>$time_max limit 100;

（2）這下不是返回2頁資料了（“全域性視野法，會改寫成offset 0 limit 200”），每個分庫還是返回一頁資料（如上圖中粉色部分）；

（3）服務層得到2頁資料，記憶體排序，取出前100條資料，作為最終的第2頁資料，這個全域性的第2頁資料，一般來說也是每個分庫都包含一部分資料（如上圖粉色部分）；

 

如此往復，查詢全域性視野第100頁資料時，不是將查詢條件改寫為

offset 0 limit 9900+100;（返回100頁資料）

而是改寫為

time>$time_max99 limit 100;（仍返回一頁資料）

以保證資料的傳輸量和排序的資料量不會隨著不斷翻頁而導致效能下降。

方案三：允許資料精度損失法

“全域性視野法”能夠返回業務無損的精確資料，在查詢頁數較大，例如第100頁時，會有效能問題，此時業務上是否能夠接受，返回的100頁不是精準的資料，而允許有一些資料偏差呢？

先來了解一下，資料庫分庫-資料均衡原理。

什麼是，資料庫分庫-資料均衡原理？

使用patition key進行分庫，在資料量較大，資料分佈足夠隨機的情況下，各分庫所有非patition key屬性，在各個分庫上，資料分佈的統計機率情況是一致的。

例如，在uid隨機的情況下，使用uid取模分兩庫，db0和db1：

（1）性別屬性，如果db0庫上的男性使用者佔比70%，則db1上男性使用者佔比也應為70%；

（2）年齡屬性，如果db0庫上18-28歲少女使用者比例佔比15%，則db1上少女使用者比例也應為15%；

（3）時間屬性，如果db0庫上每天10:00之前登入的使用者佔比為20%，則db1上應該是相同的統計規律；

…

利用這一原理，要查詢全域性100頁資料，只要將：

offset 9900 limit 100;

改寫為

offset 4950 limit 50;

即每個分庫偏移一半（4950），獲取半頁資料（50條），得到的資料集的並集，基本能夠認為，是全域性資料的offset 9900 limit 100的資料，當然，這一頁資料並不是精準的。

根據實際業務經驗，使用者都要查詢第100頁網頁、帖子、郵件的資料了，這一頁資料的精準性損失，業務上往往是可以接受的，但此時技術方案的複雜度大大降低了，既不需要返回更多的資料，也不需要進行服務記憶體排序了。

畫外音：如果業務能夠接受，這種方案的效能最好，強烈推薦。

 

方案四：二次查詢法

有沒有一種技術方案，即能夠滿足業務的精確需要，無需業務折衷，又高效能的方法呢？這就是接下來要介紹的終極武器，“二次查詢法”。

為了方便舉例，假設一頁只有5條資料，查詢第200頁的SQL語句為：

select * from T order by time offset 1000 limit 5;

步驟一：查詢改寫

select * from T order by time offset 1000 limit 5;

改寫為

select * from T order by time offset 500 limit 5;

並投遞給所有的分庫，注意，這個offset的500，來自於全域性offset的總偏移量1000，除以水平切分資料庫個數2。

畫外音：因為資料量比較大，資料隨機性較強，不妨設仍然符合“資料庫分庫-資料均衡定理”。

如果是3個分庫，則可以改寫為

select * from T order by time offset 333 limit 5;

假設這三個分庫返回的資料(time, uid)如下：

可以看到，每個分庫都是返回的按照time排序的一頁資料。

步驟二：找到所返回3頁全部資料的最小值

第一個庫，5條資料的time最小值是1487501123；

第二個庫，5條資料的time最小值是1487501133；

第三個庫，5條資料的time最小值是1487501143；

故，三頁資料中，time最小值來自第一個庫，time_min=1487501123，這個過程只需要比較各個分庫第一條資料，時間複雜度很低。

畫外音：這個time_min非常重要，後文每一個步驟要都要用到time_min。

步驟三：查詢二次改寫

第一次改寫的SQL語句是

select * from T order by time offset 333 limit 5;

第二次要改寫成一個between語句：

• between的起點是time_min

• between的終點是原來每個分庫各自返回資料的最大值

第一個分庫，第一次返回資料的最大值是1487501523

所以查詢改寫為：

select * from T order by time where time between time_min and 1487501523;

第二個分庫，第一次返回資料的最大值是1487501323

所以查詢改寫為

select * from T order by time where time between time_min and 1487501323;

第三個分庫，第一次返回資料的最大值是1487501553

所以查詢改寫為

select * from T order by time where time between time_min and 1487501553;

相對第一次查詢，第二次查詢條件放寬了，故第二次查詢會返回比第一次查詢結果集更多的資料，假設這三個分庫返回的資料(time, uid)如下：

可以看到：

分庫一的結果集，由於time_min來自原來的分庫一，所以分庫一的返回結果集和第一次查詢相同（所以其實這次訪問是可以省略的）；

分庫二的結果集，比第一次多返回了1條資料，頭部的1條記錄（time最小的記錄）是新的（上圖中粉色記錄）；

分庫三的結果集，比第一次多返回了2條資料，頭部的2條記錄（time最小的2條記錄）是新的（上圖中粉色記錄）；

步驟四：在每個結果集中虛擬一個time_min記錄，找到time_min在全域性的offset

在第一個庫中，time_min在第一個庫的offset是333；

在第二個庫中，(1487501133, uid_aa)的offset是333（根據第一次查詢條件得出的），故虛擬time_min在第二個庫的offset是331；

畫外音：從333往前推演。

在第三個庫中，(1487501143, uid_aaa)的offset是333（根據第一次查詢條件得出的），故虛擬time_min在第三個庫的offset是330；

畫外音：從333往前推演。

綜上，time_min在全域性的offset是333+331+330=994。

步驟五：既然得到了time_min在全域性的offset，就相當於有了全域性視野，根據第二次的結果集，就能夠得到全域性offset 1000 limit 5的記錄

第二次查詢在各個分庫返回的結果集是有序的，又知道了time_min在全域性的offset是994，一路排下來，容易知道全域性offset 1000 limit 5的一頁記錄（上圖中黃色記錄）。

這種方法的優點是：可以精確的返回業務所需資料，每次返回的資料量都非常小，不會隨著翻頁增加資料的返回量。

帥氣不帥氣！！！

總結

今天介紹瞭解決“跨N庫分頁”這一難題的四種方法：

方法一：全域性視野法

（1）SQL改寫，將

order by time offset X limit Y;

改寫成

order by time offset 0 limit X+Y;

（2）服務層對得到的N*(X+Y)條資料進行記憶體排序，記憶體排序後再取偏移量X後的Y條記錄；

這種方法隨著翻頁的進行，效能越來越低。

方法二：禁止跳頁查詢法

（1）用正常的方法取得第一頁資料，並得到第一頁記錄的time_max；

（2）每次翻頁，將

order by time offset X limit Y;

改寫成

order by time where time>$time_max limit Y;

以保證每次只返回一頁資料，效能為常量。

方法三：允許模糊資料法

（1）SQL查詢改寫，將

order by time offset X limit Y;

改寫成

order by time offset X/N limit Y/N;

效能很高，但拼接的結果集不精準。

方法四：二次查詢法

（1）SQL改寫，將

order by time offset X limit Y;

改寫成

order by time offset X/N limit Y;

（2）多頁返回，找到最小值time_min；

（3）between二次查詢

order by time between $time_min and $time_i_max;

（4）設定虛擬time_min，找到time_min在各個分庫的offset，從而得到time_min在全域性的offset；

（5）得到了time_min在全域性的offset，自然得到了全域性的offset X limit Y；

文章比較長，希望大家有收穫。

思路比結論更重要。