前任都能看懂的分庫分表方案

我們都知道，隨着業務量的增長，數據量也會隨之增加，這個時候就需要關注業務大表，因爲大表會影響查詢性能，DDL 變更時間很長，影響業務的可用性，同時導致從庫延遲很大，如果業務做了讀寫分離，導致用戶重複操作產生髒數據，例如重複下單。

今天就跟大家討論下那些年 MySQL 使用過的分表分庫的方案，分表分庫後的問題以及解決方案，希望對您有新的收穫哦。

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 個數據文件（orders#p#p0.ibd 一直到 orders#p#p19.ibd），來看一下 SQL 的執行過程。

從執行計劃可以看到，通過分區鍵 user_id 過濾，直接可以定位到數據所在的分區 p19（user_id =1019 % 20 = 19，所以在 p19 分區上），進而去訪問 p19 對應的數據文件 orders#p#p19.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. MySQL 單 Master 的寫入性能瓶頸。

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

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

拆分後的問題

垂直拆分

  1. 跨庫 Join 問題

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

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

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

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

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

  1. 分佈式事務問題

拆分之後，數據分佈在不同的 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 發送到消息的消費方，如果消息發送失敗，會進行重試發送。

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

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

水平拆分

  1. 分佈式全局唯一 ID

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

  1. 分片鍵選擇

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

  1. 數據擴容

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

  1. 跨庫 Join 問題

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

  1. 跨庫排序分頁

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

  1. 跨庫函數處理

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

  1. ER 分片

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

  1. 非分片鍵過濾

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

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

總結

上面聊了下 MySQ L 的分表方案，分庫方案，拆分後的問題以及給出了常用的解決方案，在實際開發中，會遇到核心業務表增長很快，數據量很大，MySQL 寫入性能瓶頸的問題，這時需要根據業務的特性考慮分庫分表，可以調研下相關的解決方案，主要有兩種方案：代碼改造（數據庫中間件 mycat，sharding-sphere）和分佈式數據庫（實際業務中使用比較多的有 PingCAP TiDB，阿里雲 DRDS），可以優先使用分佈式數據庫方案，雖然成本會有所增加，但對應用程序沒有侵入性，同時也可以比較好的支撐業務增長和系統快速迭代，今天就聊這麼多，希望對您有所收穫。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/mwxG0UNZnW8xNNdg7gSSnQ

猜你喜歡