為什么要分庫(kù)分表

隨著業(yè)務(wù)量的增加導(dǎo)致數(shù)據(jù)庫(kù)中數(shù)據(jù)量的增加，可能拖慢查詢的性能，影響業(yè)務(wù)的可用性；如果數(shù)據(jù)庫(kù)采用讀寫分離，可能會(huì)導(dǎo)致從庫(kù)的延遲較大，主庫(kù)進(jìn)行寫操作后，從庫(kù)因?yàn)檠舆t無法及時(shí)同步，會(huì)導(dǎo)致出現(xiàn)數(shù)據(jù)不一致的情況。

分表：應(yīng)對(duì)大數(shù)據(jù)量。當(dāng)單表的數(shù)據(jù)達(dá)到百萬級(jí)時(shí)，sql 的執(zhí)行性能就較差了；當(dāng)超過千萬級(jí)時(shí)，sql 的執(zhí)行性能急劇下降，所以要保持 sql 的執(zhí)行性能，就必須采用分表。

分庫(kù)：應(yīng)對(duì)高并發(fā)。一個(gè)健康的單數(shù)據(jù)庫(kù)并發(fā)最好維持到 1000 個(gè)連接左右，超過就可能造成宕機(jī)崩潰。采用分庫(kù)就可以提高數(shù)據(jù)庫(kù)的并發(fā)能力。

分庫(kù)分表的原則

在進(jìn)行分庫(kù)分表時(shí)，我們最好遵循一下原則：

1. 優(yōu)先進(jìn)行 MySQL 調(diào)優(yōu)，能不分就不分

數(shù)據(jù)量能穩(wěn)定在千萬級(jí)，近幾年不會(huì)到達(dá)億級(jí)，其實(shí)是不用著急拆的，先嘗試MySQL調(diào)優(yōu)，優(yōu)化讀寫性能。只有在MySQL調(diào)優(yōu)已經(jīng)無法解決慢查詢問題時(shí)，才可以考慮分庫(kù)分表。

1. 分片的數(shù)量盡量少

分片就是將數(shù)據(jù)存儲(chǔ)在多個(gè)數(shù)據(jù)庫(kù)實(shí)例中，在分庫(kù)中就是將數(shù)據(jù)拆分到多個(gè)獨(dú)立的數(shù)據(jù)庫(kù)中，在分表中就是將一個(gè)大表拆分成多個(gè)小表，每個(gè)小表存儲(chǔ)數(shù)據(jù)的一部分。

如果實(shí)例太多，分的太細(xì)，那么查詢一個(gè) sql 可能要跨越多個(gè)分區(qū)，降低查詢的性能。

1. 數(shù)據(jù)的分布要盡量均勻

數(shù)據(jù)應(yīng)該盡量均勻的分布在多個(gè)分片中，原因同上，這就涉及到分片建的選擇了。

分表方案

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

一張表的大小由 字段數(shù)量 × 記錄數(shù)量 構(gòu)成，也就是說如果表太大，那么要么是表的記錄太多，要么就是表的字段太多。這就產(chǎn)生了兩種分表方案：

• 垂直分表（拆分字段）
• 水平分表（拆分記錄）

垂直分表

以訂單表 orders 為例，按照字段進(jìn)行拆分，這里面需要考慮一個(gè)問題，如何拆分字段才能表上的DML性能最大化。

1. 常規(guī)的垂直分表方案就是冷熱分離拆分（將使用頻率高字段放到一張表里，剩下使用頻繁低的字段放到另一張表里）。

什么情況下可以使用冷熱分離？

1. **數(shù)據(jù)走到終態(tài)后只有讀沒有寫的需求。**例如訂單完結(jié)后基本只會(huì)讀不會(huì)改。
2. **用戶能接受新舊數(shù)據(jù)分開查詢。**比如有些電商網(wǎng)站默認(rèn)只讓查詢3個(gè)月內(nèi)的訂單，如果要查詢3個(gè)月前的訂單，還需要訪問其他的頁(yè)面。

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)查詢重寫，但是每次解析SQL時(shí)都需要根據(jù)原表名 + 字段名去獲取需要的子表，然后再改寫 SQL，執(zhí)行 SQL 返回結(jié)果，這種代碼改造量太大，而且容易出錯(cuò)。

業(yè)務(wù)場(chǎng)景舉例：

