作者：韓鋒

在東陽(yáng)等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專(zhuān)注、極致的服務(wù)理念，為客戶提供網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作 網(wǎng)站設(shè)計(jì)制作專(zhuān)業(yè)公司,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站建設(shè),營(yíng)銷(xiāo)型網(wǎng)站建設(shè),外貿(mào)網(wǎng)站建設(shè),東陽(yáng)網(wǎng)站建設(shè)費(fèi)用合理。

出處：DBAplus社群分享

Themis開(kāi)源地址：https://github.com/CreditEaseDBA

拓展閱讀：宜信開(kāi)源|數(shù)據(jù)庫(kù)審核軟件Themis的規(guī)則解析與部署攻略

【技術(shù)沙龍002期】數(shù)據(jù)中臺(tái)：宜信敏捷數(shù)據(jù)中臺(tái)建設(shè)實(shí)踐|宜信技術(shù)沙龍 將于5月23日晚8點(diǎn)線上直播，點(diǎn)擊報(bào)名

一、面臨的挑戰(zhàn)

1、運(yùn)維規(guī)模及種類(lèi)

我相信，這也是很多公司、很多DBA正在面臨或未來(lái)都會(huì)面臨的一些問(wèn)題。正是存在問(wèn)題，促使我們考慮引入數(shù)據(jù)庫(kù)審核平臺(tái)。

首先是運(yùn)維規(guī)模與人力資源之間的矛盾。從我們的情況來(lái)看，運(yùn)維了包括Oracle、MySQL、MongoDB、redis四類(lèi)數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)規(guī)模幾十套，支持公司千余名開(kāi)發(fā)人員及上百套業(yè)務(wù)系統(tǒng)。也許有的朋友會(huì)問(wèn)，從運(yùn)維規(guī)模上看，并不是很大。

的確，與很多互聯(lián)網(wǎng)公司相比，數(shù)據(jù)庫(kù)數(shù)十套的估摸并不是太大；但與互聯(lián)網(wǎng)類(lèi)公司不同，類(lèi)似宜信這類(lèi)金融類(lèi)公司對(duì)數(shù)據(jù)庫(kù)的依賴性更大，大量的應(yīng)用是重?cái)?shù)據(jù)庫(kù)類(lèi)的，且其使用復(fù)雜程度也遠(yuǎn)比互聯(lián)網(wǎng)類(lèi)的復(fù)雜。DBA除了日常運(yùn)維（這部分我們也在通過(guò)自研平臺(tái)提升運(yùn)維效率）外，還需要有大量精力應(yīng)對(duì)數(shù)據(jù)庫(kù)設(shè)計(jì)、開(kāi)發(fā)、優(yōu)化類(lèi)的工作。當(dāng)面對(duì)大量的開(kāi)發(fā)團(tuán)隊(duì)需要服務(wù)時(shí)，這個(gè)矛盾就更加凸顯出來(lái)。

2、案例

結(jié)構(gòu)設(shè)計(jì)

第二個(gè)挑戰(zhàn)，是數(shù)據(jù)庫(kù)設(shè)計(jì)、開(kāi)發(fā)質(zhì)量參差不齊的問(wèn)題。上圖就展示了一個(gè)結(jié)構(gòu)設(shè)計(jì)問(wèn)題。某核心系統(tǒng)的核心表，在這個(gè)系統(tǒng)運(yùn)行的SQL中，28%都是跟這個(gè)對(duì)象有關(guān)的。當(dāng)我們分析其結(jié)構(gòu)時(shí)，發(fā)現(xiàn)了很多的問(wèn)題：

• 表的規(guī)模很大，從設(shè)計(jì)之初就沒(méi)有考慮到拆分邏輯（例如分庫(kù)、分表、分區(qū)設(shè)計(jì)），也沒(méi)有必要的數(shù)據(jù)庫(kù)清理、歸檔策略。
• 表存在100多個(gè)字段，字段數(shù)很多且不同字段使用特征也不一致，沒(méi)有考慮到必要拆表設(shè)計(jì)。
• 表有13個(gè)索引，數(shù)目過(guò)多。表的索引過(guò)度，勢(shì)必會(huì)影響其DML效率。
• 還存在一個(gè)索引，在持續(xù)監(jiān)控中發(fā)現(xiàn)，其從未被使用過(guò)。顯然這是一個(gè)“多余”的索引。
• 還有兩個(gè)字段存在重復(fù)索引的現(xiàn)象，這也說(shuō)明在建立索引之初是比較隨意的。
• 單個(gè)記錄定義長(zhǎng)度為5800多個(gè)字節(jié)，但實(shí)際其平均保存長(zhǎng)度只有不到400字節(jié)，最大長(zhǎng)度也不長(zhǎng)。
• 分析其字段內(nèi)容，還發(fā)現(xiàn)有3個(gè)字段類(lèi)型定義異常。即沒(méi)有使用應(yīng)有的類(lèi)型保存數(shù)據(jù)，例如使用數(shù)字類(lèi)型保存日期。

綜上所述，這個(gè)表設(shè)計(jì)的問(wèn)題還有很多，而且這個(gè)表非常重要，大量語(yǔ)句訪問(wèn)和其相關(guān)。

SQL語(yǔ)句

上圖展示的是一個(gè)語(yǔ)句運(yùn)行效率的問(wèn)題。從字面可見(jiàn)，兩個(gè)表做關(guān)聯(lián)查詢，但在指定條件時(shí)沒(méi)有指定關(guān)聯(lián)條件。在下面的執(zhí)行計(jì)劃中可見(jiàn)，數(shù)據(jù)庫(kù)采用了笛卡爾積的方式運(yùn)行。從后面的成本、估算時(shí)間等可見(jiàn)，這是一個(gè)多么“巨大”的SQL。其在線上運(yùn)行的影響，可想而知。

