概述
随着資料庫中資料量日益增多,不得進行分庫分表,在分庫後将資料分布到不同的資料庫執行個體(甚至實體機器)上,以達到降低資料量,提高系統的處理能力,但是這種架構也帶來其他問題,比如本文要講解的跨庫分頁查詢
全局查詢法
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:
(1)性别屬性,如果db0庫上的男性使用者占比70%,則db1上男性使用者占比也應為70% (2)年齡屬性,如果db0庫上18-28歲少女使用者比例占比15%,則db1上少女使用者比例也應為15% (3)時間屬性,如果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】)
辨別最終結果資料(黃色表示)
優點
精确傳回資料,不會随着頁數變大而丢失資料
缺點
需要進行兩次資料庫查詢