• 郵件系統(tǒng)：郵件系統(tǒng)中最近郵件是用戶經(jīng)常訪問和修改的，三個(gè)月前的郵件或已歸檔的郵件不經(jīng)常訪問的?？梢詫⒂脩舻氖占?、發(fā)件箱里最近三個(gè)月的郵件放在一個(gè)庫(kù)里（熱庫(kù)），之前的郵件或者已讀的郵件放在另一個(gè)庫(kù)里（冷酷）。
• **日志系統(tǒng)：**在大型應(yīng)用中，日志數(shù)據(jù)是非常龐大的，但并不是所有日志都需要經(jīng)常查詢或分析?？梢詫⒆罱欢螘r(shí)間的活動(dòng)日志存放在熱庫(kù)中，而將過去的歷史日志存放在冷庫(kù)中，以減輕熱庫(kù)的負(fù)載和優(yōu)化查詢性能。
• 社交媒體平臺(tái)：社交媒體平臺(tái)上的用戶數(shù)據(jù)量通常很大，但是只有少部分用戶是活躍的，并且只有少量用戶的數(shù)據(jù)會(huì)頻繁訪問和更新，如果所有用戶都放在同一個(gè)庫(kù)里，勢(shì)必會(huì)影響活躍用戶的查詢效率?？梢詫⒒钴S用戶的個(gè)人信息、好友關(guān)系等存放在熱庫(kù)中，而將不活躍用戶的數(shù)據(jù)存放在冷庫(kù)中，以提升熱庫(kù)的性能和減少冷庫(kù)的存儲(chǔ)成本。
• **電商平臺(tái)：**電商平臺(tái)上的商品數(shù)據(jù)也可以進(jìn)行冷熱分離。熱庫(kù)中存放熱門商品的基本信息和庫(kù)存等，以支持頻繁的查詢和更新操作，而將不活躍或下架的商品信息存放在冷庫(kù)中，以減少熱庫(kù)的負(fù)載和優(yōu)化查詢性能。
• 客服工單：在我們?nèi)粘２僮鲿r(shí)，經(jīng)常能看到查詢歷史工單時(shí)會(huì)有個(gè)“近三個(gè)月工單”的選項(xiàng)，實(shí)際業(yè)務(wù)場(chǎng)景中，用戶基本只會(huì)關(guān)注近三個(gè)月工單，而且這些工單也會(huì)經(jīng)常需要進(jìn)行修改、刪除的操作，而對(duì)很早期的歷史訂單基本就沒有修改、刪除的需求，只有少量的查詢需求。

1. 還可以根據(jù)業(yè)務(wù)的層面來進(jìn)行拆分：將混合業(yè)務(wù)拆分為獨(dú)立業(yè)務(wù)。

業(yè)務(wù)場(chǎng)景舉例：

• **電商網(wǎng)站：**一個(gè)典型的混合業(yè)務(wù)，包含用戶信息、訂單信息、商品信息等?？梢詫⒂脩粜畔?、訂單信息和商品信息分別拆分到不同的庫(kù)或表中，以減少數(shù)據(jù)冗余并提高訪問效率。
• 社交媒體平臺(tái)：包含用戶信息、好友關(guān)系、動(dòng)態(tài)信息等?？梢詫⒂脩粜畔⒑秃糜殃P(guān)系分離存儲(chǔ)，以便更好地支持好友關(guān)系的查詢和更新。
• **在線游戲：**涉及角色信息、道具信息、戰(zhàn)斗日志等?？梢詫⒔巧畔⒑偷谰咝畔⒉鸱值讲煌谋碇?#xff0c;以提升查詢效率，并將戰(zhàn)斗日志存儲(chǔ)到日志數(shù)據(jù)庫(kù)中，以減輕主數(shù)據(jù)庫(kù)的負(fù)載。
• 物流系統(tǒng)：包含訂單信息、配送信息、運(yùn)輸信息等?？梢詫⒂唵涡畔?、配送信息和運(yùn)輸信息分別拆分到不同的表中，以便更好地支持訂單的查詢和跟蹤。

1. 還有如果業(yè)務(wù)表中有 text 長(zhǎng)文本類型的字段需要存儲(chǔ)。這時(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ù)也比較均勻。

一般使用散列算法，讓數(shù)據(jù)均勻的分?jǐn)偟讲煌膸?kù)表中。如把原本在一臺(tái)機(jī)器上的數(shù)據(jù)庫(kù)存放1000萬數(shù)據(jù)，分?jǐn)偟絥臺(tái)機(jī)上，拆分這1000萬的數(shù)據(jù)和后續(xù)的增量。讓每個(gè)數(shù)據(jù)庫(kù)資源來分?jǐn)傇拘枰慌_(tái)數(shù)據(jù)庫(kù)所提供的服務(wù)。

公式： sharding_key%N

這樣就將 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)。

按日期分表

這種使用方式比較普遍，尤其是按照日期維度的拆分，其實(shí)在程序?qū)用娴母膭?dòng)很小，但是擴(kuò)展性方面的收益很大。

• 日維度拆分，如test_20191021
• 月維度拆分,如test_201910
• 年維度拆分,如test_2019

