數(shù)據(jù)庫分庫分表中間件架構(gòu)解析

1、阿里巴巴分布式數(shù)據(jù)庫原理、實現(xiàn)和應(yīng)用2012.7集團共享技術(shù)平臺分布式數(shù)據(jù)庫邱碩分布式數(shù)據(jù)庫中間件AppAppAppAppCobarOracleMySQL MySQL MySQLErosaOracleErosaMySQLErosaMySQLErosaMySQLEromangaOtterOracleMySQL MySQLOtterErosaOracleErosaMySQLErosaMySQLEromanga 性能 容量 高可用 數(shù)據(jù)消費時效性 跨機房數(shù)據(jù)同步ASCDW.分布式數(shù)據(jù)庫中間件AppAppAppAppCobarOracleMySQL MySQL MySQLErosaOracleEros

2、aMySQLErosaMySQLErosaMySQLEromangaOracleMySQL MySQLOtter 性能 容量 高可用 數(shù)據(jù)消費時效性 跨機房數(shù)據(jù)同步ErosaOracleErosaMySQLErosaMySQLEromangaOtterASCDW.分布式數(shù)據(jù)庫中間件AppAppAppAppCobarOracleMySQL MySQL MySQLOracleMySQL MySQLOtterErosaOracleErosaMySQLErosaMySQLEromanga 性能 容量 高可用 數(shù)據(jù)消費時效性 跨機房數(shù)據(jù)同步ErosaOracleErosaMySQLErosaMySQLE

3、rosaMySQLEromangaOtterASCDW.分布式數(shù)據(jù)庫中間件AppAppAppAppCobarOracleMySQL MySQL MySQLErosaOracleErosaMySQLErosaMySQLErosaMySQLEromangaOracleMySQL MySQLErosaOracleErosaMySQLErosaMySQLEromanga 性能 容量 高可用 數(shù)據(jù)消費時效性 跨機房數(shù)據(jù)同步OtterASCDW.Otter分布式數(shù)據(jù)庫中間件AppAppAppAppCobarOracleMySQL MySQL MySQLErosaOracleErosaMySQLErosaM

4、ySQLErosaMySQLEromangaOracleMySQL MySQLOtter 性能 容量 高可用 數(shù)據(jù)消費時效性 跨機房數(shù)據(jù)同步ErosaOracleErosaMySQLErosaMySQLEromangaOtterASCDW.大綱n 中間件引入n Cobar策略n 系統(tǒng)實現(xiàn)n 實施應(yīng)用Cobar之前 Oracle單點數(shù)據(jù)庫 性能問題 中文站offer總數(shù)：2008年1億 - 2011年3億 高峰時：load 30、cpu使用率90% 數(shù)據(jù)庫連接過多 可用性問題 Standby切換故障 成本和伸縮性問題 依賴高成本的硬件設(shè)備OracleMySQLMySQLMySQLMySQLMyS

5、QLMySQLMySQLMySQLMySQL單點：MySQL集群替換OracleMySQLMySQLMySQL Oracle單點數(shù)據(jù)庫 性能問題 中文站offer總數(shù)：08年1億 - 今天3億 高峰時：load 30、cpu使用率90% 數(shù)據(jù)庫連接過多 可用性問題 Standby切換故障 成本和伸縮性問題 依賴高成本的硬件設(shè)備Cobar引入 水平拆分IDMEMBE_IDINFO1pavarotti1711pavarotti17MySQLIDMEMBE_IDINFO4test12345test1234IDMEMBE_IDINFO3abcd9abcd20abcdMySQLMySQLCobarApp

6、 Oracle單點數(shù)據(jù)庫 性能問題 中文站offer總數(shù)：08年1億 - 今天3億 高峰時：load 30、cpu使用率90% 數(shù)據(jù)庫連接過多 可用性問題 Standby切換故障 成本和伸縮性問題 依賴高成本的硬件設(shè)備AppIDMEMBE_IDINFO1pavarotti1711pavarotti17MySQLIDMEMBE_IDINFO4test12345test1234IDMEMBE_IDINFO3abcd9abcd20abcdMySQLMySQLAppAppAppAppAppAppAppCobar引入 連接復(fù)用AppIDMEMBE_IDINFO1pavarotti1711pavarott

