mycat技術(shù)分享

1、MyCat技術(shù)分享,——原理、實(shí)現(xiàn)和應(yīng)用,2014.10.31,肖雄,1.Mycat解決什么?2.Mycat架構(gòu)3.Mycat應(yīng)用,Mycat解決什么？,Oracle,MySQL,MSSQL,DB2,,,,,,,,,性能 容量 高可用,...,如果我有一個(gè)32核心的服務(wù)器，我就可以實(shí)現(xiàn)1個(gè)億的數(shù)據(jù)分片，我有32核心的服務(wù)器么？沒(méi)有，所以我至今無(wú)法實(shí)現(xiàn)1個(gè)億的數(shù)據(jù)分片。——MyCAT ‘s Plan,應(yīng)用,應(yīng)用,應(yīng)用,應(yīng)用,MyC

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

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

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

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

6、連接過(guò)多E-R分片難處理可用性問(wèn)題Standby切換故障成本和伸縮性問(wèn)題依賴高成本的硬件設(shè)備,MyCat引入,,MySQL,應(yīng)用,Member表,Order表,Product表,1…n,1..n,,MySQL,Member表,Order表,Product表,1…n,1..n,……,MyCat,性能問(wèn)題數(shù)據(jù)庫(kù)連接過(guò)多E-R分片難處理可用性問(wèn)題Standby切換故障成本和伸縮性問(wèn)題依賴高成本的硬件設(shè)備,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)實(shí)現(xiàn)實(shí)施應(yīng)用,mysql,MySQL,MySQL,M

8、ySQL,MySQL,MySQL,MySQL,MySQL,MySQL,MySQL,,MySQL,MySQL,MySQL,拆分?jǐn)?shù)據(jù)表,水平拆分,水平拆分,拆分字段,,水平拆分,,,,,,,,,,f(pavarotti17)=庫(kù)1,f(test1234)=庫(kù)1,f(test1234)=庫(kù)1,f(pavarotti17)=庫(kù)1,f(abcd)=庫(kù)2,f(abcd)=庫(kù)2,f(abcd)=庫(kù)2,庫(kù)1,庫(kù)2,拆分字段,,水平拆分,,,f(abcd

9、)=庫(kù)2,庫(kù)1,庫(kù)2,拆分字段,,,,,,,,,f(pavarotti17)=庫(kù)1,f(test1234)=庫(kù)1,f(test1234)=庫(kù)1,f(pavarotti17)=庫(kù)1,f(abcd)=庫(kù)2,f(abcd)=庫(kù)2,路由算法,,路由算法,pavarotti17,f(pavarotti17)=庫(kù)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,,,,分庫(kù)1,,分庫(kù)2,,分庫(kù)3,,分庫(kù)4,,路由算法,hash(pavarott) = 3170972965401 % 1024

12、= 537,,0 255,256 511,512 767,768 1023,256,,,256,,,256,,,256,,,,,,分庫(kù)1,,分庫(kù)2,,分庫(kù)3,分庫(kù)4,,,,,,路由算法——擴(kuò)容,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,,,,分庫(kù)1,,分庫(kù)2,,分庫(kù)3,,分庫(kù)4,,,,,,,路由算法——擴(kuò)容,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,,,,,,,,,分庫(kù)1,,分庫(kù)2,,分庫(kù)3,,分庫(kù)4,,分庫(kù)5,,分庫(kù)6,,分庫(kù)7,分庫(kù)8,原,分庫(kù)1,分庫(kù)2,

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

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

17、IN (‘test1234’,’pavarotti17’,’abcd’),拆分表的數(shù)據(jù)訪問(wèn)——SQL轉(zhuǎn)發(fā),Mycat,App,select * from tb1 where member_id in (‘test1234’,’pavarotti17’),select * from tb1 where member_id in (‘a(chǎn)bcd’),拆分表的數(shù)據(jù)訪問(wèn)——SQL轉(zhuǎn)發(fā),ResultMerger,前臺(tái)通信,Resu

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

