mysql分区表

一、RANGE分区（值连续）

　　1、创建以id为区分的分区表

create table range_partition_by_id(
    i_id int,
    v_name varchar(50),
    primary key (i_id)
)
partition by range(i_id)(
    partition p0 values less than (10000),
    partition P1 values less than (20000)
);　　

　　2、修改分区表，添加新分区

alter table range_partition_by_id add partition (partition p2 values less than maxvalue);

　　3、查看分区表信息

SELECT * 
FROM information_schema.PARTITIONS 
WHERE table_name='range_partition_by_id'

　　　　运行结果：

　　　　

　　　　

　　　　

　　4、创建以日期为区分的分区表

　　　 1）创建

CREATE TABLE range_partition_by_year (
  i_id int,
  t_datetime datetime
)
partition by range(year(t_datetime))(
    partition p2010 values less than (2011),
    partition p2011 values less than (2012),
    partition p2012 values less than (2013)
);

　　  2）插入属于分区范围的数据

insert into range_partition_by_year
values(1,'2010-1-1 00:00:00');
insert into range_partition_by_year
values(1,'2011-1-1 00:00:00');
insert into range_partition_by_year
values(1,'2011-1-2 00:00:00');

　　　　查询结果：

select *
from range_partition_by_year;

SELECT * 
FROM information_schema.PARTITIONS 
WHERE table_name='range_partition_by_year';

　　　　运行结果：

　　　　

　　　　

　　3）插入分区范围之外的数据

insert into range_partition_by_year
values(1,'2014-1-1 00:00:00');

　　　　运行结果：

　　　 

　5、将普通表转换为分区表

　　　1）表中无数据

alter table range_partition_after_table_created  partition by range(i_id)(
                    partition p1 values less than (1000),
                    partition p2 values less than (2000),
                    partition p3 values less than (3000),
                    partition p4 values less than maxvalue);

　　　　

　　　2）表中有数据

create table range_partition_after_table_created_has_data(
    i_id int,
    v_name varchar(50)
);

insert into range_partition_after_table_created_has_data
values(1,'111');
insert into range_partition_after_table_created_has_data
values(2001,'2001');

　　　　转换成分区表：

alter table range_partition_after_table_created_has_data  partition by range(i_id)(
                    partition p1 values less than (1000),
                    partition p2 values less than (2000),
                    partition p3 values less than (3000),
                    partition p4 values less than maxvalue); 

　　　  运行结果：

　　　　

　　　 查看分区后的状况：

SELECT * 
FROM information_schema.PARTITIONS 
WHERE table_name='range_partition_after_table_created_has_data'
;

　　　运行结果：　

　　　

附：使用中间表进行分区表转换可参考：

　　http://space.itpub.net/15480802/viewspace-689399

二、LIST分区（值离散）

　　1、创建分区表

create table list_partition(
    i_id int,
    v_name varchar(50)
)
partition by list(i_id)(
    partition p1 values in(1,3,5,7,9),
    partition p2 values in(2,4,6,8)
);

　　2、插入数据

insert into list_partition
values(1,'111'),(4,'444'),(6,'666'),(8,'888'),(5,'555')
;

　　3、查询分区状态

SELECT * 
FROM information_schema.PARTITIONS 
WHERE table_name='list_partition'
;

　　　运行结果：

　　　

　　　

　　4、插入分区范围之外的值

insert into list_partition
values(100,'0000')

　　　　运行结果：

　　　 

三、HASH分区

　　1、创建分区表

create table hash_partition(
    i_id int,
    v_name varchar(50)
)
partition BY HASH (i_id)
partitions 4
;

　　2、插入数据

insert into hash_partition
values(1,'111'),(2,'222'),(6,'666'),(8,'888'),(5,'555'),
    (100,'aaa'),(101,'bbb'),(200,'cccc'),(455,'ddd'),
    (666,'eee'),(567,'fff')
;

　　3、查看分区表状态

SELECT * 
FROM information_schema.PARTITIONS 
WHERE table_name='hash_partition'
;

　　　 运行结果：

　　　

四、COLUMN分区

 　　mysql5.5开始支持,视为RANGE分区和LIST分区的一种进化，支持INT,SMALLINT,TINYINT,

　　BIGINT.DATE,DATETIME.CHAR,VARCHAR,BINARY,VARBINARY类型

　　1、创建分区表

create table columns_partition(
    i_id int,
    t_datetime datetime
)
partition by range columns (t_datetime)(
    partition p2012_down values less than('2012-1-1 00:00:00'),
    partition p2012_2013 values less than('2013-1-1 00:00:00'),
    partition p2013_up values less than('8888-1-1 00:00:00')
);

　　2、插入数据

insert into columns_partition
values  (1,'2011-2-3 00:00:00'),
        (1,'2011-4-5 00:00:00'),
        (1,'2011-3-3 00:00:00'),
        (1,'2012-10-3 00:00:00'),
        (1,'2014-10-3 00:00:00')
;

　　3、查看分区表状态

SELECT * 
FROM information_schema.PARTITIONS 
WHERE table_name='columns_partition'
;

　　　　运行结果：

　　　　

　　　　

附：关于分区表的详细分析与性能对比：

　　http://blog.csdn.net/feihong247/article/details/7885199

　　实例：

　　http://blog.chinaunix.net/uid-24373487-id-3190201.html