一、概念
1.為什么要分表和分區(qū)？
日常開發(fā)中我們經(jīng)常會遇到大表的情況，所謂的大表是指存儲了百萬級乃至千萬級條記錄的表汤踏。這樣的表過于龐大，導(dǎo)致數(shù)據(jù)庫在查詢和插入的時候耗時太長舔腾，性能低下溪胶，如果涉及聯(lián)合查詢的情況，性能會更加糟糕稳诚。分表和表分區(qū)的目的就是減少數(shù)據(jù)庫的負(fù)擔(dān)哗脖，提高數(shù)據(jù)庫的效率，通常點來講就是提高表的增刪改查效率。
2.什么是分表才避？
分表是將一個大表按照一定的規(guī)則分解成多張具有獨立存儲空間的實體表橱夭，我們可以稱為子表，每個表都對應(yīng)三個文件桑逝，MYD數(shù)據(jù)文件棘劣，.MYI索引文件，.frm表結(jié)構(gòu)文件楞遏。這些子表可以分布在同一塊磁盤上茬暇，也可以在不同的機器上。app讀寫的時候根據(jù)事先定義好的規(guī)則得到對應(yīng)的子表名寡喝，然后去操作它而钞。
3.什么是分區(qū)？
分區(qū)和分表相似拘荡，都是按照規(guī)則分解表臼节。不同在于分表將大表分解為若干個獨立的實體表，而分區(qū)是將數(shù)據(jù)分段劃分在多個位置存放珊皿，可以是同一塊磁盤也可以在不同的機器网缝。分區(qū)后，表面上還是一張表蟋定，但數(shù)據(jù)散列到多個位置了粉臊。app讀寫的時候操作的還是大表名字，db自動去組織分區(qū)的數(shù)據(jù)驶兜。
4.mysql分表和分區(qū)有什么聯(lián)系呢扼仲？
- 都能提高mysql的性高，在高并發(fā)狀態(tài)下都有一個良好的表現(xiàn)抄淑。
- 分表和分區(qū)不矛盾屠凶，可以相互配合的，對于那些大訪問量肆资，并且表數(shù)據(jù)比較多的表矗愧，我們可以采取分表和分區(qū)結(jié)合的方式（如果merge這種分表方式，不能和分區(qū)配合的話郑原，可以用其他的分表試）唉韭，訪問量不大，但是表數(shù)據(jù)很多的表犯犁，我們可以采取分區(qū)的方式等属愤。
- 分表技術(shù)是比較麻煩的，需要手動去創(chuàng)建子表酸役，app服務(wù)端讀寫時候需要計算子表名住诸。采用merge好一些驾胆，但也要創(chuàng)建子表和配置子表間的union關(guān)系。
- 表分區(qū)相對于分表只壳，操作方便俏拱，不需要創(chuàng)建子表暑塑。
二吼句、分區(qū)
1.分區(qū)的類型：
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）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)
);
（3）Hash：隨機分配，分區(qū)數(shù)固定
例：
create table user(
a int(11),
b datetime )
partition by hash(YEAR(b))
partitions 4;
（4）Key：類似Hash驹愚，區(qū)別是只支持1列或多列,且mysql提供自身的Hash函數(shù)
例：
create table user(
a int(11),
b datetime )
partition by key(b)
partitions 4;
2.分區(qū)管理
- 新增分區(qū)
ALTER TABLE sale_data ADD PARTITION (PARTITION p201710 VALUES LESS THAN (201711));
- 刪除分區(qū)
--當(dāng)刪除了一個分區(qū)远搪，也同時刪除了該分區(qū)中所有的數(shù)據(jù)。
ALTER TABLE sale_data DROP PARTITION p201710;
- 分區(qū)的合并
下面的SQL逢捺，將p201701 - p201709 合并為3個分區(qū)p2017Q1 - p2017Q3
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)
);
3.分區(qū)應(yīng)該注意的事項：
（1）做分區(qū)時谁鳍，要么不定義主鍵，要么把分區(qū)字段加入到主鍵中劫瞳。
（2）分區(qū)字段不能為NULL倘潜，要不然怎么確定分區(qū)范圍呢，所以盡量NOT NULL
三志于、分表
1.垂直分表
把原來有很多列的表拆分成多個表涮因，原則是：
（1）把常用、不常用的字段分開放
（2）把大字段獨立存放在一個表中
2.水平分表
為了解決單表數(shù)據(jù)量過大的問題伺绽，每個水平拆分表的結(jié)構(gòu)完全一致养泡。
例：
（1）按時間結(jié)構(gòu)
如果業(yè)務(wù)系統(tǒng)對時效性較高，比如新聞發(fā)布系統(tǒng)的文章表奈应，可以把數(shù)據(jù)庫設(shè)計成時間結(jié)構(gòu)澜掩，按時間分有幾種結(jié)構(gòu)：
（a）平板式
表類似：
article_201701
article_201702
article_201703
用年來分還是用月可自定，但用日期的話表就太多了杖挣，也沒這必要输硝。一般建議是按月分就可以。
這種分法程梦，其難處在于点把，假設(shè)我要列20條數(shù)據(jù)，結(jié)果這三張表里都有2條屿附，那么業(yè)務(wù)上很有可能要求讀三次表郎逃。如果時間長了，有幾十張表挺份，而每張表是0條褒翰，那不就是要讀完整個系統(tǒng)的表才行么?另外這個結(jié)構(gòu)，要作分頁是比較難實現(xiàn)的。
主鍵：在這個系統(tǒng)中优训，主鍵是13位帶毫秒的時間戳朵你，不要用自動編號，否則難以通過主鍵定位到表揣非，也可以在查詢時帶上時間抡医，但比較煩瑣。
（b）歸檔式
表類似：
article_old
article_new
為了解決平板式的缺點早敬，可以采用時間歸檔式設(shè)計忌傻，可以看到這個系統(tǒng)只有兩張表。一張是舊文章表搞监，一張是新文章表水孩，新文章表放2個月的信息，每天定期把2
個月中的最早一天的文章歸入舊表中琐驴。這樣一方面可以解決性能問題俘种，因為一般新聞發(fā)布系統(tǒng)讀取的都是新的內(nèi)容，舊的內(nèi)容讀取少;第二可以委婉地解決功能問
題绝淡，比如平板式所說的問題宙刘，在歸檔式中最多也只需要讀2張表就完成了。
歸檔式的缺點在于舊表容量還是相對比較大够委，如果業(yè)務(wù)允許荐类，可對舊表中的超舊內(nèi)容進行再歸檔或直接清理掉。
（2）按版塊結(jié)構(gòu)
如果按照文章的所屬版塊進行拆表茁帽，比如新聞玉罐、體育版塊拆表，一方面可以使每個表數(shù)據(jù)量分離潘拨，另一方面是各版塊之間相互影響可降到最低吊输。假如新聞版塊的數(shù)據(jù)表損壞或需要維護，并不會影響到體育版塊的正常工作铁追，從而降低了風(fēng)險季蚂。版塊結(jié)構(gòu)同時常用于bbs這樣的系統(tǒng)。
板塊結(jié)構(gòu)也有幾種分法：
（a）對應(yīng)式
對于版塊數(shù)量不多琅束，而且較為固定的形式扭屁，就直接對應(yīng)就好。比如新聞版塊涩禀，可以分出新聞的目錄表料滥，新聞的文章表等。
news_category
news_article
sports_category
sports_article
可看到每一個版塊都對應(yīng)著一組相同的表結(jié)構(gòu)艾船，好處就是一目了然葵腹。在功能上高每，因為版塊之間還是有一些隔閡，所以需要聯(lián)合查詢的需求不多践宴，開發(fā)上比時間結(jié)構(gòu)的方式要輕松鲸匿。
主鍵：依舊要考慮的，在這個系統(tǒng)中阻肩，主鍵是版塊+時間戳带欢，單純的時間戳或自動編號也能用，查詢時要記得帶上版塊用于定位表磺浙。
（b）冷熱式
對應(yīng)式的缺點是洪囤，如果版塊數(shù)量很大而且不確定徒坡，那要分出的表數(shù)量就太多了撕氧。舉個例子：百度貼吧，如果按一個詞條一個表設(shè)計喇完，那得有多少張表呢?
用這樣的方式吧伦泥。
tieba_汽車
tieba_飛機
tieba_火箭
tieba_unite
這個表汽車、火箭表是屬于熱門表锦溪，定義為新建的版塊放在unite表里面不脯，待到其超過一萬張主貼的時候才開對應(yīng)表結(jié)構(gòu)。因為在貼吧這種系統(tǒng)中刻诊，冷門版塊
肯定比熱門版塊多得多防楷，這些冷門版塊通常只有幾張?zhí)樱瑸樗鼈冮_表也太浪費了;同時熱門版塊數(shù)量和訪問量等则涯，又比冷門版塊多得多复局，非常有特點。
unite表還可以擴展成哈希表粟判，利用詞條的md5編碼亿昏，可以分成n張表，我算了一下档礁，md5前一位可分36張表角钩，兩位即是1296張表，足夠了呻澜。
tieba_unite_ab
tieba_unite_ac
（3）按哈希結(jié)構(gòu)
哈希結(jié)構(gòu)通常用于博客之類的基于用戶的場合递礼，在博客這樣的系統(tǒng)里有幾個特點，
- 1是用戶數(shù)量非常多羹幸，
- 2是每個用戶發(fā)的文章數(shù)量都較少脊髓，
- 3是用戶發(fā)文章不定期，
- 4是每個用戶發(fā)得不多睹欲，但總量仍非常之大供炼。
基于這些特點一屋，用以上所說的任何一種分表方式都不合適，一沒有固定的時效不宜用時間拆袋哼，二用戶很多冀墨，而且還
偏偏都是冷門，所以也不宜用版塊(用戶)拆涛贯。
哈希結(jié)構(gòu)在上面有所提及诽嘉，既然按每個用戶不好直接拆，那就把一群用戶歸進一個表好了弟翘。
blog_aa
blog_ab
blog_ac
如上所說虫腋，md5取前兩位哈希可以達到1296張表稀余，如果覺得不夠悦冀，那就再加一位，總數(shù)可達46656張表睛琳，還不夠?
表的數(shù)量太多盒蟆，要創(chuàng)建這些表也是挺麻煩的，可以考慮在程序里往數(shù)據(jù)庫insert之前师骗，多執(zhí)行一句判斷表存在與否并創(chuàng)建表的語句历等，很實用，消耗也并不很大辟癌。
主鍵：依舊要考慮的寒屯，在這個系統(tǒng)中，主鍵是用戶ID+時間戳黍少，單純的時間戳或自動編號也能用寡夹，但查詢時要記得帶上用戶名用于定位表。
查看更多：
原文地址 http://www.cnblogs.com/luyucheng/p/6290567.html?tdsourcetag=s_pctim_aiomsg
MySQL優(yōu)化
MySQL各存儲引擎
MySQL鎖詳解
MySQL事務(wù)
MySQL索引類型
