SQL自動審核

標籤：超過   傳參   php   應用   解析   引號   creat   環境安裝   結果   

 

一、工具概述

 

SQL自動審核-自助上線平台，可以讓開發自上線，開發提交SQL後就會自動返回最佳化建議，無需DBA的再次審核，從而提升上線效率，有利於建立資料庫開發規範，讓DBA從日常繁瑣的工作中解放出來。

 

 

SQL自動審核主要完成兩方面目的：

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

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

 

思路其實很簡單：

1、擷取開發提交的SQL。

2、對要執行的SQL做分析，觸碰事先定義好的規則來判斷這個SQL是否可以自動審核通過，未通過審核的需要人工處理。

 

使用說明：

1、針對select/insert/update/create/alter加了規則，delete需要審批。

2、語句之間要有空格，例where id = 100，沒有空格會影響判斷的準確性。

3、SQL語句後面要加分號; MySQL解析器規定分號才可以執行SQL。

4、反引號`會造成上線失敗，需要用文字編輯器替換掉。

5、支援多條SQL解析，用一個分號;分割。例如：

 

insert into t1 values(1,‘a‘);

insert into t1 values(2,‘b‘);

6、JSON格式裡的雙引號要用反斜線進行轉義，例如：{\"dis_text\":\"nba\"}。

 

註：審核規則是根據我公司的情況制定而成，非Inception審核規則（只借鑒思路），使用時請注意！

 

其內部的原理，主要用Regex匹配規則實現。

 

二、功能實現

 

 SELECT審核

 

• 開發人員可以直接將SQL語句提交到平台進行風險評估

• 平台對SQL語句進行分析，自動給出其不符合開發規範的改進意見

• 適用情境：應用開發階段

 

檢查項：

1、select * 是否有必要查詢所有的欄位？

2、警告！沒有where條件，注意where後面的欄位要加上索引

3、沒有limit會查詢更多的資料

4、警告！子查詢效能低下，請轉為join表關聯

5、提示：in裡面的數值不要超過1000個

6、提示：採用join關聯，注意關聯欄位要都加上索引，如on a.id=b.id

7、提示：MySQL對多表join關聯效能低下，建議不要超過3個表以上的關聯

8、警告！like ‘%%‘雙百分比符號無法用到索引，like ‘mysql%‘這樣是可以利用到索引的

9、提示：預設情況下，MySQL對所有GROUP BY col1，col2...的欄位進行排序。如果查詢包括GROUP BY，想要避免排序結果的消耗，則可以指定ORDER BY NULL禁止排序。

10、警告！MySQL裡用到order by rand()在資料量比較多的時候是很慢的，因為會導致MySQL全表掃描，故也不會用到索引

11、提示：是否要加一個having過濾下？

12、警告！禁止不必要的order by排序，因為前面已經count統計了

13、警告！MySQL裡不支援函數索引，例DATE_FORMAT(‘create_time‘,‘%Y-%m-%d‘)=‘2016-01-01‘是無法用到索引的，需要改寫為

 create_time>=‘2016-01-01 00:00:00‘ and create_time<=‘2016-01-01 23:59:59‘

14、之後會調用美團網SQLAdvisor進行索引檢查

 

 

 INSERT審核 

 

檢查項：

• 警告：insert 表1 select 表2，會造成鎖表。

 

 UPDATE審核規則

 

 

1、警告！沒有where條件，update會全表更新，禁止執行！！！

2、更新的行數小於1000行，可以由開發自助執行。否則請聯絡DBA執行！！！

3、防止where 1=1 繞過審核規則

4、檢查更新欄位有無索引

 

 CREATE審核規則

 

檢查項：

1、警告！表沒有主鍵

2、警告！表主鍵應該是自增的，缺少AUTO_INCREMENT

3、提示：id自增欄位預設值為1，auto_increment=1

4、警告！表沒有索引

5、警告！表中的索引數已經超過5個，索引是一把雙刃劍，它可以提高查詢效率但也會降低插入和更新的速度並佔用磁碟空間

6、警告！表欄位沒有中文注釋，COMMENT應該有預設值，如COMMENT ‘姓名‘

7、警告！表沒有中文注釋

8、警告！表缺少utf8字元集，否則會出現亂碼

9、警告！表格儲存體引擎應設定為InnoDB

10、警告！表應該為timestamp類型加預設系統目前時間

  

 ALTER審核規則

 

檢查項：

1、警告！不支援create index文法，請更改為alter table add index文法。

2、警告！更改表結構要減少與資料庫的互動次數，應改為，例alter table t1 add index IX_uid(uid),add index IX_name(name)

3、表記錄小於100萬行，可以由開發自助執行。否則表太大請聯絡DBA執行!

4、支援刪除索引，但不支援刪除欄位

 

具體示範，請移步

http://blog.51cto.com/hcymysql/2053798

 

三、安裝部署

 

 環境安裝

 

1、PHP環境安裝

# yum install httpd php mysql php-mysql php-devel php-pear libssh2 libssh2-devel -y

 

2、安裝PHP SSH2擴充

pecl install -f ssh2

 

3、修改/etc/php.ini

在最後一行添加

extension=ssh2.so

 

4、關閉selinux

# vim /etc/selinux/config

SELINUX=disabled

 

5、美團網SQLAdvisor安裝

請移步 

https://github.com/Meituan-Dianping/SQLAdvisor/blob/master/doc/QUICK_START.md

 

 部署

 

將php-sqlreview.zip解壓縮到/var/www/html/目錄下

 

1、匯入dbinfo.sql（DB配置資訊表）和operation.sql（SQL工單記錄表）

 

2、修改db_config.php（DB配置資訊的IP、連接埠、使用者名稱、密碼、庫名）

 

3、修改sqladvisor_config.php（訪問SQLAdvisor伺服器的IP、SSH連接埠、SSH使用者名稱、SSH密碼）

 

4、修改sql_submit.php（記錄工單表的IP、連接埠、使用者名稱、密碼）和（調用mysql用戶端的IP、SSH連接埠、SSH使用者名稱、SSH密碼）

 

 指令碼解釋

 

1、index.html（SQL傳參入口）

2、sql_review.php（過審核規則）

3、sql_submit.php（通過後，SQL上線提交）

4、order.php（工單查詢-只記錄成功入庫的SQL）

5、order_result1.php（按照使用者名稱分頁搜尋）

6、order_result2.php（按照時間範圍分頁搜尋）

 

點擊此處【下載工具指令碼】或登入連結

https://pan.baidu.com/s/1eUct4Bo即可下載工具指令碼

現通過DBAplus社群免費為大家提供SQL自動審核-自助上線平台的下載使用。若使用過程中有任何問題或建議可隨時與我們聯絡，歡迎大家試用！

SQL自動審核