文章內(nèi)容

MySQL 5.1 分區(qū)表學習筆記

發(fā)布時間: 2012/8/29 18:04:36

MySQL 從 5.1.x 的版本開始支持分區(qū)表，直到現(xiàn)在的最新版本 5.1.56 分區(qū)表已經(jīng)比較成熟，并且該版本也是很穩(wěn)定的 MySQL 版本。另外，MySQL 5.5開始支持RANGE COLUMNS和LIST COLUMNS的分區(qū)，也就是說非整型的列不再需要通過函數(shù)轉(zhuǎn)化為整型，同時也可以對多個列進行分區(qū)。

由于分區(qū)功能并不是在存儲引擎完成的，因此大部分常見的引擎都支持，例如 InnoDB、MyISAM 和 NDB 等，但 CSV、FEDERATED和MERGE等不支持。并且僅支持水平分區(qū)，不支持垂直分區(qū)。
-

分區(qū)表的優(yōu)勢可想而知，正如官方的參考手冊中所提到的：與單個磁盤或文件系統(tǒng)分區(qū)相比，可以存儲更多的數(shù)據(jù)；一些查詢可以得到極大的優(yōu)化，這主要是借助于滿足一個給定WHERE 語句的數(shù)據(jù)可以只保存在一個或多個分區(qū)內(nèi)，這樣在查找時就不用查找其他剩余的分區(qū)；涉及到例如SUM() 和 COUNT()這樣聚合函數(shù)的查詢，可以很容易地進行并行處理；通過跨多個磁盤來分散數(shù)據(jù)查詢，來獲得更大的查詢吞吐量等等。

MySQL 支持四種類型的分區(qū)：
1、RANGE 分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)；
2、LIST 分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇；
3、HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算；
4、KEY 分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL 服務器提供其自身的哈希函數(shù)。

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

下面通過 RANGE 分區(qū)的實例操作學習分區(qū)表的所支持的操作，稍候?qū)⒔榻B HASH 分區(qū)的實例操作：
首先，可以通過使用SHOW VARIABLES命令來確定MySQL是否支持分區(qū)（注意：mysql> 為提示符）
mysql> show variables like '%partition%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| have_partition_engine | YES |
+-----------------------+-------+
如果 value 值為 YES，則說明可以繼續(xù)接下來的操作。

按照官方手冊中提供的例子（稍有改動），創(chuàng)建 RANGE 類型的分區(qū)表：
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)
)

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

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

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

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

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

再創(chuàng)建 HASH 分區(qū)表
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

增加分區(qū)數(shù)量
mysql> alter table employees2 add partition partitions 1;

減少分區(qū)數(shù)量
mysql> alter table employees2 coalesce partition 1;

注意：“ALTER TABLE ... REORGANIZE PARTITION”不能用于按照HASH或HASH分區(qū)的表。

同樣，也可以優(yōu)化上述的兩張表
mysql> alter table employees rebuild partition p0,p1;

注意：“ALTER TABLE ... REORGANIZE PARTITION”也能讓分區(qū)的數(shù)據(jù)文件重建。

查看 SQL 執(zhí)行計劃
mysql> explain partitions select * from employees;

同時，MySQL 也支持子分區(qū)，也可以每個RANGE分區(qū)的數(shù)據(jù)和索引都使用一個單獨的磁盤。
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語法，因此上述分區(qū)表的數(shù)據(jù)和索引文件分開放置是無效的。