mysql的分割槽和分表

奮程式序猿發表於2016-12-20
MySql

分割槽

分割槽就是把一個資料表的檔案和索引分散儲存在不同的物理檔案中。

mysql支援的分割槽型別包括Range、List、Hash、Key，其中Range比較常用：

RANGE分割槽：基於屬於一個給定連續區間的列值，把多行分配給分割槽。

LIST分割槽：類似於按RANGE分割槽，區別在於LIST分割槽是基於列值匹配一個離散值集合中的某個值來進行選擇。

HASH分割槽：基於使用者定義的表示式的返回值來進行選擇的分割槽，該表示式使用將要插入到表中的這些行的列值進行計算。這個函式可以包含MySQL 中有效的、產生非負整數值的任何表示式。

KEY分割槽：類似於按HASH分割槽，區別在於KEY分割槽只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函式。必須有一列或多列包含整數值。

案例：

建立一個user 表 以id進行分割槽 id 小於10的在user_1分割槽id小於20的在user_2分割槽

create table user(
    id int not null auto_increment,
    username varchar(10),
    primary key(id)
)engine = innodb charset=utf8
partition by range (id)(
    partition user_1 values less than (10),
    partition user_2 values less than (20)
);

建立後新增分割槽：

maxvalue 表示最大值   這樣大於等於20的id 都出儲存在user_3分割槽

alter table user add partition(
    partition user_3 values less than maxvalue
);

刪除分割槽：

alter  table user drop partition user_3;

現在開啟mysql的資料目錄

可以看見多了user#P#user_1.ibd 和user#P#user_2.ibd  這兩個檔案

如果表使用的儲存引擎是MyISAM型別，就是：

user#P#user_1.MYD,user#P#user_1.MYI和user#P#user_2.MYD,user#P#user_2.MYI

由此可見，mysql通過分割槽把資料儲存到不同的檔案裡，同時索引也是分割槽的。相對於未分割槽的表來說，分割槽後單獨的資料庫檔案索引檔案的大小都明顯降低，效率則明顯的提示了。可以插入一條資料然後分析查詢語句驗證一下：

insert into user values(null,'測試');

explain partitions select * from user where id =1;

可以看見僅僅在user_1分割槽執行了這條查詢。

具體分割槽的效率是多少還需要看資料量。在分割槽時可以通過 DATA DIRECTORY 和 　　INDEX DIRECTORY 選項吧不同的分割槽放到不同的磁碟上進一步提高系統的I/O吞吐量。

分割槽型別的選擇，通常使用Range型別，不過有些情況，比如主從結構中，主伺服器很少使用‘select’查詢，在主伺服器上使用 Range型別分割槽通常沒有太大的意義，此時使用Hash型別分割槽更好例如：

partition by hash(id) partitions 10;

當插入資料時，根據id吧資料平均散到各個分割槽上，由於檔案小，效率高，更新操作變得更快。

在分割槽時使用的欄位,通常情況下按時間欄位分割槽，具體情況以需求而定。劃分應用的方式有很多種，比如按時間或使用者，哪種用的多，就選擇哪種分割槽。如果使用主從結構可能就更加靈活，有的從伺服器使用時間，有的使用使用者。不過如此一來當執行查詢時，程式應該負責選擇真確的伺服器查詢，寫個mysql proxy指令碼應該可以透明的實現。

分割槽的限制：

1.主鍵或者唯一索引必須包含分割槽欄位，如primary key (id,username),不過innoDB的大組建效能不好。

2.很多時候，使用分割槽就不要在使用主鍵了，否則可能影響效能。

3.只能通過int型別的欄位或者返回int型別的表示式來分割槽，通常使用year或者to_days等函式（mysql 5.6 對限制開始放開了）。

4.每個表最多1024個分割槽，而且多分割槽會大量消耗記憶體。

5.分割槽的表不支援外來鍵，相關的邏輯約束需要使用程式來實現。

6.分割槽後，可能會造成索引失效，需要驗證分割槽可行性。

分割槽模式詳解：

* Range（範圍） – 這種模式允許DBA將資料劃分不同範圍。例如DBA可以將一個表通過年份劃分成三個分割槽，80年代（1980's）的資料，90年代（1990's）的資料以及任何在2000年（包括2000年）後的資料。

