多行業(yè)SQL審核落地總結(jié)

近年來(lái)落地了數(shù)十個(gè)行業(yè)(包含銀行、制造業(yè)、保險(xiǎn)等)的SQL審核項(xiàng)目，在項(xiàng)目對(duì)接需求，直到后期驗(yàn)收，完成優(yōu)化目標(biāo)的過(guò)程中，有一些感悟和總結(jié)，本文做一個(gè)分享。

首先要明確一下SQL審核的對(duì)象范圍是針對(duì)數(shù)據(jù)庫(kù)層面的，涉及性能、安全風(fēng)險(xiǎn)的SQL，而非業(yè)務(wù)邏輯上的風(fēng)險(xiǎn)SQL(常見(jiàn)的如敏感信息的查詢(xún)、刪除、變更等)。

從應(yīng)用場(chǎng)景上主要是4個(gè)核心的場(chǎng)景：生產(chǎn)環(huán)境優(yōu)化具體業(yè)務(wù)，生產(chǎn)環(huán)境降低業(yè)務(wù)高峰期CPU/IO，預(yù)生產(chǎn)(或測(cè)試)環(huán)境攔截低效SQL，開(kāi)發(fā)環(huán)境減少不合規(guī)SQL。

生產(chǎn)場(chǎng)景

優(yōu)化具體業(yè)務(wù)

實(shí)施案例中以制造業(yè)為主，具體需求為對(duì)應(yīng)的業(yè)務(wù)系統(tǒng)(OA、SAP、MES等)操作慢，優(yōu)化驗(yàn)收目標(biāo)也比較簡(jiǎn)單，實(shí)際業(yè)務(wù)操作變快達(dá)到驗(yàn)收目標(biāo)即可。

這類(lèi)優(yōu)化大多比較簡(jiǎn)單，系統(tǒng)的問(wèn)題基本為常見(jiàn)優(yōu)化問(wèn)題，且訪問(wèn)生產(chǎn)庫(kù)，主機(jī)基本沒(méi)限制，通過(guò)系統(tǒng)自身的優(yōu)化建議報(bào)告，建索引后，收集統(tǒng)計(jì)信息后，也方便驗(yàn)證，項(xiàng)目進(jìn)度快。

項(xiàng)目難點(diǎn)為完成業(yè)務(wù)操作與數(shù)據(jù)庫(kù)中SQL的對(duì)應(yīng)。通過(guò)業(yè)務(wù)穿特定參數(shù)，結(jié)合ASH歷史進(jìn)行模糊查詢(xún)可完成定位。得到SQL語(yǔ)句后，帶入綁定變量，統(tǒng)計(jì)運(yùn)行消耗時(shí)間，與業(yè)務(wù)操作時(shí)間對(duì)比，確認(rèn)出是否優(yōu)化SQL能到達(dá)預(yù)期效果，再實(shí)施優(yōu)化。

降低業(yè)務(wù)高峰期CPU/IO

該場(chǎng)景案例大多對(duì)應(yīng)銀行、保險(xiǎn)行業(yè)，具體需求為降低整個(gè)系統(tǒng)的CPU/IO負(fù)載。這種場(chǎng)景難度相對(duì)較高(特別是CPU)，通常有以下難點(diǎn)：

• 系統(tǒng)無(wú)明顯TOPSQL，TOPONE SQL比例(按占DB TIME百分比計(jì)算)小于4%;
• TOPSQL邏輯復(fù)雜，存在大量復(fù)雜邏輯PLSQL;
• 目標(biāo)數(shù)據(jù)庫(kù)對(duì)應(yīng)多個(gè)業(yè)務(wù)系統(tǒng)，數(shù)據(jù)庫(kù)JOB，操作系統(tǒng)CRONTAB設(shè)置JOB多;
• 業(yè)務(wù)情況復(fù)雜，一周中每天***0 SQL都有巨大變化。

生產(chǎn)環(huán)境SQL審核基本流程

以下流程生產(chǎn)環(huán)境的兩個(gè)主要場(chǎng)景都適用的：

• 確認(rèn)優(yōu)化目標(biāo)(優(yōu)化降低CPU/IO)確認(rèn)優(yōu)化時(shí)間段;
• 通過(guò)工具生成優(yōu)化報(bào)告;
• 在測(cè)試環(huán)境運(yùn)行相關(guān)SQL語(yǔ)句，收集邏輯讀，運(yùn)行時(shí)長(zhǎng)等信息，實(shí)施優(yōu)化報(bào)告中的建議，再次運(yùn)行SQL，記錄優(yōu)化前后對(duì)比效果;
• 提交有效優(yōu)化方案給開(kāi)發(fā)確認(rèn)，評(píng)估變更開(kāi)發(fā)層面認(rèn)為是否合理，(交付格式參考excel);
• 開(kāi)發(fā)評(píng)估通過(guò)后有UAT環(huán)境，可再上UAT環(huán)境測(cè)試;無(wú)UAT環(huán)境，可直接上生產(chǎn);
• 優(yōu)化上線后，記錄主機(jī)，數(shù)據(jù)庫(kù)相關(guān)指標(biāo)，確認(rèn)是否達(dá)到優(yōu)化目標(biāo)。

