MySQ分庫分表與分區的區別和思考（MySQ分庫分表與分區的區別和思考）

2023-05-26 10:07:02

一、資料庫瓶頸的思考

不管是IO瓶頸，還是CPU瓶頸，最終都會導致資料庫的活躍連接數增加，進而逼近甚至達到資料庫可承載活躍連接數的閾值。在業務Service來看就是，可用資料庫連接少甚至無連接可用。接下來就可以想像了吧（並發量、吞吐量、崩潰）。

1、IO瓶頸

第一種：磁碟讀IO瓶頸，熱點數據太多，資料庫緩存放不下，每次查詢時會產生大量的IO，降低查詢速度 -> 分庫和垂直分表。

第二種：網絡IO瓶頸，請求的數據太多，網絡帶寬不夠 -> 分庫。

2、CPU瓶頸

第一種：SQL問題，如SQL中包含join，group by，order by，非索引欄位條件查詢等，增加CPU運算的操作 -> SQL優化，建立合適的索引，在業務Service層進行業務計算。

第二種：單表數據量太大，查詢時掃描的行太多，SQL效率低，CPU率先出現瓶頸 -> 水平分表。

3、資料庫拆分

數據切分可以是物理上的，對數據通過一系列的切分規則將數據分布到不同的DB伺服器上，通過路由規則路由訪問特定的資料庫，這樣一來每次訪問面對的就不是單臺伺服器了，而是N臺伺服器，這樣就可以降低單臺機器的負載壓力。

數據切分也可以是資料庫內的，對數據通過一系列的切分規則，將數據分布到一個資料庫的不同表中，比如將article分為article_001,article_002等子表，若干個子表水平拼合有組成了邏輯上一個完整的article表，這樣做的目的其實也是很簡單的。舉個例子說明，比如article表中現在有5000w條數據，此時我們需要在這個表中增加（insert）一條新的數據，insert完畢後，資料庫會針對這張表重新建立索引，5000w行數據建立索引的系統開銷還是不容忽視的。但是反過來，假如我們將這個表分成100 個table呢，從article_001一直到article_100，5000w行數據平均下來，每個子表裡邊就只有50萬行數據，這時候我們向一張只有50w行數據的table中insert數據後建立索引的時間就會呈數量級的下降，極大了提高了DB的運行時效率，提高了DB的並發量。當然分表的好處還不知這些，還有諸如寫操作的鎖操作等，都會帶來很多顯然的好處。

綜上，分庫降低了單點機器的負載；分表，提高了數據操作的效率，尤其是Write操作的效率。

4、水平拆分

水平拆分是指數據表行的拆分，表的行數超過200萬行時，就會變慢，這時可以把一張的表的數據拆成多張表來存放。

通常情況下，我們使用取模的方式來進行表的拆分;比如一張有400W的用戶表users，為提高其查詢效率我們把其分成4張表users1，users2，users3，users4通過用ID取模的方法把數據分散到四張表內Id%4 1 = [1,2,3,4]然後查詢,更新,刪除也是通過取模的方法來查詢。

例：QQ的登錄表。假設QQ的用戶有100億，如果只有一張表，每個用戶登錄的時候資料庫都要從這100億中查找，會很慢很慢。如果將這一張表分成100份，每張表有1億條，就小了很多，比如qq0,qq1,qq1…qq99表。

用戶登錄的時候，可以將用戶的id0，那麼會得到0-99的數，查詢表的時候，將表名qq跟取模的數連接起來，就構建了表名。比如123456789用戶，取模的89，那麼就到qq89表查詢，查詢的時間將會大大縮短。

另外部分業務邏輯也可以通過地區，年份等欄位來進行歸檔拆分;進行拆分後的表，只能滿足部分查詢的高效查詢需求，這時我們就要在產品策劃上，從界面上約束用戶查詢行為。比如我們是按年來進行歸檔拆分的,這個時候在頁面設計上就約束用戶必須要先選擇年,然後才能進行查詢;在做分析或者統計時，由於是自己人的需求,多點等待其實是沒關係的,並且並發很低,這個時候可以用union把所有表都組合成一張視圖來進行查詢,然後再進行查詢。

水平拆分的優點：

◆表關聯基本能夠在資料庫端全部完成；

◆不會存在某些超大型數據量和高負載的表遇到瓶頸的問題；

◆應用程式端整體架構改動相對較少；

◆事務處理相對簡單；

◆只要切分規則能夠定義好，基本上較難遇到擴展性限制；

水平切分的缺點：

◆切分規則相對更為複雜，很難抽象出一個能夠滿足整個資料庫的切分規則；

◆後期數據的維護難度有所增加，人為手工定位數據更困難；

