一、概念

1.1 為什么要分表和分區(qū)？

日常開發(fā)中我們經(jīng)常會(huì)遇到大表的情況，所謂的大表是指存儲(chǔ)了百萬級(jí)乃至千萬級(jí)條記錄的表。這樣的表過于龐大，導(dǎo)致數(shù)據(jù)庫在查詢和插入的時(shí)候耗時(shí)太長，性能低下，如果涉及聯(lián)合查詢的情況，性能會(huì)更加糟糕。**分表和表分區(qū)的目的就是減少數(shù)據(jù)庫的負(fù)擔(dān)，提高數(shù)據(jù)庫的效率，通常點(diǎn)來講就是提高表的增刪改查效率**。

1.2 什么是分表？

分表是將一個(gè)大表按照一定的規(guī)則分解成多張具有獨(dú)立存儲(chǔ)空間的實(shí)體表，我們可以稱為子表，每個(gè)表都對(duì)應(yīng)三個(gè)文件，MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結(jié)構(gòu)文件。這些子表可以分布在同一塊磁盤上，也可以在不同的機(jī)器上。app讀寫的時(shí)候根據(jù)事先定義好的規(guī)則得到對(duì)應(yīng)的子表名，然后去操作它。

1.3 什么是分區(qū)？

分區(qū)和分表相似，都是按照規(guī)則分解表。不同在于分表將大表分解為若干個(gè)獨(dú)立的實(shí)體表，而分區(qū)是將數(shù)據(jù)分段劃分在多個(gè)位置存放，可以是同一塊磁盤也可以在不同的機(jī)器。分區(qū)后，表面上還是一張表，但數(shù)據(jù)散列到多個(gè)位置了。app讀寫的時(shí)候操作的還是大表名字，db自動(dòng)去組織分區(qū)的數(shù)據(jù)。

1.4 mysql分表和分區(qū)有什么聯(lián)系呢？

• 都能提高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)建子表。

二、分區(qū)

2.1 分區(qū)的類型

2.1.1 Range

把連續(xù)區(qū)間按范圍劃分

create table user( ? ?id int(11), money int(11) unsigned not null, ? ?date datetime ) partition by range(YEAR(date))( ? ?partition p2014 values less than (2015), ? ?partition p2015 values less than (2016), ? ?partition p2016 values less than (2017), ? ?partition p2017 values less than maxvalue );

2.1.2 List

把離散值分成集合，按集合劃分，適合有固定取值列的表

create table user( ? ?a int(11), ? ?b int(11) ) partition by list(b)( ? ?partition p0 values in (1,3,5,7,9), ? ?partition p1 values in (2,4,6,8,0) );

2.1.3 Hash

隨機(jī)分配，分區(qū)數(shù)固定

create table user( ? ?a int(11), ? ?b datetime ) partition by hash(YEAR(b)) partitions 4;

2.1.4 Key

類似Hash，區(qū)別是只支持1列或多列,且mysql提供自身的Hash函數(shù)

create table user( ? ?a int(11), ? ?b datetime ) partition by key(b) partitions 4;

2.2 分區(qū)管理

新增分區(qū)

ALTER TABLE sale_data ADD PARTITION (PARTITION p201710 VALUES LESS THAN (201711));

刪除分區(qū)

--當(dāng)刪除了一個(gè)分區(qū)，也同時(shí)刪除了該分區(qū)中所有的數(shù)據(jù)。 ALTER TABLE sale_data DROP PARTITION p201710;

分區(qū)的合并

ALTER TABLE sale_data REORGANIZE PARTITION p201701,p201702,p201703, p201704,p201705,p201706, p201707,p201708,p201709 INTO ( ? ?PARTITION p2017Q1 VALUES LESS THAN (201704), ? ?PARTITION p2017Q2 VALUES LESS THAN (201707), ? ?PARTITION p2017Q3 VALUES LESS THAN (201710) );

2.3 分區(qū)應(yīng)該注意的事項(xiàng)

• 做分區(qū)時(shí)，要么不定義主鍵，要么把分區(qū)字段加入到主鍵中。

• 分區(qū)字段不能為NULL，要不然怎么確定分區(qū)范圍呢，所以盡量NOT NULL

三、分表

3.1 垂直分表

把原來有很多列的表拆分成多個(gè)表，原則是：

（1）把常用、不常用的字段分開放

（2）把大字段獨(dú)立存放在一個(gè)表中

3.2 水平分表

為了解決單表數(shù)據(jù)量過大的問題，每個(gè)水平拆分表的結(jié)構(gòu)完全一致

3.2.1 按時(shí)間結(jié)構(gòu)

如果業(yè)務(wù)系統(tǒng)對(duì)時(shí)效性較高，比如新聞發(fā)布系統(tǒng)的文章表，可以把數(shù)據(jù)庫設(shè)計(jì)成時(shí)間結(jié)構(gòu)，按時(shí)間分有下面幾種結(jié)構(gòu)

3.2.1.1 平板式

article_201701 article_201702 article_201703

用年來分還是用月可自定，但用日期的話表就太多了，也沒這必要。一般建議是按月分就可以。

這種分法，其難處在于，假設(shè)我要列20條數(shù)據(jù)，結(jié)果這三張表里都有2條，那么業(yè)務(wù)上很有可能要求讀三次表。如果時(shí)間長了，有幾十張表，而每張表是0條，那不就是要讀完整個(gè)系統(tǒng)的表才行么?另外這個(gè)結(jié)構(gòu)，要作分頁是比較難實(shí)現(xiàn)的。

主鍵：在這個(gè)系統(tǒng)中，主鍵是13位帶毫秒的時(shí)間戳，不要用自動(dòng)編號(hào)，否則難以通過主鍵定位到表，也可以在查詢時(shí)帶上時(shí)間，但比較煩瑣。

