mysql分区表管理

分区的几种方式

5.5以前的版本只支持指定int类型为分区键，如果不以int为分区键就要使用函数将字段类类型转换为int，5.5以前date类型只支持year(),to_days()函数

到5.5以后使用关键字colums可以支持整数、字符串、日期类型，其中

整数类型:tinyint、smallint、mediumint、int、bigint

日期类型：date、datetime

字符类型：char、varchar、binary、varbinary

其它类型也暂不支持作为分区键

对于分区键值为null的时候，mysql将null当作0或者最小值

Range分区：

  
  

   
   

    
    create table ranges( 
   
   
   
   

    
    　　id int(11), 
   
   
   
   

    
    　　money int(11) unsigned not null, 
   
   
   
   

    
    　　date datetime 
   
   
   
   

    
    　　)
   
   
   
   

    
    partition by range(year(date))( 
   
   
   
   

    
    　　partition p2007 values less than (2008), 
   
   
   
   

    
    　　partition p2008 values less than (2009), 
   
   
   
   

    
    　　partition p2009 values less than (2010), 
   
   
   
   

    
    　　partition p2010 values less than maxvalue 
   
   
   
   

    
    );
   
   
  
  

?

RANG COLUMN: 

  
  

   
   

    
    create table rangess( 
   
   
   
   

    
    　　id int(11), 
   
   
   
   

    
    　　money int(11) unsigned not null, 
   
   
   
   

    
    　　dates datetime
   
   
   
   

    
    　　)
   
   
   
   

    
    partition by range columns (dates)( 
   
   
   
   

    
    　　partition p2007 values less than ('2008-01-01 00:00:00'), 
   
   
   
   

    
    　　partition p2008 values less than ('2009-01-01 00:00:00'), 
   
   
   
   

    
    　　partition p2009 values less than ('2010-01-01 00:00:00'), 
   
   
   
   

    
    　　partition p2010 values less than maxvalue 
   
   
   
   

    
    );
   
   
  
  

List分区： 

  
  

   
   

    
    create table lists( 
   
   
   
   

    
    　　a int(11), 
   
   
   
   

    
    　　b int(11) 
   
   
   
   

    
    　　)
   
   
   
   

    
    partition by list (b) (
   
   
   
   

    
    　　partition p0 values in (1,3,5,7,9), 
   
   
   
   

    
    　　partition p1 values in (2,4,6,8,0) 
   
   
   
   

    
    　);
   
   
  
  

?

LIST COLUMN:

  
  

   
   

    
    create table list( 
   
   
   
   

    
        a int(11), 
   
   
   
   

    
        b varchar(10) 
   
   
   
   

    
        )
   
   
   
   

    
        partition by list columns(b) (
   
   
   
   

    
        partition p0 values in ('a'), 
   
   
   
   

    
        partition p1 values in ('b') 
   
   
   
   

    
        );
   
   
  
  

Hash：

 HASH分区键必须是整型，key的分区键可以是整型、字符、日期型，HASH和key分区还可以指定liner hash为线性分区，

key分区如果不指定分区键，那么必须有主键或者非空唯一约束，mysql会将此列作为分区键

如果没有则必须指定分区键。

?

  
  

   
   

    
    create table hashs( 
   
   
   
   

    
    a int(11), 
   
   
   
   

    
    b datetime 
   
   
   
   

    
    )
   
   
   
   

    
    partition by hash(YEAR(b))
   
   
   
   

    
    　　partitions 4;
   
   
  
  

Key： 

  
  

   
   

    
    create table t_key( 
   
   
   
   

    
    　　a int(11), 
   
   
   
   

    
    　　b datetime) 
   
   
   
   

    
    　　partition by key (b) 
   
   
   
   

    
    　　partitions 4;
   
   
  
  

子分区：

还可以为rang和list分区创建hash和key子分区，例如：

   
   

    
    

     
     create table ts( 
    
    
    
    

     
     　　id int(11), 
    
    
    
    

     
     　　money int(11) unsigned not null, 
    
    
    
    

     
     　　dates datetime
    
    
    
    

     
     　　)
    
    
    
    

     
     partition by range columns (dates)
    
    
    
    

     
     subpartition by hash(money)
    
    
    
    

     
     subpartitions 2
    
    
    
    

     
     ( 
    
    
    
    

     
     　　partition p2007 values less than ('2008-01-01 00:00:00'), 
    
    
    
    

     
     　　partition p2008 values less than ('2009-01-01 00:00:00'), 
    
    
    
    

     
     　　partition p2009 values less than ('2010-01-01 00:00:00'), 
    
    
    
    

     
     　　partition p2010 values less than maxvalue 
    
    
    
    

     
     );
    
    
   
   

?

range/list分区管理

新增分区

ALTER TABLE sale_data

ADD PARTITION(PARTITION p201010 VALUES LESS THAN(201011));

删除分区

当删除了一个分区，也同时删除了该分区中所有的数据。

  
  

   
   

    
    ALTER TABLE sale_data DROP PARTITION p201010;
   
   
  
  

分区的合并和拆分都是使用 reorganize,
下面的SQL，将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3

    
    

     
     

      
      ALTER TABLE sale_data
     
     
     
     

      
      REORGANIZE PARTITION p201001,p201002,p201003,
     
     
     
     

      
      p201004,p201005,p201006,
     
     
     
     

      
      p201007,p201008,p201009 INTO
     
     
     
     

      
      (
     
     
     
     

      
      PARTITION p2010Q1 VALUES LESS THAN (201004),
     
     
     
     

      
      PARTITION p2010Q2 VALUES LESS THAN (201007),
     
     
     
     

      
      PARTITION p2010Q3 VALUES LESS THAN (201010)
     
     
     
     

      
      );
     
     
    
    

下面将原来P3分区拆分为p2,p3分区，原来p3分区是values less than(2015)





alter table emp_datereorganize partition p3into(

partition p2 values less than(2005),

partition p3 values less than(2015)

);

hash/key分区管理

hash和key分区不能进行删除，只能进行合并，指定coalesce关键字，

例如将原来的4hash分区的emp表合并成2个分区 ：

     
     

      
      

       
       alter table emp coalesce partition 2;
      
      
     
     

增加分区，使用add partition，例如原来4个分区，现在要增加2个分区变成6个hash分区

     
     

      
      

       
       alter table emp add partition partitions 2;