7、i17MySQLIDMEMBE_IDINFO4test12345test1234IDMEMBE_IDINFO3abcd9abcd20abcdMySQLMySQLCobarProxyAppAppAppAppAppAppApp Oracle單點數(shù)據(jù)庫 性能問題 中文站offer總數(shù)：08年1億 - 今天3億 高峰時：load 30、cpu使用率90% 數(shù)據(jù)庫連接過多 可用性問題 Standby切換故障 成本和伸縮性問題 依賴高成本的硬件設(shè)備Cobar引入IDMEMBE_IDINFO1pavarotti1711pavarotti17MySQLCobarAppXCobar引入 failoverIDME

8、MBE_IDINFO1pavarotti1711pavarotti17MySQL Master1CobarAppIDMEMBE_IDINFO1pavarotti1711pavarotti17MySQL Master2XMySQLReplicationCobar引入 failoverIDMEMBE_IDINFO1pavarotti1711pavarotti17CobarAppIDMEMBE_IDINFO1pavarotti1711pavarotti17MySQLReplicationMySQL Master1MySQL Master2大綱n 中間件引入n Cobar策略n 系統(tǒng)實現(xiàn)n 實施應(yīng)用O

9、racleMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL拆分數(shù)據(jù)表水平拆分ID MEMBE_IDINFO1pavarotti173abcd4test12345test12349abcd11pavarotti1720abcd水平拆分IDMEMBE_IDINFO1pavarotti173abcd4test12345test12349abcd11pavarotti1720abcd拆分字段水平拆分ID MEMBE_ID INFO1pavarotti173abcd4test12345test12349abcd11pavarotti

10、1720abcdIDMEMBE_IDINFO1pavarotti174test12345test123411pavarotti17ID MEMBE_ID INFO3abcd9abcd20abcdf(pavarotti17)=庫1f(test1234)=庫1f(test1234)=庫1f(pavarotti17)=庫1f(abcd)=庫2f(abcd)=庫2f(abcd)=庫2庫1庫2拆分字段水平拆分ID MEMBE_ID INFO1pavarotti173abcd4test12345test12349abcd11pavarotti1720abcdIDMEMBE_IDINFO1pavarotti

11、174test12345test123411pavarotti17ID MEMBE_ID INFO3abcd9abcd20abcdf(abcd)=庫2庫1庫2拆分字段f(pavarotti17)=庫1f(test1234)=庫1f(test1234)=庫1f(pavarotti17)=庫1f(abcd)=庫2f(abcd)=庫2路由算法路由算法pavarotti17f(pavarotti17)=庫1路由算法pavarotti17部分截取hash( ) = 3170972965401路由算法hash(pavarott) = 3170972965401 % 1024 = 5370 1023路由算法

12、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(pavarott) = 3170972965401 % 1024 = 5370 127 128 255 256 383 384 511 512 639 640 767 768 895

13、 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分庫3分庫4原原原路由算法非均勻分布hash(pavarott) = 3170972965401 % 1024 = 5370 511 512 767 768 895 896 10

14、23512256128128分庫1分庫2分庫3分庫4拆分表的數(shù)據(jù)訪問SQL轉(zhuǎn)發(fā)IDMEMBE_IDINFO1pavarotti174test12345test123411pavarotti17IDMEMBE_IDINFO3abcd9abcd20abcdCobarAppselect * from tb1 where member_id=test1234拆分表的數(shù)據(jù)訪問SQL轉(zhuǎn)發(fā)IDMEMBE_IDINFO1pavarotti174test12345test123411pavarotti17IDMEMBE_IDINFO3abcd9abcd20abcdCobarAppselect * from tb

15、1 where member_id=test1234IDMEMBE_IDINFO1pavarotti174test12345test123411pavarotti17IDMEMBE_IDINFO3abcd9abcd20abcdCobarAppSELECT * FROM tb1 WHERE member_id IN (test1234,pavarotti17,abcd)拆分表的數(shù)據(jù)訪問SQL轉(zhuǎn)發(fā)IDMEMBE_IDINFO1pavarotti174test12345test123411pavarotti17IDMEMBE_IDINFO3abcd9abcd20abcdCobarAppselect

