MySQL 分区表_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 4560 | 回复: 0   主题: MySQL 分区表        下一篇 
yd7563337
注册用户
等级:少将
经验:10191
发帖:128
精华:1
注册:2011-8-9
状态:离线
发送短消息息给yd7563337 加好友    发送短消息息给yd7563337 发消息
发表于: IP:您无权察看 2015-5-25 18:04:31 | [全部帖] [楼主帖] 楼主

MySQL 分区表 :逻辑上是一个表,物理上是由多个子表组合而成的一个表集合,每个子表相对独立,各自存储着自己的数据和索引。这种分区表又称局部分区表。mysql暂时不支持全局分区表(各个分区存储数据,索引存在其他对象中)

在以前的老版本(mysql5.6之前)中有一个变量have_partitioning 开关控制着是否开启分区,默认为开启的,,mysql5.6将这个变量去掉了,自动开启。

表分区的优点:

1.数据管理方便。单独管理某些分区,例如:删除历史数据,优化、检查、修复个别分区,备份,恢复个别分区

2.对某些特定的查询起到极大的优化作用

3.涉及到例如SUM() 和 COUNT()这样聚合函数的查询,可以很容易地进行并行处理

4.通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。

5.分散热点(hotpage)

分区表的缺点:

1.每张表最大分区数为1024

2.分区函数返回值必须是int类型,mysql5.6开始可以直接对列进行分区。

3.不能使用任何外间约束。

4.所有的主键或者唯一索引必须被保函在分区表达式中。

5.在分区表达式中,不允许子查询

分区表类型:

range分区表:根据一个列的值的范围分布存储数据。

list分区表:和range分区表相似,但是list分区面向的是离散的值。

hash分区表:根据用户提供的分区表达式的返回值来进行分布存储数据。

key分区表:根据数据库提供的哈希函数来进行分区。

columns分区:可以直接使用非整型的数据进行分区但只能在range和list上使用

子分区:又称符合分区,mysql允许在range和list的分区上再进行hash或key的字分区。

range分区表:根据一个列的值的范围分布存储数据。这种分区方式的需要用户定义分区表达式,分区临界值,而且对于分区表达式返回的值必须是一个非负整数。如果表中定义了主键,那么分区列必须也包含在主键中。这种分区模式常用与按月存储数据的场景中。

drop table if EXISTS tt ;
create table tt(
tid  int  not null   auto_increment ,
tname varchar(100) not null ,
tage TINYINT  default 0 ,
tadd varchar(100) default  '' ,
tel varchar(20)  default  0,
tmob varchar(20) DEFAULT '' ,
tsfz varchar(100) default  '',
taddtime datetime default now(),
primary key(tid ,taddtime))
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION by RANGE(year(taddtime))(
PARTITION p0 VALUES less than (2009) ,
PARTITION p1 VALUES less than (2010) ,
PARTITION p2 VALUES less than  (2011) ,
PARTITION p3 VALUES less than (2012) ,
PARTITION p4 VALUES less than  MAXVALUE /*大于2012的值将会存储在p4分区中*/
)


list分区表:和range分区表相似,但是list分区面向的是离散的值。

range分区表的一些限制list分区也同样受限。

drop table if EXISTS tt ;
create table tt(
tid  int  not null   auto_increment ,
tname varchar(100) not null ,
tage TINYINT  default 0 ,
tadd varchar(100) default  '' ,
tel varchar(20)  default  0,
tmob varchar(20) DEFAULT '' ,
tsfz varchar(100) default  '',
tdeptId int default 0 ,
taddtime datetime default now(),
primary key(tid ,tdeptId))
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION by list(tdeptId)(
partition p0 VALUES in(1,2,3),
PARTITION p1 VALUES in(4,5,6),
PARTITION p2 VALUES in(7,8,9)
)


