《MySQL表分区》PPT课件.ppt

《《MySQL表分区》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《MySQL表分区》PPT课件.ppt（52页珍藏版）》请在三一办公上搜索。

1、MySQL表分区,MySQL表分区,一、什么是表分区二、为什么要对表进行分区三、分区的类型四、分区的管理五、表分区的局限性,表分区通俗来讲就是允许把一个数据表根据一定的规则，跨文件系统划分成多个可以设置为任意大小的部分。MySQL从5.1起开始支持表分区，安装了5.1的MySQL默认是开启表分区支持的。可以通过：show variables like%partition%;来查看你的MySQL是否支持分区。,分区的优点：1.与单个磁盘或文件系统分区相比，可以存储更多的数据。2.对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加

2、新数据的过程又可以通过为那些新数据专门增加一个新的分区来很方便地实现。3.一些查询可以得到极大的优化，这主要是借助于满足一个给定where 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。,4.涉及到例如sum()和 count()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如“select salesperson_id,count(orders)as order_total from sales group by s

3、alesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。*5.通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。,分区的类型:1.RANGE分区2.LIST分区3.COLUMNS分区4.HASH分区5.KEY分区,range 分区：基于属于一个给定连续区间的列值进行分配create table employees(id int not null,fname varchar(30),lname varchar(30),hired date not null default 1970-01-01,separated date

4、 not null default 9999-12-31,job_code int not null,store_id int not null)partition by range(store_id)(partition p0 values less than(6),partition p1 values less than(11),partition p2 values less than(16),partition p3 values less than maxvalue)；,list 分区：类似range分区,它们的主要区别在于，list分区中每个分区的定义和选择是基于某列的值从属于一

5、个集合，而range分区是从属于一个连续区间值的集合create table employees(id int not null,fname varchar(30),lname varchar(30),hired date not null default 1970-01-01,separated date not null default 9999-12-31,job_code int,store_id int)partition by list(store_id)partition pnorth values in(3,5,6,9,17),partition peast values in

