資料庫分庫分表解決方案彙總

來源 | cnblogs.com/butterfly100/p/9034281.html

一. 資料切分

關係型資料庫本身比較容易成為系統瓶頸，單機儲存容量、連線數、處理能力都有限。當單表的資料量達到1000W或100G以後，由於查詢維度較多，即使新增從庫、最佳化索引，做很多操作時效能仍下降嚴重。此時就要考慮對其進行切分了，切分的目的就在於減少資料庫的負擔，縮短查詢時間。

資料庫分散式核心內容無非就是資料切分（Sharding），以及切分後對資料的定位、整合。資料切分就是將資料分散儲存到多個資料庫中，使得單一資料庫中的資料量變小，透過擴充主機的數量緩解單一資料庫的效能問題，從而達到提升資料庫操作效能的目的。

資料切分根據其切分型別，可以分為兩種方式：垂直（縱向）切分和水平（橫向）切分。

1、垂直（縱向）切分

垂直切分常見有垂直分庫和垂直分表兩種。

垂直分庫就是根據業務耦合性，將關聯度低的不同表儲存在不同的資料庫。做法與大系統拆分為多個小系統類似，按業務分類進行獨立劃分。與"微服務治理"的做法相似，每個微服務使用單獨的一個資料庫。如圖：

垂直分表是基於資料庫中的"列"進行，某個表欄位較多，可以新建一張擴充套件表，將不經常用或欄位長度較大的欄位拆分出去到擴充套件表中。在欄位很多的情況下（例如一個大表有100多個欄位），透過"大表拆小表"，更便於開發與維護，也能避免跨頁問題，MySQL底層是透過資料頁儲存的，一條記錄佔用空間過大會導致跨頁，造成額外的效能開銷。另外資料庫以行為單位將資料載入到記憶體中，這樣表中欄位長度較短且訪問頻率較高，記憶體能載入更多的資料，命中率更高，減少了磁碟IO，從而提升了資料庫效能。

垂直切分的優點：

• 解決業務系統層面的耦合，業務清晰

• 與微服務的治理類似，也能對不同業務的資料進行分級管理、維護、監控、擴充套件等

• 高併發場景下，垂直切分一定程度的提升IO、資料庫連線數、單機硬體資源的瓶頸

缺點：

• 部分表無法join，只能透過介面聚合方式解決，提升了開發的複雜度

• 分散式事務處理複雜

• 依然存在單表資料量過大的問題（需要水平切分）

2、水平（橫向）切分

當一個應用難以再細粒度的垂直切分，或切分後資料量行數巨大，存在單庫讀寫、儲存效能瓶頸，這時候就需要進行水平切分了。

水平切分分為庫內分表和分庫分表，是根據表內資料內在的邏輯關係，將同一個表按不同的條件分散到多個資料庫或多個表中，每個表中只包含一部分資料，從而使得單個表的資料量變小，達到分散式的效果。如圖所示： 

庫內分表只解決了單一表資料量過大的問題，但沒有將表分佈到不同機器的庫上，因此對於減輕MySQL資料庫的壓力來說，幫助不是很大，大家還是競爭同一個物理機的CPU、記憶體、網路IO，最好透過分庫分表來解決。

水平切分的優點：

• 不存在單庫資料量過大、高併發的效能瓶頸，提升系統穩定性和負載能力

• 應用端改造較小，不需要拆分業務模組

缺點：

• 跨分片的事務一致性難以保證

• 跨庫的join關聯查詢效能較差

• 資料多次擴充套件難度和維護量極大

水平切分後同一張表會出現在多個資料庫/表中，每個庫/表的內容不同。幾種典型的資料分片規則為：

1、根據數值範圍

按照時間區間或ID區間來切分。例如：按日期將不同月甚至是日的資料分散到不同的庫中；將userId為1~9999的記錄分到第一個庫，10000~20000的分到第二個庫，以此類推。某種意義上，某些系統中使用的"冷熱資料分離"，將一些使用較少的歷史資料遷移到其他庫中，業務功能上只提供熱點資料的查詢，也是類似的實踐。

