mycat技术分享

1、MyCat技术分享,原理、实现和应用,2014.10.31,肖雄,1.Mycat解决什么?2.Mycat架构3.Mycat应用,Mycat解决什么？,Oracle,MySQL,MSSQL,DB2,性能 容量 高可用,.,如果我有一个32核心的服务器，我就可以实现1个亿的数据分片， 我有32核心的服务器么？没有，所以我至今无法实现1个亿的数据分片。 MyCAT s Plan,应用,应用,应用,应用,MyCat,为什么选择MyCat?,1.支持读写分离，支持Mysql双主多从，以及一主多从的模式 2.支持全局表，数据自动分片到多个节点，用于高效表关联查询 3.支持独有的基于E-R 关系的分片策略，

2、实现了高效的表关联查询 4.自动故障切换，高可用性 5.提供高可用性数据分片集群 6.支持JDBC连接ORACLE、DB2、SQL Server，将其模拟为 MySQL Server使用 7.支持Mysql集群，可以作为Proxy使用 8.基于阿里开源的Cobar产品而研发，Cobar的稳定性、可靠性、 优秀的架构和性能,MyCat架构,MySQL CLI/JDBC/ODBC/,数据库驱动,Mycat结构,数据库实例,数据库A,数据库B,数据库C,More,MySQL Socket Protocol Handler,SQL Parser,SQL Router,SQL Executor,Data

3、Nodes,HeartBeat Checker,中间件引入 MyCat策略 系统实现 实施应用,MyCat应用,性能问题 数据库连接过多 E-R分片难处理 可用性问题 Standby切换故障 成本和伸缩性问题 依赖高成本的硬件设备,Mycat引入 水平拆分,MySQL,MySQL,MySQL,MyCat,应用,Member表,Member表,Member表,性能问题 数据库连接过多 E-R分片难处理 可用性问题 Standby切换故障 成本和伸缩性问题 依赖高成本的硬件设备,应用,MySQL,MySQL,MySQL,应用,应用,应用,应用,应用,应用,应用,Member表,Member表,Me

4、mber表,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引入,M

5、ySQL,Mycat,App,X,MyCat引入 failover,MySQL Master1,MyCat,App,MySQL Master2,X,MySQLReplication,MyCat引入 failover,Mycat,App,MySQLReplication,MySQL Master1,MySQL Master2,大纲,中间件引入 MyCat策略 系统实现 实施应用,mysql,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,拆分数据表,水平拆分,水平拆分,拆分字段,水平拆分,f(pava

6、rotti17)=库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( ) = 317097296

7、5401,路由算法,hash(pavarott) = 3170972965401 % 1024 = 537,0 1023,路由算法,hash(pavarott) = 3170972965401 % 1024 = 537,0 255,256 511,512 767,768 1023,256,256,256,256,分库1,分库2,分库3,分库4,路由算法,hash(pavarott) = 3170972965401 % 1024 = 537,0 255,256 511,512 767,768 1023,256,256,256,256,分库1,分库2,分库3,分库4,路由算法扩容,hash(pav

8、arott) = 3170972965401 % 1024 = 537,0 127 128 255,256 383 384 511,512 639 640 767,768 895 896 1023,128,128,128,128,128,128,128,128,分库1,分库2,分库3,分库4,路由算法扩容,hash(pavarott) = 3170972965401 % 1024 = 537,0 127 128 255,256 383 384 511,512 639 640 767,768 895 896 1023,128,128,128,128,128,128,128,128,分库1,分库2

9、,分库3,分库4,分库5,分库6,分库7,分库8,原,分库1,分库2,分库3,分库4,原,原,原,路由算法非均匀分布,hash(pavarott) = 3170972965401 % 1024 = 537,0 511,512 767,768 895 896 1023,512,256,128,128,分库1,分库2,分库3,分库4,拆分表的数据访问SQL转发,Mycat,App,select * from tb1 where member_id=test1234,拆分表的数据访问SQL转发,Mycat,App,select * from tb1 where member_id=test1234,

10、Mycat,App,SELECT * FROM tb1 WHERE member_id IN (test1234,pavarotti17,abcd),拆分表的数据访问SQL转发,Mycat,App,select * from tb1 where member_id in (test1234,pavarotti17),select * from tb1 where member_id in (abcd),拆分表的数据访问SQL转发,ResultMerger,前台通信,ResultSet:row1row2,ResultSet:row3row4row5,ResultSet:row3row1row4r

