分庫分表的方法教程

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

站在用戶的角度思考問題，與客戶深入溝通，找到大寧網(wǎng)站設(shè)計與大寧網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設(shè)計與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：網(wǎng)站制作、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、申請域名、網(wǎng)頁空間、企業(yè)郵箱。業(yè)務(wù)覆蓋大寧地區(qū)。

MySQL表大小限制

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

操作系統(tǒng)塊大小	最大文件尺寸	最大文件系統(tǒng)尺寸
1KB	16GB	2TB
2KB	256GB	8TB
4KB	2TB	16TB
8KB	16TB	32TB

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

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

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

分表方案

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

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

垂直分表

還是以訂單表 orders  為例，按照字段進(jìn)行拆分，這里面需要考慮一個問題，如何拆分字段才能表上的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)的，就是稍微比較麻煩點(diǎn)，可以搞一個  route 表(主鍵ID, 原表名，字段名，子表名)，每次解析SQL時都需要根據(jù)原表名 + 字段名去獲取需要的子表，然后再改寫 SQL，執(zhí)行 SQL  返回結(jié)果，這種代碼改造量太大，而且容易出錯，故這種垂直拆分在實際業(yè)務(wù)中用的不多。

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

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

水平分表

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

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

這樣就將 orders  表拆分成20個子表，對應(yīng)到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)。

按月分表

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

MySQL分區(qū)表

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

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

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

分庫方案

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

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

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

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

按表分庫

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

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

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

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

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

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

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

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

4. 分庫分表后動態(tài)擴(kuò)容不好實現(xiàn)，例如開始分了20個表，不影響業(yè)務(wù)的情況下擴(kuò)容至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)，不同的服務(wù)的數(shù)據(jù)庫是分布在不同的 RDS 實例上的，在禁止跨庫 Join 的情況下，數(shù)據(jù)同步是一種解決方案。

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

分布式事務(wù)問題

拆分之后，數(shù)據(jù)分布在不同的 RDS 實例上，對表的 DML 操作就變成了多個子表的 DML 操作，就涉及到分布式事務(wù)，也要遵循事務(wù) ACID  特性，同時也會提到兩個重要的理論：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 兩階段提交)，分為兩個階段：Prepare 和 Commit，事務(wù)處理過程如下

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

本地消息表

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

處理過程

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

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

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

水平拆分

分布式全局唯一ID

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

分片鍵選擇

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

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

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

跨庫Join問題

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

跨庫排序分頁

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

跨庫函數(shù)處理

在使用max，min，sum，count之類的函數(shù)進(jìn)行統(tǒng)計和計算的時候，需要先在每個分片數(shù)據(jù)源上執(zhí)行相應(yīng)的函數(shù)處理，然后將各個結(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)系的表記錄存放在同一個分片上，就能很好地避免跨分片  join 問題。

非分片鍵過濾

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

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

到此，關(guān)于“分庫分表的方法教程”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>

文章題目：分庫分表的方法教程
文章分享：http://muchs.cn/article0/jsoioo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站制作、微信公眾號、動態(tài)網(wǎng)站、營銷型網(wǎng)站建設(shè)、網(wǎng)站導(dǎo)航、企業(yè)建站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)