知數(shù)堂48期公開課t數(shù)據(jù)庫審核平臺

1、數(shù) 據(jù) 庫 審 核 平 臺 Themis韓鋒平臺簡介引入背景平臺選型平臺實踐不足及發(fā)展引 入 背 景 運維規(guī)模較大系統(tǒng): 200+開發(fā): 1000+數(shù)據(jù)庫: 40+: 7+ 設計開發(fā)質(zhì)量不高表規(guī)模xxG某系統(tǒng)表字段異常3字段數(shù)121使用SQL占比1061 / 3730長度5850 (398)索引數(shù)13字段重復索引2未用索引1 設計開發(fā)質(zhì)量不高SELECT /*+ INDEX (A1xx) */ SUM(A2.CRKSL),SUM(A2.CRKSL*A2.DJ) FROMx A2,x A1WHERE A2.CRKFLAG=AND A2.CDATE>=AND A2.CDATE<現(xiàn)狀 多

2、種不平衡重運維，輕架構、優(yōu)化重商業(yè)重手工，輕平臺、工具重初級，輕中高級趨勢 重心轉(zhuǎn)移 數(shù)據(jù)建模 數(shù)據(jù)治理 數(shù)據(jù)整合數(shù)據(jù)業(yè)務架構Data Architect 數(shù)據(jù)庫架構 數(shù)據(jù)庫結構 SQL質(zhì)量數(shù)據(jù)邏輯架構Application DBA 數(shù)據(jù)庫 數(shù)據(jù) 基礎架構Product DBA數(shù)據(jù)物理架構解決方法？ Oracle結構設計規(guī)范 Oracle開發(fā)規(guī)范 MySQL結構設計規(guī)范 MySQL開發(fā)規(guī)范出路在哪里平 臺 的 選 型業(yè)內(nèi)做法代表做法特點優(yōu)點缺點智能分析引擎自研SQL分析引擎，分析語句成本，并自動實現(xiàn)審核、分流、限流等操作。 可自動審核 擴展后可線上使用，實現(xiàn)分流等 難度大 效率不高工具+人工審

3、核自研工具加后期人工審核， 事后過濾、人工標記，跟 蹤全流程 對SQL精細粒度， 靈活度大。 完成對SQL整個生命周期的管理。 技術難度較小 人工投入商業(yè)直接抽取SQL，分析， 仍需人工介入 功能強大，有技術支持 周期短，見效快 費用較高 擴展性差我們的選擇 自研轉(zhuǎn)變思想，全民動員，人人開發(fā)知識沉淀，做好標準，方便落地小步快跑，落地實施，不斷修正結合自身，定制目標，學做減法他山之石可以攻玉，大膽引進審 核 平 臺 實 踐平臺提供能力 快速發(fā)現(xiàn)問題操作方式 WEB界面審核維度 結構、語句、執(zhí)行計劃、執(zhí)行特征審核結果 報告（WEB、XLS）平臺使用者研發(fā)DBA問題快速掌握整體性能指導設計開發(fā)批量篩

4、選低效SQL詳細信息輔助診斷實現(xiàn)原理規(guī)則1規(guī)則2規(guī)則3SQL PLAN對象結構問題1問題3SQL文本執(zhí)行特征問題2背景 大平臺結構元數(shù)據(jù)平臺元數(shù)據(jù)服務托管數(shù)據(jù)庫審核數(shù)據(jù)變更性能管理管理數(shù)據(jù)管理數(shù)據(jù)安全健康管理對象管理平臺管理12個子系統(tǒng)，36個模塊背景 技術棧審核對象審核類別示例規(guī)則對象級大表未分區(qū)未創(chuàng)建主鍵語句級多表關聯(lián)標量子執(zhí)行計劃級大表全表掃描笛卡爾積執(zhí)行特征級掃描塊數(shù)與返回比例過低子游標數(shù)過多架構簡圖流程圖模塊劃分數(shù)據(jù)規(guī)則系統(tǒng)管理結果展示內(nèi)容內(nèi)容OracleMySQL對象統(tǒng)計信息*特征*結構信息*特征SQLSQL文本執(zhí)行計劃*游標綁定變量執(zhí)行特征原理規(guī)則說明規(guī)則是系統(tǒng)的部分，主要包括規(guī)

