mysql自動化完成SQL審核

sql審核主要完成兩方面的目的.

1、避免效能太差的sql進入生產系統,導致整體效能降低

2、檢查開發設計的索引是否合理,是否需要添加索引

第一點是SQL審核最核心的地方,避免亂七八糟的sql影響線上效能,甚至導致線上系統崩潰.

第二點是屬於建模的範疇,要解決建模的最好辦法是DBA參與項目前期審核,由DBA建模,如果DBA人力資源不足,那麼就定期由DBA對開發人員進行培訓.然後發現建模太爛的就扣KPI.

現在很多公司都是人肉來完成SQL審核的,人肉審核對dba的要求較高,需要懂一些代碼,另外是費時費力,畢竟一般公司幾十個開發,對應一個DBA，而且DBA還要幹很多其他的事情.

如何將DBA從人肉SQL審核中解放出來呢?

思路其實很簡單:

1、擷取程式要執行的SQL

2、對要執行的SQL做分析,可以加各種分析條件來判斷這個SQL是否可以自動審核通過,未通過審核的需要人工處理.

3、配合後期的慢查詢日誌分析系統完成長期的監控.

開源的解決方案主要有淘寶丹臣sqlautoreview系統.可以在github上搜尋到.

但是這個系統主要是基於java sqlmapfile.xml解決自動建立索引的問題,對來源資料有要求,並且是通過解析SQL結構來假設SQL的執行計畫,不是特別準確,並且不能夠很好的區分新sql還是老sql.

所以產生了一個新的方案:

1、為所有的執行過的sql產生一個figerprint

2、基於慢查詢提供的資料,加上explain 提供的資料來判斷這個sql的效能是否可接受,或者可最佳化.

3、自動審核通過效能可接受的部分,給DBA展示效能較差的sql,然後進行最佳化.

方案的優點在於:

基於使用者真正執行的SQL,並且可以觀察SQL執行頻率.

基於MySQL真正的執行計畫和執行結果,分析更準確.

每個SQL都有一個fingerprint,只需要增量處理新加的SQL,效率和效能提高.

基於Box anemometer二次開發,讓慢查詢和sql審核同平台,增加工具整合性,提高使用者體驗(DBA和開發人員)。

方案實施:

既然咱是DBA，肯定會有更DBA的思維方式.基於現有軟體二次開發完成,減少開發成本,整合管理平台.

基於Box anemometer.安裝Box anemometer

Box anemometer是一款B/S架構,圖形化的MySQL慢查詢分析工具.功能強大易用,設計簡單直接.anemometer是基於pt-query-digest的二次封裝得來.

核心處理流程:

mysql node–>計劃任務通過pt-query-digest收集慢查詢資訊–>結果寫入到資料庫中–>anemometer按條件去展示慢查詢的結果,並且提供了圖形化和趨勢分布圖等功能.

所以anemometer已經幫我們完成了資料收集,包括每個sql的fingerprint資訊,以及相關的資訊,我們在測試環境,基於anemometer,將long_query_time設定為0,就可以收集到所以的SQL及相關資訊.

在我們收集到所有SQL以後,我們就要來分析這個SQL是否可以自動審核通過.這裡開始我們就要定製了.

定製內容如下:

一、

設定一個單獨的datasources,可以命名為audit_sql.

這個datasources裡面只放置開發環境或者測試環境的慢查詢(你要做sql審核基於哪個環境),將此環境的long_query_time設定為0,接收所有的sql查詢.

二、修改anemometer

ALTER TABLE `global_query_review` ADD audit_status VARCHAR(255) NOT NULL DEFAULT ‘refuse’ comment ‘sql審計的狀態 refuse未通過 pass審核通過’;

修改PHP代碼.

在report模組的where條件中增加一個Aduit Status的選項框,可以過濾audit_status的狀態

在show_query模組中增加一個Audit Status的選項框,可以人工設定audit_status的狀態

三、增加兩個額外的指令碼,准即時的分析audit_status為refuse的sql,如果sql的滿足自動審核通過的條件,那麼就設定audit_status為pass,表示自動審核通過.

自動審核未通過的sql,由DBA人工在anemometer上檢索和處理.

這裡就涉及到一個自動審核通過的演算法:

演算法分兩種.

第一種是准即時,也就是可以幾分鐘或者一個小時運行一次,主要是根據每個sql的執行效率判斷是否pass.

對應的指令碼名字叫做:audit_sql.py

第二種是一天一次,弱化執行效率判斷,增加一天執行的頻率判斷.

對應的指令碼名字叫做:audit_sql_day.py

各家根據自己的實際情況調整或者最佳化這兩個指令碼.

至此,你已經可以讓99%以上的代碼自動審核通過了,審核不通過的代碼你可以讓開發自己來tracking也可以主動推給開發.

對於才搭建的環境,可能會有一些亂七八糟的sql,不過使用一段時間穩定以後,異常的sql指紋都有了,那麼每天產生的sql指紋就比較少了,而這部分SQL指紋也就是程式員編寫新的代碼產生的.

二次修改過的Box anemometer代碼和對應的python指令碼都在我的GITHUB上.

https://github.com/ISADBA/anemometerAudit_SQL