MySQL 分区表

sdfouhdso888

　　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>>　　ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
　　range分区添加分区报错了。,把maxvalues 的分区去掉

　　mysql>>　　Query OK, 0 rows affected (0.05 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　添加分区,在最小临界值的前面添加分区

　　mysql>>　　-> add  PARTITION (
　　-> PARTITION  p5 VALUES less than (2008)
　　-> );
　　ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
　　还是报错了 ,在最大临界值的后面添加分区

　　mysql>>　　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中读到的信息是错误的。
　　分区的基本语法以及简单管理：
　　http://lehsyh.iteye.com/blog/732719
　　http://yuelangyc.iteye.com/blog/2006880
　　分区的创建，新增，删除，合并，拆分
　　http://www.iyunv.com/database/201204/126860.html
　　分区的管理
　　http://blog.csdn.net/seteor/article/details/17756601
　　分区的相关讨论
　　http://www.itpub.net/thread-1162975-1-1.html
　　分区性能讨论
　　http://www.cnblogs.com/acpp/archive/2010/08/09/1795464.html
　　分区的性能测试
　　mysql分区相关介绍
　　http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html
　　mysql分区注意事项：
　　http://www.iyunv.net/article/42544.htm
　　函数相关：
　　http://www.iyunv.com/database/201204/127397.html