mycat技術分享

1、MyCat技術分享,——原理、實現(xiàn)和應用,2014.10.31,肖雄,1.Mycat解決什么?2.Mycat架構3.Mycat應用,Mycat解決什么？,Oracle,MySQL,MSSQL,DB2,,,,,,,,,性能 容量 高可用,...,如果我有一個32核心的服務器，我就可以實現(xiàn)1個億的數(shù)據分片，我有32核心的服務器么？沒有，所以我至今無法實現(xiàn)1個億的數(shù)據分片?！狹yCAT ‘s Plan,應用,應用,應用,應用,MyC

2、at,為什么選擇MyCat?,1.支持讀寫分離，支持Mysql雙主多從，以及一主多從的模式 2.支持全局表，數(shù)據自動分片到多個節(jié)點，用于高效表關聯(lián)查詢 3.支持獨有的基于E-R 關系的分片策略，實現(xiàn)了高效的表關聯(lián)查詢 4.自動故障切換，高可用性 5.提供高可用性數(shù)據分片集群 6.支持JDBC連接ORACLE、DB2、SQL Server，將其模擬為

3、 MySQL Server使用 7.支持Mysql集群，可以作為Proxy使用 8.基于阿里開源的Cobar產品而研發(fā)，Cobar的穩(wěn)定性、可靠性、 優(yōu)秀的架構和性能,MyCat架構,,MySQL CLI/JDBC/ODBC/…,,數(shù)據庫驅動,Mycat結構,數(shù)據庫實例,數(shù)據庫A,數(shù)據庫B,數(shù)據庫C,More…,MySQL Socket Protocol Handler,SQL

4、Parser,SQL Router,SQL Executor,DataNodes,HeartBeat Checker,,,,,中間件引入MyCat策略系統(tǒng)實現(xiàn)實施應用,MyCat應用,性能問題數(shù)據庫連接過多E-R分片難處理可用性問題Standby切換故障成本和伸縮性問題依賴高成本的硬件設備,,Mycat引入 水平拆分,,MySQL,,MySQL,MySQL,MyCat,應用,Member表,Member表,Mem

5、ber表,性能問題數(shù)據庫連接過多E-R分片難處理可用性問題Standby切換故障成本和伸縮性問題依賴高成本的硬件設備,,,應用,MySQL,,MySQL,MySQL,應用,應用,應用,應用,應用,應用,應用,Member表,Member表,Member表,,Mycat引入 連接復用,,App,MySQL,,MySQL,MySQL,MycatProxy,App,App,App,App,App,App,App,性能問題數(shù)據庫

6、連接過多E-R分片難處理可用性問題Standby切換故障成本和伸縮性問題依賴高成本的硬件設備,MyCat引入,,MySQL,應用,Member表,Order表,Product表,1…n,1..n,,MySQL,Member表,Order表,Product表,1…n,1..n,……,MyCat,性能問題數(shù)據庫連接過多E-R分片難處理可用性問題Standby切換故障成本和伸縮性問題依賴高成本的硬件設備,MyCat引入,

7、,MySQL,Mycat,App,X,MyCat引入 failover,,MySQL Master1,MyCat,App,,MySQL Master2,X,MySQLReplication,MyCat引入 failover,,Mycat,App,,MySQLReplication,MySQL Master1,MySQL Master2,大綱,中間件引入MyCat策略系統(tǒng)實現(xiàn)實施應用,mysql,MySQL,MySQL,M

8、ySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,,MySQL,MySQL,MySQL,拆分數(shù)據表,水平拆分,水平拆分,拆分字段,,水平拆分,,,,,,,,,,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

9、)=庫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(pav

10、arott) = 3170972965401 % 1024 = 537,,0 1023,,路由算法,hash(pavarott) = 3170972965401 % 1024 =

11、 537,,0 255,,,,,,256 511,512 767,768 1023,256,,,256,,,256,,,256,,,,分庫1,,分庫2,,分庫3,,分庫4,,路由算法,hash(pavarott) = 3170972965401 % 1024

