我們在前面兩章中就已經(jīng)說明了事務問題、分庫策略、分庫數(shù)量、遷移、框架選型，本文主要講述分庫數(shù)量、主鍵、排序、分頁join的問題。

目的

• 多少行數(shù)據(jù)記錄開始做分庫分表
• 解決主鍵生成策略
• 解決排序、分頁join
• 領域表設計

分庫數(shù)量

1. 量級

分庫數(shù)量首先和單庫能處理的記錄數(shù)有關，一般來說，Mysql 單庫超過5000萬條記錄，Oracle單庫超過1億條記錄，DB壓力就很大。個人希望在500萬~1000萬時進行分庫。

2. 硬件

在滿足上述前提下，如果分庫數(shù)量少，達不到分散存儲和減輕DB性能壓力的目的；如果分庫的數(shù)量多，好處是每個庫記錄少，單庫訪問性能好，但對于跨多個庫的訪問，應用程序需要訪問多個庫，如果是并發(fā)模式，要消耗寶貴的線程資源；如果是串行模式，執(zhí)行時間會急劇增加。

分庫數(shù)量還直接影響硬件的投入，一般每個分庫跑在單獨物理機上，多一個庫意味多一臺設備。所以具體分多少個庫，要綜合評估，一般初次分庫建議分4-8個庫。

3. DDL

考慮是否會加字段，試過在表數(shù)據(jù)量在6000萬的時候，mysql加一個字段就花了一兩個小時。我還是建議在1000萬的時候就要預備了，因為數(shù)據(jù)量增長會很快的，當然我們要鑒于自身系統(tǒng)的增長速度。

對一個很大的表進行DDL修改時，MySQL會鎖住全表，這個時間會很長，這段時間業(yè)務不能訪問此表，影響很大。在此操作過程中，都算為風險時間。將數(shù)據(jù)表拆分，總量減少，有助于降低這個風險。

主鍵生成策略

因為分庫分表的情況下，對于訂單號、userId不能使用自增的形式，最好在未分庫分表前，做好訂單號的規(guī)則，不使用uuid，因為會帶字母。下面介紹雪花算法和算法的變體。實現(xiàn)還是推薦使用redis保證分布式唯一吧。

1.雪花算法

雪花算法解析 結構 snowflake的結構如下(每部分用-分開):

上面每個位的值為0/1

