(18)mysql中的分區（開發篇完）

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

分區作用

分區類型

分為4種：

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分區

線性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分區。

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

分區管理

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

2.hash或key分區

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

分表參考

mysql分表的3種方法
mysql —— 分表分區（1）