概述

隨著數(shù)據庫中數(shù)據量日益增多，不得進行分庫分表，在分庫后將數(shù)據分布到不同的數(shù)據庫實例（甚至物理機器）上，以達到降低數(shù)據量，提供系統(tǒng)的處理能力，但是這種架構也帶來其他問題，比如本文要講解的跨庫分頁查詢。

全局查詢法

test表有數(shù)據[1,2,3,4,5,6,7,8]，在單庫的時候，查詢第2頁數(shù)據并且顯示2條，語句是這樣的：

select * from test  order by id limit 2 offset 2

數(shù)據返回[3,4],但是數(shù)據切分以后，如果要查詢，這樣語句就可能就會有問題，例如：在節(jié)點1執(zhí)行此語句，返回【6,8】,   節(jié)點2返回【5,7】,然后進行排序取前二條返回了【5,6】，可以看到此結果與實際結果不一致，所以應該對sql語句改寫為:

select * from test  order by id limit 0 offset 4;

然后在根據各節(jié)點返回的數(shù)據，在進行排序，篩選出第2頁的2條。

缺點：

• 每個節(jié)點返回更多的數(shù)據，增大了網絡傳輸量
• 服務層還需要進行二次排序，增大了服務層的計算量
• 隨著頁碼的增大，性能會急劇下降

優(yōu)點：

• 查詢簡單，數(shù)據準確，不用做業(yè)務兼容，數(shù)據庫中間件都支持

禁止跳頁查詢法

在數(shù)據量很大，翻頁數(shù)很多的時候，很多產品并不提供“直接跳到指定頁面”的功能，而只提供“下一頁”的功能，這一個小小的業(yè)務折衷，就能極大的降低技術方案的復雜度。

假設db1中值為【2、4、6、8】，db2中值為【1、3、5、7】，根據id進行排序，返回對應的條數(shù)，在內存中對各個節(jié)點返回的數(shù)據進行排序，得到需要的數(shù)據，執(zhí)行以下語句，查詢第一頁數(shù)據，返回結果集為【1,2】：

select * from test where id>0 order by id  limit 2;

相比以前的方案，貌似跟以前處理流程一樣，但是在查詢第二頁時，要根據上一頁的id的最大值id_max（第一頁的最大id_max為2）,作為第二頁的最小值，那么會將如下語句：

select * from test  order by id limit 2,2;

改寫成：

select * from test order by id> 2 limit 2

這樣每個節(jié)點不用返回4頁數(shù)據了，只需要返回跟第一頁一樣頁數(shù)的數(shù)據，可以看到通過對業(yè)務的折中，性能得到大大的提升。

• 缺點：此種方案需要業(yè)務層進行處理，而且不能跳頁查詢，比如當前頁是第一頁，直接調到第五頁，因無法獲取到第四頁的最大ID，所以無法查詢第五頁的數(shù)據
• 優(yōu)點：不會隨著頁數(shù)的增大而影響查詢性能

允許數(shù)據精度損失查詢法

使用patition key進行分庫，在數(shù)據量較大，數(shù)據分布足夠隨機的情況下，各分庫所有非patition key屬性，在各個分庫上的數(shù)據分布，統(tǒng)計概率情況是一致的。

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

• 性別屬性，如果db0庫上的男性用戶占比70%，則db1上男性用戶占比也應為70%
• 年齡屬性，如果db0庫上18-28歲少女用戶比例占比15%，則db1上少女用戶比例也應為15%
• 時間屬性，如果db0庫上每天10:00之前登錄的用戶占比為20%，則db1上應該是相同的統(tǒng)計規(guī)律

精度損失查詢法示意圖

利用這一原理，如上圖要查詢全局第二頁數(shù)據，limit 2 offset 2 改寫為 limit 1 offset 1，每個分庫偏移 1（一半），獲取1條數(shù)據（半頁） ，得到的數(shù)據集的并集，那么結果為【3,4】基本能夠認為，是全局數(shù)據的limit 2 offset 2的數(shù)據，當然這里只是為了所以返回的準確數(shù)據，但是實際并不是精準的。

根據實際業(yè)務經驗，用戶都要查詢第100頁網頁、帖子、郵件的數(shù)據了，這一頁數(shù)據的精準性損失，業(yè)務上往往是可以接受的，但此時技術方案的復雜度便大大降低了，既不需要返回更多的數(shù)據，也不需要進行服務內存排序了。