12、= 537,,0 255,256 511,512 767,768 1023,256,,,256,,,256,,,256,,,,,,分庫1,,分庫2,,分庫3,分庫4,,,,,,路由算法——擴容,hash(pavarott) = 3170972965401 %

13、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) = 317097296

14、5401 % 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,,分庫5,,分庫6,,分庫7,分庫8,原,分庫1,分庫2,

15、分庫3,分庫4,原,原,原,,,路由算法——非均勻分布,hash(pavarott) = 3170972965401 % 1024 = 537,,0 511,512 767,768 895 896 1023,,512,,,256,,,128,,,128,,,,,

16、,,,分庫1,,分庫2,,分庫3,,分庫4,拆分表的數(shù)據訪問——SQL轉發(fā),Mycat,App,select * from tb1 where member_id=‘test1234’,拆分表的數(shù)據訪問——SQL轉發(fā),Mycat,App,select * from tb1 where member_id=‘test1234’,Mycat,App,SELECT * FROM tb1 WHERE member_id

17、IN (‘test1234’,’pavarotti17’,’abcd’),拆分表的數(shù)據訪問——SQL轉發(fā),Mycat,App,select * from tb1 where member_id in (‘test1234’,’pavarotti17’),select * from tb1 where member_id in (‘abcd’),拆分表的數(shù)據訪問——SQL轉發(fā),ResultMerger,前臺通信,Resu

18、ltSet:row1row2,ResultSet:row3row4row5,,ResultSet:row3row1row4row5row2,拆分表的數(shù)據訪問——結果返回,MyCat的策略,基于表的水平拆分和分布根據字段值的一致性Hash分布根據字段值的范圍分布根據字段值的固定分布數(shù)據查詢方式根據where中的拆分字段分發(fā)SQL語句其他元素的處理將Cobar收到的SQL語句做變換 分發(fā)到各個分庫執(zhí)行對執(zhí)行結果

19、合并、處理 保證返回前端的內容滿足語義,,,JOIN有限的處理,跨庫JOIN問題,SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME,tb1,tb1,tb2,tb2,,,迭代查詢,SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME,tb1,tb1,tb2,tb2,FOR row1 IN

