詳細(xì)說(shuō)明mysql中的分區(qū)

概述

?之前，看到分區(qū)，我捏個(gè)去，好高大上喲。昨天終于知道了分區(qū)是個(gè)啥玩意，也不過(guò)如此，今天總結(jié)一下，好記性不如爛筆頭嘛。
MySQL從5.1開始支持分區(qū)功能。分區(qū)一句話就是：把一張表按照某種規(guī)則（range/list/hash/key等）分成多個(gè)區(qū)域（頁(yè)/文件）保存。對(duì)mysql應(yīng)用開發(fā)來(lái)說(shuō)，分區(qū)與不分區(qū)是沒(méi)區(qū)別的（即對(duì)應(yīng)用是透明的）。如同突圍戰(zhàn)中的“化整為零”。MySQL支持大部分的存儲(chǔ)引擎（如：MyISAM、InnoDB、Memory等）創(chuàng)建分區(qū)，不支持MERGE和CSV來(lái)創(chuàng)建分區(qū)。同一個(gè)分區(qū)表中的所有分區(qū)必須是同一個(gè)存儲(chǔ)引擎。做一個(gè)引例：

#創(chuàng)建一個(gè)5個(gè)hash分區(qū)的myisam表
CREATE TABLE `test`.`partition_t1`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY HASH(MONTH(birth_date))
PARTITIONS 5;

引例結(jié)果

分區(qū)作用

• 可以存儲(chǔ)更多的數(shù)據(jù)（系統(tǒng)單個(gè)文件最大限制）

• 優(yōu)化查詢，在where子句中，如果包含分區(qū)條件，只需要掃描一個(gè)或部分分區(qū)來(lái)提高查詢效率。在涉及sum()這類函數(shù)時(shí)候， 可以在分區(qū)上并行處理，最后匯總結(jié)果。

• 對(duì)于過(guò)期或不需要的數(shù)據(jù)，可以刪除相關(guān)分區(qū)來(lái)快速刪除數(shù)據(jù)。

• 跨多個(gè)磁盤來(lái)分散數(shù)據(jù)查詢，單表的并發(fā)能力提高了，磁盤I/O性能也提高了。

分區(qū)類型

分為4種：

• range分區(qū)：基于一個(gè)給定的連續(xù)區(qū)間范圍，把數(shù)據(jù)分配到不同的分區(qū)中。

• list分區(qū)：類似range分區(qū)，區(qū)別在于list是基于枚舉出的值列表分區(qū)，range是根據(jù)范圍來(lái)分區(qū)的。

• hash分區(qū)：基于給定的分區(qū)個(gè)數(shù)，把數(shù)據(jù)分配到不同分區(qū)（取模/線性）

• key分區(qū)：類似于hash分區(qū)。

MySQL5.1中range,list,hash分區(qū)要求分區(qū)鍵必須是int。MySQL5.5及以上，支持非整型的range和list分區(qū)，即：range columns 和 list columns。
注意：無(wú)論哪種分區(qū)，要么分區(qū)表上沒(méi)有主鍵/唯一鍵，要么分區(qū)鍵必須有一個(gè)是主鍵/唯一鍵。

1.range分區(qū)

range分區(qū)是利用取值范圍（區(qū)間）劃分分區(qū)，區(qū)間要連續(xù)并且不能互相重疊，使用values less than操作符進(jìn)行分區(qū)定義。

例一：

CREATE TABLE `test`.`partition_t2`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY RANGE(id)(
   PARTITION t21 VALUES LESS THAN (10),
   PARTITION t22 VALUES LESS THAN (20),
   PARTITION t23 VALUES LESS THAN MAXVALUE
);

上例中定義了一個(gè)包含3個(gè)分區(qū)（t21,t22,t23）的range分區(qū)表，這個(gè)有點(diǎn)類似與高級(jí)語(yǔ)言中的switch語(yǔ)句。解釋如下：當(dāng)id<10的時(shí)候，在t21分區(qū)；當(dāng)20>id>=10的時(shí)候，在t22分區(qū)；當(dāng)id>=20時(shí)候，在t23分區(qū)。

例二：

CREATE TABLE `test`.`partition_t3`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY RANGE COLUMNS(birth_date)(
   PARTITION t31 VALUES LESS THAN ('1996-01-01'),
   PARTITION t32 VALUES LESS THAN ('2006-01-01'),
   PARTITION t33 VALUES LESS THAN ('2038-01-01')
);

MySQL5.5改進(jìn)range分區(qū)，提供range columns分區(qū)支持非整數(shù)分區(qū)。

2.list分區(qū)

list分區(qū)創(chuàng)建離散的值列表（類似mysql中的enum類型數(shù)據(jù)）來(lái)劃分分區(qū)，使用values in操作符來(lái)分區(qū)。list分區(qū)不必要聲明任何特定的順序的。list有很多方面類似于range。

CREATE TABLE `test`.`partition_t4`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY LIST(id)(
   PARTITION t41 VALUES IN (1,2),
   PARTITION t42 VALUES IN (3,6),
   PARTITION t43 VALUES IN (5,4),
   PARTITION t44 VALUES IN (7,8)
);

上面的例子是，當(dāng)id為1或2，在t41分區(qū)；當(dāng)id為3或6，在t42分區(qū)，以此類推...

3.hash分區(qū)