非生產(chǎn)場(chǎng)景

預(yù)生產(chǎn)環(huán)境攔截低效SQL

該場(chǎng)景的案例具體需求有兩類(lèi)：

• 分析SQL語(yǔ)句合規(guī)性;
• 發(fā)現(xiàn)存在性能瓶頸的且語(yǔ)義上需要改寫(xiě)的SQL。

語(yǔ)句合規(guī)性比較簡(jiǎn)單(通過(guò)靜態(tài)規(guī)則如select *;where 后無(wú)實(shí)際過(guò)濾，連接條件;含有笛卡爾集等能直接識(shí)別)，而存在性能瓶頸的且語(yǔ)義上需要改寫(xiě)的SQL則算是非生產(chǎn)環(huán)境的SQL審核的核心。

因?yàn)椴荒茏詣?dòng)確認(rèn)SQL語(yǔ)句執(zhí)行頻率，以及表上的數(shù)據(jù)量，數(shù)據(jù)分布可能與實(shí)際情況有較大出入，所以這個(gè)階段主要是識(shí)別那些需要改寫(xiě)的來(lái)完成優(yōu)化的SQL，畢竟這種SQL上線后要修復(fù)問(wèn)題，難度較大。

測(cè)試環(huán)境SQL審核流程圖：

SQL審核測(cè)試在功能性測(cè)試完成后進(jìn)行，審核數(shù)據(jù)庫(kù)為功能性測(cè)試連接的數(shù)據(jù)庫(kù);

系統(tǒng)中生成審核報(bào)告，提交開(kāi)發(fā)評(píng)估修改;

開(kāi)發(fā)批量修改完成后，再次生成審核報(bào)告，重復(fù)以上流程，直至無(wú)嚴(yán)重級(jí)別規(guī)則***。

開(kāi)發(fā)環(huán)境減少不合規(guī)SQL

該場(chǎng)景主要在大型企業(yè)中遇到，實(shí)施以培訓(xùn)為主，配合開(kāi)發(fā)規(guī)范文檔及靜態(tài)審核(合規(guī)性)。強(qiáng)制實(shí)施后，對(duì)開(kāi)發(fā)源頭的爛SQL有較好的控制，極大減輕了測(cè)試后需要大面積返工的風(fēng)險(xiǎn)。

開(kāi)發(fā)環(huán)境SQL審核流程：

• 開(kāi)發(fā)人員抽取開(kāi)發(fā)功能中的SQL語(yǔ)句;
• 提交SQL文本生成靜態(tài)審核報(bào)告;
• 如靜態(tài)審核報(bào)告中顯示有問(wèn)題，開(kāi)發(fā)修改SQL文本后，再次生成靜態(tài)審核報(bào)告，重復(fù)以上流程，直至無(wú)嚴(yán)重級(jí)別規(guī)則***。

SQL審核痛點(diǎn)

海量的審核結(jié)果

在最早期版本的SQL審核中，SQL審核出來(lái)的報(bào)告常常是列出了海量的問(wèn)題SQL，即便是增加了規(guī)則優(yōu)先級(jí)別后，依然因?yàn)檎页龅膯?wèn)題SQL過(guò)多，而難以實(shí)施。

在一次次的功能調(diào)整，理順流程中，我終于明白SQL審核的目標(biāo)是發(fā)現(xiàn)并解決問(wèn)題，而不是帶來(lái)更多的問(wèn)題。如果通過(guò)審核找出了海量的問(wèn)題SQL語(yǔ)句、表、索引等，以至于開(kāi)發(fā)及DBA無(wú)法完全修復(fù)找出的全部問(wèn)題，很可能在實(shí)施人員眼里有工具不如沒(méi)工具，最終工具跟流程還是脫節(jié)，推行不下去。

所以在找出問(wèn)題這個(gè)層面，其實(shí)有個(gè)隱形的條件，即有多少時(shí)間留給開(kāi)發(fā)?運(yùn)維去確認(rèn)及修復(fù)，轉(zhuǎn)換成需求即需要?jiǎng)討B(tài)的圈定問(wèn)題對(duì)象的范圍。

在SQL審核大部分的場(chǎng)景中，不論是在上線前的性能驗(yàn)收，還是日常的優(yōu)化計(jì)劃，單次SQL審核的目標(biāo)基本可以歸結(jié)為：找到一定量可修復(fù)的(甚至是有修復(fù)建議的)問(wèn)題，修復(fù)問(wèn)題，并能獲取直觀的對(duì)比效果。

在劃分范圍時(shí)，我們需要確定出***高風(fēng)險(xiǎn)級(jí)別的規(guī)則的對(duì)象(SQL、表、索引等)，此時(shí)生產(chǎn)場(chǎng)景跟非生產(chǎn)場(chǎng)景則有較大區(qū)別。生產(chǎn)場(chǎng)景更多是希望盡可能少的變更，達(dá)到預(yù)定的目標(biāo)。非生產(chǎn)場(chǎng)景則是盡可能全面的識(shí)別出潛在高風(fēng)險(xiǎn)的對(duì)象。