6、(1,2,10,11,19,20),partition pwest values in(4,12,13,14,18),partition pcentral values in(7,8,15,16)；如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“insert”查询将失败并报错,columns 分区：columns分区是range分区或list分区的一种变体，支持非整形字段作为分区的键，也可以用多个字段组合起来作为分区的键。columns分区可允许使用的分区键类型有：1.所有的整形：tinyint,smallint,mediumint,int,bigint(和range分

7、区和list分区相同)不包括decimal和float这种数字类型的。2.date 和 datetime 3.字符型：chra,varchar,binary,varbinary 不包括text和blob型,range columns分区：create table test2(a int,b int,c char(3),d int)partition by range columns(a,d,c)(partition p0 values less than(5,10,ggg),partition p1 values less than(10,20,mmm),partition p2 values

8、 less than(15,30,sss),partition p3 values less than(MAXVALUE,MAXVALUE,MAXVALUE);,list columns分区：set names utf8;create table customers(name varchar(25),city varchar(15)partition by list columns(city)(partition p0 values in(Beijing,Shanghai,Tianjin),partition p1 values in(Guanzhou,Shenzhen,Xiamen);,ha

9、sh分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含mysql 中有效的、产生非负整数值的任何表达式。hash分区主要用来确保数据在预先确定数目的分区中平均分布。在range和list分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在hash分区中，mysql 自动完成这些工作，我们所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。,要使用hash分区来分割一个表，要在create table 语句上添加一个“partition by hash(expr)”子

10、句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为mysql 整型的一列的名字。此外，需要在后面再添加一个“partitions num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。如果在关键字“PARTITIONS”后面没有加上分区的数量，将会出现语法错误。,create table employees(id int not null,fname varchar(30),lname varchar(30),hired date not null default 1970-01-01

11、,separated date not null default 9999-12-31,job_code int,store_id int)partition by hash(year(hired)partitions 4；,mysql还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“partition by”子句中添加“linear”关键字，如下面所示：create table employees(id int not null,fn

12、ame varchar(30),lname varchar(30),hired date not null default 1970-01-01,separated date not null default 9999-12-31,job_code int,store_id int)partition by linear hash(year(hired)partitions 4；,假设一个表达式expr,当使用线性哈希功能时，记录将要保存到的分区是num 个分区中的分区N，其中N是根据下面的算法得到：1.找到下一个大于num.的、2的幂，我们把这个值称为V，它可以通过下面的公式得到：2.V=P

13、OWER(2,CEILING(LOG(2,num)3.设置 N=F(column_list)&(V-1).4.当 N=num:设置 V=CEIL(V/2)设置 N=N&(V-1),例如，假设表t1，使用线性哈希分区且有4个分区，是通过下面的语句创建的：create table t1(col1 int not null,col2 char(5),col3 date)partition by linear key(col1)partitions 4;,现在假设要插入两行记录到表t1中，其中一条记录col3列值为2003-04-14，另一条记录col3列值为1998-10-19。第一条记录将要保存到

14、的分区确定如下：V=POWER(2,CEILING(LOG(2,5)=8N=YEAR(2003-04-14)&(8-1)=2003&7=3(3=4 为假（FALSE）:记录将被保存到#3号分区中),第二条记录将要保存到的分区序号计算如下：V=8N=YEAR(1998-10-19)&(8-1)=1998&7=6(6=4 为真（TRUE）:还需要附加的步骤)N=6&(CEILING(8/2)1)=6&3=2(2=4 为假（FALSE）:记录将被保存到#2分区中),key 分区：按照key进行分区类似于按照hash分区，除了hash分区使用的用户定义的表达式，而key分区的哈希函数是由mysql 服

15、务器提供。create table tk(col1 int not null,col2 char(5),col3 date)partition by linear key(col1)partitions 3;,也可以通过线性key分割一个表。下面是一个简单的例子：create table tk(col1 int not null,col2 char(5),col3 date)partition by linear key(col1)partitions 3;,在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，

16、而不是通过模数算法。关于该算法及其蕴涵式的描述请参考线性哈希功能。,子分区:对于已经通过RANGE或LIST分区了的表可以再进行子分区。子分区既可以使用HASH希分区，也可以使用KEY分区。这也被称为复合分区（composite partitioning）。例如：create table ts(id int,purchased date)partition by range(YEAR(purchased)subpartition by hash(TO_DAYS(purchased)subpartition 2(partition p0 values less than(1990),partit

17、ion p1 values less than(2000),partition p2 values less than MAXVALUE)；,表ts 有3个RANGE分区。这3个分区中的每一个分区p0,p1,和 p2又被进一步分成了2个子分区。实际上，整个表被分成了3*2=6个分区。但是，由于partition by hash子句的作用，这些分区的前2个只保存“purchased”列中值小于1990的那些记录。,子分区可以用于特别大的表，在多个磁盘空间分配数据和索引。例子：create table ts(id int,purchased date)engine=myisam partition

18、 by range(year(purchased)subpartition by hash(to_days(purchased)(partition p0 values less than(1990)(subpartition s0 data directory=/tmp/mysql/data index directory=/tmp/mysql/idx,subpartition s1 data directory=/tmp/mysql/data1 index directory=/tmp/mysql/idx1),partition p1 values less than(2000)(subp

19、artition s2 data directory=/tmp/mysql/data2 index directory=/tmp/mysql/idx2,subpartition s3 data directory=/tmp/mysql/data3 index directory=/tmp/mysql/idx3);执行不会报错，但实际是不起作用。,使用子分区的注意项：1 每个分区必须有相同数量的子分区。2 每个subpartition 子句必须包括(至少)子分区的一个名字。3 在每个分区内，子分区的名字必须是唯一的，但是在整个表中，没有必要保持唯一（以前 是，以后在一张表内保证全局唯一）。,分区

20、的管理：range分区和list分区的管理：1.删除分区create table tr(id int,name varchar(50),purchased date)partition by range(year(purchased)(partition p0 values less than(1990),partition p1 values less than(1995),partition p2 values less than(2000),partition p3 values less than(2005);,从一个按照range或list分区的表中删除一个分区，可以使用带一个dro

21、p partition子句的alter table命令来实现:alter table tr drop partition p2;如果希望从所有分区删除所有的数据，但是又要保留表的定义和表的分区模式，请使用truncate table命令。,2.添加分区：要增加一个新的RANGE或LIST分区到一个前面已经分区了的表，使用“ALTER TABLE.ADD PARTITION”语句。对于使用RANGE分区的表，可以用这个语句添加新的区间到已有分区的序列的前面或后面。例如有一个组织的全体成员数据的分区表，该表的定义如下：create table members(id int,fname varcha

22、r(25),lname varchar(25),dob date)partition by range(year(dob)(partition p0 values less than(1970),partition p1 values less than(1980),partition p2 values less than(1990);,假设成员的最小年纪是16岁。随着日历接近2005年年底，要接纳1990年（以及以后年份）出生的成员。可以按照下面的方式，修改成员表来容纳出生在19901999年之间的成员：alter table add partition(partition p3 valu

23、es less than(2000);对于通过RANGE分区的表，只可以使用ADD PARTITION添加新的分区到分区列表的高端。设法通过这种方式在现有分区的前面或之间增加一个新的分区，将会导致下面的一个错误：alter table add partition(partition p3 values less than(1960);,类似的可以增加新的分区到已经通过list分区的表。例如，假定有如下定义的一个表：create table tt(id int,data int)partition by list(data)(partition p0 values in(5,10,15),par

24、tition p1 values in(6,12,18);,可以通过下面的方法添加一个新的分区，用来保存拥有数据列值7，14和21的行：alter table tt add partition(partition p2 values in(7,14,21)；注意：不能添加这样一个新的LIST分区，该分区包含有已经包含在现有分区值列表中的任意值。如果试图这样做，将会导致错误：alter table tt add partition(partition np values in(4,8,12);,3.修改分区：使用“reorganize partition”拆分或合并分区，没有数据丢失。在执行上面

25、的语句中，mysql 把保存在分区s0和s1中的所有数据都移到分区p0中。“reorganize partition”的基本语法是：alter table tbl_name reorganize partition partition_list into(partition_definitions)；,alter table members reorganize partition p0 into(partition s0 values less than(1960),partition s1 values less than(1970);alter table members reorgan

26、ize partition s0,s1 into(partition p0 values less than(1970);alter table members reorganize partition p0,p1,p2,p3 into(partition m0 values less than(1980),partition m1 values less than(2000);,alter table tt reorganize partition p1,np into(partition p1 values in(6,18),partition np values in(4,8,12);当

27、使用“ALTER TABLE.REORGANIZE PARTITION”来对已经按照RANGE和LIST分区表进行重新分区时，需要注意：1.用来确定新分区模式的PARTITION子句使用与用在CREATE TABLE中确定分区模式的PARTITION子句相同的规则。2.新分区模式不能有任何重叠的区间（适用于按照RANGE分区的表）或值集合（适用于重新组织按照LIST分区的表）。,3.partition_definitions 列表中分区的合集应该与在partition_list 中命名分区的合集占有相同的区间或值集合。例如，分区p1和p2总共覆盖了1980到1999的这些年。因此，对这两个分区

28、的重新组织都应该覆盖相同范围的年份。4.对于按照range分区的表，只能重新组织相邻的分区，不能跳过range分区。5.不能使用REORGANIZE PARTITION来改变表的分区类型；也就是说，例如，不能把RANGE分区变为HASH分区，反之亦然。也不能使用该命令来改变分区表达式或列。,hash分区和key分区的管理：create table clients(id int,fname varchar(30),lname varchar(30),signed date)partition by hash(month(signed)partitions 12;,不能使用与从按照range或li

29、st分区的表中删除分区相同的方式来从hash或key分区的表中删除分区。但是，可以使用“alter table.coalesce partition”命令来合并hash或key分区。coalesce不能用来增加分区的数量,要增加顾客表的分区数量从12到18，使用“alter table.add partition”,具体如下：alter table clients add partition partitions 18；,要减少分区的数量从12到6，执行下面的ALTER TABLE命令：alter table clients coalesce partition 6;对于按照HASH，KEY，

30、LINEAR HASH，或LINEAR KEY分区的表，COALESCE能起到同样的作用。下面是一个类似于前面例子的另外一个例子，它们的区别只是在于表是按照LINEAR KEY 进行分区：create table clients_lk(id int,fname varchar(30),lname varchar(30),signed date)partition by linear key(signed)partitions 12;alter table clients_lk coalesce partition 6;,要改变分区的方式可以用 alter table tablename par

31、tition by hash(id)partitions 2;来实现。,分区的维护：Rebuilding partitions:alter table t1 rebuild partition p0,p1;Optimizing partitions:alter table t1 optimize partition p0,p1;,Analyzing partitions:alter table t1 analyze partition p3;Repairing partitions:alter table t1 repair partition p0,p1;Check partitions:a

32、lter table t1 check partition p1;,分区的一些局限性：一、关于Partitioning Keys,Primary Keys,and Unique Keys的限制在5.1中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键（也包括主键约束）。这句话也许不好理解，我们做几个实验：,create table t1(id int not null,uid int not null,primary key(id),unique key(uid)partition by range(id)(partition p0 values less than(5),

33、partition p1 values less than(10);ERROR 1503(HY000):A UNIQUE INDEX must include all columns in the tables partitioning function,三、关于函数的限制 在建立分区表的语句中，只能包含例如ABS()、CEILING()、FLOOR()、DAY()、HOUR()、MINUTE()、MOD()、MONTH()、QUARTER()、SECOND()、TO_DAYS()、YEAR()等返回整型的函数。分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是

34、当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键（BLOB or TEXT 列除外）。create table tkc(c1 char)partition by key(c1)partitions 4;query ok,0 rows affected(0.00 sec),运算限制支持加减乘等运算出现在分区表达式，但是运算后的结果必须是一个INT或者NULL。|,&,等不允许出现在分区表达式。最多支持1024个分区，包括子分区。当你建立分区表包含很多分区但没有超过1024限制的时候，如果报错 Got error 24 from storage engine，那意味着你需要增大open

35、_files_limit参数。,不支持fulltext indexes（全文索引），包括myisam引擎。create table articles(id int unsigned auto_increment not null primary key,title varchar(200),body text,fulltext(title,body)partition by hash(id)partitions 4;ERROR 1214(HY000):The used table type doesnt support FULLTEXT indexes,临时表不能被分区。create temp

36、orary table t1(id int not null,uid int not null,primary key(id)partition by range(id)(partition p0 values less than(5)engine=myisam,partition p1 values less than(10)engine=myisam);ERROR 1562(HY000):Cannot create temporary table with partitions,create table tkc2(c1 char)partition by hash(c1)partitions 4;error 1491(hy000):the partition function returns the wrong type create table tkc3(c1 int)partition by hash(c1)partitions 4;query ok,0 rows affected(0.00 sec),分区键不能是一个子查询。只有RANG和LIST分区能进行子分区。HASH和KEY分区不能进行子分区。分区表不支持INSERT DELAYED 分区表的分区键创建索引，那么这个索引也将被分区。分区键没有全局索引一说。,Thanks!,