其核心思想是：
第一bit為未使用，接下來的41 bit為毫秒級時間(41位的長度可以使用69年)，
然后是5bit datacenterId和5bit workerId(10位的長度最多支持部署1024個節(jié)點） ，
最后12bit 是毫秒內(nèi)的計數(shù)（12位的計數(shù)順序號支持每個節(jié)點每毫秒產(chǎn)生4096個ID序號）
一共加起來剛好64 bit，為一個Long型。(轉(zhuǎn)換成字符串長度為18)。

2.自定義生成規(guī)則

大多數(shù)的號都用上述方法即可，只是其中一些場景會特殊規(guī)則，如放款/還款的支付流水號。
為了用于便于人為閱讀，如財務核算時需要閱讀流水號，導出數(shù)據(jù)進金蝶軟件的場景，用于適應金蝶軟件導入規(guī)則。
下述這種就太長了，只能用String存儲，因為Long最大值為2^63-1=9223372036854775807。這個是個20位數(shù)字。

第一節(jié) 兩位是用于表示業(yè)務類型，足夠一個系統(tǒng)有99個業(yè)務類型，如01表示用戶還款，02表示用戶借款。如果更多業(yè)務類型，可能該考慮拆系統(tǒng)，如果真的不夠可以寫3位。當然這個不是必要，第一節(jié)只是用來容易人為識別。
第二節(jié) 是時間，像支付寶支付的流水號就是有帶時間的，這樣用戶或者客服可以直觀看出這個單是什么時候生成，排查問題也比較方便
第三節(jié) 是機器id，由代碼獲取ip，然后自定義算法，生成一個5位數(shù)，記得不要寫真實ip，不然就會被所有人發(fā)現(xiàn)了。
第四節(jié) 是計數(shù)位，表示同一個ip下在同一個毫秒下，可以有9999次計數(shù)

共28位，已經(jīng)超出long的最大值，所以存String類型。

額外第五節(jié)

有些公司會有第五節(jié)，第五節(jié) 是父級id的hash值，意思是假如這個是還款支付流水號，最后四位可以是userId的hash值。

這樣做是有原因的，最后4位可以方便的根據(jù)支付流水號定位到物理表坐標。因為如果這個是支付流水號，假如這個支付流水號只有前面四節(jié)，根據(jù)上一章的第四、五方案一致性hash，根據(jù)會算出分庫和分表的所在位置。但是這樣就不方便開發(fā)、運維人為上mysql server找到數(shù)據(jù)。所以會填上userId的hash值（如 id mode 64）作為第五節(jié)的前兩位表示分庫位置，userId / 64 mod 64作為分表坐標。

例如 用戶id % 64 取余 最多可以分64張表，而目前可能用不到這么多，每相鄰4個數(shù)字分配到一張表，共16張表，既 userID % 64 / 4 * 4 ,而這個地方存儲 userID % 64 即可，不必存最終分表的結果（這個算法設計請閱讀第一章）。

但是我認為第五節(jié)不是很合理，這種方式不方便后續(xù)做擴容，mod 64 可能不足以支撐業(yè)務時，可能要分128片（mod 128）的時候，可能分表的規(guī)則變更了，但是訂單號已無法進行變更，這些訂單號也不能去update，已經(jīng)給財務那邊做核算了。

所以我認為最好不與其他的相關聯(lián)。

跨節(jié)點查詢

包括跨節(jié)點Join、count,order by,group by以及聚合函數(shù)問題、跨分片的排序分頁，其實分庫后都沒辦法連表、join查詢的，因為對兩個數(shù)據(jù)庫操作都是創(chuàng)建兩個tcp連接，兩個tcp連接之間是不會通信，所以只能查詢出數(shù)據(jù)后，再程序中給兩個數(shù)據(jù)庫查出來的數(shù)據(jù)做聚合的代碼操作。

以分頁來舉例

一般來講，分頁時需要按照指定字段進行排序。當排序字段就是分片字段的時候，我們通過分片規(guī)則可以比較容易定位到指定的分片，而當排序字段非分片字段的時候，如不是用戶表的用戶的userId，情況就會變得比較復雜了。

為了最終結果的準確性，我們需要在不同的分片節(jié)點中將數(shù)據(jù)進行排序并返回，并將不同分片返回的結果集進行匯總和再次排序，最后再返回給用戶。如下圖所示：

image

上面圖中所描述的只是最簡單的一種情況（取第一頁數(shù)據(jù)），看起來對性能的影響并不大。但是，如果想取出第10頁數(shù)據(jù)，情況又將變得復雜很多，如下圖所示：

image

有些讀者可能并不太理解，為什么不能像獲取第一頁數(shù)據(jù)那樣簡單處理（排序取出前10條再合并、排序）。其實并不難理解，因為各分片節(jié)點中的數(shù)據(jù)可能是隨機的，為了排序的準確性，必須把所有分片節(jié)點的前N頁數(shù)據(jù)都排序好后做合并，最后再進行整體的排序。很顯然，這樣的操作是比較消耗資源的，用戶越往后翻頁，系統(tǒng)性能將會越差。
那如何解決分庫情況下的分頁問題呢？有以下幾種辦法：

如果是在前臺應用提供分頁，則限定用戶只能看前面n頁，這個限制在業(yè)務上也是合理的，一般看后面的分頁意義不大（如果一定要看，可以要求用戶縮小范圍重新查詢）。

如果是后臺批處理任務要求分批獲取數(shù)據(jù)，則可以加大page size，比如每次獲取5000條記錄，有效減少分頁數(shù)（當然離線訪問一般走備庫，避免沖擊主庫）。

分庫設計時，一般還有配套大數(shù)據(jù)平臺匯總所有分庫的記錄，有些分頁查詢可以考慮走大數(shù)據(jù)平臺。

Join

如同分頁一樣，沒有辦法兩個庫之間join查詢，只能通過兩次查詢后，在程序中進行聚合，或者先做一個表的查詢，把得出的結果作為第二個表的查詢條件。
盡量通過數(shù)據(jù)冗余或表分組來降低跨庫 Join 的可能。

領域表設計

在開發(fā)過程中，盡量使用利用領域驅(qū)動，按照數(shù)據(jù)關聯(lián)性劃分數(shù)據(jù)模型，把關聯(lián)度高度綁定在一起，在項目初期很有可能幾個領域都在一個數(shù)據(jù)庫中，如劃分好用戶模塊、訂單模塊等。

因為用戶主表和用戶明細表會經(jīng)常一起查詢，這兩個表都用一個字段作為分庫分表作為Key，用戶的主表和明細表都在一個庫中。這樣就可以減少Join和關聯(lián)查詢、分頁查詢等。
訂單表可以用userId和訂單id兩個字段作為分庫分表的Key，以后可以不用與用戶表放在一個庫。

2019-09-29

歡迎關注

我的公眾號 ：地藏思維

掘金：地藏Kelvin
簡書：地藏Kelvin
CSDN：地藏Kelvin
我的Gitee： 地藏Kelvin https://gitee.com/dizang-kelvin