[[354914]]

本文轉(zhuǎn)載自微信公眾號「三太子敖丙」，作者三太子敖丙 。轉(zhuǎn)載本文請聯(lián)系三太子敖丙公眾號。

我們都知道，隨著業(yè)務量的增長，數(shù)據(jù)量也會隨之增加，這個時候就需要關(guān)注業(yè)務大表，因為大表會影響查詢性能，DDL變更時間很長，影響業(yè)務的可用性，同時導致從庫延遲很大，如果業(yè)務做了讀寫分離，導致用戶重復操作產(chǎn)生臟數(shù)據(jù)，例如重復下單。

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

MySQL表大小限制

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

查看操作系統(tǒng)頁大小及塊大小

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

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

分表方案

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

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

垂直分表

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

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

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

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

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

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

水平分表

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

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

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

按月分表

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

MySQL分區(qū)表

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

這樣就創(chuàng)建了20個分區(qū)，對應磁盤上就是20個數(shù)據(jù)文件(orders#p#p0.ibd一直到orders#p#p19.ibd)，來看一下SQL的執(zhí)行過程。

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

分庫方案

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

按業(yè)務分庫

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

這樣按照業(yè)務模塊拆分之后，相應的 trade 數(shù)據(jù)庫被拆分到了三個 RDS 實例中，數(shù)據(jù)庫的寫入能力提升，服務的接口響應時間也變短了，提高了系統(tǒng)的穩(wěn)定性。

按表分庫

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

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

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

分布式數(shù)據(jù)庫

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

1. MySQL單 Master 的寫入性能瓶頸。
2. 分庫分表后的 SQL 解析處理，服務調(diào)用鏈路變長，系統(tǒng)變得不穩(wěn)定。
3. 分庫分表后動態(tài)擴容不好實現(xiàn)，例如開始分了20個表，不影響業(yè)務的情況下擴容至50個表不好實現(xiàn)。

拆分后的問題

垂直拆分

跨庫Join問題

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

首先要考慮這種垂直拆分的合理性，如果可以調(diào)整，那就優(yōu)先調(diào)整，如果無法調(diào)整，根據(jù)以往的實際經(jīng)驗，總結(jié)幾種常見的解決思路。

全局表

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

數(shù)據(jù)同步

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

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

分布式事務問題

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

MySQL XA事務

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

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

本地消息表

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

處理過程

消息生產(chǎn)方：需要額外建一個消息表，并記錄消息發(fā)送狀態(tài)，消息表和業(yè)務數(shù)據(jù)要在一個事務里提交，也就是說他們要在一個數(shù)據(jù)庫里面。然后消息會經(jīng)過 MQ 發(fā)送到消息的消費方，如果消息發(fā)送失敗，會進行重試發(fā)送。

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

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

水平拆分

分布式全局唯一ID

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

分片鍵選擇

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

數(shù)據(jù)擴容

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

跨庫Join問題

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

跨庫排序分頁

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

跨庫函數(shù)處理

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

ER分片

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

非分片鍵過濾

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

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

總結(jié)

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

好啦以上就是本期的全部內(nèi)容了，我是敖丙，你知道的越多，你不知道的越多，我們下期見。