開源|效能優化利器：資料庫稽核平臺Themis的選型與實踐

宜信技術學院發表於2019-06-28

作者：韓鋒
出處：DBAplus社群分享

Themis開源地址：https://github.com/CreditEaseDBA

擴充閱讀：宜信開源|資料庫稽核軟體Themis的規則解析與部署攻略

【技術沙龍002期】資料中臺：宜信敏捷資料中臺建設實踐|宜信技術沙龍將於5月23日晚8點線上直播，點選報名

一、面臨的挑戰

1、運維規模及種類

我相信，這也是很多公司、很多DBA正在面臨或未來都會面臨的一些問題。正是存在問題，促使我們考慮引入資料庫稽核平臺。

首先是運維規模與人力資源之間的矛盾。從我們的情況來看，運維了包括Oracle、MySQL、MongoDB、Redis四類資料庫，資料庫規模幾十套，支援公司千餘名開發人員及上百套業務系統。也許有的朋友會問，從運維規模上看，並不是很大。

的確，與很多網際網路公司相比，資料庫數十套的估摸並不是太大；但與網際網路類公司不同，類似宜信這類金融類公司對資料庫的依賴性更大，大量的應用是重資料庫類的，且其使用複雜程度也遠比網際網路類的複雜。DBA除了日常運維（這部分我們也在通過自研平臺提升運維效率）外，還需要有大量精力應對資料庫設計、開發、優化類的工作。當面對大量的開發團隊需要服務時，這個矛盾就更加凸顯出來。

2、案例

結構設計

第二個挑戰，是資料庫設計、開發質量參差不齊的問題。 上圖就展示了一個結構設計問題。某核心系統的核心表，在這個系統執行的SQL中，28%都是跟這個物件有關的。當我們分析其結構時，發現了很多的問題：

表的規模很大，從設計之初就沒有考慮到拆分邏輯（例如分庫、分表、分割槽設計），也沒有必要的資料庫清理、歸檔策略。
表存在100多個欄位，欄位數很多且不同欄位使用特徵也不一致，沒有考慮到必要拆表設計。
表有13個索引，數目過多。表的索引過度，勢必會影響其DML效率。
還存在一個索引，在持續監控中發現，其從未被使用過。顯然這是一個“多餘”的索引。
還有兩個欄位存在重複索引的現象，這也說明在建立索引之初是比較隨意的。
單個記錄定義長度為5800多個位元組，但實際其平均儲存長度只有不到400位元組，最大長度也不長。
分析其欄位內容，還發現有3個欄位型別定義異常。即沒有使用應有的型別儲存資料，例如使用數字型別儲存日期。

綜上所述，這個表設計的問題還有很多，而且這個表非常重要，大量語句訪問和其相關。

SQL語句

上圖展示的是一個語句執行效率的問題。從字面可見，兩個表做關聯查詢，但在指定條件時沒有指定關聯條件。在下面的執行計劃中可見，資料庫採用了笛卡爾積的方式執行。從後面的成本、估算時間等可見，這是一個多麼“巨大”的SQL。其線上上執行的影響，可想而知。

也許有人會說，這是一個人為失誤，一般不會發生。但我要說的是，第一，人為失誤無法避免，誰也不能保證寫出SQL的執行質量；第二，開發人員對資料庫的理解不同，很難保證寫出的SQL都是高效的；第三，開發人員面臨大量業務需求，經常處理趕工狀態，很難有更多的精力放在優化上面。這因為有這些問題，線上語句執行質量就成了DBA經常面臨的挑戰之一。

3、重心轉移

這是一張很經典的圖，它描述了和資料庫相關工作的職能劃分。作為DBA，除了面臨以上挑戰外，從資料庫工作發展階段及自身發展需求來看，也面臨一個重心的轉移：原有傳統DBA的運維職能逐步被弱化，大量的工具、平臺的湧現及資料庫自我運維能力的提升，簡化DBA的工作；緊隨而來的資料庫架構、結構設計、SQL質量優化逐步成為重點；再往上層的資料治理、建模等工作也越來越受到一些公司的重視。由此可見，DBA未來工作的中心也逐步上移。對中間資料邏輯結構部分，也需要一些工具、平臺更好地支撐DBA的工作。

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