20、select * FROM tb1{ADD(SELECT * FROM tb2 WHERE tb2.name = 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.MEMB

21、ER_ID=t2.NAME WHERE t1.id = 5,SELECT * FROM idx 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

22、 t1.gmt>600,,,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 WHERE t1.gmt>600,SELECT idx.id2, tb1.* FROM idx INNER

23、 JOIN tb1 ON idx.id1=tb1.id WHERE t1.gmt>600,SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME WHERE t1.gmt>600,跨庫索引,跨庫索引,,,tb1,tb1,tb2,tb2,SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_I

24、D=t2.NAME WHERE t1.gmt>600 AND t2.time>600,idx,,,跨庫索引,idx,idx,索引表的拆分WHERE條件中的字段所在表的拆分字段，作為索引拆分字段索引包含兩張表的主鍵JOIN字段WHERE中的其他字段索引的更新分布式事務的支持,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,select ... order by c1

25、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,11,13,14,9,7,3,返回結果,返回結果,返回結果,8,SELECT c1 FROM tb1 ORDER B

26、Y 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,返回結果,返回結果

27、,返回結果,8,1,最終結果集,SELECT 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

28、,8,10,6,5,11,13,14,9,7,3,返回結果,返回結果,返回結果,8,3,最終結果集,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,返回結果,返回結果,返回結果,8,4,最終結果集,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,最終結果

29、集,分庫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,返回結果,返回結果,返回結果,8,4,5,SELECT c1 FROM tb1

30、 ORDER BY c1 LIMIT 4, 2,,Order By/Limit 方案總結,select c1 from tb1 order by c1 limit 100000000, 2,所有分庫都要查詢100000002條數(shù)據Cobar需要遍歷100000002條數(shù)據,對如下SQL,一次交互得到結果Offset大小有限制,Order By / Limit 優(yōu)化,目標：解決查詢

31、量大問題遍歷量大問題前提各個分庫數(shù)據分布大致一樣,select c1 from tb1 order by c1 limit 9999999, 4,select ... order by c1 limit 33333333, 4,select ... order by c1 limit 33333333, 4,select ... order by c1 limit 3333333

32、3, 4,分庫1,分庫2,分庫3,step1:分成3條語句發(fā)給分庫,分庫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,11,9,返回結果,返回

33、結果,返回結果,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 c

34、1 limit 9999999, 4,3,11,5,3,11,33333331條,33333333條,33333332條,分庫1,分庫2,分庫3,7,4,5,3,8,6,9,10,7,6,11,9,返回結果,返回結果,返回結果,類似于原始方案,select c1 from tb1 order by c1 limit 9999999,

35、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,返回結果,返回結果

36、,返回結果,類似于原始方案,select c1 from tb1 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

37、 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,

38、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

39、 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

40、,Order By / Limit 再優(yōu)化,Step1不必得到全部結果select min(c1) mi, max(c1) ma from (select c1 from tb1 order by c1 limit 3333333,4) tStep2和Step3合并select * from (select * from tb1 where c1 between mi and ma) t1, (select count(*)

41、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.9

42、56,7.6131,4.4604,,,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) FR

43、OM 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 sum(price), c1 FROM

44、 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

45、.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,SQL執(zhí)行策略總結,WHERE - 基于SQL轉發(fā)JOIN - 迭代 分布式索引ORDER BY/LIMIT

46、 - 多次查詢減小數(shù)據量GROUP BY - 增加ORDER BY,MyCat 事務支持,前端連接,sql1,sql2,commit,,MyCat 事務支持,前端連接,分庫1連接,sql1,sql2,commit,,MyCat 事務支持,前端連接,分庫1連接,sql1,commit,,分庫2連接,sql2,分庫3連接,sql2,MyCat 事務支持,前端連接,分庫1連接,sql1,commit,,分庫2連接,sql2,分庫3

47、連接,sql2,Commit有先后：隔離性問題Commit有失?。阂恢滦詥栴},大綱,中間件引入MyCat策略水平拆分的數(shù)據分布幾種SQL元素的執(zhí)行策略事務策略系統(tǒng)實現(xiàn)實施應用,schema,tableSpace,dataNode,datasource,cndb,pc2,主,備,主,備,default,offer[0],主,備,offer[1],主,備,detail[0],主,備,default,default,of

48、fer,detail,detail,邏輯層次 —— 接口同MySQL,jdbc:mysql://cobarIp:8066/cndb?user=foo&password=bar,MySQL,MySQL,MySQL,,Mycat結構,Front-end Communication,MySQL Protcol Adaptor (BIO),Application1,MySQL,,MySQL,,MySQL,,Data Nodes,Moni

49、torConfigure,HA Pool,MySQL,MySQL,,Processor(1),Processor(n),SQL Executor,SQL Router,SQL Parser,ResultMerger,,,,,,SQL Executor,SQL Router,SQL Parser,ResultMerger,,,,,,...,Manager,,MySQL Protocol,MySQL Protocol,MySQL P

50、rotocol,MySQL Protocol,ManagementProtocol,MySQL,MySQL,MySQL,,Cobar結構,Front-end Communication,MySQL Protcol Adaptor (BIO),Application1,MySQL,,MySQL,,MySQL,,Data Nodes,MonitorConfigure,HA Pool,MySQL,MySQL,,Processor(1),

51、Processor(n),SQL Executor,SQL Router,SQL Parser,ResultMerger,,,,,,SQL Executor,SQL Router,SQL Parser,ResultMerger,,,,,,...,Manager,,MySQL Protocol,MySQL Protocol,MySQL Protocol,MySQL Protocol,ManagementProtocol,Applic

52、ation1,JDBC和Server的通信協(xié)議,MySQLJDBCDriver,MySQLServer,,MySQL Protocol,JDBC和Server的通信協(xié)議,PreparedStatement ps = conn.prepareStatement( "select * from tb1 where id=?"); ps.setLong(1, 1

53、2345);ResultSet rs = ps.executeQuery();,MySQLServer,,Application1,MySQLJDBCDriver,JDBC和Server的通信協(xié)議,MySQLServer,Application1,MySQLJDBCDriver,PreparedStatement ps = conn.prepareStatement( "

54、select * from tb1 where id=?"); ps.setLong(1, 12345);ResultSet rs = ps.executeQuery();,,,select * from tb1where id=?,stmt_id/param_num/columm_num,,parameter_type,,,,column_type,,,JDBC和Server的通信協(xié)議,Prepar

55、edStatement ps = conn.prepareStatement( "select * from tb1 where id=?"); ps.setLong(1, 12345);ResultSet rs = ps.executeQuery();,MySQLServer,,Application1,MySQLJDBCDriver,JDBC和Ser

56、ver的通信協(xié)議,PreparedStatement ps = conn.prepareStatement( "select * from tb1 where id=?"); ps.setLong(1, 12345);ResultSet rs = ps.executeQuery();,MySQLServer,,Application1,MySQLJDBC

57、Driver,,stmt_id {param}+,,field_count,,column_type,,,,{column_val}+,,{column_val}+,,,JDBC和Server的通信協(xié)議,PreparedStatement ps = conn.prepareStatement( "select * from tb1 where id=?");

58、 ps.setLong(1, 12345);ResultSet rs = ps.executeQuery();,,Application1,MySQLJDBCDriver,,stmt_id {param}+,,field_count,,column_type,,,,{column_val}+,,{column_val}+,,,MySQLServer,JDBC和Server的通信協(xié)議,Statement stmt

59、= conn.createStatement();ResultSet rs = stmt.executeQuery( “select * from tb1 where id=12345”);,,Application1,MySQLJDBCDriver,,sql,,field_count,,column_type,,,,{column_val}+,,{column_val}+,,,MySQ

60、LServer,JDBC和Server的通信協(xié)議,Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery( “select * from tb1 where id=12345”);,,Application1,MySQLJDBCDriver,,sql,,field_count,,column_type

61、,,,,{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

62、Configure,HA Pool,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,Manage

63、mentProtocol,SQL +Parameters,ResultSetMetaDataResultSet(Rows),...,MySQL,MySQL,MySQL,Mycat結構,Front-end Communication,MySQL Protcol Adaptor (BIO),Application1,MySQL,,,MySQL Protocol,MySQL,,MySQL,,Data Nodes,MonitorConf

64、igure,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,ManagementP

65、rotocol,SQL Parser,SQL Parser,SELECT id, member_id FROM wp_image WHERE member_id = ‘123’,SQL Parser,select,id,member_id,wp_image,=,member_id,‘123’,exprList,from,,,Processor(1),Processor(n),MySQL,MySQL,MySQL,,Mycat結構,Fron

66、t-end Communication,Application1,MySQL,,MySQL,,MySQL,,MonitorConfigure,MySQL,MySQL,,SQL Executor,SQL Router,SQL Parser,ResultMerger,,,,,,SQL Executor,SQL Router,SQL Parser,ResultMerger,,,,,,...,Manager,,MySQL Protoco

67、l,MySQL Protocol,MySQL Protocol,MySQL Protocol,ManagementProtocol,MySQL Protcol Adaptor (BIO),Data Nodes,HA Pool,后臺數(shù)據訪問邏輯層次,HA Pool,MySQL Protocol Adapter,Data Node,S,M,,S,M,,S,M,,S,M,,S,M,,ip:port/offer,ip:port/offer,i

68、p:port/ibank,ip:port/ibank,ip:port/ibank,數(shù)據庫連接基于協(xié)議數(shù)據包與MySQL交互,基于MySQL協(xié)議,MySQL,新Cobar,,select * from offer,,select * from offer,基于MySQL協(xié)議,MySQL,新Cobar,,select * from offer,,,select * from offer,后臺數(shù)據訪問邏輯層次,S,M,HA Pool,My