在業務體量還不是很大的時候，單庫單表即可滿足業務上的需求，隨著業務體量的增大，無論是CPU還是IO都可能出現性能瓶頸，由于大量連接達到單庫上，導致單庫無法承載這些活躍的連接數，這使得我們從Java進程的角度看來就是數據庫連接很少或者沒有連接可用，最終出現并發、吞吐全面下降甚至是系統崩潰。

所以，筆者整理了這篇分庫分表的文章來逐一分析拆解這些問題。

一、關于一些分庫分表性能指標的補充

這里我們補充一下IO瓶頸和CPU瓶頸，關于IO瓶頸，即是數據表中存在大量熱點數據，大量的請求都需要到數據庫進行查詢，因為大量的IO請求進來導致數據庫連接數不足導致性能瓶頸這就是所謂的IO瓶頸，針對這種情況我們可以考慮根據熱點數據類型采取垂直分表或者分庫的方式解決。

就像下面這種情況，因為有大量請求專門查詢用戶名和地址，所以我們采用垂直分表的方式將熱點數據拆出來獨立維護，解決原有訂單表過度冗余的字段使得熱點數據體量直接減小，再通過內存中間件加以緩存緩解數據庫壓力解決IO性能瓶頸：

假如熱點數據在垂直分表后，數據量可以減小，那么我們就采取垂直分表結合緩存中間件的方式解決。如果熱點數據無法通過垂直分表或者說通過垂直分表后數據規模仍然很大的話，那么我們就必須通過水平分庫解決了。

而CPU瓶頸則是因為表關聯join或者各種運算例如group by，order by等導致查詢效率低下，這種情況如果無法通過索引或者業務代碼層面進行計算的方式解決的話，那么就只能通過業務層面并結合水平分表縮小數據體量提升數據聚合效率。

二、分庫分表基本概念介紹

1. 為什么不使用MySQL分區

可能也會有讀者問到，為什么不采用MySQL分區表呢？ 這里我們需要了解一下MySQL分區表的工作原理，它會將分區的數據表在在物理層面進行分區，但在邏輯上還是一張表，這使得用戶在查詢的時候對分區是沒有感知的。所以說使用MySQL分區會帶來以下好處：

• 在一定的數據量情況下，使用分區鍵進行查詢可以快速定位數據。
• 因為分區會在物理層面進行切分，所以對于需要定期刪除分區數據的場景下，MySQL分區是非常方便管理的。

而同樣的它也存在如下缺點：

• 無法創建外鍵，當然這對于現代開發規范來說這一點沒有太大影響。
• 并發量上來了依然存在IO瓶頸。
• 查詢時必須帶上分區鍵，否則會對所有分區進行掃描。
• 對分區查詢時的優化都是由MySQL優化器自定義，對用戶來說是黑盒可控性較差，不如分庫分表靈活。

2. 分庫分表的基本概念

本質上分庫分表是3個概念，這里我們都是從水平拓展的維度討論問題，本質上分庫分表中的概念分別對應：

(1) 分庫：以8C16G的MySQL實例為例，經過業界壓測普遍認為其TPS大約在2500~3000左右，所以當系統的并發量超過這個時候，就很可能出現連接數不足導致服務癱瘓的問題，所以業界就有了水平分庫增加更多的數據庫連接的同時還能分散系統請求從而提升系統并發度：

(2) 分表：當單表體量超過一定閾值之后，無論數據檢索還是修改操作對應的耗時的都會增加，最經典的就是深分頁問題，此時我們就可以通過水平分表來縮小單表數據體量以提升數據檢索速度。

(3) 分庫分表：如果系統是典型高并發、海量數據的場景，也就是上述兩種情況的綜合體，那么我們就需要通過分庫分表這種綜合方案來解決問題了。

三、分庫分表的兩種維度

1. 垂直分庫或者水平分庫

垂直分庫是解耦服務間依賴的常見手段，在傳統單體架構時，我們的所有的數據表都在一個數據庫中。隨著業務體量的增加，為了針對業務進行優化，我們可以將不同業務進行圈表拆分到不同庫中，這就是垂直分庫，通過垂直分庫進行針對性優化，從而針對這些業務孵化出一個業務模式，達到服務化。