從DBA日常工作來看，傳統運維工作還是佔了較大的比重，而架構優化類則相對較少。通過引入這一平臺，可以幫助DBA更方便地開展架構、優化類工作。
公司使用了較多的商業產品，而開源則使用較少。從公司長遠戰略來看，開源產品的使用會越來越多。從功能角度來看，商業產品相較於開源產品是有優勢的。基於開源產品的軟體開發，對開發者自身技術技能要求更高。希望通過引入這一產品，可以更容易完成這一轉型過程。
沒有平臺之前，DBA還是大量通過手工方式設計、優化資料庫，其效率十分低下。特別是面對眾多產品線、眾多開發團隊時，往往感覺力不從心。

公司自有團隊人員上，還是以初中級為主，中高階人員相對較少。如何快速提升整體設計、優化能力，保證統一的優化效果成為擺在面前的問題。

正是有了上述多種的不平衡，促使我們考慮引入工具、平臺去解決資料庫質量問題。

我剛來到公司時，看到公司的這些問題，也曾考慮通過制度、規範的形式進行解決。一開始就著手製定了很多的規範，然後在各個部門去培訓、宣講。這種方式執行一段時間後，暴露出一些問題：

整體效果改善並不明顯。實施效果取決於各個部門的重視程度及員工的個人能力。
規範落地效果無法度量，也很難做到量化分析。往往只能通過上線執行結果來直觀感知。
缺乏長期有效的跟蹤機制。無法對具體某個系統長期跟蹤其執行質量。
從DBA的角度來看，面對大量的系統，很難依據每個規範，詳細稽核其結構設計、SQL執行質量。

面臨上述這些挑戰、現存的各種問題，該如何解決？

經過討論，最後大家一致認為，引入資料庫稽核平臺，可以幫助解決上面所述問題。

二、平臺的選型

1、業內做法

在專案之初，我考察了業內其它企業是如何資料庫稽核的，大致可分為三個思路：

第一類，是以BAT公司為代表的網際網路類公司。它們通過自研的SQL引擎，可實現成本分析、自動稽核、訪問分流、限流等，可做到事前稽核、自動稽核。但技術難度較大，公司現有技術能力明顯不足。

第二類，是通過自研工具收集DB執行情況，根據事前定義規則進行稽核，結合人工操作來完成整個稽核流程。這種方案只能做到事後稽核，但技術難度較小，靈活度很大。其核心就是規則集的制定，可根據情況靈活擴充套件。

第三類，是一些商業產品，實現思路類似第二類，但是加上一些自主分析能力，功能更為強大，但仍需人工介入處理且需要不小資金投入。而且考察幾款商業產品，沒有能完全滿足所需功能的。

綜合上面幾類做法，最終確定我們採用“工具+人工稽核”的方式，自研自己的稽核平臺。

2、我們的選擇——自研

在啟動研發這一平臺之初，我們就在團隊內部達成了一些共識。

DBA需要扭轉傳統運維的思想，每個人都參與到平臺開發過程中。
過去我們積累的一些內容（例如前期制定的規範）可以作為知識庫沉澱下來，並標準化，這些為後期規則的制定做好了鋪墊。
在平臺推進中，從最簡單的部分入手，開發好的就上線實施，觀察效果；根據實施效果，不斷修正後面的工作。
結合我們自身的特點，定製目標；對於有些較複雜的部分，可果斷延後甚至放棄。
參考其它公司或商業產品的設計思想，大膽引入。

三、稽核平臺實踐

下面來看看，稽核平臺的基本功能及實現原理及方法，這部分是本次分享的重點。

1、平臺定位

在專案之初，我們就平臺的定位做了描述：

平臺的核心能力是快速發現資料庫設計、SQL質量問題。
平臺只做事後稽核，自主優化部分放在二期實現。當然在專案設計階段引入這個，也可以起到一部分事前稽核的功能。
通過Web介面完成全部工作，主要使用者是DBA和有一定資料庫基礎的研發人員。
可針對某個使用者稽核，可稽核包括資料結構、SQL文字、SQL執行特徵、SQL執行計劃等多個維度。
稽核結果通過Web頁面或匯出檔案的形式提供。
平臺需支援公司主流的Oracle、MySQL，其它資料庫放在二期實現。
儘量提供靈活定製的能力，便於日後擴充套件功能。

