天天看點

Mysql中的分庫分表

mysql中的分庫分表

分庫:減少并發問題

分表:降低了分布式事務

分表

1、垂直分表

把其中的不常用的基礎資訊提取出來,放到一個表中通過id進行關聯。降低表的大小來控制性能,但是這種方式沒有解決高資料量帶來的性能損耗。

優點

1、拆分後業務清楚,達到專庫專用。

2、可以實作熱資料和冷資料的分離,将不經常變化的資料和變動較大的資料分散到不同的

庫/表裡面。

3、便于維護。

缺點

1、不能解決資料量大帶來的性能損耗,讀寫的壓力依舊很大。

2、不同的業務不能誇庫關聯,隻能通過業務關聯。

2、水準分表

以某個字段按照一定的規則将一個表資料分到多個表中

優點

1、單表的資料量減少了,提高性能。

2、提高了系統的穩定性和負載能力。

3、切分出來的表結構相同,程式改動的少。

缺點

1、拆分規則較難抽象。

2、資料分片在擴容是需要遷移

3、維護量增大

4、依然存在跨庫無法join等問題,同時涉及分布式事物,資料一緻性問題。

分庫政策

hash取模 通過一個表中的字段進行散列

range 範圍區分 (比如:時間,地區....)

list 預定義 (步長區分

分庫分表需要解決的問題

1、事物問題

解決事物問題有兩種可行的規方案分布式事物和通過應用程式與資料庫共同控制實作

1、使用分布式事物

優點:交給資料庫管理,簡單有效

缺點:性能代價高,特别是shard越來越多時

2、應用程式和資料庫共同控制

原理:将一個跨多個資料庫的分布式事物分拆成多個僅處于單個資料上面的小事物,并通過

應用程式來總控各個小事物。

優點:性能上有優勢。

缺點:需要應用程式在事物控制上做靈活的設計。

2、跨節點JOIN問題

隻要進行切分,跨節點JOIN是不可避免的。解決這一問題的普遍做法兩次查詢實作。在第一次查詢的結果集中找出關聯資料的ID,根據這些ID發起第二次請求得到關聯的資料。

3、跨界店的count.order by,group by聚合函數的問題

這是一類問題,因為他們都需要基于全部資料集進行計算。解決方案:與跨界店IOIN問題類似,分别在各個節點得到結果後再用用程式端進行合并。和JOIN不同的是每個節點的查詢可以并行執行,是以很多時候它的速度比單一大表快很多。結果集很大,對應用程式的記憶體消耗是一個問題。

4、資料遷移,容量規劃,擴容等問題

來自淘寶綜合業務平台團隊,它利用對2的倍數取餘具有向前相容的特性(如對4取餘得1的數對2取餘也是1)來配置設定資料,避免了行級别資料的遷移,但是依然需要進行表級别的遷移,同時對于擴容規模和分别數量都有限制。總體上,這些方案都不是十分的理想,多多少少存在一些缺點,同時反映了Sharding擴容的難度。

5、事物

優點

1、基于兩階段送出,最大限度保證跨庫資料庫操作的“原子性”,是分布式系統下最嚴謹的事物實作方式。

2、實作簡單,工作量小。由于多數應用伺服器以及一些獨立的分布式事物協調器做出了大量的封裝工作,使得項目引入分布式事物的難度和工作量基本上可以忽略不計。

缺點

系統伸縮的死敵。基于兩階段送出的分布式事物在送出事物時需要進行多個節點之間的協調,最大限度的退後了事物的時間點,客觀上延遲了事物的執行時間,會導緻事物通路共享資源時發生沖突和死鎖的機率增高,随着資料庫節點的增加,這種趨勢會越來越嚴重,進而成為系統在資料層面上水準伸縮的“枷鎖”。

事物補償(幂等值)

對那些對性能要求很高,但對一緻性要求并不高的系統。往往并不苟求系統的實時一緻性,隻要在一個允許的時間周期内達到最終一緻性即可,這使得事物補償機制成為一種方案。事物補償機制最初被提出是在“長事物”的進行中,單對于分布式系統確定一緻性很有很好的參考意義。籠統的講,與事物在執行過程中發生錯誤立即復原的方式不同,事物補償性是一種事後檢查并補救的措施,它隻期望在一個容許的時間周期内得到最終一緻的結果就可以了。事物補償是實作與系統業務緊密相關,并沒有一種标準的處理方案。一些常見的實作方式:對資料進行對賬檢查,基于日志進行對比,定期同标準資料來源進行同步。。。

6、ID問題

一旦資料被切分到多個實體節點上,我麼将不再依賴資料庫自身的主鍵生成機制。一方面,每個分區資料庫生成的ID無法保證在全局上是唯一的;另一方面,應用程式在插入資料之前需要擷取ID,以便SQL路由。

UUID

使用UUID作為主鍵是最簡單的方案,但是缺點也是非常明顯的。由于UUID非常的長,除占用大量的存儲空間,最主要的問題在索引上,在建立索引和基于索引進行查詢時存在性能

問題。

結合資料庫維護一個Sequence表此方案簡單,在資料庫中建立一個Sequence表

CREATE TABLE `SEQUENCE` (
`table_name` varchar(18) NOT NULL,
`nextid` bigint(20) NOT NULL,
PRIMARY KEY (`table_name`)
) ENGINE=InnoDB      

每當需要為某個表新紀錄生成ID是就從Sequence表中取出對應表的nextid,并将nextid的值加1更新到資料庫中一以備下次使用。此方法簡單,但是缺點明顯,由于所有插入任何都需要通路該表,該表很容易成為系統的性能瓶頸,同時也存在單點的問題,一旦還資料庫失效,整個應用程式将無法工作。有人提出用Master-Slave進行主從同步,但是也隻能解決單點問題,并不能解決讀寫1:1的通路壓力問題。Twitter的分布式自增ID算法Snowflake在分布式系統中嗎,需要生成全局的UID的場合還是蠻多的,twitter的snowfake解決了這種需要,實作也是很簡單的,除去配置資訊,核心代碼就是毫秒級時間41位 機器ID 10位毫秒内序列12位。

* 10---0000000000 0000000000 0000000000 0000000000 0 --- 00000 ---00000 --
-000000000000      

在上面的字元串中,第一位為未使用(實際上也可作為long的符号位),接下來的41位為毫秒級時間,然後5位datacenter辨別位,5位機器ID(并不算辨別符,實際是為線程辨別),然後12位該毫秒内的目前毫秒内的計數,加起來剛好64位,為一個Long型。這樣的好處是,整體上按照時間自增排序,并且整個分布式系統内不會産生ID碰撞(由datacenter和機器ID作區分),并且效率較高,經測試,snowflake每秒能夠産生26萬ID左右,完全滿足需要。

7、跨分片的排序分頁

一般來說,分頁的時候需要按照指定的字段進行排序。當排序字段就是分片字段的時候,我們通過分片規則可以較為容易的定位到指定的分片,而當排序字段非分片字段的時候,情況就複雜了。為了結果的準确性,我們需要在不通的分片節點将資料進行排序并傳回,将不通分片傳回的結果集進行彙總和再次排序。最後傳回給使用者。

Mysql中的分庫分表

上面描述的是最簡單的一種情況(去第一頁的資料),看起來對性能的影響不大。但是,如果取出第10頁資料,情況就複雜很多了。

Mysql中的分庫分表

為什麼不取10條,而是把前10頁的資料全部取出來了呢?其實也不難了解,因為個節點的資料可能是随機的,為了排序的準确性,必須把所有分片節點前的前N也資料都拍好序後做合并,最終進行整體的排序。很顯然這樣的操作是很消耗性能的,使用者越往後翻,性能就會越差。

那麼如何解決呢?

1、如果是前台提供分頁。則限定使用者隻能看前面n頁,這個限定在業務上也是合情合理的,一般看後面的分頁的意義不大(如果一定要看,可以要求使用者縮小範圍重新查詢)

2、如果是背景批處理任務要求分批擷取資料,則可以加大page size,比如每次擷取5000條記錄,有效減少分頁(當然離線通路一般走備庫,避免沖擊主庫)

3、可以借助大資料平台

8、分庫政策

分庫次元确定後,如何把記錄分到各個庫中呢

1、根據數值範圍,比如ID為1-9999到第一個庫,10000-20000的到第二個庫

2、根據數值取模,比如ID mod n,餘數為0的記錄到第一個庫中,為1的記錄到第二個庫中。

9、分庫數量

分庫數量首先和單庫處理的記錄數有關,一般來說MySQL單庫超過5000萬條記錄,Oracle超過1億條記錄,DB壓力就很大(當然還跟資料表中記錄有關系)在滿足上述前提下,如果分庫的數量少了,達不到分散存儲和減輕DB性能壓力的目的;果分庫的數量多,好處是每個庫的記錄少,單庫通路性能好,但對于跨多個庫的通路,應用如程式需要通路多個庫,如果并發模式,要消耗寶貴的線程資源;如果串行模式,執行時間會急劇增加。分庫的數量影響硬體的投入,一般每個分庫泡在單獨的實體機上面,多一個意味着多一台裝置。一般建議分4-8個庫。

10、路由透明

分庫從某種意義上,意味着DB schema改變了,必然影響應用,但這種改變和業務無關,是以要盡量保證分庫對應用代碼透明,分庫邏輯盡量在資料通路層處理。當然完全做到這一點很困難,具體哪些應該由DAL負責,哪些由應用負責,這裡有一些建議:對于單庫通路,比如查詢條件指定使用者Id,則該SQL隻需通路特定庫。此時應該由DAL層自動路由到特定庫,當庫二次分裂時,也隻要修改mod 因子,應用代碼不受影響。

對于簡單的多庫查詢,DAL負責彙總各個資料庫傳回的記錄,此時仍對上層應用透明。

Mysql中的分庫分表