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

林灣村龍貓發表於2017-01-20
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;複製程式碼

(18)mysql 中的分割槽（開發篇完）
引例結果

分割槽作用

  • 可以儲存更多的資料（系統單個檔案最大限制）
  • 優化查詢，在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分割槽。10的時候，在t21分割槽；當20>

例二：

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
);複製程式碼

(18)mysql 中的分割槽（開發篇完）
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不能用來增加分割槽數量。

分表參考

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

相關文章