百億級資料 分庫分表 後怎麼分頁查詢？

隨著資料的日益增多，在架構上不得不分庫分表，提高系統的讀寫速度，但是這種架構帶來的問題也是很多，這篇文章就來講一講跨庫/表分頁查詢的解決方案。

架構背景

筆者曾經做過大型的電商系統中的訂單服務，在企業初期時業務量很少，單庫單表基本扛得住，但是隨著時間推移，資料量越來越多，訂單服務在讀寫的效能上逐漸變差，架構組也嘗試過各種最佳化方案，比如前面介紹過的：冷熱分離、查詢分離各種方案。雖說提升一些效能，但是在每日百萬資料增長的情況下，也是杯水車薪。

關於冷熱分離和查詢分離不了解的，可以看筆者前面的文章：

• 冷熱分離
• 使用 查詢分離 後 從20s最佳化到500ms

最終經過架構組的討論，選擇了分庫分表；至於如何拆分，分片鍵如何選擇等等細節不是本文重點，不再贅述。

在分庫分表之前先來拆解一下業務需求：

1. C端使用者需要查詢自己所有的訂單
2. 後臺管理員、客服需要查詢訂單資訊（根據訂單號、使用者資訊.....查詢）
3. B端商家需要查詢自己店鋪的訂單資訊

針對以上三個需求，判斷下優先順序，當然首先需要滿足C端使用者的業務場景，因此最終選用了uid作為了shardingKey

當然選擇uid作為shardingKey僅僅滿足了C端使用者的業務場景，對於後臺和C端使用者的業務場景如何做呢？很簡單，只需要將資料異構一份存放在ES或者HBase中就可以實現，比較簡單，不再贅述。

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

假設現在需要根據訂單的時間進行排序分頁查詢（這裡不討論shardingKey路由，直接全表掃描），在單表中的SQL如下：

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

這條SQL非常容易理解，就是翻頁查詢第2頁資料，每頁查詢5條資料，其中offest=5

假設現在t_order_1和t_order_2中的資料如下：

以上20條資料從小到大的排序如下：

t_order_1中對應的排序如下：

t_order_2中對應的排序如下：

那麼單表結構下最終結果只需要查詢一次，結果如下：

分表的架構下如何分頁查詢呢？下面介紹幾種方案

1. 全域性查詢法

在資料拆分之後，如果還是上述的語句，在兩個表中直接執行，變成如下兩條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;

將獲取的資料然後在記憶體中再次進行排序，那麼最終的結果如下：

可以看到上述的結果肯定是不對的。

所以正確的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;

也就是說，要在每個表中將前兩頁的資料全部查詢出來，然後在記憶體中再次重新排序，最後從中取出第二頁的資料，這就是全域性查詢法

該方案的缺點非常明顯：

• 隨著頁碼的增加，每個節點返回的資料會增多，效能非常低
• 服務層需要進行二次排序，增加了服務層的計算量，如果資料過大，對記憶體和CPU的要求也非常高

不過這種方案也有很多的最佳化方法，比如Sharding-JDBC中就對此種方案做出了最佳化，採用的是流式處理 + 歸併排序的方式來避免記憶體的過量佔用，有興趣的可以自行去了解一下。

2. 禁止跳頁查詢法

資料量很大時，可以禁止跳頁查詢，只提供下一頁的查詢方法，比如APP或者小程式中的下拉翻頁，這是一種業務折中的方案，但是卻能極大的降低業務複雜度

比如第一頁的排序資料如下：

那麼查詢第二頁的時候可以將上一頁的最大值1664088392作為查詢條件，此時的兩個表中的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;

然後同樣是需要在記憶體中再次進行重新排序，最後取出前5條資料

但是這樣的好處就是不用返回前兩頁的全部資料了，只需要返回一頁資料，在頁數很大的情況下也是一樣，在效能上的提升非常大

此種方案的缺點也是非常明顯：不能跳頁查詢，只能一頁一頁的查詢，比如說從第一頁直接跳到第五頁，因為無法獲取到第四頁的最大值，所以這種跳頁查詢肯定是不行的。

3. 二次查詢法

以上兩種方案或多或少的都有一些缺點，下面介紹一下二次查詢法，這種方案既能滿足效能要求，也能滿足業務的要求，不過相對前面兩種方案理解起來比較困難。

還是上面的SQL：

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

1. SQL改寫

第一步需要對上述的SQL進行改寫：

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;

執行後的結果如下：

下圖中紅色部分則為最終結果：

2. 返回資料的最小值

t_order_1：5條資料中最小值為：1664088479

t_order_2：5條資料中最小值為：1664088392

那麼兩張表中的最小值為1664088392，記為time_min，來自t_order_2這張表，這個過程只需要比較各個分庫第一條資料，時間複雜度很低

3. 查詢二次改寫

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

t_order_1這張表，第一次查詢時的最大值為1664088581，則SQL改寫後：

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

t_order_2這張表，第一次查詢時的最大值為1664088481，則SQL改寫後：

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

此時查詢的結果如下（紅色部分）：

上述例子只是資料巧合導致第2步的結果和第3步的結果相同，實際情況下一般第3步的結果會比第2步的結果返回的資料會多。

4. 在每個結果集中虛擬一個time_min記錄，找到time_min在全域性的offset

在每個結果集中虛擬一個time_min記錄，找到time_min在全域性的offset，下圖藍色部分為虛擬的time_min，紅色部分為第2步的查詢結果集

因為第1步改後的SQL的offset為2，所以查詢結果集中每個分表的第一條資料offset為3（2+1）；

t_order_1中的第一條資料為1664088479，這裡的offset為3，則向上推移一個找到了虛擬的time_min，則offset=2

t_order_2中的第一條資料就是time_min，則offset=3

那麼此時的time_min的全域性offset=2+3=5

5. 查詢最終資料

找到了time_min的最終全域性offset=5之後，那麼就可以知道排序的資料了。

將第2步獲取的兩個結果集在記憶體中重新排序後，結果如下：

現在time_min也就是1664088392的offset=5，那麼原先的SQL：select * from t_order order by time asc limit 5,5;的結果顯而易見了，向後推移一位，則結果為：

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

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

缺點也是很明顯：需要進行兩次查詢

總結

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

1. 全域性查詢法：這種方案最簡單，但是隨著頁碼的增加，效能越來越低
2. 禁止跳頁查詢法：這種方案是在業務上更改，不能跳頁查詢，由於只返回一頁資料，效能較高
3. 二次查詢法：資料精確，在資料分佈均衡的情況下適用，查詢的資料較少，不會隨著翻頁增加資料的返回量，效能較高