這樣的優點在於：

• 單表大小可控

• 天然便於水平擴充套件，後期如果想對整個分片叢集擴容時，只需要新增節點即可，無需對其他分片的資料進行遷移

• 使用分片欄位進行範圍查詢時，連續分片可快速定位分片進行快速查詢，有效避免跨分片查詢的問題。

缺點：

• 熱點資料成為效能瓶頸。連續分片可能存在資料熱點，例如按時間欄位分片，有些分片儲存最近時間段內的資料，可能會被頻繁的讀寫，而有些分片儲存的歷史資料，則很少被查詢

2、根據數值取模

一般採用hash取模mod的切分方式，例如：將 Customer 表根據 cusno 欄位切分到4個庫中，餘數為0的放到第一個庫，餘數為1的放到第二個庫，以此類推。這樣同一個使用者的資料會分散到同一個庫中，如果查詢條件帶有cusno欄位，則可明確定位到相應庫去查詢。

優點：

• 資料分片相對比較均勻，不容易出現熱點和併發訪問的瓶頸

缺點：

• 後期分片叢集擴容時，需要遷移舊的資料（使用一致性hash演算法能較好的避免這個問題）

• 容易面臨跨分片查詢的複雜問題。比如上例中，如果頻繁用到的查詢條件中不帶cusno時，將會導致無法定位資料庫，從而需要同時向4個庫發起查詢，再在記憶體中合併資料，取最小集返回給應用，分庫反而成為拖累。

二. 分庫分錶帶來的問題

分庫分表能有效的緩解單機和單庫帶來的效能瓶頸和壓力，突破網路IO、硬體資源、連線數的瓶頸，同時也帶來了一些問題。下面將描述這些技術挑戰以及對應的解決思路。 

1、事務一致性問題

分散式事務

當更新內容同時分佈在不同庫中，不可避免會帶來跨庫事務問題。跨分片事務也是分散式事務，沒有簡單的方案，一般可使用"XA協議"和"兩階段提交"處理。

分散式事務能最大限度保證了資料庫操作的原子性。但在提交事務時需要協調多個節點，推後了提交事務的時間點，延長了事務的執行時間。導致事務在訪問共享資源時發生衝突或死鎖的機率增高。隨著資料庫節點的增多，這種趨勢會越來越嚴重，從而成為系統在資料庫層面上水平擴充套件的枷鎖。

最終一致性

對於那些效能要求很高，但對一致性要求不高的系統，往往不苛求系統的實時一致性，只要在允許的時間段內達到最終一致性即可，可採用事務補償的方式。與事務在執行中發生錯誤後立即回滾的方式不同，事務補償是一種事後檢查補救的措施，一些常見的實現方法有：對資料進行對賬檢查，基於日誌進行對比，定期同標準資料來源進行同步等等。事務補償還要結合業務系統來考慮。

2、跨節點關聯查詢 join 問題

切分之前，系統中很多列表和詳情頁所需的資料可以透過sql join來完成。而切分之後，資料可能分佈在不同的節點上，此時join帶來的問題就比較麻煩了，考慮到效能，儘量避免使用join查詢。

解決這個問題的一些方法：

1）全域性表

全域性表，也可看做是"資料字典表"，就是系統中所有模組都可能依賴的一些表，為了避免跨庫join查詢，可以將這類表在每個資料庫中都儲存一份。這些資料通常很少會進行修改，所以也不擔心一致性的問題。

2）欄位冗餘

一種典型的反正規化設計，利用空間換時間，為了效能而避免join查詢。例如：訂單表儲存userId時候，也將userName冗餘儲存一份，這樣查詢訂單詳情時就不需要再去查詢"買家user表"了。

但這種方法適用場景也有限，比較適用於依賴欄位比較少的情況。而冗餘欄位的資料一致性也較難保證，就像上面訂單表的例子，買家修改了userName後，是否需要在歷史訂單中同步更新呢？這也要結合實際業務場景進行考慮。

