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

物理服務(wù)機(jī)的CPU、內(nèi)存、存儲(chǔ)設(shè)備、連接數(shù)等資源有限，某個(gè)時(shí)段大量連接同時(shí)執(zhí)行操作，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)在處理上遇到性能瓶頸。為了解決這個(gè)問(wèn)題，行業(yè)先驅(qū)門(mén)充分發(fā)揚(yáng)了分而治之的思想，對(duì)大庫(kù)表進(jìn)行分割，然后實(shí)施更好的控制和管理，同時(shí)使用多臺(tái)機(jī)器的CPU、內(nèi)存、存儲(chǔ)，提供更好的性能。而分治有兩種實(shí)現(xiàn)方式：垂直拆分和水平拆分。

2 垂直拆分（Scale Up 縱向擴(kuò)展）

垂直拆分分為垂直分庫(kù)和垂直分表，主要按功能模塊拆分，以解決各個(gè)庫(kù)或者各個(gè)表之間的資源競(jìng)爭(zhēng)。比如分為訂單庫(kù)、商品庫(kù)、用戶(hù)庫(kù)...這種方式，多個(gè)數(shù)據(jù)庫(kù)之間的表結(jié)構(gòu)是不同的。

2.1 垂直分庫(kù)

先說(shuō)說(shuō)垂直分庫(kù)。垂直分庫(kù)其實(shí)是一種簡(jiǎn)單邏輯分割。比如我們的數(shù)據(jù)庫(kù)中有商品表Products、還有對(duì)訂單表Orders，還有積分表Scores。接下來(lái)我們就可以創(chuàng)建三個(gè)數(shù)據(jù)庫(kù)，一個(gè)數(shù)據(jù)庫(kù)存放商品，一個(gè)數(shù)據(jù)庫(kù)存放訂單，一個(gè)數(shù)據(jù)庫(kù)存放積分。

垂直分庫(kù)有一個(gè)優(yōu)點(diǎn)，他能夠根據(jù)業(yè)務(wù)場(chǎng)景進(jìn)行孵化，比如某一單一場(chǎng)景只用到某2-3張表，基本上應(yīng)用和數(shù)據(jù)庫(kù)可以拆分出來(lái)做成相應(yīng)的服務(wù)。拆分方式如下圖所示：

2.2 垂直分表 

再來(lái)說(shuō)說(shuō)垂直分表，比較適用于那種字段比較多的表，假設(shè)我們一張表有100個(gè)字段，我們分析了一下當(dāng)前業(yè)務(wù)執(zhí)行的SQL語(yǔ)句，有20個(gè)字段是經(jīng)常使用的，而另外80個(gè)字段使用比較少。

這樣我們就可以把20個(gè)字段放在主表里面，我們?cè)賱?chuàng)建一個(gè)輔助表，存放另外80個(gè)字段。當(dāng)然主表和輔助表都是有主鍵的，他們通過(guò)主鍵進(jìn)行關(guān)聯(lián)合并，就可以組合成100個(gè)字段的表。拆分方式如下圖所示。

除了這種訪問(wèn)頻率的冷熱拆分之外，還可以按照字段類(lèi)型結(jié)構(gòu)來(lái)拆分，比如大文本字段單獨(dú)放在一個(gè)表中，與基礎(chǔ)字段隔離，提高基礎(chǔ)字段的訪問(wèn)效率。

也可以將字段按照功能用途來(lái)拆分，比如采購(gòu)的物料表可以按照基本屬性、銷(xiāo)售屬性、采購(gòu)屬性、生產(chǎn)制造屬性、財(cái)務(wù)會(huì)計(jì)屬性等用途垂直拆分。

總體來(lái)說(shuō)：垂直拆分有以下優(yōu)點(diǎn)：

• 跟隨業(yè)務(wù)進(jìn)行分割，類(lèi)似微服務(wù)的分治理念，方便解耦之后的管理及擴(kuò)展。
• 高并發(fā)的場(chǎng)景下，垂直拆分使用多臺(tái)服務(wù)器的CPU、I/O、內(nèi)存能提升性能，同時(shí)對(duì)單機(jī)數(shù)據(jù)庫(kù)連接數(shù)、一些資源限制也得到了提升，能實(shí)現(xiàn)冷熱數(shù)據(jù)的分離。

垂直拆分的缺點(diǎn)：

• 部分業(yè)務(wù)表無(wú)法join，應(yīng)用層需要很大的改造，只能通過(guò)聚合的方式來(lái)實(shí)現(xiàn)。增加了開(kāi)發(fā)的難度。
• 單表數(shù)據(jù)量膨脹的問(wèn)題依然沒(méi)有得到有效的解決。分布式事務(wù)也是一個(gè)難題。

