本文更新於2019-06-30，使用MySQL 5.7，操作系統為Deepin 15.4。分區類型可以使用SHOW PLUGINS查看是否安裝了分區插件。 MySQL創建分區表支持使用大部分存儲引擎，但不支持使用MERGE或CSV存儲引擎。同一個分區表的所有分區必須使用同一種存儲引擎。 MySQ ...

本文更新於2019-06-30，使用MySQL 5.7，操作系統為Deepin 15.4。

分區類型

可以使用SHOW PLUGINS查看是否安裝了分區插件。

MySQL創建分區表支持使用大部分存儲引擎，但不支持使用MERGE或CSV存儲引擎。同一個分區表的所有分區必須使用同一種存儲引擎。

MySQL分區作用於整個表的所有數據和索引，其索引一定是本地LOCAL索引。

無論那種分區類型，要麼分區表上沒有主鍵/唯一鍵，要麼分區表的主鍵/唯一鍵包含分區鍵。

分區表中的分區名不區分大小寫。

可以通過查詢information_schema.PARTITIONS得知分區中的行數，來間接確定記錄是插入到那一個分區中的：

SELECT PARTITION_NAME, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = tablename

Range分區

CREATE TABLE tablename (...) [[STORAGE] ENGINE=engine]
PARTITION BY RANGE [COLUMNS] (partitionkey[, ...]) (
PARTITION partitionname VALUES LESS THAN (value)|MAXVALUE
[, ...]
)

如不使用COLUMNS，則Range分區的分區鍵必需為INT類型的某一列，或某一返回INT類型的表達式。如使用COLUMNS，則為Range Columns分區，分區鍵可為非INT類型，可為多列分區，不能為表達式。

Range分區的區間必須連續且不能互相重疊，區間為左閉右開區間。每個分區都是按順序進行定義的，從最低到最高。

分區鍵的值如果是NULL則會被當作最小值來處理。

List分區

CREATE TABLE tablename (...) [[STORAGE] ENGINE=engine]
PARTITION BY LIST [COLUMNS] (partitionkey[, ...]) (
PARTITION partitionname VALUES IN (value[, ...])
[, ...]
)

如不使用COLUMNS，List分區的分區鍵必需為INT類型的某一列，或某一返回INT類型的表達式。如使用COLUMNS，則為List Columns分區，分區鍵可為非INT類型，可為多列分區，不能為表達式。

List分區的聲明不必按照特定的順序。

分區鍵的值如果是NULL則必須出現在分區定義的枚舉列表中。

Columns分區

Columns分區可分為Range Columns分區和List Columns分區，其分區鍵都支持整數、日期時間、字元串數據類型。

所有整數類型，即TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，不支持其他數值類型。
日期時間類型DATE和DATETIME。分區日期處理支持的函數有YEAR()、TO_DAYS()、TO_SECONDS()。
字元串類型CHAR、VARCHAR、BINARY、VARBINARY，不支持[*]TEXT和[*]BLOB。

Columns分區可使用一列或多列作為分區鍵，即支持多列分區，不支持表達式作為分區鍵。其分區鍵是基於元組的比較，即多列排序。

Hash分區

CREATE TABLE tablename (...) [[STORAGE] ENGINE=engine]
PARTITION BY [LINEAR] HASH(partitionkey)
PARTITIONS count

MySQL支持兩種Hash分區，常規Hash分區和線性Hash分區。常規Hash分區使用的是取模演算法，線性Hash分區使用的是一個線性的2的冪的運演算法則。常規Hash分區在分區管理（增加、刪除、合併、拆分分區）時代價較大，線程Hash分區在分區管理時能處理得更迅速，但各個分區之間數據分佈不太均勻。常規Hash分區將記錄保存到分區編號為MOD(partitionkey, count)的分區中，線性Hash分區保存的分區編號按照如下規則計算。當線性Hash分區的個數為2的冪的時候，其和常規Hash分區的分區結果是一致的。

找到下一個大於等於count的2的冪V = Power(2, Ceiling(Log(2, count)))，V有可能大於count。
設置N = partitionkey & (V - 1)。由上一步得，V - 1為一個所有位都為1的整數，運算結果N共有V種可能（0至V - 1）。
當N > count時，設置V = Ceiling(V / 2)，由第一步得知該值肯定小於count且為一個2的冪。使用新的V設置N = N & (V - 1)即為分區編號。

Hash分區的分區鍵必需為INT類型的某一列，或某一返回INT類型的表達式。

分區鍵的值如果是NULL則會將其當作零值處理。

Key分區

CREATE TABLE tablename (...) [[STORAGE] ENGINE=engine]
PARTITION BY [LINEAR] KEY ([partitionkey[, ...]])
PARTITIONS count

Key分區的分區鍵可為除[*]TEXT和[*]BLOB類型以外的一列或多列，不能為表達式。如未指定分區鍵，則使用主鍵作為分區鍵；若無主鍵，則選擇非空唯一鍵作為分區鍵。MySQL使用伺服器的HASH函數計算列的散列值。

分區鍵的值如果是NULL則會將其當作零值處理。

Key分區表不能執行ALTER TABLE DROP PRIMARY KEY來刪除主鍵，否則會返回錯誤：Field in list of fields for partition function not found in table。

Key分區使用LINEAR和Hash分區有相同的作用。

子分區

Range分區和List分區可再進行子分區（複合分區），子分區可以使用Hash分區或Key分區。

CREATE TABLE tablename (...) [[STORAGE] ENGINE=engine]
PARTITION BY RANGE|LIST [COLUMNS] (partitionkey1[, ...])
SUBPARTITION BY HASH|KEY (partitionkey2[, ...])
SUBPARTITIONS count
(
PARTITION partitionname VALUES {LESS THAN (value)|MAXVALUE}|{IN (value[, ...])}
[, ...]
)

PARTITION子句的VALUES根據分區是Range分區還是List分區填寫（下同）。

分區管理

Range分區和List分區的分區管理

刪除分區，會同時刪除分區中的數據：

ALTER TABLE tablename
DROP PARTITION partitionname

增加分區，Range分區只能添加分區至分區列表最大一端：

ALTER TABLE tablename
ADD PARTITION (
PARTITION partitionname VALUES {LESS THAN (value)|MAXVALUE}|{IN (value[, ...])}
)

重定義分區，不會丟失原有數據，可以用來拆分一個分區為多個分區，也可以用來合併多個相鄰分區（指定義語句相鄰）為一個分區或多個分區，同時重定義的分區範圍必需與原分區相同：

ALTER TABLE tablename
REORGANIZE PARTITION partitionname1[, ...] INTO (
PARTITION partitionname2 VALUES {LESS THAN (value)|MAXVALUE}|{IN (value[, ...])}
[, ...]
)

如不使用相鄰分區重定義，則會返回錯誤：When reorganizing a set of partitions they must be in consecutive order。

Hash分區和Key分區的分區管理

合併分區：

ALTER TABLE tablename
COALESCE PARTITION count

不能通過加大count值來增加分區的數量，否則會返回錯誤：Cannot remove all partitions, use DROP TABLE instead!。

增加分區，為新增count個分區，而不是增加到count個分區：

ALTER TABLE tablename
ADD PARTITION PARTITIONS count

MySQL學習筆記（14）：分區