Categories
程式開發

陸金所 AI SQL Review 系統演進和實踐


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 審核平台會直接和代碼庫進行對接;

陸金所 AI SQL Review 系統演進和實踐 1

接下來是差異比較,人工 SQL 審核平台會讀取出當前生產版本和待上線版本之間 SQL 代碼的變動和執行計劃差異,並自動生成比對報告;

之後是最消耗開發和 DBA 人力資源的人工審核階段,由開發和 DBA 閱讀比對報告並給出評估意見。評估意見被 SQL 審核系統作為標籤數據永久保存;

最後是上線批准,在一個版本里每一筆 SQL 都經過審核並優化消除掉性能風險後,才允許上線提交到生產。

所以回顧整個人工 SQL 審核流程,會發現人工 SQL 審核系統具備以下特點:

  • 人工 SQL 審核系統會對所有應用、每個版本下的每筆 SQL 進行非常細粒度和端到端的管理;

  • 同時呢,人工 SQL 系統會提前自動完成代碼和執行計劃的差異比對;

  • 接下來,DBA 會審核每個版本里發生變更的 SQL、導致的執行計劃差異以及對性能的影響;

  • 最後為了讓審核結果能落地,發版平台將建立起對人工 SQL 審核系統的強依賴關係。

陸金所 AI SQL Review 系統演進和實踐 2

這是陸金所研發的 SQL 審核平台功能界面,它會自動到代碼庫裡去查找相同一個 SQL 標籤在不同版本里的代碼和執行計劃變動情況,並生成報告給開發和 DBA 進行審核。開發和 DBA 可以點擊通過並選擇通過的意見,如果性能存在問題,也可以點擊不通過並選擇性能問題的分類,並輸入優化意見。人工 SQL 審核平台會保存上面所有的評審數據。

這個平台自從2016年上線以來收集了陸金所每個版本下每筆 SQL 語句的代碼、執行計劃、評審和優化意見。所以基於這套人工審核平台我們收集了大量的標籤數據。

陸金所 AI SQL Review 系統演進和實踐 3

可以看到圖中是人工 SQL 審核流程第3個環節的詳細展開,這同時也是一個對 SQL 審核打標籤並進行標籤收集的過程。整個流程基於 SQL 審核平台完成的事前準備開始,通過審核的直接具備上線條件,不通過的審核的將由 DBA 進行性能問題分類,並根據性能問題點設計對應的優化方案交由開發進行優化。優化完成後再次再次審核,一直到通過上線。其中無論是審核、分類和優化環節既是在做 SQL 的評審和優化,同時也是人工打標籤的過程。而這個打標籤的過程將消耗大量的開發和 DBA 人力資源。

在整個系統高速迭代的過程中,如果要確保人工 SQL 審核流程落地,開發和 DBA 的人力資源將成為巨大的瓶頸。所以,當時我們就在思考,能否有更好的解決方案。

AI SQL 審核系統的研發過程

基於此,研發團隊開始了 AI SQL 審核系統的研發。

陸金所 AI SQL Review 系統演進和實踐 4

AI SQL 審核系統的研發條件有哪些呢?人工SQL 審核平台上線三年後,積累下了大量真實的、和SQL 審核相關的歷史數據,包括SQL 在每個應用版本的代碼改​​動、生產環境執行計劃變動、數據字典和統計信息,以及DBA 的審核標籤數據,和上線生產後的監控系統捕獲的運行時效。所以可以考慮一下,是否可以基於此嘗試使用算法來模擬 DBA 對 SQL 的審核。

那麼如何才能使用歷史數據加 AI 算法來模擬 DBA 對 SQL 的審核呢,這個場景具體是要解決一個什麼問題呢?這個問題的核心是為了找到 SQL代碼、執行計劃、統計信息、綁定變量和執行效率之間的相關性。並通過相關性基於 SQL 代碼、執行計劃、統計信息、綁定變量來預測執行效率。如果可以準確預測出 SQL 的執行效率,就能準確的評估出 SQL 是否具備上線條件。

陸金所 AI SQL Review 系統演進和實踐 5

