MySQL分區(qū)類型

博文大綱：

• 1、RANGE分區(qū)
• 2、LIST分區(qū)
• 3、HASH分區(qū)
• 4、key分區(qū)
• 5、MySQL分表和分區(qū)的區(qū)別
• 6、附加：如何實現(xiàn)將分區(qū)放在不同的目錄下進行存儲

MySQL分區(qū)類型如下：

• RANFGE分區(qū)
• LIST分區(qū)
• HASH分區(qū)
• key分區(qū)

上面的四種分區(qū)的條件必須是整形，如果不是整形需要通過函數(shù)將其轉(zhuǎn)換為整形。

1、RANGE分區(qū)

RANGE分區(qū)是基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。這些區(qū)間要連續(xù)且不能相互重疊，使用values less than操作符來進行定義。

創(chuàng)建一個RANGE分區(qū)方式的表：

mysql> create table employees (
    -> id int not null,
    -> fname varchar(30),
    -> lname varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '9999-12-31',
    -> job_code int not null,
    -> store_id int not null
    -> )
    -> partition by range (store_id) (
    -> partition p0 values less than (6),
    -> partition p1 values less than (11),
    -> partition p2 values less than (16),
    -> partition p3 values less than (21)
    -> );

按照這種分區(qū)方案，當插入的記錄store_id小于6，會保存在p0這個分區(qū)，如果store_id小于11則會將數(shù)據(jù)保存在p1這個分區(qū).....以此類推。但是在上面的分區(qū)方案中，定義的最后一個分區(qū)是小于21的，那么，如果此時有store_id大于或等于21的記錄，則會插入失敗，因為數(shù)據(jù)庫不知道應(yīng)該插入到哪個分區(qū)中，避免這種情況的發(fā)生，就需要在指定分區(qū)方案或者新增一個分區(qū)，最后一個分區(qū)指定的范圍應(yīng)該為maxvalue，而不是一個具體的值。

根據(jù)上面創(chuàng)建的表，可以添加一個范圍為maxvalue的分區(qū)，命令如下：

mysql> alter table employees add partition (partition p4 values less than maxvalue);

如果最后一個分區(qū)指定的范圍是maxvalue，那么后期想要添加分區(qū)的話，需要使用以下方式（使用類似于分區(qū)合并的指令）：

mysql> alter table employees reorganize partition p4 into
    -> ( partition p03 values less than (25),
    -> partition p04 values less than maxvalue
    -> );

刪除分區(qū)的指令如下（注：當分區(qū)被刪除，那么分區(qū)所存儲的數(shù)據(jù)也將被刪除，慎用！?。。?/p>

mysql> alter table employees drop partition p2;

2、LIST分區(qū)

LIST分區(qū)類似于RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇。LIST分區(qū)通過使用“partition by list（expr）”來實現(xiàn)，其中“expr”是某列值或一個基于某個列值、并返回一個整數(shù)值的表達式，然后通過“values in（value_list）”的方式來定義每個分區(qū)，其中“value_list”是一個通過逗號分隔的整數(shù)列表。

創(chuàng)建LIST分區(qū)類型舉例：

mysql> create table employees (
    -> id int not null,
    -> fname varchar(30),
    -> lname varchar(30),
    -> hired date not null default '1970-01-01',
    -> separated date not null default '2100-12-31',
    -> job_code int,
    -> store_id int
    -> )
    -> partition by list(store_id)(
    -> partition pNorth values in (3,5,6,9,17),
    -> partition pEast values in (1,2,10,11,19,20),
    -> partition pWest values in (4,12,13,14,18),
    -> partition pCentral values in (7,8,15,16)
    -> );

在上面創(chuàng)建的表中，如果插入一個store_id為22（不在定義的分列表中）的數(shù)據(jù)，那么將會插入失敗，如下：

失敗的原因就是：LIST分區(qū)沒有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在內(nèi)的定義。將要匹配的任何值都
必須在值列表中找到。

解決辦法就是增加有這個值的分區(qū)，如下:

mysql>  alter table employees add partition (partition p4 values ins (22,23,24));

3、HASH分區(qū)

這種模式允許DBA通過對表的一個或多個列的Hash Key進行計算，最后通過這個Hash碼不同數(shù)值對應(yīng)的數(shù)據(jù)區(qū)域進行分區(qū)。 hash分區(qū)的目的是將數(shù)據(jù)均勻的分布到預(yù)先定義的各個分區(qū)中，保證各分區(qū)的數(shù)據(jù)量大致一致。在range和list分區(qū)中，必須明確指定一個給定的列值或列值集合應(yīng)該保存在哪個分區(qū)中；而在hash分區(qū)中，MySQL自動完成這些工作，用戶所要定一個列值或者表達式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。

1）創(chuàng)建hash分區(qū)的表

mysql> create table t_hash(a int(11),b datetime) partition by hash(year(b)) partitions 4;

上述創(chuàng)建表的命令中，使用了year函數(shù)來提取b列中的年份來做分區(qū)的依據(jù)，通過partitions來指定需要4個分區(qū)。

2）插入測試數(shù)據(jù)

mysql> insert into t_hash values(1,'2010-04-01');

上述指令插入的數(shù)據(jù)將被存放在p2分區(qū)，計算方法如下：

查看information_schema庫中的partitions也可以查看到p2分區(qū)中會有1條記錄，如下：

mysql> select * from information_schema.partitions where table_schema='test001' and table_name='t_hash'\G

返回結(jié)果如下：

