作者丨薛梁
AI SQL 稽核系統是陸金所於 2019 下半年重點開發的一個智慧化資料庫最佳化工具，已經為陸金所節約了大量用於 SQL 稽核的 DBA 和開發資源。陸金所資料架構團隊負責人王英傑老師在 2019 年 12 月北京·
ArchSummit 全球架構師峰會
上介紹了陸金所 AI SQL Review 系統演進話題，希望陸金所處理 SQL 稽核工作經驗給大家帶來一些新的思路。
陸金所 AI SQL 稽核系統的專案背景
做過開發和 DBA 的同學應該都深有感觸，SQL 的效能對核心系統的可用率至關重要。因為一條爛 SQL 拖慢整個系統甚至讓系統宕機的案例比比皆是。所以在應用發版前，建立起一套行之有效的人工 SQL 稽核流程，把 SQL 效能問題發現在上線前，是不少公司的主流做法。陸金所一直以來都非常重視應用發版前 SQL 的稽核工作，為此還研發了一整套人工 SQL 稽核系統。
人工 SQL 稽核系統確實幫我們在上線前發現了大量 SQL 的效能問題，並進行了提前最佳化，避免了很多生產事故的發生。但人工 SQL 稽核平臺稽核部分主要還是靠人工，所以非常依賴開發和 DBA 的人力資源，這樣一來，為了有效執行人工 SQL 稽核系統，將遭遇到開發和 DBA 的人力資源瓶頸。為什麼這麼說呢？
這裡看一下人工 SQL 稽核的流程，首先是程式碼提交，人工 SQL 稽核平臺會直接和程式碼庫進行對接；
接下來是差異比較，人工 SQL 稽核平臺會讀取出當前生產版本和待上線版本之間 SQL 程式碼的變動和執行計劃差異，並自動生成比對報告；
之後是最消耗開發和 DBA 人力資源的人工稽核階段，由開發和 DBA 閱讀比對報告並給出評估意見。評估意見被 SQL 稽核系統作為標籤資料永久儲存；
最後是上線批准，在一個版本里每一筆 SQL 都經過稽核並最佳化消除掉效能風險後，才允許上線提交到生產。
所以回顧整個人工 SQL 稽核流程，會發現人工 SQL 稽核系統具備以下特點：
人工 SQL 稽核系統會對所有應用、每個版本下的每筆 SQL 進行非常細粒度和端到端的管理；
同時呢，人工 SQL 系統會提前自動完成程式碼和執行計劃的差異比對；
接下來，DBA 會稽核每個版本里發生變更的 SQL、導致的執行計劃差異以及對效能的影響；
最後為了讓稽核結果能落地，發版平臺將建立起對人工 SQL 稽核系統的強依賴關係。
這是陸金所研發的 SQL 稽核平臺功能介面，它會自動到程式碼庫裡去查詢相同一個 SQL 標籤在不同版本里的程式碼和執行計劃變動情況，並生成報告給開發和 DBA 進行稽核。開發和 DBA 可以點選透過並選擇透過的意見，如果效能存在問題，也可以點選不透過並選擇效能問題的分類，並輸入最佳化意見。人工 SQL 稽核平臺會儲存上面所有的評審資料。
這個平臺自從 2016 年上線以來收集了陸金所每個版本下每筆 SQL 語句的程式碼、執行計劃、評審和最佳化意見。所以基於這套人工稽核平臺我們收集了大量的標籤資料。
可以看到圖中是人工 SQL 稽核流程第 3 個環節的詳細展開，這同時也是一個對 SQL 稽核打標籤並進行標籤收集的過程。整個流程基於 SQL 稽核平臺完成的事前準備開始，透過稽核的直接具備上線條件，不透過的稽核的將由 DBA 進行效能問題分類，並根據效能問題點設計對應的最佳化方案交由開發進行最佳化。最佳化完成後再次再次稽核，一直到透過上線。其中無論是稽核、分類和最佳化環節既是在做 SQL 的評審和最佳化，同時也是人工打標籤的過程。而這個打標籤的過程將消耗大量的開發和 DBA 人力資源。
在整個系統高速迭代的過程中，如果要確保人工 SQL 稽核流程落地，開發和 DBA 的人力資源將成為巨大的瓶頸。所以，當時我們就在思考，能否有更好的解決方案。
第二個部分是 AI SQL 稽核系統的研發過程
基於此，研發團隊開始了 AI SQL 稽核系統的研發。
AI SQL 稽核系統的研發條件有哪些呢？人工 SQL 稽核平臺上線三年後，積累下了大量真實的、和 SQL 稽核相關的歷史資料，包括 SQL 在每個應用版本的程式碼改動、生產環境執行計劃變動、資料字典和統計資訊，以及 DBA 的稽核標籤資料，和上線生產後的監控系統捕獲的執行時效。所以可以考慮一下，是否可以基於此嘗試使用演算法來模擬 DBA 對 SQL 的稽核。
那麼如何才能使用歷史資料加 AI 演算法來模擬 DBA 對 SQL 的稽核呢，這個場景具體是要解決一個什麼問題呢？這個問題的核心是為了找到 SQL 程式碼、執行計劃、統計資訊、繫結變數和執行效率之間的相關性。並透過相關性基於 SQL 程式碼、執行計劃、統計資訊、繫結變數來預測執行效率。如果可以準確預測出 SQL 的執行效率，就能準確的評估出 SQL 是否具備上線條件。
上圖展示了 AI SQL 稽核系統實現對 SQL 執行效率預測的實現方案。如圖所示，AI SQL 稽核系統主要有三大部分組成：輸入為 SQL 稽核平臺以及其他自動化工具收集的 SQL 語句、執行計劃、統計資訊和繫結變數。目標是生產監控系統捕獲的 SQL 的平均執行時間。我們希望透過演算法可以找到這兩者之間的關係。
模型則是包含四個部分，分別是：
1）特徵嵌入，2) 特徵壓縮降維，3）特徵裁剪，4）預測
可以看到我們使用的預測模型是最為簡單的線性預型，這麼做的目的也是想讓過擬合風險降到最低。下面對這四個個核心演算法展開介紹。
特徵嵌入的目標是對資料進行處理，把 ai 模型不可讀的 text 格式轉化為 ai 模型可以計算的數字格式。這裡對 SQL 語句做了詞頻逆文件頻處理。tf-idf 是一種業界常用的方法，從詞頻來突出 SQL 包含了哪些表，哪些欄位。對於執行計劃裡的非數字資訊，比如表的連線，索引的掃描方式，巢狀查詢的關聯方式等，我們做了素數編碼。素數編碼可以完整保留每個元素的全部資訊，同時又抹去了元素之間的為位置關係。降低了位置關係對模型訓練的干擾，方便機器學習。經過處理後把 SQL 語句、執行計劃、統計資訊和繫結變數轉化成一個非常寬的矩陣。這樣就完成了對輸入資料的前期處理。
為了保證矩陣有解，並最大化降低過擬合風險。我們利用 VAEs 演算法對稀疏的寬矩陣進行資訊壓縮降維，把上千個特徵向量的矩陣壓縮為 53 個特徵向量的矩陣。在進行降維壓縮的過程中，我們發現陸金所的 SQL 資料呈現明顯的兩極化，符合預期。
最後使用隨機森林演算法進行特徵提取，從 53 個特徵向量裡保留 30% 資訊增益最大的 38 個特徵向量。從宏觀 ROC 曲線可以看到保留 30% 的特徵覆蓋的面積最大、魯棒性最強，因此我們只保留前 30% 資訊。我們將這種方法稱為粗粒度學習。及把迴歸問題先劃分成一個多分類問題，先粗力度的學習特徵與目標的相關性。這裡，將我們基於 DBA 經驗將 SQL 的執行時間劃分為 10 個區間，具體區間如下：
基於之前生成的 38 個特徵，我們做了一個線性迴歸來對 SQL 執行效率預測。預測的方案如下：
首先我們選取了 300 條生產環境的 SQL 進行目標預測。
這 300 條 SQL 在監控系統中我們可以獲取真實的執行時長，按照從低到高排序，繪製出橙色曲線。
使用 38 個特徵配和線性迴歸演算法，我們預測出了這 300 個 SQL 的執行時長。即在不執行 SQL 的情況下，依靠資料（資料包括 SQL 文字、執行計劃、統計資訊和繫結變數）和模型來預測 SQL 的執行時長。
預測結果見藍色曲線，可以看到預測結果存在一定的噪音和毛刺，但這些噪音可以接受的，因為總體趨勢和生產環境的執行時長呈現高度一致性。因此基於這套模型，可以對 SQL 的執行效率進行一個大體上較為準確的預測。然後再基於預測結果，智慧評估 SQL 是否具備上線條件。
最終將這個結果，作為 SQL 稽核平臺的第一道環節。如果 AI 稽核透過即直接上線，人工不再稽核。SQL 稽核總量裡 80% 的 SQL 都是符合效能預期的，這些 SQL 因為由 AI 直接稽核後上線，所以也節省了 80% 的人工稽核工作量。
如果對 SQL 的稽核可以使用 AI 來智慧評估，那對於效能不好的 SQL，是否也可以透過演算法來模擬開發和 DBA 對 SQL 進行調優。即把 SQL 稽核流程中分類和最佳化這兩個步驟，也透過模型來模擬。基於此，我們開始了第二版本的研發。
如果把 AI 的學習過程當做是個搜尋問題，那麼之前使用的方法需要對問題的解做完全遍歷，如左圖（頻率派）。簡而言之，頻率派需要大量的資料來支援模型做完全遍歷搜尋找到最優解。對於 SQL 最佳化問題 SQL 對應的問題有很多種，如果讓頻率派學到一個最優解。那麼每一種 SQL 問題都要對應一大批訓練資料。其中的人力成本不可估計，這對於我們來說是不切實際的。
因此我們選擇了貝葉斯派 （右圖）。貝葉斯派，基於先驗可以對搜尋空間做縮減，這樣一能提高訓練速度，二也解決了資料不足的問題。
這個版本只是一個“建議系統”，及對 SQL 提出可修改的建議。不同於 AI 預測 SQL 執行效能，對 SQL 給出最佳化建議，需要模型瞭解 SQL 的結構關係，比如多表之間的連線關係，單表字段與索引之間的關係等等。同時 SQL 效能最佳化系統對一個 SQL 往往會生成多個最佳化建議。對於大部分 n 到 1（n 個輸入 一個輸出）的機器學習演算法，並不能很好的解決問題。
這裡我們提出了一種機器學習 + 搜尋結合的方法，即基於貝葉斯的啟發式搜尋方法。啟發式搜尋分為三個部分：
首先還是預處理，為了能讓模型理解 SQL 的結構，我們專門開發 LUParser SQL 解析器，將 SQL 語句解析成一個有向圖（後面會具體介紹）作為搜尋的資訊之一。這一點和預測 SQL 執行時長時對 SQL 程式碼處理的詞頻逆文件頻有所不同。除了結構資訊以外，執行計劃，統計資訊，繫結變數我們都一併保留，並作特徵嵌入，嵌入方法與之前一樣這裡就不再介紹。
啟發式搜尋的核心是啟發函式，我們基於輸入資料計算出，輸入與搜尋節點之前的條件機率。
最後基於啟發值的大小搜尋出一套 SQL 最佳化建議。
這裡主要介紹一下技術核心陸金所 SQL 解析器 LUParser：
LUParser 是在 AST 樹基礎上，進一步將列，表，檢視等的關係解析成一個有向圖。這種方式能夠幫助機器更好的理解 SQL 裡的關聯關係，方便對 SQL 做系統最佳化。有向圖的一個好處是能夠保留比樹更多的資訊，可以透過有向圖基於不同的需求，解析成不同的樹。這裡展示一個複雜的多層半連線巢狀 SQL 語句解析結果。可以看到有向圖可以非常清晰的把包含三層巢狀子查詢的 SQL 全部資訊都展示出來。
下面是 LUPareser 的計算過程，主要有三部組成：
首先正則化分詞，將 SQL 語句轉換成精度不高的 AST 樹。這裡使用基於關鍵字 + 正則化做了個粗粒度的轉換。
之後會做深度優先完全遍歷，主要目的是將 AST 樹中的 token 封裝成圖的節點。
建立圖連結。圖連結是按照子查詢到表到欄位的順序依次建立的。整個過程也只需要一次遍歷 。
整個演算法下來我們需要對 SQL 做三次遍歷即可解析完成。
介紹完特徵處理，下面會著重介紹一下啟發式搜尋：
首先要構建一個搜尋地圖：
1。1 結合歷史 DBA 評審話術和 DBA 經驗，劃分出一個狀態庫（搜尋地圖）。狀態主要分為索引最佳化，表連線最佳化，分頁排序最佳化和子查詢最佳化等。同時我們的狀態還在不斷的補充完善中。
1。2 這些狀態都是一個個相互獨立的方法。一個狀態可以有多種輸出，但必須輸出一個數值，用於計算啟發值。
構建基於貝葉斯的啟發函式
2。1 啟發函式用於構建狀態與狀態之間的轉換。
2。2 啟發函式有兩個輸入，一當前狀態的位置（S），二 SQL 資訊（X）。啟發函式輸出為每個候選狀態的分數。分數越高則被選擇的機率越大。
搜尋終止條件：
只允許同一個狀態至多能被使用一次。如果搜尋發現沒有可選狀態或者候選狀態為空時搜尋終止。
下面介紹一下啟發函式的訓練方法，主要分為兩個部分：
基於歷史評審意見，生成地圖並計算兩個狀態之間的條件機率：
a。 我們首先對建議做分詞，濾重等預處理。
b。 將處理好的詞，透過同義詞轉換，抓取關鍵字等等，對映成搜尋地圖。
c。 在給定 SQL 特徵下（X），統計狀態與狀態之間的聯合機率和條件機率。
d。 透過鏈式法則生成啟發式函式。
在計算條件機率時會對部分狀態之間的聯合分佈做限定：比如，索引問題更傾向於查詢區分度和直方圖。
上線前對接程式碼庫和發版平臺，對稽核不透過的 SQL，先給出一輪最佳化建議供開發和 DBA 參考。上線後對接監控平臺，監控平臺捕獲慢查詢後，呼叫 AI SQL 稽核服務介面，自動給出最佳化建議。同時 AI 明確最佳化建議優於 CBO 的執行計劃才會給出建議反饋。
我們的建議模型基於貝葉斯派模，雖然模型輸出可控，但也會受限於 DBA 的經驗。於是我們思考，能否開發一種激勵演算法，來鼓勵模型去嘗試對 SQL 做不同的最佳化。為此我們構思出了一種基於強化學習 + 對抗模型的 SQL 改寫最佳化模型，希望模型透過不斷的嘗試改寫 SQL 來學得如何去最佳化一條查詢語句。該想法目前還處於研發階段。
整個演算法由三個神經網路模型組成。分別為：
SQL 生成器，SQL 最佳化器，SQL 惡化器。
SQL 生成器：
1。1 目的：在 AI 能夠改寫 SQL 之前，我們希望 AI 可以準確的寫 SQL。所以我們先訓練了一個 SQL 生成器。
1。2 訓練方法：SQL 生成器的輸入由 LUParser 提供，主要有兩個資訊，1） SQL 的結構 2） 連結關係。目前我們已經成功訓練出 SQL 生成器。
對抗模型：
2。1 繼承 SQL 生成器：將訓練好的 SQL 生成器直接“遷移”給 SQL 最佳化器，和 SQL 惡化器，省去他們學習如何寫 SQL 的時間。
2。2 對抗體制：最佳化器不斷地最佳化惡化器提供的差 SQL。反之，惡化器不斷地惡化最佳化器提供的好 SQL。這裡需要強化學習的介入，來保證最佳化器 和惡化器處理後的 SQL 和之前 SQL 的結果一致。
強化學習：
3。1 目的：為了保證最佳化器和惡化器處理後 SQL 的結果一致，我們引入了強化學習。
3。2 方法：以最佳化器為例， 當最佳化器收到惡化器傳遞給的 SQL 後，最佳化器透過強化學習，會不斷地嘗試對該 SQL 做改寫。直到生成滿足條件的 SQL 才會傳給惡化器。
對抗模型優點：
理論上如果單使用強化學習，模型是可以學到如何最佳化 SQL 的。但是這裡存在一個弊端，只用強化學習，會讓模型接觸的資料範圍過窄，訓練出來的模型泛化性過低。模型很容易學到只對某個 SQL 做最佳化的方法。
對 AI SQL 稽核系統的未來展望
陸金所的 AI SQL 稽核系統目前已經完成了對針對 Oracle 資料庫 SQL 稽核兩個版本的研發，還會嘗試開發支援 MySQL、Hive 和 Impala 等資料庫 SQL 稽核的功能。如果大家對陸金所 AI SQL 稽核系統技術細節感興趣，歡迎溝通。
點個在看少個 bug