MySQL全面瓦解28：分庫分表

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自動完成這些工作，

你所要做的只是基於將要被雜湊的列值指定一個列值或表示式，以及指定被分割槽的表將要被分割成的分割槽數量。 示例如下：

drop table if EXISTS  `t_userinfo`; 
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(YEAR(createtime))
PARTITIONS 10; 

上面的例子，使用HASH函式對createtime日期進行HASH運算，並根據這個日期來分割槽資料，這裡共分為10個分割槽。 

建表語句上新增一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個返回整數的表示式，它可以是欄位型別為MySQL 整型的一列的名字，也可以是返回非負數的表示式。

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

3.2.2 RANGE（範圍）

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

drop table if EXISTS  `t_userinfo`; 
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(gwcode) (
PARTITION P0 VALUES LESS THAN(101) ,
PARTITION P1 VALUES LESS THAN(201) ,
PARTITION P2 VALUES LESS THAN(301) ,
PARTITION P3 VALUES LESS THAN MAXVALUE
);

上面的示例，使用了範圍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”是一個通過逗號分隔的整數列表。 示例如下：

drop table if EXISTS  `t_userinfo`; 
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(`gwcode`) (
PARTITION P0 VALUES IN (46,77,89) ,
PARTITION P1 VALUES IN (106,125,177) ,
PARTITION P2 VALUES IN (205,219,289) ,
PARTITION P3 VALUES IN (302,317,458,509,610) 
);

上面的例子，使用了列表匹配LIST函式對員工崗位編號進行分割槽，共分為4個分割槽，編號為46,77,89的對應在分割槽P0中，106,125,177類別在分割槽P1中，依次類推即可。

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

3.2.4 KEY（鍵值）

類似於按HASH分割槽，區別在於KEY分割槽只支援計算一列或多列，且MySQL 伺服器提供其自身的雜湊函式。必須有一列或多列包含整數值。 示例如下：

drop table if EXISTS  `t_userinfo`; 
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(gwcode)
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名使用者資料，然後再對這些資料進行合併計算，才能得出結果。