CREATE TABLE users (  
       id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
       usersname VARCHAR(30) NOT NULL DEFAULT '',  
       email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY RANGE (id) (  
       PARTITION p0 VALUES LESS THAN (3000000),  
      
       PARTITION p1 VALUES LESS THAN (6000000), 
     
       PARTITION p2 VALUES LESS THAN (9000000),  
     
       PARTITION p3 VALUES LESS THAN MAXVALUE     
);

在這裡，將使用者表分成4個分割槽，以每300萬條記錄為界限，每個分割槽都有自己獨立的資料、索引檔案的存放目錄。

還可以將這些分割槽所在的物理磁碟分開完全獨立，可以提高磁碟IO吞吐量。

CREATE TABLE users (  
       id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
       usersname VARCHAR(30) NOT NULL DEFAULT '',  
       email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY RANGE (id) (  
       PARTITION p0 VALUES LESS THAN (3000000)  
       DATA DIRECTORY = '/data0/data'  
       INDEX DIRECTORY = '/data0/index',  
  
       PARTITION p1 VALUES LESS THAN (6000000)  
       DATA DIRECTORY = '/data1/data'  
       INDEX DIRECTORY = '/data1/index',  
  
       PARTITION p2 VALUES LESS THAN (9000000)  
       DATA DIRECTORY = '/data2/data'  
       INDEX DIRECTORY = '/data2/index',  
  
       PARTITION p3 VALUES LESS THAN MAXVALUE     
       DATA DIRECTORY = '/data3/data'   
       INDEX DIRECTORY = '/data3/index'  
);

 

* List（預定義列表） – 這種模式允許系統通過DBA定義的列表的值所對應的行資料進行分割。例如：DBA根據使用者的型別進行分割槽。 

CREATE TABLE user (  
     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
     name VARCHAR(30) NOT NULL DEFAULT '' ,
     user_type   int not null
)  
PARTITION BY LIST (user_type ) (  
     PARTITION p0 VALUES IN (0,4,8,12) , 
     PARTITION p1 VALUES IN (1,5,9,13) ,  
     PARTITION p2 VALUES IN (2,6,10,14),  
     PARTITION p3 VALUES IN (3,7,11,15)   
);

分成4個區,同樣可以將分割槽設定的獨立的磁碟中。



* Key（鍵值） – 上面Hash模式的一種延伸，這裡的Hash Key是MySQL系統產生的。 

CREATE TABLE user (  
     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
     name VARCHAR(30) NOT NULL DEFAULT '',  
     email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY KEY (id) PARTITIONS 4 (  
     PARTITION p0,  
     PARTITION p1,  
     PARTITION p2,  
     PARTITION p3
);

 

* Hash（雜湊） – 這中模式允許DBA通過對錶的一個或多個列的Hash Key進行計算，最後通過這個Hash碼不同數值對應的資料區域進行分割槽，。例如DBA可以建立一個對錶主鍵進行分割槽的表。 

CREATE TABLE user (  
     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
     username VARCHAR(30) NOT NULL DEFAULT '',  
     email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY HASH (id) PARTITIONS 4 (  
     PARTITION p0 ,  
     PARTITION p1,  
     PARTITION p2,
     PARTITION p3  
);

分成4個區,同樣可以將分割槽設定的獨立的磁碟中。


= 分割槽管理 =


刪除分割槽

ALERT TABLE users DROP PARTITION p0;

重建分割槽

  RANGE 分割槽重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

將原來的 p0,p1 分割槽合併起來，放到新的 p0 分割槽中。

LIST 分割槽重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));

將原來的 p0,p1 分割槽合併起來，放到新的 p0 分割槽中。

HASH/KEY 分割槽重建

ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;

用 REORGANIZE 方式重建分割槽的數量變成2，在這裡數量只能減少不能增加。想要增加可以用 ADD PARTITION 方法。

新增分割槽

新增 RANGE 分割槽  

alter table user add partition(partition user_3 values less than maxvalue);

 

新增 LIST 分割槽 

ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19));

新增 HASH/KEY 分割槽

ALTER TABLE users ADD PARTITION PARTITIONS 8;

將分割槽總數擴充套件到8個。

給已有的表加上分割槽

alter table results partition by RANGE (month(ttime))   
(PARTITION p0 VALUES LESS THAN (1),  
PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) ,  
PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) ,  
PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) ,  
PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) ,  
PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11),  
PARTITION p11 VALUES LESS THAN (12),  
PARTITION P12 VALUES LESS THAN (13) );

分表

