文章內容

MySQL 5.1 分區表學習筆記

發布時間: 2012/8/29 18:04:36

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語法，因此上述分區表的數據和索引文件分開放置是無效的。