11、ow5row2,拆分表的数据访问结果返回,MyCat的策略,基于表的水平拆分和分布 根据字段值的一致性Hash分布 根据字段值的范围分布 根据字段值的固定分布 数据查询方式 根据where中的拆分字段分发 SQL语句其他元素的处理 将Cobar收到的SQL语句做变换 分发到各个分库执行 对执行结果合并、处理 保证返回前端的内容满足语义,JOIN有限的处理,跨库JOIN问题,SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME,tb1,tb1,tb2,tb2,迭代查询,SELECT * FROM tb1 INNER JOIN tb2 O

12、N t1.MEMBER_ID=t2.NAME,tb1,tb1,tb2,tb2,FOR row1 IN select * FROM tb1 ADD(SELECT * FROM tb2 WHERE = row1.member_id)TO RESULT ,跨库索引,tb1,tb1,tb2,tb2,idx,idx,扫描idx，再根据每一行的id1,id2查到最终结果,跨库索引,tb1,tb1,tb2,tb2,SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME WHERE t1.id = 5,SELECT * FROM id

13、x WHERE id1 = 5,再根据id1,id2查到最终结果,跨库索引,tb1,tb1,tb2,tb2,idx,idx,一定以 JOIN_COL 为索引的拆分字段吗？,跨库索引,SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME WHERE t1.gmt600,tb1,tb1,tb2,tb2,跨库索引,tb1,tb1,tb2,tb2,idx,idx,tb1,tb1,tb2,tb2,idx,idx,SELECT idx.id2, tb1.* FROM idx INNER JOIN tb1 ON idx.id1=tb1.id WHE

14、RE t1.gmt600,SELECT idx.id2, tb1.* FROM idx INNER JOIN tb1 ON idx.id1=tb1.id WHERE t1.gmt600,SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME WHERE t1.gmt600,跨库索引,跨库索引,tb1,tb1,tb2,tb2,SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME WHERE t1.gmt600 AND t2.time600,idx,跨库索引,idx,idx,索引表

15、的拆分 WHERE条件中的字段所在表的拆分字段，作为索引拆分字段 索引包含 两张表的主键 JOIN字段 WHERE中的其他字段 索引的更新 分布式事务的支持,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,select . order by c1 limit 0, 6,select . order by c1 limit 0, 6,select . order by c1 limit 0, 6,分库1,分库2,分库3,cobar,Order By/Limit,分库1,分库2,分库3,Order By/Limit,4,5,6,3,2,7,8,10,6,5,1,

16、11,13,14,9,7,3,返回结果,返回结果,返回结果,8,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,分库1,分库2,分库3,Order By/Limit,4,5,6,3,2,7,8,10,6,5,1,11,13,14,9,7,3,返回结果,返回结果,返回结果,8,0,最终结果集,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,分库1,分库2,分库3,Order By/Limit,4,5,6,3,2,7,8,10,6,5,11,13,14,9,7,3,返回结果,返回结果,返回结果,8,1,最终结果集,SELECT

17、 c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,分库1,分库2,分库3,Order By/Limit,4,5,6,3,7,8,10,6,5,11,13,14,9,7,3,返回结果,返回结果,返回结果,8,2,最终结果集,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,分库1,分库2,分库3,Order By/Limit,4,5,6,7,8,10,6,5,11,13,14,9,7,3,返回结果,返回结果,返回结果,8,3,最终结果集,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,分库1,分库2,分库

18、3,Order By/Limit,4,5,6,7,8,10,6,5,11,13,14,9,7,返回结果,返回结果,返回结果,8,4,最终结果集,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,最终结果集,分库1,分库2,分库3,Order By/Limit,5,6,7,8,10,6,5,11,13,14,9,7,返回结果,返回结果,返回结果,8,4,4,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,最终结果集,分库1,分库2,分库3,Order By/Limit,4,6,7,8,10,6,5,11,13,14,9,7,返

19、回结果,返回结果,返回结果,8,4,5,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,Order By/Limit 方案总结,select c1 from tb1 order by c1 limit 100000000, 2,所有分库都要查询100000002条数据 Cobar需要遍历100000002条数据,对如下SQL,一次交互得到结果 Offset大小有限制,Order By / Limit 优化,目标：解决 查询量大问题 遍历量大问题 前提 各个分库数据分布大致一样,select c1 from tb1 order by c1 limit 9999

