mysql 表分区

注： 1.只有mysql 5.1版本及其以上版本才支持分区，5.6版本才支持截断分区，分区原理基本和Oracle相同。

   2.只有主键才可作为分区依据 ，创建主键的代码 primary key 。

3.如果想使用主键id之外的列来作为分区的依据，可以使用复合主键primary key (id,name)

一：范围分区

就是根据数据库表中某一字段的值的范围来划分分区，例如：

Sql代码  

create table graderecord

  (

  sno varchar2(10),

  sname varchar2(20),

  dormitory varchar2(3),

  grade int  primary key

)

partition by range(grade)

(

  partition bujige values less than(60), --不及格

  partition jige values less than(85), --及格

  partition youxiu values less than(maxvalue) --优秀

)

下面查询一下全部数据，然后查询各个分区数据，代码一起写：

查询代码  

select * from graderecord;

select * from graderecord partition(bujige);

select * from graderecord partition(jige);

select * from graderecord partition(youxiu);

说明：数据中有空值，会自动将其规划到maxvalue的分区中。

二：散列分区

散列分区是根据字段的hash值进行均匀分布，尽可能的实现各分区所散列的数据相等。

Sql代码  

create table graderecord

(

  sno varchar2(10),

  sname varchar2(20),

  dormitory varchar2(3),

  grade int

)

partition by hash(sno)

(

  partition p1,

  partition p2,

  partition p3

);

查询分区数据：

Sql代码  

select * from graderecord partition(p1);

select * from graderecord partition(p2);

select * from graderecord partition(p3);

说明：散列分区即为哈希分区，采用哈希码技术分区，也可能下一次搜索就不是之前的数据了。

 

三：列表分区

列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。

Sql代码  

create table graderecord

(

  sno varchar2(10),

  sname varchar2(20),

  dormitory varchar2(3),

  grade int

)

partition by list(dormitory)

(

  partition d229 values('229'),

  partition d228 values('228'),

  partition d240 values('240')

);

 

注意可以配置多列！也可以写成

partition by list(dormitory)

(

  partition d229 values('229'),

  partition d228 values('228','240')

);

 

查询如下：

Sql代码  

select * from graderecord partition(d229);

select * from graderecord partition(d228);

select * from graderecord partition(d240); 

四：复合分区 （范围-散列分区，范围-列表分区）

首先讲范围-散列分区。先声明一下：列表分区不支持多列，但是范围分区和哈希分区支持多列。

代码如下：

Sql代码  

create table graderecord

(

  sno varchar2(10),

  sname varchar2(20),

  dormitory varchar2(3),

  grade int

)

partition by range(grade)

subpartition by hash(sno,sname)

(

  partition p1 values less than(75)

            (

               subpartition sp1,subpartition sp2

            ),

  partition p2 values less than(maxvalue)

            (

               subpartition sp3,subpartition sp4

            )

);  

 以grade划分范围，然后以sno和sname划分散列分区，当数据量大的时候散列分区则趋于“平均”。

查询如下：

Sql代码  

select * from graderecord partition(p1);

select * from graderecord partition(p2);

select * from graderecord subpartition(sp1);

select * from graderecord subpartition(sp2);

select * from graderecord subpartition(sp3);

select * from graderecord subpartition(sp4); 

说明：当数据量越来越大时，哈希分区的分区表中数据越来越趋于平衡。

 

下面讲讲分区的维护操作：

(1)分裂分区，以第一个范围分区为例：

Sql代码  

alter table graderecord split partition jige at(75)   

      into(partition keyi,partition lianghao);   

 把分区及格分裂为两个分区：可以和良好。

 

(2)合并分区，以第一个范围分区为例：

Sql代码  

alter table graderecord merge partitions keyi,lianghao   

into partition jige;  

 把可以和良好两个分区合并为及格。

 

(3)添加分区，由于在范围分区上添加分区要求添加的分区范围大于原有分区最大值，但原有分区最大值已经为maxvalue，故本处以第二个散列分区为例：

Sql代码  

alter table graderecord add partition p4;  

 给散列分区例子又增加了一个分区p4 。

 

(4)删除分区，语法：

Sql代码  

alter table table_name drop partition partition_name;  

 

(5)截断分区，清空分区中的数据

Sql代码  

alter table table_name truncate partition partition_name;  

 

说明：对待分区的操作同样可以对待子分区，效果一样。删除一个分区会同时删除其下的子分区。合并多个分区也会把他们的子分区自动合并。分裂分区时注意分裂点。

 

另外不带模板子分区和带有模板子分区的分区表操作的区别：带有子分区模板的分区表在添加分区时候自动添加子分区，不带模板子分区的分区表没有这个功能；带有子分区模板的分区表在更改分区时只需更改分区，不带模板子分区的分区表在更改分区时一定注意连同子分区一起更改。