開源MySQL分布式中間件剖析

1、dble-開源MySQL分布式中間件剖析目錄 CONTENTS一、dble 簡介二、分布式事務(wù)實(shí)現(xiàn) 三、復(fù)雜查詢實(shí)現(xiàn) 四、查詢優(yōu)化舉例分布式系統(tǒng)架構(gòu)示意圖開源分布式中間件double開源分布式中間件doubledble是基于MyCat的企業(yè)級加強(qiáng)版的sharding中間件優(yōu)化代碼結(jié)構(gòu)Bug修復(fù)(100+)核心功能完善（例：分布式事務(wù)，SQL支持度等）復(fù)雜查詢的支持與改進(jìn)非核心功能選擇性裁減（例如異構(gòu)數(shù)據(jù)庫支持等）項(xiàng)目地址:/actiontech/dbledble 功能特性項(xiàng)目地址:/actiontech/dble分布式事務(wù)實(shí)現(xiàn)分布式事務(wù)問題的產(chǎn)生顯式分布式事務(wù)：轉(zhuǎn)賬隱式分布式事務(wù)：發(fā)午餐補(bǔ)助分

2、布式事務(wù)的實(shí)現(xiàn)-用普通事務(wù)簡單實(shí)現(xiàn)第一階段：執(zhí)行第二階段：提交分布式事務(wù)的實(shí)現(xiàn)-普通事務(wù)實(shí)現(xiàn)方式的問題導(dǎo)致數(shù)據(jù)不一致可能的場景網(wǎng)絡(luò)閃斷參與者數(shù)據(jù)庫服務(wù)故障參與者數(shù)據(jù)庫宿主機(jī)宕機(jī)協(xié)調(diào)者故障協(xié)調(diào)者宿主機(jī)宕機(jī)分布式事務(wù)的實(shí)現(xiàn)-MySQL XA事務(wù)XA事務(wù)執(zhí)行階段XA事務(wù)PREPARE階段XA事務(wù)COMMIT階段分布式事務(wù)的實(shí)現(xiàn)- MySQL XA事務(wù)網(wǎng)絡(luò)閃斷：已經(jīng)完成PREPARE的XA事務(wù)， 即使連接斷開，事務(wù)狀態(tài)也 不會丟失?？梢孕陆ㄟB接繼 續(xù)提交中間件可以設(shè)定次數(shù)閾值， 多次執(zhí)行，直到成功。分布式事務(wù)的實(shí)現(xiàn)- MySQL XA事務(wù)參與者數(shù)據(jù)庫服務(wù)故障MySQL 5.7的 PREPARE狀態(tài)會寫

3、入binlog即使數(shù)據(jù)庫服務(wù)重啟， PREPARE狀態(tài)也不 會丟失。中間件可以在數(shù)據(jù)庫服務(wù)恢復(fù)后，繼續(xù)提 交。分布式事務(wù)的實(shí)現(xiàn)- MySQL XA事務(wù)參與者數(shù)據(jù)庫宿主機(jī)宕機(jī)由于MySQL 5.7的 PREPARE狀態(tài)會進(jìn)binlog， 如果數(shù)據(jù)結(jié)點(diǎn)采用了半同步等高可用方式，XA事務(wù)的PREPARE狀態(tài)會同步到slave機(jī)器 上.即使發(fā)生了主從切換，狀態(tài)也仍然不丟失中間件可以在高可用切換之后，繼續(xù)提交。分布式事務(wù)的實(shí)現(xiàn)- MySQL XA事務(wù)協(xié)調(diào)者進(jìn)程級故障：觀察結(jié)點(diǎn)狀態(tài)如下： NODE1：NODE2:能否判斷NODE 2上的事務(wù)該提交or回滾？分布式事務(wù)的實(shí)現(xiàn)- MySQL XA事務(wù) (協(xié)調(diào)者

4、進(jìn)程級故障)無法判斷node2上的事務(wù)該提交or回滾!可能情況1：可能情況2：分布式事務(wù)的實(shí)現(xiàn)- MySQL XA事務(wù)協(xié)調(diào)者進(jìn)程級故障：解決方案：日志先行。在XA PREPARE 和 XA COMMIT 之前先將狀態(tài)日志落盤。異常發(fā)生后，可以根據(jù)日志進(jìn)行手動恢復(fù) 或者自動恢復(fù)。衍生問題：寫日志可能成為性能瓶頸分布式事務(wù)的實(shí)現(xiàn)- MySQL XA事務(wù)協(xié)調(diào)者寫日志性能問題:啟發(fā)：MySQL Group Commit：思路 ：dble Group Commit：分布式事務(wù)的實(shí)現(xiàn)- MySQL XA事務(wù)協(xié)調(diào)者宿主機(jī)故障：需要狀態(tài)日志高可用：（Road Map）寫入ZooKeeper,Consul 等分