也許有人會(huì)說(shuō)，這是一個(gè)人為失誤，一般不會(huì)發(fā)生。但我要說(shuō)的是，第一，人為失誤無(wú)法避免，誰(shuí)也不能保證寫(xiě)出SQL的運(yùn)行質(zhì)量；第二，開(kāi)發(fā)人員對(duì)數(shù)據(jù)庫(kù)的理解不同，很難保證寫(xiě)出的SQL都是高效的；第三，開(kāi)發(fā)人員面臨大量業(yè)務(wù)需求，經(jīng)常處理趕工狀態(tài)，很難有更多的精力放在優(yōu)化上面。這因?yàn)橛羞@些問(wèn)題，線上語(yǔ)句執(zhí)行質(zhì)量就成了DBA經(jīng)常面臨的挑戰(zhàn)之一。

3、重心轉(zhuǎn)移

這是一張很經(jīng)典的圖，它描述了和數(shù)據(jù)庫(kù)相關(guān)工作的職能劃分。作為DBA，除了面臨以上挑戰(zhàn)外，從數(shù)據(jù)庫(kù)工作發(fā)展階段及自身發(fā)展需求來(lái)看，也面臨一個(gè)重心的轉(zhuǎn)移：原有傳統(tǒng)DBA的運(yùn)維職能逐步被弱化，大量的工具、平臺(tái)的涌現(xiàn)及數(shù)據(jù)庫(kù)自我運(yùn)維能力的提升，簡(jiǎn)化DBA的工作；緊隨而來(lái)的數(shù)據(jù)庫(kù)架構(gòu)、結(jié)構(gòu)設(shè)計(jì)、SQL質(zhì)量?jī)?yōu)化逐步成為重點(diǎn)；再往上層的數(shù)據(jù)治理、建模等工作也越來(lái)越受到一些公司的重視。由此可見(jiàn)，DBA未來(lái)工作的中心也逐步上移。對(duì)中間數(shù)據(jù)邏輯結(jié)構(gòu)部分，也需要一些工具、平臺(tái)更好地支撐DBA的工作。

除上述情況外，我司還存在幾種的不平衡。

• 從DBA日常工作來(lái)看，傳統(tǒng)運(yùn)維工作還是占了較大的比重，而架構(gòu)優(yōu)化類(lèi)則相對(duì)較少。通過(guò)引入這一平臺(tái)，可以幫助DBA更方便地開(kāi)展架構(gòu)、優(yōu)化類(lèi)工作。

• 公司使用了較多的商業(yè)產(chǎn)品，而開(kāi)源則使用較少。從公司長(zhǎng)遠(yuǎn)戰(zhàn)略來(lái)看，開(kāi)源產(chǎn)品的使用會(huì)越來(lái)越多。從功能角度來(lái)看，商業(yè)產(chǎn)品相較于開(kāi)源產(chǎn)品是有優(yōu)勢(shì)的?；陂_(kāi)源產(chǎn)品的軟件開(kāi)發(fā)，對(duì)開(kāi)發(fā)者自身技術(shù)技能要求更高。希望通過(guò)引入這一產(chǎn)品，可以更容易完成這一轉(zhuǎn)型過(guò)程。

• 沒(méi)有平臺(tái)之前，DBA還是大量通過(guò)手工方式設(shè)計(jì)、優(yōu)化數(shù)據(jù)庫(kù)，其效率十分低下。特別是面對(duì)眾多產(chǎn)品線、眾多開(kāi)發(fā)團(tuán)隊(duì)時(shí)，往往感覺(jué)力不從心。

公司自有團(tuán)隊(duì)人員上，還是以初中級(jí)為主，中高級(jí)人員相對(duì)較少。如何快速提升整體設(shè)計(jì)、優(yōu)化能力，保證統(tǒng)一的優(yōu)化效果成為擺在面前的問(wèn)題。

正是有了上述多種的不平衡，促使我們考慮引入工具、平臺(tái)去解決數(shù)據(jù)庫(kù)質(zhì)量問(wèn)題。

我剛來(lái)到公司時(shí)，看到公司的這些問(wèn)題，也曾考慮通過(guò)制度、規(guī)范的形式進(jìn)行解決。一開(kāi)始就著手制定了很多的規(guī)范，然后在各個(gè)部門(mén)去培訓(xùn)、宣講。這種方式運(yùn)行一段時(shí)間后，暴露出一些問(wèn)題：

• 整體效果改善并不明顯。實(shí)施效果取決于各個(gè)部門(mén)的重視程度及員工的個(gè)人能力。

• 規(guī)范落地效果無(wú)法度量，也很難做到量化分析。往往只能通過(guò)上線運(yùn)行結(jié)果來(lái)直觀感知。

• 缺乏長(zhǎng)期有效的跟蹤機(jī)制。無(wú)法對(duì)具體某個(gè)系統(tǒng)長(zhǎng)期跟蹤其運(yùn)行質(zhì)量。

• 從DBA的角度來(lái)看，面對(duì)大量的系統(tǒng)，很難依據(jù)每個(gè)規(guī)范，詳細(xì)審核其結(jié)構(gòu)設(shè)計(jì)、SQL運(yùn)行質(zhì)量。

面臨上述這些挑戰(zhàn)、現(xiàn)存的各種問(wèn)題，該如何解決？

經(jīng)過(guò)討論，最后大家一致認(rèn)為，引入數(shù)據(jù)庫(kù)審核平臺(tái)，可以幫助解決上面所述問(wèn)題。

二、平臺(tái)的選型

1、業(yè)內(nèi)做法

在項(xiàng)目之初，我考察了業(yè)內(nèi)其它企業(yè)是如何數(shù)據(jù)庫(kù)審核的，大致可分為三個(gè)思路：

第一類(lèi)，是以BAT公司為代表的互聯(lián)網(wǎng)類(lèi)公司。它們通過(guò)自研的SQL引擎，可實(shí)現(xiàn)成本分析、自動(dòng)審核、訪問(wèn)分流、限流等，可做到事前審核、自動(dòng)審核。但技術(shù)難度較大，公司現(xiàn)有技術(shù)能力明顯不足。

第二類(lèi)，是通過(guò)自研工具收集DB運(yùn)行情況，根據(jù)事前定義規(guī)則進(jìn)行審核，結(jié)合人工操作來(lái)完成整個(gè)審核流程。這種方案只能做到事后審核，但技術(shù)難度較小，靈活度很大。其核心就是規(guī)則集的制定，可根據(jù)情況靈活擴(kuò)展。

