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

1、數(shù) 據(jù) 庫 審 核 平 臺(tái) Themis韓鋒平臺(tái)簡(jiǎn)介引入背景平臺(tái)選型平臺(tái)實(shí)踐不足及發(fā)展引 入 背 景 運(yùn)維規(guī)模較大系統(tǒng): 200+開發(fā): 1000+數(shù)據(jù)庫: 40+: 7+ 設(shè)計(jì)開發(fā)質(zhì)量不高表規(guī)模xxG某系統(tǒng)表字段異常3字段數(shù)121使用SQL占比1061 / 3730長(zhǎng)度5850 (398)索引數(shù)13字段重復(fù)索引2未用索引1 設(shè)計(jì)開發(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ùn)維，輕架構(gòu)、優(yōu)化重商業(yè)重手工，輕平臺(tái)、工具重初級(jí)，輕中高級(jí)趨勢(shì) 重心轉(zhuǎn)移 數(shù)據(jù)建模 數(shù)據(jù)治理 數(shù)據(jù)整合數(shù)據(jù)業(yè)務(wù)架構(gòu)Data Architect 數(shù)據(jù)庫架構(gòu) 數(shù)據(jù)庫結(jié)構(gòu) SQL質(zhì)量數(shù)據(jù)邏輯架構(gòu)Application DBA 數(shù)據(jù)庫 數(shù)據(jù) 基礎(chǔ)架構(gòu)Product DBA數(shù)據(jù)物理架構(gòu)解決方法？ Oracle結(jié)構(gòu)設(shè)計(jì)規(guī)范 Oracle開發(fā)規(guī)范 MySQL結(jié)構(gòu)設(shè)計(jì)規(guī)范 MySQL開發(fā)規(guī)范出路在哪里平 臺(tái) 的 選 型業(yè)內(nèi)做法代表做法特點(diǎn)優(yōu)點(diǎn)缺點(diǎn)智能分析引擎自研SQL分析引擎，分析語句成本，并自動(dòng)實(shí)現(xiàn)審核、分流、限流等操作。 可自動(dòng)審核 擴(kuò)展后可線上使用，實(shí)現(xiàn)分流等 難度大 效率不高工具+人工審

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

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

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

6、表關(guān)聯(lián)嵌套循環(huán)層次過多類型轉(zhuǎn)換存在隱式類型轉(zhuǎn)換綁定變量未使用綁定變量執(zhí)行計(jì)劃格式規(guī)則實(shí)現(xiàn)（執(zhí)行計(jì)劃）左側(cè)，執(zhí)行計(jì)劃數(shù)據(jù)樣本。右側(cè)，規(guī)則實(shí)現(xiàn)樣例（mongo語句）。規(guī)則實(shí)現(xiàn)（執(zhí)行計(jì)劃）大表全表掃描規(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í)行計(jì)劃按統(tǒng)計(jì)信息篩選"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í)行計(jì)劃并計(jì)分匯總展示報(bào)告規(guī)則實(shí)現(xiàn)（執(zhí)行計(jì)劃）規(guī)則示例（文本級(jí)）規(guī)則說明select *嵌套select子句謂詞中出現(xiàn)反向操作符多個(gè)過濾條件通過or連接存在子存在三個(gè)以上的多表關(guān)聯(lián)存在全連接或外連接delete中必須出現(xiàn)whereupdate中出現(xiàn)order by子句inlist元素過多重復(fù)子句出現(xiàn)union集合操作規(guī)則實(shí)現(xiàn)（文本級(jí)）實(shí)現(xiàn)就是基于正則表達(dá)式，做模式匹配。 示例 BAD_JOIN“rule_cmd”:“(cross join)|(outer join)” 示例 SUB QUERY規(guī)則示例（執(zhí)行特征

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