MySQL水平分区表实际操作总结

原文链接：http://shuailan.iteye.com/blog/1056247

MySQL分区表操作代码(本案例按月分区):

1. 创建分区表

CREATE TABLE `表名` (
  	`EQUIPMENTID` char(17) NOT NULL,
  	`ATTRIBUTEID` char(4) NOT NULL,
  	`VALUE` varchar(20) NOT NULL,
  	`COLLECTTIME` datetime NOT NULL
) 
ENGINE=InnoDB (适用大部分引擎,可根据需要调整)
DEFAULT CHARSET=latin1(编码可根据需要修改) 
PARTITION BY RANGE (to_days(COLLECTTIME)) 
(PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')),
PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) , 
PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) ,
PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) , 
PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) ,
PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) , 
PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) ,
PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) , 
PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) ,
PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) , 
PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')),
PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')),
PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')),
PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')),
PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')),
PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),
PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')),
PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')),
PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')),
PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')),
PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')),
PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')),
PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')),
PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')),
PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE );

CREATE TABLE `表名` (
  	`EQUIPMENTID` char(17) NOT NULL,
  	`ATTRIBUTEID` char(4) NOT NULL,
  	`VALUE` varchar(20) NOT NULL,
  	`COLLECTTIME` datetime NOT NULL
) 
ENGINE=InnoDB (适用大部分引擎,可根据需要调整)
DEFAULT CHARSET=latin1(编码可根据需要修改) 
PARTITION BY RANGE (to_days(COLLECTTIME)) 
(PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')),
PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) , 
PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) ,
PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) , 
PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) ,
PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) , 
PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) ,
PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) , 
PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) ,
PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) , 
PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')),
PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')),
PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')),
PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')),
PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')),
PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),
PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')),
PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')),
PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')),
PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')),
PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')),
PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')),
PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')),
PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')),
PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE );

2.为现有表创建分区

Sql代码

alter table 表名
PARTITION BY RANGE (to_days(COLLECTTIME)) 
(PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')),
PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) , 
PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) ,
PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) , 
PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) ,
PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) , 
PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) ,
PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) , 
PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) ,
PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) , 
PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')),
PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')),
PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')),
PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')),
PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')),
PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),
PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')),
PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')),
PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')),
PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')),
PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')),
PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')),
PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')),
PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')),
PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE );

如果表中已有数据,分区时会自动进行分区存储,不必担心数据丢失或者手动分类数据.

3. 删除表中的指定分区

Sql代码

ALTER TABLE 表名 DROP PARTITION 分区名;  

删除分区会导致分区数据丢失,建议先备份.

4. 追加表分区

Sql代码

ALTER TABLE 表名 DROP PARTITION pmax;  
ALTER TABLE 表名  
ADD PARTITION (  
PARTITION p201201 VALUES LESS THAN (to_days('2012-2-1')),  
PARTITION pmax VALUES LESS THAN MAXVALUE);  

需要先删除MAXVALUE分区后增加分区后再重建MAXVALUE分区,删除前需要先备份MAXVALUE分区数据.

5.查看标分区信息

Sql代码

SELECT   
        partition_name part,    
        partition_expression expr,    
        partition_description descr,    
        table_rows    
FROM   
        INFORMATION_SCHEMA.partitions    
WHERE   
        TABLE_SCHEMA = schema()   
        AND TABLE_NAME='表名';  

6. 查看查询语句涉及分区信息

Sql代码

1. 
   

该操作只查看查询语句相关的分区信息,不会返回查询结果.

以上是我对MySQL的初体验总结,没啥心得体会,只有一点点成就感,希望和大家分享.

另外分区表尚存在问题:

1,是否可将分区表设置在不同硬盘,innodb可行?

2,是否可根据多条件进行水平分区,类似group by 列1,列2...

3,是否能将分区设置成不同引擎,例如当前使用中的分区为innodb,老的分区使用MyISAM.