上圖展示了 AI SQL 審核系統實現對 SQL 執行效率預測的實現方案。如圖所示,AI SQL 審核系統主要有三大部分組成:輸入為 SQL 審核平台以及其他自動化工具收集的 SQL 語句、執行計劃、統計信息和綁定變量。目標是生產監控系統捕獲的 SQL 的平均執行時間。我們希望通過算法可以找到這兩者之間的關係。

模型則是包含四個部分,分別是:1)特徵嵌入,2) 特徵壓縮降維,3)特徵裁剪,4)預測

可以看到我們使用的預測模型是最為簡單的線性預型,這麼做的目的也是想讓過擬合風險降到最低。下面對這四個個核心算法展開介紹。

陸金所 AI SQL Review 系統演進和實踐 6

特徵嵌入的目標是對數據進行處理,把ai模型不可讀的 text 格式轉化為ai模型可以計算的數字格式。這裡對 SQL 語句做了詞頻逆文檔頻處理。 tf-idf 是一種業界常用的方法,從詞頻來突出 SQL 包含了哪些表,哪些字段。對於執行計劃裡的非數字信息,比如表的連接,索引的掃描方式,嵌套查詢的關聯方式等,我們做了素數編碼。素數編碼可以完整保留每個元素的全部信息,同時又抹去了元素之間的為位置關係。降低了位置關係對模型訓練的干擾,方便機器學習。經過處理後把 SQL 語句、執行計劃、統計信息和綁定變量轉化成一個​​非常寬的矩陣。這樣就完成了對輸入數據的前期處理。

陸金所 AI SQL Review 系統演進和實踐 7

為了保證矩陣有解,並最大化降低過擬合風險。我們利用 VAEs 算法對稀疏的寬矩陣進行信息壓縮降維,把上千個特徵向量的矩陣壓縮為53個特徵向量的矩陣。在進行降維壓縮的過程中,我們發現陸金所的 SQL 數據呈現明顯的兩極化,符合預期。

最後使用隨機森林算法進行特徵提取,從53個特徵向量裡保留30%信息增益最大的38個特徵向量。從宏觀 ROC 曲線可以看到保留30%的特徵覆蓋的面積最大、魯棒性最強,因此我們只保留前30%信息。我們將這種方法稱為粗粒度學習。及把回歸問題先劃分成一個多分類問題,先粗力度的學習特徵與目標的相關性。這裡,將我們基於 DBA 經驗將 SQL 的執行時間劃分為10個區間,具體區間如下:

  • (0,0.1) 毫秒
  • (0.1,0.5)毫秒
  • (0.5,1)毫秒
  • (1,10)毫秒
  • (10,100)毫秒
  • (100,1000)毫秒
  • (1,10)秒
  • (10,100)秒
  • (100,1000)秒
  • (1000,INF)秒

陸金所 AI SQL Review 系統演進和實踐 8

基於之前生成的38個特徵,我們做了一個線性回歸來對 SQL 執行效率預測。預測的方案如下:

  • 首先我們選取了300條生產環境的 SQL 進行目標預測。

  • 這300條 SQL 在監控系統中我們可以獲取真實的執行時長,按照從低到高排序,繪製出橙色曲線。

  • 使用38個特徵配和線性回歸算法,我們預測出了這300個 SQL 的執行時長。即在不執行 SQL 的情況下,依靠數據(數據包括 SQL 文本、執行計劃、統計信息和綁定變量)和模型來預測 SQL 的執行時長。

預測結果見藍色曲線,可以看到預測結果存在一定的噪音和毛刺,但這些噪音可以接受的,因為總體趨勢和生產環境的執行時長呈現高度一致性。因此基於這套模型,可以對 SQL 的執行效率進行一個大體上較為準確的預測。然後再基於預測結果,智能評估 SQL 是否具備上線條件。

最終將這個結果,作為 SQL 審核平台的第一道環節。如果 AI 審核通過即直接上線,人工不再審核。 SQL 審核總量裡80%的 SQL 都是符合性能預期的,這些 SQL 因為由 AI 直接審核後上線,所以也節省了80%的人工審核工作量。

陸金所 AI SQL Review 系統演進和實踐 9

如果對 SQL 的審核可以使用AI來智能評估,那對於性能不好的 SQL,是否也可以通過算法來模擬開發和 DBA 對 SQL 進行調優。即把 SQL 審核流程中分類和優化這兩個步驟,也通過模型來模擬。基於此,我們開始了第二版本的研發。