5、布式事務(wù)的實(shí)現(xiàn)- MySQL XA事務(wù)MySQL XA 事務(wù)實(shí)現(xiàn)分布式事務(wù)復(fù)盤優(yōu)點(diǎn)：大幅度提高了分布式事務(wù)的健壯性。在發(fā)生一些常見故障時候，能夠做到數(shù)據(jù)的最終一致性。缺點(diǎn)：一定程度上降低了性能（寫日志，多次網(wǎng)絡(luò)交互等）。限制：需要工程實(shí)踐上的外部依賴。XA PREPARE狀態(tài)不丟失 - MySQL5.7 XA實(shí)現(xiàn),MySQL配置。 數(shù)據(jù)結(jié)點(diǎn)服務(wù)器故障- 數(shù)據(jù)節(jié)點(diǎn)的MySQL高可用狀態(tài)日志的不丟失-日志的高可用。復(fù)雜查詢實(shí)現(xiàn)預(yù)備知識-一般中間件執(zhí)行邏輯一般中間件內(nèi)部執(zhí)行邏輯待解決問題：復(fù)雜聚合例:select count(distinct column) JOINUNIONSub Query以上

6、混合復(fù)雜語句預(yù)備知識-一般中間件執(zhí)行邏輯工程上的解決方式：復(fù)雜聚合：修改業(yè)務(wù)邏輯避開JOIN :盡量使用ER 表， global 表UNION：修改業(yè)務(wù)邏輯避開Sub Query：修改業(yè)務(wù)邏輯避開這些方法都不錯，但在不同和場景下， 成本代價相對高昂有沒有通用的解決方式呢？復(fù)雜查詢實(shí)現(xiàn)-關(guān)系代數(shù)復(fù)雜查詢實(shí)現(xiàn)思路:關(guān)系代數(shù) 查詢樹1.解析SQL時候，將SQL轉(zhuǎn)為基本元組，以及對元組進(jìn)行關(guān)系運(yùn)算，構(gòu)建查詢樹 。2.將基本元組及可下發(fā)的運(yùn)算作為查詢樹的葉子節(jié)點(diǎn)，下發(fā)到物理數(shù)據(jù)結(jié)點(diǎn)查詢。3.結(jié)果集返回后，不可下發(fā)的運(yùn)算作為查詢樹的非葉子節(jié)點(diǎn)對子樹返回結(jié)果處理。關(guān)系代數(shù)（部分）復(fù)雜查詢實(shí)現(xiàn)-JOINJOI

7、N 舉例：select * from table1 a inner join table2 b on a.id =b.id ;table1 有四個分片dn14 table2 有2個分片 dn1,dn2查詢樹：下發(fā)語句：SQL1:select * from table1; SQL2:select * from table2;中間件后續(xù)操作：1.各個節(jié)點(diǎn)結(jié)果合并2.將結(jié)果集按照 a.id =b.id過濾及JOIN復(fù)雜查詢實(shí)現(xiàn)-UNIONUNION 舉例：select id,name from table1 a union select id,name from table2 b ;table1 有

8、四個分片dn14 table2 有2個分片 dn1,dn2查詢樹：下發(fā)語句：SQL1:select id,name from table1 ; SQL2:select id,name from table2;中間件后續(xù)操作：1.各個節(jié)點(diǎn)結(jié)果合并2.將結(jié)果集UNION All 操作3.對結(jié)果集去重復(fù)雜查詢實(shí)現(xiàn)-復(fù)雜聚合聚合 舉例：select count(distinct col) from table2; table2 有2個分片 dn1,dn2查詢樹：下發(fā)語句：SQL1:select distinct col from table1 ;中間件后續(xù)操作：1.各個節(jié)點(diǎn)結(jié)果合并2.對結(jié)果分組合并3

9、.計算聚合函數(shù)COUNT復(fù)雜查詢實(shí)現(xiàn)-子查詢子查詢舉例：select * from table1 a where id (select id from table2 b where name =test) ;table1 有四個分片dn14table2 有2個分片 dn1,dn2查詢樹1：下發(fā)語句：SQL1:s select id from table2 b where name =test;中間件后續(xù)操作：1.各個節(jié)點(diǎn)結(jié)果合并2.對結(jié)果進(jìn)行判斷生成New Query，二次查詢樹 或者報錯給客戶端查詢樹2： 下發(fā)語句：select * from table1 a where condition

