MySQL分區和分表

標籤：article   new   unsigned   技術   user   href   html   問題   獨立   

一、概念

1.為什麼要分表和分區？
日常開發中我們經常會遇到大表的情況，所謂的大表是指儲存了百萬級乃至千萬級條記錄的表。這樣的表過於龐大，導致資料庫在查詢和插入的時候耗時太長，效能低下，如果涉及聯集查詢的情況，效能會更加糟糕。分表和表分區的目的就是減少資料庫的負擔，提高資料庫的效率，通常點來講就是提高表的增刪改查效率。
2.什麼是分表？
分表是將一個大表按照一定的規則分解成多張具有隔離儲存區 (Isolated Storage)空間的實體表，我們可以稱為子表，每個表都對應三個檔案，MYD資料檔案，.MYI索引檔案，.frm表結構檔案。這些子表可以分布在同一塊磁碟上，也可以在不同的機器上。app讀寫的時候根據事先定義好的規則得到對應的子表名，然後去操作它。
3.什麼是分區？
分區和分表相似，都是按照規則分解表。不同在於分表將大表分解為若干個獨立的實體表，而分區是將資料分段劃分在多個位置存放，可以是同一塊磁碟也可以在不同的機器。分區後，表面上還是一張表，但資料散列到多個位置了。app讀寫的時候操作的還是大表名字，db自動去組織分區的資料。
4.mysql分表和分區有什麼聯絡呢？
（1）都能提高mysql的性高，在高並髮狀態下都有一個良好的表現。
（2）分表和分區不矛盾，可以相互配合的，對於那些大訪問量，並且表資料比較多的表，我們可以採取分表和分區結合的方式（如果merge這種分表方式，不能和分區配合的話，可以用其他的分表試），訪問量不大，但是表資料很多的表，我們可以採取分區的方式等。
（3）分表技術是比較麻煩的，需要手動去建立子表，app服務端讀寫時候需要計運算元表名。採用merge好一些，但也要建立子表和配置子表間的union關係。
（4）表分區相對於分表，操作方便，不需要建立子表。

二、分區

1.分區的類型：
（1）Range：把連續區間按範圍劃分
例：

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：隨機分配，分區數固定
例：

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

（4）Key：類似Hash，區別是只支援1列或多列,且mysql提供自身的Hash函數
例：

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

2.分區管理
（1）新增分區

ALTER TABLE sale_dataADD PARTITION (PARTITION p201710 VALUES LESS THAN (201711));

（2）刪除分區

--當刪除了一個分區，也同時刪除了該分區中所有的資料。ALTER TABLE sale_data DROP PARTITION p201710;

（3）分區的合并
下面的SQL，將p201701 - p201709 合并為3個分區p2017Q1 - p2017Q3

ALTER TABLE sale_dataREORGANIZE 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.分區應該注意的事項：
（1）做分區時，要麼不定義主鍵，要麼把分區欄位加入到主鍵中。
（2）分區欄位不能為NULL，要不然怎麼確定分區範圍呢，所以盡量NOT NULL

三、分表
1.垂直分表
把原來有很多列的表拆分成多個表，原則是：
（1）把常用、不常用的欄位分開放
（2）把大欄位獨立存放在一個表中
2.水平分表
為瞭解決單表資料量過大的問題，每個水平分割表的結構完全一致。
例：
（1）按時間結構
如果業務系統對時效性較高，比如新聞發布系統的文章表，可以把資料庫設計成時間結構，按時間分有幾種結構：
（a）平板式
表類似：

article_201701article_201702article_201703

用年來分還是用月可自定，但用日期的話表就太多了，也沒這必要。一般建議是按月分就可以。
這種分法，其難處在於，假設我要列20條資料，結果這三張表裡都有2條，那麼業務上很有可能要求讀三次表。如果時間長了，有幾十張表，而每張表是0條，那不就是要讀完整個系統的表才行麼?另外這個結構，要作分頁是比較難實現的。
主鍵：在這個系統中，主鍵是13位帶毫秒的時間戳記，不要用自動編號，否則難以通過主鍵定位到表，也可以在查詢時帶上時間，但比較煩瑣。
（b）歸檔式
表類似：

