概述

?之前，看到分區，我捏個去，好高大上喲。昨天終于知道了分區是個啥玩意，也不過如此，今天總結一下，好記性不如爛筆頭嘛。
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操作符進行分區定義。

例一：

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中文網其它相關文章！