第三類(lèi)，是一些商業(yè)產(chǎn)品，實(shí)現(xiàn)思路類(lèi)似第二類(lèi)，但是加上一些自主分析能力，功能更為強(qiáng)大，但仍需人工介入處理且需要不小資金投入。而且考察幾款商業(yè)產(chǎn)品，沒(méi)有能完全滿足所需功能的。

綜合上面幾類(lèi)做法，最終確定我們采用“工具+人工審核”的方式，自研自己的審核平臺(tái)。

2、我們的選擇——自研

在啟動(dòng)研發(fā)這一平臺(tái)之初，我們就在團(tuán)隊(duì)內(nèi)部達(dá)成了一些共識(shí)。

• DBA需要扭轉(zhuǎn)傳統(tǒng)運(yùn)維的思想，每個(gè)人都參與到平臺(tái)開(kāi)發(fā)過(guò)程中。

• 過(guò)去我們積累的一些內(nèi)容（例如前期制定的規(guī)范）可以作為知識(shí)庫(kù)沉淀下來(lái)，并標(biāo)準(zhǔn)化，這些為后期規(guī)則的制定做好了鋪墊。

• 在平臺(tái)推進(jìn)中，從最簡(jiǎn)單的部分入手，開(kāi)發(fā)好的就上線實(shí)施，觀察效果；根據(jù)實(shí)施效果，不斷修正后面的工作。

• 結(jié)合我們自身的特點(diǎn)，定制目標(biāo)；對(duì)于有些較復(fù)雜的部分，可果斷延后甚至放棄。

• 參考其它公司或商業(yè)產(chǎn)品的設(shè)計(jì)思想，大膽引入。

三、審核平臺(tái)實(shí)踐

下面來(lái)看看，審核平臺(tái)的基本功能及實(shí)現(xiàn)原理及方法，這部分是本次分享的重點(diǎn)。

1、平臺(tái)定位

在項(xiàng)目之初，我們就平臺(tái)的定位做了描述：

• 平臺(tái)的核心能力是快速發(fā)現(xiàn)數(shù)據(jù)庫(kù)設(shè)計(jì)、SQL質(zhì)量問(wèn)題。

• 平臺(tái)只做事后審核，自主優(yōu)化部分放在二期實(shí)現(xiàn)。當(dāng)然在項(xiàng)目設(shè)計(jì)階段引入這個(gè)，也可以起到一部分事前審核的功能。

• 通過(guò)Web界面完成全部工作，主要使用者是DBA和有一定數(shù)據(jù)庫(kù)基礎(chǔ)的研發(fā)人員。

• 可針對(duì)某個(gè)用戶審核，可審核包括數(shù)據(jù)結(jié)構(gòu)、SQL文本、SQL執(zhí)行特征、SQL執(zhí)行計(jì)劃等多個(gè)維度。

• 審核結(jié)果通過(guò)Web頁(yè)面或?qū)С鑫募男问教峁?/p>

• 平臺(tái)需支持公司主流的Oracle、MySQL，其它數(shù)據(jù)庫(kù)放在二期實(shí)現(xiàn)。

• 盡量提供靈活定制的能力，便于日后擴(kuò)展功能。

2、平臺(tái)使用者

作為平臺(tái)的兩類(lèi)主要使用方，研發(fā)人員和DBA都可以從平臺(tái)中受益。

• 對(duì)于研發(fā)人員而言，只用這平臺(tái)可方便定位問(wèn)題，及時(shí)進(jìn)行修改；此外通過(guò)對(duì)規(guī)則的掌握，也可以指導(dǎo)他們?cè)O(shè)計(jì)開(kāi)發(fā)工作。

• 對(duì)于DBA而言，可快速掌握多個(gè)系統(tǒng)的整體情況，批量篩選出低效SQL，并可通過(guò)平臺(tái)提供的信息快速診斷一般性問(wèn)題。

3、實(shí)現(xiàn)原理

整個(gè)平臺(tái)的基本實(shí)現(xiàn)原理很簡(jiǎn)單，就是將我們的審核對(duì)象（目前支持四種），通過(guò)規(guī)則集進(jìn)行篩選。符合規(guī)則的審核對(duì)象，都是疑似有問(wèn)題的。平臺(tái)會(huì)將這些問(wèn)題及關(guān)聯(lián)信息提供出來(lái)，供人工甄別使用。由此可見(jiàn)，平臺(tái)的功能強(qiáng)大與否，主要取決于規(guī)則集的豐富程度。平臺(tái)也提供了部分?jǐn)U展能力，方便擴(kuò)展規(guī)則集。

4、平臺(tái)設(shè)計(jì)

審核對(duì)象

在開(kāi)始介紹平臺(tái)實(shí)現(xiàn)之前，再來(lái)熟悉下“審核對(duì)象”這個(gè)概念。目前我們支持的有四類(lèi)對(duì)象，分別說(shuō)明一下。

• 對(duì)象級(jí)。這里所說(shuō)的對(duì)象就是指數(shù)據(jù)庫(kù)對(duì)象，常見(jiàn)的表、分區(qū)、索引、視圖、觸發(fā)器等等。典型規(guī)則，例如大表未分區(qū)等。

• 語(yǔ)句級(jí)。這里所說(shuō)的語(yǔ)句級(jí)，實(shí)際是指SQL語(yǔ)句文本本身。典型規(guī)則，例如多表關(guān)聯(lián)。

• 執(zhí)行計(jì)劃級(jí)。這里是指數(shù)據(jù)庫(kù)中SQL的執(zhí)行計(jì)劃。典型規(guī)則，例如大表全表掃描。

• 執(zhí)行特征級(jí)。這里是指語(yǔ)句在數(shù)據(jù)庫(kù)上的真實(shí)執(zhí)行情況。典型規(guī)則，例如掃描塊數(shù)與返回記錄比例過(guò)低。