不明顯的Top SQL

在生產(chǎn)環(huán)境中審核SQL的常見(jiàn)的一個(gè)場(chǎng)景是OLTP類(lèi)的應(yīng)用沒(méi)有使用綁定變量，此類(lèi)場(chǎng)景通過(guò)按照?qǐng)?zhí)行計(jì)劃聚合SQL，或是按照`FORCE_MATCHING_SIGNATURE` 聚合SQL可能取得一定的效果。

然而也有復(fù)雜些的場(chǎng)景，即使完成了相關(guān)的聚合后，依然找不到占比高的TOP SQL。換個(gè)角度來(lái)看問(wèn)題，SQL審核大部分時(shí)候，我們審核的對(duì)象是SQL語(yǔ)句。這種視角在處理SQL語(yǔ)句變種多，有一定關(guān)聯(lián)相似性的場(chǎng)景時(shí)，就比較乏力。

這種場(chǎng)景其實(shí)切換成對(duì)象視角，即抽出數(shù)據(jù)庫(kù)中表的訪問(wèn)條件路徑及訪問(wèn)條件，按照dbtime 占比排序，可大幅度聚合訪問(wèn)路徑層面的優(yōu)化需求，并實(shí)現(xiàn)自動(dòng)化優(yōu)化建議。

SQL審核實(shí)施人員能力要求高

初期的生產(chǎn)環(huán)境的SQL審核對(duì)實(shí)施人員的要求較高，需要實(shí)施人員深入理解SQL審核規(guī)則，并能靈活應(yīng)用優(yōu)化的技能才能完成SQL審核的全流程，這樣甲方爸爸想培養(yǎng)人員自主掌握這套流程的實(shí)施就相對(duì)困難。在我們的實(shí)踐中，對(duì)這個(gè)痛點(diǎn)也開(kāi)了處方。

在談處理思路前，我們先通過(guò)是否涉及到SQL的改寫(xiě)將問(wèn)題分為兩個(gè)大類(lèi)：

• 不需要改寫(xiě)(數(shù)據(jù)庫(kù)層面優(yōu)化DBA主導(dǎo));
• 需要改寫(xiě)(SQL語(yǔ)義層面優(yōu)化需開(kāi)發(fā)配合)。

需要改寫(xiě)的相對(duì)較復(fù)雜，其實(shí)一般偏AP的系統(tǒng)更多是這種需求。不需要改寫(xiě)的SQL，其實(shí)在統(tǒng)計(jì)過(guò)優(yōu)化手段后發(fā)現(xiàn)，占比***的優(yōu)化方式還是訪問(wèn)路徑層面的優(yōu)化，大白話就是建合適的索引。

而這種優(yōu)化手段對(duì)于SAP、ERP、DRM、HIS等等偏TP的系統(tǒng)都有非常好的優(yōu)化效果，大部分類(lèi)似系統(tǒng)可能僅僅通過(guò)索引的優(yōu)化就能達(dá)到客戶(hù)的優(yōu)化預(yù)期。

這部分，我們目前也已經(jīng)通過(guò)自動(dòng)化的優(yōu)化建議降低了對(duì)實(shí)施人員能力的要求，而改寫(xiě)部分則依然依賴(lài)人工的參與，這種細(xì)分場(chǎng)景還在自動(dòng)化攻關(guān)中。

開(kāi)發(fā)確認(rèn)周期長(zhǎng)

目前實(shí)施的審核項(xiàng)目中，一般涉及開(kāi)發(fā)確認(rèn)的步驟都較慢，有時(shí)項(xiàng)目周期大幅拖長(zhǎng)就是由于頻繁需要開(kāi)發(fā)確認(rèn)，比如有的實(shí)施人員習(xí)慣通過(guò)awr報(bào)告，再次確認(rèn)SQL優(yōu)化級(jí);或是希望分步走，少量多次穩(wěn)步上生產(chǎn)，最終導(dǎo)致項(xiàng)目的延期。這個(gè)需求也轉(zhuǎn)換了我們對(duì)SQL審核的預(yù)期，也就是單次的SQL審核需要在實(shí)施前有個(gè)明確的收效預(yù)期。

總結(jié)

各行各業(yè)IT部門(mén)對(duì)SQL審核的需求日益旺盛，導(dǎo)致SQL審核細(xì)分場(chǎng)景較多，不同場(chǎng)景的關(guān)注重點(diǎn)差異也較大，自動(dòng)優(yōu)化建議配合人工測(cè)試/優(yōu)化是我們目前落地的主要方式。通過(guò)審核的規(guī)則準(zhǔn)確識(shí)別風(fēng)險(xiǎn)是項(xiàng)目的技術(shù)關(guān)鍵，我們的知識(shí)庫(kù)也在項(xiàng)目落地中不斷地校驗(yàn)更新，關(guān)于規(guī)則這塊后期的文章中再做交流。