分庫分表的常見問題和示例

分庫分表

網(wǎng)上分庫分表的資料很多，這里主要是重新整理和梳理一下。如有和其他文章類似片段或解決方案，純屬前人總結或者業(yè)內標準。

分表分庫一般會在以下情況下出現(xiàn)：

一、數(shù)據(jù)庫本身的性能瓶頸

1. 單機數(shù)據(jù)庫的存儲容量限制

2. 單機數(shù)據(jù)庫的連接數(shù)限制

3. 單張表的性能瓶頸

4. 單張表性能瓶頸；

5. 單個數(shù)據(jù)庫性能瓶頸；

二、特殊場景需求

1. SasS 特定場景下的數(shù)據(jù)隔離需要；

數(shù)據(jù)庫瓶頸

不管是 IO 瓶頸，還是 CPU 瓶頸，最終都會導致數(shù)據(jù)庫的查詢緩慢甚至無法查詢。進而導致業(yè)務服務的難以提高并發(fā)量、吞吐量。數(shù)據(jù)庫瓶頸也會導致查詢緩慢、大量的超時情況進而導致程序無法使用或者崩潰的情況

IO 瓶頸

第一種：磁盤讀 IO 瓶頸，數(shù)據(jù)太多，數(shù)據(jù)庫緩存放不下，每次查詢時會產(chǎn)生大量的 IO，降低查詢速度 -> 分庫和垂直分表

第二種：網(wǎng)絡 IO 瓶頸，請求的數(shù)據(jù)太多，網(wǎng)絡帶寬不夠 -> 分庫

CPU 瓶頸

第一種：SQL 問題，如 SQL 中包含 join，group by，order by，非索引字段條件查詢等，增加 CPU 運算的操作 -> SQL 優(yōu)化，建立合適的索引，在業(yè)務 Service 層進行業(yè)務計算。

第二種：單表數(shù)據(jù)量太大，查詢時掃描的行太多，SQL 效率低，CPU 率先出現(xiàn)瓶頸 -> 水平分表。

網(wǎng)上也流傳著一些更通俗具體的說法比如：當單表的數(shù)據(jù)量達到1000W或100G以后。更通俗的說話就是數(shù)據(jù)庫已經(jīng)無法滿足性能需求了。至于什么情況不能滿足性能需求，還是要根據(jù)具體的場景來確定的，并沒有什么金科玉律。

特殊場景需求

這個就沒有辦法了，直接接受吧 ????????????????????

里面涉及的具體實現(xiàn)以 Java 為主，其他語言使用者自行腦補

數(shù)據(jù)庫本身的性能瓶頸是無法避免，但我們可以想法減輕數(shù)據(jù)庫的壓力，減輕數(shù)據(jù)庫瓶頸本身帶來的影響。

緩存

緩存可以稱的上提供性能減少數(shù)據(jù)庫查詢的一個萬金油方案，其雖然不能完美的，但一定是最先想到的。

一般可以使用進程內緩存和分布式緩存兩種方案相結合的方案。對于一致性要求不高，甚至允許一定時間內可以有數(shù)據(jù)差異的功能，可以直接采用進程內緩存來實現(xiàn)，這種方案更高效，不過其和程序本身占用同一個進程，需要考慮進程內緩存的容量問題，具體方案可以使用 Google Guava、Caffeine 以及 Spring Cache 等；如果對于一致性要求高，并且不想緩存占用更多的進程內存，則可以使用分布式緩存，其通過一個高性能外部的 Server 來存儲一些需要緩存的數(shù)據(jù)，服務通過網(wǎng)絡通信來獲取外部 Server 的緩存數(shù)據(jù)，其增加了一部分網(wǎng)絡開銷，但不用再占用業(yè)務服務的進程內存。

數(shù)據(jù)庫讀寫分離

讀寫分離也是一種有效降低數(shù)據(jù)庫壓力的方案，通過數(shù)據(jù)庫主從結構，主節(jié)點負責讀寫，從節(jié)點負責讀。這樣我們可以通過將一些讀請求分散到從節(jié)點，來減輕主節(jié)點的壓力。比如一些報表、分析、統(tǒng)計的功能模塊只允許其訪問從庫，可以在一定的條件下提升整體性能

關于一些常見的數(shù)據(jù)庫架構的模式，這里不在敘述，這和本文無關，具體模式以后可能會再寫一篇進行補充。