3.2.1.2 歸檔式

article_old article_new

為了解決平板式的缺點(diǎn)，可以采用時(shí)間歸檔式設(shè)計(jì)，可以看到這個(gè)系統(tǒng)只有兩張表。一張是舊文章表，一張是新文章表，新文章表放2個(gè)月的信息，每天定期把2個(gè)月中的最早一天的文章歸入舊表中。這樣一方面可以解決性能問題，因?yàn)橐话阈侣劙l(fā)布系統(tǒng)讀取的都是新的內(nèi)容，舊的內(nèi)容讀取少;第二可以委婉地解決功能問題，比如平板式所說的問題，在歸檔式中最多也只需要讀2張表就完成了。

歸檔式的缺點(diǎn)在于舊表容量還是相對(duì)比較大，如果業(yè)務(wù)允許，可對(duì)舊表中的超舊內(nèi)容進(jìn)行再歸檔或直接清理掉。

3.2.2 按版塊結(jié)構(gòu)

如果按照文章的所屬版塊進(jìn)行拆表，比如新聞、體育版塊拆表，一方面可以使每個(gè)表數(shù)據(jù)量分離，另一方面是各版塊之間相互影響可降到最低。假如新聞版塊的數(shù)據(jù)表損壞或需要維護(hù)，并不會(huì)影響到體育版塊的正常工作，從而降低了風(fēng)險(xiǎn)。版塊結(jié)構(gòu)同時(shí)常用于bbs這樣的系統(tǒng)。

板塊結(jié)構(gòu)也有如下幾種分法

3.2.2.1 對(duì)應(yīng)式

對(duì)于版塊數(shù)量不多，而且較為固定的形式，就直接對(duì)應(yīng)就好。比如新聞版塊，可以分出新聞的目錄表，新聞的文章表等。

news_category news_article sports_category sports_article

可看到每一個(gè)版塊都對(duì)應(yīng)著一組相同的表結(jié)構(gòu)，好處就是一目了然。在功能上，因?yàn)榘鎵K之間還是有一些隔閡，所以需要聯(lián)合查詢的需求不多，開發(fā)上比時(shí)間結(jié)構(gòu)的方式要輕松。

主鍵：依舊要考慮的，在這個(gè)系統(tǒng)中，主鍵是版塊+時(shí)間戳，單純的時(shí)間戳或自動(dòng)編號(hào)也能用，查詢時(shí)要記得帶上版塊用于定位表。

3.2.2.2 冷熱式

對(duì)應(yīng)式的缺點(diǎn)是，如果版塊數(shù)量很大而且不確定，那要分出的表數(shù)量就太多了。舉個(gè)例子：百度貼吧，如果按一個(gè)詞條一個(gè)表設(shè)計(jì)，那得有多少張表呢?

用這樣的方式吧。

tieba_汽車 tieba_飛機(jī) tieba_火箭 tieba_unite

這個(gè)表汽車、火箭表是屬于熱門表，定義為新建的版塊放在unite表里面，待到其超過一萬張主貼的時(shí)候才開對(duì)應(yīng)表結(jié)構(gòu)。因?yàn)樵谫N吧這種系統(tǒng)中，冷門版塊

肯定比熱門版塊多得多，這些冷門版塊通常只有幾張?zhí)?，為它們開表也太浪費(fèi)了;同時(shí)熱門版塊數(shù)量和訪問量等，又比冷門版塊多得多，非常有特點(diǎn)。

unite表還可以擴(kuò)展成哈希表，利用詞條的md5編碼，可以分成n張表，我算了一下，md5前一位可分36張表，兩位即是1296張表，足夠了。

tieba_unite_ab tieba_unite_ac

3.2.3 按哈希結(jié)構(gòu)

哈希結(jié)構(gòu)通常用于博客之類的基于用戶的場(chǎng)合，在博客這樣的系統(tǒng)里有幾個(gè)特點(diǎn)，

• 是用戶數(shù)量非常多，

• 是每個(gè)用戶發(fā)的文章數(shù)量都較少，

• 是用戶發(fā)文章不定期，

• 是每個(gè)用戶發(fā)得不多，但總量仍非常之大。

基于這些特點(diǎn)，用以上所說的任何一種分表方式都不合適，一沒有固定的時(shí)效不宜用時(shí)間拆，二用戶很多，而且還偏偏都是冷門，所以也不宜用版塊(用戶)拆。

哈希結(jié)構(gòu)在上面有所提及，既然按每個(gè)用戶不好直接拆，那就把一群用戶歸進(jìn)一個(gè)表好了。

blog_aa blog_ab blog_ac

如上所說，md5取前兩位哈希可以達(dá)到1296張表，如果覺得不夠，那就再加一位，總數(shù)可達(dá)46656張表，還不夠?

表的數(shù)量太多，要?jiǎng)?chuàng)建這些表也是挺麻煩的，可以考慮在程序里往數(shù)據(jù)庫insert之前，多執(zhí)行一句判斷表存在與否并創(chuàng)建表的語句，很實(shí)用，消耗也并不很大。

主鍵：依舊要考慮的，在這個(gè)系統(tǒng)中，主鍵是用戶ID+時(shí)間戳，單純的時(shí)間戳或自動(dòng)編號(hào)也能用，但查詢時(shí)要記得帶上用戶名用于定位表。

最后送大家一份?JetBrains IDEA 破解教程和干貨，快拿去用吧：

百度網(wǎng)盤鏈接：https://pan.baidu.com/s/1jxvnKgTsTbkVmjcPGeTm0g

提取碼：2cg8?