2、平臺使用者

作為平臺的兩類主要使用方，研發人員和DBA都可以從平臺中受益。

對於研發人員而言，只用這平臺可方便定位問題，及時進行修改；此外通過對規則的掌握，也可以指導他們設計開發工作。
對於DBA而言，可快速掌握多個系統的整體情況，批量篩選出低效SQL，並可通過平臺提供的資訊快速診斷一般性問題。

3、實現原理

整個平臺的基本實現原理很簡單，就是將我們的稽核物件（目前支援四種），通過規則集進行篩選。符合規則的稽核物件，都是疑似有問題的。平臺會將這些問題及關聯資訊提供出來，供人工甄別使用。由此可見，平臺的功能強大與否，主要取決於規則集的豐富程度。平臺也提供了部分擴充套件能力，方便擴充套件規則集。

4、平臺設計

稽核物件

在開始介紹平臺實現之前，再來熟悉下“稽核物件”這個概念。目前我們支援的有四類物件，分別說明一下。

物件級。這裡所說的物件就是指資料庫物件，常見的表、分割槽、索引、檢視、觸發器等等。典型規則，例如大表未分割槽等。
語句級。這裡所說的語句級，實際是指SQL語句文字本身。典型規則，例如多表關聯。
執行計劃級。這裡是指資料庫中SQL的執行計劃。典型規則，例如大表全表掃描。
執行特徵級。這裡是指語句在資料庫上的真實執行情況。典型規則，例如掃描塊數與返回記錄比例過低。

需要說明一下，這四類稽核物件中，後三種必須在系統上線執行後才會抓取到，第一種可以在只有資料結構的情況下執行(個別規則還需要有資料)。

此外，上述規則中，除了第二類為通用規則外，其他都與具體資料庫相關。即每種的資料庫，都有自己不同的規則。

架構簡圖

這裡畫出是系統架構框架簡圖，我簡單說明一下。

圖中的方框部分，為平臺的主要模組。底色不同的模組，表示當前的進度狀態不同。虛線代表資料流，實線代表控制流。其核心為這幾個模組：

資料採集模組。它是負責從資料來源抓取稽核需要的基礎資料。目前支援從Oracle、MySQL抓取。
OBJ/SQL儲存庫。這是系統的共同儲存部分，採集的資料和處理過程中的中間資料、結果資料都儲存在這裡。其核心資料分為物件類和SQL類。物理是採用的MongoDB。
核心管理模組。圖中右側虛線部分包含的兩個模組：SQL管理和OBJ管理就是這部分。它主要是完成物件的全生命週期管理。目前只做了簡單的物件過濾功能，因此還是白色底色，核心的功能尚未完成。
稽核規則和稽核引擎模組。這部分是平臺一期的核心元件。稽核規則模組是完成規則的定義、配置工作。稽核引擎模組是完成具體規則的稽核執行部分。
優化規則和優化引擎模組。這部分是平臺二期的核心元件。目前尚未開發，因此為白色底色。
系統管理模組。這部分是完成平臺基礎功能，例如任務排程、空間管理、稽核報告生成、匯出等功能。

流程圖

讓我們從處理流程的角度，看看平臺的整體處理過程。

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

初始化規則。平臺本身內建了很多規則，在這一過程中到匯入到配置庫中。
新增規則。平臺本身提供了一定的擴充套件能力，可以依據規範新增一條規則。
修改規則。可以根據自身情況開啟或關閉規則。對於每條規則，還內建了一些引數，也可在此處修改。此外，針對違反規則的情況，還可以設定扣分方法（例如違反一次扣幾分、最多可扣幾分）等。
規則本身及相關引數、配置資訊等都會儲存在配置庫中。

2) “任務管理”部分，這是後臺管理的一個部分，主要完成與任務相關的工作。系統中的大多數互動都是通過作業非同步完成的。其後臺是通過celery+flower實現的。

3) “資料採集”部分，這部分是通過任務排程定時出發採集作業完成，也有少量部分是實時查詢線上庫完成的。採集的結果儲存在資料庫中，供後續分析部分呼叫。