需要說(shuō)明一下，這四類(lèi)審核對(duì)象中，后三種必須在系統(tǒng)上線運(yùn)行后才會(huì)抓取到，第一種可以在只有數(shù)據(jù)結(jié)構(gòu)的情況下運(yùn)行(個(gè)別規(guī)則還需要有數(shù)據(jù))。

此外，上述規(guī)則中，除了第二類(lèi)為通用規(guī)則外，其他都與具體數(shù)據(jù)庫(kù)相關(guān)。即每種的數(shù)據(jù)庫(kù)，都有自己不同的規(guī)則。

架構(gòu)簡(jiǎn)圖

這里畫(huà)出是系統(tǒng)架構(gòu)框架簡(jiǎn)圖，我簡(jiǎn)單說(shuō)明一下。

圖中的方框部分，為平臺(tái)的主要模塊。底色不同的模塊，表示當(dāng)前的進(jìn)度狀態(tài)不同。虛線代表數(shù)據(jù)流，實(shí)線代表控制流。其核心為這幾個(gè)模塊：

• 數(shù)據(jù)采集模塊。它是負(fù)責(zé)從數(shù)據(jù)源抓取審核需要的基礎(chǔ)數(shù)據(jù)。目前支持從Oracle、MySQL抓取。

• OBJ/SQL存儲(chǔ)庫(kù)。這是系統(tǒng)的共同存儲(chǔ)部分，采集的數(shù)據(jù)和處理過(guò)程中的中間數(shù)據(jù)、結(jié)果數(shù)據(jù)都保存在這里。其核心數(shù)據(jù)分為對(duì)象類(lèi)和SQL類(lèi)。物理是采用的MongoDB。

• 核心管理模塊。圖中右側(cè)虛線部分包含的兩個(gè)模塊：SQL管理和OBJ管理就是這部分。它主要是完成對(duì)象的全生命周期管理。目前只做了簡(jiǎn)單的對(duì)象過(guò)濾功能，因此還是白色底色，核心的功能尚未完成。

• 審核規(guī)則和審核引擎模塊。這部分是平臺(tái)一期的核心組件。審核規(guī)則模塊是完成規(guī)則的定義、配置工作。審核引擎模塊是完成具體規(guī)則的審核執(zhí)行部分。

• 優(yōu)化規(guī)則和優(yōu)化引擎模塊。這部分是平臺(tái)二期的核心組件。目前尚未開(kāi)發(fā)，因此為白色底色。

• 系統(tǒng)管理模塊。這部分是完成平臺(tái)基礎(chǔ)功能，例如任務(wù)調(diào)度、空間管理、審核報(bào)告生成、導(dǎo)出等功能。

流程圖

讓我們從處理流程的角度，看看平臺(tái)的整體處理過(guò)程。

1) “規(guī)則管理”部分，這部分主要完成以下一些功能。

• 初始化規(guī)則。平臺(tái)本身內(nèi)置了很多規(guī)則，在這一過(guò)程中到導(dǎo)入到配置庫(kù)中。

• 新增規(guī)則。平臺(tái)本身提供了一定的擴(kuò)展能力，可以依據(jù)規(guī)范新增一條規(guī)則。

• 修改規(guī)則?？梢愿鶕?jù)自身情況開(kāi)啟或關(guān)閉規(guī)則。對(duì)于每條規(guī)則，還內(nèi)置了一些參數(shù)，也可在此處修改。此外，針對(duì)違反規(guī)則的情況，還可以設(shè)置扣分方法（例如違反一次扣幾分、最多可扣幾分）等。

• 規(guī)則本身及相關(guān)參數(shù)、配置信息等都會(huì)存儲(chǔ)在配置庫(kù)中。

2) “任務(wù)管理”部分，這是后臺(tái)管理的一個(gè)部分，主要完成與任務(wù)相關(guān)的工作。系統(tǒng)中的大多數(shù)交互都是通過(guò)作業(yè)異步完成的。其后臺(tái)是通過(guò)celery+flower實(shí)現(xiàn)的。

3) “數(shù)據(jù)采集”部分，這部分是通過(guò)任務(wù)調(diào)度定時(shí)出發(fā)采集作業(yè)完成，也有少量部分是實(shí)時(shí)查詢線上庫(kù)完成的。采集的結(jié)果保存在數(shù)據(jù)庫(kù)中，供后續(xù)分析部分調(diào)用。

4) “規(guī)則解析”部分，這部分是由用戶通過(guò)界面觸發(fā)，任務(wù)調(diào)度模塊會(huì)啟動(dòng)一個(gè)后臺(tái)異步任務(wù)完成解析工作。之所以設(shè)計(jì)為異步完成，主要是審核工作可能時(shí)間較長(zhǎng)（特別是選擇審核類(lèi)別較多、審核對(duì)象很多、開(kāi)啟的審核規(guī)則較多）的情況。審核結(jié)果會(huì)保存在數(shù)據(jù)庫(kù)中。

5) “任務(wù)查看、導(dǎo)出”部分，在用戶發(fā)起審核任務(wù)后，可在此部分查看進(jìn)度（處于審核中、還是審核完成）。當(dāng)審核完成后，可選擇審核任務(wù)，瀏覽審核結(jié)果或選擇導(dǎo)出均可。如果是選擇導(dǎo)出的話，會(huì)生成異步后臺(tái)作業(yè)生成文件，放置在下載服務(wù)器上。

以上就是整個(gè)審核的大體流程。后續(xù)將看到各部分的詳細(xì)信息。

模塊劃分

總結(jié)一下，平臺(tái)主要是由上述四個(gè)模塊組成：數(shù)據(jù)采集、規(guī)則解析、系統(tǒng)管理、結(jié)果展示。后面將針對(duì)不同模塊的實(shí)現(xiàn)，進(jìn)行詳細(xì)說(shuō)明。

5、數(shù)據(jù)采集

采集內(nèi)容

先來(lái)看看數(shù)據(jù)采集模塊。從表格可見(jiàn)，兩種類(lèi)型數(shù)據(jù)庫(kù)的采集內(nèi)容不同。

