分庫分表的方法教程

這篇文章主要介紹“分庫分表的方法教程”，在日常操作中，相信很多人在分庫分表的方法教程問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”分庫分表的方法教程”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

MySQL表大小限制

MySQL一般安裝部署在Linux操作系統上(例如CentOS  7.4)，默認都是InnoDB存儲引擎，且開啟了獨立表空間選項(參數innodb_file_per_table=1)，此時創建一個表 orders  就會自動生成一個數據文件 orders.ibd，文件大小是受操作系統 Block 大小限制的，下面是 ext3 文件系統塊大小和最大尺寸的對應關系。

查看操作系統頁大小及塊大小

這就說明 MySQL 單表的最大尺寸不能超過  2TB，我們簡單來算一下，假設一個表的平均行長度為32KB(InnoDB最大行長度限制65536字節，64KB)，那么他最大能存儲多少行數據?4 x 1024  x 1024 x 1024 / 32 = 134217728大約 1.4 億不到。

對于餓了么，美團那外賣種交易系統的訂單表 1.4 億是很容易達到的，一天平均 2000W 訂單，一周就到 1.4  億了，沒法玩了，一般都會采用異地多活的方案，根據用戶的位置將數據寫到相應的 IDC 數據中心，這其實也是一種高大上的分表方案，不在我們今天討論范圍啦。

分表方案

分表的應用場景是單表數據量增長速度過快，影響了業務接口的響應時間，但是 MySQL 實例的負載并不高，這時候只需要分表，不需要分庫(拆分實例)。

我們知道，一個表大小是滿足如下公式的：TABLE_SIZE = AVG_ROW_SIZE x  ROWS，從這里可以知道表太大，要么是平均行長度太大，也就說表的字段太多，要么是表的記錄數太多。這就產生兩種不同的分表方案，即切分字段(垂直分表)和切分記錄(水平分表)  。

垂直分表

還是以訂單表 orders  為例，按照字段進行拆分，這里面需要考慮一個問題，如何拆分字段才能表上的DML性能最大化，常規的方案是冷熱分離(將使用頻率高字段放到一張表里，剩下使用頻繁低的字段放到另一張表里)。

orders 表通過拆分之后，就變成了 orders01 和 orders02 兩張表，在磁盤上就會存儲兩個數據文件 orders01.ibd 和  orders02.ibd，orders 表最大尺寸就是 4TB 了，拆分完之后，該怎么查詢呢?舉個例子：

分析下上面的 SQL，select  后面的列分別位于兩張表中(order_id,order_sn在orders01中，source在orders02中)，上面的SQL可以查詢重寫為如下形式。

如果用了數據庫中間件就會自動實現查詢重寫，例如 mycat，sharding-sphere，不用中間件的話，也可以實現的，就是稍微比較麻煩點，可以搞一個  route 表(主鍵ID, 原表名，字段名，子表名)，每次解析SQL時都需要根據原表名 + 字段名去獲取需要的子表，然后再改寫 SQL，執行 SQL  返回結果，這種代碼改造量太大，而且容易出錯，故這種垂直拆分在實際業務中用的不多。

如果業務表中有必須的 Text 類型來存儲數據，這時可以利用垂直拆分來減少表大小，將 text 字段拆分到子表中。

這樣將 text 類型拆分放到子表中之后，原表的平均行長度就變小了，就可以存儲更多的數據了。

水平分表

水平拆分表就是按照表中的記錄進行分片，舉個例子，目前訂單表 orders 有 2000w 數據，根據業務的增長，估算一年之后會達到1億，同時參考阿里云  RDS for MySQL 的最佳實踐，單表不建議超過 500w，1億數據分20個子表就夠了。

問題來了，按照什么來拆分呢?主鍵id還是用戶的user_id，按主鍵ID拆分數據很均勻，通過ID查詢 orders 的場景幾乎沒有，業務訪問 orders  大部分場景都是根據 user_id來過濾的，而且 user_id 的唯一性又很高(一個 user_id 對應的 orders 表記錄不多，選擇性很好)，按照  user_id 來作為 Sharding key能滿足大部分業務場景，拆分之后每個子表數據也比較均勻。

這樣就將 orders  表拆分成20個子表，對應到InnoDB的存儲上就是20個數據文件(orders_0.ibd，orders_1.ibd等)，這時候執行SQL語句select  order_id, order_sn, source from **orders** where user_id =  1001;就能很快的定位到要查找記錄的位置是在orders_1，然后做查詢重寫，轉化為SQL語句select order_id, order_sn,  source from **orders_01** where user_id = 1001，這種查詢重寫功能很多中間件都已經實現了，常用的就是  sharding-sphere 或者 sharding-jdbc 都可以實現。

按月分表