例如對(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ù)之后就可以刪除這些表釋放空間。

按主鍵范圍分表

例如【1,200w】主鍵在一個(gè)表，【200w，400w】主鍵在一個(gè)表。優(yōu)點(diǎn)是單表數(shù)據(jù)量可控。缺點(diǎn)是流量無法分?jǐn)?#xff0c;寫操作集中在最后面的表。

分庫(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ù)

舉個(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 等。

分片鍵的選擇

選擇最佳的分表字段是一個(gè)需要仔細(xì)考慮的問題。最佳的分表字段應(yīng)該是能夠讓數(shù)據(jù)分布均勻、頻繁查詢的字段以及不可變的字段。通過選擇最佳的分表字段，可以提高系統(tǒng)的性能和查詢效率。

常用字段：

• **主鍵ID：**頻繁查詢并且唯一，非常適合作分表字段。例如，在用戶表中，用戶ID作為分表字段是一個(gè)不錯(cuò)的選擇，因?yàn)橛脩鬒D是唯一的，而且在查詢用戶信息時(shí)經(jīng)常會(huì)用到。
• **時(shí)間字段：**如果業(yè)務(wù)需要按時(shí)間范圍查詢數(shù)據(jù)，那么選擇時(shí)間字段作為分表字段是合理的。例如，在日志表中，可以選擇時(shí)間戳字段作為分表字段，以便按天、按月或按年分割數(shù)據(jù)，方便查詢和維護(hù)。
• **地理信息字段：**如果業(yè)務(wù)需要按地區(qū)查詢數(shù)據(jù)，那么選擇地理信息字段作為分表字段是合適的。例如，在訂單表中，可以選擇訂單地區(qū)字段作為分表字段，以便將訂單數(shù)據(jù)按地區(qū)進(jìn)行拆分，方便查詢和擴(kuò)展。
• **關(guān)聯(lián)字段：**如果業(yè)務(wù)需要頻繁進(jìn)行關(guān)聯(lián)查詢，那么選擇訂單號(hào)等關(guān)聯(lián)字段作為分表字段。例如，在訂單表中，可以選擇訂單號(hào)作為分表字段，因?yàn)橛唵翁?hào)唯一且包含業(yè)務(wù)信息，并且日常查詢、關(guān)聯(lián)查詢都是根據(jù)訂單號(hào)查詢的，很少根據(jù)id查詢，方便查詢和維護(hù)。

選擇分表字段的原則：

\1. 數(shù)據(jù)分布均勻

最佳的分表字段應(yīng)該是能夠讓數(shù)據(jù)分布均勻的字段，這樣可以避免某個(gè)表的數(shù)據(jù)過多，導(dǎo)致查詢效率降低。在用戶表中，如果以地區(qū)作為分表字段，可能會(huì)導(dǎo)致某些地區(qū)的數(shù)據(jù)過多，而某些地區(qū)的數(shù)據(jù)過少。

2. 頻繁查詢的字段

盡量選擇查詢頻率最高的字段（例如主鍵id），然后根據(jù)表拆分方式選擇字段。在一個(gè)訂單表中，如果經(jīng)常需要根據(jù)用戶ID查詢訂單信息，那么以用戶ID作為分表字段是一個(gè)不錯(cuò)的選擇。

3. 不可變字段

最佳的分表字段還應(yīng)該是不可變的字段，這樣可以避免在數(shù)據(jù)遷移時(shí)出現(xiàn)問題。在一個(gè)商品表中，如果選擇以商品名稱作為分表字段，那么當(dāng)商品名稱發(fā)生變化時(shí)，就需要將數(shù)據(jù)移動(dòng)到不同的表中，這樣會(huì)增加系統(tǒng)的復(fù)雜度。

查詢重寫

修改代碼里的查詢、更新語句，以便讓其適應(yīng)分庫(kù)分表后的情況。通常查詢重寫是通過一些工具來自動(dòng)實(shí)現(xiàn)，比如 jdbc sharding，mycat 等

查詢語句改造：

• **單庫(kù)查詢改為跨庫(kù)查詢：**對(duì)于需要查詢的字段，需要明確指定查詢的庫(kù)和表，以避免查詢到錯(cuò)誤的數(shù)據(jù)。例如，原來的查詢語句 “SELECT * FROM users WHERE id = 1” 可以修改為 “SELECT * FROM db.table_name WHERE id = 1”，其中 db 為目標(biāo)數(shù)據(jù)庫(kù)，table_name 為目標(biāo)表。
• **單表查詢改為跨表查詢：**例如投訴記錄表根據(jù)哈希取余的方式分成10個(gè)表，如果id%1=0，則查0號(hào)表complaint_records_0。

可能出現(xiàn)的問題

• 分布式全局唯一 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等。