4) “規則解析”部分，這部分是由使用者通過介面觸發，任務排程模組會啟動一個後臺非同步任務完成解析工作。之所以設計為非同步完成，主要是稽核工作可能時間較長（特別是選擇稽核類別較多、稽核物件很多、開啟的稽核規則較多）的情況。稽核結果會儲存在資料庫中。

5) “任務檢視、匯出”部分，在使用者發起稽核任務後，可在此部分檢視進度（處於稽核中、還是稽核完成）。當稽核完成後，可選擇稽核任務，瀏覽稽核結果或選擇匯出均可。如果是選擇匯出的話，會生成非同步後臺作業生成檔案，放置在下載伺服器上。

以上就是整個稽核的大體流程。後續將看到各部分的詳細資訊。

模組劃分

總結一下，平臺主要是由上述四個模組組成：資料採集、規則解析、系統管理、結果展示。後面將針對不同模組的實現，進行詳細說明。

5、資料採集

採集內容

先來看看資料採集模組。從表格可見，兩種型別資料庫的採集內容不同。

Oracle提供了較為豐富的資訊，需要的基本都可採集到；MySQL功能相對能採集到的資訊較少。

表格中的“對號+星號”，表示非定時作業完成，而是後面實時回庫抓取的。下面簡單說下，各部分的採集內容。

物件級，採集了物件統計資訊、儲存特徵、結構資訊、訪問特徵。
SQL級，採集了SQL文字，執行計劃、快取遊標、繫結變數、執行特徵等。

這些資訊都將作為後面稽核的依據。

採集原理

下面簡單介紹下采集的與原理：

Oracle部分，是通過定時作業採集的AWR資料，然後轉儲到一套MongoDB中。這裡跟有些類似產品不同，沒有直接採集記憶體中的資料，而是取自離線的資料。其目的是儘量減少對線上執行的影響。Oracle提供的功能比較豐富，通過對AWR及資料字典的訪問，基本就可以獲得全部的資料。
MySQL部分，情況就要複雜一些，原因是其功能沒有那麼豐富。多類資料是通過不同源來獲取。SQL文字類及執行特徵類的，是通過pt工具分析慢查詢日誌定時入到Anemometer平臺庫，然後從此庫傳入MongoDB。其它類資訊（包括資料字典類、執行計劃類等）是在需要時通過實時回庫查詢的。為了防止影響主庫，一般是通過路由到從庫上執行獲得的。

6、規則解析

概要說明

下面介紹整個系統最為核心的部分—規則解析模組，它所完成的功能是依據定義規則，稽核採集的資料，篩選出違反規則的資料。對篩選出的資料進行計分，並記錄下來供後續生成稽核報告使用。同時還會記錄附加資訊，用於輔助進行一些判斷工作。

這裡有個核心的概念—“規則”。後面可以看到一個內建規則的定義，大家就會比較清楚了。從分類來看，可大致分為以下幾種。

從資料庫型別角度來區分，規則可分為Oracle、MySQL。不是所有規則都區分資料庫，文字類的規則就不區分。
從複雜程度來區分，規則可分為簡單規則和複雜規則。這裡所說的簡單和複雜，實際是指規則稽核的實現部分。簡單規則是可以描述為MongoDB或關聯式資料庫的一組查詢語句；而複雜規則是需要在外部通過程式體實現的。
從稽核物件角度來區分，規則可分為物件類、文字類、執行計劃類和執行特徵類。下面會針對每類稽核物件，分別做說明。

規則定義

這是一個規則體的宣告物件，我說明一下各欄位含義，大家也可對規則有個清晰的認識。