優(yōu)化數(shù)據(jù)庫結構和查詢語句

1. 對一些查詢條件加索引

2. 對一個表中不經(jīng)常被查詢的數(shù)據(jù)切割到一個子表中，保證主表的查詢性能

3. 適當?shù)膬?yōu)化表結構等等

當然 SQL 優(yōu)化不是本文的重點，但這也是一個優(yōu)化的方向，好的 SQL 和表結構對應性能還是有很大影響的。

其他混合方案

我們也可以通過混合其他存儲方案來減輕數(shù)據(jù)庫的壓力，比如 MongoDB、ElasticSearch。通過混合使用一些更高性能的技術方案來提高整體性能。

既然到了這里，那么肯定就要分庫分表，我第一次接觸分表的時候是 16 年大三的時候，到現(xiàn)在已有 4 年多，但記憶猶新。接下來簡單的說一下當時的情況。

第一次分表的經(jīng)歷

當時還在上大三，當時有個一個項目，里面有個 2 個模塊，一個是訂單，一個是用戶的操作記錄，這兩個都是數(shù)據(jù)量比較大的模塊，因為訂單的實時性比較高，無法做緩存處理，用戶的操作記錄數(shù)據(jù)量比較大，做緩存也沒有太大的必要，同時公司采用的云數(shù)據(jù)庫，還算比較貴，又因為其他表的數(shù)據(jù)量都比較小，沒必要為了這兩個大表來增加數(shù)據(jù)庫，這里就考慮了直接不增加數(shù)據(jù)庫實例的情況下，只做分表操作。

針對用戶操作記錄的處理

用戶操作記錄是記錄用戶在我們項目中的一系列行為，本身不能在通過拆分子表的形式降低表的大小，這里使用水平分表。根據(jù)查詢情況，用戶一般是查詢最近幾天的記錄，對幾個月前的歷史記錄很少查詢，所以這里按月份對日志表進行水平拆分。

原數(shù)據(jù)表名稱：user_action_record_log

新數(shù)據(jù)表名稱：user_action_record_log_201607、user_action_record_log_201608、user_action_record_log_201609、user_action_record_log_201610 …

將行為日志按月份拆分，表名增加對應的月份后綴

對插入的影響： 插入數(shù)據(jù)時，需要根據(jù)時間動態(tài)拼接表名

對查詢的影響： 因為按照時間進行拆分，在查詢的時候需要要求用戶指定時間段，當然這個本身影響不大，如果用戶不選擇時間，則默認只查當前月，各種情況如下：

1. 用戶不輸入時間段，默認只查當前月，通過時間計算得到當前月的表名進行查詢，例如：select * from user_action_record_log_201610

2. 用戶輸入了時間段，則計算時間段跨過的具體的月列表，然后通過 union all 進行連接查詢，例如 select * from user_action_record_log_201609 union all select * from user_action_record_log_201610 ,當然月份跨度越大 union 越多.

通過這種方式將一個大表，按月份變成了多個小表。

針對訂單表的處理

訂單表本身字段還是挺多的，包含買家信息、賣家信息、商品信息等，在做查詢時，有很多字段既不進行顯示又不參與查詢條件，這樣可以先進行垂直拆分，將訂單表這個大表拆分成一個主表一個子表，主表負責存儲一個主要的既參與查詢又參與大部分業(yè)務展示邏輯的字段，子表負責存儲大部分業(yè)務都不需要用到的字段，一般只有點到詳情也才需要的數(shù)據(jù)。

原數(shù)據(jù)表名稱：order

新數(shù)據(jù)庫名稱：order_pri, order_sub

這樣大部分查詢可以通過 select * from order_pri where ... 來完成，小部分通過 select op.*, os.* from order_pri op join order_sub os on os.parent_id = op.id where ... 來完成，這樣系統(tǒng)也穩(wěn)定運行了一段時間。因為訂單表本身增長還是挺快的，這種方案過了一段時間就不行了。這時候想了一下能不能使用日志的方案呢，因為商家更多關注的是近期的訂單，而不是歷史的訂單，再對這里面進行水平分表，按照創(chuàng)建時間分！說干就干。

原數(shù)據(jù)表名稱：order_pri, order_sub

新數(shù)據(jù)庫名稱：order_pri_201607, order_pri_201608, order_sub_201607, order_sub_201608

