02-分布式微服務(wù)數(shù)據(jù)庫訪問框架sharding jdbc設(shè)計與實現(xiàn)

1、分布式微服務(wù)數(shù)據(jù)庫訪問框架Sharding-JDBC的設(shè)計與實現(xiàn)當當 張亮虧聯(lián)網(wǎng)領(lǐng)域數(shù)據(jù)庫面臨的問題高可用存儲穩(wěn)定性要求高幵發(fā)訪問頻繁數(shù)據(jù)海量各種數(shù)據(jù)庫方案對比RDBMSNoSQLNewSQLSQL支持原生不支持不完善事務(wù)ACID+XABASEF1存儲引擎成熟較成熟待驗證數(shù)據(jù)分片有限支持支持支持勱態(tài)擴容不支持有限支持支持較好RDBMS解決方案的優(yōu)缺點開發(fā)友好，面向SQL存儲引擎穩(wěn)定單節(jié)點事務(wù)引擎成熟未達閥值的單機性能高單節(jié)點并發(fā)訪問頻率受限單節(jié)點數(shù)據(jù)承載量受限 分布式事務(wù)性能難以接受分布式擴展困難當當數(shù)據(jù)庫中間層的關(guān)注重點分片分庫分表讀寫分離分布式主鍵事務(wù)弱XA柔性事務(wù)治理配置勱態(tài)化數(shù)據(jù)源自勱

2、切換分片類型垂直分片業(yè)務(wù)拆分水平分片算法規(guī)則拆分SELECT * FROM t_user WHERE id=1SELECT * FROM t_order WHERE id=1SELECT * FROM t_user WHERE id=1SELECT * FROM t_order WHERE id=1垂直分片SELECT * FROM t_user WHERE id=1SELECT * FROM t_user WHERE id=2SELECT * FROM t_user WHERE id=1SELECT * FROM t_user WHERE id=2水平分片水平分片策略哈希范圍標簽時間復合us

3、er_1SELECT * FROM t_user WHERE id=1SELECT * FROM t_user WHERE id=2user_2哈希取模分片策略user_1 11000SELECT * FROM t_user WHERE id=1SELECT * FROM t_user WHERE id=1001user_210012000SELECT * FROM t_user WHERE id=2001user_32000范圍分片策略user_bjSELECT * FROM t_user WHERE location=bjSELECT * FROM t_user WHERE locatio

4、n =shuser_sh標簽分片策略db_2015SELECT * FROM t_order WHERE year=2015SELECT * FROM t_order WHERE year=2016db_2016SELECT * FROM t_order WHERE year=2017db_2017時間分片策略user_1SELECT * FROM t_orderWHERE user_id=1 AND order_id=1001user_2SELECT * FROM t_orderWHERE user_id=1 AND order_id=1002SELECT * FROM t_orderWHE

5、RE user_id=2 AND order_id=2001SELECT * FROM t_orderWHERE user_id=2 AND order_id=2002order_1order_2order_1order_2復合分片策略實現(xiàn)方案非透明業(yè)務(wù)修改SQL指定數(shù)據(jù)源透明化完全屏蔽分片細則ProxyORMJDBC數(shù)據(jù)庫單一任意任意ORM任意單一任意異構(gòu)語言任意僅Java僅Java性能損耗略高損耗低損耗低透明化實現(xiàn)方案選型Sharding-JDBC是什么開源的分布式數(shù)據(jù)庫中間件，它無需額外部署和依賴，舊代碼遷秱成本幾乎為零。面向開發(fā)的微服務(wù)與于原生的基礎(chǔ)類庫。完整的實現(xiàn)了分庫分表、讀寫分離

6、和分布式主鍵功能，幵初步實現(xiàn)了柔性事務(wù)，治理正在功能開發(fā)中。Sharding-JDBC的由來Sharding-JDBC兼容性SQL支持DQLDMLDDL數(shù)據(jù)庫支持MySQLOracleSQLServerPostgreSQLORM支持MybatisJPAHibernateJDBC業(yè)務(wù)代碼 Sharding-JDBC業(yè)務(wù)代碼 Sharding-JDBCSQL解析結(jié)果歸幵SQL改寫SQL執(zhí)行應用應用Sharding-JDBC內(nèi)部實現(xiàn)分片規(guī)則配置JDBC規(guī)范改寫SQL路由數(shù)據(jù)分片架構(gòu)圖JDBC規(guī)范改寫ShardingDataSourceShardingConnectioninitializeConne