Oracle提供了較為豐富的信息，需要的基本都可采集到；MySQL功能相對(duì)能采集到的信息較少。

表格中的“對(duì)號(hào)+星號(hào)”，表示非定時(shí)作業(yè)完成，而是后面實(shí)時(shí)回庫(kù)抓取的。下面簡(jiǎn)單說(shuō)下，各部分的采集內(nèi)容。

• 對(duì)象級(jí)，采集了對(duì)象統(tǒng)計(jì)信息、存儲(chǔ)特征、結(jié)構(gòu)信息、訪問(wèn)特征。

• SQL級(jí)，采集了SQL文本，執(zhí)行計(jì)劃、緩存游標(biāo)、綁定變量、執(zhí)行特征等。

這些信息都將作為后面審核的依據(jù)。

采集原理

下面簡(jiǎn)單介紹下采集的與原理：

• Oracle部分，是通過(guò)定時(shí)作業(yè)采集的AWR數(shù)據(jù)，然后轉(zhuǎn)儲(chǔ)到一套MongoDB中。這里跟有些類(lèi)似產(chǎn)品不同，沒(méi)有直接采集內(nèi)存中的數(shù)據(jù)，而是取自離線的數(shù)據(jù)。其目的是盡量減少對(duì)線上運(yùn)行的影響。Oracle提供的功能比較豐富，通過(guò)對(duì)AWR及數(shù)據(jù)字典的訪問(wèn)，基本就可以獲得全部的數(shù)據(jù)。

• MySQL部分，情況就要復(fù)雜一些，原因是其功能沒(méi)有那么豐富。多類(lèi)數(shù)據(jù)是通過(guò)不同源來(lái)獲取。SQL文本類(lèi)及執(zhí)行特征類(lèi)的，是通過(guò)pt工具分析慢查詢?nèi)罩径〞r(shí)入到Anemometer平臺(tái)庫(kù)，然后從此庫(kù)傳入MongoDB。其它類(lèi)信息（包括數(shù)據(jù)字典類(lèi)、執(zhí)行計(jì)劃類(lèi)等）是在需要時(shí)通過(guò)實(shí)時(shí)回庫(kù)查詢的。為了防止影響主庫(kù)，一般是通過(guò)路由到從庫(kù)上執(zhí)行獲得的。

6、規(guī)則解析

概要說(shuō)明

下面介紹整個(gè)系統(tǒng)最為核心的部分—規(guī)則解析模塊，它所完成的功能是依據(jù)定義規(guī)則，審核采集的數(shù)據(jù)，篩選出違反規(guī)則的數(shù)據(jù)。對(duì)篩選出的數(shù)據(jù)進(jìn)行計(jì)分，并記錄下來(lái)供后續(xù)生成審核報(bào)告使用。同時(shí)還會(huì)記錄附加信息，用于輔助進(jìn)行一些判斷工作。

這里有個(gè)核心的概念—“規(guī)則”。后面可以看到一個(gè)內(nèi)置規(guī)則的定義，大家就會(huì)比較清楚了。從分類(lèi)來(lái)看，可大致分為以下幾種。

• 從數(shù)據(jù)庫(kù)類(lèi)型角度來(lái)區(qū)分，規(guī)則可分為Oracle、MySQL。不是所有規(guī)則都區(qū)分?jǐn)?shù)據(jù)庫(kù)，文本類(lèi)的規(guī)則就不區(qū)分。

• 從復(fù)雜程度來(lái)區(qū)分，規(guī)則可分為簡(jiǎn)單規(guī)則和復(fù)雜規(guī)則。這里所說(shuō)的簡(jiǎn)單和復(fù)雜，實(shí)際是指規(guī)則審核的實(shí)現(xiàn)部分。簡(jiǎn)單規(guī)則是可以描述為MongoDB或關(guān)系數(shù)據(jù)庫(kù)的一組查詢語(yǔ)句；而復(fù)雜規(guī)則是需要在外部通過(guò)程序體實(shí)現(xiàn)的。

• 從審核對(duì)象角度來(lái)區(qū)分，規(guī)則可分為對(duì)象類(lèi)、文本類(lèi)、執(zhí)行計(jì)劃類(lèi)和執(zhí)行特征類(lèi)。下面會(huì)針對(duì)每類(lèi)審核對(duì)象，分別做說(shuō)明。

規(guī)則定義

這是一個(gè)規(guī)則體的聲明對(duì)象，我說(shuō)明一下各字段含義，大家也可對(duì)規(guī)則有個(gè)清晰的認(rèn)識(shí)。

• db_type：規(guī)則的數(shù)據(jù)庫(kù)類(lèi)別，支持Oracle、MySQL。

• input_parms：輸入?yún)?shù)。規(guī)則是可以定義多個(gè)輸出參數(shù)，這是一個(gè)參數(shù)列表，每個(gè)參數(shù)自身又是一個(gè)字典類(lèi)，描述參數(shù)各種信息。

• output_parms：輸出參數(shù)。類(lèi)似上面的輸入?yún)?shù)，也是一個(gè)字典對(duì)象列表。描述了根據(jù)規(guī)則返回信息結(jié)構(gòu)。

• rule_complexity：規(guī)則是復(fù)雜規(guī)則還是簡(jiǎn)單規(guī)則。如果是簡(jiǎn)單規(guī)則，則直接取rule_cmd內(nèi)容作為規(guī)則審核的實(shí)現(xiàn)。如果是復(fù)雜規(guī)則，則是從外部定義的rule_name命令腳本中獲得規(guī)則實(shí)現(xiàn)。

• rule_cmd：規(guī)則的實(shí)現(xiàn)部分。規(guī)則可能是mongodb的查詢語(yǔ)句、可能是一個(gè)正則表達(dá)式，具體取決于rule_type。

• rule_desc：規(guī)則描述，僅供顯示。

• rule_name：規(guī)則名稱。是規(guī)則的唯一標(biāo)識(shí)，全局唯一。

• rule_status：規(guī)則狀態(tài)，ON或是OFF。對(duì)于關(guān)閉的規(guī)則，在審核時(shí)會(huì)忽略它。

