[[354914]]

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

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

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

MySQL表大小限制

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

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

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

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

分表方案

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

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

垂直分表

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

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

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

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

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

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

水平分表

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

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

這樣就將 orders 表拆分成20個(gè)子表，對(duì)應(yīng)到InnoDB的存儲(chǔ)上就是20個(gè)數(shù)據(jù)文件(orders_0.ibd，orders_1.ibd等)，這時(shí)候執(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)實(shí)現(xiàn)了，常用的就是 sharding-sphere 或者 sharding-jdbc 都可以實(shí)現(xiàn)。

按月分表

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

MySQL分區(qū)表

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

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

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

分庫方案

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

按業(yè)務(wù)分庫

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

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

按表分庫

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

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

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

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

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

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

拆分后的問題

垂直拆分

跨庫Join問題

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

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

全局表

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

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

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

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

分布式事務(wù)問題

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

MySQL XA事務(wù)

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

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

本地消息表

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

處理過程

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

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

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

水平拆分

分布式全局唯一ID

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

分片鍵選擇

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

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

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

跨庫Join問題

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

跨庫排序分頁

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

跨庫函數(shù)處理

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

ER分片

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

非分片鍵過濾

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

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

總結(jié)

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

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