db_type：規則的資料庫類別，支援Oracle、MySQL。
input_parms：輸入引數。規則是可以定義多個輸出引數，這是一個引數列表，每個引數自身又是一個字典類，描述引數各種資訊。
output_parms：輸出引數。類似上面的輸入引數，也是一個字典物件列表。描述了根據規則返回資訊結構。
rule_complexity：規則是複雜規則還是簡單規則。如果是簡單規則，則直接取rule_cmd內容作為規則稽核的實現。如果是複雜規則，則是從外部定義的rule_name命令指令碼中獲得規則實現。
rule_cmd：規則的實現部分。規則可能是mongodb的查詢語句、可能是一個正規表示式，具體取決於rule_type。
rule_desc：規則描述，僅供顯示。
rule_name：規則名稱。是規則的唯一標識，全域性唯一。
rule_status：規則狀態，ON或是OFF。對於關閉的規則，在稽核時會忽略它。
rule_summary：一個待廢棄的欄位，意義同rule_desc。
rule_text：規則型別，分為物件、文字、執行計劃、執行特徵四類。圖中的示例標識一個文字型別的規則，rule_cmd是正規表示式。
solution：觸發此規則的優化建議。
weight：權重，即單次違反規則的扣分制。
max_score：扣分上限，為了避免違反一個規則，產生過大影響，設定此引數。

規則定義（物件級）

先來看第一類規則—物件規則。這是針對資料庫物件設定的一組規則。上面表格，顯示了一些示例。常見的物件，諸如表、分割槽、索引、欄位、函式、儲存過程、觸發器、約束、序列等都是稽核的物件。以表為例，內建了很多規則。

例如：第一個的“大表過多”。表示一個資料庫中的大表個數超過規則定義閥值。這裡的大表又是通過規則輸入引數來確定，引數包括表記錄數、表物理尺寸。整體描述這個規則就是“資料庫中超過指定尺寸或指定記錄數的表的個數超過規定閥值，則觸發稽核規則”。其它物件的規則也類似。

規則實現（物件級）

物件規則的實現部分，比較簡單。除個別規則外，基本都是對資料字典資訊進行查詢，然後依據規則定義進行判斷。上面示例就是對索引的一個規則實現中，查詢資料字典資訊。

規則定義（執行計劃級）

第二類規則是執行計劃類的規則，它也劃分為若干類別。例如訪問路徑類、表間關聯類、型別轉換類、繫結變數類等。

以最為常見的的訪問路徑類為例，進行說明下。如最為常見的一個規則“大表掃描”。它表示的是SQL語句的執行中，執行了對大表的訪問，並且訪問的路徑是採用全表掃描的方式。這個規則的輸入引數，包含了對大表的定義（物理大小或記錄數）；輸出部分則包括了表名、表大小及附加資訊（包括整個執行計劃、指定大表的統計資訊等內容）。

這類規則針對的資料來源，是從線上資料庫中抓取的。Oracle部分是直接從AWR中按時間段提取的，MySQL部分是使用explain命令返查資料庫得到的。

資訊儲存格式

在這裡特別說明一下，在儲存執行計劃的時候，使用了MongoDB這種文件性資料庫。目的就是利用其schemaless特性，方便相容不同資料庫、不同版本執行計劃的差異。都可以儲存在一個集合中，後續的規則稽核也是利用的mongo中的查詢語句實現的。這也是最初引入mongo的初衷，後續也將其它類資訊放入庫中。現在整個稽核平臺，除了pt工具接入的部分使用MySQL外，其餘都在MongoDB中。此外，MySQL庫可以直接輸出json格式的執行計劃，很方便就入庫了；Oracle部分也組成json格式入庫。

規則實現（執行計劃）

左邊就是一個Oracle的執行計劃儲存在MongoDB中的樣子。其實就是將sqlplan字典資料插入到mongo中。右側就是一個規則實現的樣例，就是基於mongo的查詢語句。後面我們會可看到一個詳細的示例。

7、平臺實現

規則實現

這裡以“大表全表掃描”規則為例，進行說明。上面是在Oracle中的資料字典儲存的執行計劃，下面是存在Mongo中的。可見，就是完全複製下來的。

基於這樣的結構，如何實現規則過濾呢？其實就是通過mongo中的find語句實現的。下面具體解讀下這個語句的執行步驟。

最上面的find()部分，是用來過濾執行計劃的。將滿足指定使用者、時間範圍、訪問路徑(“TABLE ACCESS”+”FULL”)的執行計劃篩選出來。
篩選出的部分，會關聯物件資料，將符合“大表”條件的部分篩選出來。大表規則是記錄數大於指定引數或者物理大小大於指定引數的。
取得的結果，將儲存期sql_id、plan_hash_value、object_name資訊返回。這三個資訊將分別用於後續提取SQL語句資訊、執行計劃資訊、關聯物件資訊使用。
取得的全部結果集，將按照先前設定的扣分原則，統計扣分。
提取到的三部分資訊+扣分資訊，將作為結果返回，並在前端展示。