3）資料組裝

在系統層面，分兩次查詢，第一次查詢的結果集中找出關聯資料id，然後根據id發起第二次請求得到關聯資料。最後將獲得到的資料進行欄位拼裝。

4）ER分片

關係型資料庫中，如果可以先確定表之間的關聯關係，並將那些存在關聯關係的表記錄存放在同一個分片上，那麼就能較好的避免跨分片join問題。在1:1或1:n的情況下，通常按照主表的ID主鍵切分。如下圖所示：

這樣一來，Data Node1上面的order訂單表與orderdetail訂單詳情表就可以透過orderId進行區域性的關聯查詢了，Data Node2上也一樣。

3、跨節點分頁、排序、函式問題

跨節點多庫進行查詢時，會出現limit分頁、order by排序等問題。分頁需要按照指定欄位進行排序，當排序欄位就是分片欄位時，透過分片規則就比較容易定位到指定的分片；當排序欄位非分片欄位時，就變得比較複雜了。需要先在不同的分片節點中將資料進行排序並返回，然後將不同分片返回的結果集進行彙總和再次排序，最終返回給使用者。如圖所示：

上圖中只是取第一頁的資料，對效能影響還不是很大。但是如果取得頁數很大，情況則變得複雜很多，因為各分片節點中的資料可能是隨機的，為了排序的準確性，需要將所有節點的前N頁資料都排序好做合併，最後再進行整體的排序，這樣的操作是很耗費CPU和記憶體資源的，所以頁數越大，系統的效能也會越差。

在使用Max、Min、Sum、Count之類的函式進行計算的時候，也需要先在每個分片上執行相應的函式，然後將各個分片的結果集進行彙總和再次計算，最終將結果返回。如圖所示：

4、全域性主鍵避重問題

在分庫分表環境中，由於表中資料同時存在不同資料庫中，主鍵值平時使用的自增長將無用武之地，某個分割槽資料庫自生成的ID無法保證全域性唯一。因此需要單獨設計全域性主鍵，以避免跨庫主鍵重複問題。有一些常見的主鍵生成策略：

1）UUID

UUID標準形式包含32個16進位制數字，分為5段，形式為8-4-4-4-12的36個字元，例如：550e8400-e29b-41d4-a716-446655440000

UUID是主鍵是最簡單的方案，本地生成，效能高，沒有網路耗時。但缺點也很明顯，由於UUID非常長，會佔用大量的儲存空間；另外，作為主鍵建立索引和基於索引進行查詢時都會存在效能問題，在InnoDB下，UUID的無序性會引起資料位置頻繁變動，導致分頁。

2）結合資料庫維護主鍵ID表

在資料庫中建立 sequence 表：

CREATE TABLE `sequence` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `stub` char(1) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=MyISAM;

stub欄位設定為唯一索引，同一stub值在sequence表中只有一條記錄，可以同時為多張表生成全域性ID。sequence表的內容，如下所示：

+-------------------+------+
| id | stub |
+-------------------+------+
| 72157623227190423 | a |
+-------------------+------+

使用 MyISAM 儲存引擎而不是 InnoDB，以獲取更高的效能。MyISAM使用的是表級別的鎖，對錶的讀寫是序列的，所以不用擔心在併發時兩次讀取同一個ID值。

當需要全域性唯一的64位ID時，執行：

REPLACE INTO sequence (stub) VALUES ('a');
SELECT LAST_INSERT_ID();

這兩條語句是Connection級別的，select last_insert_id() 必須與 replace into 在同一資料庫連線下才能得到剛剛插入的新ID。

使用replace into代替insert into好處是避免了錶行數過大，不需要另外定期清理。

此方案較為簡單，但缺點也明顯：存在單點問題，強依賴DB，當DB異常時，整個系統都不可用。配置主從可以增加可用性，但當主庫掛了，主從切換時，資料一致性在特殊情況下難以保證。另外效能瓶頸限制在單臺MySQL的讀寫效能。

