面試官：給我講一下分庫分表方案

作者：尜尜人物
鏈接：https://www.cnblogs.com/littlecharacter/p/9342129.html

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

1疆瑰、IO瓶頸

2陵霉、CPU瓶頸

二貌夕、分庫分表

1、水平分庫

2虏等、水平分表

3弄唧、垂直分庫

4、垂直分表

三霍衫、分庫分表工具

四候引、分庫分表步驟

五、分庫分表問題

1慕淡、非partition key的查詢問題（水平分庫分表背伴，拆分策略為常用的hash法）

2、非partition key跨庫跨表分頁查詢問題（水平分庫分表峰髓，拆分策略為常用的hash法）

3傻寂、擴容問題（水平分庫分表，拆分策略為常用的hash法）

六携兵、分庫分表總結(jié)

七疾掰、分庫分表示例

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

不管是IO瓶頸徐紧，還是CPU瓶頸静檬，最終都會導(dǎo)致數(shù)據(jù)庫的活躍連接數(shù)增加，進而逼近甚至達到數(shù)據(jù)庫可承載活躍連接數(shù)的閾值并级。在業(yè)務(wù)Service來看就是拂檩，可用數(shù)據(jù)庫連接少甚至無連接可用。接下來就可以想象了吧（并發(fā)量嘲碧、吞吐量稻励、崩潰）。

1愈涩、IO瓶頸

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

第二種：網(wǎng)絡(luò)IO瓶頸毁腿，請求的數(shù)據(jù)太多辑奈，網(wǎng)絡(luò)帶寬不夠 ->** 分庫苛茂。**

2、CPU瓶頸

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

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

二抢埋、分庫分表

1弹灭、水平分庫

image

1、概念：以字段為依據(jù)揪垄，按照一定策略（hash穷吮、range等），將一個庫中的數(shù)據(jù)拆分到多個庫中饥努。

2捡鱼、結(jié)果：

每個庫的結(jié)構(gòu)都一樣；
每個庫的數(shù)據(jù)都不一樣酷愧，沒有交集驾诈；
所有庫的并集是全量數(shù)據(jù)；

3溶浴、場景：系統(tǒng)絕對并發(fā)量上來了乍迄，分表難以根本上解決問題，并且還沒有明顯的業(yè)務(wù)歸屬來垂直分庫士败。

4就乓、分析：庫多了，io和cpu的壓力自然可以成倍緩解拱烁。

2、水平分表

image

1噩翠、概念：以字段為依據(jù)戏自，按照一定策略（hash、range等）伤锚，將一個表中的數(shù)據(jù)拆分到多個表中擅笔。

2志衣、結(jié)果：

每個表的結(jié)構(gòu)都一樣；
每個表的數(shù)據(jù)都不一樣猛们，沒有交集念脯；
所有表的并集是全量數(shù)據(jù)；

3弯淘、場景：系統(tǒng)絕對并發(fā)量并沒有上來绿店，只是單表的數(shù)據(jù)量太多，影響了SQL效率庐橙，加重了CPU負(fù)擔(dān)假勿，以至于成為瓶頸。

4态鳖、分析：表的數(shù)據(jù)量少了转培，單次SQL執(zhí)行效率高，自然減輕了CPU的負(fù)擔(dān)浆竭。

3浸须、垂直分庫

image

1、概念：以表為依據(jù)邦泄，按照業(yè)務(wù)歸屬不同删窒，將不同的表拆分到不同的庫中。

2虎韵、結(jié)果：

每個庫的結(jié)構(gòu)都不一樣易稠；
每個庫的數(shù)據(jù)也不一樣，沒有交集包蓝；
所有庫的并集是全量數(shù)據(jù)驶社；

3、場景：系統(tǒng)絕對并發(fā)量上來了测萎，并且可以抽象出單獨的業(yè)務(wù)模塊亡电。

