《MySQL表分区》PPT课件

MySQL表分区houdelei,MySQL表分区,一、什么是表分区二、为什么要对表进行分区三、分区的类型四、分区的管理五、表分区的局限性,表分区通俗来讲就是允许把一个数据表根据一定的规则，跨文件系统划分成多个可以设置为任意大小的部分。MySQL从5.1起开始支持表分区，安装了5.1的MySQL默认是开启表分区支持的。可以通过：showvariableslike%partition%;来查看你的MySQL是否支持分区。,分区的优点：1.与单个磁盘或文件系统分区相比，可以存储更多的数据。2.对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区来很方便地实现。3.一些查询可以得到极大的优化，这主要是借助于满足一个给定where语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。,4.涉及到例如sum()和count()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如“selectsalesperson_id,count(orders)asorder_totalfromsalesgroupbysalesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。*5.通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。,分区的类型:1.RANGE分区2.LIST分区3.COLUMNS分区4.HASH分区5.KEY分区,range分区：基于属于一个给定连续区间的列值进行分配createtableemployees(idintnotnull,fnamevarchar(30),lnamevarchar(30),hireddatenotnulldefault1970-01-01,separateddatenotnulldefault9999-12-31,job_codeintnotnull,store_idintnotnull)partitionbyrange(store_id)(partitionp0valueslessthan(6),partitionp1valueslessthan(11),partitionp2valueslessthan(16),partitionp3valueslessthanmaxvalue)；,list分区：类似range分区,它们的主要区别在于，list分区中每个分区的定义和选择是基于某列的值从属于一个集合，而range分区是从属于一个连续区间值的集合createtableemployees(idintnotnull,fnamevarchar(30),lnamevarchar(30),hireddatenotnulldefault1970-01-01,separateddatenotnulldefault9999-12-31,job_codeint,store_idint)partitionbylist(store_id)partitionpnorthvaluesin(3,5,6,9,17),partitionpeastvaluesin(1,2,10,11,19,20),partitionpwestvaluesin(4,12,13,14,18),partitionpcentralvaluesin(7,8,15,16)；如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“insert”查询将失败并报错,columns分区：columns分区是range分区或list分区的一种变体，支持非整形字段作为分区的键，也可以用多个字段组合起来作为分区的键。columns分区可允许使用的分区键类型有：1.所有的整形：tinyint,smallint,mediumint,int,bigint(和range分区和list分区相同)不包括decimal和float这种数字类型的。2.date和datetime3.字符型：chra,varchar,binary,varbinary不包括text和blob型,rangecolumns分区：createtabletest2(aint,bint,cchar(3),dint)partitionbyrangecolumns(a,d,c)(partitionp0valueslessthan(5,10,ggg),partitionp1valueslessthan(10,20,mmm),partitionp2valueslessthan(15,30,sss),partitionp3valueslessthan(MAXVALUE,MAXVALUE,MAXVALUE);,listcolumns分区：setnamesutf8;createtablecustomers(namevarchar(25),cityvarchar(15)partitionbylistcolumns(city)(partitionp0valuesin(Beijing,Shanghai,Tianjin),partitionp1valuesin(Guanzhou,Shenzhen,Xiamen);,hash分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含mysql中有效的、产生非负整数值的任何表达式。hash分区主要用来确保数据在预先确定数目的分区中平均分布。在range和list分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在hash分区中，mysql自动完成这些工作，我们所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。,要使用hash分区来分割一个表，要在createtable语句上添加一个“partitionbyhash(expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为mysql整型的一列的名字。此外，需要在后面再添加一个“partitionsnum”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。如果在关键字“PARTITIONS”后面没有加上分区的数量，将会出现语法错误。,createtableemployees(idintnotnull,fnamevarchar(30),lnamevarchar(30),hireddatenotnulldefault1970-01-01,separateddatenotnulldefault9999-12-31,job_codeint,store_idint)partitionbyhash(year(hired)partitions4；,mysql还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“partitionby”子句中添加“linear”关键字，如下面所示：createtableemployees(idintnotnull,fnamevarchar(30),lnamevarchar(30),hireddatenotnulldefault1970-01-01,separateddatenotnulldefault9999-12-31,job_codeint,store_idint)partitionbylinearhash(year(hired)partitions4；,假设一个表达式expr,当使用线性哈希功能时，记录将要保存到的分区是num个分区中的分区N，其中N是根据下面的算法得到：1.找到下一个大于num.的、2的幂，我们把这个值称为V，它可以通过下面的公式得到：2.V=POWER(2,CEILING(LOG(2,num)3.设置N=F(column_list),现在假设要插入两行记录到表t1中，其中一条记录col3列值为2003-04-14，另一条记录col3列值为1998-10-19。第一条记录将要保存到的分区确定如下：V=POWER(2,CEILING(LOG(2,5)=8N=YEAR(2003-04-14),也可以通过线性key分割一个表。下面是一个简单的例子：createtabletk(col1intnotnull,col2char(5),col3date)partitionbylinearkey(col1)partitions3;,在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。关于该算法及其蕴涵式的描述请参考线性哈希功能。,子分区:对于已经通过RANGE或LIST分区了的表可以再进行子分区。子分区既可以使用HASH希分区，也可以使用KEY分区。这也被称为复合分区（compositepartitioning）。例如：createtablets(idint,purchaseddate)partitionbyrange(YEAR(purchased)subpartitionbyhash(TO_DAYS(purchased)subpartition2(partitionp0valueslessthan(1990),partitionp1valueslessthan(2000),partitionp2valueslessthanMAXVALUE)；,表ts有3个RANGE分区。这3个分区中的每一个分区p0,p1,和p2又被进一步分成了2个子分区。实际上，整个表被分成了3*2=6个分区。但是，由于partitionbyhash子句的作用，这些分区的前2个只保存“purchased”列中值小于1990的那些记录。,子分区可以用于特别大的表，在多个磁盘空间分配数据和索引。例子：createtablets(idint,purchaseddate)engine=myisampartitionbyrange(year(purchased)subpartitionbyhash(to_days(purchased)(partitionp0valueslessthan(1990)(subpartitions0datadirectory=/tmp/mysql/dataindexdirectory=/tmp/mysql/idx,subpartitions1datadirectory=/tmp/mysql/data1indexdirectory=/tmp/mysql/idx1),partitionp1valueslessthan(2000)(subpartitions2datadirectory=/tmp/mysql/data2indexdirectory=/tmp/mysql/idx2,subpartitions3datadirectory=/tmp/mysql/data3indexdirectory=/tmp/mysql/idx3);执行不会报错，但实际是不起作用。,使用子分区的注意项：1每个分区必须有相同数量的子分区。2每个subpartition子句必须包括(至少)子分区的一个名字。3在每个分区内，子分区的名字必须是唯一的，但是在整个表中，没有必要保持唯一（5.1.7以前是，5.1.8以后在一张表内保证全局唯一）。,分区的管理：range分区和list分区的管理：1.删除分区createtabletr(idint,namevarchar(50),purchaseddate)partitionbyrange(year(purchased)(partitionp0valueslessthan(1990),partitionp1valueslessthan(1995),partitionp2valueslessthan(2000),partitionp3valueslessthan(2005);,从一个按照range或list分区的表中删除一个分区，可以使用带一个droppartition子句的altertable命令来实现:altertabletrdroppartitionp2;如果希望从所有分区删除所有的数据，但是又要保留表的定义和表的分区模式，请使用truncatetable命令。,2.添加分区：要增加一个新的RANGE或LIST分区到一个前面已经分区了的表，使用“ALTERTABLE.ADDPARTITION”语句。对于使用RANGE分区的表，可以用这个语句添加新的区间到已有分区的序列的前面或后面。例如有一个组织的全体成员数据的分区表，该表的定义如下：createtablemembers(idint,fnamevarchar(25),lnamevarchar(25),dobdate)partitionbyrange(year(dob)(partitionp0valueslessthan(1970),partitionp1valueslessthan(1980),partitionp2valueslessthan(1990);,假设成员的最小年纪是16岁。随着日历接近2005年年底，要接纳1990年（以及以后年份）出生的成员。可以按照下面的方式，修改成员表来容纳出生在19901999年之间的成员：altertableaddpartition(partitionp3valueslessthan(2000);对于通过RANGE分区的表，只可以使用ADDPARTITION添加新的分区到分区列表的高端。设法通过这种方式在现有分区的前面或之间增加一个新的分区，将会导致下面的一个错误：altertableaddpartition(partitionp3valueslessthan(1960);,类似的可以增加新的分区到已经通过list分区的表。例如，假定有如下定义的一个表：createtablett(idint,dataint)partitionbylist(data)(partitionp0valuesin(5,10,15),partitionp1valuesin(6,12,18);,可以通过下面的方法添加一个新的分区，用来保存拥有数据列值7，14和21的行：altertablettaddpartition(partitionp2valuesin(7,14,21)；注意：不能添加这样一个新的LIST分区，该分区包含有已经包含在现有分区值列表中的任意值。如果试图这样做，将会导致错误：altertablettaddpartition(partitionnpvaluesin(4,8,12);,3.修改分区：使用“reorganizepartition”拆分或合并分区，没有数据丢失。在执行上面的语句中，mysql把保存在分区s0和s1中的所有数据都移到分区p0中。“reorganizepartition”的基本语法是：altertabletbl_namereorganizepartitionpartition_listinto(partition_definitions)；,altertablemembersreorganizepartitionp0into(partitions0valueslessthan(1960),partitions1valueslessthan(1970);altertablemembersreorganizepartitions0,s1into(partitionp0valueslessthan(1970);altertablemembersreorganizepartitionp0,p1,p2,p3into(partitionm0valueslessthan(1980),partitionm1valueslessthan(2000);,altertablettreorganizepartitionp1,npinto(partitionp1valuesin(6,18),partitionnpvaluesin(4,8,12);当使用“ALTERTABLE.REORGANIZEPARTITION”来对已经按照RANGE和LIST分区表进行重新分区时，需要注意：1.用来确定新分区模式的PARTITION子句使用与用在CREATETABLE中确定分区模式的PARTITION子句相同的规则。2.新分区模式不能有任何重叠的区间（适用于按照RANGE分区的表）或值集合（适用于重新组织按照LIST分区的表）。,3.partition_definitions列表中分区的合集应该与在partition_list中命名分区的合集占有相同的区间或值集合。例如，分区p1和p2总共覆盖了1980到1999的这些年。因此，对这两个分区的重新组织都应该覆盖相同范围的年份。4.对于按照range分区的表，只能重新组织相邻的分区，不能跳过range分区。5.不能使用REORGANIZEPARTITION来改变表的分区类型；也就是说，例如，不能把RANGE分区变为HASH分区，反之亦然。也不能使用该命令来改变分区表达式或列。,hash分区和key分区的管理：createtableclients(idint,fnamevarchar(30),lnamevarchar(30),signeddate)partitionbyhash(month(signed)partitions12;,不能使用与从按照range或list分区的表中删除分区相同的方式来从hash或key分区的表中删除分区。但是，可以使用“altertable.coalescepartition”命令来合并hash或key分区。coalesce不能用来增加分区的数量,要增加顾客表的分区数量从12到18，使用“altertable.addpartition”,具体如下：altertableclientsaddpartitionpartitions18；,要减少分区的数量从12到6，执行下面的ALTERTABLE命令：altertableclientscoalescepartition6;对于按照HASH，KEY，LINEARHASH，或LINEARKEY分区的表，COALESCE能起到同样的作用。下面是一个类似于前面例子的另外一个例子，它们的区别只是在于表是按照LINEARKEY进行分区：createtableclients_lk(idint,fnamevarchar(30),lnamevarchar(30),signeddate)partitionbylinearkey(signed)partitions12;altertableclients_lkcoalescepartition6;,要改变分区的方式可以用altertabletablenamepartitionbyhash(id)partitions2;来实现。,分区的维护：Rebuildingpartitions:altertablet1rebuildpartitionp0,p1;Optimizingpartitions:altertablet1optimizepartitionp0,p1;,Analyzingpartitions:altertablet1analyzepartitionp3;Repairingpartitions:altertablet1repairpartitionp0,p1;Checkpartitions:altertablet1checkpartitionp1;,分区的一些局限性：一、关于PartitioningKeys,PrimaryKeys,andUniqueKeys的限制在5.1中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键（也包括主键约束）。这句话也许不好理解，我们做几个实验：,createtablet1(idintnotnull,uidintnotnull,primarykey(id),uniquekey(uid)partitionbyrange(id)(partitionp0valueslessthan(5),partitionp1valueslessthan(10);ERROR1503(HY000):AUNIQUEINDEXmustincludeallcolumnsinthetablespartitioningf