article_oldarticle_new

為瞭解決平板式的缺點，可以採用時間歸檔式設計，可以看到這個系統只有兩張表。一張是舊文章表，一張是新文章表，新文章表放2個月的資訊，每天定期把2
個月中的最早一天的文章歸入舊錶中。這樣一方面可以解決效能問題，因為一般新聞發布系統讀取的都是新的內容，舊的內容讀取少;第二可以委婉地解決功能問
題，比如平板式所說的問題，在歸檔式中最多也只需要讀2張表就完成了。
歸檔式的缺點在於舊錶容量還是相對比較大，如果業務允許，可對舊錶中的超舊內容進行再歸檔或直接清理掉。
（2）按版塊結構
如果按照文章的所屬版塊進行拆表，比如新聞、體育版塊拆表，一方面可以使每個表資料量分離，另一方面是各版塊之間相互影響可降到最低。假如新聞版塊的資料表損壞或需要維護，並不會影響到體育版塊的正常工作，從而降低了風險。版塊結構同時常用於bbs這樣的系統。
板塊結構也有幾種分法：
（a）對應式
對於版塊數量不多，而且較為固定的形式，就直接對應就好。比如新聞版塊，可以分出新聞的目錄表，新聞的文章表等。

news_categorynews_articlesports_categorysports_article

可看到每一個版塊都對應著一組相同的表結構，好處就是一目瞭然。在功能上，因為版塊之間還是有一些隔閡，所以需要聯集查詢的需求不多，開發上比時間結構的方式要輕鬆。
主鍵：依舊要考慮的，在這個系統中，主鍵是版塊+時間戳記，單純的時間戳記或自動編號也能用，查詢時要記得帶上版塊用於定位表。
（b）冷熱式
對應式的缺點是，如果版塊數量很大而且不確定，那要分出的表數量就太多了。舉個例子：百度貼吧，如果按一個詞條一個表設計，那得有多少張表呢?
用這樣的方式吧。

tieba_汽車tieba_飛機tieba_火箭tieba_unite

這個表汽車、火箭表是屬於熱門表，定義為建立的版塊放在unite表裡面，待到其超過一萬張主貼的時候才開對應表結構。因為在貼吧這種系統中，冷門版塊
肯定比熱門版塊多得多，這些冷門版塊通常只有幾張文章，為它們開表也太浪費了;同時熱門版塊數量和訪問量等，又比冷門版塊多得多，非常有特點。
unite表還可以擴充成雜湊表，利用詞條的md5編碼，可以分成n張表，我算了一下，md5前一位可分36張表，兩位即是1296張表，足夠了。

tieba_unite_abtieba_unite_ac

（3）按雜湊結構
雜湊結構通常用於部落格之類的基於使用者的場合，在部落格這樣的系統裡有幾個特點，1是使用者數量非常多，2是每個使用者發的文章數量都較少，3是使用者發文章不定
期，4是每個使用者發得不多，但總量仍非常之大。基於這些特點，用以上所說的任何一種分表方式都不合適，一沒有固定的時效不宜用時間拆，二使用者很多，而且還
偏偏都是冷門，所以也不宜用版塊(使用者)拆。
雜湊結構在上面有所提及，既然按每個使用者不好直接拆，那就把一群使用者歸進一個表好了。

blog_aablog_abblog_ac

如上所說，md5取前兩位雜湊可以達到1296張表，如果覺得不夠，那就再加一位，總數可達46656張表，還不夠?
表的數量太多，要建立這些表也是挺麻煩的，可以考慮在程式裡往資料庫insert之前，多執行一句判斷表存在與否並建立表的語句，很實用，消耗也並不很大。
主鍵：依舊要考慮的，在這個系統中，主鍵是使用者ID+時間戳記，單純的時間戳記或自動編號也能用，但查詢時要記得帶上使用者名稱用於定位表。

參考資料：
http://blog.csdn.net/shmnh/article/details/44055059
http://blog.csdn.net/hijiankang/article/details/9173825
http://blog.csdn.net/feihong247/article/details/8100960
http://niehan.blog.techweb.com.cn/archives/279.html

MySQL分區和分表