因為高并發導致單點數據庫無法承載這些連接，所以我們將相同結構的數據表放到不同的數據庫，然后用戶通過分庫算法定位到這些數據進行操作，以減輕數據庫的io和cpu壓力，這就是典型的水平分庫。

2. 垂直分表和水平分表

關于常見的分表技術有垂直分表和水平分表，其中垂直分表主要是優化查詢的一種常見手段，從物理角度來說，它就是將一張表垂直進行拆分以實現確保將熱點數據與非熱點數據進行隔離，確保每次進行查詢時緩存行可以盡可能緩存更多的字段，避免到磁盤進行隨機IO導致的IO瓶頸。

水平分表則是為了解決大數據存儲和查詢問題，從物理角度來說它就是將大數據表橫切一刀分為無數張小表，然后所有的操作都需要針對體積更小的小表進行操作，從而減小單體查詢檢索的IO量，提升檢索效率：

四、常見的分庫分表方案

1. 簡介常見的集中分庫分表設計方案

為保證分庫分表后數據能夠被準確的定位并查詢到，分表的策略也是很重要的，這里筆者列出幾種比較常見的分表方案:

• range范圍發表法
• hash取模法
• range+hash法

2. range法分表

rang法實現比較簡單，就是針對每個表都指定一個id范圍，假設我們現在有3張分表，分表1存儲1-500w的數據，分表2存儲500w-1000w的數據，分表3存儲1000w-1500w的數據。因為每個表范圍是固定的，那么我們在進行數據查詢時就很方便了，例如我們想查詢id為1500的訂單詳情，直接通過id%500w即定位到分表0。

這種方案在數據查詢比較均衡的情況下表現良好，遇到熱點問題就比較棘手了，例如雙十一淘寶訂單都集中在分表3和分表4，這就會導致這兩張表單位時間內承載大量查詢和操作請求，而其他表卻無法去負擔這些壓力，這也就是我們常說的數據偏斜問題。

3. hash取模法

于是就有一種均攤數據的分表算法即hash取模法，這種算法要求我們盡可能在功能實現前，評估將來的數據量，例如就是5000w，那么我們就設置10張表，每張表500w。后續進行插入操作時我們只需根據自增id值進行取模運算然后均攤存儲到不同表即可。例如：我們現在有一條數據得到id為1000，通過1000%10=0，由此可知這條數據就可以存到tb_0表中.

hash分表算法雖然可以均攤數據存儲，避免數據熱點問題，但是也存在一定的缺點，即查詢問題，假如我們現在只有3張分表，id算法為id%3，一旦數據體量增加，我們的分表需要增加到6張，那么規則就需要改變了，很明顯這種改動量存在的風險是非常大的。

4. range+hash法

由上可知range法可以很好的進行擴容，而hash法可以完美的均攤存儲。所以我們更建議使用range+hash法進行分庫分表，通過range法決定當前存儲的區域，再結合hash取模法指定這個區域中具體的一張表。

例如: 舉個例子，筆者現在根據業務需求對數據表進行拆分得到6張分表：

• 每張表存儲1000w條數據。
• 1個庫作為一個range范圍，id自增。
• 1個range包含兩張張分表，總和2000w數據。

對應規則得到的表名和含義如下：

-- tb_0前綴 存儲0-2000w的數據
tb_0_0
tb_0_1

-- tb_1前綴 存儲2000-4000w的數據
tb_1_0
tb_1_1

-- tb_2前綴 存儲4000w-6000w的數據
tb_2_0
tb_2_1

根據我們上文所說，通過range決定區域，假設我們現在數據id為600w，根據上表前綴可知我們要存儲的數據表為tb_0開頭的表，因為tb_0開頭的表有兩張，由此我們再用hash法進行取模，即600w%2=0，由此可知數據最終要存到tb_0_0表。 我們再回過頭說說擴容問題，因為我們通過range法決定存儲的分表area，所以假設需要增加分表，我們也只需定義一個新的range范圍和這個范圍的分表算法即可。

就比如，我們現在就需要增加兩張分表，那么我們可以直接指定這兩張分表區域為3，的id范圍是6000w到8000w，因為這個區域還是兩張分表，所以算法也是hash%2，簡單配置一下即可實現擴容，無需對代碼進行改造，可以說這套方案相較于前兩者會更出色一些。

五、分庫分表涉及的一些涉及問題

1. 分庫分表時如何選擇分表字段（推薦id）

