分庫(kù)分表的方法教程

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

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)頁(yè)大小及塊大小

這就說明 MySQL 單表的最大尺寸不能超過  2TB，我們簡(jiǎn)單來算一下，假設(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  億了，沒法玩了，一般都會(huì)采用異地多活的方案，根據(jù)用戶的位置將數(shù)據(jù)寫到相應(yīng)的 IDC 數(shù)據(jù)中心，這其實(shí)也是一種高大上的分表方案，不在我們今天討論范圍啦。

分表方案

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

我們知道，一個(gè)表大小是滿足如下公式的：TABLE_SIZE = AVG_ROW_SIZE x  ROWS，從這里可以知道表太大，要么是平均行長(zhǎng)度太大，也就說表的字段太多，要么是表的記錄數(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ù)庫(kù)中間件就會(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 類型拆分放到子表中之后，原表的平均行長(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í)踐，單表不建議超過 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語(yǔ)句select  order_id, order_sn, source from **orders** where user_id =  1001;就能很快的定位到要查找記錄的位置是在orders_1，然后做查詢重寫，轉(zhuǎn)化為SQL語(yǔ)句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ù)之后，就可以歸檔到歷史庫(kù)了，用于數(shù)據(jù)倉(cāng)庫(kù)ETL來做分析報(bào)表，確認(rèn)數(shù)據(jù)都同步到歷史庫(kù)之后就可以刪除這些表釋放空間。

MySQL分區(qū)表

你可能在想，上面的水平分表之后，還要改造代碼要能保證 SQL  正確的路由，執(zhí)行并返回結(jié)果，這個(gè)調(diào)用鏈路有點(diǎn)長(zhǎng)吧，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ù)文件(ordersp0.ibd一直到ordersp19.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ù)文件 ordersp19.ibd 即可獲得數(shù)據(jù)。這種方案的好處就是 MySQL 內(nèi)部實(shí)現(xiàn) SQL  路由的功能，不用去改造業(yè)務(wù)代碼。

分庫(kù)方案

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

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

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

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

按表分庫(kù)

上面介紹了分表方案，常見的有垂直分表和水平分表(拆分后的子表都在同一個(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í)例遇到了寫入瓶頸，普通的 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 太大的問題，查詢的時(shí)候要先通過分區(qū)鍵 user_id 定位是哪個(gè)  RDS 實(shí)例，再定位到具體的子表，然后做  DML操作，問題是代碼改造的工作量大，而且服務(wù)調(diào)用鏈路變長(zhǎng)了，對(duì)系統(tǒng)的穩(wěn)定性有一定的影響。其實(shí)已經(jīng)有些數(shù)據(jù)庫(kù)中間件實(shí)現(xiàn)了分庫(kù)分表的功能，例如常見的  mycat，阿里云的 DRDS 等。

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

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

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

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

3. 分庫(kù)分表后的 SQL 解析處理，服務(wù)調(diào)用鏈路變長(zhǎng)，系統(tǒng)變得不穩(wěn)定。

4. 分庫(kù)分表后動(dòng)態(tài)擴(kuò)容不好實(shí)現(xiàn)，例如開始分了20個(gè)表，不影響業(yè)務(wù)的情況下擴(kuò)容至50個(gè)表不好實(shí)現(xiàn)。

拆分后的問題

垂直拆分

跨庫(kù)Join問題

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

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

全局表

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

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

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

通過數(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ù)問題

拆分之后，數(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ù)庫(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ù)。

處理過程

消息生產(chǎn)方：需要額外建一個(gè)消息表，并記錄消息發(fā)送狀態(tài)，消息表和業(yè)務(wù)數(shù)據(jù)要在一個(gè)事務(wù)里提交，也就是說他們要在一個(gè)數(shù)據(jù)庫(kù)里面。然后消息會(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，分庫(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  寫入性能還是不夠，需要再擴(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)出力不討好的感覺啦。

跨庫(kù)Join問題

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

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

在處理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)行匯總和再次排序，最后再返回給用戶。

跨庫(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 問題。

非分片鍵過濾

大部分業(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ù)倉(cāng)(例如基于MPP架構(gòu)的分析型數(shù)據(jù)庫(kù)  ADB，分布式列式數(shù)據(jù)庫(kù) Clickhouse)，將需要的表實(shí)時(shí)同步到數(shù)倉(cāng)，然后再做處理，這也是實(shí)際業(yè)務(wù)中常見一種解決方案。

到此，關(guān)于“分庫(kù)分表的方法教程”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！