• rule_summary：一個(gè)待廢棄的字段，意義同rule_desc。

• rule_text：規(guī)則類(lèi)型，分為對(duì)象、文本、執(zhí)行計(jì)劃、執(zhí)行特征四類(lèi)。圖中的示例標(biāo)識(shí)一個(gè)文本類(lèi)型的規(guī)則，rule_cmd是正則表達(dá)式。

• solution：觸發(fā)此規(guī)則的優(yōu)化建議。

• weight：權(quán)重，即單次違反規(guī)則的扣分制。

• max_score：扣分上限，為了避免違反一個(gè)規(guī)則，產(chǎn)生過(guò)大影響，設(shè)置此參數(shù)。

規(guī)則定義（對(duì)象級(jí)）

先來(lái)看第一類(lèi)規(guī)則—對(duì)象規(guī)則。這是針對(duì)數(shù)據(jù)庫(kù)對(duì)象設(shè)置的一組規(guī)則。上面表格，顯示了一些示例。常見(jiàn)的對(duì)象，諸如表、分區(qū)、索引、字段、函數(shù)、存儲(chǔ)過(guò)程、觸發(fā)器、約束、序列等都是審核的對(duì)象。以表為例，內(nèi)置了很多規(guī)則。

例如：第一個(gè)的“大表過(guò)多”。表示一個(gè)數(shù)據(jù)庫(kù)中的大表個(gè)數(shù)超過(guò)規(guī)則定義閥值。這里的大表又是通過(guò)規(guī)則輸入?yún)?shù)來(lái)確定，參數(shù)包括表記錄數(shù)、表物理尺寸。整體描述這個(gè)規(guī)則就是“數(shù)據(jù)庫(kù)中超過(guò)指定尺寸或指定記錄數(shù)的表的個(gè)數(shù)超過(guò)規(guī)定閥值，則觸發(fā)審核規(guī)則”。其它對(duì)象的規(guī)則也類(lèi)似。

規(guī)則實(shí)現(xiàn)（對(duì)象級(jí)）

對(duì)象規(guī)則的實(shí)現(xiàn)部分，比較簡(jiǎn)單。除個(gè)別規(guī)則外，基本都是對(duì)數(shù)據(jù)字典信息進(jìn)行查詢，然后依據(jù)規(guī)則定義進(jìn)行判斷。上面示例就是對(duì)索引的一個(gè)規(guī)則實(shí)現(xiàn)中，查詢數(shù)據(jù)字典信息。

規(guī)則定義（執(zhí)行計(jì)劃級(jí)）

第二類(lèi)規(guī)則是執(zhí)行計(jì)劃類(lèi)的規(guī)則，它也劃分為若干類(lèi)別。例如訪問(wèn)路徑類(lèi)、表間關(guān)聯(lián)類(lèi)、類(lèi)型轉(zhuǎn)換類(lèi)、綁定變量類(lèi)等。

以最為常見(jiàn)的的訪問(wèn)路徑類(lèi)為例，進(jìn)行說(shuō)明下。如最為常見(jiàn)的一個(gè)規(guī)則“大表掃描”。它表示的是SQL語(yǔ)句的執(zhí)行中，執(zhí)行了對(duì)大表的訪問(wèn)，并且訪問(wèn)的路徑是采用全表掃描的方式。這個(gè)規(guī)則的輸入?yún)?shù)，包含了對(duì)大表的定義（物理大小或記錄數(shù)）；輸出部分則包括了表名、表大小及附加信息（包括整個(gè)執(zhí)行計(jì)劃、指定大表的統(tǒng)計(jì)信息等內(nèi)容）。

這類(lèi)規(guī)則針對(duì)的數(shù)據(jù)源，是從線上數(shù)據(jù)庫(kù)中抓取的。Oracle部分是直接從AWR中按時(shí)間段提取的，MySQL部分是使用explain命令返查數(shù)據(jù)庫(kù)得到的。

信息存儲(chǔ)格式

在這里特別說(shuō)明一下，在保存執(zhí)行計(jì)劃的時(shí)候，使用了MongoDB這種文檔性數(shù)據(jù)庫(kù)。目的就是利用其schemaless特性，方便兼容不同數(shù)據(jù)庫(kù)、不同版本執(zhí)行計(jì)劃的差異。都可以保存在一個(gè)集合中，后續(xù)的規(guī)則審核也是利用的mongo中的查詢語(yǔ)句實(shí)現(xiàn)的。這也是最初引入mongo的初衷，后續(xù)也將其它類(lèi)信息放入庫(kù)中。現(xiàn)在整個(gè)審核平臺(tái)，除了pt工具接入的部分使用MySQL外，其余都在MongoDB中。此外，MySQL庫(kù)可以直接輸出json格式的執(zhí)行計(jì)劃，很方便就入庫(kù)了；Oracle部分也組成json格式入庫(kù)。

規(guī)則實(shí)現(xiàn)（執(zhí)行計(jì)劃）

左邊就是一個(gè)Oracle的執(zhí)行計(jì)劃保存在MongoDB中的樣子。其實(shí)就是將sqlplan字典數(shù)據(jù)插入到mongo中。右側(cè)就是一個(gè)規(guī)則實(shí)現(xiàn)的樣例，就是基于mongo的查詢語(yǔ)句。后面我們會(huì)可看到一個(gè)詳細(xì)的示例。

7、平臺(tái)實(shí)現(xiàn)

規(guī)則實(shí)現(xiàn)

這里以“大表全表掃描”規(guī)則為例，進(jìn)行說(shuō)明。上面是在Oracle中的數(shù)據(jù)字典保存的執(zhí)行計(jì)劃，下面是存在Mongo中的?？梢?jiàn)，就是完全復(fù)制下來(lái)的。

基于這樣的結(jié)構(gòu)，如何實(shí)現(xiàn)規(guī)則過(guò)濾呢？其實(shí)就是通過(guò)mongo中的find語(yǔ)句實(shí)現(xiàn)的。下面具體解讀下這個(gè)語(yǔ)句的執(zhí)行步驟。

