Mysql數據庫分庫分表全面瓦解

Posted on by

1 為什麼要分庫分表

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

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

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

2.1 垂直分庫

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

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

2.2 垂直分表 

再來說說垂直分表，比較適用於那種字段比較多的表，假設我們一張表有100個字段，我們分析瞭一下當前業務執行的SQL語句，有20個字段是經常使用的，而另外80個字段使用比較少。

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

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

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

總體來說：垂直拆分有以下優點：

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

垂直拆分的缺點：

  • 部分業務表無法join，應用層需要很大的改造，隻能通過聚合的方式來實現。增加瞭開發的難度。
  • 單表數據量膨脹的問題依然沒有得到有效的解決。分佈式事務也是一個難題。

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

水平拆分又分為庫內分表和分庫分表，來解決單表中數據量增長出現的壓力，這些數據庫中的表結構完全相同。

3.1 庫內分表

先說說庫內分表。假設當我們的Orders表達到瞭5000萬行記錄的時候，非常影響數據庫的讀寫效率，怎麼辦呢？

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

關於水平分表的時機，業內的標準不是很統一，阿裡的Java 開發手冊的標準是當單表行數超過 500萬行或者單表容量超過 2 GB時，才推薦進行分庫分表。百度的則是1000 W行的進行分表，這個是百度的DBA經過測試推算出的結果。

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

所以個人覺得不必拘泥於500W 還是1000W，開發人員在使用過程中，如果壓測發現因為數據基數變大而導致執行效率慢下來，就可以開始考慮分表瞭。

3.2 庫內分表的實現策略

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

3.2.1 HASH（哈希）

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

你所要做的隻是基於將要被哈希的列值指定一個列值或表達式，以及指定被分區的表將要被分割成的分區數量。 示例如下：

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函數對createtime日期進行HASH運算，並根據這個日期來分區數據，這裡共分為10個分區。 

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

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

3.2.2 RANGE（范圍）

基於屬於一個給定連續區間的列值，把多行分配給同一個分區，這些區間要連續且不能相互重疊，使用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函數對崗位編號進行分區，共分為4個分區，

崗位編號為1~100 的對應在分區P0中，101~200的編號在分區P1中，依次類推即可。那麼類別編號大於300，可以使用MAXVALUE來將大於300的數據統一存放在分區P3中即可。 

3.2.3 LIST（預定義列表）

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

然後通過“VALUES IN (value_list)”的方式來定義每個分區，其中“value_list”是一個通過逗號分隔的整數列表。 示例如下：

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函數對員工崗位編號進行分區，共分為4個分區，編號為46,77,89的對應在分區P0中，106,125,177類別在分區P1中，依次類推即可。

不同於RANGE的是，LIST分區的數據必須匹配列表中的崗位編號才能進行分區，所以這種方式隻是適合比較區間值確定並少量的情況。 

3.2.4 KEY（鍵值）

類似於按HASH分區，區別在於KEY分區隻支持計算一列或多列，且MySQL 服務器提供其自身的哈希函數。必須有一列或多列包含整數值。 示例如下：

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;

註意：此種分區算法目前使用的比較少，使用服務器提供的哈希函數有不確定性，對於後期數據統計、整理存在會更復雜，所以我們更傾向於使用由我們定義表達式的Hash，大傢知道其存在和怎麼使用即可。 

3.2.5 Composite（復合模式）

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

3.3 分庫分表

庫內分表解決瞭單表數據量過大的瓶頸問題，但使用還是同一主機的CPU、IO、內存，另外單庫的連接數也有限制，並不能完全的降低系統的壓力。

此時，我們就要考慮另外一種技術叫分庫分表。分庫分表在庫內分表的基礎上，將分的表挪動到不同的主機和數據庫上。可以充分的使用其他主機的CPU、內存和IO資源。 拆分方式進一步演進到下面：

4 分庫分表存在的問題

4.1 事務問題

在執行分庫分表之後，由於數據存儲到瞭不同的庫上，數據庫事務管理出現瞭困難。如果依賴數據庫本身的分佈式事務管理功能去執行事務，將付出高昂的性能代價；如果由應用程序去協助控制，形成程序邏輯上的事務，又會造成編程方面的負擔。

4.2 跨庫跨表的join問題

在執行瞭分庫分表之後，難以避免會將原本邏輯關聯性很強的數據劃分到不同的表、不同的庫上，這時，表的關聯操作將受到限制，我們無法join位於不同分庫的表，也無法join分表粒度不同的表，結果原本一次查詢能夠完成的業務，可能需要多次查詢才能完成。 

4.3 額外的數據管理負擔和數據運算壓力

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

隻需一個order by語句就可以搞定，但是在進行分表之後，將需要n個order by語句，分別查出每一個分表的前100名用戶數據，然後再對這些數據進行合並計算，才能得出結果。

以上就是Mysql數據庫分庫分表全面瓦解的詳細內容，更多關於Mysql分庫分表的資料請關註WalkonNet其它相關文章！

推薦閱讀：