flickr團隊使用的一種主鍵生成策略，與上面的sequence表方案類似，但更好的解決了單點和效能瓶頸的問題。

這一方案的整體思想是：建立2個以上的全域性ID生成的伺服器，每個伺服器上只部署一個資料庫，每個庫有一張sequence表用於記錄當前全域性ID。表中ID增長的步長是庫的數量，起始值依次錯開，這樣能將ID的生成雜湊到各個資料庫上。如下圖所示：

由兩個資料庫伺服器生成ID，設定不同的auto_increment值。第一臺sequence的起始值為1，每次步長增長2，另一臺的sequence起始值為2，每次步長增長也是2。結果第一臺生成的ID都是奇數（1, 3, 5, 7 ...），第二臺生成的ID都是偶數（2, 4, 6, 8 ...）。

這種方案將生成ID的壓力均勻分佈在兩臺機器上。同時提供了系統容錯，第一臺出現了錯誤，可以自動切換到第二臺機器上獲取ID。但有以下幾個缺點：系統新增機器，水平擴充套件時較複雜；每次獲取ID都要讀寫一次DB，DB的壓力還是很大，只能靠堆機器來提升效能。

可以基於flickr的方案繼續最佳化，使用批次的方式降低資料庫的寫壓力，每次獲取一段區間的ID號段，用完之後再去資料庫獲取，可以大大減輕資料庫的壓力。如下圖所示：

還是使用兩臺DB保證可用性，資料庫中只儲存當前的最大ID。ID生成服務每次批次拉取6個ID，先將max_id修改為5，當應用訪問ID生成服務時，就不需要訪問資料庫，從號段快取中依次派發0~5的ID。當這些ID發完後，再將max_id修改為11，下次就能派發6~11的ID。於是，資料庫的壓力降低為原來的1/6。

3）Snowflake分散式自增ID演算法

Twitter的snowflake演算法解決了分散式系統生成全域性ID的需求，生成64位的Long型數字，組成部分：

• 第一位未使用

• 接下來41位是毫秒級時間，41位的長度可以表示69年的時間

• 5位datacenterId，5位workerId。10位的長度最多支援部署1024個節點

• 最後12位是毫秒內的計數，12位的計數順序號支援每個節點每毫秒產生4096個ID序列

這樣的好處是：毫秒數在高位，生成的ID整體上按時間趨勢遞增；不依賴第三方系統，穩定性和效率較高，理論上QPS約為409.6w/s（1000*2^12），並且整個分散式系統內不會產生ID碰撞；可根據自身業務靈活分配bit位。

不足就在於：強依賴機器時鐘，如果時鐘回撥，則可能導致生成ID重複。

綜上結合資料庫和snowflake的唯一ID方案，可以參考業界較為成熟的解法：Leaf——美團點評分散式ID生成系統，並考慮到了高可用、容災、分散式下時鐘等問題。

5、資料遷移、擴容問題

當業務高速發展，面臨效能和儲存的瓶頸時，才會考慮分片設計，此時就不可避免的需要考慮歷史資料遷移的問題。一般做法是先讀出歷史資料，然後按指定的分片規則再將資料寫入到各個分片節點中。此外還需要根據當前的資料量和QPS，以及業務發展的速度，進行容量規劃，推算出大概需要多少分片（一般建議單個分片上的單表資料量不超過1000W）。

如果採用數值範圍分片，只需要新增節點就可以進行擴容了，不需要對分片資料遷移。如果採用的是數值取模分片，則考慮後期的擴容問題就相對比較麻煩。

三. 什麼時候考慮切分

下面講述一下什麼時候需要考慮做資料切分。

1、能不切分儘量不要切分

並不是所有表都需要進行切分，主要還是看資料的增長速度。切分後會在某種程度上提升業務的複雜度，資料庫除了承載資料的儲存和查詢外，協助業務更好的實現需求也是其重要工作之一。