• 最上面的find()部分，是用來(lái)過(guò)濾執(zhí)行計(jì)劃的。將滿足指定用戶、時(shí)間范圍、訪問(wèn)路徑(“TABLE ACCESS”+”FULL”)的執(zhí)行計(jì)劃篩選出來(lái)。

• 篩選出的部分，會(huì)關(guān)聯(lián)對(duì)象數(shù)據(jù)，將符合“大表”條件的部分篩選出來(lái)。大表規(guī)則是記錄數(shù)大于指定參數(shù)或者物理大小大于指定參數(shù)的。

• 取得的結(jié)果，將保存期sql_id、plan_hash_value、object_name信息返回。這三個(gè)信息將分別用于后續(xù)提取SQL語(yǔ)句信息、執(zhí)行計(jì)劃信息、關(guān)聯(lián)對(duì)象信息使用。

• 取得的全部結(jié)果集，將按照先前設(shè)定的扣分原則，統(tǒng)計(jì)扣分。

• 提取到的三部分信息+扣分信息，將作為結(jié)果返回，并在前端展示。

規(guī)則實(shí)現(xiàn)（執(zhí)行計(jì)劃）

這部分是MySQL中實(shí)現(xiàn)層次結(jié)果存儲(chǔ)的一個(gè)實(shí)例。

第一個(gè)圖展示的是原始的執(zhí)行計(jì)劃。

第二個(gè)圖是代碼實(shí)現(xiàn)的摘要。

第三個(gè)圖是真正保存在庫(kù)中的樣子。核心部分就是對(duì)item_level的生成。

規(guī)則定義（文本級(jí)）

第三類(lèi)規(guī)則是文本類(lèi)的規(guī)則，這是一類(lèi)與數(shù)據(jù)庫(kù)種類(lèi)無(wú)關(guān)、描述SQL語(yǔ)句文本特征的規(guī)則。在實(shí)現(xiàn)上是采用文本正則匹配或程序方式進(jìn)行處理的。它的主要目的是規(guī)范開(kāi)發(fā)人員的SQL寫(xiě)法，避免復(fù)雜的、性能較差的、不規(guī)范的SQL寫(xiě)法。

規(guī)則實(shí)現(xiàn)（文本級(jí)）

這部分描述的是文本規(guī)則的實(shí)現(xiàn)方式。第一個(gè)示例bad_join，是一種簡(jiǎn)單規(guī)則，通過(guò)正則文本匹配實(shí)現(xiàn)。第二個(gè)示例sub_query，是通過(guò)程序判斷括號(hào)嵌套來(lái)完成對(duì)子查詢(或多級(jí)子查詢)的判斷。

規(guī)則定義（執(zhí)行特征級(jí)）

最后一類(lèi)規(guī)則是執(zhí)行特征類(lèi)的。這部分是與數(shù)據(jù)庫(kù)緊密關(guān)聯(lián)的，將符合一定執(zhí)行特征的語(yǔ)句篩選出來(lái)。這些語(yǔ)句不一定是低效的，可能只是未來(lái)考慮優(yōu)化的重點(diǎn)，或者說(shuō)優(yōu)化效益最高的一些語(yǔ)句。這里面主要都是一些對(duì)資源的消耗情況等。

8、系統(tǒng)管理

規(guī)則管理

后面通過(guò)一些界面展示，介紹下平臺(tái)的功能。

第一部分系統(tǒng)管理模塊中規(guī)則管理的部分。在這部分，可完成新增自有規(guī)則。其核心是規(guī)則實(shí)現(xiàn)部分，通過(guò)SQL語(yǔ)句、Mongo查詢語(yǔ)句、自定義Python文件的形式定義規(guī)則實(shí)現(xiàn)體。自定義規(guī)則的依據(jù)是現(xiàn)有抓取的數(shù)據(jù)源，定義者需要熟悉現(xiàn)有數(shù)據(jù)結(jié)構(gòu)及含義。目前尚不支持自定義抓取數(shù)據(jù)源。

對(duì)定義好的規(guī)則，可在此處完成規(guī)則修改。主要是對(duì)規(guī)則狀態(tài)、閥值、扣分項(xiàng)等進(jìn)行配置。

任務(wù)管理

在配置好規(guī)則后，可在此處完成任務(wù)發(fā)布的工作。

上面是規(guī)則任務(wù)發(fā)布的界面，在選擇數(shù)據(jù)源(ip、port、schema)后，選擇審核類(lèi)型及審核日期。目前審核數(shù)據(jù)源的定時(shí)策略還是以天為單位，因此日期不能選擇當(dāng)天。

當(dāng)任務(wù)發(fā)布后，可在任務(wù)結(jié)果查看界面觀察執(zhí)行情況。根據(jù)審核類(lèi)型、數(shù)據(jù)源對(duì)象多少、語(yǔ)句多少等，審核的時(shí)長(zhǎng)不定，一般是在5分鐘以內(nèi)。當(dāng)審核作業(yè)狀態(tài)為“成功”時(shí)，代表審核作業(yè)完成，可以查看或?qū)С鰧徍私Y(jié)果了。

9、結(jié)果展示

對(duì)象審核結(jié)果概覽

上圖是一個(gè)對(duì)象審核報(bào)告的示例。在報(bào)告的開(kāi)頭部分，是一個(gè)概覽頁(yè)面。它集中展示審核報(bào)告中各類(lèi)規(guī)則及扣分情況；并通過(guò)一個(gè)餅圖展示其占比情況。這便于我們集中精力先處理核心問(wèn)題。

在最上面，還可以觀察到有一個(gè)規(guī)則總分的顯示。這是我們將規(guī)則扣分按照百分制，折算后得到的一個(gè)分?jǐn)?shù)。分值越高，代表違反的情況越少，審核對(duì)象的質(zhì)量越高。引入“規(guī)則總分”這一項(xiàng)，在設(shè)計(jì)之初是有些爭(zhēng)議的，擔(dān)心有了這個(gè)指標(biāo)會(huì)比較打擊開(kāi)發(fā)人員的積極性，不利于平臺(tái)的推廣使用。這里有幾點(diǎn)，說(shuō)明一下。