終極大招-二次查詢法

以上介紹的方案或多或少都有一定缺點，那么有沒有一種方式能夠滿足業(yè)務需要，也能滿足性能要求的方法呢，有，那就是二次查詢法。

因此方案相比前三個方案理解起來相對復雜點，為了方便說明，所以先單一DB說起，以下單一DB中保存用戶年齡數(shù)據，1到30歲，總共30條，如果要查詢：

select * from T order by age limit 5 offset 10 

那么會返回以下粉色標識數(shù)據，即【11-15】，請記住此結果，下面會講解怎么分庫查詢以下結果。

單一DB數(shù)據集

把以上所有數(shù)據進行拆分打散存放到3個分庫中，如下：

分庫數(shù)據集

通過上文介紹，在單一DB中查詢limit 5 offset 10,返回了【11-15】結果，那如果在以上三個分庫全局查詢limit 5 offset 10怎么做？

第一步：語句改寫

將 select * from T order by age limit 5 offset 10 改寫為 select * from T order by age limit 5 offset 3 , 并投遞給所有的分庫，注意，這個 offset 的 3，來自于全局offset的總偏移量 10，除以水平切分數(shù)據庫個數(shù) 3。

執(zhí)行select * from T order by age limit 5 offset 3，結果如下（粉色標識數(shù)據），為了便于理解用青黃色標識庫表前三條數(shù)據：

執(zhí)行l(wèi)imit 5 offset 3數(shù)據集（青黃色表庫表前三條數(shù)據）

第二步：找到返回數(shù)據的最小值

• 第一個庫，5 條數(shù)據的 age 最小值是10；
• 第二個庫，5 條數(shù)據的 age 最小值是 6；
• 第三個庫，5 條數(shù)據的 age 最小值是 12;

標識結果集最小值

故，三頁數(shù)據中，age最小值來自第二個庫，age_min=6，這個過程只需要比較各個分庫第一條數(shù)據，時間復雜度很低

第三步：查詢二次改寫

第一次改寫的SQL語句是select * from T order by age  limit 5 offset 3 第二次要改寫成一個between語句，between的起點是age_min，between的終點是原來每個分庫各自返回數(shù)據的最大值：

• 第一個分庫，第一次返回數(shù)據的最大值是22 所以查詢改寫為select * from T order by age where age between age_min and 22
• 第二個分庫，第一次返回數(shù)據的最大值是20 所以查詢改寫為select * from T order by age where age between age_min and 20
• 第三個分庫，第一次返回數(shù)據的最大值是25 所以查詢改寫為select * from T order by age where age between age_min and 25

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

二次查詢結果，（深藍色表示）

分庫一的結果集，比第一次多返回了1條數(shù)據，上圖中深藍色記錄7

由于age_min來自原來的分庫二，所以分庫二的返回結果集和第一次查詢相同,其實這次查詢可以省掉

分庫三的結果集，比第一次多返回了3條數(shù)據，上圖中深藍色記錄8,9,10

第四步：找到age_min在全局的offset

在每個結果集中虛擬一個age_min記錄，找到age_min在全局的offset

標識全局offset

因為查詢語句為 limit 5 offset 3 ，所以查詢結果集中每個分庫的第一條數(shù)據offset為4；

• 分庫一中，根據第一次查詢條件得出的10的offset是4，查詢又返回了一條數(shù)據向前推進一位索引，故虛擬age_min在第一個庫的offset是2
• 分庫二沒有數(shù)據變化所以age_min的offset=4
• 分庫三中，根據第一次查詢條件得出的12的offset是4，查詢又返回了三條數(shù)據向前推進三位索引，故虛擬age_min在第三個庫的offset是0

因此age_min的全局offset為：2+4+0=6

第五步：查找最終數(shù)據

既然得到了age_min在全局的offset，就有了全局視野，根據第二次的結果集，就能夠得到全局limit 5 offset 10的記錄（下圖黃色標識數(shù)據【11-15】）

標識最終結果數(shù)據（黃色表示）

• 優(yōu)點：精確返回數(shù)據，不會隨著頁數(shù)變大而丟失數(shù)據
• 缺點：需要進行兩次數(shù)據庫查詢