16、* from tb1 where member_id in (test1234,pavarotti17)select * from tb1 where member_id in (abcd)拆分表的數(shù)據(jù)訪問SQL轉(zhuǎn)發(fā)IDMEMBE_IDINFO1pavarotti174test12345test123411pavarotti17IDMEMBE_IDINFO3abcd9abcd20abcdResultMerger前臺通信ResultSet:row1row2ResultSet:row3row4row5ResultSet:row3row1row4row5row2拆分表的數(shù)據(jù)訪問結(jié)果返回多維水平拆分v

17、isit表productuserinfoCoca-ColaApepsiCFantaDCoca-ColaACoca-ColaCFantaB7UpDpepsiAproductuserinfoCoca-ColaACoca-ColaACoca-ColaCproductuserinfopepsiCpepsiAproductuserinfoFantaDFantaBproductuserinfo7UpDSELECT * FROM visit WHERE user=A多維水平拆分visit表productuserinfoCoca-ColaApepsiCFantaDCoca-ColaACoca-ColaCFan

18、taB7UpDpepsiAproductuserinfoCoca-ColaACoca-ColaApepsiAproductuserinfopepsiCCoca-ColaCproductuserinfoFantaD7UpDproductuserinfoFantaBSELECT * FROM visit WHERE product = Coca-Cola分庫1分庫2分庫3分庫4分庫5分庫6分庫7分庫8分庫9分庫10分庫11分庫12分庫13分庫14分庫15分庫16product值Hash取模user值Hash取模01230123visit表USERPRODUCT 一張表的多個字段同時作為拆分字段Ha

19、sh(“A”)%4 = 分庫1分庫2分庫3分庫4分庫5分庫6分庫7分庫8分庫9分庫10分庫11分庫12分庫13分庫14分庫15分庫16product值Hash取模user值Hash取模01230123CocaColaAHash(“CocaCola”)%4 = SELECT * FROM visit WHERE product=ColaCola AND user=A分庫1分庫2分庫3分庫4分庫5分庫6分庫7分庫8分庫9分庫10分庫11分庫12分庫13分庫14分庫15分庫16product值Hash取模user值Hash取模01230123CocaColaHash(“CocaCola”)%4 =

20、SELECT * FROM visit WHERE product=ColaColaHash(“A”)%4 = A分庫1分庫2分庫3分庫4分庫5分庫6分庫7分庫8分庫9分庫10分庫11分庫12分庫13分庫14分庫15分庫16product值Hash取模user值Hash取模01230123SELECT * FROM visit WHERE product=ColaCola AND user=ACobar的策略 MySQL集群替代Oracle單點 基于表的水平拆分和分布根據(jù)字段值的一致性Hash分布多維拆分 數(shù)據(jù)查詢方式根據(jù)where中的拆分字段分發(fā) SQL語句其他元素的處理將Cobar收到的S

21、QL語句做變換 分發(fā)到各個分庫執(zhí)行對執(zhí)行結(jié)果合并、處理 保證返回前端的內(nèi)容滿足語義JOIN有限的處理 跨庫JOIN問題SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAMEIDMEMBE_ID2zzzz4xyzxyzIDNAME4efghijk5aaaa6abcdIDMEMBE_ID1efghijk3xxxxxx5abcdIDNAME2zzzz3xyzxyztb1tb1tb2tb2IDMEMBER_IDIDNAME1efghijk4efghijk5abcd6abcd2zzzz2zzzz4xyzxyz3xyzxyz迭代查詢SELECT *

22、 FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAMEIDMEMBE_ID2zzzz4xyzxyzIDNAME4efghijk5aaaa6abcdIDMEMBE_ID1efghijk3xxxxxx5abcdIDNAME2zzzz3xyzxyztb1tb1tb2tb2FOR row1 IN select * FROM tb1ADD(SELECT * FROM tb2 WHERE = row1.member_id)TO RESULT跨庫索引IDMEMBE_ID2zzzz4xyzxyzIDNAME4efghijk5aaaa6abcdIDME