19、合并、處理 保證返回前端的內(nèi)容滿足語(yǔ)義,,,JOIN有限的處理,跨庫(kù)JOIN問(wèn)題,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},,,跨庫(kù)索引,tb1,tb1,tb2,tb2,idx,idx,掃描idx，再根據(jù)每一行的id1,id2查到最終結(jié)果,,,跨庫(kù)索引,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,再根據(jù)id1,id2查到最終結(jié)果,,,,跨庫(kù)索引,tb1,tb1,tb2,tb2,idx,idx,一定以 JOIN_COL 為索引的拆分字段嗎？,跨庫(kù)索引,SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME WHERE

22、 t1.gmt>600,,,tb1,tb1,tb2,tb2,,,跨庫(kù)索引,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,跨庫(kù)索引,跨庫(kù)索引,,,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,,,跨庫(kù)索引,idx,idx,索引表的拆分WHERE條件中的字段所在表的拆分字段，作為索引拆分字段索引包含兩張表的主鍵JOIN字段WHERE中的其他字段索引的更新分布式事務(wù)的支持,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,分庫(kù)1,分庫(kù)2,分庫(kù)3,cobar,Order By/Limit,分庫(kù)1,分庫(kù)2,分庫(kù)3,Order By/Limit,4,5,6,3,2,7,8,10,6,5,1,11,13,14,9,7,3,返回結(jié)果,返回結(jié)果,返回結(jié)果,8,SELECT c1 FROM tb1 ORDER B

26、Y c1 LIMIT 4, 2,分庫(kù)1,分庫(kù)2,分庫(kù)3,Order By/Limit,4,5,6,3,2,7,8,10,6,5,1,11,13,14,9,7,3,返回結(jié)果,返回結(jié)果,返回結(jié)果,8,0,最終結(jié)果集,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,分庫(kù)1,分庫(kù)2,分庫(kù)3,Order By/Limit,4,5,6,3,2,7,8,10,6,5,11,13,14,9,7,3,返回結(jié)果,返回結(jié)果

27、,返回結(jié)果,8,1,最終結(jié)果集,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,分庫(kù)1,分庫(kù)2,分庫(kù)3,Order By/Limit,4,5,6,3,7,8,10,6,5,11,13,14,9,7,3,返回結(jié)果,返回結(jié)果,返回結(jié)果,8,2,最終結(jié)果集,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,分庫(kù)1,分庫(kù)2,分庫(kù)3,Order By/Limit,4,5,6,7

28、,8,10,6,5,11,13,14,9,7,3,返回結(jié)果,返回結(jié)果,返回結(jié)果,8,3,最終結(jié)果集,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,分庫(kù)1,分庫(kù)2,分庫(kù)3,Order By/Limit,4,5,6,7,8,10,6,5,11,13,14,9,7,返回結(jié)果,返回結(jié)果,返回結(jié)果,8,4,最終結(jié)果集,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,最終結(jié)果

29、集,分庫(kù)1,分庫(kù)2,分庫(kù)3,Order By/Limit,5,6,7,8,10,6,5,11,13,14,9,7,返回結(jié)果,返回結(jié)果,返回結(jié)果,8,4,4,SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2,最終結(jié)果集,分庫(kù)1,分庫(kù)2,分庫(kù)3,Order By/Limit,4,6,7,8,10,6,5,11,13,14,9,7,返回結(jié)果,返回結(jié)果,返回結(jié)果,8,4,5,SELECT c1 FROM tb1

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

31、量大問(wèn)題遍歷量大問(wèn)題前提各個(gè)分庫(kù)數(shù)據(jù)分布大致一樣,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,分庫(kù)1,分庫(kù)2,分庫(kù)3,step1:分成3條語(yǔ)句發(fā)給分庫(kù),分庫(kù)1,分庫(kù)2,分庫(kù)3,7,4,5,3,8,6,9,10,7,6,11,9,返回結(jié)果,返回結(jié)果,返回結(jié)果,select c1 from tb1 order by c1 limit 9999999, 4,找出查詢結(jié)果中最小和最大值,分庫(kù)1,分庫(kù)2,分庫(kù)3,7,4,5,3,8,6,9,10,7,6,11,9,返回結(jié)果,返回

