数据库介绍-gb core2分片与索引策略

GBase8t南大通用数据技术股份有限公 理解表分片和索引分片的意义、策掌握表分片、索引分片的创建及管理语理解分片的应用场景和使用原了解分片对应用的性能影响和调优方法2——表分片的意义及策二索引分片策略与语三分片的管四分片与性能调3——表分片的策略和意4分片是数据库中以表(Table)为对象的数据分散储存方法分片的存在或变更是不会影响使用者或应用程序本身分片的存在或变更会影响应用程序的执行方法与效率56有效处理大表(Larger有效利用并发运行利用“分片忽略”缩小表空间访问(fragment分片并行化执行(PDQ)方法与效分片容易管理表组合/表分片(attach分片有效增进利用率7分片PDQ,表数据和索引分片目的：提高应程序的效原因一：分片使得——GBase8t动态服务器可以并行地扫描多个磁盘上的数样GBase8t服务器可以分配多条线程，从所有数据分片上并行地选取数据。8分片PDQ,I/O(二9轮轮询法分片—Round-表表达式分片—Expression-表达式分片（Expression- FRAGMENTBY(a10INtabdbspc1,(a20INtabdbspc2,(a30INtabdbspc3;CREATEINDEXidx1ONtb1 FRAGMENTBY(a<=10)INidxdbspc1,(a<=20)INidxdbspc2,(a<=30)IN表达式分片基于表的多列(multiple基于表的多列(multiplecolumns)CREATETABLEorders(order_numinteger,yearinteger,monthamountfloatFRAGMENTBYyear<21ANDmonth>=1ANDmonth<4INyear<40ANDmonth>=4ANDmonth<7IN表达式分片CREATETABLEaddress(streetchar(40),citychar(20),statechar(20),zipcodeinteger)FRAGMENTBYzipcode=95228ORzipcode=95443INdatadbs01,zipcode=91120ORzipcode=92310INdatadbs02,REMAINDERINdatadbs03;Createindexidx_addresson(zipcodeSetexplainSelect*fromaddresswherezipcodeSelect*fromaddresswherezipcode表达式分片MODinnamechar(50),salaryinteger)FRAGMENTBYMOD(id_num,3)=0INMOD(id_num,3)=1INMOD(id_num,3)=2IN--createindexidx_employeeonsetexplainselect*fromemployeewhere表达式分片分片表达式的顺序是非常重要可以使用remainderfragment来存放不能满足任何一个分片表的数remainderfragmentCREATETABLEtab1(col1integer,col2date)FRAGMENTBYEXPRESSIONcol1>=0andcol1<100indatadbs01,col1>=100andcol1<200indatadbs02,remainderindatadbs03;简单，不需要了解数据的分把数据均匀地分配到所有分片提高查询性只能用于表，不能用于索不能用于分片忽略以增加性(col1integer,col2FRAGMENTBYroundrobindatadbs01,Createindexidx_tab1on--索引idx_tab1基于离散值列表的片段数据GBase8t间隔分足最初的分片时，GBase8t系统将会自动创建一个分片来存储这条记录。createcreatetableorder3(order_numintegernotnull,order_dateDATEfragmentbyRANGE(order_date)storein(dbs0,dbs1,dbs2,partitionQ0VALUES<'01/01/2011'indbs0,partitionQ1VALUES<'04/01/2011'indbs1,partitionQ2VALUES<'07/01/2011'indbs2,partitionQ3VALUES<'10/01/2011'indbs3extentsize10240nextsize10240lockmode,)和分离(DecDec

MayenablesstoringdataoverGBase8t间隔分片策略—表结createtablesales(amountint,idint,data_timedatetimeyeartofragmentbyinterval(1units storein(sdbs1,sdbs3,--partitionp0values<'2011-06-2800:00:00'increateuniqueindexidx_saleson插入数据，自动分insertintosalesvalues(100,1,'2011-06-2800:00:00');insertintosalesvalues(102,2,'2011-06-2800:00:00');insertintosalesvalues(100,1,'2011-06-2801:00:00');insertintosalesvalues(100,2,'2011-06-2801:00:00');insertintosalesvalues(100,1,'2011-06-2802:00:00');insertintosalesvalues(100,2,'2011-06-28GBase8t间隔分片策略—查看partition情oncheck-pttestdb:sales|grepTablefragmentpartitionp0inDBspacebigdatadbsTablefragmentpartitionsys_p1inDBspacesdbs1Tablefragmentpartitionsys_p2inDBspacesdbs1Tablefragmentpartitionsys_p3inDBspacesdbs1Tablefragmentpartitionsys_p4inDBspacesdbs1Tablefragmentpartitionsys_p5inDBspacesdbs1Tablefragmentpartitionsys_p6inDBspacesdbs1Tablefragmentpartitionsys_p7inDBspacesdbs1Tablefragmentpartitionsys_p8inDBspacesdbs1Tablefragmentpartitionsys_p9inDBspacesdbs1Indexidx_salesfragmentpartitionp0inDBspaceIndexidx_salesfragmentpartitionsys_p1inDBspacesdbs1Indexidx_salesfragmentpartitionsys_p2inDBspacesdbs1Indexidx_salesfragmentpartitionsys_p3inDBspacesdbs1Indexidx_salesfragmentpartitionsys_p4inDBspacesdbs1Indexidx_salesfragmentpartitionsys_p5inDBspacesdbs1Indexidx_salesfragmentpartitionsys_p6inDBspacesdbs1Indexidx_salesfragmentpartitionsys_p7inDBspacesdbs1Indexidx_salesfragmentpartitionsys_p8inDBspacesdbs1Indexidx_salesfragmentpartitionsys_p9inDBspacesdbs1在一个dbspace内可以创建多个分简化dbspace管DiskArray磁盘阵列对于不理想的并行CREATECREATETABLEtab1(col1integer,col2FRAGMENTBYpartitionpart1(col1>=0andcol1<100)indatadbs01,partitionpart2(col1>=100andcol1<200)indatadbs01,partitionpart3(col1>=200)indatadbs01分片(Fragmentation和区段示(xINTEGER,yINTEGER,zCHARFRAGMENTBYx<=10andx>=1intab_adbs1,x<=20andx>10intab_adbs2,x<=30andx>20intab_adbs3EXTENTSIZE12000NEXTSIZE

每个分片的初始化大小为 二二索引分片策略与语数据库索引可以被分片(be数据库索引与表可以有不同的分片可以显式声明索引分片策索引继承表的分片(Attachor索引不继承表分片(Indexisnon-索引分片可以与表分片有不同大小页面(Page索引分片举例索引没有FRAGMENTBY子索引继承表的分片(AttachorCREATECREATETABLEtab1(col1integer,col2FRAGMENTBYcol1>=0andcol1<100indatadbs01,col1>=100andcol1<200indatadbs02;CREATEINDEXidx1ontab1(col1);CREATEINDEXidx2ontab1索引idx1,idx2索引分片举例表：非分片处索引：分片处CREATECREATETABLEtab1(col1integer,col2date)CREATEINDEXidx1ontab1(col1)FRAGMENTBYcol1>=0andcol1<100incol1>=100andcol<200in表：分片处索引：非分片处索引的空间可能非常CREATETABLEtab1(col1integer,col2date)CREATETABLEtab1(col1integer,col2date)FRAGMENTBYroundrobinindatadbs01,datadbs02CREATEINDEXidx1ontab1(col1)IN表：分片处索引：分片处理(不同于表的分片策CREATECREATETABLEtab1(col1integer,col2FRAGMENTBYroundrobinindatadbs01,CREATEINDEXidx1ontab1FRAGMENTBYcol1>=0andcol1<100incol1>=100andcol<200in表：分片处索引：分片处理(相同策略，不同存储CREATECREATETABLEtab1(col1integer,col2date)FRAGMENTBYEXPRESSIONcol1>=0andcol1<100indatadbs01,col1>=100andcol<200indatadbs02;CREATEINDEXidx1ontab1FRAGMENTBYcol1>=0andcol1<100incol1>=100andcol<200indetach表：分片处索引：非分片处CREATECREATETABLEtab1(col1integer,col2FRAGMENTBYcol1>=0andcol1<100incol1>=100andcol1<200inCREATEINDEXidx1ontab1(col1)in--detach)detach表：非分片处索引：主键(primary)等约束自动创建的索(col1integer,col2date,primarykey(c1))FRAGMENTBYcol1>=0andcol1<100incol1>=100andcol1<200indatadbs02该索引为detached三三分片的管ALTERFRAGMENT...ALTERFRAGMENT...ADD(orALTERFRAGMENT...ALTERFRAGMENT...ATTACH(or分片表attach&detach1月1月2月3月4月4月1月1月2月3月4月

1月2月3月4月1月2月3月4月4月Alterfragments把两个结构完全相同的非分片表合并成一个分片dbspacesdbspaceAlterfragmentontabletab1ATTACHtab1aspartitionp1,tab2aspartitionALTERFRAGMENTONTABLEtb1ATTACHtb1AS(a<=100),tb2AS(a>把一个非分片表合并成为一个分片表的一个分Alterfragmentontablemytab1attachmytab3aspartition我们可以使BEFOREAFTER句，用来调整表达式的顺Alterfragmentontablef1attachf3as(col1<=0)beforeAlterfragmentontablef1attachf3as(col1<=0)afterAlterfragments–在目标表中使用Checkconstraint避免在ATTACHE期间不必要的对于目标表，外键，主键，唯一性约束，和serial类型的字段都是允许索引重建是可以避免的，如果：数据没有uniqueCREATETABLEtb1(aINT)FRAGMENTBY(a<=10)INtabdbspc1,(a<=20)INtabdbspc2,(a<=30)INCREATEINDEXidx1ONtb1(a)FRAGMENTBY(a<=10)INidxdbspc1,(a<=20)INidxdbspc2,(a<=30)INCREATETABLEtb2(aINTCHECKa>30ANDa<=40)INCREATEINDEXidx2ONtb2(a)INALTERFRAGMENTONTABLEtb1ATTACHtb2AS(a<=CREATETABLEtb1(aINT)FRAGMENTBY(a<=10)INtabdbspc1,(a<=20)INtabdbspc2,(a<=30)INCREATEINDEXidx1ONtb1CREATETABLEtb2(aINTCHECKa>30ANDa<=40)INCREATEINDEXidx2ONtb2(a)INALTERFRAGMENTONTABLEtb1ATTACHtb2AS(a<=CREATETABLEtb1(aint)INCREATEINDEXidx1ONtb1(a)inCREATETABLEtb2(aint)INCREATEINDEXidx2ONtb2(a)inALTERFRAGMENTONTABLEATTACHtb1AS(a<=100),tb2AS(a>CREATEINDEXidx1ONtb1(a)FRAGMENTBYa<=100INdb1,a>100INAlterfragment把一个分片表分解成多个AlterAlterfragmentontablef1DETACHdbspace2Alterfragmentontablemytab1DETACHpartitionp2如果索引的分片策略和表的分片策略不同，则需要索引重建(如相同,对应与被detached的分片的索引将被删CREATECREATETABLEtb1(aint)FRAGMENTBYEXPRESSION(a>=0ANDa<5)INdb1,(a>=5ANDa<10)IN(a>=10ANDa<15)INdb3;CREATEINDEXidx1ONtb1(a);ALTERFRAGMENTONTABLEtb1DETACHdb3CREATETABLEtb1(aint,bint)FRAGMENTBYEXPRESSION(a>=0ANDa<5)INdb1,(a>=5ANDa<10)INdb2,(a>=10ANDa<15)INdb3;CREATEINDEXidx1ontb1(a)FRAGMENTBYEXPRESSION(a>=0ANDa<5)INdb4,(a>=5ANDa<10)IN(a>=10ANDa<15)INALTERFRAGMENTONTABLEtb1DETACHdb3四四分片与性能调分片忽略(Fragmentation并行操Joins,sorts,aggregates,groups,and平衡更细的备份和恢复粒更高的可用更好的安全分 扫描线程 分

扫描线程 XX分XX 扫 扫描线程 分XXElimination*INSERTsUPDATEsSELECTs基于SQL语句中的查询条件(querypredicates)，只访问符合件的表分片与索引分片,避免全表扫对于表达式分片有对于表和索引的分片都有CREATECREATETABLEtab1(col1integer,col2FRAGMENTBYcol1>=0andcol1<100indbsp1,col1>=100andcol1<200indbsp2,col1>=200indbsp3SELECT*FROMtab1WHEREcol1=SELECT*FROMtab1WHEREcol1BETWEEN andSELECTSELECT*FROMtab1WHEREcol1=1)usr1.tab1:SEQUENTIALSCAN(Serial,fragments:1)Filters:usr1.tab1.col1=125SELECT*FROMtab1WHEREcol1BETWEEN25and1)usr1.tab1:SEQUENTIALSCAN(Serial,fragments:Filters:(usr1.tab1.col1<=75ANDusr1.tab1.col1>=25Fragment适用以下场景优化INSERTUPDATE,SELECTorDELETEfetchIN,=,<,>,=>,<=,AND,OR,NOT,MATCH,不适用于以下场景!=,ISNULL,ISNOT把不同分片的数据尽量分配到不同的设备放在后面，常用的放在前面同一个表达式中最严格的部分放在前面，以避免不必要的表达式验尽量创建连续的没有交叉区域的分片，以便分片忽略能够生分片表与统计信息更新(update分片表数据量非常大，很多情况下无法完成updatestatistics操为保证优化器的正确执行，对分片表进行了alterfragment操作，建议Updatestatisticslowfortabletabname(分片字段其他情况只对索引第一个字段进行统计更新即支持对指定的分片进行统计信息更关于分片表中的rowid不是唯如果需要在分片表中使用rowid，则必须明确的创建该列CREATETABLEorders(Order_numSERIAL,Customer_numCREATETABLEorders(Order_numSERIAL,Customer_numINTEGER,Part_numCHAR(20))WITHROWIDSFRAGMENTbyROUNDROBININdbs1ALTERTABLEitemsADDALTERTABLEitemsDROP轮转CREATETEMPTABLEtemp_table(column1INTEGER,column2CHAR(10)WITHNOLOGPRAGMENTBYROUNDINtempdbs1,tempdbs2,基于表达CREATETEMPtemp_table(column1INTEGER,column2CHAR(10))WITHNOLOGFRAGMENTBYEXPRESSIONcolumn1<1000intempdbs1;column1<2000intempdbs2;column1>=2000in自动采用roundrobin方式Select*fromtablenameintotemptmp_tablewithnoGBase8t允许我们通过设置DATASKIP参数，或者setdataskip语句的ToturndataSETDATASKIPToturnoffdataSETDATASKIPToskipspecificSETDATASKIPFollowtheskipstrategysetbytheconfigurationSETDATASKIPGBase8t的压缩可以针对表和分片进持需要经常访问的新数据处于未压缩状态selectsysadmin:task('fragmentcompressrepackshrink',partn)fromsysfragmentsF,systablesTwhereF.tabid=T.tabid andF.partitioninLIST{"dbsp3","dbsp4"}andT.tabname="trans_detail";executefunctionsysadmin:task("fragmentcompress",OAT进行单个分片进行压创建分片表在datadbs01createtable(data_timedatetimeyeartosecond,idint,abcinteger,c1varchar(255),c2integer,c3int8)fragmentbyexpressionpartition(data_time<'2011-01-0200:00:00'ANDdata_time>='2011-01-0100:00:00')partition(data_time<'2011-01-0300:00:00'ANDdata_time>='2011-01-0200:00:00')createuniqueindexidx1_frag_tableonfrag_tablecreateindexidx2_frag_tableonfrag_table创建表在createcreatetable(data_timedatetimeyeartosecond,idint,abcc1c3int8)indatadbs03createuniqueindexidx1_tab1ontab1(data_time,id)increateindexidx2_tab1ontab1(abc)inaltertabletab1addconstraint(data_time<'2011-01-0400:00:00'ANDdata_time>='2011-01-03ATTACHtab1ASpartitionp3ATTACHtab1ASpartitionp3(data_time<'2011-01-0400:00:00'ANDdata_time>='2011-01-0300:00:00');表的新结createtable"informix".frag_table(data_timedatetimeyeartosecond,idinteger,abcinteger,c1varchar(255),c2integer,c3int8)fragmentbyexpressionpartitionp1((data_time<datetime(2011-01-0200:00:00)yeartosecond)AND(data_time>=datetime(2011-01-0100:00:00)yeartosecond))indatadbs01,partitionp2((data_time<datetime(2011-01-0300:00:00)yeartosecond)AND(data_time>=datetime(2011-01-0200:00:00)yeartosecond))indatadbs02,partitionp3((data_time<datetime(2011-01-0400:00:00)yeartosecond)AND(data_time>=datetime(2011-01-0300:00:00)yeartosecond))indatadbs03extentsize16nextsize16lockmoderow;createuniqueindex"informix".idx1_frag_tableon(data_time,id)usingbtreecreateindex"informix".idx2_frag_tableon"informix".frag_table(abc)usingbtreeselect*fromfrag_tablewhereEstimatedCost:Estimated#ofRowsReturned:1)