詳細介紹mysql中的分割區

概述

 之前，看到分區，我捏個去，好高大上喲。昨天終於知道了分區是個啥玩意，也不過如此，今天總結一下，好記性不如爛筆頭嘛。
MySQL從5.1開始支援分割區功能。分區一句話就是：把一張表格依照某種規則（range/list/hash/key等）分成多個區域（頁/檔）儲存。 對mysql應用開發來說，分區與不分區是沒區別的（即對應用是透明的）。如同突圍戰中的「化整為零」。 MySQL支援大部分的儲存引擎（如：MyISAM、InnoDB、Memory等）建立分割區，不支援MERGE和CSV來建立分割區。同一個分區表中的所有分區必須是同一個儲存引擎。做一個引例：

#创建一个5个hash分区的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;

引例結果

分割區作用

• 可以儲存更多的資料（系統單一檔案最大限制）

  最佳化查詢，在
where子句
• 中，如果包含分割區條件，只需要掃描一個或部分分區來提高查詢效率。在涉及sum()這類

  函數時候， 可以在分區上並行處理，最後總結結果。

• 對於過期或不需要的數據，可以

  刪除

  相關分割區來快速刪除資料。

跨多個磁碟來分散資料查詢，單表的並發能力提高了，磁碟I/O效能也提高了。

分割區類型

• 分為4種：

• range分割區：基於一個給定的連續區間範圍，把資料分配到不同的分區。

• list分割區：類似range分割區，差別在於list是基於列舉的值清單分割區，range是根據範圍來分割的。

• hash分區：基於給定的分區個數，把資料分配到不同分區（取模/線性）

key分區：類似hash分區。 MySQL5.1中range,list,hash分割區要求分割區鍵必須是int。 MySQL5.5及以上，支援非整型的range和list分割區，即：range columns 和 list columns。

注意：無論哪種分區，要么分區表上沒有主鍵/唯一鍵，要么分區鍵必須有一個是主鍵/唯一鍵。

1.range分割區

range分割區是利用取值範圍（區間）分割分割區，區間要連續且不能互相重疊，使用

values less than運算子

進行分割定義。

<a href="http://www.php.cn/code/5745.html" target="_blank">例一：</a>

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
);

上例中定義了一個包含3個分割區（t21,t22,t23）的range分割區表，

這個有點類似與高階語言中的switch語句

。解釋如下：當id<10的時候，在t21分區；當20>id>=10的時候，在t22分區；當id>=20時候，在t23分區。

例二：

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改進range分割區，提供range columns分割區支援非整數

分割區。

2.list分區

list分區建立離散的值清單（類似mysql中的enum類型資料）來劃分分區，使用

values in操作符來分割區。 list分區不必要聲明任何特定的順序的。 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)
);

上面的例子是，當id為1或2，在t41分區；當id為3或6，在t42分區，以此類推...

3 .hash分區

hash分區主要用來分散熱點讀取，確保資料在預定確定個數分區中盡可能的平均分佈。一個表執行hash分區，mysql會對分區鍵套用一個雜湊函數，以此確定資料應該放在n個分區中的哪一個分區。 hash分區支援兩種雜湊函數（分區方式）：

取模演算法（預設hash分區方式）

和

線性的2的冪的運演算法則（liner hash 分區）
• 。

  常規hash分割區

  #顶部引例就是常规hash分区

• mysql不建議使用涉及多列的hash

  表達式

  。

• 常規hash在分區管理上帶來的代價太大了，不適合靈活變動的分區的需求。請參閱：一致性雜湊演算法
  因為常規hash分區在管理上的問題，所有mysql引入線性hash分區。
• 線性hash分割區

  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;

  上例中，建立一個5個分割區的線性hash分割區。 ############線性hash分割區優點：在分割區維護上，mysql能夠處理得更加迅速；###

• 线性hash分区缺点：分区各个分区之间数据分布不太均衡。

4.key分区

• hash分区允许用户自定义的表达式，而key分区不允许使用用户自定义的表达式。

• hash分区只支持整数分区，key分区支持除了blob或text类型之外的其他数据类型分区。

• 与hash分区不同，创建key分区表的时候，可以不指定分区键，默认会选择使用主键/唯一键作为分区键，没有主键/唯一键,必须指定分区键。

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 与子分区

1.columns分区

columns 包括range columns与list columns 支持非整型的分区键。columns分区支持多列分区。

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)
);

判断依据：（a<10） or ((1=10) and (10<10))。

2.子分区

子分区是分区表中对每一个分区的再次分割，又被称为复合分区。MySQL从MySQL5.1开始支持对通过range和list的表再进行子分区，子分区即可以hash分区，也可以使用key分区。子分区适合保存非常大量的数据记录。

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子分区

分区管理

MySQL5.1提供添加、删除、重定义、合并、拆分分区命令。

1.range或list分区

#删除分区
alter table partition_t8 drop partition p2;
#添加一个分区
alter table partition_t8 add partition(
    partition p4 values less than (2030)
    )
#重定义一个分区
alter table partition_t8 reorganize partition p3 into(
    partition p2 values less than (2005),
    partition p3 values less than (2015)
);

• 只能从range分区列表最大端增加分区。

• 增加list分区，不能添加一个包含现有分区值列表中的任意值分区，也就是说对一个固定的分区键值，必须指定并且只能指定一个唯一的分区。

• 重新定义range分区，只能够重新定义相邻的分区，同时重新定义的分区区间必须和原分区区间覆盖相同的区间。

2.hash或key分区

#减少分区数,(如将分区数减少到2)
alter table partition_t8 coalesce partition 2;
#增加分区数(如：为分区数增加了8)
alter table partiton_t8 add partition partitions 8;

• coalesce不能用来增加分区数量。

【相关推荐】

1. 免费mysql在线视频教程

2. MySQL最新手册教程

3. 数据库设计那些事

以上是詳細介紹mysql中的分割區的詳細內容。更多資訊請關注PHP中文網其他相關文章！