隨著數(shù)據(jù)的日益增多，在架構(gòu)上不得不分庫分表，提高系統(tǒng)的讀寫速度，但是這種架構(gòu)帶來的問題也是很多，這篇文章就來講一講跨庫/表分頁查詢的解決方案。

架構(gòu)背景

筆者曾經(jīng)做過大型的電商系統(tǒng)中的訂單服務(wù)，在企業(yè)初期時(shí)業(yè)務(wù)量很少，單庫單表基本扛得住，但是隨著時(shí)間推移，數(shù)據(jù)量越來越多，訂單服務(wù)在讀寫的性能上逐漸變差，架構(gòu)組也嘗試過各種優(yōu)化方案，比如前面介紹過的：、各種方案。雖說提升一些性能，但是在每日百萬數(shù)據(jù)增長的情況下，也是杯水車薪。

最終經(jīng)過架構(gòu)組的討論，選擇了分庫分表；至于如何拆分，分片鍵如何選擇等等細(xì)節(jié)不是本文重點(diǎn)，不再贅述。

在分庫分表之前先來拆解一下業(yè)務(wù)需求：。

• C端用戶需要查詢自己所有的訂單。
• 后臺(tái)管理員、客服需要查詢訂單信息（根據(jù)訂單號(hào)、用戶信息.....查詢）。
• B端商家需要查詢自己店鋪的訂單信息。

針對(duì)以上三個(gè)需求，判斷下優(yōu)先級(jí)，當(dāng)然首先需要滿足C端用戶的業(yè)務(wù)場景，因此最終選用了uid作為了shardingKey。

當(dāng)然選擇uid作為shardingKey僅僅滿足了C端用戶的業(yè)務(wù)場景，對(duì)于后臺(tái)和C端用戶的業(yè)務(wù)場景如何做呢？很簡單，只需要將數(shù)據(jù)異構(gòu)一份存放在ES或者HBase中就可以實(shí)現(xiàn)，比較簡單，不再贅述。

假設(shè)將訂單表根據(jù)hash(uid%2+1)拆分成了兩張表，如下圖：

假設(shè)現(xiàn)在需要根據(jù)訂單的時(shí)間進(jìn)行排序分頁查詢（這里不討論shardingKey路由，直接全表掃描），在單表中的SQL如下：

select * from t_order order by time asc limit 5,5;

這條SQL非常容易理解，就是翻頁查詢第2頁數(shù)據(jù)，每頁查詢5條數(shù)據(jù)，其中offest=5

假設(shè)現(xiàn)在t_order_1和t_order_2中的數(shù)據(jù)如下：

以上20條數(shù)據(jù)從小到大的排序如下：

t_order_1中對(duì)應(yīng)的排序如下：

t_order_2中對(duì)應(yīng)的排序如下：

那么單表結(jié)構(gòu)下最終結(jié)果只需要查詢一次，結(jié)果如下：

分表的架構(gòu)下如何分頁查詢呢？下面介紹幾種方案：

1. 全局查詢法

在數(shù)據(jù)拆分之后，如果還是上述的語句，在兩個(gè)表中直接執(zhí)行，變成如下兩條SQL：

select * from t_order_1 order by time asc limit 5,5;

select * from t_order_2 order by time asc limit 5,5;

將獲取的數(shù)據(jù)然后在內(nèi)存中再次進(jìn)行排序，那么最終的結(jié)果如下：

可以看到上述的結(jié)果肯定是不對(duì)的。

所以正確的SQL改寫成如下：

select * from t_order_1 order by time asc limit 0,10;

select * from t_order_2 order by time asc limit 0,10;

也就是說，要在每個(gè)表中將前兩頁的數(shù)據(jù)全部查詢出來，然后在內(nèi)存中再次重新排序，最后從中取出第二頁的數(shù)據(jù)，這就是全局查詢法

該方案的缺點(diǎn)非常明顯：

隨著頁碼的增加，每個(gè)節(jié)點(diǎn)返回的數(shù)據(jù)會(huì)增多，性能非常低。

服務(wù)層需要進(jìn)行二次排序，增加了服務(wù)層的計(jì)算量，如果數(shù)據(jù)過大，對(duì)內(nèi)存和CPU的要求也非常高。

不過這種方案也有很多的優(yōu)化方法，比如Sharding-JDBC中就對(duì)此種方案做出了優(yōu)化，采用的是，有興趣的可以自行去了解一下。

2. 禁止跳頁查詢法

數(shù)據(jù)量很大時(shí)，可以禁止跳頁查詢，只提供下一頁的查詢方法，比如APP或者小程序中的下拉刷新，這是一種業(yè)務(wù)折中的方案，但是卻能極大的降低業(yè)務(wù)復(fù)雜度。

比如第一頁的排序數(shù)據(jù)如下：

那么查詢第二頁的時(shí)候可以將上一頁的最大值作為查詢條件，此時(shí)的兩個(gè)表中的SQL改寫如下：

select * from t_order_1 where time>1664088392 order by time asc limit 5;