對插入的影響： 插入數(shù)據(jù)時，需要根據(jù)時間動態(tài)拼接表名

對查詢的影響： 因為按照創(chuàng)建時間進行拆分，在查詢的時候需要要求用戶指定時間段，當然這個本身影響不大，如果用戶不選擇時間，則默認只查當前月，各種情況如下：

1. 用戶不輸入時間段，默認只查當前月，通過時間計算得到當前月的表名進行查詢，例如：select * from order_pri_201608

2. 用戶輸入了時間段，則計算時間段跨過的具體的月列表，然后通過 union all 進行連接查詢，例如 select * from order_pri_201608 union all select * from order_pri_201609 ,當然月份跨度越大 union 越多.

通過這種方式將一個大表，按月份變成了多個小表。

對修改的影響：前端在修改某條記錄時既要傳入id, 又要傳入這個訂單的創(chuàng)建時間，主要為了定位到是哪個月份的表，當然也可以做個映射表來解決這個問題，我們沒做

這樣的系統(tǒng)其實已經(jīng)可以使用了，查詢的速度也提高了很多，基本上可以很好的運行了。難道這就完了，可不是這樣的，我們可是一個精益求精的團隊。

首先看一下訂單的狀態(tài)和各種狀態(tài)的含義：

訂單狀態(tài)主要有待付款、待發(fā)貨、待收貨、待評價、已關閉、以及退款中。

• 待付款：代表買家下單了但是還沒有付款；

• 待發(fā)貨：代表買家付款了賣家還沒有發(fā)貨；

• 已發(fā)貨（同待收貨）：代表賣家已經(jīng)發(fā)貨并寄出商品了；

• 已完成（同待評價）：代表買家已經(jīng)確認收到貨了；

• 已關閉：代表訂單過期了買家也沒付款、或者賣家關閉了訂單；

• 退款中：代表用戶已申請退款。

因為我們做的是阿里巴巴的第三方應用，所以我們的訂單結構和阿里巴巴的驚人的相似，我們項目主要服務于阿里巴巴上的商家而不是用戶，在我們的系統(tǒng)中商家更關心的是待付款、待發(fā)貨的訂單，待付款的需要進行催單、待發(fā)貨的需要商家趕緊發(fā)貨、其余狀態(tài)的訂單更多的是做統(tǒng)計，而不是實時操作，針對我們的這種業(yè)務場景，我們發(fā)現(xiàn)如果根據(jù)訂單的狀態(tài)進行分表而不是根據(jù)創(chuàng)建時間分表，似乎更加的合理，同時我們將系統(tǒng)的功能進行優(yōu)化，將非 待付款、待發(fā)貨 的訂單單獨做個頁面專門做數(shù)據(jù)查詢，對 待付款、待發(fā)貨 的訂單進行優(yōu)化處理，因為這種狀態(tài)的訂單一般很快就會被商家處理，變成其他狀態(tài)，這樣這種狀態(tài)的數(shù)據(jù)本身就不多，更適合實時查詢，對非待付款、待發(fā)貨狀態(tài)的訂單進行月份切割，保證熱點數(shù)據(jù)的查詢簡便，有保證非熱點數(shù)據(jù)的查詢快速，這樣做顯然更好。

首先將待付款、待發(fā)貨、待收貨、待評價、已關閉、以及退款中分為 3 組大狀態(tài)

根據(jù)業(yè)務情況未完成分類的數(shù)據(jù)比較少，一般為 7 天內數(shù)據(jù)，不需要進一步拆分；已完成分類的數(shù)據(jù)較多，需要進一步拆分，這里繼續(xù)根據(jù)月份進行拆分；退款分類的數(shù)據(jù)也比較少，大部分用戶都不會退款，不需要進一步拆分。

根據(jù)情況最終的分表情況如下：

最終需要根據(jù)不同的狀態(tài)使用不同的查詢方式，因為熱點數(shù)據(jù)都在 order_pri_uncompleted 表中，則可以使用正常的查詢語句，同時因為量相對少，也不會產(chǎn)生瓶頸；對于所有的數(shù)據(jù)已完成（completed）因為本身量大，還需跨表查詢。通過不同狀態(tài)，不同月份的控制最終達到了對于業(yè)務來說相對優(yōu)化的方案。

在上面的表拆分中也需要注意以下幾種情況：