規則實現（執行計劃）

這部分是MySQL中實現層次結果儲存的一個例項。

第一個圖展示的是原始的執行計劃。

第二個圖是程式碼實現的摘要。

第三個圖是真正儲存在庫中的樣子。核心部分就是對item_level的生成。

規則定義（文字級）

第三類規則是文字類的規則，這是一類與資料庫種類無關、描述SQL語句文字特徵的規則。在實現上是採用文字正則匹配或程式方式進行處理的。它的主要目的是規範開發人員的SQL寫法，避免複雜的、效能較差的、不規範的SQL寫法。

規則實現（文字級）

這部分描述的是文字規則的實現方式。第一個示例bad_join，是一種簡單規則，通過正則文字匹配實現。第二個示例sub_query，是通過程式判斷括號巢狀來完成對子查詢(或多級子查詢)的判斷。

規則定義（執行特徵級）

最後一類規則是執行特徵類的。這部分是與資料庫緊密關聯的，將符合一定執行特徵的語句篩選出來。這些語句不一定是低效的，可能只是未來考慮優化的重點，或者說優化效益最高的一些語句。這裡面主要都是一些對資源的消耗情況等。

8、系統管理

規則管理

後面通過一些介面展示，介紹下平臺的功能。

第一部分系統管理模組中規則管理的部分。在這部分，可完成新增自有規則。其核心是規則實現部分，通過SQL語句、Mongo查詢語句、自定義Python檔案的形式定義規則實現體。自定義規則的依據是現有抓取的資料來源，定義者需要熟悉現有資料結構及含義。目前尚不支援自定義抓取資料來源。

對定義好的規則，可在此處完成規則修改。主要是對規則狀態、閥值、扣分項等進行配置。

任務管理

在配置好規則後，可在此處完成任務釋出的工作。

上面是規則任務釋出的介面，在選擇資料來源(ip、port、schema)後，選擇稽核型別及稽核日期。目前稽核資料來源的定時策略還是以天為單位，因此日期不能選擇當天。

當任務釋出後，可在任務結果檢視介面觀察執行情況。根據稽核型別、資料來源物件多少、語句多少等，稽核的時長不定，一般是在5分鐘以內。當稽核作業狀態為“成功”時，代表稽核作業完成，可以檢視或匯出稽核結果了。

9、結果展示

物件稽核結果概覽

上圖是一個物件稽核報告的示例。在報告的開頭部分，是一個概覽頁面。它集中展示稽核報告中各類規則及扣分情況；並通過一個餅圖展示其佔比情況。這便於我們集中精力先處理核心問題。

在最上面，還可以觀察到有一個規則總分的顯示。這是我們將規則扣分按照百分制，折算後得到的一個分數。分值越高，代表違反的情況越少，稽核物件的質量越高。引入“規則總分”這一項，在設計之初是有些爭議的，擔心有了這個指標會比較打擊開發人員的積極性，不利於平臺的推廣使用。這裡有幾點，說明一下。

引入規則總分，是為了資料化資料庫設計、開發、執行質量。以往在很多優化中，很難去量化優化前後的效果。這裡提供了一種手段去做前後對比。可能這個方式不是太科學的，但是畢竟提供一種可量化的手段。
各業務系統差異較大，沒有必要做橫向對比。A系統60分，B系統50分，不代表A的質量就比B的質量高。
單一系統可多做縱向對比，即對比改造優化前後的規則總分。可在一定程度上反映出系統質量的變化。
規則總分，跟規則配置關係很大。如關閉規則或將違反規則的閥值調低，都會提高分數。這要根據系統自身情況來確定。同一規則，對不同系統使用，其閥值是可以不同的。舉例而言，資料倉儲類的應用，大表全部掃描就是一個比較正常的行為，可考慮關閉此規則或將單次違反閥值、總扣分上限降低。

物件稽核結果明細