23、MBE_ID1efghijk3xxxxxx5abcdIDNAME2zzzz3xyzxyztb1tb1tb2tb2ID1ID2JOIN_COL22zzzz43xyzxyzidxID1ID2JOIN_COL14efghijk56abcdidx掃描idx，再根據(jù)每一行的id1,id2查到最終結(jié)果跨庫索引IDMEMBE_ID2zzzz4xyzxyzIDNAME4efghijk5aaaa6abcdIDMEMBE_ID1efghijk3xxxxxx5abcdIDNAME2zzzz3xyzxyztb1tb1tb2tb2SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER

24、_ID=t2.NAME WHERE t1.id = 5SELECT * FROM idx WHERE id1 = 5再根據(jù)id1,id2查到最終結(jié)果跨庫索引IDMEMBE_ID2zzzz4xyzxyzIDNAME4efghijk5aaaa6abcdIDMEMBE_ID1efghijk3xxxxxx5abcdIDNAME2zzzz3xyzxyztb1tb1tb2tb2ID1ID2JOIN_COL22zzzz43xyzxyzidxID1ID2JOIN_COL14efghijk56abcdidx一定以 JOIN_COL 為索引的拆分字段嗎？跨庫索引SELECT * FROM tb1 INNER JO

25、IN tb2 ON t1.MEMBER_ID=t2.NAME WHERE t1.gmt600IDMEMBE_IDGMT2zzzz5254xyzxyz1010IDNAME4efghijk5aaaa6abcdIDMEMBE_IDGMT1efghijk12053xxxxxx1315abcd604IDNAME2zzzz3xyzxyztb1tb1tb2tb2跨庫索引IDMEMBE_IDGMT2zzzz5254xyzxyz1010IDNAME4efghijk5aaaa6abcdIDMEMBE_IDGMT1efghijk12053xxxxxx1315abcd604IDNAME2zzzz3xyzxyztb1t

26、b1tb2tb2ID1ID2JOIN_COL22zzzz43xyzxyzidxID1ID2JOIN_COL14efghijk56abcdidxIDMEMBE_ID2zzzz4xyzxyzIDMEMBE_ID1efghijk3xxxxxx5abcdtb1tb1ID1ID2JOIN_COL22zzzz43xyzxyzidxID1ID2JOIN_COL14efghijk56abcdidxSELECT idx.id2, tb1.* FROM idx INNER JOIN tb1 ON idx.id1=tb1.id WHERE t1.gmt600SELECT idx.id2, tb1.* FROM id

27、x INNER JOIN tb1 ON idx.id1=tb1.id WHERE t1.gmt600SELECT * FROM tb1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME WHERE t1.gmt600跨庫索引跨庫索引IDMEMBE_IDGMT2zzzz5254xyzxyz1010IDNAMETIME4efghijk1235aaaa9226abcd222IDMEMBE_IDGMT1efghijk12053xxxxxx1315abcd604IDNAMETIME2zzzz12013xyzxyz1111tb1tb1tb2tb2SELECT * FROM tb

28、1 INNER JOIN tb2 ON t1.MEMBER_ID=t2.NAME WHERE t1.gmt600 AND t2.time600ID1ID2JOIN_COLTIME22zzzz120143xyzxyz1111idx跨庫索引ID1ID2JOIN_COLTIME22zzzz120143xyzxyz1111idxID1ID2JOIN_COLTIME14efghijk12356abcd222idx 索引表的拆分WHERE條件中的字段所在表的拆分字段，作為索引拆分字段 索引包含兩張表的主鍵JOIN字段WHERE中的其他字段 索引的更新分布式事務(wù)的支持SELECT c1 FROM tb1 O

29、RDER BY c1 LIMIT 4, 2select . order by c1 limit 0, 6select . order by c1 limit 0, 6select . order by c1 limit 0, 6分庫1分庫2分庫3cobarOrder By/Limit分庫1分庫2分庫3Order By/Limit456327810651111314973返回結(jié)果返回結(jié)果返回結(jié)果8SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2分庫1分庫2分庫3Order By/Limit456327810651111314973返回結(jié)果返回結(jié)果返回結(jié)果80最終

