MySQL分表分區(qū)是解決大數(shù)據(jù)量導(dǎo)致MySQL性能低下的兩種方法。

什么是MySQL分表

從表面意思上看，MySQL分表就是將一個(gè)表分成多個(gè)表，數(shù)據(jù)和數(shù)據(jù)結(jié)構(gòu)都有可能會變。MySQL分表分為垂直分表和水平分表。

1、垂直分表

垂直分表是按表中的字段來劃分的，如下圖所示。

在上圖中，我們將本來分布在同一張表中的C1、C2、C3、C4四個(gè)字段垂直劃分到兩個(gè)表中。第一張表中分布C1、C3、C4三個(gè)字段，第二張表中分布C1、C2兩個(gè)字段。拆分后的兩個(gè)表通過C1這個(gè)共同的字段關(guān)聯(lián)起來。

2、水平分表

水平分表是按表中的記錄來劃分的。如下圖所示。

在上圖中，我們將本來分布在同一張表中的四條記錄，水平拆分到兩個(gè)表中。第一張表中，分布兩條記錄；第二張表中，分布兩條記錄。

3、分表操作

MySQL分表既可以自定義規(guī)則，也可以使用業(yè)內(nèi)通用規(guī)則，還可以使用merge存儲引擎來實(shí)現(xiàn)。

1）自定義規(guī)則

按照用戶或業(yè)務(wù)的編號分表。對與用戶或業(yè)務(wù)可以按照編號%n，進(jìn)行分成n表。

按照日期分表。對于日志或統(tǒng)計(jì)類等的表?？梢园凑漳?月，日，周分表。

2）使用Merge存儲引擎

使用Merge存儲引擎實(shí)現(xiàn)MySQL分表比較適合那些沒有事先考慮分表，隨著數(shù)據(jù)的增多，已經(jīng)出現(xiàn)了數(shù)據(jù)查詢慢的情況。使用Merge存儲引擎實(shí)現(xiàn)MySQL分表可以避免改代碼。使用Merge實(shí)現(xiàn)MySQL分表可以按如下形式操作：

在上圖中，ENGINE = MERGE表示，使用merge引擎。另外ENGINE = MRG_MyISAM是一樣的意思。UNION = (user1, user2)表示，掛接了user1、user2表，INSERT_METHOD = LAST表示插入方式：0不允許插入，F(xiàn)IRST插入到UNION中的第一個(gè)表，LAST插入到UNION中的最后一個(gè)表。

使用Merge存儲引擎實(shí)現(xiàn)MySQL分表，分表后的結(jié)果會分為主表和子表，主表類似于一個(gè)殼子，邏輯上封裝了子表，實(shí)際上數(shù)據(jù)都是存儲在子表中的。如下圖所示。

上圖是對user表進(jìn)行merge分表的結(jié)果，alluser是總表，user1和user2是分表。每一個(gè)表都有自己的表結(jié)構(gòu)，子表而且還保存了數(shù)據(jù)和索引，總表沒有保存數(shù)據(jù)和索引，總表只保存了分表的關(guān)系，以及插入數(shù)據(jù)的方式。

4、分表查詢

對于分表后的查詢操作，依然是聯(lián)合查詢，視圖等基本操作，或者使用merge引擎合并數(shù)據(jù)并在此表中查詢。復(fù)雜一些操作需要借助存儲過程來完成，借助外部工具實(shí)現(xiàn)對分表的管理。如：

• 垂直分表的使用join連接、水平分表的使用union連接。
• 對于使用Merge存儲引擎實(shí)現(xiàn)的MySQL分表，可以直接查詢總表。

5、注意事項(xiàng)

1）重復(fù)記錄 / 重復(fù)索引

若建立Merge表前，分表t1 / t2已經(jīng)存在，并且t1 / t2中存在重復(fù)記錄。查詢時(shí)，遇到滿足記錄的條目就會返回。意思就是只會顯示一條記錄，同時(shí)不會報(bào)錯(cuò)。若建立Merge表后，insert / update時(shí)，出現(xiàn)重復(fù)索引，則會提示錯(cuò)誤。MERGE表只對建表之后的操作負(fù)責(zé)。

2）如何刪除一個(gè)分表

不能直接刪除一個(gè)分表，這樣會破壞Merge表。正確的方法是： 

alter table t ENGINE = MRG_MyISAM UNION = (t1) INSERT_METHOD = LAST；  
drop table t1；  

3）誤刪Merge總表