7、ctionConnectionConnectionroute(Prepared)StatementmergeDataSourcecreateDataSourceDataSourcecreateSharding(Prepared)StatementwrapShardingResultSetexecuteResultSetcreateget無需解析SQL的場景僅分庫的單分片查詢僅分庫的跨分片的無聚合、排序、分組查詢需要解析SQL的場景包含分表的查詢跨分片的聚合、排序、分組查詢復雜查詢，如：OR、UNION、子查詢等為什么需要解析SQLSELECTHIGH_PRIORITY STRAIGHT_JOI

8、N SQL_BUFFER_RESULT SQL_NO_CACHEid, name FROM table_x WHERE id=1INTO OUTFILE file_a LOCK IN SHARE MODESELECT id, name FROM table_a WHERE id=1table : table_x sharding-column : id sharding-value : 1SELECTHIGH_PRIORITY STRAIGHT_JOIN SQL_BUFFER_RESULT SQL_NO_CACHEid, name FROM token WHERE id=1INTO OUTFI

9、LE file_a LOCK IN SHARE MODESQL解析示例LexerParserASTOutputVisitor使用Druid DruidAbstractMySQLVisitorParseContextSQLBuilder問題（僅針對丏注亍 Sharding的解析）性能需解析全SQL，語法樹復雜需二次訪問語法樹，再次生成解析結(jié)果準確性重新生成導致原SQL變化易讀性針對OutputVisitor重寫，編碼零散兼容性Druid升級后不兼容之前版本SQL解析 初版自研SQL解析LexerParserParseContextSQLBuilder提升性能僅解析與分片相關(guān)部分無需二次訪問語法樹

10、準確性使用原SQL，無需再生成易讀性代碼聚合，無零散編碼穩(wěn)定性減少依賴，剝離第三方lib升級兼容問題SQL解析 再版單分片鍵精確路由（=，IN） + 范圍路由（Between）單分片鍵Inline表達式，eg：t_user_$userid % 8多分片鍵分片算法的復雜度由用戶自行控制StandardInline Complex無分片鍵通過Hint直接指定DataNodeHint不分片None路由算法SQL中僅存在單一表單表SQL中僅存在多表，但分表策略完全一致級聯(lián)表SQL中僅存在多表，丐分表策略不一致笛卡爾積（不推薦OLTP）路由類型標記TokenLIMIT m, n = LIMIT 0, n

11、AVG(expr) = SUM(expr), COUNT(expr)排序列生成補列分組列生成補列ORDER BY補充主鍵生成補列表名稱LIMIT AVG ORDER BYGROUP BY自增主鍵SQL改寫score1009080score958575表t_score_0表t_score_1score8580score9080score8575SELECT score FROM t_score ORDER BY score DESC LIMIT 1，2SQL不改寫的查詢結(jié)果表t_score_0表t_score_1最終歸幵結(jié)果為何改寫LimitSQL改寫SELECT score FROM t_sc

12、ore ORDER BY score DESC LIMIT 0，3SQL改寫后的查詢結(jié)果score9590score1009080score958575表t_score_0表t_score_1最終歸幵結(jié)果為何改寫Limit無需補列的場景SELECT id, name FROM t_user WHERE ORDER BY name需要補列的場景SELECT id, age FROM t_user WHERE ORDER BY nameSELECT id, name AS n FROM t_user WHERE ORDER BY name SELECT u.* FROM t_user AS u J

13、OIN t_order AS o ONu.user_id=o.user_id WHERE ORDER BY name為何補列LIMIT m, n = LIMIT 0, n跳過前n條數(shù)據(jù)比較幵返回最小(大)值多結(jié)果集累加，DISTINCT暫未實現(xiàn)AVG(expr) = SUM(expr), COUNT(expr)return SUM / COUNT排序列生成補列多結(jié)果集歸幵排序分組列生成補列所有結(jié)果集加載至內(nèi)存進行分組、聚合、排序LIMITMIN，MAX SUM，COUNT AVGORDER BY GROUP BY結(jié)果歸幵僅讀寫分離僅分庫分表分庫分表+讀寫分離讀寫分離Inline表達式分布式自增序列柔性事務(wù)其他功能全新package + maven坐標com.dangdang.ddframe.rdb = io.shardingjdbc配置勱態(tài)化勱態(tài)修改數(shù)據(jù)源 + 分片規(guī)則支持ZooKeeper + Etcd數(shù)據(jù)訪問治理熔斷數(shù)據(jù)訪問實例禁用讀寫分離從庫數(shù)據(jù)庫訪問鏈路追蹤Whats new for 2.x20152016.022016.052016.042016.06分庫分表配置多樣化讀寫分離2016.11ddframe勱態(tài)數(shù)據(jù)源簡單分表Best efforts柔