项目10配套软件mycat技术

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

2、换，高可用性 5.提供高可用性数据分片集群 6.支持JDBC连接ORACLE、DB2、SQL Server，将其模拟为 MySQL Server使用 7.支持Mysql集群，可以作为Proxy使用 8.基于阿里开源的Cobar产品而研发，Cobar的稳定性、可靠性、 优秀的架构和性能MyCat架构MySQL CLI/JDBC/ODBC/数据库驱动Mycat结构数据库实例数据库A数据库B数据库CMoreMySQL Socket Protocol HandlerSQL ParserSQL RouterSQL ExecutorDataNodesHeartBeat Checkern 中间件引入n My

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

4、tti17MySQLIDMEMBE_IDINFO4test12345test1234IDMEMBE_IDINFO3abcd9abcd20abcdMySQLMySQLMycatProxyAppAppAppAppAppAppApp 性能问题 数据库连接过多 E-R分片难处理 可用性问题 Standby切换故障 成本和伸缩性问题 依赖高成本的硬件设备MyCat引入MySQL应用Member表Order表Product表1n1.nMySQLMember表Order表Product表1n1.nMyCat 性能问题 数据库连接过多 E-R分片难处理 可用性问题 Standby切换故障 成本和伸缩性问题 依

5、赖高成本的硬件设备MyCat引入IDMEMBE_IDINFO1pavarotti1711pavarotti17MySQLMycatAppXMyCat引入 failoverIDMEMBE_IDINFO1pavarotti1711pavarotti17MySQL Master1MyCatAppIDMEMBE_IDINFO1pavarotti1711pavarotti17MySQL Master2XMySQLReplicationMyCat引入 failoverIDMEMBE_IDINFO1pavarotti1711pavarotti17MycatAppIDMEMBE_IDINFO1pavarott

6、i1711pavarotti17MySQLReplicationMySQL Master1MySQL Master2大纲n 中间件引入n MyCat策略n 系统实现n 实施应用mysqlMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL拆分数据表水平拆分ID MEMBE_IDINFO1pavarotti173abcd4test12345test12349abcd11pavarotti1720abcd水平拆分IDMEMBE_IDINFO1pavarotti173abcd4test12345test12349abcd11pava

7、rotti1720abcd拆分字段水平拆分ID MEMBE_ID INFO1pavarotti173abcd4test12345test12349abcd11pavarotti1720abcdIDMEMBE_IDINFO1pavarotti174test12345test123411pavarotti17ID MEMBE_ID INFO3abcd9abcd20abcdf(pavarotti17)=库1f(test1234)=库1f(test1234)=库1f(pavarotti17)=库1f(abcd)=库2f(abcd)=库2f(abcd)=库2库1库2拆分字段水平拆分ID MEMBE_ID

8、 INFO1pavarotti173abcd4test12345test12349abcd11pavarotti1720abcdIDMEMBE_IDINFO1pavarotti174test12345test123411pavarotti17ID MEMBE_ID INFO3abcd9abcd20abcdf(abcd)=库2库1库2拆分字段f(pavarotti17)=库1f(test1234)=库1f(test1234)=库1f(pavarotti17)=库1f(abcd)=库2f(abcd)=库2路由算法路由算法pavarotti17f(pavarotti17)=库1路由算法pavarot

9、ti17部分截取hash( ) = 3170972965401路由算法hash(pavarott) = 3170972965401 % 1024 = 5370 1023路由算法hash(pavarott) = 3170972965401 % 1024 = 5370 255 256 511 512 767 768 1023256256256256分库1分库2分库3分库4路由算法hash(pavarott) = 3170972965401 % 1024 = 5370 255 256 511 512 767 768 1023256256256256分库1分库2分库3分库4路由算法扩容hash(pav

10、arott) = 3170972965401 % 1024 = 5370 127 128 255 256 383 384 511 512 639 640 767 768 895 896 1023128128128128128128128128分库1分库2分库3分库4路由算法扩容hash(pavarott) = 3170972965401 % 1024 = 5370 127 128 255 256 383 384 511 512 639 640 767 768 895 896 1023128128128128128128128128分库1分库2分库3分库4分库5分库6分库7分库8原分库1分库2分