hash分区表:根据用户提供的分区表达式的返回值来进行分布存储数据。分区表达式返回的值必须为非负整数。然后用户指定一个分区个数,系统自动使用分区表达式的返回的非负整数除以分区数,然后取余数,存放在对应的分区中

drop table if EXISTS tt ;
create table tt(
tid  int  not null   auto_increment ,
tname varchar(100) not null ,
tage TINYINT  default 0 ,
tadd varchar(100) default  '' ,
tel varchar(20)  default  0,
tmob varchar(20) DEFAULT '' ,
tsfz varchar(100) default  '',
tdeptId int default 0 ,
taddtime datetime default now(),
primary key(tid,taddtime))
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION by HASH(year(taddtime))
PARTITIONS 4;


key分区表:根据数据库提供的哈希函数对给定的列进行来进行分区。和hash最大的区别在于不需要用户提供分区函数。

drop table if EXISTS tt ;
create table tt(
tid  int  not null   auto_increment ,
tname varchar(100) not null ,
tage TINYINT  default 0 ,
tadd varchar(100) default  '' ,
tel varchar(20)  default  0,
tmob varchar(20) DEFAULT '' ,
tsfz varchar(100) default  '',
tdeptId int default 0 ,
taddtime datetime default now(),
primary key(tid,taddtime))
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION by key(taddtime)
PARTITIONS 4;


columns分区:mysql5.6可以直接使用非整型的数据进行分区但只能在range和list上使用。在mysql5.6之前必须使用函数将列换成成整型才能对表进行分区,可以用来支持分区的常用函数有year(),to_days(),month()等。mysql5.6开始对整列进行分区,不需要函数进行转换,但是这个功能仅仅限于range和list分区

range columns 分区