對于賬務或者計費類系統，每天晚上都會做前一天的日結或日賬任務，每月的1號都會做月結或月賬任務，任務執行完之后相關表的數據都已靜態化了(業務層不需要這些數據)，根據業務的特性，可以按月創建表，比如對于賬單表  bills，就可以創建按月分表(十月份表bills_202010，202011十一月份表)，出完月賬任務之后，就可以歸檔到歷史庫了，用于數據倉庫ETL來做分析報表，確認數據都同步到歷史庫之后就可以刪除這些表釋放空間。

MySQL分區表

你可能在想，上面的水平分表之后，還要改造代碼要能保證 SQL  正確的路由，執行并返回結果，這個調用鏈路有點長吧，MySQL內部有沒有分表的解決方案呢?其實是有的，可以考慮使用 MySQL 的 HASH 分區，常規的  hash 也是基于分區個數取模(%)運算的，跟上面的user_id % 20是一樣的，來看一個例子。

這樣就創建了20個分區，對應磁盤上就是20個數據文件(ordersp0.ibd一直到ordersp19.ibd)，來看一下SQL的執行過程。

從執行計劃可以看到，通過分區鍵user_id過濾，直接可以定位到數據所在的分區 p19(user_id =1019 % 20 =  19，所以在p19分區上)，進而去訪問p19對應的數據文件 ordersp19.ibd 即可獲得數據。這種方案的好處就是 MySQL 內部實現 SQL  路由的功能，不用去改造業務代碼。

分庫方案

聊了下分表的方案，那什么時候分庫呢?我們知道，MySQL 的高可用架構大多都是一主多從，所有寫入操作都發生在 Master  上，隨著業務的增長，數據量的增加，很多接口響應時間變得很長，經常出現 Timeout，而且通過升級 MySQL  實例配置已經無法解決問題了，這時候就要分庫，通常有兩種做法：按業務拆庫和按表分庫，下面就介紹這兩種分庫方案啦。

按業務分庫

舉個例子，交易系統 trade 數據庫單獨部署在一臺 RDS  實例，現在交易需求及功能越來越多，訂單，價格及庫存相關的表增長很快，部分接口的耗時增加，同時有大量的慢查詢告警，升級 RDS  配置效果不大，這時候就需要考慮拆分業務，將庫存，價格相關的接口獨立出來。

這樣按照業務模塊拆分之后，相應的 trade 數據庫被拆分到了三個 RDS  實例中，數據庫的寫入能力提升，服務的接口響應時間也變短了，提高了系統的穩定性。

按表分庫

上面介紹了分表方案，常見的有垂直分表和水平分表(拆分后的子表都在同一個 RDS 實例中存儲)，對應的分庫就是垂直分庫和水平分庫，這里的分庫其實是拆分  RDS 實例，是將拆分后的子表存儲在不同的 RDS 實例中，垂直分庫實際業務用的很少，就不介紹了，主要介紹下水平分庫。

舉個例子，交易數據庫的訂單表 orders 有2億多數據，RDS 實例遇到了寫入瓶頸，普通的 insert 都需要50ms，時常也會收到 CPU  使用率告警，這時就要考慮分庫了。根據業務量增長趨勢，計劃擴容一臺同配置的RDS實例，將訂單表 orders 拆分20個子表，每個 RDS 實例10個。

這樣解決了訂單表 orders 太大的問題，查詢的時候要先通過分區鍵 user_id 定位是哪個  RDS 實例，再定位到具體的子表，然后做  DML操作，問題是代碼改造的工作量大，而且服務調用鏈路變長了，對系統的穩定性有一定的影響。其實已經有些數據庫中間件實現了分庫分表的功能，例如常見的  mycat，阿里云的 DRDS 等。

分布式數據庫

通過上面的分表和分庫方案的介紹，主要會遇到下面三類問題：

1. 鴻蒙官方戰略合作共建——HarmonyOS技術社區

2. MySQL單 Master 的寫入性能瓶頸。

3. 分庫分表后的 SQL 解析處理，服務調用鏈路變長，系統變得不穩定。

4. 分庫分表后動態擴容不好實現，例如開始分了20個表，不影響業務的情況下擴容至50個表不好實現。

拆分后的問題

垂直拆分

跨庫Join問題

在垂直拆分之前，系統中所需的數據是可以通過表 Join 來完成的，而拆分之后，數據庫可能分布式在不同 RDS 實例，Join 處理起來比較麻煩，根據  MySQL 開發規范，一般是禁止跨庫 Join 的，那該怎么處理呢?

首先要考慮這種垂直拆分的合理性，如果可以調整，那就優先調整，如果無法調整，根據以往的實際經驗，總結幾種常見的解決思路。

全局表

用過 mycat 做分庫分表的朋友都清楚，有個全局表的概念，也就是每個 DataNode  上都有一份全量數據，例如一些數據字典表，數據很少修改，可以避免跨庫 Join 的性能問題。

數據同步

對于分布式系統，不同的服務的數據庫是分布在不同的 RDS 實例上的，在禁止跨庫 Join 的情況下，數據同步是一種解決方案。

