[[354914]]

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

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

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

MySQL表大小限制

MySQL一般安裝部署在Linux操作系統(tǒng)上(例如CentOS 7.4)，默認(rèn)都是InnoDB存儲(chǔ)引擎，且開(kāi)啟了獨(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)頁(yè)大小及塊大小

這就說(shuō)明 MySQL 單表的最大尺寸不能超過(guò) 2TB，我們簡(jiǎn)單來(lái)算一下，假設(shè)一個(gè)表的平均行長(zhǎng)度為32KB(InnoDB最大行長(zhǎng)度限制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 億了，沒(méi)法玩了，一般都會(huì)采用異地多活的方案，根據(jù)用戶的位置將數(shù)據(jù)寫(xiě)到相應(yīng)的 IDC 數(shù)據(jù)中心，這其實(shí)也是一種高大上的分表方案，不在我們今天討論范圍啦。

分表方案

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

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

垂直分表

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

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

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

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

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

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

水平分表

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

問(wèn)題來(lái)了，按照什么來(lái)拆分呢?主鍵id還是用戶的user_id，按主鍵ID拆分?jǐn)?shù)據(jù)很均勻，通過(guò)ID查詢 orders 的場(chǎng)景幾乎沒(méi)有，業(yè)務(wù)訪問(wèn) orders 大部分場(chǎng)景都是根據(jù) user_id來(lái)過(guò)濾的，而且 user_id 的唯一性又很高(一個(gè) user_id 對(duì)應(yīng)的 orders 表記錄不多，選擇性很好)，按照 user_id 來(lái)作為 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語(yǔ)句select order_id, order_sn, source from **orders** where user_id = 1001;就能很快的定位到要查找記錄的位置是在orders_1，然后做查詢重寫(xiě)，轉(zhuǎn)化為SQL語(yǔ)句select order_id, order_sn, source from **orders_01** where user_id = 1001，這種查詢重寫(xiě)功能很多中間件都已經(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ù)之后，就可以歸檔到歷史庫(kù)了，用于數(shù)據(jù)倉(cāng)庫(kù)ETL來(lái)做分析報(bào)表，確認(rèn)數(shù)據(jù)都同步到歷史庫(kù)之后就可以刪除這些表釋放空間。

MySQL分區(qū)表

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

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

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

分庫(kù)方案

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

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

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

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

按表分庫(kù)

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

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

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

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

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

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

拆分后的問(wèn)題

垂直拆分

跨庫(kù)Join問(wèn)題

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

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

全局表

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

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

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

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

分布式事務(wù)問(wèn)題

拆分之后，數(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ù)問(wèn)題不同的方案。

MySQL XA事務(wù)

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

如果任何一個(gè) XA Client 否決了此次提交，所有數(shù)據(jù)庫(kù)都要求 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ù)。

處理過(guò)程

消息生產(chǎn)方：需要額外建一個(gè)消息表，并記錄消息發(fā)送狀態(tài)，消息表和業(yè)務(wù)數(shù)據(jù)要在一個(gè)事務(wù)里提交，也就是說(shuō)他們要在一個(gè)數(shù)據(jù)庫(kù)里面。然后消息會(huì)經(jīng)過(guò) 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í)掃描本地消息表，把還沒(méi)處理完成的消息或者失敗的消息再發(fā)送一遍。如果有靠譜的自動(dòng)對(duì)賬補(bǔ)賬邏輯，這種方案還是非常實(shí)用的。

水平拆分

分布式全局唯一ID

MySQL InnoDB的表都是使用自增的主鍵ID，分庫(kù)分表之后，數(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ù)庫(kù) trade 中的訂單表 orders 已經(jīng)做了水平分庫(kù)(位于兩個(gè)不同RDS實(shí)例上)，這時(shí)發(fā)現(xiàn)兩個(gè) RDS 寫(xiě)入性能還是不夠，需要再擴(kuò)容一個(gè)RDS，同時(shí)將 orders 從原來(lái)的 20 個(gè)子表擴(kuò)容到 40個(gè)(user_id % 40)，這就需要遷移數(shù)據(jù)來(lái)實(shí)現(xiàn)數(shù)據(jù)重平衡，既要停機(jī)遷移數(shù)據(jù)，又要修改代碼，有點(diǎn)出力不討好的感覺(jué)啦。

跨庫(kù)Join問(wèn)題

跟垂直拆分中的跨庫(kù) Join 問(wèn)題是一樣的。

跨庫(kù)排序分頁(yè)

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

跨庫(kù)函數(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 問(wèn)題。

非分片鍵過(guò)濾

大部分業(yè)務(wù)場(chǎng)景都可以根據(jù)分片鍵來(lái)過(guò)濾，但是有些場(chǎng)景沒(méi)有分片鍵過(guò)濾，例如按照狀態(tài)和時(shí)間范圍來(lái)查詢訂單表 orders，常見(jiàn)的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ù)倉(cāng)(例如基于MPP架構(gòu)的分析型數(shù)據(jù)庫(kù) ADB，分布式列式數(shù)據(jù)庫(kù) Clickhouse)，將需要的表實(shí)時(shí)同步到數(shù)倉(cāng)，然后再做處理，這也是實(shí)際業(yè)務(wù)中常見(jiàn)一種解決方案。

總結(jié)

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

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