日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

公告:魔扣目錄網為廣大站長提供免費收錄網站服務,提交前請做好本站友鏈:【 網站目錄:http://www.ylptlb.cn 】, 免友鏈快審服務(50元/站),

點擊這里在線咨詢客服
新站提交
  • 網站:51998
  • 待審:31
  • 小程序:12
  • 文章:1030137
  • 會員:747

概述

隨著數據庫中數據量日益增多,不得進行分庫分表,在分庫后將數據分布到不同的數據庫實例(甚至物理機器)上,以達到降低數據量,提供系統的處理能力,但是這種架構也帶來其他問題,比如本文要講解的跨庫分頁查詢。

全局查詢法

分庫分表必會:跨庫分頁查詢看此一篇就夠了

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】)

分庫分表必會:跨庫分頁查詢看此一篇就夠了

標識最終結果數據(黃色表示)

  • 優點:精確返回數據,不會隨著頁數變大而丟失數據
  • 缺點:需要進行兩次數據庫查詢

分享到:
標簽:分庫分表
用戶無頭像

網友整理

注冊時間:

網站:5 個   小程序:0 個  文章:12 篇

  • 51998

    網站

  • 12

    小程序

  • 1030137

    文章

  • 747

    會員

趕快注冊賬號,推廣您的網站吧!
最新入駐小程序

數獨大挑戰2018-06-03

數獨一種數學游戲,玩家需要根據9

答題星2018-06-03

您可以通過答題星輕松地創建試卷

全階人生考試2018-06-03

各種考試題,題庫,初中,高中,大學四六

運動步數有氧達人2018-06-03

記錄運動步數,積累氧氣值。還可偷

每日養生app2018-06-03

每日養生,天天健康

體育訓練成績評定2018-06-03

通用課目體育訓練成績評定