◆應用系統各模塊耦合度較高，可能會對後面數據的遷移拆分造成一定的困難。

垂直拆分

垂直拆分是指數據表列的拆分，把一張列比較多的表拆分為多張表。表的記錄並不多，但是欄位卻很長，表佔用空間很大，檢索表的時候需要執行大量的IO，嚴重降低了性能。這時需要把大的欄位拆分到另一個表，並且該表與原表是一對一的關係。

通常我們按以下原則進行垂直拆分：

把不常用的欄位單獨放在一張表；把text，blob等大欄位拆分出來放在附表中;；經常組合查詢的列放在一張表中;

例如學生答題表tt：有如下欄位：

Id name 分數題目回答

其中題目和回答是比較大的欄位，id name 分數比較小。

如果我們只想查詢id為8的學生的分數：select 分數 from tt where id = 8;雖然知識查詢分數，但是題目和回答這兩個大欄位也是要被掃描的，很消耗性能。但是我們只關心分數，並不想查詢題目和回答。這就可以使用垂直分割。我們可以把題目單獨放到一張表中，通過id與tt表建立一對一的關係，同樣將回答單獨放到一張表中。這樣我們插敘tt中的分數的時候就不會掃描題目和回答了。

垂直切分的優點

◆ 資料庫的拆分簡單明了，拆分規則明確；

◆ 應用程式模塊清晰明確，整合容易；

◆ 數據維護方便易行，容易定位；

垂直切分的缺點

◆ 部分表關聯無法在資料庫級別完成，需要在程序中完成；

◆ 對於訪問極其頻繁且數據量超大的表仍然存在性能平靜，不一定能滿足要求；

◆ 事務處理相對更為複雜；

◆ 切分達到一定程度之後，擴展性會遇到限制；

◆ 過讀切分可能會帶來系統過渡複雜而難以維護

二、分區表的實現原理

2.1 實現方式

具體如何實現上面連結裡有寫，這裡只需記住如果表中存在主鍵或唯一索引時，分區列必須是唯一索引的一個組成部分。這個是資料庫分的，應用透明，代碼無需修改任何東西。

2.2 內部文件

先去data目錄，如果不知道目錄位置的可以執行：

接下來看下內部文件：

從上圖我們可以看出，有2中類型的文件，.frm文件和.ibd文件

frm文件：表結構文件

ibd文件：InnoDB中，索引和數據都在同個文件.ibdata（你的執行結果可能是.MYD索引文件和.MYI數據文件，沒關係，這是MyIsAm存儲引擎，對應著InnoDB的.ibd文件）。因為Order這張表分為5個區，所以有5個這樣的文件

par文件：你執行的結果可能有.par文件也可能沒有。注意：從MySql 5.7.6開始，不再創建.par分區定義文件。分區定義存儲在內部數據字典中。

2.3 數據處理

分區表後，提高了MySql性能。如果一張表的話，那就只有一個.ibd文件，一顆大的B 樹。如果分表後，將按分區規則，分成不同的區，也就是一個大的B 樹，分成多個小的樹。

讀的效率肯定提升了，如果走分區鍵索引的話，先走對應分區的輔助索引B 樹，再走對應分區的聚集索引B 樹。

如果沒有走分區鍵，將會在所有分區都會執行一次。會造成多次邏輯IO！平時開發如果想查看sql語句的分區查詢可以使用explain partitons select xxxxx語句。可以看到一句select語句走了幾個分區。

mysql> explain PARTITIONs select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime<'2016-08-25 23:59:00'; ---- ------------- ------------------- ------------ ------ --------------- ------ --------- ------ ------- ------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | ---- ------------- ------------------- ------------ ------ --------------- ------ --------- ------ ------- ------------- | 1 | SIMPLE | ClientActionTrack | p20160825 | ALL | NULL | NULL | NULL | NULL | 33868 | Using where | ---- ------------- ------------------- ------------ ------ --------------- ------ --------- ------ ------- -------------

row in set (0.00 sec)

複製

三、分庫分表的邏輯

當一張表隨著時間和業務的發展，庫裡表的數據量會越來越大。數據操作也隨之會越來越大。一臺物理機的資源有限，最終能承載的數據量、數據的處理能力都會受到限制。這時候就會使用分庫分表來承接超大規模的表，單機放不下的那種。

區別於分區的是，分區一般都是放在單機裡的，用的比較多的是時間範圍分區，方便歸檔。只不過分庫分表需要代碼實現，分區則是mysql內部實現。分庫分表和分區並不衝突，可以結合使用。

3.1 實現

3.1.1 分庫分表標準

存儲佔用100G 數據增量每天200w 單表條數1億條

3.1.2 分庫分表欄位

