MySQL 5.1 分區(qū)表學(xué)習(xí)筆記

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

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

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

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

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

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

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

按照官方手冊(cè)中提供的例子（稍有改動(dòng)），創(chuàng)建 RANGE 類(lèi)型的分區(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ū)，名稱(chēng)為 p3
mysql> alter table employees add partition ( partition p3 values less than (31));

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

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

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

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

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

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

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

同時(shí)，MySQL 也支持子分區(qū)，也可以每個(gè)RANGE分區(qū)的數(shù)據(jù)和索引都使用一個(gè)單獨(dú)的磁盤(pán)。
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 存儲(chǔ)引擎會(huì)忽略 DATA DIRECTORY 和 INDEX DIRECTORY語(yǔ)法，因此上述分區(qū)表的數(shù)據(jù)和索引文件分開(kāi)放置是無(wú)效的。