MySQL表分區(qū)ppt課件

MySQL表分區(qū)houdelei,MySQL表分區(qū),1,一、什么是表分區(qū)二、為什么要對表進行分區(qū)三、分區(qū)的類型四、分區(qū)的管理五、表分區(qū)的局限性,2,表分區(qū)通俗來講就是允許把一個數(shù)據(jù)表根據(jù)一定的規(guī)則，跨文件系統(tǒng)劃分成多個可以設(shè)置為任意大小的部分。MySQL從5.1起開始支持表分區(qū)，安裝了5.1的MySQL默認(rèn)是開啟表分區(qū)支持的?？梢酝ㄟ^：showvariableslike%partition%;來查看你的MySQL是否支持分區(qū)。,3,分區(qū)的優(yōu)點：1.與單個磁盤或文件系統(tǒng)分區(qū)相比，可以存儲更多的數(shù)據(jù)。2.對于那些已經(jīng)失去保存意義的數(shù)據(jù)，通?？梢酝ㄟ^刪除與那些數(shù)據(jù)有關(guān)的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過程又可以通過為那些新數(shù)據(jù)專門增加一個新的分區(qū)來很方便地實現(xiàn)。3.一些查詢可以得到極大的優(yōu)化，這主要是借助于滿足一個給定where語句的數(shù)據(jù)可以只保存在一個或多個分區(qū)內(nèi)，這樣在查找時就不用查找其他剩余的分區(qū)。因為分區(qū)可以在創(chuàng)建了分區(qū)表后進行修改，所以在第一次配置分區(qū)方案時還不曾這么做時，可以重新組織數(shù)據(jù)，來提高那些常用查詢的效率。,4,4.涉及到例如sum()和count()這樣聚合函數(shù)的查詢，可以很容易地進行并行處理。這種查詢的一個簡單例子如“selectsalesperson_id,count(orders)asorder_totalfromsalesgroupbysalesperson_id；”。通過“并行”，這意味著該查詢可以在每個分區(qū)上同時進行，最終結(jié)果只需通過總計所有分區(qū)得到的結(jié)果。*5.通過跨多個磁盤來分散數(shù)據(jù)查詢，來獲得更大的查詢吞吐量。,5,分區(qū)的類型:1.RANGE分區(qū)2.LIST分區(qū)3.COLUMNS分區(qū)4.HASH分區(qū)5.KEY分區(qū),6,range分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值進行分配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)；,7,list分區(qū)：類似range分區(qū),它們的主要區(qū)別在于，list分區(qū)中每個分區(qū)的定義和選擇是基于某列的值從屬于一個集合，而range分區(qū)是從屬于一個連續(xù)區(qū)間值的集合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)；如果試圖插入列值（或分區(qū)表達式的返回值）不在分區(qū)值列表中的一行時，那么“insert”查詢將失敗并報錯,8,columns分區(qū)：columns分區(qū)是range分區(qū)或list分區(qū)的一種變體，支持非整形字段作為分區(qū)的鍵，也可以用多個字段組合起來作為分區(qū)的鍵。columns分區(qū)可允許使用的分區(qū)鍵類型有：1.所有的整形：tinyint,smallint,mediumint,int,bigint(和range分區(qū)和list分區(qū)相同)不包括decimal和float這種數(shù)字類型的。2.date和datetime3.字符型：chra,varchar,binary,varbinary不包括text和blob型,9,rangecolumns分區(qū)：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);,10,listcolumns分區(qū)：setnamesutf8;createtablecustomers(namevarchar(25),cityvarchar(15)partitionbylistcolumns(city)(partitionp0valuesin(Beijing,Shanghai,Tianjin),partitionp1valuesin(Guanzhou,Shenzhen,Xiamen);,11,hash分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含mysql中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達式。hash分區(qū)主要用來確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布。在range和list分區(qū)中，必須明確指定一個給定的列值或列值集合應(yīng)該保存在哪個分區(qū)中；而在hash分區(qū)中，mysql自動完成這些工作，我們所要做的只是基于將要被哈希的列值指定一個列值或表達式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。,12,要使用hash分區(qū)來分割一個表，要在createtable語句上添加一個“partitionbyhash(expr)”子句，其中“expr”是一個返回一個整數(shù)的表達式。它可以僅僅是字段類型為mysql整型的一列的名字。此外，需要在后面再添加一個“partitionsnum”子句，其中num是一個非負(fù)的整數(shù)，它表示表將要被分割成分區(qū)的數(shù)量。如果沒有包括一個PARTITIONS子句，那么分區(qū)的數(shù)量將默認(rèn)為1。如果在關(guān)鍵字“PARTITIONS”后面沒有加上分區(qū)的數(shù)量，將會出現(xiàn)語法錯誤。,13,createtableemployees(idintnotnull,fnamevarchar(30),lnamevarchar(30),hireddatenotnulldefault1970-01-01,separateddatenotnulldefault9999-12-31,job_codeint,store_idint)partitionbyhash(year(hired)partitions4；,14,mysql還支持線性哈希功能，它與常規(guī)哈希的區(qū)別在于，線性哈希功能使用的一個線性的2的冪（powers-of-two）運算法則，而常規(guī)哈希使用的是求哈希函數(shù)值的模數(shù)。線性哈希分區(qū)和常規(guī)哈希分區(qū)在語法上的唯一區(qū)別在于，在“partitionby”子句中添加“l(fā)inear”關(guān)鍵字，如下面所示：createtableemployees(idintnotnull,fnamevarchar(30),lnamevarchar(30),hireddatenotnulldefault1970-01-01,separateddatenotnulldefault9999-12-31,job_codeint,store_idint)partitionbylinearhash(year(hired)partitions4；,15,假設(shè)一個表達式expr,當(dāng)使用線性哈希功能時，記錄將要保存到的分區(qū)是num個分區(qū)中的分區(qū)N，其中N是根據(jù)下面的算法得到：1.找到下一個大于num.的、2的冪，我們把這個值稱為V，它可以通過下面的公式得到：2.V=POWER(2,CEILING(LOG(2,num)3.設(shè)置N=F(column_list),17,現(xiàn)在假設(shè)要插入兩行記錄到表t1中，其中一條記錄col3列值為2003-04-14，另一條記錄col3列值為1998-10-19。第一條記錄將要保存到的分區(qū)確定如下：V=POWER(2,CEILING(LOG(2,5)=8N=YEAR(2003-04-14),20,也可以通過線性key分割一個表。下面是一個簡單的例子：createtabletk(col1intnotnull,col2char(5),col3date)partitionbylinearkey(col1)partitions3;,21,在KEY分區(qū)中使用關(guān)鍵字LINEAR和在HASH分區(qū)中使用具有同樣的作用，分區(qū)的編號是通過2的冪（powers-of-two）算法得到，而不是通過模數(shù)算法。關(guān)于該算法及其蘊涵式的描述請參考線性哈希功能。,22,子分區(qū):對于已經(jīng)通過RANGE或LIST分區(qū)了的表可以再進行子分區(qū)。子分區(qū)既可以使用HASH希分區(qū)，也可以使用KEY分區(qū)。這也被稱為復(fù)合分區(qū)（compositepartitioning）。例如：createtablets(idint,purchaseddate)partitionbyrange(YEAR(purchased)subpartitionbyhash(TO_DAYS(purchased)subpartition2(partitionp0valueslessthan(1990),partitionp1valueslessthan(2000),partitionp2valueslessthanMAXVALUE)；,23,表ts有3個RANGE分區(qū)。這3個分區(qū)中的每一個分區(qū)p0,p1,和p2又被進一步分成了2個子分區(qū)。實際上，整個表被分成了3*2=6個分區(qū)。但是，由于partitionbyhash子句的作用，這些分區(qū)的前2個只保存“purchased”列中值小于1990的那些記錄。,24,子分區(qū)可以用于特別大的表，在多個磁盤空間分配數(shù)據(jù)和索引。例子：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),25,partitionp1valueslessthan(2000)(subpartitions2datadirectory=/tmp/mysql/data2indexdirectory=/tmp/mysql/idx2,subpartitions3datadirectory=/tmp/mysql/data3indexdirectory=/tmp/mysql/idx3);執(zhí)行不會報錯，但實際是不起作用。,26,使用子分區(qū)的注意項：1每個分區(qū)必須有相同數(shù)量的子分區(qū)。2每個subpartition子句必須包括(至少)子分區(qū)的一個名字。3在每個分區(qū)內(nèi)，子分區(qū)的名字必須是唯一的，但是在整個表中，沒有必要保持唯一（5.1.7以前是，5.1.8以后在一張表內(nèi)保證全局唯一）。,27,分區(qū)的管理：range分區(qū)和list分區(qū)的管理：1.刪除分區(qū)createtabletr(idint,namevarchar(50),purchaseddate)partitionbyrange(year(purchased)(partitionp0valueslessthan(1990),partitionp1valueslessthan(1995),partitionp2valueslessthan(2000),partitionp3valueslessthan(2005);,28,從一個按照range或list分區(qū)的表中刪除一個分區(qū)，可以使用帶一個droppartition子句的altertable命令來實現(xiàn):altertabletrdroppartitionp2;如果希望從所有分區(qū)刪除所有的數(shù)據(jù)，但是又要保留表的定義和表的分區(qū)模式，請使用truncatetable命令。,29,2.添加分區(qū)：要增加一個新的RANGE或LIST分區(qū)到一個前面已經(jīng)分區(qū)了的表，使用“ALTERTABLE.ADDPARTITION”語句。對于使用RANGE分區(qū)的表，可以用這個語句添加新的區(qū)間到已有分區(qū)的序列的前面或后面。例如有一個組織的全體成員數(shù)據(jù)的分區(qū)表，該表的定義如下：createtablemembers(idint,fnamevarchar(25),lnamevarchar(25),dobdate)partitionbyrange(year(dob)(partitionp0valueslessthan(1970),partitionp1valueslessthan(1980),partitionp2valueslessthan(1990);,30,假設(shè)成員的最小年紀(jì)是16歲。隨著日歷接近2005年年底，要接納1990年（以及以后年份）出生的成員?？梢园凑障旅娴姆绞?，修改成員表來容納出生在19901999年之間的成員：altertableaddpartition(partitionp3valueslessthan(2000);對于通過RANGE分區(qū)的表，只可以使用ADDPARTITION添加新的分區(qū)到分區(qū)列表的高端。設(shè)法通過這種方式在現(xiàn)有分區(qū)的前面或之間增加一個新的分區(qū)，將會導(dǎo)致下面的一個錯誤：altertableaddpartition(partitionp3valueslessthan(1960);,31,類似的可以增加新的分區(qū)到已經(jīng)通過list分區(qū)的表。例如，假定有如下定義的一個表：createtablett(idint,dataint)partitionbylist(data)(partitionp0valuesin(5,10,15),partitionp1valuesin(6,12,18);,32,可以通過下面的方法添加一個新的分區(qū)，用來保存擁有數(shù)據(jù)列值7，14和21的行：altertablettaddpartition(partitionp2valuesin(7,14,21)；注意：不能添加這樣一個新的LIST分區(qū)，該分區(qū)包含有已經(jīng)包含在現(xiàn)有分區(qū)值列表中的任意值。如果試圖這樣做，將會導(dǎo)致錯誤：altertablettaddpartition(partitionnpvaluesin(4,8,12);,33,3.修改分區(qū)：使用“reorganizepartition”拆分或合并分區(qū)，沒有數(shù)據(jù)丟失。在執(zhí)行上面的語句中，mysql把保存在分區(qū)s0和s1中的所有數(shù)據(jù)都移到分區(qū)p0中?！皉eorganizepartition”的基本語法是：altertabletbl_namereorganizepartitionpartition_listinto(partition_definitions)；,34,altertablemembersreorganizepartitionp0into(partitions0valueslessthan(1960),partitions1valueslessthan(1970);altertablemembersreorganizepartitions0,s1into(partitionp0valueslessthan(1970);altertablemembersreorganizepartitionp0,p1,p2,p3into(partitionm0valueslessthan(1980),partitionm1valueslessthan(2000);,35,altertablettreorganizepartitionp1,npinto(partitionp1valuesin(6,18),partitionnpvaluesin(4,8,12);當(dāng)使用“ALTERTABLE.REORGANIZEPARTITION”來對已經(jīng)按照RANGE和LIST分區(qū)表進行重新分區(qū)時，需要注意：1.用來確定新分區(qū)模式的PARTITION子句使用與用在CREATETABLE中確定分區(qū)模式的PARTITION子句相同的規(guī)則。2.新分區(qū)模式不能有任何重疊的區(qū)間（適用于按照RANGE分區(qū)的表）或值集合（適用于重新組織按照LIST分區(qū)的表）。,36,3.partition_definitions列表中分區(qū)的合集應(yīng)該與在partition_list中命名分區(qū)的合集占有相同的區(qū)間或值集合。例如，分區(qū)p1和p2總共覆蓋了1980到1999的這些年。因此，對這兩個分區(qū)的重新組織都應(yīng)該覆蓋相同范圍的年份。4.對于按照range分區(qū)的表，只能重新組織相鄰的分區(qū)，不能跳過range分區(qū)。5.不能使用REORGANIZEPARTITION來改變表的分區(qū)類型；也就是說，例如，不能把RANGE分區(qū)變?yōu)镠ASH分區(qū)，反之亦然。也不能使用該命令來改變分區(qū)表達式或列。,37,hash分區(qū)和key分區(qū)的管理：createtableclients(idint,fnamevarchar(30),lnamevarchar(30),signeddate)partitionbyhash(month(signed)partitions12;,38,不能使用與從按照range或list分區(qū)的表中刪除分區(qū)相同的方式來從hash或key分區(qū)的表中刪除分區(qū)。但是，可以使用“altertable.coalescepartition”命令來合并hash或key分區(qū)。coalesce不能用來增加分區(qū)的數(shù)量,要增加顧客表的分區(qū)數(shù)量從12到18，使用“altertable.addpartition”,具體如下：altertableclientsaddpartitionpartitions18；,39,要減少分區(qū)的數(shù)量從12到6，執(zhí)行下面的ALTERTABLE命令：altertableclientscoalescepartition6;對于按照HASH，KEY，LINEARHASH，或LINEARKEY分區(qū)的表，COALESCE能起到同樣的作用。下面是一個類似于前面例子的另外一個例子，它們的區(qū)別只是在于表是按照LINEARKEY進行分區(qū)：createtableclients_lk(idint,fnamevarchar(30),lnamevarchar(30),signeddate)partitionbylinearkey(signed)partitions12;altertableclients_lkcoalescepartition6;,40,要改變分區(qū)的方式可以用altertabletablenamepartitionbyhash(id)partitions2;來實現(xiàn)。,41,分區(qū)的維護：Rebuildingpartitions:altertablet1rebuildpartitionp0,p1;Optimizingpartitions:altertablet1optimizepartitionp0,p1;,42,Analyzingpartitions:altertablet1analyzepartitionp3;Repairingpartitions:altertablet1repairpartitionp0,p1;Checkpartitions:altertablet1checkpartitionp1;,43,分區(qū)的一些局限性：一、關(guān)于PartitioningKeys,PrimaryKeys,andUniqueKeys的限制在5.1中分區(qū)表對唯一約束有明確的規(guī)定，每一個唯一約束必須包含在分區(qū)表的分區(qū)鍵（也包括主鍵約束）。這句話也許不好理解，我們做幾個實驗：,44,createtablet1(idintnotnull,uidintnotnull,primarykey(id),uniquekey(uid)partitionbyrange(id)(partitionp0valueslessthan(5),partitionp1valueslessthan(10);ERROR1503(HY000):AUNIQUEINDEXmustincludeallcolumnsinthetablespartitioningfunction,