10、中間件后續(xù)操作：各個節(jié)點(diǎn)結(jié)果聚合dble 內(nèi)部結(jié)構(gòu)（執(zhí)行邏輯）通過構(gòu)建查詢樹的方式 可以支持各種復(fù)雜查詢聚合分組JOINUNION (ALL)Sub Query復(fù)雜類型/表達(dá)式的排序視圖（on Road Map）以上組合查詢優(yōu)化舉例查詢優(yōu)化舉例優(yōu)化（改寫下發(fā)SQL）目的：正確的查詢結(jié)果生成良好的查詢計劃優(yōu)化查詢速度構(gòu)建適應(yīng)分布式場景的查詢計劃查詢優(yōu)化舉例優(yōu)化原理：關(guān)系代數(shù)的等價規(guī)則Relational Algebraic Equivalence Transformation Rule.pdf聚合操作查詢優(yōu)化KlugEquivalence of Relational Algebra and Re

11、lational Calculus Query Languages Having Aggregate FunctionsYan and LarsonEager Aggregation and Lazy Aggregation.pdfS Chaudhuri ， K ShimIncluding Group-By in Query Optimization .pdf外連接查詢優(yōu)化Rosenthal and ReinerExtending the Algebraic Framework of Query Processing to Handle Outerjoins.pdfGalindo-Legari

12、a and RosenthalHow to extend a conventional optimizer to handle one- and two-sided outer join.pdfGalindo-Legariaouterjoins as disjunctions .PDF關(guān)聯(lián)子查詢優(yōu)化Won KimOn Optimizing an SQL-like Nested Query.pdfGanski and WongOptimization of Nested SQL Queries Revisited.pdfGalindo-Legaria and Joshi 2001Orthogon

13、al Optimization of Subqueries and Aggregation.pdf查詢優(yōu)化舉例分布式場景下的一些優(yōu)化經(jīng)驗(yàn)：正確性為第一要務(wù)利用已有的全局/ER關(guān)系表減少中間件與數(shù)據(jù)庫之間的數(shù)據(jù)傳輸盡量將能下發(fā)的計算下發(fā)給結(jié)點(diǎn)完成減少中間件運(yùn)算的空間/時間復(fù)雜度查詢優(yōu)化舉例規(guī)則:保證LIMIT正確性例 ：原始語句：select * from tb_test order by id limit 20,10；如果原樣下發(fā)到各個結(jié)點(diǎn)，則返回的結(jié)果集再合并最終結(jié)果不能保證正確 優(yōu)化后下發(fā)的語句:select * from tb_test order by id limit0,30(20+

14、10).查詢優(yōu)化舉例規(guī)則： JOIN 查詢樹生成時將與JOIN條件相關(guān)列添加排序例 ：select * from table1 a inner join table2 b on a.id =b.id ;下發(fā)語句：SQL1:select * from table1; SQL2:select * from table2; 中間件時間復(fù)雜度:設(shè)SQL1返回M行，SQL2返回N 行，比較運(yùn)算為 O(M*N).改寫為：SQL1:select * from table1 order by id;（p個分片） SQL2:select * from table2 order by id;（q個分片） 增加了節(jié)

15、點(diǎn)排序的成本，相當(dāng)于多路并發(fā)排序中間件比較運(yùn)算時間復(fù)雜度:合并結(jié)點(diǎn)數(shù)據(jù)復(fù)雜度A ：2個多路有序歸并排序=O(p*logM)+O(q*logN) Join時間復(fù)雜度B ：最小O(Min(M,N),最大O(M+N-1).總復(fù)雜度Z=A+B.可以流水線處理查詢優(yōu)化舉例規(guī)則:Having中與聚合無關(guān)的條件可以合并到where中例 ：select id from test group by id havingcol2 =test and count(*)2;下發(fā)語句：SQL1: select id from test;需要拉取全量的test 表數(shù)據(jù)在中間件處理改寫為：SQL:select test.id

16、 from test where test.col2 =test；查詢優(yōu)化舉例規(guī)則:查詢樹父節(jié)點(diǎn)約束條件推向內(nèi)部葉子節(jié)點(diǎn)例 ：select * from table1 inner join table2 on = where table1.id5 and table2.id5 order by name SQL2：select * from table2where table2.id10 order by name 條件下推，減少從結(jié)點(diǎn)獲取的數(shù)據(jù)量查詢優(yōu)化舉例規(guī)則:NEST LOOP JOIN（需配置啟用）例 ：select * from table1 ,table2 where table1

17、.id=table2.id and =test;下發(fā)語句：SQL1:select * from table1 where=test order by id; SQL2:select * from table2 order by id;需要拉取全量的table2表數(shù)據(jù)在中間件處理改寫為：SQL1:select * from table1 where=test order by id;（驅(qū)動表） SQL1:select * from table2 where id in(sql1 的id list)注：目前驅(qū)動表的選擇還很粗糙，未來期望1.通過維護(hù)數(shù)據(jù)統(tǒng)計直方圖，2.hint來解決。查詢優(yōu)化舉例規(guī)則:利用global和ER關(guān)系表1. global 表優(yōu)化:多表查詢盡