概述
隨著數據庫中數據量日益增多,不得進行分庫分表,在分庫后將數據分布到不同的數據庫實例(甚至物理機器)上,以達到降低數據量,提供系統的處理能力,但是這種架構也帶來其他問題,比如本文要講解的跨庫分頁查詢。
全局查詢法
test表有數據[1,2,3,4,5,6,7,8],在單庫的時候,查詢第2頁數據并且顯示2條,語句是這樣的:
select * from test order by id limit 2 offset 2
數據返回[3,4],但是數據切分以后,如果要查詢,這樣語句就可能就會有問題,例如:在節點1執行此語句,返回【6,8】, 節點2返回【5,7】,然后進行排序取前二條返回了【5,6】,可以看到此結果與實際結果不一致,所以應該對sql語句改寫為:
select * from test order by id limit 0 offset 4;
然后在根據各節點返回的數據,在進行排序,篩選出第2頁的2條。
缺點:
- 每個節點返回更多的數據,增大了網絡傳輸量
- 服務層還需要進行二次排序,增大了服務層的計算量
- 隨著頁碼的增大,性能會急劇下降
優點:
- 查詢簡單,數據準確,不用做業務兼容,數據庫中間件都支持
禁止跳頁查詢法
在數據量很大,翻頁數很多的時候,很多產品并不提供“直接跳到指定頁面”的功能,而只提供“下一頁”的功能,這一個小小的業務折衷,就能極大的降低技術方案的復雜度。
假設db1中值為【2、4、6、8】,db2中值為【1、3、5、7】,根據id進行排序,返回對應的條數,在內存中對各個節點返回的數據進行排序,得到需要的數據,執行以下語句,查詢第一頁數據,返回結果集為【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
這樣每個節點不用返回4頁數據了,只需要返回跟第一頁一樣頁數的數據,可以看到通過對業務的折中,性能得到大大的提升。
- 缺點:此種方案需要業務層進行處理,而且不能跳頁查詢,比如當前頁是第一頁,直接調到第五頁,因無法獲取到第四頁的最大ID,所以無法查詢第五頁的數據
- 優點:不會隨著頁數的增大而影響查詢性能
允許數據精度損失查詢法
使用patition key進行分庫,在數據量較大,數據分布足夠隨機的情況下,各分庫所有非patition key屬性,在各個分庫上的數據分布,統計概率情況是一致的。
例如,在uid隨機的情況下,使用uid取模分兩庫,db0和db1:
- 性別屬性,如果db0庫上的男性用戶占比70%,則db1上男性用戶占比也應為70%
- 年齡屬性,如果db0庫上18-28歲少女用戶比例占比15%,則db1上少女用戶比例也應為15%
- 時間屬性,如果db0庫上每天10:00之前登錄的用戶占比為20%,則db1上應該是相同的統計規律
精度損失查詢法示意圖
利用這一原理,如上圖要查詢全局第二頁數據,limit 2 offset 2 改寫為 limit 1 offset 1,每個分庫偏移 1(一半),獲取1條數據(半頁) ,得到的數據集的并集,那么結果為【3,4】基本能夠認為,是全局數據的limit 2 offset 2的數據,當然這里只是為了所以返回的準確數據,但是實際并不是精準的。
根據實際業務經驗,用戶都要查詢第100頁網頁、帖子、郵件的數據了,這一頁數據的精準性損失,業務上往往是可以接受的,但此時技術方案的復雜度便大大降低了,既不需要返回更多的數據,也不需要進行服務內存排序了。
終極大招-二次查詢法
以上介紹的方案或多或少都有一定缺點,那么有沒有一種方式能夠滿足業務需要,也能滿足性能要求的方法呢,有,那就是二次查詢法。
因此方案相比前三個方案理解起來相對復雜點,為了方便說明,所以先單一DB說起,以下單一DB中保存用戶年齡數據,1到30歲,總共30條,如果要查詢:
select * from T order by age limit 5 offset 10
那么會返回以下粉色標識數據,即【11-15】,請記住此結果,下面會講解怎么分庫查詢以下結果。
單一DB數據集
把以上所有數據進行拆分打散存放到3個分庫中,如下:
分庫數據集
通過上文介紹,在單一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,除以水平切分數據庫個數 3。
執行select * from T order by age limit 5 offset 3,結果如下(粉色標識數據),為了便于理解用青黃色標識庫表前三條數據:
執行limit 5 offset 3數據集(青黃色表庫表前三條數據)
第二步:找到返回數據的最小值
- 第一個庫,5 條數據的 age 最小值是10;
- 第二個庫,5 條數據的 age 最小值是 6;
- 第三個庫,5 條數據的 age 最小值是 12;
標識結果集最小值
故,三頁數據中,age最小值來自第二個庫,age_min=6,這個過程只需要比較各個分庫第一條數據,時間復雜度很低
第三步:查詢二次改寫
第一次改寫的SQL語句是select * from T order by age limit 5 offset 3 第二次要改寫成一個between語句,between的起點是age_min,between的終點是原來每個分庫各自返回數據的最大值:
- 第一個分庫,第一次返回數據的最大值是22 所以查詢改寫為select * from T order by age where age between age_min and 22
- 第二個分庫,第一次返回數據的最大值是20 所以查詢改寫為select * from T order by age where age between age_min and 20
- 第三個分庫,第一次返回數據的最大值是25 所以查詢改寫為select * from T order by age where age between age_min and 25
相對第一次查詢,第二次查詢條件放寬了,故第二次查詢會返回比第一次查詢結果集更多的數據,假設這三個分庫返回的數據如下:
二次查詢結果,(深藍色表示)
分庫一的結果集,比第一次多返回了1條數據,上圖中深藍色記錄7
由于age_min來自原來的分庫二,所以分庫二的返回結果集和第一次查詢相同,其實這次查詢可以省掉
分庫三的結果集,比第一次多返回了3條數據,上圖中深藍色記錄8,9,10
第四步:找到age_min在全局的offset
在每個結果集中虛擬一個age_min記錄,找到age_min在全局的offset
標識全局offset
因為查詢語句為 limit 5 offset 3 ,所以查詢結果集中每個分庫的第一條數據offset為4;
- 分庫一中,根據第一次查詢條件得出的10的offset是4,查詢又返回了一條數據向前推進一位索引,故虛擬age_min在第一個庫的offset是2
- 分庫二沒有數據變化所以age_min的offset=4
- 分庫三中,根據第一次查詢條件得出的12的offset是4,查詢又返回了三條數據向前推進三位索引,故虛擬age_min在第三個庫的offset是0
因此age_min的全局offset為:2+4+0=6
第五步:查找最終數據
既然得到了age_min在全局的offset,就有了全局視野,根據第二次的結果集,就能夠得到全局limit 5 offset 10的記錄(下圖黃色標識數據【11-15】)
標識最終結果數據(黃色表示)
- 優點:精確返回數據,不會隨著頁數變大而丟失數據
- 缺點:需要進行兩次數據庫查詢