33、結(jié)果,返回結(jié)果,step2:以最小值和最大值為界再查詢,select c1 from tb1 order by c1 limit 9999999, 4,3,11,5,3,11,分庫(kù)1,分庫(kù)2,分庫(kù)3,7,4,5,3,8,6,9,10,7,6,11,9,返回結(jié)果,返回結(jié)果,返回結(jié)果,step3:反查出每一個(gè)返回結(jié)果的offset,select c1 from tb1 order by c

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

35、4,3,11,5,3,11,9999996,分庫(kù)1,分庫(kù)2,分庫(kù)3,7,4,5,3,8,6,9,10,7,6,11,9,返回結(jié)果,返回結(jié)果,返回結(jié)果,類似于原始方案,select c1 from tb1 order by c1 limit 9999999, 4,11,5,3,11,9999997,分庫(kù)1,分庫(kù)2,分庫(kù)3,7,4,5,8,6,9,10,7,6,11,9,返回結(jié)果,返回結(jié)果

36、,返回結(jié)果,類似于原始方案,select c1 from tb1 order by c1 limit 9999999, 4,11,5,3,11,9999998,分庫(kù)1,分庫(kù)2,分庫(kù)3,7,4,5,8,6,9,10,7,6,11,9,返回結(jié)果,返回結(jié)果,返回結(jié)果,類似于原始方案,select c1 from tb1 order by c1

37、 limit 9999999, 4,11,5,11,9999999,最終結(jié)果集,最終結(jié)果集,分庫(kù)1,分庫(kù)2,分庫(kù)3,7,4,5,8,6,9,10,7,6,11,9,返回結(jié)果,返回結(jié)果,返回結(jié)果,類似于原始方案,select c1 from tb1 order by c1 limit 9999999, 4,11,5,11,9999999,最終結(jié)果集,分庫(kù)1,分庫(kù)2,分庫(kù)3,7,4,5,

38、8,6,9,10,7,6,11,9,返回結(jié)果,返回結(jié)果,返回結(jié)果,類似于原始方案,select c1 from tb1 order by c1 limit 9999999, 4,11,5,11,9999999,最終結(jié)果集,分庫(kù)1,分庫(kù)2,分庫(kù)3,7,4,5,8,6,9,10,7,6,11,9,返回結(jié)果,返回結(jié)果,返回結(jié)果,類似于原始方案,select c1 from tb1 order

39、 by c1 limit 9999999, 4,11,5,11,9999999,最終結(jié)果集,分庫(kù)1,分庫(kù)2,分庫(kù)3,7,4,5,8,6,9,10,7,6,11,9,返回結(jié)果,返回結(jié)果,返回結(jié)果,類似于原始方案,select c1 from tb1 order by c1 limit 9999999, 4,11,5,11,9999999

40、,Order By / Limit 再優(yōu)化,Step1不必得到全部結(jié)果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,最終結(jié)果集,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,最終結(jié)果集,99.956,,,最終結(jié)果集,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,,,最終結(jié)果集,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,,,最終結(jié)果集,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í)行策略總結(jié),WHERE - 基于SQL轉(zhuǎn)發(fā)JOIN - 迭代 分布式索引ORDER BY/LIMIT

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

47、連接,sql2,Commit有先后：隔離性問(wèn)題Commit有失?。阂恢滦詥?wèn)題,大綱,中間件引入MyCat策略水平拆分的數(shù)據(jù)分布幾種SQL元素的執(zhí)行策略事務(wù)策略系統(tǒng)實(shí)現(xiàn)實(shí)施應(yīng)用,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結(jié)構(gòu),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結(jié)構(gòu),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結(jié)構(gòu),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結(jié)構(gòu),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結(jié)構(gòu),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,后臺(tái)數(shù)據(jù)訪問(wèn)邏輯層次,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ù)據(jù)庫(kù)連接基于協(xié)議數(shù)據(jù)包與MySQL交互,基于MySQL協(xié)議,MySQL,新Cobar,,select * from offer,,select * from offer,基于MySQL協(xié)議,MySQL,新Cobar,,select * from offer,,,select * from offer,后臺(tái)數(shù)據(jù)訪問(wèn)邏輯層次,S,M,HA Pool,My