30、結(jié)果集SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2分庫1分庫2分庫3Order By/Limit45632781065111314973返回結(jié)果返回結(jié)果返回結(jié)果81最終結(jié)果集SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2分庫1分庫2分庫3Order By/Limit4563781065111314973返回結(jié)果返回結(jié)果返回結(jié)果82最終結(jié)果集SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2分庫1分庫2分庫3Order By/Limit456781065111314973返回結(jié)果返回結(jié)果返回

31、結(jié)果83最終結(jié)果集SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2分庫1分庫2分庫3Order By/Limit45678106511131497返回結(jié)果返回結(jié)果返回結(jié)果84最終結(jié)果集SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2最終結(jié)果集分庫1分庫2分庫3Order By/Limit5678106511131497返回結(jié)果返回結(jié)果返回結(jié)果844SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2最終結(jié)果集分庫1分庫2分庫3Order By/Limit4678106511131497返回結(jié)果返回

32、結(jié)果返回結(jié)果845SELECT c1 FROM tb1 ORDER BY c1 LIMIT 4, 2Order By/Limit 方案總結(jié)select c1 from tb1 order by c1 limit 100000000, 2 所有分庫都要查詢100000002條數(shù)據(jù) Cobar需要遍歷100000002條數(shù)據(jù) 對如下SQL 一次交互得到結(jié)果 Offset大小有限制Order By / Limit 優(yōu)化 目標(biāo)：解決 查詢量大問題 遍歷量大問題 前提 各個分庫數(shù)據(jù)分布大致一樣select c1 from tb1 order by c1 limit 9999999, 4select .

33、order by c1 limit 33333333, 4select . order by c1 limit 33333333, 4select . order by c1 limit 33333333, 4分庫1分庫2分庫3 step1:分成3條語句發(fā)給分庫分庫1分庫2分庫374538691076119返回結(jié)果返回結(jié)果返回結(jié)果select c1 from tb1 order by c1 limit 9999999, 4 找出查詢結(jié)果中最小和最大值分庫1分庫2分庫374538691076119返回結(jié)果返回結(jié)果返回結(jié)果 step2:以最小值和最大值為界再查詢select c1 from tb1

34、 order by c1 limit 9999999, 43115311分庫1分庫2分庫374538691076119返回結(jié)果返回結(jié)果返回結(jié)果 step3:反查出每一個返回結(jié)果的offsetselect c1 from tb1 order by c1 limit 9999999, 4311531133333331條33333333條33333332條分庫1分庫2分庫374538691076119返回結(jié)果返回結(jié)果返回結(jié)果 類似于原始方案select c1 from tb1 order by c1 limit 9999999, 431153119999996分庫1分庫2分庫374538691076

35、119返回結(jié)果返回結(jié)果返回結(jié)果 類似于原始方案select c1 from tb1 order by c1 limit 9999999, 41153119999997分庫1分庫2分庫37458691076119返回結(jié)果返回結(jié)果返回結(jié)果 類似于原始方案select c1 from tb1 order by c1 limit 9999999, 41153119999998分庫1分庫2分庫37458691076119返回結(jié)果返回結(jié)果返回結(jié)果 類似于原始方案select c1 from tb1 order by c1 limit 9999999, 4115119999999最終結(jié)果集最終結(jié)果集分庫1分

36、庫2分庫37458691076119返回結(jié)果返回結(jié)果返回結(jié)果 類似于原始方案select c1 from tb1 order by c1 limit 9999999, 4115119999999最終結(jié)果集分庫1分庫2分庫374 58691076119返回結(jié)果返回結(jié)果返回結(jié)果 類似于原始方案select c1 from tb1 order by c1 limit 9999999, 4115119999999最終結(jié)果集分庫1分庫2分庫374 58691076119返回結(jié)果返回結(jié)果返回結(jié)果 類似于原始方案select c1 from tb1 order by c1 limit 9999999, 41

37終結(jié)果集分庫1分庫2分庫374 58691076119返回結(jié)果返回結(jié)果返回結(jié)果 類似于原始方案select c1 from tb1 order by c1 limit 9999999, 4115119999999Order By / Limit 再優(yōu)化 Step1不必得到全部結(jié)果 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 betwe

