多行業SQL審核落地總結

近年來落地了數十個行業(包含銀行、制造業、保險等)的SQL審核項目，在項目對接需求，直到后期驗收，完成優化目標的過程中，有一些感悟和總結，本文做一個分享。

首先要明確一下SQL審核的對象范圍是針對數據庫層面的，涉及性能、安全風險的SQL，而非業務邏輯上的風險SQL(常見的如敏感信息的查詢、刪除、變更等)。

從應用場景上主要是4個核心的場景：生產環境優化具體業務，生產環境降低業務高峰期CPU/IO，預生產(或測試)環境攔截低效SQL，開發環境減少不合規SQL。

生產場景

優化具體業務

實施案例中以制造業為主，具體需求為對應的業務系統(OA、SAP、MES等)操作慢，優化驗收目標也比較簡單，實際業務操作變快達到驗收目標即可。

這類優化大多比較簡單，系統的問題基本為常見優化問題，且訪問生產庫，主機基本沒限制，通過系統自身的優化建議報告，建索引后，收集統計信息后，也方便驗證，項目進度快。

項目難點為完成業務操作與數據庫中SQL的對應。通過業務穿特定參數，結合ASH歷史進行模糊查詢可完成定位。得到SQL語句后，帶入綁定變量，統計運行消耗時間，與業務操作時間對比，確認出是否優化SQL能到達預期效果，再實施優化。

降低業務高峰期CPU/IO

該場景案例大多對應銀行、保險行業，具體需求為降低整個系統的CPU/IO負載。這種場景難度相對較高(特別是CPU)，通常有以下難點：

• 系統無明顯TOPSQL，TOPONE SQL比例(按占DB TIME百分比計算)小于4%;
• TOPSQL邏輯復雜，存在大量復雜邏輯PLSQL;
• 目標數據庫對應多個業務系統，數據庫JOB，操作系統CRONTAB設置JOB多;
• 業務情況復雜，一周中每天***0 SQL都有巨大變化。

生產環境SQL審核基本流程

以下流程生產環境的兩個主要場景都適用的：

• 確認優化目標(優化降低CPU/IO)確認優化時間段;
• 通過工具生成優化報告;
• 在測試環境運行相關SQL語句，收集邏輯讀，運行時長等信息，實施優化報告中的建議，再次運行SQL，記錄優化前后對比效果;
• 提交有效優化方案給開發確認，評估變更開發層面認為是否合理，(交付格式參考excel);
• 開發評估通過后有UAT環境，可再上UAT環境測試;無UAT環境，可直接上生產;
• 優化上線后，記錄主機，數據庫相關指標，確認是否達到優化目標。

非生產場景

預生產環境攔截低效SQL

該場景的案例具體需求有兩類：

• 分析SQL語句合規性;
• 發現存在性能瓶頸的且語義上需要改寫的SQL。

語句合規性比較簡單(通過靜態規則如select *;where 后無實際過濾，連接條件;含有笛卡爾集等能直接識別)，而存在性能瓶頸的且語義上需要改寫的SQL則算是非生產環境的SQL審核的核心。

因為不能自動確認SQL語句執行頻率，以及表上的數據量，數據分布可能與實際情況有較大出入，所以這個階段主要是識別那些需要改寫的來完成優化的SQL，畢竟這種SQL上線后要修復問題，難度較大。

測試環境SQL審核流程圖：

SQL審核測試在功能性測試完成后進行，審核數據庫為功能性測試連接的數據庫;

系統中生成審核報告，提交開發評估修改;

開發批量修改完成后，再次生成審核報告，重復以上流程，直至無嚴重級別規則***。

開發環境減少不合規SQL

該場景主要在大型企業中遇到，實施以培訓為主，配合開發規范文檔及靜態審核(合規性)。強制實施后，對開發源頭的爛SQL有較好的控制，極大減輕了測試后需要大面積返工的風險。

開發環境SQL審核流程：

• 開發人員抽取開發功能中的SQL語句;
• 提交SQL文本生成靜態審核報告;
• 如靜態審核報告中顯示有問題，開發修改SQL文本后，再次生成靜態審核報告，重復以上流程，直至無嚴重級別規則***。