• 引入規(guī)則總分，是為了數(shù)據(jù)化數(shù)據(jù)庫(kù)設(shè)計(jì)、開(kāi)發(fā)、運(yùn)行質(zhì)量。以往在很多優(yōu)化中，很難去量化優(yōu)化前后的效果。這里提供了一種手段去做前后對(duì)比?？赡苓@個(gè)方式不是太科學(xué)的，但是畢竟提供一種可量化的手段。

• 各業(yè)務(wù)系統(tǒng)差異較大，沒(méi)有必要做橫向?qū)Ρ取系統(tǒng)60分，B系統(tǒng)50分，不代表A的質(zhì)量就比B的質(zhì)量高。

• 單一系統(tǒng)可多做縱向?qū)Ρ龋磳?duì)比改造優(yōu)化前后的規(guī)則總分。可在一定程度上反映出系統(tǒng)質(zhì)量的變化。

• 規(guī)則總分，跟規(guī)則配置關(guān)系很大。如關(guān)閉規(guī)則或?qū)⑦`反規(guī)則的閥值調(diào)低，都會(huì)提高分?jǐn)?shù)。這要根據(jù)系統(tǒng)自身情況來(lái)確定。同一規(guī)則，對(duì)不同系統(tǒng)使用，其閥值是可以不同的。舉例而言，數(shù)據(jù)倉(cāng)庫(kù)類(lèi)的應(yīng)用，大表全部掃描就是一個(gè)比較正常的行為，可考慮關(guān)閉此規(guī)則或?qū)未芜`反閥值、總扣分上限降低。

對(duì)象審核結(jié)果明細(xì)

這部分是對(duì)象審核的明細(xì)部分，對(duì)應(yīng)每個(gè)規(guī)則其詳細(xì)情況，可在左側(cè)鏈接中進(jìn)一步查看對(duì)象信息。篇幅所限，不做展示了。

執(zhí)行計(jì)劃審核結(jié)果概覽

這部分執(zhí)行計(jì)劃的概覽展示，跟對(duì)象的情況類(lèi)似。也是每種規(guī)則的扣分情況。

執(zhí)行計(jì)劃審核結(jié)果明細(xì)

這部分是執(zhí)行計(jì)劃的明細(xì)部分。

展開(kāi)之后，可以看到違反每種規(guī)則的明細(xì)。上圖就是違反全表掃描的規(guī)則的明細(xì)部分。

在上面是一些通用的解決方案說(shuō)明。這里將可能觸發(fā)此類(lèi)規(guī)則的情況及解決方案進(jìn)行了說(shuō)明。相當(dāng)于一個(gè)小知識(shí)庫(kù)，便于開(kāi)發(fā)人員優(yōu)化。后面在平臺(tái)二期，會(huì)做更為精準(zhǔn)的優(yōu)化引擎部分，這部分還會(huì)展開(kāi)。

下面是每條違反的語(yǔ)句情況，我們可以看到語(yǔ)句文本、執(zhí)行計(jì)劃、關(guān)聯(lián)信息（例如此規(guī)則的大表名稱）等。還可以進(jìn)一步點(diǎn)開(kāi)語(yǔ)句，展開(kāi)信息。

這部分是針對(duì)每條SQL的信息，包括語(yǔ)句文本、執(zhí)行計(jì)劃、執(zhí)行特征、關(guān)聯(lián)對(duì)象統(tǒng)計(jì)信息等。DBA可從這些信息就可以做一些初步的優(yōu)化判斷工作。

此外，平臺(tái)也提供了導(dǎo)出功能?？蓪?dǎo)出為excel文件，供用戶下載查看。這里就展示了。

10、我們遇到的坑

在實(shí)際開(kāi)發(fā)過(guò)程中，碰到了很多問(wèn)題。我們這里簡(jiǎn)單介紹兩個(gè)，例如：

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ì)話。為了防止誤殺，可打個(gè)標(biāo)識(shí)“eXplAin format=json”，然后使用pt-kill識(shí)別eXplAin關(guān)鍵字。

11、推進(jìn)流程

此平臺(tái)在宜信公司運(yùn)行以來(lái)，為很多系統(tǒng)提供了審核報(bào)告，大大加快了數(shù)據(jù)庫(kù)結(jié)構(gòu)、SQL優(yōu)化的速度，減輕了DBA的日常工作壓力。在工作實(shí)施過(guò)程中，我們也摸索了一套推行方法。該平臺(tái)已開(kāi)源后，如有朋友使用，可參考實(shí)施。

收集信息階段

海量收集公司的數(shù)據(jù)庫(kù)系統(tǒng)的運(yùn)行情況，掌握第一手資料。快速了解各業(yè)務(wù)系統(tǒng)的質(zhì)量，做好試點(diǎn)選擇工作。

人工分析階段

重點(diǎn)系統(tǒng)，人工介入分析。根據(jù)規(guī)則審核中暴露出的核心問(wèn)題，“以點(diǎn)帶面”，有針對(duì)性的給出分析及優(yōu)化報(bào)告。

交流培訓(xùn)階段

主動(dòng)上門(mén)，跟開(kāi)發(fā)團(tuán)隊(duì)溝通交流報(bào)告情況。借分析報(bào)告的機(jī)會(huì)，可對(duì)開(kāi)發(fā)團(tuán)隊(duì)進(jìn)行必要的培訓(xùn)工作，結(jié)合他們身邊的案例，更具有說(shuō)服作用。

反饋改進(jìn)階段

落實(shí)交流的成果，督促其改進(jìn)。通過(guò)審核平臺(tái)定期反饋改進(jìn)質(zhì)量。有一定基礎(chǔ)的團(tuán)隊(duì)，可開(kāi)發(fā)平臺(tái)，供開(kāi)發(fā)人員自己使用。使SQL質(zhì)量問(wèn)題，不再僅僅是DBA的問(wèn)題，而和項(xiàng)目中的每個(gè)人都有關(guān)系。

內(nèi)容來(lái)源：宜信技術(shù)學(xué)院