優(yōu)質(zhì)博文：IT-BLOG-CN

背景：我們系統(tǒng)上云后，數(shù)據(jù)根據(jù)用戶UDL部分?jǐn)?shù)據(jù)在國內(nèi)，部分?jǐn)?shù)據(jù)存儲在海外，因此需要考慮分庫查詢的分頁排序問題

一、分庫后帶來的問題

需求根據(jù)訂單創(chuàng)單時間進(jìn)行排序分頁查詢，在單表中的查詢SQL如下(省略部分查詢內(nèi)容)：每頁獲取10條記錄

select orderId, orderStatus from t_order order by create_time asc limit 20, 10；

我們做了分庫之后，如果需要完成上述的需求，需要在兩個表中直接執(zhí)行如下兩條SQL：offset都需要從0開始，否則數(shù)據(jù)就不正確了。我這里為了區(qū)分，表的名字后面帶上對應(yīng)的環(huán)境，實際生產(chǎn)sql是一樣的，只是查詢的庫不同而已。

select * from t_order_sha order by create_time asc limit 0,30;select * from t_order_fra order by create_time asc limit 0,30;

如上所示：我們需要將前3頁的數(shù)據(jù)全部查出來，然后在內(nèi)存中重新排序，最后從中取出第3頁的數(shù)據(jù)，也稱為“全局查詢法”。

該方案存在的問題： 隨著頁碼的增加，每個節(jié)點返回的數(shù)據(jù)會增多，性能也隨著下降。同時，服務(wù)層需要進(jìn)行二次排序，增加了服務(wù)層的計算量，如果數(shù)據(jù)過大，對內(nèi)存和cpu的要求也非常高。

不過這種方案也有很多優(yōu)化方案，Sharding-JDBC中就對此方案做出優(yōu)化，采用的是流式處理和歸并排序避免內(nèi)存的過量占用。

二、禁止跳頁查詢法

我們部分系統(tǒng)（航班列表頁）是通過點擊“更多”按鈕展示下一頁的數(shù)據(jù)（只提供了查詢下一頁的功能），此時頁面上展示的是前n頁的數(shù)據(jù)集。

上述的功能在分庫分頁查詢的情況下，可以極大的降低業(yè)務(wù)的復(fù)雜度，因為當(dāng)查詢第二頁數(shù)據(jù)的時候，可以將上一頁的最大值最為查詢條件，此時的SQL可以改寫為：

select * from t_order_sha where create_time>1726671336 order by time asc limit 10;select * from t_order_fra where create_time>1726671336 order by time asc limit 10;

查詢到數(shù)據(jù)后需要在內(nèi)存中進(jìn)行重新排序，但相對于“全局查詢”數(shù)據(jù)量已經(jīng)減少了很多，頁碼越大性能提升越明顯。此方案的缺點也非常明顯：不能跳頁查詢，只能一頁一頁查詢。

三、二分查找法

二分查找法既能滿足性能要求，也能滿足業(yè)務(wù)要求，不過相對前面兩種方案理解起來比較困難。

我們還是以上述的查詢語句為例（這里為了演示方便，修改為查詢第二頁，每頁返回5條數(shù)據(jù)）：

select orderId, orderStatus from t_order order by create_time asc limit 5, 5;

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

核心思想：第一頁的5數(shù)據(jù)肯定包含在t_order_sha或t_order_fra表中的二分后的0-2之中

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

select * from t_order_sha order by create_time asc limit 2,5;select * from t_order_fra order by create_time asc limit 2,5;

紅色部分表示查詢結(jié)果

t_order_sha	t_order_fra
00000000001	00000000002
00000000003	00000000008
00000000004	00000000009
00000000005	00000000010
00000000006	00000000011
00000000007	00000000012
00000000013	00000000014
…	…

【2】返回查詢數(shù)據(jù)中的最小值： t_order_sha = 00000000003 (這個過程只需要比較各個分庫的第一條數(shù)據(jù)，時間復(fù)雜度很低)

【3】查詢二次改寫： 第二次查詢使用beteween語句，起點是第二部返回的最小值，終點是每個表第一次查詢后的最大值。

t_order_sha 這張表，第一次查詢的最大值00000000013，則SQL改寫后：

select * from t_order_1 where time between 00000000004 and 00000000013 order by time asc;

t_order_fra 這張表，第一次查詢的最大值00000000014，則SQL改寫后：

select * from t_order_1 where time between 00000000004 and 00000000014 order by time asc;

紅色部分為第次的查詢結(jié)果

t_order_sha	t_order_fra
00000000001	00000000002
00000000003	00000000008
00000000004	00000000009
00000000005	00000000010
00000000006	00000000011
00000000007	00000000012
00000000013	00000000014
…	…

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

下圖藍(lán)色部分為虛擬的time_min，紅色部分為第2步的查詢結(jié)果集。

t_order_sha	t_order_fra
00000000001	00000000002
00000000003	00000000004
00000000004	00000000008
00000000005	00000000009
00000000006	00000000010
00000000007	00000000011
00000000013	00000000012
	00000000014
…	…

t_order_sha中的第一條數(shù)據(jù)就是time_min，則offset=3。
t_order_fra中的第一條數(shù)據(jù)為00000000008，這里的offset為2，則向上推移一個找到了虛擬的time_min，則offset=1。

那么此時的time_min的全局offset=1+3=4。

【5】查找最終結(jié)果： 找到了time_min的最終全局offset=4之后，再根據(jù)第2步獲取的兩個結(jié)果集在內(nèi)存中重新排序。

[00000000004,00000000005,00000000006,00000000007,00000000008,00000000009,00000000010,00000000011,00000000012,00000000013,000000000104]

現(xiàn)在time_min也就是00000000004的offset=4，那么原先的SQL：select * from t_order order by time asc limit 5,5;，此時可以發(fā)現(xiàn)SQL中的總偏移量和最小值的偏移量的差值5-4=1，因此需要對排序后的結(jié)果集向后推移一位取值。同時因為最小值也包含在集合中的，無論前面的差值是多少，這里都需要將最小值踢出去，所以也需要再向后移一位。根據(jù)SQL取5條數(shù)據(jù)，就能夠得到如下結(jié)果：

00000000006,00000000007,00000000008,00000000009,00000000010

這種方案的優(yōu)點：可以精確的返回業(yè)務(wù)所需數(shù)據(jù)，每次返回的數(shù)據(jù)量都非常小，不會隨著翻頁增加數(shù)據(jù)的返回量。
缺點也是很明顯：需要進(jìn)行兩次查詢。