分庫分表欄位取值非常重要

1.在大多數場景該欄位是查詢欄位

2.數值型

一般使用userId，可以滿足上述條件

3.2 分布式資料庫中間件

分布式資料庫中間件分為兩種，proxy和客戶端式架構。proxy模式有MyCat、DBProxy等，客戶端式架構有TDDL、Sharding-JDBC等。那麼proxy和客戶端式架構有何區別呢？各自有什麼優缺點呢？其實看一張圖便可知曉。

proxy模式的話我們的select和update語句都是發送給代理，由這個代理來操作具體的底層資料庫。所以必須要求代理本身需要保證高可用，否則資料庫沒有宕機，proxy掛了，那就走遠了。

客戶端模式通常在連接池上做了一層封裝，內部與不同的庫連接，sql交給smart-client進行處理。通常僅支持一種語言，如果其他語言要使用，需要開發多語言客戶端。

各自的優缺點如下：

3.3 內部文件

找了一個分庫分表分區的例子，基本上和分區表的差不多，只是多了多了很多表的.ibd文件，上面有文件的解釋：

[miaojiaxing@Grim testmydata]# ls | grep 'base_info'base_info_00.frmbase_info_00#P#p_2018.ibdbase_info_00#P#p_2019.ibdbase_info_00#P#p_2020.ibdbase_info_00#P#p_2021.ibdbase_info_00#P#p_init.ibdbase_info_00#P#p_max.ibdbase_info_01.frmbase_info_01#P#p_2018.ibdbase_info_01#P#p_2019.ibdbase_info_01#P#p_2020.ibdbase_info_01#P#p_2021.ibdbase_info_01#P#p_init.ibdbase_info_01#P#p_max.ibdbase_info.frmbase_info.ibd.4 問題

複製

3.4.1 事務問題

既然分庫分表了，那麼肯定涉及到分布式事務，如何保證插入到不同庫的多條記錄能夠要麼同時成功，要麼同時失敗。有些同學可能想到XA，XA性能差而且不需要使用mysql5.7。柔性事務是目前主流的方案，TCC模式就屬於柔性事務。

對於分布式事務問題每家公司有自己的實現，華為用saga，阿里用TXC，螞蟻用DTX，支持FMT模式和TCC模式。

3.4.2 join問題

tddl、MyCAT等都支持跨分片join。但是盡力避免跨庫join，比如通過欄位冗餘的方式等。

如果出現了這種情況且中間件支持分片join，那麼可以這樣使用。如果不支持可以手工查詢。

四、分庫分表策略

1、水平分庫

概念：以欄位為依據，按照一定策略（hash、range等），將一個庫中的數據拆分到多個庫中。結果：每個庫的結構都一樣；每個庫的數據都不一樣，沒有交集；所有庫的併集是全量數據；場景：系統絕對並發量上來了，分表難以根本上解決問題，並且還沒有明顯的業務歸屬來垂直分庫。分析：庫多了，io和cpu的壓力自然可以成倍緩解。

2、水平分表

概念：以欄位為依據，按照一定策略（hash、range等），將一個表中的數據拆分到多個表中。結果：每個表的結構都一樣；每個表的數據都不一樣，沒有交集；所有表的併集是全量數據；場景：系統絕對並發量並沒有上來，只是單表的數據量太多，影響了SQL效率，加重了CPU負擔，以至於成為瓶頸。分析：表的數據量少了，單次SQL執行效率高，自然減輕了CPU的負擔。3、垂直分庫

概念：以表為依據，按照業務歸屬不同，將不同的表拆分到不同的庫中。結果：每個庫的結構都不一樣；每個庫的數據也不一樣，沒有交集；所有庫的併集是全量數據；場景：系統絕對並發量上來了，並且可以抽象出單獨的業務模塊。分析：到這一步，基本上就可以服務化了。例如，隨著業務的發展一些公用的配置表、字典表等越來越多，這時可以將這些表拆到單獨的庫中，甚至可以服務化。再有，隨著業務的發展孵化出了一套業務模式，這時可以將相關的表拆到單獨的庫中，甚至可以服務化。4、垂直分表