不到萬不得已不用輕易使用分庫分表這個大招，避免"過度設計"和"過早最佳化"。分庫分表之前，不要為分而分，先盡力去做力所能及的事情，例如：升級硬體、升級網路、讀寫分離、索引最佳化等等。當資料量達到單表的瓶頸時候，再考慮分庫分表。

2、資料量過大，正常運維影響業務訪問

這裡說的運維指：

1）對資料庫備份，如果單表太大，備份時需要大量的磁碟IO和網路IO。例如1T的資料，網路傳輸佔50MB時候，需要20000秒才能傳輸完畢，整個過程的風險都是比較高的

2）對一個很大的表進行DDL修改時，MySQL會鎖住全表，這個時間會很長，這段時間業務不能訪問此表，影響很大。如果使用pt-online-schema-change，使用過程中會建立觸發器和影子表，也需要很長的時間。在此操作過程中，都算為風險時間。將資料表拆分，總量減少，有助於降低這個風險。

3）大表會經常訪問與更新，就更有可能出現鎖等待。將資料切分，用空間換時間，變相降低訪問壓力

3、隨著業務發展，需要對某些欄位垂直拆分

舉個例子，假如專案一開始設計的使用者表如下：

id                   bigint #使用者的ID
name varchar #使用者的名字
last_login_time datetime #最近登入時間
personal_info text #私人資訊
..... #其他資訊欄位

在專案初始階段，這種設計是滿足簡單的業務需求的，也方便快速迭代開發。而當業務快速發展時，使用者量從10w激增到10億，使用者非常的活躍，每次登入會更新 last_login_name 欄位，使得 user 表被不斷update，壓力很大。而其他欄位：id, name, personal_info 是不變的或很少更新的，此時在業務角度，就要將 last_login_time 拆分出去，新建一個 user_time 表。

personal_info 屬性是更新和查詢頻率較低的，並且text欄位佔據了太多的空間。這時候，就要對此垂直拆分出 user_ext 表了。

4、資料量快速增長

隨著業務的快速發展，單表中的資料量會持續增長，當效能接近瓶頸時，就需要考慮水平切分，做分庫分表了。此時一定要選擇合適的切分規則，提前預估好資料容量。

5、安全性和可用性

雞蛋不要放在一個籃子裡。在業務層面上垂直切分，將不相關的業務的資料庫分隔，因為每個業務的資料量、訪問量都不同，不能因為一個業務把資料庫搞掛而牽連到其他業務。利用水平切分，當一個資料庫出現問題時，不會影響到100%的使用者，每個庫只承擔業務的一部分資料，這樣整體的可用性就能提高。

四. 案例分析

1、使用者中心業務場景

使用者中心是一個非常常見的業務，主要提供使用者註冊、登入、查詢/修改等功能，其核心表為：

User(uid, login_name, passwd, sex, age, nickname)

uid為使用者ID,  主鍵

login_name, passwd, sex, age, nickname, 使用者屬性

任何脫離業務的架構設計都是耍流氓，在進行分庫分表前，需要對業務場景需求進行梳理：

使用者側：前臺訪問，訪問量較大，需要保證高可用和高一致性。主要有兩類需求：

• 使用者登入：透過login_name/phone/email查詢使用者資訊，1%請求屬於這種型別

• 使用者資訊查詢：登入之後，透過uid來查詢使用者資訊，99%請求屬這種型別

運營側：後臺訪問，支援運營需求，按照年齡、性別、登陸時間、註冊時間等進行分頁的查詢。是內部系統，訪問量較低，對可用性、一致性的要求不高。

2、水平切分方法

當資料量越來越大時，需要對資料庫進行水平切分，上文描述的切分方法有"根據數值範圍"和"根據數值取模"。

"根據數值範圍"：以主鍵uid為劃分依據，按uid的範圍將資料水平切分到多個資料庫上。例如：user-db1儲存uid範圍為0~1000w的資料，user-db2儲存uid範圍為1000w~2000wuid資料。

優點是：擴容簡單，如果容量不夠，只要增加新db即可。