20、999, 4,select . order by c1 limit 33333333, 4,select . order by c1 limit 33333333, 4,select . order by c1 limit 33333333, 4,分库1,分库2,分库3,step1:分成3条语句发给分库,分库1,分库2,分库3,7,4,5,3,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,select c1 from tb1 order by c1 limit 9999999, 4,找出查询结果中最小和最大值,分库1,分库2,分库3,7,4,5,3,8,6,9,10,7,6

21、,11,9,返回结果,返回结果,返回结果,step2:以最小值和最大值为界再查询,select c1 from tb1 order by c1 limit 9999999, 4,3,11,5,3,11,分库1,分库2,分库3,7,4,5,3,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,step3:反查出每一个返回结果的offset,select c1 from tb1 order by c1 limit 9999999, 4,3,11,5,3,11,33333331条,33333333条,33333332条,分库1,分库2,分库3,7,4,5,3,8,6,9,10,7,6

22、,11,9,返回结果,返回结果,返回结果,类似于原始方案,select c1 from tb1 order by c1 limit 9999999, 4,3,11,5,3,11,9999996,分库1,分库2,分库3,7,4,5,3,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,select c1 from tb1 order by c1 limit 9999999, 4,11,5,3,11,9999997,分库1,分库2,分库3,7,4,5,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,select c1 from tb

23、1 order by c1 limit 9999999, 4,11,5,3,11,9999998,分库1,分库2,分库3,7,4,5,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,select c1 from tb1 order by c1 limit 9999999, 4,11,5,11,9999999,最终结果集,最终结果集,分库1,分库2,分库3,7,4,5,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,select c1 from tb1 order by c1 limit 9999999, 4,11,5,11,9

24、999999,最终结果集,分库1,分库2,分库3,7,4,5,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,select c1 from tb1 order by c1 limit 9999999, 4,11,5,11,9999999,最终结果集,分库1,分库2,分库3,7,4,5,8,6,9,10,7,6,11,9,返回结果,返回结果,返回结果,类似于原始方案,select c1 from tb1 order by c1 limit 9999999, 4,11,5,11,9999999,最终结果集,分库1,分库2,分库3,7,4,5,8,6,9,10,7,

25、6,11,9,返回结果,返回结果,返回结果,类似于原始方案,select c1 from tb1 order by c1 limit 9999999, 4,11,5,11,9999999,Order By / Limit 再优化,Step1不必得到全部结果 select min(c1) mi, max(c1) ma from (select c1 from tb1 order by c1 limit 3333333,4) t Step2和Step3合并 select * from (select * from tb1 where c1 between mi and ma) t1, (selec

26、t count(*) from tb1 where c1 mi) t2,Group By,SELECT sum(price) FROM tb1 GROUP BY c1,Group By,SELECT sum(price) FROM tb1 GROUP BY c1,SELECT sum(price), c1 FROM tb1 GROUP BY c1 ORDER BY c1,12.32222,7.9131,8.8604,99.956,7.6131,4.4604,Group By,SELECT sum(price) FROM tb1 GROUP BY c1,SELECT sum(price), c1

27、 FROM tb1 GROUP BY c1 ORDER BY c1,12.32222,7.9131,8.8604,7.6131,4.4604,最终结果集,99.956,Group By,SELECT sum(price) FROM tb1 GROUP BY c1,SELECT sum(price), c1 FROM tb1 GROUP BY c1 ORDER BY c1,12.32222,7.9131,8.8604,7.6131,4.4604,最终结果集,99.956,最终结果集,Group By,SELECT sum(price) FROM tb1 GROUP BY c1,SELECT su

28、m(price), c1 FROM tb1 GROUP BY c1 ORDER BY c1,12.32222,15.5131,8.8604,4.4604,99.956,最终结果集,Group By,SELECT sum(price) FROM tb1 GROUP BY c1,SELECT sum(price), c1 FROM tb1 GROUP BY c1 ORDER BY c1,12.32222,15.5131,13.2604,99.956,最终结果集,Group By,SELECT sum(price) FROM tb1 GROUP BY c1,SELECT sum(price), c1

