MySQL5.5创建线性Hash分区表,并将表文件分布到不同的物理磁盘上:http://blog.csdn.net/ylqmf/article/details/7357463

MySQL5.1开始支持表分区！！

一、COLUMNSPartitioning(columns分区特性)详见：

RANGCOLUMNSpartitioning和LISTCOLUMNSpartitioning

#LISTCOLUMNS

ALTER TABLE expensesPARTITION 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'));

#RANGCOLUMNS

CREATE 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))

三、分区表的限制

详见：