MySQL 5.1 分區表學習筆記

MySQL 從 5.1.x 的版本開始支援分區表，直到現在的最新版本 5.1.56 分區表已經比較成熟，並且該版本也是很穩定的 MySQL 版本。另外，MySQL 5.5開始支援RANGE COLUMNS和LIST COLUMNS的分區，也就是說非整型的列不再需要通過函數轉化為整型，同時也可以對多個列進行分區。

由於資料分割函數並不是在儲存引擎完成的，因此大部分常見的引擎都支援，例如 InnoDB、MyISAM 和 NDB 等，但 CSV、FEDERATED和MERGE等不支援。並且僅支援水平資料分割，不支援垂直資料分割。

分區表的優勢可想而知，正如官方的參考手冊中所提到的：與單個磁碟或檔案系統分區相比，可以儲存更多的資料；一些查詢可以得到極大的最佳化，這主要是藉助於滿足一個給定WHERE 語句的資料可以只儲存在一個或多個分區內，這樣在尋找時就不用尋找其他剩餘的分區；涉及到例如SUM() 和 COUNT()這樣彙總函式的查詢，可以很容易地進行平行處理；通過跨多個磁碟來分散資料查詢，來獲得更大的查詢輸送量等等。

MySQL 支援四種類型的分區：
1、RANGE 分區：基於屬於一個給定連續區間的列值，把多行分配給分區；
2、LIST 分區：類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇；
3、HASH分區：基於使用者定義的運算式的傳回值來進行選擇的分區，該運算式使用將要插入到表中的這些行的列值進行計算；
4、KEY 分區：類似於按HASH分區，區別在於KEY分區只支援計算一列或多列，且MySQL 伺服器提供其自身的雜湊函數。

MySQL 5.1 提供了許多修改分區表的方式。添加、刪除、重新定義、合并或拆分已經存在的分區是可能的。所有這些操作都可以通過使用ALTER TABLE 命令的分區擴充來實現。關於如何添加和刪除分區的處理，RANGE和LIST分區非常相似，HASH和KEY分區也非常相似。基於這個原因，我們先介紹RANGE和HASH這兩種分區的管理。

下面通過 RANGE 分區的執行個體操作學習分區表的所支援的操作，稍候將介紹 HASH 分區的執行個體操作：
首先，可以通過使用SHOW VARIABLES命令來確定MySQL是否支援分區（注意：mysql> 為提示符）
mysql> show variables like '%partition%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| have_partition_engine | YES |
+-----------------------+-------+
如果 value 值為 YES，則說明可以繼續接下來的操作。

按照官方手冊中提供的例子（稍有改動），建立 RANGE 類型的分區表：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (21)
)

增加分區，名稱為 p3
mysql> alter table employees add partition ( partition p3 values less than (31));

刪除名稱為 p3 分區
mysql> alter table employees drop partition p3;

拆分名稱為 p2 分區為 p2 p3 兩個分區，注意被拆分的分區只能是分區表的最後一個分區
mysql> alter table employees reorganize partition p2 into (partition p2 values less than (21), partition p3 values less than (31));

合并 名稱為 p2 p3 的兩個分區為一個分區 p2 ，注意合并後分區 p2 的值不能小於原來 p3 分區的值
mysql> alter table employees reorganize partition p2,p3 into (partition p2 values less than (31));

注意：
1、如果不存在手工擴充分區的問題，可以使用 “VALUES LESS THAN MAXVALUE” 定義分區。
2、LIST分區沒有類似如 “VALUES LESS THAN MAXVALUE” 這樣的包含其他值在內的定義，將要匹配的任何值都必須在值列表中找到。
3、值為 NULL 的情況，如果是RANGE分區則MySQL 會將該值放到最左邊的分區，因為 NULL 值被視為小於任何一個非 NULL 值得，這和 Oracle 剛好相反；如果是LIST分區則必須明確的指出哪個分區放 NULL 值。

再建立 HASH 分區表
CREATE TABLE employees2 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4

增加分區數量
mysql> alter table employees2 add partition partitions 1;

減少分區數量
mysql> alter table employees2 coalesce partition 1;

注意：“ALTER TABLE ... REORGANIZE PARTITION”不能用於按照HASH或HASH分區的表。

同樣，也可以最佳化上述的兩張表
mysql> alter table employees rebuild partition p0,p1;

注意：“ALTER TABLE ... REORGANIZE PARTITION”也能讓分區的資料檔案重建。

查看 SQL 執行計畫
mysql> explain partitions select * from employees;

同時，MySQL 也支援子分區，也可以每個RANGE分區的資料和索引都使用一個單獨的磁碟。
CREATE TABLE employees3 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (YEAR(hired))
SUBPARTITION BY HASH(TO_DAYS(hired)) (
PARTITION p0 VALUES LESS THAN (2010) (
SUBPARTITION s0 DATA DIRECTORY = '/disk0/data' INDEX DIRECTORY = '/disk0/idx',
SUBPARTITION s1 DATA DIRECTORY = '/disk1/data' INDEX DIRECTORY = '/disk1/idx'
),
PARTITION p1 VALUES LESS THAN (2011) (
SUBPARTITION s2 DATA DIRECTORY = '/disk2/data' INDEX DIRECTORY = '/disk2/idx',
SUBPARTITION s3 DATA DIRECTORY = '/disk3/data' INDEX DIRECTORY = '/disk3/idx'
),
PARTITION p2 VALUES LESS THAN (2012) (
SUBPARTITION s4 DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s5 DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx'
)
);
需要注意的是 InnoDB 儲存引擎會忽略 DATA DIRECTORY 和 INDEX DIRECTORY文法，因此上述分區表的資料和索引檔案分開放置是無效的。

詳細請參考官方的手冊。