38、en mi and ma) t1, (select count(*) from tb1 where c1 mi) t2Group BySELECT sum(price) FROM tb1 GROUP BY c1IDPRICEC1112.3222231.613158.860476.3131IDPRICEC124.460447.6131699.956Group BySELECT sum(price) FROM tb1 GROUP BY c1IDPRICEC1112.3222231.613158.860476.3131IDPRICEC124.460447.6131699.956SELECT sum(

39、price), c1 FROM tb1 GROUP BY c1 ORDER BY c112.322227.91318.860499.9567.61314.4604Group 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.86047.61314.4604最終結(jié)果集99.956Group B

40、ySELECT 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.86047.61314.4604最終結(jié)果集99.956最終結(jié)果集Group BySELECT sum(price) FROM tb1 GROUP BY c1IDPRICEC1112.3222231.613158.860476.3131IDPRI

41、CEC124.460447.6131699.956SELECT sum(price), c1 FROM tb1 GROUP BY c1 ORDER BY c112.3222215.51318.86044.460499.956最終結(jié)果集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.513

42、113.260499.956最終結(jié)果集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.956SQL執(zhí)行策略總結(jié) WHERE - 基于SQL轉(zhuǎn)發(fā) JOIN - 迭代 分布式索引 ORDER BY/LIMIT - 多次查詢減小數(shù)據(jù)量 GROUP BY - 增加OR

43、DER BYCobar 事務(wù)支持前端連接sql1sql2commitCobar 1.2 事務(wù)支持前端連接分庫1連接sql1sql2commitCobar 1.2 事務(wù)支持前端連接分庫1連接sql1commit分庫2連接sql2分庫3連接sql2Cobar 事務(wù)支持前端連接分庫1連接sql1commit分庫2連接sql2分庫3連接sql2 Commit有先后：隔離性問題 Commit有失?。阂恢滦詥栴}大綱n 中間件引入n Cobar策略n 水平拆分的數(shù)據(jù)分布n 幾種SQL元素的執(zhí)行策略n 事務(wù)策略n 系統(tǒng)實現(xiàn)n 實施應(yīng)用schematableSpacedataNodedatasourcecndb

44、pc2主備主備defaultoffer0主備offer1主備detail0主備defaultdefaultofferdetaildetail邏輯層次 接口同MySQLjdbc:mysql:/cobarIp:8066/cndb?user=foo&password=bar MySQLMySQLMySQLCobar結(jié)構(gòu)Front-end CommunicationMySQL Protcol Adaptor (BIO)Application1MySQLMySQL ProtocolMySQLMySQLData NodesMonitorConfigureHA PoolMySQLMySQLProce

45、ssor(1)Processor(n)SQL ExecutorSQL RouterSQL ParserResultMergerSQL ExecutorSQL RouterSQL ParserResultMerger. ManagerMySQL ProtocolMySQL Protocol MySQL Protocol MySQL ProtocolManagementProtocolMySQLMySQLMySQLCobar結(jié)構(gòu)Front-end CommunicationMySQL Protcol Adaptor (BIO)Application1MySQLMySQL ProtocolMySQL

46、MySQLData NodesMonitorConfigureHA PoolMySQLMySQLProcessor(1)Processor(n)SQL ExecutorSQL RouterSQL ParserResultMergerSQL ExecutorSQL RouterSQL ParserResultMerger. ManagerMySQL ProtocolMySQL Protocol MySQL Protocol MySQL ProtocolManagementProtocolApplication1JDBC和Server的通信協(xié)議MySQLJDBCDriverMySQLServerM

47、ySQL ProtocolJDBC和Server的通信協(xié)議PreparedStatement ps = conn.prepareStatement( select * from tb1 where id=?); ps.setLong(1, 12345);ResultSet rs = ps.executeQuery();MySQLServerApplication1MySQLJDBCDriverJDBC和Server的通信協(xié)議MySQLServerApplication1MySQLJDBCDriverPreparedStatement ps = conn.prepareStatement( se

48、lect * from tb1 where id=?); ps.setLong(1, 12345);ResultSet rs = ps.executeQuery();select * from tb1where id=?stmt_id/param_num/columm_numparameter_typecolumn_typeJDBC和Server的通信協(xié)議PreparedStatement ps = conn.prepareStatement( select * from tb1 where id=?); ps.setLong(1, 12345);ResultSet rs = ps.execu