4、分析：到這一步硅瞧，基本上就可以服務(wù)化了份乒。例如，隨著業(yè)務(wù)的發(fā)展一些公用的配置表腕唧、字典表等越來越多或辖，這時可以將這些表拆到單獨的庫中，甚至可以服務(wù)化枣接。再有颂暇，隨著業(yè)務(wù)的發(fā)展孵化出了一套業(yè)務(wù)模式，這時可以將相關(guān)的表拆到單獨的庫中但惶，甚至可以服務(wù)化耳鸯。

4湿蛔、垂直分表

image

1、概念：以字段為依據(jù)县爬，按照字段的活躍性溉卓，將表中字段拆到不同的表（主表和擴展表）中画恰。

2荣回、結(jié)果：

每個表的結(jié)構(gòu)都不一樣立砸；
每個表的數(shù)據(jù)也不一樣，一般來說纲缓，每個表的字段至少有一列交集卷拘，一般是主鍵，用于關(guān)聯(lián)數(shù)據(jù)祝高；
所有表的并集是全量數(shù)據(jù)栗弟；

3、場景：系統(tǒng)絕對并發(fā)量并沒有上來工闺，表的記錄并不多乍赫，但是字段多，并且熱點數(shù)據(jù)和非熱點數(shù)據(jù)在一起陆蟆，單行數(shù)據(jù)所需的存儲空間較大雷厂。以至于數(shù)據(jù)庫緩存的數(shù)據(jù)行減少，查詢時會去讀磁盤數(shù)據(jù)產(chǎn)生大量的隨機讀IO叠殷，產(chǎn)生IO瓶頸改鲫。

4、分析：可以用列表頁和詳情頁來幫助理解林束。垂直分表的拆分原則是將熱點數(shù)據(jù)（可能會冗余經(jīng)常一起查詢的數(shù)據(jù)）放在一起作為主表像棘，非熱點數(shù)據(jù)放在一起作為擴展表。這樣更多的熱點數(shù)據(jù)就能被緩存下來壶冒，進而減少了隨機讀IO缕题。拆了之后，要想獲得全部數(shù)據(jù)就需要關(guān)聯(lián)兩個表來取數(shù)據(jù)胖腾。但記住烟零，千萬別用join，因為join不僅會增加CPU負(fù)擔(dān)并且會講兩個表耦合在一起（必須在一個數(shù)據(jù)庫實例上）咸作。關(guān)聯(lián)數(shù)據(jù)锨阿，應(yīng)該在業(yè)務(wù)Service層做文章，分別獲取主表和擴展表數(shù)據(jù)然后用關(guān)聯(lián)字段關(guān)聯(lián)得到全部數(shù)據(jù)记罚。

三墅诡、分庫分表工具

1、sharding-sphere：jar毫胜，前身是sharding-jdbc书斜；

2、TDDL：jar酵使，Taobao Distribute Data Layer荐吉；

3、Mycat：中間件口渔。

注：工具的利弊样屠，請自行調(diào)研，官網(wǎng)和社區(qū)優(yōu)先缺脉。

四痪欲、分庫分表步驟

根據(jù)容量（當(dāng)前容量和增長量）評估分庫或分表個數(shù) -> 選key（均勻）-> 分表規(guī)則（hash或range等）-> 執(zhí)行（一般雙寫）-> 擴容問題（盡量減少數(shù)據(jù)的移動）。

五攻礼、分庫分表問題

1业踢、非partition key的查詢問題（水平分庫分表，拆分策略為常用的hash法）

1礁扮、端上除了partition key只有一個非partition key作為條件查詢

映射法

image

基因法

image

注：寫入時知举，基因法生成user_id，如圖太伊。關(guān)于xbit基因雇锡，例如要分8張表，23=8僚焦，故x取3锰提，即3bit基因。根據(jù)user_id查詢時可直接取模路由到對應(yīng)的分庫或分表芳悲。根據(jù)user_name查詢時立肘，先通過user_name_code生成函數(shù)生成user_name_code再對其取模路由到對應(yīng)的分庫或分表。id生成常用snowflake算法芭概。

2赛不、端上除了partition key不止一個非partition key作為條件查詢