上面的例子并不能把數(shù)據(jù)均勻的分布到各個分區(qū)，因為按照YEAR函數(shù)進行的，該值本身是離散的。如果對連續(xù)的值進行HASH分區(qū)，如自增長的主鍵，則可以較好地將數(shù)據(jù)平均分布。

4、key分區(qū)

key分區(qū)和hash分區(qū)相似，不同在于hash分區(qū)是用戶自定義函數(shù)進行分區(qū)，key分區(qū)使用mysql數(shù)據(jù)庫提供的函數(shù)進行分區(qū)，NDB cluster使用MD5函數(shù)來分區(qū)，對于其他存儲引擎mysql使用內(nèi)部的hash函數(shù)。

創(chuàng)建一個key分區(qū)的表：

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

如果后期需要新增分區(qū)，使用以下指令即可：

mysql> alter table t_key add partition partitions 5;

上述指令是新增了5個分區(qū)，也就是說，現(xiàn)在t_key這個表一共有9個分區(qū)了。

注： mysql-5.5開始支持COLUMNS分區(qū)，可視為RANGE和LIST分區(qū)的進化，COLUMNS分區(qū)可以直接使用非整形數(shù)據(jù)進行分區(qū)。COLUMNS分區(qū)支持以下數(shù)據(jù)類型： 所有整形，如INT SMALLINT
TINYINT BIGINT。FLOAT和DECIMAL則不支持。 日期類型，如DATE和DATETIME。其余日期類型不支持。字符串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支持。 COLUMNS可以使用多個列進行分區(qū)。

5、MySQL分表和分區(qū)的區(qū)別

1） 實現(xiàn)方式上

• mysql的分表是真正的分表，一張表分成很多表后，每一個小表都是完整的一張表，都對應(yīng)三個文件，一個.MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結(jié)構(gòu)文件。
• 分區(qū)不一樣，一張大表進行分區(qū)后，它還是一張表，不會變成二張表，但是它存放數(shù)據(jù)的區(qū)塊變多了

2）數(shù)據(jù)處理上

• 分表后，數(shù)據(jù)都是存放在分表里，總表只是一個外殼，存取數(shù)據(jù)發(fā)生在一個一個的分表里面。
• 分區(qū)呢，不存在分表的概念，分區(qū)只不過把存放數(shù)據(jù)的文件分成了許多小塊，分區(qū)后的表呢，還是一張表，數(shù)據(jù)處理還是由自己來完成。

3）提高性能上

• 分表后，單表的并發(fā)能力提高了，磁盤I/O性能也提高了。因為查尋一次所花的時間變短了，如果出現(xiàn)高并發(fā)的話，總表可以根據(jù)不同的查詢，將并發(fā)壓力分到不同的小表里面。
• mysql提出了分區(qū)的概念，主要是想突破磁盤I/O瓶頸，想提高磁盤的讀寫能力，來增加mysql性能。
• 在這一點上，分區(qū)和分表的側(cè)重點不同，分表重點是存取數(shù)據(jù)時，如何提高mysql并發(fā)能力上；而分區(qū)呢，如何突破磁盤的讀寫能力，從而達到提高mysql性能的目的。

4）實現(xiàn)的難易度上

• 分表的方法有很多，用merge來分表，是最簡單的一種方式。這種方式跟分區(qū)難易度差不多，并且對程序代碼來說可以做到透明的。如果是用其他分表方式就比分區(qū)麻煩了。
• 分區(qū)實現(xiàn)是比較簡單的，建立分區(qū)表，和建平常的表沒什么區(qū)別，并且對開代碼端來說是透明的。

5） 其他區(qū)別

• 都能提高mysql的性高，在高并發(fā)狀態(tài)下都有一個良好的表現(xiàn)。
• 分表和分區(qū)不矛盾，可以相互配合的，對于那些大訪問量，并且表數(shù)據(jù)比較多的表，我們可以采取分表和分區(qū)結(jié)合的方式，訪問量不大，但是表數(shù)據(jù)很多的表，我們可以采取分區(qū)的方式等。
• 分表技術(shù)是比較麻煩的，需要手動去創(chuàng)建子表，app服務(wù)端讀寫時候需要計算子表名。采用merge好一些，但也要創(chuàng)建子表和配置子表間的union關(guān)系。
• 表分區(qū)相對于分表，操作方便，不需要創(chuàng)建子表。

6、附加：如何實現(xiàn)將分區(qū)放在不同的目錄下進行存儲

先在本地創(chuàng)建好需要的目錄：

[root@mysql ~]# mkdir /data
[root@mysql ~]# chown -R mysql.mysql /data

然后創(chuàng)建表時，指定data directory就可以，如下：

mysql> create table user(
    -> id int not null auto_increment,
    -> name varchar(30) not null default '',
    -> primary key(id)) default charset=utf8 auto_increment=1
    -> partition by range(id)(
    -> partition p1 values less than (3) data directory '/data/area1',
    -> partition p2 values less than (6) data directory '/data/area2',
    -> partition p3 values less than (9) data directory '/data/area3');

查看本地目錄，則會發(fā)現(xiàn)自動創(chuàng)建了相應(yīng)的存放數(shù)據(jù)的目錄，如下：

注：使用mysql默認的存儲引擎inodb時候，只需要指定data directory 就可以，因為inodb的數(shù)據(jù)和索引在一個文件中。但是創(chuàng)建表格時指定engine=myisam時，修改分區(qū)的存儲位置，需要同時指定data directory和index directory。

———————— 本文至此結(jié)束，感謝閱讀 ————————