drop table if EXISTS tt ;
create table tt(
tid  int   not null   auto_increment ,
tname varchar(100) not null ,
tage TINYINT  default 0 ,
tadd varchar(100) default  '' ,
tel varchar(20)  default  0,
tmob varchar(20) DEFAULT '' ,
tsfz varchar(100) default  '',
tdeptId int default 0 ,
taddtime datetime DEFAULT now(),
PRIMARY key(tid,taddtime)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION by range COLUMNS(taddtime)(
PARTITION p0 VALUES less than ('2009-01-01') ,
PARTITION p1 VALUES less than ('2010-01-01') ,
PARTITION p2 VALUES less than ('2011-01-01'),
PARTITION p3 VALUES less than ('2012-01-01'),
PARTITION p4 VALUES less than MAXVALUE
)


list columns 分区:

drop table if EXISTS tt ;
create table tt(
tid  int   not null   auto_increment ,
tname varchar(100) not null ,
tage TINYINT  default 0 ,
tadd varchar(100) default  '' ,
tel varchar(20)  default  0,
tmob varchar(20) DEFAULT '' ,
tsfz varchar(100) default  '',
tdeptId int default 0 ,
taddtime datetime DEFAULT now(),
PRIMARY key(tid,tname)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION by list COLUMNS(tname)(

PARTITION p0 VALUES in ('张三疯','张无忌') ,
PARTITION p1 VALUES in ('郭靖','杨康') ,
PARTITION p2 VALUES in ('李四','张三'),
PARTITION p3 VALUES in ('甲鱼','乌龟')
)


Q1:list分区中,出现定义表达式以外的值 

mysql> insert into  tt(tname,tage,tadd,tel,tmob,tsfz) VALUES('朱元璋',120,'武当山' ,18099001122,'012-46319976','') ;
ERROR 1526 (HY000): Table has no partition for value from column_list


直接插入失败了,原因是mysql不知道将这条数据存储在哪个分区中。 

分区管理与维护:

删除分区:

alter table tt drop PARTITION p0 ;
alter table tt drop PARTITION p0,p1 ;


note:不可以删除hash和key分区

添加分区:

range 分区:

mysql> alter table tt add  PARTITION ( PARTITION  p0 VALUES less than (2009));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition


range分区添加分区报错了。,把maxvalues 的分区去掉

mysql> alter table tt  drop PARTITION p4 ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0


添加分区,在最小临界值的前面添加分区

mysql> alter table tt
-> add  PARTITION (
-> PARTITION  p5 VALUES less than (2008)
-> );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition


还是报错了 ,在最大临界值的后面添加分区

mysql> alter table tt  add  PARTITION ( PARTITION  p5 VALUES less than (2013) );
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0


成功添加分区

对range分区添加新的分区,range分区不能使用maxvalues,并且只能在最大临界值的后面添加。

list分区添加的新的分区:

alter table tt add PARTITION (PARTITION p3 VALUES in (10))


不能对hash和key分区添加新的分区

拆分分区:

Reorganizepartition关键字可以对表的部分分区或全部分区进行修改,并且不会丢失数据。分解前后分区的整体范围应该一致。

下面是一个拆分 range columns 分区的实例

将p1分区拆分p5和p6两个分区:

alter table  tt
REORGANIZE PARTITION p1 into (
PARTITION p5 VALUES less than ('2009-06-01'),
PARTITION p6 VALUES less than ('2010-01-01')
)


拆分p4最后一个分区

alter table  tt
REORGANIZE PARTITION p4 into (
PARTITION p7 VALUES less than ('2013-01-01'),
PARTITION p8 VALUES less than ('2014-01-01'),
PARTITION p9 VALUES less than MAXVALUE
)


合并p5,p6分区:

alter table tt
REORGANIZE PARTITION p5,p6  into (
PARTITION p1 VALUES less than ('2010-01-01')
)


合并p7,p8,p9分区:

alter table tt
REORGANIZE PARTITION p7,p8,p9  into (
PARTITION p4 VALUES less than MAXVALUE
)


重新定义分区表:可以利用这种方法对一张新表创建分区,而不需要另外的数据导入导出

alter table tt
PARTITION by range COLUMNS(taddtime)(
PARTITION p0 VALUES less than ('2010-01-01') ,
PARTITION p1 VALUES less than ('2010-05-01') ,
PARTITION p2 VALUES less than ('2011-01-01'),
PARTITION p3 VALUES less than ('2011-05-01'),
PARTITION p4 VALUES less than MAXVALUE
)


删除表的所有分区: #不会丢失数据,表分变成普通表

ALTER TABLE tt REMOVE PARTITIONING


重建分区: 

这和先删除保存在分区中的所有记录,然后重新插入它们,具有同样的效果。它可用于整理分区碎片。

ALTER TABLE tt REBUILD PARTITION p0, p1;


优化分区:

如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHAR,BLOB,或TEXT类型的列)作了许多修改,可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间,并整理分区数据文件的碎片。

ALTER TABLE tt OPTIMIZE PARTITION p0, p1;


分析分区:读取并保存分区的键分布。

ALTER TABLE tt ANALYZE PARTITION p1;


修复分区:修复损坏的分区。

ALTER TABLE tt REPAIR PARTITION p0,p1;


检查分区:

ALTER TABLE tt CHECK PARTITION all;


截断分区:就是删除分区中的数据

ALTER TABLE tt TRUNCATE PARTITION p1;


置换分区:从mysql5.6之后才开始引入的,以前的老版本不支持置换分区。将指定分区的记录置换到普通表中,普通表的定义应该与分区表相同,包括约束、索引等

ALTER TABLE tt exchange PARTITION p0 WITH TABLE t_exch;


普通表里面的数据和分区里面的数据相互交换。

查看分区表的属性:

select * from  information_schema.partitions where TABLE_SCHEMA='TestDB' and  table_name ='tt' \G   ;


在对表进行过拆分,合并,或重新定义之后 建议最好是优化一下

alter table  tt  optimize  parition all


否则 有可能从information_schema.partitions中读到的信息是错误的。

--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论