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

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

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

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

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

6、表關(guān)聯(lián)嵌套循環(huán)層次過(guò)多類(lèi)型轉(zhuǎn)換存在隱式類(lèi)型轉(zhuǎn)換綁定變量未使用綁定變量執(zhí)行計(jì)劃格式規(guī)則實(shí)現(xiàn)（執(zhí)行計(jì)劃）左側(cè)，執(zhí)行計(jì)劃數(shù)據(jù)樣本。右側(cè)，規(guī)則實(shí)現(xiàn)樣例（mongo語(yǔ)句）。規(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",過(guò)濾執(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ī)則說(shuō)明select *嵌套select子句謂詞中出現(xiàn)反向操作符多個(gè)過(guò)濾條件通過(guò)or連接存在子存在三個(gè)以上的多表關(guān)聯(lián)存在全連接或外連接delete中必須出現(xiàn)whereupdate中出現(xiàn)order by子句inlist元素過(guò)多重復(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ī)則說(shuō)明掃描塊數(shù)與返回?cái)?shù)比例過(guò)低子游標(biāo)過(guò)多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)閉，打開(kāi)，修改規(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ì)劃中出的問(wèn)題【會(huì)話進(jìn)入sleep狀態(tài)，假死】解決方法執(zhí)行會(huì)話之前設(shè)置wait_timtout=3，這個(gè)時(shí)間根據(jù)實(shí)際情況進(jìn)行調(diào)整?！緮?shù)據(jù)量過(guò)大，長(zhǎng)時(shí)間沒(méi)有結(jié)果】會(huì)話處于query狀態(tài)，但是數(shù)據(jù)量很大或因?yàn)閿?shù)據(jù)庫(kù)對(duì)format=json支持不是很好，長(zhǎng)時(shí)間不出來(lái)，會(huì)影響其他會(huì)話。解決方法使用pt-kill工具殺掉會(huì)話。為了防止誤殺，