mysql分区操作

--创建分区(按时间年)
ALTER TABLE ad_site_trace PARTITION BY RANGE (YEAR(visit_time))
(PARTITION p0 VALUES LESS THAN (2011),
PARTITION p1 VALUES LESS THAN (2012)  );

--创建分区(按id进行哈希),分成100个区
ALTER TABLE ad_site_trace PARTITION BY HASH(id)
PARTITIONS 100;

--指标数值表分区
ALTER TABLE site_statisticdata_day PARTITION BY HASH(id)
PARTITIONS 100;

EXPLAIN SELECT COUNT(id) FROM ad_site_trace WHERE id >10000
--重建分区
ALTER TABLE ad_site_trace REBUILD PARTITION ( p0,p1);

 

 

表分区

增加新的分区  ALTER TABLE ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

ALTER TABLE members REORGANIZE PARTITION p0 INTO (

    PARTITION s0 VALUES LESS THAN (1960),

    PARTITION s1 VALUES LESS THAN (1970)

)；

SHOW CREATE TABLE trb3\G


已存在表增加分区

alter table temp_justin partition by range(to_days(create_time))
(
partition p1012 values less than (to_days('2011-01-01')),
partition p1101 values less than (to_days('2011-02-01')),
partition p1102 values less than (to_days('2011-03-01')),
partition p1103 values less than (to_days('2011-04-01')),
partition p1104 values less than (to_days('2011-05-01')),
partition p1105 values less than (to_days('2011-06-01')),
partition p1106 values less than (to_days('2011-07-01')),
partition p1107 values less than (to_days('2011-08-01')),
partition p1108 values less than (to_days('2011-09-01')),
partition p1109 values less than (to_days('2011-10-01')),
partition p11010 values less than (to_days('2011-11-01')),
partition p11011 values less than (to_days('2011-12-01')),
partition p11012 values less than (to_days('2012-01-01'))
);


PARTITION BY RANGE (year(c3)) (PARTITION p0VALUESLESS THAN (1995),

　　-> PARTITION p1 VALUES LESS THAN (1996) ,PARTITIONp2 VALUES LESS THAN (1997) ,

　　-> PARTITION p3 VALUES LESS THAN (1998) ,PARTITIONp4 VALUES LESS THAN (1999) ,

　　-> PARTITION p5 VALUES LESS THAN (2000) ,PARTITIONp6 VALUES LESS THAN (2001) ,

　　-> PARTITION p7 VALUES LESS THAN (2002) ,PARTITIONp8 VALUES LESS THAN (2003) ,

　　-> PARTITION p9 VALUES LESS THAN (2004) ,PARTITIONp10 VALUES LESS THAN (2010),

　　-> PARTITION p11 VALUES LESS THAN MAXVALUE);


    mysql> CREATE TABLE part_date3
   2.
          ->      (  c1 int default NULL,
   3.
          ->  c2 varchar(30) default NULL,
   4.
          ->  c3 date default NULL) engine=myisam
   5.
          ->      partition by range (to_days(c3))
   6.
          -> (PARTITION p0 VALUES LESS THAN (to_days('1995-01-01')),
   7.
          -> PARTITION p1 VALUES LESS THAN (to_days('1996-01-01')) ,
   8.
          -> PARTITION p2 VALUES LESS THAN (to_days('1997-01-01')) ,
   9.
          -> PARTITION p3 VALUES LESS THAN (to_days('1998-01-01')) ,
  10.
          -> PARTITION p4 VALUES LESS THAN (to_days('1999-01-01')) ,
  11.
          -> PARTITION p5 VALUES LESS THAN (to_days('2000-01-01')) ,
  12.
          -> PARTITION p6 VALUES LESS THAN (to_days('2001-01-01')) ,
  13.
          -> PARTITION p7 VALUES LESS THAN (to_days('2002-01-01')) ,
  14.
          -> PARTITION p8 VALUES LESS THAN (to_days('2003-01-01')) ,
  15.
          -> PARTITION p9 VALUES LESS THAN (to_days('2004-01-01')) ,
  16.
          -> PARTITION p10 VALUES LESS THAN (to_days('2010-01-01')),
  17.
          -> PARTITION p11 VALUES LESS THAN MAXVALUE );
  18.