MySQL分區(qū)類型

博文大綱：

成都創(chuàng)新互聯(lián)公司10多年成都企業(yè)網(wǎng)站定制服務(wù);為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁設(shè)計(jì)及高端網(wǎng)站定制服務(wù),成都企業(yè)網(wǎng)站定制及推廣,對(duì)衛(wèi)生間隔斷等多個(gè)領(lǐng)域擁有豐富的網(wǎng)站維護(hù)經(jīng)驗(yàn)的網(wǎng)站建設(shè)公司。

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

---

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

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

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

1、RANGE分區(qū)

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

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

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

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

如果最后一個(gè)分區(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ū)的指令如下（注：當(dāng)分區(qū)被刪除，那么分區(qū)所存儲(chǔ)的數(shù)據(jù)也將被刪除，慎用?。。。?/p>

mysql> alter table employees drop partition p2;

2、LIST分區(qū)

LIST分區(qū)類似于RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來進(jìn)行選擇。LIST分區(qū)通過使用“partition by list（expr）”來實(shí)現(xiàn)，其中“expr”是某列值或一個(gè)基于某個(gè)列值、并返回一個(gè)整數(shù)值的表達(dá)式，然后通過“values in（value_list）”的方式來定義每個(gè)分區(qū)，其中“value_list”是一個(gè)通過逗號(hào)分隔的整數(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)建的表中，如果插入一個(gè)store_id為22（不在定義的分列表中）的數(shù)據(jù)，那么將會(huì)插入失敗，如下：

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

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

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

3、HASH分區(qū)

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

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

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

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

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

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

返回結(jié)果如下：

上面的例子并不能把數(shù)據(jù)均勻的分布到各個(gè)分區(qū)，因?yàn)榘凑誝EAR函數(shù)進(jìn)行的，該值本身是離散的。如果對(duì)連續(xù)的值進(jìn)行HASH分區(qū)，如自增長的主鍵，則可以較好地將數(shù)據(jù)平均分布。

4、key分區(qū)

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

創(chuàng)建一個(gè)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個(gè)分區(qū)，也就是說，現(xiàn)在t_key這個(gè)表一共有9個(gè)分區(qū)了。

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

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

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

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

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

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

3）提高性能上

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

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

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

5） 其他區(qū)別

• 都能提高mysql的性高，在高并發(fā)狀態(tài)下都有一個(gè)良好的表現(xiàn)。
• 分表和分區(qū)不矛盾，可以相互配合的，對(duì)于那些大訪問量，并且表數(shù)據(jù)比較多的表，我們可以采取分表和分區(qū)結(jié)合的方式，訪問量不大，但是表數(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)建子表。

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

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

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

然后創(chuàng)建表時(shí)，指定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');

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

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

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

分享名稱：MySQL分區(qū)類型
本文鏈接：http://bm7419.com/article40/pcegho.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供標(biāo)簽優(yōu)化、Google、網(wǎng)站策劃、靜態(tài)網(wǎng)站、網(wǎng)站建設(shè)、定制開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)