通過數據同步工具將 user 庫的 users 表實時同步到trade庫中，這樣就可以直接在 trade 庫做 Join  操作，比較依賴于同步工具的穩定性，如果同步有延遲，就會導致數據不一致，產生臟數據，需要做好風險評估和兜底方案。

分布式事務問題

拆分之后，數據分布在不同的 RDS 實例上，對表的 DML 操作就變成了多個子表的 DML 操作，就涉及到分布式事務，也要遵循事務 ACID  特性，同時也會提到兩個重要的理論：CAP(Consistency一致性，Availability可用性，Partition  tolerance分區容忍性Partitiontolerance)和BASE(Basically Available基本可用， Soft  state軟狀態，Eventually consistent最終一致性)，進而產生了解決分布式事務問題不同的方案。

MySQL XA事務

MySQL支持分布式事務(XA 事務或者 2PC 兩階段提交)，分為兩個階段：Prepare 和 Commit，事務處理過程如下

如果任何一個 XA Client 否決了此次提交，所有數據庫都要求 XA Manager  回滾它們在事務中的信息，優點是可以最大程度保證了數據的強一致，適合對數據強一致要求很高的業務場景;缺點就是實現復雜，犧牲了可用性，對性能影響較大，不適合高并發高性能場景。

本地消息表

本地消息表實現方式應該是業界使用最多的，其核心思想是將分布式事務拆分成本地事務進行處理，其基本的設計思想是將遠程分布式事務拆分成一系列的本地事務。

處理過程

消息生產方：需要額外建一個消息表，并記錄消息發送狀態，消息表和業務數據要在一個事務里提交，也就是說他們要在一個數據庫里面。然后消息會經過 MQ  發送到消息的消費方，如果消息發送失敗，會進行重試發送。

消息消費方：需要處理這個消息，并完成自己的業務邏輯，此時如果本地事務處理成功，表明已經處理成功了，如果處理失敗，那么就會重試執行。如果是業務上面的失敗，可以給生產方發送一個業務補償消息，通知生產方進行回滾等操作。

生產方和消費方定時掃描本地消息表，把還沒處理完成的消息或者失敗的消息再發送一遍。如果有靠譜的自動對賬補賬邏輯，這種方案還是非常實用的。

水平拆分

分布式全局唯一ID

MySQL InnoDB的表都是使用自增的主鍵ID，分庫分表之后，數據表分布不同的分片上，如果使用自增 ID 作為主鍵，就會出現不同分片上的主機 ID  重復現象，可以利用 Snowflake 算法生成唯一ID。

分片鍵選擇

選擇分片鍵時，需要先統計該表上的所有的  SQL，盡量選擇使用頻率且唯一值多的字段作為分片鍵，既能做到數據均勻分布，又能快速定位到數據位置，例如user_id，order_id等。

數據擴容

舉個例子，目前交易數據庫 trade 中的訂單表 orders 已經做了水平分庫(位于兩個不同RDS實例上)，這時發現兩個 RDS  寫入性能還是不夠，需要再擴容一個RDS，同時將 orders 從原來的 20 個子表擴容到 40個(user_id %  40)，這就需要遷移數據來實現數據重平衡，既要停機遷移數據，又要修改代碼，有點出力不討好的感覺啦。

跨庫Join問題

跟垂直拆分中的跨庫 Join 問題是一樣的。

跨庫排序分頁

在處理order by user_id limit n場景是，當排序字段就是分片字段 user_id  的時候，通過分片鍵可以很容易定位到具體的分片，而當排序字段非分片字段的時候，例如order by  create_time，處理起來就會變得復雜，需要在不同的分片節中將數據進行排序并返回，并將不同分片返回的結果集進行匯總和再次排序，最后再返回給用戶。

跨庫函數處理

在使用max，min，sum，count之類的函數進行統計和計算的時候，需要先在每個分片數據源上執行相應的函數處理，然后將各個結果集進行二次處理，最終再將處理結果返回。

ER分片

在 RDBMS 系統中，表之間往往存在一些關聯的關系，如果可以先確定好關聯關系，并將那些存在關聯關系的表記錄存放在同一個分片上，就能很好地避免跨分片  join 問題。

非分片鍵過濾

大部分業務場景都可以根據分片鍵來過濾，但是有些場景沒有分片鍵過濾，例如按照狀態和時間范圍來查詢訂單表 orders，常見的SQL 這樣的。

這種就很痛苦了，只能全部分片數據掃描一遍，將每個分片的數據Union之后再回復給客戶端，這種場景可以考慮創建復合索引(status，create_time)讓SQL走索引范圍掃描，同時減少返回的數據量，如果是核心業務場景，可以考慮實時實時數倉(例如基于MPP架構的分析型數據庫  ADB，分布式列式數據庫 Clickhouse)，將需要的表實時同步到數倉，然后再做處理，這也是實際業務中常見一種解決方案。

到此，關于“分庫分表的方法教程”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！