不足是：請求量不均勻，一般新註冊的使用者活躍度會比較高，所以新的user-db2會比user-db1負載高，導致伺服器利用率不平衡

"根據數值取模"：也是以主鍵uid為劃分依據，按uid取模的值將資料水平切分到多個資料庫上。例如：user-db1儲存uid取模得1的資料，user-db2儲存uid取模得0的uid資料。

優點是：資料量和請求量分佈均均勻

不足是：擴容麻煩，當容量不夠時，新增加db，需要rehash。需要考慮對資料進行平滑的遷移。

3、非uid的查詢方法

水平切分後，對於按uid查詢的需求能很好的滿足，可以直接路由到具體資料庫。而按非uid的查詢，例如login_name，就不知道具體該訪問哪個庫了，此時需要遍歷所有庫，效能會降低很多。

對於使用者側，可以採用"建立非uid屬性到uid的對映關係"的方案；對於運營側，可以採用"前臺與後臺分離"的方案。

3.1、建立非uid屬性到uid的對映關係

1）對映關係

例如：login_name不能直接定位到資料庫，可以建立login_name→uid的對映關係，用索引表或快取來儲存。當訪問login_name時，先透過對映表查詢出login_name對應的uid，再透過uid定位到具體的庫。

對映表只有兩列，可以承載很多資料，當資料量過大時，也可以對對映表再做水平切分。這類kv格式的索引結構，可以很好的使用cache來最佳化查詢效能，而且對映關係不會頻繁變更，快取命中率會很高。

2）基因法

分庫基因：假如透過uid分庫，分為8個庫，採用uid%8的方式進行路由，此時是由uid的最後3bit來決定這行User資料具體落到哪個庫上，那麼這3bit可以看為分庫基因。

上面的對映關係的方法需要額外儲存對映表，按非uid欄位查詢時，還需要多一次資料庫或cache的訪問。如果想要消除多餘的儲存和查詢，可以透過f函式取login_name的基因作為uid的分庫基因。生成uid時，參考上文所述的分散式唯一ID生成方案，再加上最後3位bit值=f(login_name)。當查詢login_name時，只需計算f(login_name)%8的值，就可以定位到具體的庫。不過這樣需要提前做好容量規劃，預估未來幾年的資料量需要分多少庫，要預留一定bit的分庫基因。

3.2、前臺與後臺分離

對於使用者側，主要需求是以單行查詢為主，需要建立login_name/phone/email到uid的對映關係，可以解決這些欄位的查詢問題。

而對於運營側，很多批次分頁且條件多樣的查詢，這類查詢計算量大，返回資料量大，對資料庫的效能消耗較高。此時，如果和使用者側共用同一批服務或資料庫，可能因為後臺的少量請求，佔用大量資料庫資源，而導致使用者側訪問效能降低或超時。

這類業務最好採用"前臺與後臺分離"的方案，運營側後臺業務抽取獨立的service和db，解決和前臺業務系統的耦合。由於運營側對可用性、一致性的要求不高，可以不訪問實時庫，而是透過binlog非同步同步資料到運營庫進行訪問。在資料量很大的情況下，還可以使用ES搜尋引擎或Hive來滿足後臺複雜的查詢方式。

五. 支援分庫分表中介軟體

站在巨人的肩膀上能省力很多，目前分庫分表已經有一些較為成熟的開源解決方案：

• sharding-jdbc（噹噹）

• TSharding（蘑菇街）

• Atlas（奇虎360）

• Cobar（阿里巴巴）

• MyCAT（基於Cobar）

• Oceanus（58同城）

• Vitess（谷歌）

六. 參考

[1] 資料庫分散式架構掃盲——分庫分表（及銀行核心系統適用性思考） 

[2] 分庫分表的思想 

[3] 水平分庫分表的關鍵步驟以及可能遇到的問題 

[4] 從原則、方案、策略及難點闡述分庫分表 

[5] Leaf——美團點評分散式ID生成系統