select * from t_order_2 time>1664088392 order by time asc limit 5;

然后同樣是需要在內(nèi)存中再次進(jìn)行重新排序，最后取出前5條數(shù)據(jù)

但是這樣的好處就是不用返回前兩頁的全部數(shù)據(jù)了，只需要返回一頁數(shù)據(jù)，在頁數(shù)很大的情況下也是一樣，在性能上的提升非常大

此種方案的缺點(diǎn)也是非常明顯：不能跳頁查詢，只能一頁一頁的查詢，比如說從第一頁直接跳到第五頁，因?yàn)闊o法獲取到第四頁的最大值，所以這種跳頁查詢肯定是不行的。

3. 二次查詢法

以上兩種方案或多或少的都有一些缺點(diǎn)，下面介紹一下二次查詢法，這種方案既能滿足性能要求，也能滿足業(yè)務(wù)的要求，不過相對(duì)前面兩種方案理解起來比較困難。

還是上面的SQL：

select * from t_order order by time asc limit 5,5;

（1）SQL改寫

第一步需要對(duì)上述的SQL進(jìn)行改寫：

select * from t_order order by time asc limit 2,5;

注意：原先的SQL的offset=5，稱之為全局offset，這里由于是拆分成了兩張表，因此改寫后的offset=全局offset/2=5/2=2。

最終的落到每張表的SQL如下：

select * from t_order_1 order by time asc limit 2,5;

select * from t_order_2 order by time asc limit 2,5;

執(zhí)行后的結(jié)果如下：

下圖中紅色部分則為最終結(jié)果：

（2）返回?cái)?shù)據(jù)的最小值

t_order_1：5條數(shù)據(jù)中最小值為：

t_order_1：5條數(shù)據(jù)中最小值為：

那么兩張表中的最小值為，記為，來自t_order_2這張表，這個(gè)過程只需要比較各個(gè)分庫第一條數(shù)據(jù)，時(shí)間復(fù)雜度很低。

（3）查詢二次改寫

第二次的SQL改寫也是非常簡單，使用between語句，起點(diǎn)就是第2步返回的最小值time_min，終點(diǎn)就是每個(gè)表中在第一次查詢時(shí)的最大值。

t_order_1這張表，第一次查詢時(shí)的最大值為1664088581，則SQL改寫后：

select * from t_order_1 where time between $time_min and 1664088581 order by time asc;

t_order_2這張表，第一次查詢時(shí)的最大值為1664088481，則SQL改寫后：

select * from t_order_2 where time between $time_min and 1664088481 order by time asc;

此時(shí)查詢的結(jié)果如下（紅色部分）：

上述例子只是數(shù)據(jù)巧合導(dǎo)致第2步的結(jié)果和第3步的結(jié)果相同，實(shí)際情況下一般第3步的結(jié)果會(huì)比第2步的結(jié)果返回的數(shù)據(jù)會(huì)多。

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

在每個(gè)結(jié)果集中虛擬一個(gè)time_min記錄，找到time_min在全局的offset，下圖藍(lán)色部分為虛擬的time_min，紅色部分為第2步的查詢結(jié)果集。

因?yàn)榈?步改后的SQL的offset為2，所以查詢結(jié)果集中每個(gè)分表的第一條數(shù)據(jù)offset為3（2+1）；

t_order_1中的第一條數(shù)據(jù)為，這里的offset為3，則向上推移一個(gè)找到了虛擬的time_min，則offset=2。

t_order_2中的第一條數(shù)據(jù)就是time_min，則offset=3。

那么此時(shí)的time_min的全局offset=2+3=5。

（5） 查找最終數(shù)據(jù)

找到了time_min的最終全局offset=5之后，那么就可以知道排序的數(shù)據(jù)了。

將第2步獲取的兩個(gè)結(jié)果集在內(nèi)存中重新排序后，結(jié)果如下：

現(xiàn)在time_min也就是的offset=5，那么原先的SQL：select * from t_order order by time asc limit 5,5;的結(jié)果顯而易見了，向后推移一位，則結(jié)果為：

剛好符合之前的結(jié)果，說明二次查詢的方案沒問題

這種方案的優(yōu)點(diǎn)：可以精確的返回業(yè)務(wù)所需數(shù)據(jù)，每次返回的數(shù)據(jù)量都非常小，不會(huì)隨著翻頁增加數(shù)據(jù)的返回量。

缺點(diǎn)也是很明顯：需要進(jìn)行兩次查詢

總結(jié)

本篇文章中介紹了分庫分表后的分頁查詢的三種方案：

全局查詢法：這種方案最簡單，但是隨著頁碼的增加，性能越來越低。

禁止跳頁查詢法：這種方案是在業(yè)務(wù)上更改，不能跳頁查詢，由于只返回一頁數(shù)據(jù)，性能較高。

二次查詢法：數(shù)據(jù)精確，查詢的數(shù)據(jù)較少，不會(huì)隨著翻頁增加數(shù)據(jù)的返回量，性能較高。