5、則、計分等。規(guī)則部分可簡單劃分如下： 數(shù)據(jù)庫類型Oracle、MySQL 規(guī)則復雜度簡單、復雜規(guī)則 審核對象對象類、文本類、執(zhí)行計劃類、執(zhí)行特征類規(guī)則定義規(guī)則示例（對象級）規(guī)則類別規(guī)則說明表、分區(qū)大表過多超過指定規(guī)模沒有分區(qū)單表或單分區(qū)數(shù)據(jù)量過多存在并行屬性分區(qū)數(shù)量過多索引外鍵沒有索引字段重復索引聚簇因子多大索引字段重復索引字段字段數(shù)量過多長度多長字段類型不匹配規(guī)則實現(xiàn)（對象級）對象類規(guī)則比較簡單，原理是從目標數(shù)據(jù)里數(shù)據(jù)字典信息，再根據(jù)結果進行分數(shù)的計算。規(guī)則示例（執(zhí)行計劃級）規(guī)則類別規(guī)則說明路徑大表掃描大索引掃描大索引快速全掃描索引跳躍掃描分區(qū)全掃描非連續(xù)分區(qū)掃描跨分區(qū)掃描表間關聯(lián)笛卡爾積多

6、表關聯(lián)嵌套循環(huán)層次過多類型轉(zhuǎn)換存在隱式類型轉(zhuǎn)換綁定變量未使用綁定變量執(zhí)行計劃格式規(guī)則實現(xiàn)（執(zhí)行計劃）左側(cè)，執(zhí)行計劃數(shù)據(jù)樣本。右側(cè)，規(guī)則實現(xiàn)樣例（mongo語句）。規(guī)則實現(xiàn)（執(zhí)行計劃）大表全表掃描規(guī)則 èdb.sqlplan.find(SQL_ID:'fzzxntg1g9u6r',OBJECT_TYPE:1,OBJECT_NAME:1,OPERATION_DISPLAY:1,OPTIONS:1,_id:0) "O" : "SELECT STATEMENT", "OPTIONS" : null, "OB

7、JECT_NAME" : null "O" : "TABLE ACCESS", "OPTIONS" : "FULL", "OBJECT_NAME" : "T_TEST"db.sql.find("OPERATION":"TABLE"OPTIONS":"FULL","USERNAME":"username", "ETL_DATE":"

8、;etl_date").forEach(function(x)if(db.obj_tab_info.findOne( "TABLE_NAME":x.OBJECT_NAME,$or:ACCESS",過濾執(zhí)行計劃按統(tǒng)計信息篩選"NUM_ROWS":$gt:params1,"PHY_SIZE(MB)":$gt:params0)db.tmp.save( "SQL_ID":x.SQL_ID,"PLAN_HASH_VALUE":x.PLAN_HASH_VALUE, "OBJEC

9、T_NAME":x.OBJECT_NAME);)"按特征篩選獲得執(zhí)行計劃并計分匯總展示報告規(guī)則實現(xiàn)（執(zhí)行計劃）規(guī)則示例（文本級）規(guī)則說明select *嵌套select子句謂詞中出現(xiàn)反向操作符多個過濾條件通過or連接存在子存在三個以上的多表關聯(lián)存在全連接或外連接delete中必須出現(xiàn)whereupdate中出現(xiàn)order by子句inlist元素過多重復子句出現(xiàn)union集合操作規(guī)則實現(xiàn)（文本級）實現(xiàn)就是基于正則表達式，做模式匹配。 示例 BAD_JOIN“rule_cmd”:“(cross join)|(outer join)” 示例 SUB QUERY規(guī)則示例（執(zhí)行特征

10、級）規(guī)則說明掃描塊數(shù)與返回數(shù)比例過低子游標過多elapsed_timecpu_timebuffer_getsdisk_readsdirect_writesExecutions規(guī)則管理規(guī)則是平臺，其豐富程度代表整體能力。為了滿足不同需求，平臺動態(tài)增加自有規(guī)則，只要遵守統(tǒng)一格式即可。規(guī)則管理平臺對規(guī)則動態(tài)關閉，打開，修改規(guī)則參數(shù)等。任務管理左側(cè)為任務發(fā)布界面下面為任務結果查看界面審核結果概覽（對象）審核結果明細（對象）審核結果概覽（執(zhí)行計劃）審核結果明細（執(zhí)行計劃）審核結果明細（執(zhí)行計劃）審核結果明細（執(zhí)行計劃）遇到的坑mysql在json格式執(zhí)行計劃中出的問題【會話進入sleep狀態(tài)，假死】解決方法執(zhí)行會話之前設置wait_timtout=3，這個時間根據(jù)實際情況進行調(diào)整?！緮?shù)據(jù)量過大，長時間沒有結果】會話處于query狀態(tài)，但是數(shù)據(jù)量很大或因為數(shù)據(jù)庫對format=json支持不是很好，長時間不出來，會影響其他會話。解決方法使用pt-kill工具殺掉會話。為了防止誤殺，