11、库3分库4原原原路由算法非均匀分布hash(pavarott) = 3170972965401 % 1024 = 5370 511 512 767 768 895 896 1023512256128128分库1分库2分库3分库4拆分表的数据访问SQL转发IDMEMBE_IDINFO1pavarotti174test12345test123411pavarotti17IDMEMBE_IDINFO3abcd9abcd20abcdMycatAppselect * from tb1 where member_id=test1234拆分表的数据访问SQL转发IDMEMBE_IDINFO1pavarott

12、i174test12345test123411pavarotti17IDMEMBE_IDINFO3abcd9abcd20abcdMycatAppselect * from tb1 where member_id=test1234IDMEMBE_IDINFO1pavarotti174test12345test123411pavarotti17IDMEMBE_IDINFO3abcd9abcd20abcdMycatAppSELECT * FROM tb1 WHERE member_id IN (test1234,pavarotti17,abcd)拆分表的数据访问SQL转发IDMEMBE_IDINFO

13、1pavarotti174test12345test123411pavarotti17IDMEMBE_IDINFO3abcd9abcd20abcdMycatAppselect * from tb1 where member_id in (test1234,pavarotti17)select * from tb1 where member_id in (abcd)拆分表的数据访问SQL转发IDMEMBE_IDINFO1pavarotti174test12345test123411pavarotti17IDMEMBE_IDINFO3abcd9abcd20abcdResultMerger前台通信R

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

15、MEMBE_ID2zzzz4xyzxyzIDNAME4efghijk5aaaa6abcdIDMEMBE_ID1efghijk3xxxxxx5abcdIDNAME2zzzz3xyzxyztb1tb1tb2tb2IDMEMBER_IDIDNAME1efghijk4efghijk5abcd6abcd2zzzz2zzzz4xyzxyz3xyzxyz迭代查询SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAMEIDMEMBE_ID2zzzz4xyzxyzIDNAME4efghijk5aaaa6abcdIDMEMBE_ID1efghijk3xxxx

16、xx5abcdIDNAME2zzzz3xyzxyztb1tb1tb2tb2FOR row1 IN select * FROM tb1ADD(SELECT * FROM tb2 WHERE = row1.member_id)TO RESULT跨库索引IDMEMBE_ID2zzzz4xyzxyzIDNAME4efghijk5aaaa6abcdIDMEMBE_ID1efghijk3xxxxxx5abcdIDNAME2zzzz3xyzxyztb1tb1tb2tb2ID1ID2JOIN_COL22zzzz43xyzxyzidxID1ID2JOIN_COL14efghijk56abcdi

17、dx扫描idx，再根据每一行的id1,id2查到最终结果跨库索引IDMEMBE_ID2zzzz4xyzxyzIDNAME4efghijk5aaaa6abcdIDMEMBE_ID1efghijk3xxxxxx5abcdIDNAME2zzzz3xyzxyztb1tb1tb2tb2SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME WHERE t1.id = 5SELECT * FROM idx WHERE id1 = 5再根据id1,id2查到最终结果跨库索引IDMEMBE_ID2zzzz4xyzxyzIDNAME4efghijk5aa

18、aa6abcdIDMEMBE_ID1efghijk3xxxxxx5abcdIDNAME2zzzz3xyzxyztb1tb1tb2tb2ID1ID2JOIN_COL22zzzz43xyzxyzidxID1ID2JOIN_COL14efghijk56abcdidx一定以 JOIN_COL 为索引的拆分字段吗？跨库索引SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME WHERE t1.gmt600IDMEMBE_IDGMT2zzzz5254xyzxyz1010IDNAME4efghijk5aaaa6abcdIDMEMBE_IDGMT1e

19、fghijk12053xxxxxx1315abcd604IDNAME2zzzz3xyzxyztb1tb1tb2tb2跨库索引IDMEMBE_IDGMT2zzzz5254xyzxyz1010IDNAME4efghijk5aaaa6abcdIDMEMBE_IDGMT1efghijk12053xxxxxx1315abcd604IDNAME2zzzz3xyzxyztb1tb1tb2tb2ID1ID2JOIN_COL22zzzz43xyzxyzidxID1ID2JOIN_COL14efghijk56abcdidxIDMEMBE_ID2zzzz4xyzxyzIDMEMBE_ID1efghijk3xxxxx