3 水平拆分（Scale Out 橫向擴(kuò)展） 

水平拆分又分為庫(kù)內(nèi)分表和分庫(kù)分表，來(lái)解決單表中數(shù)據(jù)量增長(zhǎng)出現(xiàn)的壓力，這些數(shù)據(jù)庫(kù)中的表結(jié)構(gòu)完全相同。

3.1 庫(kù)內(nèi)分表

先說(shuō)說(shuō)庫(kù)內(nèi)分表。假設(shè)當(dāng)我們的Orders表達(dá)到了5000萬(wàn)行記錄的時(shí)候，非常影響數(shù)據(jù)庫(kù)的讀寫(xiě)效率，怎么辦呢？

我們可以考慮按照訂單編號(hào)的order_id進(jìn)行rang分區(qū),就是把訂單編號(hào)在1-1000萬(wàn)的放在order1表中，將編號(hào)在1000萬(wàn)-2000萬(wàn)的放在order2中，以此類(lèi)推，每個(gè)表中存放1000萬(wàn)數(shù)據(jù)。

關(guān)于水平分表的時(shí)機(jī)，業(yè)內(nèi)的標(biāo)準(zhǔn)不是很統(tǒng)一，阿里的Java 開(kāi)發(fā)手冊(cè)的標(biāo)準(zhǔn)是當(dāng)單表行數(shù)超過(guò) 500萬(wàn)行或者單表容量超過(guò) 2 GB時(shí)，才推薦進(jìn)行分庫(kù)分表。百度的則是1000 W行的進(jìn)行分表，這個(gè)是百度的DBA經(jīng)過(guò)測(cè)試推算出的結(jié)果。

但是這邊忽略了單表的字段數(shù)和字段類(lèi)型，如果字段數(shù)很多，超過(guò)50列，對(duì)性能影響也是不小的，我們?cè)?jīng)有個(gè)業(yè)務(wù)，表字段是隨著業(yè)務(wù)的增長(zhǎng)而自動(dòng)擴(kuò)增的，到了后期，字段越來(lái)越多，查詢(xún)性能也越來(lái)越慢。

所以個(gè)人覺(jué)得不必拘泥于500W 還是1000W，開(kāi)發(fā)人員在使用過(guò)程中，如果壓測(cè)發(fā)現(xiàn)因?yàn)閿?shù)據(jù)基數(shù)變大而導(dǎo)致執(zhí)行效率慢下來(lái)，就可以開(kāi)始考慮分表了。

3.2 庫(kù)內(nèi)分表的實(shí)現(xiàn)策略

目前在MySql中支持四種表分區(qū)的方式，分別為HASH、RANGE、LIST及KEY，當(dāng)然在其它的類(lèi)型數(shù)據(jù)庫(kù)中，分區(qū)的實(shí)現(xiàn)方式略有不同，但是分區(qū)的思想原理是相同，具體如下：

3.2.1 HASH（哈希）

HASH分區(qū)主要用來(lái)確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布，而在RANGE和LIST分區(qū)中，必須明確指定一個(gè)給定的列值或列值集合應(yīng)該保存在哪個(gè)分區(qū)中，而在HASH分區(qū)中，MySQL自動(dòng)完成這些工作，

你所要做的只是基于將要被哈希的列值指定一個(gè)列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。 示例如下：

