概述

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

全局查詢法

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

select * from test  order by id limit 2 offset 2

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

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

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

缺點(diǎn)：

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

優(yōu)點(diǎn)：

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

禁止跳頁查詢法

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

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

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

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

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

改寫成：

select * from test order by id> 2 limit 2

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

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

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

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

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

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

精度損失查詢法示意圖

利用這一原理，如上圖要查詢?nèi)值诙摂?shù)據(jù)，limit 2 offset 2 改寫為 limit 1 offset 1，每個(gè)分庫偏移 1（一半），獲取1條數(shù)據(jù)（半頁） ，得到的數(shù)據(jù)集的并集，那么結(jié)果為【3,4】基本能夠認(rèn)為，是全局?jǐn)?shù)據(jù)的limit 2 offset 2的數(shù)據(jù)，當(dāng)然這里只是為了所以返回的準(zhǔn)確數(shù)據(jù)，但是實(shí)際并不是精準(zhǔn)的。

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

終極大招-二次查詢法

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

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

select * from T order by age limit 5 offset 10 

那么會(huì)返回以下粉色標(biāo)識(shí)數(shù)據(jù)，即【11-15】，請(qǐng)記住此結(jié)果，下面會(huì)講解怎么分庫查詢以下結(jié)果。

單一DB數(shù)據(jù)集

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

分庫數(shù)據(jù)集

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

第一步：語句改寫

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

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

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

第二步：找到返回?cái)?shù)據(jù)的最小值

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

標(biāo)識(shí)結(jié)果集最小值

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

第三步：查詢二次改寫

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

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

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

二次查詢結(jié)果，（深藍(lán)色表示）

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

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

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

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

在每個(gè)結(jié)果集中虛擬一個(gè)age_min記錄，找到age_min在全局的offset

標(biāo)識(shí)全局offset

因?yàn)椴樵冋Z句為 limit 5 offset 3 ，所以查詢結(jié)果集中每個(gè)分庫的第一條數(shù)據(jù)offset為4；

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

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

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

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

標(biāo)識(shí)最終結(jié)果數(shù)據(jù)（黃色表示）

• 優(yōu)點(diǎn)：精確返回?cái)?shù)據(jù)，不會(huì)隨著頁數(shù)變大而丟失數(shù)據(jù)
• 缺點(diǎn)：需要進(jìn)行兩次數(shù)據(jù)庫查詢