20、x5abcdtb1tb1ID1ID2JOIN_COL22zzzz43xyzxyzidxID1ID2JOIN_COL14efghijk56abcdidxSELECT idx.id2, tb1.* FROM idx INNER JOIN tb1 ON idx.id1=tb1.id WHERE t1.gmt600SELECT idx.id2, tb1.* FROM idx INNER JOIN tb1 ON idx.id1=tb1.id WHERE t1.gmt600SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME WHERE t1.g

21、mt600跨库索引跨库索引IDMEMBE_IDGMT2zzzz5254xyzxyz1010IDNAMETIME4efghijk1235aaaa9226abcd222IDMEMBE_IDGMT1efghijk12053xxxxxx1315abcd604IDNAMETIME2zzzz12013xyzxyz1111tb1tb1tb2tb2SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME WHERE t1.gmt600 AND t2.time600ID1ID2JOIN_COLTIME22zzzz120143xyzxyz1111idx跨库索

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

23、分库2分库3cobarOrder By/Limit分库1分库2分库3Order By/Limit456327810651111314973返回结果返回结果返回结果8SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2分库1分库2分库3Order By/Limit456327810651111314973返回结果返回结果返回结果80最终结果集SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2分库1分库2分库3Order By/Limit45632781065111314973返回结果返回结果返回结果81最终结果集SELECT c1 F

24、ROM tb1 ORDER BY c1 LIMIT 4, 2分库1分库2分库3Order By/Limit4563781065111314973返回结果返回结果返回结果82最终结果集SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2分库1分库2分库3Order By/Limit456781065111314973返回结果返回结果返回结果83最终结果集SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2分库1分库2分库3Order By/Limit45678106511131497返回结果返回结果返回结果84最终结果集SELECT c

25、1 FROM tb1 ORDER BY c1 LIMIT 4, 2最终结果集分库1分库2分库3Order By/Limit5678106511131497返回结果返回结果返回结果844SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2最终结果集分库1分库2分库3Order By/Limit4678106511131497返回结果返回结果返回结果845SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2Order By/Limit 方案总结select c1 from tb1 order by c1 limit 100000000,

26、2 所有分库都要查询100000002条数据 Cobar需要遍历100000002条数据 对如下SQL 一次交互得到结果 Offset大小有限制Order By / Limit 优化 目标：解决 查询量大问题 遍历量大问题 前提 各个分库数据分布大致一样select c1 from tb1 order by c1 limit 9999999, 4select . order by c1 limit 33333333, 4select . order by c1 limit 33333333, 4select . order by c1 limit 33333333, 4分库1分库2分库3 st

27、ep1:分成3条语句发给分库分库1分库2分库374538691076119返回结果返回结果返回结果select c1 from tb1 order by c1 limit 9999999, 4 找出查询结果中最小和最大值分库1分库2分库374538691076119返回结果返回结果返回结果 step2:以最小值和最大值为界再查询select c1 from tb1 order by c1 limit 9999999, 43115311分库1分库2分库374538691076119返回结果返回结果返回结果 step3:反查出每一个返回结果的offsetselect c1 from tb1 ord

28、er by c1 limit 9999999, 4311531133333331条33333333条33333332条分库1分库2分库374538691076119返回结果返回结果返回结果 类似于原始方案select c1 from tb1 order by c1 limit 9999999, 431153119999996分库1分库2分库374538691076119返回结果返回结果返回结果 类似于原始方案select c1 from tb1 order by c1 limit 9999999, 41153119999997分库1分库2分库37458691076119返回结果返回结果返回结果

29、 类似于原始方案select c1 from tb1 order by c1 limit 9999999, 41153119999998分库1分库2分库37458691076119返回结果返回结果返回结果 类似于原始方案select c1 from tb1 order by c1 limit 9999999, 4115119999999最终结果集最终结果集分库1分库2分库37458691076119返回结果返回结果返回结果 类似于原始方案select c1 from tb1 order by c1 limit 9999999, 4115119999999最终结果集分库1分库2分库374 586