29、 FROM tb1 GROUP BY c1 ORDER BY c1,12.32222,15.5131,13.2604,99.956,SQL执行策略总结,WHERE - 基于SQL转发 JOIN - 迭代 分布式索引 ORDER BY/LIMIT - 多次查询减小数据量 GROUP BY - 增加ORDER BY,MyCat 事务支持,前端连接,sql1,sql2,commit,MyCat 事务支持,前端连接,分库1连接,sql1,sql2,commit,MyCat 事务支持,前端连接,分库1连接,sql1,commit,分库2连接,sql2,分库3连接,sql2,MyCat 事务支持,前端连接

30、,分库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/

31、cndb?user=foo ps.setLong(1, 12345); ResultSet rs = ps.executeQuery();,MySQLServer,Application1,MySQLJDBC Driver,JDBC和Server的通信协议,MySQLServer,Application1,MySQLJDBC Driver,PreparedStatement ps = conn.prepareStatement( select * from tb1 where id=?); ps.setLong(1, 12345); ResultSet rs = ps.executeQuery

32、();,select * from tb1where id=?,stmt_id/param_num/columm_num,parameter_type,column_type,JDBC和Server的通信协议,PreparedStatement ps = conn.prepareStatement( select * from tb1 where id=?); ps.setLong(1, 12345); ResultSet rs = ps.executeQuery();,MySQLServer,Application1,MySQLJDBC Driver,JDBC和Server的通信协议,Pre

33、paredStatement ps = conn.prepareStatement( select * from tb1 where id=?); ps.setLong(1, 12345); ResultSet rs = ps.executeQuery();,MySQLServer,Application1,MySQLJDBC Driver,stmt_id param+,field_count,column_type,column_val+,column_val+,JDBC和Server的通信协议,PreparedStatement ps = conn.prepareStatement( se

34、lect * from tb1 where id=?); ps.setLong(1, 12345); ResultSet rs = ps.executeQuery();,Application1,MySQLJDBC Driver,stmt_id param+,field_count,column_type,column_val+,column_val+,MySQLServer,JDBC和Server的通信协议,Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery( “select * from tb1

35、 where id=12345”);,Application1,MySQLJDBC Driver,sql,field_count,column_type,column_val+,column_val+,MySQLServer,JDBC和Server的通信协议,Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery( “select * from tb1 where id=12345”);,Application1,MySQLJDBC Driver,sql,field_count,column_type,

36、column_val+,column_val+,MySQLServer,CobarServer,Processor(1),Processor(n),MySQL,MySQL,MySQL,Mycat结构,Front-end Communication,MySQL Protcol Adaptor (BIO),Application1,MySQL,MySQL,MySQL,Data Nodes,Monitor Configure,HA Pool,MySQL,MySQL,SQL Executor,SQL Router,SQL Parser,ResultMerger,SQL Executor,SQL Rou

37、ter,SQL Parser,ResultMerger,.,Manager,MySQL Protocol,MySQL Protocol,MySQL Protocol,MySQL Protocol,ManagementProtocol,SQL +Parameters,ResultSetMetaDataResultSet(Rows),.,MySQL,MySQL,MySQL,Mycat结构,Front-end Communication,MySQL Protcol Adaptor (BIO),Application1,MySQL,MySQL Protocol,MySQL,MySQL,Data Nod

38、es,Monitor Configure,HA Pool,MySQL,MySQL,Processor(1),Processor(n),SQL Executor,SQL Router,ResultMerger,SQL Executor,SQL Router,ResultMerger,Manager,MySQL Protocol,MySQL Protocol,MySQL Protocol,MySQL Protocol,ManagementProtocol,SQL Parser,SQL Parser,SELECT id, member_id FROM wp_image WHERE member_id

39、 = 123,SQL Parser,select,id,member_id,wp_image,=,member_id,123,exprList,from,Processor(1),Processor(n),MySQL,MySQL,MySQL,Mycat结构,Front-end Communication,Application1,MySQL,MySQL,MySQL,Monitor Configure,MySQL,MySQL,SQL Executor,SQL Router,SQL Parser,ResultMerger,SQL Executor,SQL Router,SQL Parser,ResultMerger,.,Manager,MySQL Protocol,MySQL Protocol,MySQL Protocol,MySQL Protocol,ManagementProtocol,MySQL Protcol Adaptor (BIO),Data Nodes,HA Pool,后台数据访问逻辑层次,HA Pool,MySQL Protocol Adapter,Data Node,S,M,S,M,S,M,S,M,S,M,ip:port/offer,ip:port/offer,ip:port/ibank,ip:port/ibank