MySQL5.5创建线性Hash分区表,并将表文件分布到不同的物理磁盘上:http://blog.csdn.net/ylqmf/article/details/7357463
MySQL5.1开始支持表分区!!
一、COLUMNSPartitioning(columns分区特性)详见:http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns.html
RANGCOLUMNSpartitioning和LISTCOLUMNSpartitioning
#LISTCOLUMNSALTER TABLE expenses
PARTITION BY LIST COLUMNS(category)
(
PARTITION p01 VALUES IN ( 'lodging', 'food'),
PARTITION p02 VALUES IN ( 'flights', 'ground transportation'),
PARTITION p03 VALUES IN ( 'leisure', 'customer entertainment'),
PARTITION p04 VALUES IN ( 'communications'),
PARTITION p05 VALUES IN ( 'fees')
);
#RANGCOLUMNSCREATE TABLE t2
(
dt DATE
)
PARTITION BY RANGE COLUMNS (dt)(
PARTITION p01 VALUES LESS THAN ('2007-01-01'),
PARTITION p02 VALUES LESS THAN ('2008-01-01'),
PARTITION p03 VALUES LESS THAN ('2009-01-01'),
PARTITION p04 VALUES LESS THAN (MAXVALUE)
);
二、子分区:(对range/list再进行hash/key子分区)
InMySQL5.5,itispossibletosubpartitiontablesthatarepartitionedbyRANGEorLIST.SubpartitionsmayuseeitherHASHorKEYpartitioning.Thisisalsoknownascompositepartitioning。
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(TO_DAYS(purchased))
SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
注意子分区的名字:sp0|1
等价于==>
CREATE TABLE `ts` (
`id` int(11) DEFAULT NULL,
`purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (YEAR(purchased))
SUBPARTITION BY HASH (to_days(purchased))
(PARTITION p0 VALUES LESS THAN (1990)
(SUBPARTITION s0 ENGINE = InnoDB,
SUBPARTITION s1 ENGINE = InnoDB),
PARTITION p1 VALUES LESS THAN (2000)
(SUBPARTITION s2 ENGINE = InnoDB,
SUBPARTITION s3 ENGINE = InnoDB),
PARTITION p2 VALUES LESS THAN MAXVALUE
(SUBPARTITION s4 ENGINE = InnoDB,
SUBPARTITION s5 ENGINE = InnoDB)) */
1.这3个分区中的每一个分区——p0,p1,和p2——又被进一步分成了2个子分区(hash分区),最前的2个分区只保存“purchased”列中值小于1990的那些记录。子分区是分区表中每个分区的再次分割。
2.每个SUBPARTITION子句必须包括(至少)子分区的一个名字。否则,你可能要对该子分区设置任何你所需要的选项,或者允许该子分区对那些选项采用其默认的设置执行失败:
重新定义分区:数据会合并和分散
分散:ALTERTABLEtest.membersREORGANIZEPARTITIONp0INTO(PARTITIONs0VALUESLESSTHAN(1960),PARTITIONs1VALUESLESSTHAN(1970))
合并:ALTERTABLEtest.membersREORGANIZEPARTITIONs0,s1INTO(PARTITIONp0VALUESLESSTHAN(1970))
三、分区表的限制