本文总结个这段时间研究MySQL水平分区表总结,列举分区表的相关操作和通过实际数据对分区表读写的性能比较.

在网上看了很多文章,都太过于概念,注意集中在介绍分区表的优点,而不注重时间操作,跟大学教授论文似的,唯一由于的一片文章和大家分享一下吧http://fanqiang.chinaunix.net/db/mysql/2006-05-08/4135.shtml.

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(`时间字段名`))

(PARTITION pminVALUES LESS THAN (to_days('2010-01-01')),

PARTITION p201001VALUES LESS THAN (to_days('2010-02-01')) ,

PARTITION p201002VALUES LESS THAN (to_days('2010-03-01')) ,

PARTITION p201003VALUES LESS THAN (to_days('2010-04-01')) ,

PARTITION p201004VALUES LESS THAN (to_days('2010-05-01')) ,

PARTITION p201005VALUES LESS THAN (to_days('2010-06-01')) ,

PARTITION p201006VALUES LESS THAN (to_days('2010-07-01')) ,

PARTITION p201007VALUES LESS THAN (to_days('2010-08-01')) ,

PARTITION p201008VALUES LESS THAN (to_days('2010-09-01')) ,

PARTITION p201009VALUES LESS THAN (to_days('2010-10-01')) ,

PARTITION p201010VALUES LESS THAN (to_days('2010-11-01')),

PARTITION p201011VALUES LESS THAN (to_days('2010-12-01')),

PARTITION p201012VALUES LESS THAN (to_days('2011-01-01')),

PARTITION pmax VALUESLESS THAN MAXVALUE );

?

2.????????为现有表创建分区:

????????alter table 表名

PARTITION BY RANGE(to_days(`时间字段名`))

(PARTITION pminVALUES LESS THAN (to_days('2010-01-01')),

PARTITION p201001VALUES LESS THAN (to_days('2010-02-01')) ,

PARTITION p201002VALUES LESS THAN (to_days('