1 為什么要分庫分表

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

2 垂直拆分（Scale Up 縱向擴展）

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

2.1 垂直分庫

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

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

2.2 垂直分表 

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

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

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

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

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

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

垂直拆分的缺點：

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

3 水平拆分（Scale Out 橫向擴展） 

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

3.1 庫內(nèi)分表

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

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

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

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

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

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

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

3.2.1 HASH（哈希）

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

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

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

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

3.2.2 RANGE（范圍）

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

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ù)對崗位編號進行分區(qū)，共分為4個分區(qū)，

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

3.2.3 LIST（預定義列表）

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

然后通過“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ù)對員工崗位編號進行分區(qū)，共分為4個分區(qū)，編號為46,77,89的對應在分區(qū)P0中，106,125,177類別在分區(qū)P1中，依次類推即可。

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

3.2.4 KEY（鍵值）

類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL 服務器提供其自身的哈希函數(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ū)算法目前使用的比較少，使用服務器提供的哈希函數(shù)有不確定性，對于后期數(shù)據(jù)統(tǒng)計、整理存在會更復雜，所以我們更傾向于使用由我們定義表達式的Hash，大家知道其存在和怎么使用即可。 

3.2.5 Composite（復合模式）

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

3.3 分庫分表

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

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

4 分庫分表存在的問題

4.1 事務問題

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

4.2 跨庫跨表的join問題

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

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

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

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

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

最新評論