SQL審核痛點

海量的審核結果

在最早期版本的SQL審核中，SQL審核出來的報告常常是列出了海量的問題SQL，即便是增加了規則優先級別后，依然因為找出的問題SQL過多，而難以實施。

在一次次的功能調整，理順流程中，我終于明白SQL審核的目標是發現并解決問題，而不是帶來更多的問題。如果通過審核找出了海量的問題SQL語句、表、索引等，以至于開發及DBA無法完全修復找出的全部問題，很可能在實施人員眼里有工具不如沒工具，最終工具跟流程還是脫節，推行不下去。

所以在找出問題這個層面，其實有個隱形的條件，即有多少時間留給開發?運維去確認及修復，轉換成需求即需要動態的圈定問題對象的范圍。

在SQL審核大部分的場景中，不論是在上線前的性能驗收，還是日常的優化計劃，單次SQL審核的目標基本可以歸結為：找到一定量可修復的(甚至是有修復建議的)問題，修復問題，并能獲取直觀的對比效果。

在劃分范圍時，我們需要確定出***高風險級別的規則的對象(SQL、表、索引等)，此時生產場景跟非生產場景則有較大區別。生產場景更多是希望盡可能少的變更，達到預定的目標。非生產場景則是盡可能全面的識別出潛在高風險的對象。

不明顯的Top SQL

在生產環境中審核SQL的常見的一個場景是OLTP類的應用沒有使用綁定變量，此類場景通過按照執行計劃聚合SQL，或是按照`FORCE_MATCHING_SIGNATURE` 聚合SQL可能取得一定的效果。

然而也有復雜些的場景，即使完成了相關的聚合后，依然找不到占比高的TOP SQL。換個角度來看問題，SQL審核大部分時候，我們審核的對象是SQL語句。這種視角在處理SQL語句變種多，有一定關聯相似性的場景時，就比較乏力。

這種場景其實切換成對象視角，即抽出數據庫中表的訪問條件路徑及訪問條件，按照dbtime 占比排序，可大幅度聚合訪問路徑層面的優化需求，并實現自動化優化建議。

SQL審核實施人員能力要求高

初期的生產環境的SQL審核對實施人員的要求較高，需要實施人員深入理解SQL審核規則，并能靈活應用優化的技能才能完成SQL審核的全流程，這樣甲方爸爸想培養人員自主掌握這套流程的實施就相對困難。在我們的實踐中，對這個痛點也開了處方。

在談處理思路前，我們先通過是否涉及到SQL的改寫將問題分為兩個大類：

• 不需要改寫(數據庫層面優化DBA主導);
• 需要改寫(SQL語義層面優化需開發配合)。

需要改寫的相對較復雜，其實一般偏AP的系統更多是這種需求。不需要改寫的SQL，其實在統計過優化手段后發現，占比***的優化方式還是訪問路徑層面的優化，大白話就是建合適的索引。

而這種優化手段對于SAP、ERP、DRM、HIS等等偏TP的系統都有非常好的優化效果，大部分類似系統可能僅僅通過索引的優化就能達到客戶的優化預期。

這部分，我們目前也已經通過自動化的優化建議降低了對實施人員能力的要求，而改寫部分則依然依賴人工的參與，這種細分場景還在自動化攻關中。

開發確認周期長

目前實施的審核項目中，一般涉及開發確認的步驟都較慢，有時項目周期大幅拖長就是由于頻繁需要開發確認，比如有的實施人員習慣通過awr報告，再次確認SQL優化級;或是希望分步走，少量多次穩步上生產，最終導致項目的延期。這個需求也轉換了我們對SQL審核的預期，也就是單次的SQL審核需要在實施前有個明確的收效預期。

總結

各行各業IT部門對SQL審核的需求日益旺盛，導致SQL審核細分場景較多，不同場景的關注重點差異也較大，自動優化建議配合人工測試/優化是我們目前落地的主要方式。通過審核的規則準確識別風險是項目的技術關鍵，我們的知識庫也在項目落地中不斷地校驗更新，關于規則這塊后期的文章中再做交流。