hash分區(qū)主要用來(lái)分散熱點(diǎn)讀取，確保數(shù)據(jù)在預(yù)定確定個(gè)數(shù)分區(qū)中盡可能的平均分布。一個(gè)表執(zhí)行hash分區(qū)，mysql會(huì)對(duì)分區(qū)鍵應(yīng)用一個(gè)散列函數(shù)，以此確定數(shù)據(jù)應(yīng)該放在n個(gè)分區(qū)中的哪一個(gè)分區(qū)。hash分區(qū)支持兩種散列函數(shù)（分區(qū)方式）：取模算法（默認(rèn)hash分區(qū)方式）和線性的2的冪的運(yùn)算法則（liner hash 分區(qū)）。

常規(guī)hash分區(qū)

#頂部引例就是常規(guī)hash分區(qū)

• mysql不推薦使用涉及多列的hash表達(dá)式。

• 常規(guī)hash在分區(qū)管理上帶來(lái)的代價(jià)太大了，不適合靈活變動(dòng)的分區(qū)的需求。參見(jiàn)：一致性哈希算法

• 因?yàn)槌Ｒ?guī)hash分區(qū)在管理上的問(wèn)題，所有mysql引入線性hash分區(qū)。

  線性hash分區(qū)

  CREATE TABLE `test`.`partition_t5`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
  ) ENGINE=MYISAM
  PARTITION BY LINEAR HASH(id)
  PARTITIONS 5;

  上例中，創(chuàng)建一個(gè)5個(gè)分區(qū)的線性hash分區(qū)。

• 線性hash分區(qū)優(yōu)點(diǎn)：在分區(qū)維護(hù)上，mysql能夠處理更加迅速；

• 線性hash分區(qū)缺點(diǎn)：分區(qū)各個(gè)分區(qū)之間數(shù)據(jù)分布不太均衡。

4.key分區(qū)

• hash分區(qū)允許用戶自定義的表達(dá)式，而key分區(qū)不允許使用用戶自定義的表達(dá)式。

• hash分區(qū)只支持整數(shù)分區(qū)，key分區(qū)支持除了blob或text類型之外的其他數(shù)據(jù)類型分區(qū)。

• 與hash分區(qū)不同，創(chuàng)建key分區(qū)表的時(shí)候，可以不指定分區(qū)鍵，默認(rèn)會(huì)選擇使用主鍵/唯一鍵作為分區(qū)鍵，沒(méi)有主鍵/唯一鍵,必須指定分區(qū)鍵。

CREATE TABLE `test`.`partition_t6`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY LINEAR KEY(email)
PARTITIONS 5;

columns 與子分區(qū)

1.columns分區(qū)

columns 包括range columns與list columns 支持非整型的分區(qū)鍵。columns分區(qū)支持多列分區(qū)。

CREATE TABLE `test`.`partition_t7`(  
  `a` INT UNSIGNED NOT NULL,
  `b` INT UNSIGNED NOT NULL
)
PARTITION BY RANGE COLUMNS(a,b)(
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,10),
    PARTITION p2 VALUES LESS THAN (10,20),
    PARTITION p3 VALUES LESS THAN (10,35),
    PARTITION p4 VALUES LESS THAN (10,MAXVALUE),
    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

判斷依據(jù)：（a<10） or ((1=10) and (10<10))。

2.子分區(qū)

子分區(qū)是分區(qū)表中對(duì)每一個(gè)分區(qū)的再次分割，又被稱為復(fù)合分區(qū)。MySQL從MySQL5.1開始支持對(duì)通過(guò)range和list的表再進(jìn)行子分區(qū)，子分區(qū)即可以hash分區(qū)，也可以使用key分區(qū)。子分區(qū)適合保存非常大量的數(shù)據(jù)記錄。

CREATE TABLE partition_t8(id INT,purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(TO_DAYS(purchased))
SUBPARTITIONS 2(
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

mysql子分區(qū)

分區(qū)管理

MySQL5.1提供添加、刪除、重定義、合并、拆分分區(qū)命令。

1.range或list分區(qū)

#刪除分區(qū)
alter table partition_t8 drop partition p2;
#添加一個(gè)分區(qū)
alter table partition_t8 add partition(
    partition p4 values less than (2030)
    )
#重定義一個(gè)分區(qū)
alter table partition_t8 reorganize partition p3 into(
    partition p2 values less than (2005),
    partition p3 values less than (2015)
);

• 只能從range分區(qū)列表最大端增加分區(qū)。

• 增加list分區(qū)，不能添加一個(gè)包含現(xiàn)有分區(qū)值列表中的任意值分區(qū)，也就是說(shuō)對(duì)一個(gè)固定的分區(qū)鍵值，必須指定并且只能指定一個(gè)唯一的分區(qū)。

• 重新定義range分區(qū)，只能夠重新定義相鄰的分區(qū)，同時(shí)重新定義的分區(qū)區(qū)間必須和原分區(qū)區(qū)間覆蓋相同的區(qū)間。

2.hash或key分區(qū)

#減少分區(qū)數(shù),(如將分區(qū)數(shù)減少到2)
alter table partition_t8 coalesce partition 2;
#增加分區(qū)數(shù)(如：為分區(qū)數(shù)增加了8)
alter table partiton_t8 add partition partitions 8;

• coalesce不能用來(lái)增加分區(qū)數(shù)量。

【相關(guān)推薦】

1. 免費(fèi)mysql在線視頻教程

2. MySQL最新手冊(cè)教程

3. 數(shù)據(jù)庫(kù)設(shè)計(jì)那些事

以上就是詳細(xì)介紹mysql中的分區(qū)的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！