1 drop table if EXISTS  `t_userinfo`; 
 2 CREATE TABLE `t_userinfo` (
 3 `id` int(10) unsigned NOT NULL,
 4 `personcode` varchar(20) DEFAULT NULL,
 5 `personname` varchar(100) DEFAULT NULL,
 6 `depcode` varchar(100) DEFAULT NULL,
 7 `depname` varchar(500) DEFAULT NULL,
 8 `gwcode` int(11) DEFAULT NULL,
 9 `gwname` varchar(200) DEFAULT NULL,
10 `gravalue` varchar(20) DEFAULT NULL,
11 `createtime` DateTime NOT NULL
12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
13 PARTITION BY HASH(YEAR(createtime))
14 PARTITIONS 10;

上面的例子，使用HASH函數(shù)對(duì)createtime日期進(jìn)行HASH運(yùn)算，并根據(jù)這個(gè)日期來(lái)分區(qū)數(shù)據(jù)，這里共分為10個(gè)分區(qū)。 

建表語(yǔ)句上添加一個(gè)“PARTITION BY HASH (expr)”子句，其中“expr”是一個(gè)返回整數(shù)的表達(dá)式，它可以是字段類(lèi)型為MySQL 整型的一列的名字，也可以是返回非負(fù)數(shù)的表達(dá)式。

另外，可能需要在后面再添加一個(gè)“PARTITIONS num”子句，其中num 是一個(gè)非負(fù)的整數(shù)，它表示表將要被分割成分區(qū)的數(shù)量。 

3.2.2 RANGE（范圍）

基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給同一個(gè)分區(qū)，這些區(qū)間要連續(xù)且不能相互重疊，使用VALUES LESS THAN操作符來(lái)進(jìn)行定義。示例如下：

1 drop table if EXISTS  `t_userinfo`; 
 2 CREATE TABLE `t_userinfo` (
 3 `id` int(10) unsigned NOT NULL,
 4 `personcode` varchar(20) DEFAULT NULL,
 5 `personname` varchar(100) DEFAULT NULL,
 6 `depcode` varchar(100) DEFAULT NULL,
 7 `depname` varchar(500) DEFAULT NULL,
 8 `gwcode` int(11) DEFAULT NULL,
 9 `gwname` varchar(200) DEFAULT NULL,
10 `gravalue` varchar(20) DEFAULT NULL,
11 `createtime` DateTime NOT NULL
12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
13 PARTITION BY RANGE(gwcode) (
14 PARTITION P0 VALUES LESS THAN(101) ,
15 PARTITION P1 VALUES LESS THAN(201) ,
16 PARTITION P2 VALUES LESS THAN(301) ,
17 PARTITION P3 VALUES LESS THAN MAXVALUE
18 );

上面的示例，使用了范圍RANGE函數(shù)對(duì)崗位編號(hào)進(jìn)行分區(qū)，共分為4個(gè)分區(qū)，

崗位編號(hào)為1~100 的對(duì)應(yīng)在分區(qū)P0中，101~200的編號(hào)在分區(qū)P1中，依次類(lèi)推即可。那么類(lèi)別編號(hào)大于300，可以使用MAXVALUE來(lái)將大于300的數(shù)據(jù)統(tǒng)一存放在分區(qū)P3中即可。 

3.2.3 LIST（預(yù)定義列表）

類(lèi)似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇分區(qū)的。LIST分區(qū)通過(guò)使用“PARTITION BY LIST(expr)”來(lái)實(shí)現(xiàn)，其中“expr” 是某列值或一個(gè)基于某個(gè)列值、并返回一個(gè)整數(shù)值的表達(dá)式，

然后通過(guò)“VALUES IN (value_list)”的方式來(lái)定義每個(gè)分區(qū)，其中“value_list”是一個(gè)通過(guò)逗號(hào)分隔的整數(shù)列表。 示例如下：

1 drop table if EXISTS  `t_userinfo`; 
 2 CREATE TABLE `t_userinfo` (
 3 `id` int(10) unsigned NOT NULL,
 4 `personcode` varchar(20) DEFAULT NULL,
 5 `personname` varchar(100) DEFAULT NULL,
 6 `depcode` varchar(100) DEFAULT NULL,
 7 `depname` varchar(500) DEFAULT NULL,
 8 `gwcode` int(11) DEFAULT NULL,
 9 `gwname` varchar(200) DEFAULT NULL,
10 `gravalue` varchar(20) DEFAULT NULL,
11 `createtime` DateTime NOT NULL
12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
13 PARTITION BY LIST(`gwcode`) (
14 PARTITION P0 VALUES IN (46,77,89) ,
15 PARTITION P1 VALUES IN (106,125,177) ,
16 PARTITION P2 VALUES IN (205,219,289) ,
17 PARTITION P3 VALUES IN (302,317,458,509,610) 
18 );

上面的例子，使用了列表匹配LIST函數(shù)對(duì)員工崗位編號(hào)進(jìn)行分區(qū)，共分為4個(gè)分區(qū)，編號(hào)為46,77,89的對(duì)應(yīng)在分區(qū)P0中，106,125,177類(lèi)別在分區(qū)P1中，依次類(lèi)推即可。

不同于RANGE的是，LIST分區(qū)的數(shù)據(jù)必須匹配列表中的崗位編號(hào)才能進(jìn)行分區(qū)，所以這種方式只是適合比較區(qū)間值確定并少量的情況。 

3.2.4 KEY（鍵值）

類(lèi)似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL 服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。 示例如下：

1 drop table if EXISTS  `t_userinfo`; 
 2 CREATE TABLE `t_userinfo` (
 3 `id` int(10) unsigned NOT NULL,
 4 `personcode` varchar(20) DEFAULT NULL,
 5 `personname` varchar(100) DEFAULT NULL,
 6 `depcode` varchar(100) DEFAULT NULL,
 7 `depname` varchar(500) DEFAULT NULL,
 8 `gwcode` int(11) DEFAULT NULL,
 9 `gwname` varchar(200) DEFAULT NULL,
10 `gravalue` varchar(20) DEFAULT NULL,
11 `createtime` DateTime NOT NULL
12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
13 PARTITION BY KEY(gwcode)
14 PARTITIONS 10;

注意：此種分區(qū)算法目前使用的比較少，使用服務(wù)器提供的哈希函數(shù)有不確定性，對(duì)于后期數(shù)據(jù)統(tǒng)計(jì)、整理存在會(huì)更復(fù)雜，所以我們更傾向于使用由我們定義表達(dá)式的Hash，大家知道其存在和怎么使用即可。 

3.2.5 Composite（復(fù)合模式）

Composite是上面幾種模式的組合使用，比如你在Range的基礎(chǔ)上，再進(jìn)行Hash 哈希分區(qū)。

3.3 分庫(kù)分表

庫(kù)內(nèi)分表解決了單表數(shù)據(jù)量過(guò)大的瓶頸問(wèn)題，但使用還是同一主機(jī)的CPU、IO、內(nèi)存，另外單庫(kù)的連接數(shù)也有限制，并不能完全的降低系統(tǒng)的壓力。

此時(shí)，我們就要考慮另外一種技術(shù)叫分庫(kù)分表。分庫(kù)分表在庫(kù)內(nèi)分表的基礎(chǔ)上，將分的表挪動(dòng)到不同的主機(jī)和數(shù)據(jù)庫(kù)上。可以充分的使用其他主機(jī)的CPU、內(nèi)存和IO資源。 拆分方式進(jìn)一步演進(jìn)到下面：

4 分庫(kù)分表存在的問(wèn)題

4.1 事務(wù)問(wèn)題

在執(zhí)行分庫(kù)分表之后，由于數(shù)據(jù)存儲(chǔ)到了不同的庫(kù)上，數(shù)據(jù)庫(kù)事務(wù)管理出現(xiàn)了困難。如果依賴(lài)數(shù)據(jù)庫(kù)本身的分布式事務(wù)管理功能去執(zhí)行事務(wù)，將付出高昂的性能代價(jià)；如果由應(yīng)用程序去協(xié)助控制，形成程序邏輯上的事務(wù)，又會(huì)造成編程方面的負(fù)擔(dān)。

4.2 跨庫(kù)跨表的join問(wèn)題

在執(zhí)行了分庫(kù)分表之后，難以避免會(huì)將原本邏輯關(guān)聯(lián)性很強(qiáng)的數(shù)據(jù)劃分到不同的表、不同的庫(kù)上，這時(shí)，表的關(guān)聯(lián)操作將受到限制，我們無(wú)法join位于不同分庫(kù)的表，也無(wú)法join分表粒度不同的表，結(jié)果原本一次查詢(xún)能夠完成的業(yè)務(wù)，可能需要多次查詢(xún)才能完成。 

4.3 額外的數(shù)據(jù)管理負(fù)擔(dān)和數(shù)據(jù)運(yùn)算壓力

額外的數(shù)據(jù)管理負(fù)擔(dān)，最顯而易見(jiàn)的就是數(shù)據(jù)的定位問(wèn)題和數(shù)據(jù)的增刪改查的重復(fù)執(zhí)行問(wèn)題，這些都可以通過(guò)應(yīng)用程序解決，但必然引起額外的邏輯運(yùn)算，例如，對(duì)于一個(gè)記錄用戶(hù)成績(jī)的用戶(hù)數(shù)據(jù)表userTable，業(yè)務(wù)要求查出成績(jī)最好的100位，在進(jìn)行分表之前，

只需一個(gè)order by語(yǔ)句就可以搞定，但是在進(jìn)行分表之后，將需要n個(gè)order by語(yǔ)句，分別查出每一個(gè)分表的前100名用戶(hù)數(shù)據(jù)，然后再對(duì)這些數(shù)據(jù)進(jìn)行合并計(jì)算，才能得出結(jié)果。

以上就是Mysql數(shù)據(jù)庫(kù)分庫(kù)分表全面瓦解的詳細(xì)內(nèi)容，更多關(guān)于Mysql分庫(kù)分表的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論