30、91076119返回结果返回结果返回结果 类似于原始方案select c1 from tb1 order by c1 limit 9999999, 4115119999999最终结果集分库1分库2分库374 58691076119返回结果返回结果返回结果 类似于原始方案select c1 from tb1 order by c1 limit 9999999, 4115119999999最终结果集分库1分库2分库374 58691076119返回结果返回结果返回结果 类似于原始方案select c1 from tb1 order by c1 limit 9999999, 411511999999

31、9Order 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, (select count(*) from tb1 where c1 mi) t2Group BySELECT sum(price) FROM tb1 GROUP BY c1IDPRICEC1112

32、.3222231.613158.860476.3131IDPRICEC124.460447.6131699.956Group BySELECT sum(price) FROM tb1 GROUP BY c1IDPRICEC1112.3222231.613158.860476.3131IDPRICEC124.460447.6131699.956SELECT sum(price), c1 FROM tb1 GROUP BY c1 ORDER BY c112.322227.91318.860499.9567.61314.4604Group BySELECT sum(price) FROM tb1 G

33、ROUP BY c1IDPRICEC1112.3222231.613158.860476.3131IDPRICEC124.460447.6131699.956SELECT sum(price), c1 FROM tb1 GROUP BY c1 ORDER BY c112.322227.91318.86047.61314.4604最终结果集99.956Group BySELECT sum(price) FROM tb1 GROUP BY c1IDPRICEC1112.3222231.613158.860476.3131IDPRICEC124.460447.6131699.956SELECT su

34、m(price), c1 FROM tb1 GROUP BY c1 ORDER BY c112.322227.91318.86047.61314.4604最终结果集99.956最终结果集Group BySELECT sum(price) FROM tb1 GROUP BY c1IDPRICEC1112.3222231.613158.860476.3131IDPRICEC124.460447.6131699.956SELECT sum(price), c1 FROM tb1 GROUP BY c1 ORDER BY c112.3222215.51318.86044.460499.956最终结果集

35、Group BySELECT sum(price) FROM tb1 GROUP BY c1IDPRICEC1112.3222231.613158.860476.3131IDPRICEC124.460447.6131699.956SELECT sum(price), c1 FROM tb1 GROUP BY c1 ORDER BY c112.3222215.513113.260499.956最终结果集Group BySELECT sum(price) FROM tb1 GROUP BY c1IDPRICEC1112.3222231.613158.860476.3131IDPRICEC124.4

36、60447.6131699.956SELECT sum(price), c1 FROM tb1 GROUP BY c1 ORDER BY c112.3222215.513113.260499.956SQL执行策略总结 WHERE - 基于SQL转发 JOIN - 迭代 分布式索引 ORDER BY/LIMIT - 多次查询减小数据量 GROUP BY - 增加ORDER BYMyCat 事务支持前端连接sql1sql2commitMyCat 事务支持前端连接分库1连接sql1sql2commit MyCat 事务支持前端连接分库1连接sql1commit分库2连接sql2分库3连接sql2My

37、Cat 事务支持前端连接分库1连接sql1commit分库2连接sql2分库3连接sql2 Commit有先后：隔离性问题 Commit有失败：一致性问题大纲n 中间件引入n MyCat策略n 水平拆分的数据分布n 几种SQL元素的执行策略n 事务策略n 系统实现n 实施应用schematableSpacedataNodedatasourcecndbpc2主备主备defaultoffer0主备offer1主备detail0主备defaultdefaultofferdetaildetail逻辑层次 接口同MySQLjdbc:mysql:/cobarIp:8066/cndb?user=foo&am

38、p;password=bar MySQLMySQLMySQLMycat结构Front-end CommunicationMySQL Protcol Adaptor (BIO)Application1MySQLMySQL ProtocolMySQLMySQLData NodesMonitorConfigureHA PoolMySQLMySQLProcessor(1)Processor(n)SQL ExecutorSQL RouterSQL ParserResultMergerSQL ExecutorSQL RouterSQL ParserResultMerger. ManagerMySQL Pr

