Mycat-doc-mastermycatmycat技术分享

MyCat技术分享,原理、实现和应用,2014.10.31,肖雄,1.Mycat解决什么?2.Mycat架构3.Mycat应用,Mycat解决什么？,Oracle,MySQL,MSSQL,DB2,性能容量高可用,.,如果我有一个32核心的服务器，我就可以实现1个亿的数据分片，我有32核心的服务器么？没有，所以我至今无法实现1个亿的数据分片。MyCATsPlan,应用,应用,应用,应用,MyCat,为什么选择MyCat?,1.支持读写分离，支持Mysql双主多从，以及一主多从的模式2.支持全局表，数据自动分片到多个节点，用于高效表关联查询3.支持独有的基于E-R关系的分片策略，实现了高效的表关联查询4.自动故障切换，高可用性5.提供高可用性数据分片集群6.支持JDBC连接ORACLE、DB2、SQLServer，将其模拟为MySQLServer使用7.支持Mysql集群，可以作为Proxy使用8.基于阿里开源的Cobar产品而研发，Cobar的稳定性、可靠性、优秀的架构和性能,MyCat架构,MySQLCLI/JDBC/ODBC/,数据库驱动,Mycat结构,数据库实例,数据库A,数据库B,数据库C,More,MySQLSocketProtocolHandler,SQLParser,SQLRouter,SQLExecutor,DataNodes,HeartBeatChecker,中间件引入MyCat策略系统实现实施应用,MyCat应用,性能问题数据库连接过多E-R分片难处理可用性问题Standby切换故障成本和伸缩性问题依赖高成本的硬件设备,Mycat引入水平拆分,MySQL,MySQL,MySQL,MyCat,应用,Member表,Member表,Member表,性能问题数据库连接过多E-R分片难处理可用性问题Standby切换故障成本和伸缩性问题依赖高成本的硬件设备,应用,MySQL,MySQL,MySQL,应用,应用,应用,应用,应用,应用,应用,Member表,Member表,Member表,Mycat引入连接复用,App,MySQL,MySQL,MySQL,MycatProxy,App,App,App,App,App,App,App,性能问题数据库连接过多E-R分片难处理可用性问题Standby切换故障成本和伸缩性问题依赖高成本的硬件设备,MyCat引入,MySQL,应用,Member表,Order表,Product表,1n,1.n,MySQL,Member表,Order表,Product表,1n,1.n,MyCat,性能问题数据库连接过多E-R分片难处理可用性问题Standby切换故障成本和伸缩性问题依赖高成本的硬件设备,MyCat引入,MySQL,Mycat,App,X,MyCat引入failover,MySQLMaster1,MyCat,App,MySQLMaster2,X,MySQLReplication,MyCat引入failover,Mycat,App,MySQLReplication,MySQLMaster1,MySQLMaster2,大纲,中间件引入MyCat策略系统实现实施应用,mysql,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转发,Mycat,App,select*fromtb1wheremember_id=test1234,拆分表的数据访问SQL转发,Mycat,App,select*fromtb1wheremember_id=test1234,Mycat,App,SELECT*FROMtb1WHEREmember_idIN(test1234,pavarotti17,abcd),拆分表的数据访问SQL转发,Mycat,App,select*fromtb1wheremember_idin(test1234,pavarotti17),select*fromtb1wheremember_idin(abcd),拆分表的数据访问SQL转发,ResultMerger,前台通信,ResultSet:row1row2,ResultSet:row3row4row5,ResultSet:row3row1row4row5row2,拆分表的数据访问结果返回,MyCat的策略,基于表的水平拆分和分布根据字段值的一致性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(*)fromtb1wherec1mi)t2,GroupBy,SELECTsum(price)FROMtb1GROUPBYc1,GroupBy,SELECTsum(price)FROMtb1GROUPBYc1,SELECTsum(price),c1FROMtb1GROUPBYc1ORDERBYc1,12.32222,7.9131,8.8604,99.956,7.6131,4.4604,GroupBy,SELECTsum(price)FROMtb1GROUPBYc1,SELECTsum(price),c1FROMtb1GROUPBYc1ORDERBYc1,12.32222,7.9131,8.8604,7.6131,4.4604,最终结果集,99.956,GroupBy,SELECTsum(price)FROMtb1GROUPBYc1,SELECTsum(price),c1FROMtb1GROUPBYc1ORDERBYc1,12.32222,7.9131,8.8604,7.6131,4.4604,最终结果集,99.956,最终结果集,GroupBy,SELECTsum(price)FROMtb1GROUPBYc1,SELECTsum(price),c1FROMtb1GROUPBYc1ORDERBYc1,12.32222,15.5131,8.8604,4.4604,99.956,最终结果集,GroupBy,SELECTsum(price)FROMtb1GROUPBYc1,SELECTsum(price),c1FROMtb1GROUPBYc1ORDERBYc1,12.32222,15.5131,13.2604,99.956,最终结果集,GroupBy,SELECTsum(price)FROMtb1GROUPBYc1,SELECTsum(price),c1FROMtb1GROUPBYc1ORDERBYc1,12.32222,15.5131,13.2604,99.956,SQL执行策略总结,WHERE-基于SQL转发JOIN-迭代分布式索引ORDERBY/LIMIT-多次查询减小数据量GROUPBY-增加ORDERBY,MyCat事务支持,前端连接,sql1,sql2,commit,MyCat事务支持,前端连接,分库1连接,sql1,sql2,commit,MyCat事务支持,前端连接,分库1连接,sql1,commit,分库2连接,sql2,分库3连接,sql2,MyCat事务支持,前端连接,分库1连接,sql1,commit,分库2连接,sql2,分库3连接,sql2,Commit有先后：隔离性问题Commit有失败：一致性问题,大纲,中间件引入MyCat策略水平拆分的数据分布几种SQL元素的执行策略事务策略系统实现实施应用,schema,tableSpace,dataNode,datasource,cndb,pc2,主,备,主,备,default,offer0,主,备,offer1,主,备,detail0,主,备,default,default,offer,detail,detail,逻辑层次接口同MySQL,jdbc:mysql:/cobarIp:8066/cndb?user=foops.setLong(1,12345);ResultSetrs=ps.executeQuery();,MySQLServer,Application1,MySQLJDBCDriver,JDBC和Server的通信协议,MySQLServer,Application1,MySQLJDBCDriver,PreparedStatementps=conn.prepareStatement(select*fromtb1whereid=?);ps.setLong(1,12345);ResultSetrs=ps.executeQuery();,select*fromtb1whereid=?,stmt_id/param_num/columm_num,parameter_type,column_type,JDBC和Server的通信协议,PreparedStatementps=conn.prepareStatement(select*fromtb1whereid=?);ps.setLong(1,12345);ResultSetrs=ps.executeQuery();,MySQLServer,Application1,MySQLJDBCDriver,JDBC和Server的通信协议,PreparedStatementps=conn.prepareStatement(select*fromtb1whereid=?);ps.setLong(1,12345);ResultSetrs=ps.executeQuery();,MySQLServer,Application1,MySQLJDBCDriver,stmt_idparam+,field_count,column_type,column_val+,column_val+,JDBC和Server的通信协议,PreparedStatementps=conn.prepareStatement(select*fromtb1whereid=?);ps.setLong(1,12345);ResultSetrs=ps.executeQuery();,Application1,MySQLJDBCDriver,stmt_idparam+,field_count,column_type,column_val+,column_val+,MySQLServer,JDBC和Server的通信协议,Statementstmt=conn.createStatement();ResultSetrs=stmt.executeQuery(“select*fromtb1whereid=12345”);,Application1,MySQLJDBCDriver,sql,field_count,column_type,column_val+,column_val+,MySQLServer,JDBC和Server的通信协议,Statementstmt=conn.createStatement();ResultSetrs=stmt.executeQuery(“select*fromtb1whereid=12345”);,Application1,MySQLJDBCDriver,sql,field_count,column_type,column_val+,column_val+,MySQLServer,CobarServer,Processor(1),Processor(n),MySQL,MySQL,MySQL,Mycat结构,Front-endCommunication,MySQLProtcolAdaptor(BIO),Application1,MySQL,MySQL,MySQL,DataNodes,MonitorConfigure,HAPool,MySQL,MySQL,SQLExecutor,SQLRouter,SQLParser,ResultMerger,SQLExecutor,SQLRouter,SQLParser,ResultMerger,.,Manager,MySQLProtocol,MySQLProtocol,MySQLProtocol,MySQLProtocol,ManagementProtocol,SQL+Parameters,ResultSetMetaDataResultSet(Rows),.,MySQL,MySQL,MySQL,Mycat结构,Front-endCommunication,MySQLProtcolAdaptor(BIO),Application1,MySQL,MySQLProtocol,MySQL,MySQL,DataNodes,MonitorConfigure,HAPool,MySQL,MySQL,Processor(1),Processor(n),SQLExecutor,SQLRouter,ResultMerger,SQLExecutor,SQLRouter,ResultMerger,Manager,MySQLProtocol,MySQLProtocol,MySQLProtocol,MySQLProtocol,ManagementProtocol,SQLParser,SQLParser,SELECTid,member_idFROMwp_imageWHEREmember_id=123,SQLParser,select,id,member_id,wp_image,=,member_id,123,exprList,from,Processor(1),Processor(n),MySQL,MySQL,MySQL,Mycat结构,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,ip:port/ibank,ip:port/ibank,数据库连接基于协议数据包与MySQ