概念：以欄位為依據，按照欄位的活躍性，將表中欄位拆到不同的表（主表和擴展表）中。結果：每個表的結構都不一樣；每個表的數據也不一樣，一般來說，每個表的欄位至少有一列交集，一般是主鍵，用於關聯數據；所有表的併集是全量數據；場景：系統絕對並發量並沒有上來，表的記錄並不多，但是欄位多，並且熱點數據和非熱點數據在一起，單行數據所需的存儲空間較大。以至於資料庫緩存的數據行減少，查詢時會去讀磁碟數據產生大量的隨機讀IO，產生IO瓶頸。分析：可以用列表頁和詳情頁來幫助理解。垂直分表的拆分原則是將熱點數據（可能會冗餘經常一起查詢的數據）放在一起作為主表，非熱點數據放在一起作為擴展表。這樣更多的熱點數據就能被緩存下來，進而減少了隨機讀IO。拆了之後，要想獲得全部數據就需要關聯兩個表來取數據。但記住，千萬別用join，因為join不僅會增加CPU負擔並且會講兩個表耦合在一起（必須在一個資料庫實例上）。關聯數據，應該在業務Service層做文章，分別獲取主表和擴展表數據然後用關聯欄位關聯得到全部數據。

五、表分區的策略

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

1、HASH

HASH分區主要用來確保數據在預先確定數目的分區中平均分布，而在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中，而在HASH分區中，MySQL自動完成這些工作，你所要做的只是基於將要被哈希的列值指定一個列值或表達式，以及指定被分區的表將要被分割成的分區數量。

比如：

CREATE TABLE t_product_item (

id int(7) not null,

title varchar(40) not null,

subtitle varchar(60) null,

price double not null,

imgurl varchar(70) not null,

producttype int(2) not null,

createtime datetime not null

)

ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY HASH(YEAR(createtime))

PARTITIONS 10

;

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

NOTE：

可以通過在CREATE TABLE 語句上添加一個「PARTITION BY HASH (expr)」子句，其中「expr」是一個返回整數的表達式。它可以是欄位類型為MySQL 整型的一列的名字，也可以是返回非負數的表達式。另外，可能需要在後面再添加一個「PARTITIONSnum」子句，其中num 是一個非負的整數，它表示表將要被分割成分區的數量。

2、RANGE

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

比如：

CREATE TABLE t_product_item (

id int(7) not null,

title varchar(40) not null,

subtitle varchar(60) null,

price double not null,

imgurl varchar(70) not null,

producttype int(2) not null,

createtime datetime not null

)

ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE(producttype) (

PARTITIONP0 VALUES LESS THAN(2),

PARTITIONP1 VALUES LESS THAN(4),

PARTITIONp2 VALUES LESS THAN(6),

PARTITIONp3 VALUES LESS THAN MAXVALUE

);

上面的例子，使用了範圍RANGE函數對產品類型進行分區，共分為4個分區，產品類別為0,1的對應在分區P0中，2,3類別在分區P1中，依次類推即可。那麼類別編號大於6的怎麼分區呢？我們可以使用MAXVALUE來將大於6的數據統一存放在分區P3中即可。

3、LIST

類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇分區的。LIST分區通過使用「PARTITION BY LIST(expr)」來實現，其中「expr」是某列值或一個基於某個列值、並返回一個整數值的表達式，然後通過「VALUES IN (value_list)」的方式來定義每個分區，其中「value_list」是一個通過逗號分隔的整數列表。

比如：

DROP TABLE IF EXISTS t_product_item;

CREATE TABLE t_product_item (

id int(7) not null,

title varchar(40) not null,

subtitle varchar(60) null,

price double not null,

imgurl varchar(70) not null,

producttype int(2) not null,

createtime datetime not null

)

ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY LIST(producttype) (

PARTITIONP0 VALUES IN (0,1),

PARTITIONP1 VALUES IN (2,3),

PARTITIONP2 VALUES IN (4,5),

PARTITIONP3 VALUES IN (6,7,8,9,10,11,12)

)

上面的例子，使用了列表匹配LIST函數對產品類型進行分區，共分為4個分區，產品類別為0,1的對應在分區P0中，2,3類別在分區P1中，依次類推即可。那麼類別編號大於12的怎麼分區呢？這裡不同於RANGE，LIST分區的數據必須匹配列表中的產品類別才能進行分區。

4、KEY

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

比如：

CREATE TABLE t_product_item (

id int(7) not null,

title varchar(40) not null,

subtitle varchar(60) null,

price double not null,

imgurl varchar(70) not null,

producttype int(2) not null,

createtime datetime not null

)

ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY KEY(producttype)

PARTITIONS 10;

NOTE：

此種分區算法目前使用的比較少，大家知道其存在和怎麼使用即可。

六、總結

分表和在用途上不一樣，分表是為了承接超大規模的表，單機放不下那種。分區的話則一般都是放在單機裡的，用的比較多的是時間範圍分區，方便歸檔。性能穩定上的話都是一個個子表，差不多，區別應該是分區表是mysql內部實現的，會比分表方案少一點數據交互只要你堅持，一步一步來，總歸會成功的。切忌，學技術急不來，快就是穩，穩就是快。技術有限，接收指正。如果您覺得寫的可以，請點個推薦。