誤刪Merge表，是不會造成數(shù)據(jù)丟失的，只需重新創(chuàng)建總表。

什么是MySQL分區(qū)

從表面意思看，MySQL分區(qū)就是將一張表的數(shù)據(jù)分成多個(gè)存儲區(qū)塊，而數(shù)據(jù)結(jié)構(gòu)不變。另外，這些存儲區(qū)塊既可以在同一個(gè)磁盤上，也可以在不同的磁盤上。如下圖所示。

上圖是對表aa進(jìn)行分區(qū)后，磁盤上的文件分布。從圖中我們可以看到，分區(qū)后aa表的數(shù)據(jù)結(jié)構(gòu)沒有發(fā)生變化，而數(shù)據(jù)和索引存儲的位置由原來的一個(gè)變成了兩個(gè)。另外，多出了一個(gè).par文件，打開.par文件后你可以看出他記錄了這張表的分區(qū)信息。

1、分區(qū)操作

MySQL從5.1.3開始支持Partition，你可以使用如下命令來確認(rèn)你的版本是否支持Partition：

MySQL支持的分區(qū)類型包括Range、List、Hash、Key，其中Range比較常用：

1）Range（范圍） – 這種模式允許DBA將數(shù)據(jù)劃分不同范圍。例如DBA可以將一個(gè)表通過年份劃分成三個(gè)分區(qū)，80年代（1980's）的數(shù)據(jù)，90年代（1990's）的數(shù)據(jù)以及任何在2000年（包括2000年）后的數(shù)據(jù)。如下：

在這里，將用戶表分成4個(gè)分區(qū)，以每300萬條記錄為界限，每個(gè)分區(qū)都有自己獨(dú)立的數(shù)據(jù)、索引文件的存放目錄。

2）List（預(yù)定義列表） – 這種模式允許系統(tǒng)通過DBA定義的列表的值所對應(yīng)的行數(shù)據(jù)進(jìn)行分割。例如：DBA根據(jù)用戶的類型進(jìn)行分區(qū)。

3）Key（鍵值） – 上面Hash模式的一種延伸，這里的Hash Key是MySQL系統(tǒng)產(chǎn)生的。

4）Hash（哈希） – 這中模式允許DBA通過對表的一個(gè)或多個(gè)列的Hash Key進(jìn)行計(jì)算，最后通過這個(gè)Hash碼不同數(shù)值對應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)，。例如DBA可以建立一個(gè)對表主鍵進(jìn)行分區(qū)的表。

2、注意事項(xiàng)

1）以上每一種分區(qū)方式，都可以將這些分區(qū)所在的物理磁盤分開完全獨(dú)立，以提高磁盤IO吞吐量。如下：

上圖就是對Range（范圍）分區(qū)類型進(jìn)行物理空間的分離操作。

2）分區(qū)雖然很爽，但目前的實(shí)現(xiàn)還有很多限制：

• 主鍵或者唯一索引必須包含分區(qū)字段：如PRIMARY KEY(i，created)。
• 很多時(shí)候，使用了分區(qū)就不要再使用主鍵，否則可能影響性能。
• 只能通過int類型的字段或者返回int類型的表達(dá)式來分區(qū)：通常使用YEAR或TO_DAYS等函數(shù)。
• 每個(gè)表最多1024個(gè)分區(qū)：不可能無限制的擴(kuò)展分區(qū)，而且過度使用分區(qū)往往會消耗大量系統(tǒng)內(nèi)存。
• 采用分區(qū)的表不支持外鍵：相關(guān)的約束邏輯必須通過程序來實(shí)現(xiàn)。

MySQL分表和分區(qū)的異同

都能提高mysql的性高，在高并發(fā)狀態(tài)下都有一個(gè)良好的表現(xiàn)。

分表和分區(qū)不矛盾，可以相互配合的，對于那些大訪問量，并且表數(shù)據(jù)比較多的表，我們可以采取分表和分區(qū)結(jié)合的方式（如果merge這種分表方式，不能和分區(qū)配合的話，可以用其他的分表試），訪問量不大，但是表數(shù)據(jù)很多的表，我們可以采取分區(qū)的方式等。

分表技術(shù)是比較麻煩的，需要手動(dòng)去創(chuàng)建子表，app服務(wù)端讀寫時(shí)候需要計(jì)算子表名。采用merge好一些，但也要?jiǎng)?chuàng)建子表和配置子表間的union關(guān)系。

表分區(qū)相對于分表，操作方便，不需要?jiǎng)?chuàng)建子表。