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ú)效的。
詳細(xì)請(qǐng)參考官方的手冊(cè)。 本文出自:億恩科技【mszdt.com】
服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)!虛擬主機(jī)域名注冊(cè)頂級(jí)提供商!15年品質(zhì)保障!--億恩科技[ENKJ.COM]
|