整體來說分庫分表的選用的字段可以有很多種的方案例如：

• 按照用戶id
• 按照時間
• 按照地區

只不過在選擇的時候一定要結合業務場景進行設計同時也要考慮到下面這兩個問題：

• 如何保證數據盡可能均勻分布到庫表，同時保證保證檢索效率？
• 確定字段后，如何在數據檢索前明確知曉數據存在的庫表？

假設我們的查詢都是按照時間維度進行查詢，那么我們就使用數據表中帶有時間性質的字段作為分表字段，例如我們現在的訂單表order分表算法是按月進行分表，在1月創建的數據存放至tb_1，在2月創建的數據存放至tb_2，那么我們雪花id算法作為分布式id生成工具，其原因如下：

• 雪花算法自增有序，不會導致大量頁分裂而導致檢索性能下降問題。
• 雪花算法有41bit的空間記錄當前時間戳，所以按照我們的分表算法，可以直接通過生成的id定位到日期從而確定庫表。

如下所示，我們1.25創建的數據得到的訂單id是1882967322877497344，基于高41bit得到時間是1月份所以存入分表1，后續查詢時，我們只需要知道對應訂單的id即可定位到分表從而利用主鍵索引檢索到數據：

2. 分庫分表全局ID生成方案

分庫分表勢必涉及一些關于全局庫表id的設計方案，感興趣的讀者可以參考讀者這篇文章：《來聊聊大廠常用的分布式 ID 生成方案》

3. 數據偏斜問題和解決方案

數據偏斜即按照現有分庫分表算法出現了某份庫表數據遠遠大于其他表數據，進而導致：

• 性能瓶頸：因為數據偏斜導致數據分布不均勻，對于分表后的性能表現和分表前并沒有很大的提升
• 資源利用不均勻
• 查詢效率低下

數據偏斜問題的根因大部分是分表算法設計不好所導致，例如上面提到的range分表法無法針對業務高峰期的id段進行數據均攤，針對該問題我們建議從以下幾個角度考慮并選用合適的方案：

• 如果沒有特定的范圍查詢或者分頁查詢等需要，僅僅針對特定幾條數據的檢索，我們可以將分表算法改為hash算法結合取模運算均勻分布數據。
• 如果需要進行特定日期等范圍查詢的要求，建議在特殊月份做特殊的分表算法，并針對該月份的分表規則進行特殊處理，例如11月份訂單是平時的3倍，我們就可以在該月份多部署幾個庫源和服務，針對該每個服務都有各自的workerId對應一個庫源，如下圖一個java-service對應一個tb_11_x的庫，通過負載均衡算法將訂單請求打到不同的服務上以保證數據均勻的落到不同的庫表中。

4. 如何解決分庫分表后的join問題

水平拓展后可能會導致庫表發布到不同的MySQL實例上，這使得原有的單數據源關聯查詢變為多庫源關聯：

實際上解決該問題的辦法有如下幾種:

• 應用層進行關聯，即應用層面分別查詢兩張表然后將數據關聯。
• 通過數據庫中間件例如shardingsphere等工具實現，不過shardingsphere的聯邦查詢還不是很穩定，慎用。
• 將需要關聯查詢的數據直接冗余到分表上。
• 通過es等搜索引擎統一結構化存儲提供外部檢索查詢。

5. 非partition key查詢問題(讀擴散問題)

問題說明： 進行分表后，對于非partition key的查詢就由為的復雜，因為非partition key和partition key沒有任何關聯如果沒有采取任何措施的話，查詢效率就會十分低下。最簡單的例子就是上文600w那條數據，他記錄著一個用戶的個人信息，假如我們希望通過用戶名name進行查詢，又該如何定位到這條數據呢？很明顯在沒有任何措施的情況下，只能通過逐表遍歷查詢解決了。

解決方案：

(1) 映射法：對此我們提出第一種解決方案——映射法，即通過建立一張中間表將partition key和非partition key進行關聯，以上面的例子，我們想通過name進行查詢時，可直接通過映射表帶入對應的name，從而得到對應的id，進而根據id得到對應的表即進行查詢了。

映射算法也存在一定的缺陷，其一為了查詢要同時維護兩套表，并且普通索引更新時對應的映射表也得更新，而且一旦數據量逐漸增大時，可能還需要對映射表進行水平拆分，再一次增加的業務實現的復雜度。