39、otocolMySQL Protocol MySQL Protocol MySQL ProtocolManagementProtocolMySQLMySQLMySQLCobar结构Front-end CommunicationMySQL Protcol Adaptor (BIO)Application1MySQLMySQL ProtocolMySQLMySQLData NodesMonitorConfigureHA PoolMySQLMySQLProcessor(1)Processor(n)SQL ExecutorSQL RouterSQL ParserResultMergerSQL Exec

40、utorSQL RouterSQL ParserResultMerger. ManagerMySQL ProtocolMySQL Protocol MySQL Protocol MySQL ProtocolManagementProtocolApplication1JDBC和Server的通信协议MySQLJDBCDriverMySQLServerMySQL ProtocolJDBC和Server的通信协议PreparedStatement ps = conn.prepareStatement( select * from tb1 where id=?); ps.setLong(1, 1234

41、5);ResultSet rs = ps.executeQuery();MySQLServerApplication1MySQLJDBCDriverJDBC和Server的通信协议MySQLServerApplication1MySQLJDBCDriverPreparedStatement ps = conn.prepareStatement( select * from tb1 where id=?); ps.setLong(1, 12345);ResultSet rs = ps.executeQuery();select * from tb1where id=?stmt_id/param_

42、num/columm_numparameter_typecolumn_typeJDBC和Server的通信协议PreparedStatement ps = conn.prepareStatement( select * from tb1 where id=?); ps.setLong(1, 12345);ResultSet rs = ps.executeQuery();MySQLServerApplication1MySQLJDBCDriverJDBC和Server的通信协议PreparedStatement ps = conn.prepareStatement( select * from

43、tb1 where id=?); ps.setLong(1, 12345);ResultSet rs = ps.executeQuery();MySQLServerApplication1MySQLJDBCDriverstmt_id param+field_countcolumn_typecolumn_val+column_val+JDBC和Server的通信协议PreparedStatement ps = conn.prepareStatement( select * from tb1 where id=?); ps.setLong(1, 12345);ResultSet rs = ps.e

44、xecuteQuery();Application1MySQLJDBCDriverstmt_id param+field_countcolumn_typecolumn_val+column_val+MySQLServerJDBC和Server的通信协议Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery( “select * from tb1 where id=12345”);Application1MySQLJDBCDriversqlfield_countcolumn_typecolumn_val+c

45、olumn_val+MySQLServerJDBC和Server的通信协议Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery( “select * from tb1 where id=12345”);Application1MySQLJDBCDriversqlfield_countcolumn_typecolumn_val+column_val+MySQLServerCobarServerProcessor(1)Processor(n)MySQLMySQLMySQLMycat结构Front-end C

46、ommunicationMySQL Protcol Adaptor (BIO)Application1MySQLMySQL ProtocolMySQLMySQLData NodesMonitorConfigureHA PoolMySQLMySQLSQL ExecutorSQL RouterSQL ParserResultMergerSQL ExecutorSQL RouterSQL ParserResultMerger. ManagerMySQL ProtocolMySQL Protocol MySQL Protocol MySQL ProtocolManagementProtocolSQL

47、+ParametersResultSetMetaDataResultSet(Rows). MySQLMySQLMySQLMycat结构Front-end CommunicationMySQL Protcol Adaptor (BIO)Application1MySQLMySQL ProtocolMySQLMySQLData NodesMonitorConfigureHA PoolMySQLMySQLProcessor(1)Processor(n)SQL ExecutorSQL RouterResultMergerSQL ExecutorSQL RouterResultMergerManager

48、MySQL ProtocolMySQL Protocol MySQL Protocol MySQL ProtocolManagementProtocolSQL ParserSQL ParserSELECT id, member_id FROM wp_image WHERE member_id = 123SQL Parserselectidmember_idwp_image=member_id123exprListfromProcessor(1)Processor(n)MySQLMySQLMySQLMycat结构Front-end CommunicationApplication1MySQLMySQL Prot