1. id 的生成，需要考慮一下數(shù)據(jù)表自增 id 在跨表查詢時是否存在重復的情況，根據(jù)情況考慮全局 id 方案

2. 拆分的表是否適合查詢，畢竟我們拆分主要就是為了查詢

3. 拆分前后性能的對比

4. 拆分后的 SQL 更加復雜了，對于統(tǒng)計分析來說，是否需要引入新的的技術

常見的幾種概念

水平分表

以字段為依據(jù)，按照一定策略（hash、range 等），將一個表中的數(shù)據(jù)拆分到多個表中。

在這種分表策略中：

• 每個表的結構都一樣；

• 每個表的數(shù)據(jù)都不一樣，沒有交集；

• 所有表的并集是全量數(shù)據(jù)；

• 所有的數(shù)據(jù)還在同一個數(shù)據(jù)庫實例中。

在上面的例子中，將 user_action_record_log 按照月份進行拆分就屬于水平分表。

垂直分表

以字段為依據(jù)，按照字段的活躍性，將表中字段拆到不同的表（主表和擴展表）中。

在這種分表策略中：

• 每個表的結構都不一樣；

• 每個表的數(shù)據(jù)也不一樣，擴展表保存主表的一個唯一標識，用于關聯(lián)數(shù)據(jù)；

• 所有表的并集是全量數(shù)據(jù)；

• 所有的數(shù)據(jù)還在同一個數(shù)據(jù)庫實例中。

在上面的例子中，將 order 表分為 order_pri, order_sub 就屬于垂直分表

分表是針對于單個數(shù)據(jù)庫實例還沒到瓶頸的情況下，只是單表因為數(shù)據(jù)量大已經(jīng)達到瓶頸

水平分庫

以字段為依據(jù)，按照一定策略（hash、range 等），將一個表中的數(shù)據(jù)拆分到多個表中。

在這種拆分策略中：

• 每個庫的結構都一樣；

• 每個庫的數(shù)據(jù)都不一樣，沒有交集；

• 所有庫的并集是全量數(shù)據(jù)；

這個是看上去和水平分表很像，只不過是通過策略分到了不同的庫。

垂直分庫

以表為依據(jù)，按照業(yè)務歸屬不同，將不同的表拆分到不同的庫中。

在這種拆分策略中：

• 每個庫的結構都不一樣；

• 每個庫的數(shù)據(jù)也不一樣，沒有交集；

• 所有庫的并集是全量數(shù)據(jù)；

在分庫是一個比較麻煩的操作，因為分庫會導致事務處理麻煩、跨庫聚合數(shù)據(jù)麻煩、無法跨庫 join 問題，在選擇分庫策略的時候一定要考慮實際需要。如果可以分表解決的，那就先嘗試分表，而不是直接分庫。當然如果不需要考慮事務、join 等問題，選什么方案都是可以的。

分區(qū)步驟

1. 預估容量：這里的容量包括當前容量和可能的增長量；

2. 評估分區(qū)個數(shù)：根據(jù)預估的容量來確定分區(qū)的個數(shù)是多少合適；

3. 確定分區(qū)鍵 partition key：在確定分區(qū)鍵的時候，既要考慮能夠均勻的散落到不同的表中，也要考慮適合查詢和修改；

4. 分表規(guī)則：一般可以通過對分區(qū)鍵進行 hash 或者 range 來確定歸屬哪個分區(qū)，也可以直接做個固定情況的分區(qū)規(guī)則，比如上面訂單例子中，直接使用狀態(tài)進行分片；

5. 擴容計劃：不管怎么評估容量和分區(qū)個數(shù)，都有一定的概率再次出現(xiàn)瓶頸，也要考慮一下再次出現(xiàn)瓶頸的時候，如何進行擴容。如果可以停機清洗數(shù)據(jù)的話，這方便也可以不考慮，或者少考慮，對于不能停機清洗數(shù)據(jù)的業(yè)務，需要多考慮一下；

分區(qū)鍵的選取和方法示例

唯一 ID

最簡單的方法是對唯一id 進行 hash 取模計算，比如我們要分 2 個表，就可以直接 hash(id) % 2, 這樣就可以把數(shù)據(jù)分成 2 份。

建議：使用這種取模的方式，推薦將表分為 2 的次方個，方便后續(xù)的擴容