(2) Elasticsearch：上述的映射表起始就是一種倒排索引的思想，而ES天生就是做這種事情的，針對當前問題，我們直接集成ES，通過開源工具canal監聽MySQL的binlog拿到日志變更，將數據采集到ES中，通過ES近乎實時查詢能力即可完美解決上述問題。

(3) 最終方案：這些做法要么會增加維護的困難和復雜度，亦或者需要增加新的中間件，還需要為了考慮可靠性增加更多的硬件資源。所以，如果業務允許的情況下，針對這種大數據存儲，我們更建議直接采用TIDB進行數據存儲，它是成熟的分布式數據存儲數據庫，它通過引入range的概念對數據表進行分片，有點類似于range范圍分表，且支持普通索引分片類似倒排索引。且其語法和MySQL幾乎一樣，市面也有很多工具可以輔助完成數據遷移，如果項目允許的話，很明顯這套數據庫是最干凈利落的解決方案了。

6. 分庫分表擴容問題

(1) 問題簡介：

因為各種原有我們需要對舊有數據表進行擴容，對此數據遷移就是一個很麻煩的問題，有沒有什么比較安全且易實現的方案呢？這里筆者為大家推薦兩種比較常見的解決方案。

(2) 解決方案：

升級從庫：先說說升級從庫法，這種方式就是通過升級從庫為主庫的方式實現數據遷移再改造hash的遷移方式。 舉個例子，假設我們現在有兩個分庫，每個庫中有一張分表，對應的分庫分表算法即id%2得到庫索引，然后將數據存入對應分庫的分表中，例如我們現在要存儲一個id為600w的數據，通過算法得到值為0，那么這條數據就存入分庫0的tb表，對應的我們的從庫也跟隨db0做數據同步。

當現有主庫數據已達到一定體量導致查詢性能下降，我們可直接將各自的從庫升級為主庫，這是第一步。

完成升級從庫為主庫之后，db0對應的從庫變為db2，此時這兩個數據的數據表是重復的，因為我們將分表算法修改為id%4，所以我們需要基于這個算法清除冗余數據，即主庫0刪除id%4=2(這些是升級為主庫的db2數據)，db1刪除id%4=3(這個是升級為主庫的db3的數據)，其余兩個從庫同理，自此完成算法和數據遷移的升級。

雙寫擴容：雙寫擴容是現如今比較常見的方案，步驟為:

• 設計一套全新的算法的分庫分表將新的數據插入到新表中。
• 通過同步雙寫將新數據插入新老兩庫。
• 通過異步的方式查詢老庫的數據全部寫到新表中。
• 完成遷移工作后以老庫為準核對數據，核對結束后配置關閉雙寫，后續的數據都寫入新庫。

這種方案相較于前者更加穩妥，也是筆者較為推薦的一種解決方案。

7. 分頁查詢

將單表進行水平維度的分庫分表之后所導致的庫源不一致，傳統的limit查詢就無法針對整個分布式維度的分頁，此時我們不得不借助一些第三方工具類將庫源抽象成一個維度進行實現分表查詢，我們以sharding-jdbc為例，它的做法就說基于當前查詢的頁數n，到所有庫源中查詢前n頁的數據并聚合，將分布式庫源檢索結果聚合成一個維度，然后進行排序從而得到實際上的第二頁的數據并返回。

例如，我們的分庫分表希望查到第二頁的數據，按照sharding-jdbc的做法，它就會將所有庫表的前2頁的數據查出來，然后進行歸并排序得到一個完整維度的前2頁的數據，最后再篩選出第二頁數據返回給用戶：

但是這種做法也存在一個指明的缺陷，即深分頁數據的檢索，按照這張方案的做法，假設我們查詢100w頁的數據10條，那么我們就需要歸并n表*100w頁*10條的數據進行歸并排序，這意味著我們的程序的內存很大概率會被打穿。

針對sharding-jdbc，感興趣的讀者可以參考筆者這篇文章，這里面針對跨庫表分頁查詢有著相對詳細的介紹：

本質上，跨庫表分頁查詢就是因為散列的數據缺少一個全局視角，針對該問題業界也有一個在業務和性能上相對折中的方案——二次查詢法，下面筆者就以一個單表進行水平分庫分表后的查詢為例演示一下這套方案。