分表和分割槽類似，區別是，分割槽是把一個邏輯表檔案分成幾個物理檔案後進行儲存，而分表則是把原先的一個表分成幾個表。進行分表查詢時可以通過union或者檢視。

分表又分垂直分割和水平分割，其中水平分分割最為常用。水平分割通常是指切分到另外一個資料庫或表中。例如對於一個會員表，按對3的模進行分割:

table = id%3

如果id%3 = 0 則將使用者資料放入到user_0表中，如id%3=1就放入user_1表中，依次類推。

在這裡有個問題，這個uid應該是所有會員按序增長的，可他是怎麼得到的呢？使用auto_increment是不行的，這樣就用到序列了。

對於一些流量統計系統，其資料量比較大，並且對過往資料的關注度不高，這時按年、月、日進行分表，將每日統計資訊放到一個以日期命名的表中；或者按照增量進行分表，如每個表100萬資料，超過100萬就放入第二個表。還可以按Hash進行分表，但是按日期和取模餘數分表最為常見，也容易擴充套件。

分表後可能會遇到新的問題，那就是查詢，分頁和統計。通用的方法是在程式中進行處理，輔助檢視。

使用分表案例：

案例1：

對會員資料對5取模，放在5個表中，如何查詢會員資料：

1.已知id查詢會員資料，程式碼如下：

<?php
//查詢單個會員資料
$customer_table = 'customer'.$id%5;
$sql = 'select * from '.$customer_table.' where customer_id = '.$id;
//查詢全部會員資料
$sql = '';
$tbale = ['customer0','customer1','customer2','customer3','customer4'];
foreach($table as $v){
$sql .='select * from '.$v.' union';
}
$sql = substr($sql,0,-5);

?>

這樣就可以查詢某一個會員的資料或者全部會員的資料了。同理，分頁的話在這個大集合中使用limit 就可以了。但是這樣做又會有一個疑問，把所有的表連起來查詢和部分表沒有什麼區別，其實在實際的應用中，不可能檢視所有的會員資料，一次檢視20個然後分頁。完全沒有必要做union，僅查詢一個表就可以了，唯一需要考慮的是在分頁零界點時的銜接。其實，這個銜接是否那麼重要？即使偶爾出現幾條資料的差異，也不會對業務有任何的影響。

2.和其它表進行關聯和1類似。

3.根據會員姓名搜尋使用者資訊。在這種需求下，需要搜尋所有的表，並對結果進行彙總。雖然這樣做產生了多次的查詢，但並不代表效率低。好的sql語句執行10次也比差的sql語句執行一次快。

案例2：

在一個流量監控系統中，由於網路流量巨大，統計資料很龐大，需要按天分表。先要得到任意日，周，月的資料。

1.需要任意一天的資料。直接查詢當天的資料表即可。

2.需要幾天的資料。分愛查詢這幾天的資料，然後進行彙總。

3.需要查詢一週的資料。對一週的資料定期彙總到一個week表，從這個表裡面查詢。這個彙總過程可以由一個外部程式完成，也可以由定期的指令碼完成。

4.查詢一個月的資料。彙總本月所有的資料到month表，在此表查詢。

5.查詢5個月內的詳細資料。不支援。僅支援最多3個月的詳細資料。資料沒3個月已歸檔一次。在大資料的處理中，必須做出一些犧牲。對於超出3個月的資料，僅提供統計資料，詳細資料需要檢視歸檔。90天或者180天，給資料儲存設個界限，也是大部分這類系統的常規做法，超出90天的資料就不再提供資料詳單了。比如，移動的通話記錄最多儲存半年，即180天，超過這個範圍的資料不在提供查詢。如果你實在需要，可能就要聯絡移動的工程師了。

分表前應該儘量按照實際業務來分表，參考依據就是哪些欄位在查詢中起到作用，那就這些欄位來分表，並且需要在分表前就估算好規模，也就是先確定好規則在分表。

對於分表後的操作，依然是聯合查詢，檢視等基本操作，或者使用merge引擎合併資料並在此表中查詢。複雜一些操作需要藉助儲存過程來完成，藉助外部工具實現對分表的管理。

對於比較龐大的資料，不論是否進行分表，都必須考慮功能和效率的平衡性，並在功能上做出讓步。我們不能事事遷就使用者，而應該對某些影響效率的功能做出限制。例如移動公司的180天限制、論壇禁止對老帖進行回覆等。

 

相關文章