Oracle SQL 自動化審覈工具的實現
本文由 dbaplus 社羣授權轉載。
一、背景
我們客戶現場的 Oracle 運維團隊需要對開發團隊提交上來的 Oracle 數據庫 SQL 腳本進行評審。衆所周知,這個活兒看起來高大上,實際上單靠人工檢查的話,耗時費事、效率低下且機械重複,是很難長期實施的。
根據 SRE 以軟件工程方法解決運維問題的邏輯,我們當然需要使用自動化的工具來解決這個問題。
二、自動化審查
首先,Oracle 運維團隊將 SQL 評審經驗總結爲上百個評審規則,例如:
- 所有新建對象的 SQL 都需要在對象名的前面加上用戶名;
- 創建 SEQUENCE 的 SQL 語句,需要指定 CACHE 值不小於 200;
- delete 和 update 等 DML 語句,必須帶 where 條件;
- ……
用這些評審規則去審覈一個個 SQL,仍然是非常苦逼的活兒,我們需要一個自動化的工具來實現。爲了不重複製造輪子,最好的方法當然是找一個開源的工具進行二次開發,經過團隊討論和反覆驗證後,最終採用了開源的 SOAR 進行二次開發實現。
SOAR 工具原來是基於 MySQL 數據庫進行開發的,可客戶現場 SQL 檢查是基於 Oracle 的 SQL 腳本。儘管 Oracle 和 MySQL 在語法上有明顯的差別,但上述的分析框架和邏輯是可以重用的,我們主要是通過屏蔽 SOAR 自帶的 SQL 檢查規則,通過添加自定義規則實現。
1、SOAR 組成
SOAR,即 SQL Optimizer And Rewriter,是一款 SQL 智能優化與改寫工具,由小米運維 DBA 團隊出品。SOAR 主要由語法解析器、集成環境、優化建議、重寫邏輯、工具集五大模塊組成。
2、與其他工具對比
3、功能特性
- 跨平臺支持(支持 Linux、Mac 環境,Windows 環境理論上也支持,不過未全面測試);
- 支持基於啓發式算法的語句優化;
- 支持複雜查詢的多列索引優化(UPDATE, INSERT, DELETE, SELECT);
- 支持 EXPLAIN 信息豐富解讀;
- 支持 SQL 指紋、壓縮和美化;
- 支持同一張表多條 ALTER 請求合併;
- 支持自定義規則的 SQL 改寫。
三、工具框架安裝
操作系統版本:CentOS 7.2。
1、安裝 Go 環境
這裏使用二進制包來安裝,下載二進制安裝包:
複製代碼
$ wget https://dl.google.com/go/go1.10.2.linux-amd64.tar.gz $ tar zxvf go1.10.2.linux-amd64.tar.gz
配置環境變量:
複製代碼
#go 的安裝目錄 exportGOROOT= 解壓的 go 的目錄 exportGOPATH= 解壓的 go 的目錄 exportPATH=$PATH:$GOROOT/bin
查看 Go 版本:
2、安裝 Git 客戶端
使用具有安裝權限的用戶執行以下命令:
複製代碼
$ yum -yinstallgit
查看 Git 客戶端版本:
3、下載 SOAR 源碼並編譯
新建 workspace 目錄:
複製代碼
$mkdir workspace $cdworkspace
下載 SOAR 源碼並編譯:
複製代碼
$goget-d github.com/XiaoMi/soar $cd${GOPATH}/src/github.com/XiaoMi/soar &&make
安裝驗證:
複製代碼
$cd${GOPATH}/bin $echo'select * from film'| ./soar
四、規則開發
1、下載 goland IDE
2、打開下載的 soar 源代碼工程
複製代碼
$ gitclonehttps://github.com/XiaoMi/soar.git
3、基於 SOAR 的啓發式檢查規則進行二次開發,主要增加規則代碼:
並在配置文件中屏蔽 SOAR 自帶默認檢查規則:
將 SOAR 可執行文件以及 soar.yaml 放到需要執行的目錄 soar_path:
複製代碼
$cd$soar_path $./soar -config ./soar.yaml -query 待評審和檢查的文件絕對路徑
五、圖形化界面
我們還針對 SOAR 提供的 web 圖形化界面的小工具,進行了定製改造。讓這款小工具可以進一步開放給開發團隊的同事使用。大致步驟如下:
1、安裝 Python
複製代碼
$ yuminstallpython36 python36-pip $ pipinstallFlask $ pipinstallpymysql $ pipinstallpycryptodome
若 Crypto 模塊找不到, 則需要在 Python 的依賴庫目錄 Lib\site-packages 中將 crypto 重命名爲 Crypto。
2、下載 soar-web 並啓動
複製代碼
$ wget https://codeload.github.com/xiyangxixian/soar-web/zip/master-Osoar-web-master.zip $ unzip soar-web.zip $ cd soar-web-matster
將上述二次開發的 SOAR 執行文件以及 soar.yaml 文件上傳到指定目錄下:
並修改 core/common.py 文件:
最終開放給開發團隊效果如下圖:
六、小結
至此,這個 Oracle SQL 審覈的小工具就開發完成了。通過一些簡易的配置和開發實現 90% Oracle SQL 的自動化審覈,極大簡化了現場 DBA 的工作量。當然,還可以實現更多種類數據庫的支持,留待更多 DBA 同仁去探索一番。
作者介紹:
梁銘圖,新炬網絡首席架構師,十多年數據庫運維、數據庫設計、數據治理以及系統規劃建設經驗,擁有 Oracle OCM、Togaf 企業架構師(鑑定級)、IBM CATE 等認證,曾獲 dbaplus 年度 MVP 以及華爲雲 MVP 等榮譽,並參與數據資產管理國家標準的編寫工作。在數據庫運維管理和架構設計、運維體系規劃、數據資產管理方面有深入研究。