默認情況下，在單庫單表的情況下，我們查詢第二頁的數據4條對應的SQL為：

select * from tb offset 4 limit 4

對應的查詢結果如下圖所示，即id在5~8這個區間的數據：

在進行水平拆分后，得到兩張分表如下所示，接下來筆者就演示一下如何基于二次查詢實現相對簡單且高效的數據檢索：

我們需要對這條SQL進行改造以保證后續步驟能夠準確獲取全局視角，首先我們假設分表散列均勻，所以均攤一個offset即可兩張表對應的SQL為：

select * from tb_1 offset 2 limit 4
select * from tb_2 offset 2 limit 4

于是我們就得到了下面這張圖中綠色區間的數據：

基于上述檢索到的數據進行排序，我們得到id的最小值為4，基于這個最小值我們進行第二次查詢，對應的查詢采用范圍查詢的方式以排序的最小值作為起點，當前表的最大值作為終點：

select * from tb_1 where id between 4 and  12
select * from tb_2 where id  between 4 and 11

可以看到分表1數據區間不變，分表2多了一條數據5，此時我們就可以基于這份樣本得到數值4在全局視角的offset值：

• tb_1中的數值4是offset 2即偏移2得來的數據，這意味著小于4的數值有兩個。
• tb_2中的二次查詢后找到大于等于4的區間是通過offset 1得來的，這意味著小于4的數值只有1個。

由此可得數值在全局視角是offset 2+1即offset 3的結果。

基于此結果可知，我們還需要偏移一條數據即可完成offset 4的偏移，因為數值4在全局是offset 3的結果，所以跳過數值4就可以實現offset 4，于是我們可知數值5開始之后4個元素就是全局視角的第二頁的數值結果，由此查詢出5~8，二次查詢法完成：

可以看到，二次查詢大體步驟為：

• 均衡偏移獲取候選數據。
• 獲得最小數據作為二次查詢檢索范圍。
• 基于二次查詢結果獲得最小值在全局的偏移量。
• 將二次查詢結果排序結合最小值在全局視角的偏移量得出最終得出分表后的分頁結果。

這種做法無論是在性能還是實現復雜度都做了較好的折中，算是比較不錯的解決方案。

8. 分布式事務

因為水平拓展使得庫源可能分布在不同的服務器上，所以系統在進行多表操作的時候無法保證數據操作的ACID，此時我們就必須借助一個第三方工具來統一管理分布式數據源，常見的方案又seata或者rocketMQ。

我們就以seata為例了解一下主流的分布式事務解決思路：

• seata首先會針對分布式庫源要操作的數據進行前置鏡像備份。
• 協調分布式庫源執行本地事務。
• 基于分布式庫源事務結果判斷事務是否提交。
• 如果某個庫源事務失敗，則通知其他庫源一并回滾，反之統一提交。

可以看出針對分庫分表后的分布式事務本質上就是通過第三方的工具在邏輯上的統一協調來保證分布式事務的ACID：

9. 在分庫分表后的模糊查詢

針對大庫表進行分庫分表分散壓力之后，針對下面這種非前綴匹配還是顯得有些力不從心(索引失效)：

select * from tb where name like '%ming%'

對于此問題最好的辦法就是專業事情讓專業的工具解決，我們完全可以通過bin.log訂閱分庫分表數據將其提交到elasticSearch中，通過其強大的自然語言處理的分詞器和倒排索引這種天生為數據檢索而生的設計理念來解決模糊搜索問題：

10. 分庫分表如何進行庫表資源評估

• 確定數據表體量以及TPS
• 針對TPS進行分庫
• 基于分庫后得到單庫的數據體量進行分表

例如我們現在TPS為6000，每日數據體量在2e,按照上述步驟的推算過程為：

• 按照先常見的硬件配置對MySQL實例(8C32G)的壓測，單庫TPS基本在2000~3000，按照我們業務體量TPS需求為6000，大體需要3個庫，當然我們也可以適當冗余一個庫預防流量突增，所以最終我們分配4個庫。
• 平均之后單庫數據差不多在7000w以內(2e/3)，按照業界標準單條數據1k的情況下單表最好在2000w以內，所以我們單庫標準分3張表，還是同樣道理冗余1張預防突增的數據，也可以不用，因為我們已經適當冗余了一個數據庫。

基于4庫3表指定分庫分表方案并完成業務落地：