陸金所 AI SQL Review 系統演進和實踐 10

如果把 AI 的學習過程當做是個搜索問題,那麼之前使用的方法需要對問題的解做完全遍歷,如左圖(頻率派)。簡而言之,頻率派需要大量的數據來支持模型做完全遍歷搜索找到最優解。對於 SQL 優化問題 SQL對應的問題有很多種,如果讓頻率派學到一個最優解。那麼每一種 SQL 問題都要對應一大批訓練數據。其中的人力成本不可估計,這對於我們來說是不切實際的。

因此我們選擇了貝葉斯派 (右圖)。貝葉斯派,基於先驗可以對搜索空間做縮減,這樣一能提高訓練速度,二也解決了數據不足的問題。

陸金所 AI SQL Review 系統演進和實踐 11

這個版本只是一個“建議系統”,及對 SQL 提出可修改的建議。不同於 AI 預測 SQL 執行性能,對 SQL 給出優化建議,需要模型了解 SQL 的結構關係,比如多表之間的連接關係,單表字段與索引之間的關係等等。同時 SQL 性能優化系統對一個 SQL 往往會生成多個優化建議。對於大部分 n 到 1(n個輸入 一個輸出)的機器學習算法,並不能很好的解決問題。

這裡我們提出了一種機器學習 + 搜索結合的方法,即基於貝葉斯的啟發式搜索方法。啟發式搜索分為三個部分:

  • 首先還是預處理,為了能讓模型理解 SQL 的結構,我們專門開發 LUParser SQL 解析器,將 SQL 語句解析成一個有向圖(後面會具體介紹)作為搜索的信息之一。這一點和預測 SQL 執行時長時對 SQL 代碼處理的詞頻逆文檔頻有所不同。除了結構信息以外,執行計劃,統計信息,綁定變量我們都一併保留,並作特徵嵌入,嵌入方法與之前一樣這裡就不再介紹。

  • 啟發式搜索的核心是啟發函數,我們基於輸入數據計算出,輸入與搜索節點之前的條件概率。

  • 最後基於啟發值的大小搜索出一套 SQL 優化建議。

陸金所 AI SQL Review 系統演進和實踐 12

這裡主要介紹一下技術核心陸金所 SQL 解析器 LUParser:

LUParser 是在 AST 樹基礎上,進一步將列,表,視圖等的關係解析成一個有向圖。這種方式能夠幫助機器更好的理解 SQL 裡的關聯關係,方便對 SQL 做系統優化。有向圖的一個好處是能夠保留比樹更多的信息,可以通過有向圖基於不同的需求,解析成不同的樹。這裡展示一個複雜的多層半連接嵌套 SQL 語句解析結果。可以看到有向圖可以非常清晰的把包含三層嵌套子查詢的 SQL 全部信息都展示出來。

陸金所 AI SQL Review 系統演進和實踐 13

下面是 LUPareser 的計算過程,主要有三部組成:

  1. 首先正則化分詞,將 SQL 語句轉換成精度不高的 AST 樹。這裡使用基於關鍵字+正則化做了個粗粒度的轉換。

  2. 之後會做深度優先完全遍歷,主要目的是將 AST 樹中的 token 封裝成圖的節點。

  3. 建立圖鏈接。圖鏈接是按照子查詢到表到字段的順序依次建立的。整個過程也只需要一次遍歷 。

整個算法下來我們需要對 SQL 做三次遍歷即可解析完成。

陸金所 AI SQL Review 系統演進和實踐 14

介紹完特徵處理,下面會著重介紹一下啟發式搜索:

  1. 首先要構建一個搜索地圖:
  • 結合歷史 DBA 評審話術和 DBA 經驗,劃分出一個狀態庫(搜索地圖)。狀態主要分為索引優化,表連接優化,分頁排序優化和子查詢優化等。同時我們的狀態還在不斷的補充完善中。
  • 這些狀態都是一個個相互獨立的方法。一個狀態可以有多種輸出,但必須輸出一個數值,用於計算啟發值。
  1. 構建基於貝葉斯的啟發函數
  • 啟發函數用於構建狀態與狀態之間的轉換。
  • 啟發函數有兩個輸入,一當前狀態的位置(S),二SQL信息(X)。啟發函數輸出為每個候選狀態的分數。分數越高則被選擇的概率越大。
  1. 搜索終止條件:

只允許同一個狀態至多能被使用一次。如果搜索發現沒有可選狀態或者候選狀態為空時搜索終止。

陸金所 AI SQL Review 系統演進和實踐 15

下面介紹一下啟發函數的訓練方法,主要分為兩個部分:

  1. 基於歷史評審意見,生成地圖併計算兩個狀態之間的條件概率:
  • 我們首先對建議做分詞,濾重等預處理。
  • 將處理好的詞,通過同義詞轉換,抓取關鍵字等等,映射成搜索地圖。
  • 在給定 SQL 特徵下(X),統計狀態與狀態之間的聯合概率和條件概率。
  • 通過鍊式法則生成啟發式函數。
  1. 在計算條件概率時會對部分狀態之間的聯合分佈做限定:比如,索引問題更傾向於查找區分度和直方圖。

上線前對接代碼庫和發版平台,對審核不通過的 SQL,先給出一輪優化建議供開發和 DBA 參考。上線後對接監控平台,監控平台捕獲慢查詢後,調用 AI SQL 審核服務接口,自動給出優化建議。同時 AI 明確優化建議優於 CBO 的執行計劃才會給出建議反饋。

我們的建議模型基於貝葉斯派模,雖然模型輸出可控,但也會受限於 DBA 的經驗。於是我們思考,能否開發一種激勵算法,來鼓勵模型去嘗試對SQL做不同的優化。為此我們構思出了一種基於強化學習+對抗模型的 SQL 改寫優化模型,希望模型通過不斷的嘗試改寫 SQL 來學得如何去優化一條查詢語句。該想法目前還處於研發階段。

陸金所 AI SQL Review 系統演進和實踐 16

整個算法由三個神經網絡模型組成。分別為:SQL 生成器,SQL 優化器,SQL 惡化器。

1. SQL 生成器:

  • 1.1 目的:在 AI 能夠改寫 SQL之前,我們希望 AI 可以準確的寫 SQL。所以我們先訓練了一個 SQL 生成器。
  • 1.2 訓練方法:SQL 生成器的輸入由 LUParser 提供,主要有兩個信息,1) SQL 的結構 2) 鏈接關係。目前我們已經成功訓練出 SQL 生成器。

2. 對抗模型:

  • 2.1 繼承 SQL 生成器:將訓練好的 SQL 生成器直接“遷移”給 SQL 優化器,和 SQL 惡化器,省去他們學習如何寫 SQL 的時間。
  • 2.2 對抗體制:優化器不斷地優化惡化器提供的差SQL。反之,惡化器不斷地惡化優化器提供的好 SQL。這裡需要強化學習的介入,來保證優化器 和惡化器處理後的 SQL 和之前 SQL 的結果一致。

3. 強化學習:

  • 3.1 目的:為了保證優化器和惡化器處理後 SQL 的結果一致,我們引入了強化學習。
  • 3.2 方法:以優化器為例, 當優化器收到惡化器傳遞給的 SQL 後,優化器通過強化學習,會不斷地嘗試對該 SQL 做改寫。直到生成滿足條件的 SQL 才會傳給惡化器。

對抗模型優點:

理論上如果單使用強化學習,模型是可以學到如何優化 SQL 的。但是這裡存在一個弊端,只用強化學習,會讓模型接觸的數據范圍過窄,訓練出來的模型泛化性過低。模型很容易學到只對某個 SQL 做優化的方法。

對 AI SQL 審核系統的未來展望

陸金所的 AI SQL 審核系統目前已經完成了對針對 Oracle 數據庫 SQL 審核兩個版本的研發,還會嘗試開發支持 MySQL、Hive 和 Impala 等數據庫 SQL 審核的功能。如果大家對陸金所 AI SQL 審核系統技術細節感興趣,歡迎溝通。

活動推薦:

ArchSummit全球架構師峰會(深圳站)2020,精選100+國內外專家技術實踐落地案例,AIOps、微服務架構、數據中台、大前端趨勢等等熱門技術,歡迎推薦或自薦。