1 為什么要分庫分表

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

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

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

2.1 垂直分庫

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

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

2.2 垂直分表 

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

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

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

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

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

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

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

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

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

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

3.1 庫內(nèi)分表

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

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

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

但是這邊忽略了單表的字段數(shù)和字段類型，如果字段數(shù)很多，超過50列，對性能影響也是不小的，我們曾經(jīng)有個業(yè)務(wù)，表字段是隨著業(yè)務(wù)的增長而自動擴(kuò)增的，到了后期，字段越來越多，查詢性能也越來越慢。

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

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

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

3.2.1 HASH（哈希）

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

你所要做的只是基于將要被哈希的列值指定一個列值或表達(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ù)對createtime日期進(jìn)行HASH運(yùn)算，并根據(jù)這個日期來分區(qū)數(shù)據(jù)，這里共分為10個分區(qū)。 

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

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

3.2.2 RANGE（范圍）

基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給同一個分區(qū)，這些區(qū)間要連續(xù)且不能相互重疊，使用VALUES LESS THAN操作符來進(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ù)對崗位編號進(jìn)行分區(qū)，共分為4個分區(qū)，

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

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

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

然后通過“VALUES IN (value_list)”的方式來定義每個分區(qū)，其中“value_list”是一個通過逗號分隔的整數(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ù)對員工崗位編號進(jìn)行分區(qū)，共分為4個分區(qū)，編號為46,77,89的對應(yīng)在分區(qū)P0中，106,125,177類別在分區(qū)P1中，依次類推即可。

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

3.2.4 KEY（鍵值）

類似于按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ù)有不確定性，對于后期數(shù)據(jù)統(tǒng)計(jì)、整理存在會更復(fù)雜，所以我們更傾向于使用由我們定義表達(dá)式的Hash，大家知道其存在和怎么使用即可。 

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

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

3.3 分庫分表

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

此時，我們就要考慮另外一種技術(shù)叫分庫分表。分庫分表在庫內(nèi)分表的基礎(chǔ)上，將分的表挪動到不同的主機(jī)和數(shù)據(jù)庫上?？梢猿浞值氖褂闷渌鳈C(jī)的CPU、內(nèi)存和IO資源。 拆分方式進(jìn)一步演進(jìn)到下面：

4 分庫分表存在的問題

4.1 事務(wù)問題

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

4.2 跨庫跨表的join問題

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

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

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

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

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

最新評論