項(xiàng)目開發(fā)中，隨著數(shù)據(jù)庫數(shù)據(jù)量越來越大，單個(gè)表中數(shù)據(jù)太多，從而導(dǎo)致查詢速度變慢，而且由于表的鎖機(jī)制導(dǎo)致應(yīng)用操作也受到嚴(yán)重影響，出現(xiàn)了數(shù)據(jù)庫性能瓶頸。因此我們需要考慮分表與分區(qū)，MySQL分表分區(qū)就是為了解決大數(shù)據(jù)量導(dǎo)致MySQL性能低下的問題。
什么是MySQL分表
從表面意思上看，MySQL分表就是將一個(gè)表分成多個(gè)表，數(shù)據(jù)和數(shù)據(jù)結(jié)構(gòu)都有可能會(huì)變。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、分表操作
1）自定義規(guī)則
按照用戶或業(yè)務(wù)的編號(hào)分表。對(duì)與用戶或業(yè)務(wù)可以按照編號(hào)%n，進(jìn)行分成n表。
按照日期分表。對(duì)于日志或統(tǒng)計(jì)類等的表。可以按照年,月，日，周分表。
2）使用Merge存儲(chǔ)引擎
使用Merge存儲(chǔ)引擎實(shí)現(xiàn)MySQL分表比較適合那些沒有事先考慮分表，隨著數(shù)據(jù)的增多，已經(jīng)出現(xiàn)了數(shù)據(jù)查詢慢的情況。使用Merge存儲(chǔ)引擎實(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存儲(chǔ)引擎實(shí)現(xiàn)MySQL分表，分表后的結(jié)果會(huì)分為主表和子表，主表類似于一個(gè)殼子，邏輯上封裝了子表，實(shí)際上數(shù)據(jù)都是存儲(chǔ)在子表中的。如下圖所示。
上圖是對(duì)user表進(jìn)行merge分表的結(jié)果，alluser是總表，user1和user2是分表。每一個(gè)表都有自己的表結(jié)構(gòu)，子表而且還保存了數(shù)據(jù)和索引，總表沒有保存數(shù)據(jù)和索引，總表只保存了分表的關(guān)系，以及插入數(shù)據(jù)的方式。
4、分表查詢
對(duì)于分表后的查詢操作，依然是聯(lián)合查詢，視圖等基本操作，或者使用merge引擎合并數(shù)據(jù)并在此表中查詢。復(fù)雜一些操作需要借助存儲(chǔ)過程來完成，借助外部工具實(shí)現(xiàn)對(duì)分表的管理。如：
垂直分表的使用join連接、水平分表的使用union連接。 對(duì)于使用Merge存儲(chǔ)引擎實(shí)現(xiàn)的MySQL分表，可以直接查詢總表。
5、注意事項(xiàng)
1）重復(fù)記錄 / 重復(fù)索引
若建立Merge表前，分表t1 / t2已經(jīng)存在，并且t1 / t2中存在重復(fù)記錄。查詢時(shí)，遇到滿足記錄的條目就會(huì)返回。意思就是只會(huì)顯示一條記錄，同時(shí)不會(huì)報(bào)錯(cuò)。若建立Merge表后，insert / update時(shí)，出現(xiàn)重復(fù)索引，則會(huì)提示錯(cuò)誤。MERGE表只對(duì)建表之后的操作負(fù)責(zé)。
2）如何刪除一個(gè)分表
不能直接刪除一個(gè)分表，這樣會(huì)破壞Merge表。正確的方法是：
alter table t ENGINE = MRG_MyISAM UNION = (t1) INSERT_METHOD = LAST;
drop table t1;
3）誤刪Merge總表
誤刪Merge表，是不會(huì)造成數(shù)據(jù)丟失的，只需重新創(chuàng)建總表。
什么是MySQL分區(qū)
從表面意思看，MySQL分區(qū)就是將一張表的數(shù)據(jù)分成多個(gè)存儲(chǔ)區(qū)塊，而數(shù)據(jù)結(jié)構(gòu)不變。另外，這些存儲(chǔ)區(qū)塊既可以在同一個(gè)磁盤上，也可以在不同的磁盤上。如下圖所示。
上圖是對(duì)表aa進(jìn)行分區(qū)后，磁盤上的文件分布。從圖中我們可以看到，分區(qū)后aa表的數(shù)據(jù)結(jié)構(gòu)沒有發(fā)生變化，而數(shù)據(jù)和索引存儲(chǔ)的位置由原來的一個(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定義的列表的值所對(duì)應(yīng)的行數(shù)據(jù)進(jìn)行分割。例如：DBA根據(jù)用戶的類型進(jìn)行分區(qū)。
3） Key（鍵值） – 上面Hash模式的一種延伸，這里的Hash Key是MySQL系統(tǒng)產(chǎn)生的。
4）Hash（哈希）– 這中模式允許DBA通過對(duì)表的一個(gè)或多個(gè)列的Hash Key進(jìn)行計(jì)算，最后通過這個(gè)Hash碼不同數(shù)值對(duì)應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)，。例如DBA可以建立一個(gè)對(duì)表主鍵進(jìn)行分區(qū)的表。
2、注意事項(xiàng)
1）以上每一種分區(qū)方式，都可以將這些分區(qū)所在的物理磁盤分開完全獨(dú)立，以提高磁盤IO吞吐量。如下：
上圖就是對(duì)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ū)往往會(huì)消耗大量系統(tǒng)內(nèi)存。
采用分區(qū)的表不支持外鍵：相關(guān)的約束邏輯必須通過程序來實(shí)現(xiàn)。
MySQL分表和分區(qū)的異同
都能提高mysql的性高，在高并發(fā)狀態(tài)下都有一個(gè)良好的表現(xiàn)。
分表和分區(qū)不矛盾，可以相互配合的，對(duì)于那些大訪問量，并且表數(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ū)相對(duì)于分表，操作方便，不需要?jiǎng)?chuàng)建子表。
