数据库分库分表中间件架构解析.ppt

阿里巴巴分布式数据库,原理、实现和应用,2012.7,集团共享技术平台分布式数据库邱硕,分布式数据库中间件,App,App,App,App,Cobar,Oracle,MySQL,MySQL,MySQL,ErosaOracle,ErosaMySQL,ErosaMySQL,ErosaMySQL,Eromanga,Otter,Oracle,MySQL,MySQL,Otter,ErosaOracle,ErosaMySQL,ErosaMySQL,Eromanga,性能容量高可用数据消费时效性跨机房数据同步,ASC,DW,.,分布式数据库中间件,App,App,App,App,Cobar,Oracle,MySQL,MySQL,MySQL,ErosaOracle,ErosaMySQL,ErosaMySQL,ErosaMySQL,Eromanga,Oracle,MySQL,MySQL,Otter,性能容量高可用数据消费时效性跨机房数据同步,ErosaOracle,ErosaMySQL,ErosaMySQL,Eromanga,Otter,ASC,DW,.,分布式数据库中间件,App,App,App,App,Cobar,Oracle,MySQL,MySQL,MySQL,Oracle,MySQL,MySQL,Otter,ErosaOracle,ErosaMySQL,ErosaMySQL,Eromanga,性能容量高可用数据消费时效性跨机房数据同步,ErosaOracle,ErosaMySQL,ErosaMySQL,ErosaMySQL,Eromanga,Otter,ASC,DW,.,分布式数据库中间件,App,App,App,App,Cobar,Oracle,MySQL,MySQL,MySQL,ErosaOracle,ErosaMySQL,ErosaMySQL,ErosaMySQL,Eromanga,Oracle,MySQL,MySQL,ErosaOracle,ErosaMySQL,ErosaMySQL,Eromanga,性能容量高可用数据消费时效性跨机房数据同步,Otter,ASC,DW,.,Otter,分布式数据库中间件,App,App,App,App,Cobar,Oracle,MySQL,MySQL,MySQL,ErosaOracle,ErosaMySQL,ErosaMySQL,ErosaMySQL,Eromanga,Oracle,MySQL,MySQL,Otter,性能容量高可用数据消费时效性跨机房数据同步,ErosaOracle,ErosaMySQL,ErosaMySQL,Eromanga,Otter,ASC,DW,.,大纲,中间件引入Cobar策略系统实现实施应用,Cobar之前,Oracle单点数据库性能问题中文站offer总数：2008年1亿-2011年3亿高峰时：load30、cpu使用率90%数据库连接过多可用性问题Standby切换故障成本和伸缩性问题依赖高成本的硬件设备,Oracle,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,单点：MySQL集群替换Oracle,MySQL,MySQL,MySQL,Oracle单点数据库性能问题中文站offer总数：08年1亿-今天3亿高峰时：load30、cpu使用率90%数据库连接过多可用性问题Standby切换故障成本和伸缩性问题依赖高成本的硬件设备,Cobar引入水平拆分,MySQL,MySQL,MySQL,Cobar,App,Oracle单点数据库性能问题中文站offer总数：08年1亿-今天3亿高峰时：load30、cpu使用率90%数据库连接过多可用性问题Standby切换故障成本和伸缩性问题依赖高成本的硬件设备,App,MySQL,MySQL,MySQL,App,App,App,App,App,App,App,Cobar引入连接复用,App,MySQL,MySQL,MySQL,CobarProxy,App,App,App,App,App,App,App,Oracle单点数据库性能问题中文站offer总数：08年1亿-今天3亿高峰时：load30、cpu使用率90%数据库连接过多可用性问题Standby切换故障成本和伸缩性问题依赖高成本的硬件设备,Cobar引入,MySQL,Cobar,App,X,Cobar引入failover,MySQLMaster1,Cobar,App,MySQLMaster2,X,MySQLReplication,Cobar引入failover,Cobar,App,MySQLReplication,MySQLMaster1,MySQLMaster2,大纲,中间件引入Cobar策略系统实现实施应用,Oracle,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,拆分数据表,水平拆分,水平拆分,拆分字段,水平拆分,f(pavarotti17)=库1,f(test1234)=库1,f(test1234)=库1,f(pavarotti17)=库1,f(abcd)=库2,f(abcd)=库2,f(abcd)=库2,库1,库2,拆分字段,水平拆分,f(abcd)=库2,库1,库2,拆分字段,f(pavarotti17)=库1,f(test1234)=库1,f(test1234)=库1,f(pavarotti17)=库1,f(abcd)=库2,f(abcd)=库2,路由算法,路由算法,pavarotti17,f(pavarotti17)=库1,路由算法,pavarotti17,部分截取,hash()=3170972965401,路由算法,hash(pavarott)=3170972965401%1024=537,01023,路由算法,hash(pavarott)=3170972965401%1024=537,0255,256511,512767,7681023,256,256,256,256,分库1,分库2,分库3,分库4,路由算法,hash(pavarott)=3170972965401%1024=537,0255,256511,512767,7681023,256,256,256,256,分库1,分库2,分库3,分库4,路由算法扩容,hash(pavarott)=3170972965401%1024=537,0127128255,256383384511,512639640767,7688958961023,128,128,128,128,128,128,128,128,分库1,分库2,分库3,分库4,路由算法扩容,hash(pavarott)=3170972965401%1024=537,0127128255,256383384511,512639640767,7688958961023,128,128,128,128,128,128,128,128,分库1,分库2,分库3,分库4,分库5,分库6,分库7,分库8,原,分库1,分库2,分库3,分库4,原,原,原,路由算法非均匀分布,hash(pavarott)=3170972965401%1024=537,0511,512767,7688958961023,512,256,128,128,分库1,分库2,分库3,分库4,拆分表的数据访问SQL转发,Cobar,App,select*fromtb1wheremember_id=test1234,拆分表的数据访问SQL转发,Cobar,App,select*fromtb1wheremember_id=test1234,Cobar,App,SELECT*FROMtb1WHEREmember_idIN(test1234,pavarotti17,abcd),拆分表的数据访问SQL转发,Cobar,App,select*fromtb1wheremember_idin(test1234,pavarotti17),select*fromtb1wheremember_idin(abcd),拆分表的数据访问SQL转发,ResultMerger,前台通信,ResultSet:row1row2,ResultSet:row3row4row5,ResultSet:row3row1row4row5row2,拆分表的数据访问结果返回,多维水平拆分,visit表,SELECT*FROMvisitWHEREuser=A,多维水平拆分,visit表,SELECT*FROMvisitWHEREproduct=Coca-Cola,分库1,分库2,分库3,分库4,分库5,分库6,分库7,分库8,分库9,分库10,分库11,分库12,分库13,分库14,分库15,分库16,product值Hash取模,user值Hash取模,0,1,2,3,0,1,2,3,visit表,一张表的多个字段同时作为拆分字段,Hash(“A”)%4=,分库1,分库2,分库3,分库4,分库5,分库6,分库7,分库8,分库9,分库10,分库11,分库12,分库13,分库14,分库15,分库16,product值Hash取模,user值Hash取模,0,1,2,3,0,1,2,3,CocaColaA,Hash(“CocaCola”)%4=,SELECT*FROMvisitWHEREproduct=ColaColaANDuser=A,分库1,分库2,分库3,分库4,分库5,分库6,分库7,分库8,分库9,分库10,分库11,分库12,分库13,分库14,分库15,分库16,product值Hash取模,user值Hash取模,0,1,2,3,0,1,2,3,CocaCola,Hash(“CocaCola”)%4=,SELECT*FROMvisitWHEREproduct=ColaCola,Hash(“A”)%4=,A,分库1,分库2,分库3,分库4,分库5,分库6,分库7,分库8,分库9,分库10,分库11,分库12,分库13,分库14,分库15,分库16,product值Hash取模,user值Hash取模,0,1,2,3,0,1,2,3,SELECT*FROMvisitWHEREproduct=ColaColaANDuser=A,Cobar的策略,MySQL集群替代Oracle单点基于表的水平拆分和分布根据字段值的一致性Hash分布多维拆分数据查询方式根据where中的拆分字段分发SQL语句其他元素的处理将Cobar收到的SQL语句做变换分发到各个分库执行对执行结果合并、处理保证返回前端的内容满足语义,JOIN有限的处理,跨库JOIN问题,SELECT*FROMtb1INNERJOINtb2ONt1.MEMBER_ID=t2.NAME,tb1,tb1,tb2,tb2,迭代查询,SELECT*FROMtb1INNERJOINtb2ONt1.MEMBER_ID=t2.NAME,tb1,tb1,tb2,tb2,FORrow1INselect*FROMtb1ADD(SELECT*FROMtb2WHERE=row1.member_id)TORESULT,跨库索引,tb1,tb1,tb2,tb2,idx,idx,扫描idx，再根据每一行的id1,id2查到最终结果,跨库索引,tb1,tb1,tb2,tb2,SELECT*FROMtb1INNERJOINtb2ONt1.MEMBER_ID=t2.NAMEWHEREt1.id=5,SELECT*FROMidxWHEREid1=5,再根据id1,id2查到最终结果,跨库索引,tb1,tb1,tb2,tb2,idx,idx,一定以JOIN_COL为索引的拆分字段吗？,跨库索引,SELECT*FROMtb1INNERJOINtb2ONt1.MEMBER_ID=t2.NAMEWHEREt1.gmt600,tb1,tb1,tb2,tb2,跨库索引,tb1,tb1,tb2,tb2,idx,idx,tb1,tb1,tb2,tb2,idx,idx,SELECTidx.id2,tb1.*FROMidxINNERJOINtb1ONidx.id1=tb1.idWHEREt1.gmt600,SELECTidx.id2,tb1.*FROMidxINNERJOINtb1ONidx.id1=tb1.idWHEREt1.gmt600,SELECT*FROMtb1INNERJOINtb2ONt1.MEMBER_ID=t2.NAMEWHEREt1.gmt600,跨库索引,跨库索引,tb1,tb1,tb2,tb2,SELECT*FROMtb1INNERJOINtb2ONt1.MEMBER_ID=t2.NAMEWHEREt1.gmt600ANDt2.time600,idx,跨库索引,idx,idx,索引表的拆分WHERE条件中的字段所在表的拆分字段，作为索引拆分字段索引包含两张表的主键JOIN字段WHERE中的其他字段索引的更新分布式事务的支持,SELECTc1FROMtb1ORDERBYc1LIMIT4,2,select.orderbyc1limit0,6,select.orderbyc1limit0,6,select.orderbyc1limit0,6,分库1,分库2,分库3,cobar,OrderBy/Limit,分库1,分库2,分库3,OrderBy/Limit,4,5,6,3,2,7,8,10,6,5,1,11,13,14,9,7,3,返回结果,返回结果,返回结果,8,SELECTc1FROMtb1ORDERBYc1LIMIT4,2,分库1,分库2,分库3,OrderBy/Limit,4,5,6,3,2,7,8,10,6,5,1,11,13,14,9,7,3,返回结果,返回结果,返回结果,8,0,最终结果集,SELECTc1FROMtb1ORDERBYc1LIMIT4,2,分库1,分库2,分库3,OrderBy/Limit,4,5,6,3,2,7,8,10,6,5,11,13,14,9,7,3,返回结果,返回结果,返回结果,8,1,最终结果集,SELECTc1FROMtb1ORDERBYc1LIMIT4,2,分库1,分库2,分库3,OrderBy/Limit,4,5,6,3,7,8,10,6,5,11,13,14,9,7,3,返回结果,返回结果,返回结果,8,2,最终结果集,SELECTc1FROMtb1ORDERBYc1LIMIT4,2,分库1,分库2,分库3,OrderBy/Limit,4,5,6,7,8,10,6,5,11,13,14,9,7,3,返回结果,返回结果,返回结果,8,3,最终结果集,SELECTc1FROMtb1ORDERBYc1LIMIT4,2,分库1,分库2,分库3,OrderBy/Limit,4,5,6,7,8,10,6,5,11,13,14,9,7,返回结果,返回结果,返回结果,8,4,最终结果集,SELECTc1FROMtb1ORDERBYc1LIMIT4,2,最终结果集,分库1,分库2,分库3,OrderBy/Limit,5,6,7,8,10,6,5,11,13,14,9,7,返回结果,返回结果,返回结果,8,4,4,SELECTc1FROMtb1ORDERBYc1LIMIT4,2,最终结果集,分库1,分库2,分库3,OrderBy/Limit,4,6,7,8,10,6,5,11,13,14,9,7,返回结果,返回结果,返回结果,8,4,5,SELECTc1FROMtb1ORDERBYc1LIMIT4,2,OrderBy/Limit方案总结,selectc1fromtb1orderbyc1limit100000000,2,所有分库都要查询100000002条数据Cobar需要遍历100000002条数据,对如下SQL,一次交互得到结果Offset大小有限制,OrderBy/Limit优化,目标：解决查询量大问题遍历量大问题前提各个分库数据分布大致一样,selectc1fromtb1orderbyc1limit9999999,4,select.orderbyc1limit33333333,4,select.orderbyc1limit33333333,4,select.orderbyc1limit33333333,4,分库1,分库2,分库3,step1:分成3条语句发给分库,分库1,分库2,分库3,7,4,5,3,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,selectc1fromtb1orderbyc1limit9999999,4,找出查询结果中最小和最大值,分库1,分库2,分库3,7,4,5,3,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,step2:以最小值和最大值为界再查询,selectc1fromtb1orderbyc1limit9999999,4,3,11,5,3,11,分库1,分库2,分库3,7,4,5,3,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,step3:反查出每一个返回结果的offset,selectc1fromtb1orderbyc1limit9999999,4,3,11,5,3,11,33333331条,33333333条,33333332条,分库1,分库2,分库3,7,4,5,3,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,selectc1fromtb1orderbyc1limit9999999,4,3,11,5,3,11,9999996,分库1,分库2,分库3,7,4,5,3,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,selectc1fromtb1orderbyc1limit9999999,4,11,5,3,11,9999997,分库1,分库2,分库3,7,4,5,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,selectc1fromtb1orderbyc1limit9999999,4,11,5,3,11,9999998,分库1,分库2,分库3,7,4,5,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,selectc1fromtb1orderbyc1limit9999999,4,11,5,11,9999999,最终结果集,最终结果集,分库1,分库2,分库3,7,4,5,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,selectc1fromtb1orderbyc1limit9999999,4,11,5,11,9999999,最终结果集,分库1,分库2,分库3,7,4,5,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,selectc1fromtb1orderbyc1limit9999999,4,11,5,11,9999999,最终结果集,分库1,分库2,分库3,7,4,5,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,selectc1fromtb1orderbyc1limit9999999,4,11,5,11,9999999,最终结果集,分库1,分库2,分库3,7,4,5,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,selectc1fromtb1orderbyc1limit9999999,4,11,5,11,9999999,OrderBy/Limit再优化,Step1不必得到全部结果selectmin(c1)mi,max(c1)mafrom(selectc1fromtb1orderbyc1limit3333333,4)tStep2和Step3合并select*from(select*fromtb1wherec1betweenmiandma)t1,(selectcount(*)fromtb1wherec1、!、VariableUser-DefSystemVariable：version,基本元素-Literal,StringLiteralNabc、_latin1abc、abcdef”abc”d”、abcdNumberLiteral123、.123、123.、123.e4、.123E-4123e4vs123e4fHex/BitLiteral0 x89af、x89af、0b101011、b101011_latin10 x89af,基本元素,语法元素,MySQL5.5语法结构,Identifier,Keyword,Literal,Punctuation,表达式,TableReferences,Limit,OrderBy,.,Delete,Update,Insert,Replace,Select,Call,Set/Show,SQL语句,Variable,语法元素TableReferences,table_references:table_reference,table_referencetable_reference:table_factorINNER|CROSSJOINtable_factorjoin_condition|STRAIGHT_JOINtable_factorONconditional_expr|(LEFT|RIGHT)OUTERJOINtable_referencejoin_condition|NATURAL(LEFT|RIGHT)OUTERJOINtable_factortable_factor:tbl_nameASaliasindex_hint_list|table_subqueryASalias|(table_references)join_condition:ONconditional_expr|USING(column_list)index_hint_list:index_hintindex_hintindex_hint:USE(INDEX|KEY)FOR(JOIN|ORDERBY|GROUPBY)(index_list)|IGNORE(INDEX|KEY)FOR(JOIN|ORDERBY|GROUPBY)(index_list)|FORCE(INDEX|KEY)FOR(JOIN|ORDERBY|GROUPBY)(index_list)index_list:index_name,index_name,MySQL不支持的MySQL语法,table_reference:table_factorJOINtable_factorjoin_conditiontable_reference:table_factorLEFTJOINtable_referencejoin_condition,LEFTJOIN,tb1,t2,JOIN,t3,USING(id),规则1,规则2,MySQL不支持的MySQL语法,table_reference:table_factorJOINtable_factorjoin_conditiontable_reference:table_factorLEFTJOINtable_referencejoin_condition,LEFTJOIN,tb1,t2,JOIN,t3,USING(id),table_factor,规则1,规则2,MySQL不支持的MySQL语法,table_reference:table_factorJOINtable_factorjoin_conditiontable_reference:table_factorLEFTJOINtable_referencejoin_condition,LEFTJOIN,tb1,t2,JOIN,t3,USING(id),table_factor,规则1,规则2,table_reference,table_reference,规则2,table_factor,MySQL不支持的MySQL语法,table_reference:table_factorJOINtable_factorjoin_conditiontable_reference:table_factorLEFTJOINtable_referencejoin_condition,LEFTJOIN,tb1,t2,JOIN,t3,USING(id),table_factor,table_factor,table_factor,join_condition,规则1,规则2,table_reference,table_reference,规则1,规则2,MySQL不支持的MySQL语法,table_reference:table_factorJOINtable_factorjoin_conditiontable_reference:table_factorLEFTJOINtable_referencejoin_condition,LEFTJOIN,tb1,t2,JOIN,t3,USING(id),table_factor,table_factor,table_factor,join_condition,规则1,规则2,table_reference,table_reference,ERROR!,规则1,规则2,MySQL不支持的MySQL语法,table_reference:table_factorJOINtable_factorjoin_conditiontable_reference:table_factorLEFTJOINtable_referencejoin_condition,LEFTJOIN,tb1,t2,JOIN,t3,USING(id),table_factor,table_factor,table_factor,table_reference,join_condition,table_reference,table_factor,table_factor,table_factor,join_condition,规则1,规则2,table_reference,table_reference,ERROR!,规则1,规则1,规则2,规则2,MySQL不支持的MySQL语法,table_reference:table_factorJOINtable_factorjoin_conditiontable_reference:table_factorLEFTJOINtable_referencejoin_condition,LEFTJOIN,tb1,t2,JOIN,t3,USING(id),table_factor,table_factor,table_factor,table_reference,join_condition,table_reference,table_factor,table_factor,table_factor,join_condition,规则1,规则2,table_reference,table_reference,ERROR!,规则1,规则1,规则2,规则2,TableReferences的表达式特性,table_references:table_reference,table_referencetable_reference:(table_references)|(queryUNIONquery)ASaliasquery:(query)|SELECT.,(select.)UNOIN(select.)ASt1,(t1,t2),t3),LL(1),规则1,规则2,规则2,规则1,expr:(expr)|tableRefs|subquery,类似于表达式：,基本元素,语法元素,MySQL5.5语法结构,Identifier,Keyword,Literal,Punctuation,表达式,TableReferences,Limit,OrderBy,.,Delete,Update,Insert,Replace,Select,Call,Set/Show,SQL语句,Variable,表达式,优先级和结合型MySQLManual文档不精确文档+实验特殊函数Keyword作为函数名：23个INSERT(Quadratic,3,4,What)非规则参数列表：13个SELECTTRIM(BOTHxFROMxxxbarx)特殊Identifier1=any+21=any(select),simple:selectidfromt1“short:seLEcTid,member_id,image_patht,image_size,STATUS,gmt_modifiedfromwp_imagewheRettnid=?ANDmember_idt=t-123.456short2:selectcount(*)fromMESSAGE_REC_RECORDwhereRECEIVER_VACOUNT=?andRECEIVER_IDin(?,?,?,?,?,?,?,?,?,?,?,?)andRECEIVER_STATUSin(?,?)andSPAM_STATUSin(?)andDELETE_STATUS=?long:selectID,GMT_CREATE,GMT_MODIFIED,INBOX_FOLDER_ID,MESSAGE_ID,FEEDBACK_TYPE,TARGET_ID,TRADE_ID,SUBJECT,SENDER_ID,SENDER_TYPE,S_DISPLAY_NAME,SENDER_STATUS,RECEIVER_ID,RECEIVER_TYPE,R_DISPLAY_NAME,RECEIVER_STATUS,SPAM_STATUS,REPLY_STATUS,ATTACHMENT_STATUS,SENDER_COUNTRY,RECEIVER_COUNTRY,APP_FROM,APP_TO,APP_SOURCE,SENDER_VACOUNT,RECEIVER_VACOUNT,DISTRIBUTE_STATUS,ORG_RECEIVER_ID,CUSTOMER_ID,OPERATOR_ID,OPERATOR_NAME,FOLLOW_STATUS,DELETE_STATUS,FOLLOW_TIME,BATCH_COUNTfromMESSAGE_REC_RECORDwhereRECEIVER_VACOUNT=?andID=?long2:selectA.ID,A.GMT_CREATE,A.GMT_MODIFIED,A.MESSAGE_ID,A.SENDER_COMPANY_ID,A.SENDER_ID,A.SENDER_IP,A.SENDER_TYPE,A.SENDER_COMPANY,A.SENDER_ADDRESS,A.SENDER_COUNTRY,A.SENDER_ZIP,A.SENDER_PHONE,A.SENDER_FAX,A.SENDER_EMAIL,A.RECEIVER_COMPANY_ID,A.RECEIVER_ID,A.RECEIVER_TYPE,A.RECEIVER_COMPANY,A.RECEIVER_ADDRESS,A.RECEIVER_COUNTRY,A.RECEIVER_ZIP,A.RECEIVER_PHONE,A.RECEIVER_FAX,A.RECEIVER_EMAIL,A.TARGET_ID,A.FEEDBACK_TYPE,A.FEEDBACK_CATEGORY,A.SUBJECT,A.APP_SOURCE,A.ATTACHMENT_IDS,B.REMARK,A.S_DISPLAY_NAME,A.R_DISPLAY_NAME,A.RELATED_FEEDBACK_IDS,A.NOTICE_MESSAGE,B.PROPERTIES,A.APP_FROM,APP_TO,A.SENDER_VACOUNT,A.RECEIVER_VACOUNTfromMESSAGE_DETAILA,MESSAGE_DETAIL_REMARK_PROPERTIESBwhereA.ID=?andA.ID=B.ID,性能对比,GC对比,Processor(1),Processor(n),MySQL,MySQL,MySQL,Cobar结构,Front-endCommunication,Application1,MySQL,MySQL,MySQL,MonitorConfigure,MySQL,MySQL,SQLExecutor,SQLRouter,SQLParser,ResultMerger,SQLExecutor,SQLRouter,SQLParser,ResultMerger,.,Manager,MySQLProtocol,MySQLProtocol,MySQLProtocol,MySQLProtocol,ManagementProtocol,MySQLProtcolAdaptor(BIO),DataNodes,HAPool,后台数据访问逻辑层次,HAPool,MySQLProtocolAdapter,DataNode,S,M,S,M,S,M,S,M,S,M,ip:port/offer,ip:port/offer,ip:port/ibank,