查詢和修改都可以通過 hash 取模的方法查到對應的表或者庫。

擴容方式：

比如計劃分 2 個表，但因為后續(xù)數(shù)量激增，2 個表不在滿足，可以直接將分區(qū)個數(shù)翻倍，變成 4 個表，則現(xiàn)在邏輯為 hash(id) % 4, 從圖中可以看出如果分區(qū)數(shù)是 2 的次方，則每次擴容，只需要對上一次表在進行對應 hash 取模，就能再次平均分配，在重新分配的過程中，冗余的數(shù)據(jù)為 1/2

時間分表

根據(jù)某個時間段來分表也是一種可行的方案，時間分表不存在擴容時需要清洗數(shù)據(jù)的問題，但是需要每隔一段時間增加一個分區(qū)

使用固定的路由表

可以制定一個固定的路由表進行分區(qū)

• 比如手機號可以根據(jù)前 3 位分表，這種每個表最大量為 1 億

• 身份證號可以根據(jù)前 6 位劃分區(qū)域存儲

• 訂單可以根據(jù)不同的狀態(tài)

不管使用何種方式去分庫分表，數(shù)據(jù)如何歸檔、數(shù)據(jù)歸檔后如何保證用戶能查詢到、如何保證一致性等等都需要去考慮解決。

ID 問題

因為將數(shù)據(jù)切分到了不同的表和庫中，MySQL 本身的自增 id 就不能保證唯一性了，這時候需要其他方案來保證。

• UUID

• 數(shù)據(jù)庫自增 ID

• 雪花算法

• 美團 Leaf

• 百度 uid-generator

方案很多，自行參考，保證唯一即可

分布式事務

將數(shù)據(jù)拆分到不同的數(shù)據(jù)庫實例的情況，就會出現(xiàn)分布式事務問題，分布式事務的出現(xiàn)也會影響性能。常見的分布式事務的方案有：

• 數(shù)據(jù)庫本身的 XA 協(xié)議

• TCC 事務

• 2PC/3PC 方案

• 最終一致性性方案

其中可以使用 Alibaba Seata 等

跨節(jié)點查詢問題

join 問題

跨數(shù)據(jù)庫節(jié)點 join 本身就比較麻煩，甚至不支持，可以通過冗余數(shù)據(jù)的方式來減少和避免 join 的情況，或者通過業(yè)務邏輯來進行join 操作而不是數(shù)據(jù)庫 join

排序和分頁

因為數(shù)據(jù)本身分布在不同的節(jié)點上，所有的節(jié)點數(shù)據(jù)才是全集，對數(shù)據(jù)進行排序和分頁也是一個難點問題，數(shù)據(jù)量少的情況下可以進行業(yè)務服務進行內存分頁，數(shù)據(jù)量大的時候就沒辦法使用了。

統(tǒng)計函數(shù)

一些數(shù)據(jù)難以統(tǒng)計，可能需要其他的三方統(tǒng)計方式，或者通過一些分析引擎來做。

• min

• max

• sum

• count

• concat

非分片鍵上的查詢

因為數(shù)據(jù)分區(qū)是根據(jù)分片鍵來區(qū)分的，如果查詢條件中存在分片鍵則很容易的定位到指定的分片，如果查詢條件中不存在分片的鍵，就需要其他方式查詢：

1. 查詢所有的數(shù)據(jù)：這個方案本身就不可取，一是查詢緩慢，二是分區(qū)數(shù)量變化還得更新對應的 SQL

2. 做個映射表：將可能用到的查詢條件單獨在存一個映射表中，對應上分區(qū)，查詢通過這個映射表先查到分區(qū)，在去指定分區(qū)查具體的數(shù)據(jù)，這種方案適用于查詢條件字段只占用總字段的很小一部分的情況下，如果占用一大部分，那映射表本身就會很大。映射表不一定非要存在數(shù)據(jù)庫中，也可以進行混合存儲，比如 MongoDB

數(shù)據(jù)遷移和擴容

上面的例子中也有提到，如果預估的分區(qū)不夠的情況，還要進行擴容操作，方案中也要將這部分考慮進去。

目前分庫分表一些成熟的開源解決方案：

• Apache Shardingsphere

• Alibaba Cobar 僅供參考

• MyCAT（Base Cobar）

• 58同城 Oceanus 僅供參考

• Google Vitess