mysql 主表存hash和子表的名字_MySQL表分区

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

三、分区表的限制