49、teQuery();MySQLServerApplication1MySQLJDBCDriverJDBC和Server的通信協(xié)議PreparedStatement ps = conn.prepareStatement( select * from tb1 where id=?); ps.setLong(1, 12345);ResultSet rs = ps.executeQuery();MySQLServerApplication1MySQLJDBCDriverstmt_id param+field_countcolumn_typecolumn_val+column_val+JDBC和Serv

50、er的通信協(xié)議PreparedStatement ps = conn.prepareStatement( select * from tb1 where id=?); ps.setLong(1, 12345);ResultSet rs = ps.executeQuery();Application1MySQLJDBCDriverstmt_id param+field_countcolumn_typecolumn_val+column_val+MySQLServerJDBC和Server的通信協(xié)議Statement stmt = conn.createStatement();ResultSet

51、rs = stmt.executeQuery( “select * from tb1 where id=12345”);Application1MySQLJDBCDriversqlfield_countcolumn_typecolumn_val+column_val+MySQLServerJDBC和Server的通信協(xié)議Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery( “select * from tb1 where id=12345”);Application1MySQLJDBCDriversq

52、lfield_countcolumn_typecolumn_val+column_val+MySQLServerCobarServerProcessor(1)Processor(n)MySQLMySQLMySQLCobar結(jié)構(gòu)Front-end CommunicationMySQL Protcol Adaptor (BIO)Application1MySQLMySQL ProtocolMySQLMySQLData NodesMonitorConfigureHA PoolMySQLMySQLSQL ExecutorSQL RouterSQL ParserResultMergerSQL Execu

53、torSQL RouterSQL ParserResultMerger. ManagerMySQL ProtocolMySQL Protocol MySQL Protocol MySQL ProtocolManagementProtocolSQL +ParametersResultSetMetaDataResultSet(Rows)Cobar 通信層 統(tǒng)一管理NIO的Buffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBBy

54、teBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer共16MBBuffer PoolCobar 通信層 統(tǒng)一管理NIO的Buffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBBy

55、teBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer共16MBBuffer Poolselect * from tb1 where id=?Cobar 通信層 統(tǒng)一管理NIO的Buffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KB

56、ByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer共16MBBuffer Poolselect * from tb1 where id=?Cobar 通信層 統(tǒng)一管理NIO的Buffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4

57、KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer4KBByteBuffer共16MBBuffer Pool15萬TPS. MySQLMySQLMySQLCobar結(jié)構(gòu)Front-end CommunicationMySQL Protcol Adaptor (B

58、IO)Application1MySQLMySQL ProtocolMySQLMySQLData NodesMonitorConfigureHA PoolMySQLMySQLProcessor(1)Processor(n)SQL ExecutorSQL RouterResultMergerSQL ExecutorSQL RouterResultMergerManagerMySQL ProtocolMySQL Protocol MySQL Protocol MySQL ProtocolManagementProtocolSQL ParserSQL ParserSELECT id, member_

59、id FROM wp_image WHERE member_id = 123SQL Parserselectidmember_idwp_image=member_id123exprListfrom架構(gòu)演變 基于JavaCC生成SQL Parser性能較差，優(yōu)化不方便 仿照ANTLR生成的Parser結(jié)構(gòu)手寫基于LL(*)的識別器，中間對象過多 基于LL(2)識別器的手寫第一版第二版第三版架構(gòu)調(diào)整LexertokentokentokentokenChar ReaderExpression ParserDML ParserSQL StringLexertokenExpression ParserD

60、ML ParserSQL char第三版LL(2)第二版LL(*)cache功能對比第一版 第二版 第三版dontNOYESYES.123e-2YESYESYES7ElevenNOYESYES_latin 0 x123NOYESYESabc defNOYESYESINSERT(Quadratic, 3, 4, What)NOYESYESTRIM(LEADING x FROM xxxbarxxx)NOYESYESUnion selectYESYESYEStable joinPARTPARTYES基本元素語法元素MySQL 5.5 語法結(jié)構(gòu)IdentifierKeywordLiteralPunctuati