簡介：?第十一屆中國數據庫技術大會（DTCC2020），在北京隆重召開。在12.23日性能優化與SQL審計專場上，邀請了阿里巴巴數據庫技術團隊高級技術專家梁高中為大家介紹DAS之基于Workload的全局自動優化實踐。 SQL自動優化是阿里云數據庫自治服務重要自治場景之一，該服務支撐阿里巴巴集團全網慢SQL的自動優化，目前已累計自動優化超4900萬慢SQL。阿里在構建這一能力過程中有經驗也有教訓，期望從基于Workload的全局優化能力構建歷程、智能化自動優化閉環實踐兩個方面和大家分享。

SQL自動優化是阿里云數據庫自治服務重要自治場景之一，該服務支撐阿里巴巴集團全網慢SQL的自動優化，目前已累計自動優化超4900萬慢SQL。阿里在構建這一能力過程中有經驗也有教訓，期望從基于Workload的全局優化能力構建歷程、智能化自動優化閉環實踐兩個方面和大家分享。

演講嘉賓簡介：

梁高中，阿里巴巴數據庫技術團隊高級技術專家，2017年加入阿里巴巴集團，目前負責阿里巴巴阿里云數據庫自治服務研發負責人。加入阿里巴巴前，曾就職于IBM，華為等，擁有12+年的數據庫產品、數據庫優化經驗，曾擔任數據庫優化專家系統，跨源跨數據中心聯邦數據庫等開發團隊負責人。

以下內容根據演講視頻以及PPT整理而成。

本次分享主要圍繞以下三個方面：
一、SQL優化場景
二、核心診斷能力構建
三、自動優化閉環

一、SQL優化場景

1. SQL優化挑戰

數據庫診斷優化是提高數據庫性能和穩定性的關鍵技術之一， SQL優化是其中至關重要的一環。目前約80%的數據庫性能問題可通過SQL優化手段解決。SQL優化目前還是面臨著很多挑戰，首先，SQL優化需要基于多方面的數據庫領域專家知識和經驗。而且SQL優化耗時繁重，當面臨如阿里這樣的大規模的業務場景時，SQL持續優化充滿挑戰。下圖中有一個基于真實業務數據所畫出的，隨時間變化的數據庫慢SQL趨勢圖
，T1代表著發現數據庫實例因慢SQL造成性能異常的時間點，而T2表示優化過程結束，恢復常態時間點。那么T1越短表示發現性能異常的耗費時間越少。其次T2-T1時間是異常處理時長，如果處理時間過長，一方面會嚴重影響業務，另一方面大大增加故障風險。

2. SQL優化三大場景

如果將SQL優化功能提供給用戶，主要涉及三種場景。首先是單SQL工具輔助診斷。用戶可以選擇以單SQL為輸入，輔助診斷工具會根據給定SQL及相關環境信息，給出優化建議（改寫、最優索引建議等），最大化加速查詢。還有基于負載全局輔助診斷工具，主要以Workload負載為優化單位，綜合考慮Workload中影響整體性能的特征，實現負載整體性能最大化提升同時最大化降低空間消耗。這兩個場景以輔助決策方式，為用戶提供SQL診斷和優化。還有一種場景是自動SQL優化，通過構建完善的自動化流程，實現問題SQL識別、優化建議生成、評估自動上線，后續跟蹤、收益計算的全自動化流程。

二、核心診斷能力構建

支持SQL優化，就需要對核心診斷能力進行構建。那什么是核心診斷能力？即針對問題SQL，給出非常準確的建議。用戶通常會遇到下面幾種SQL優化問題。

1. 單SQL優化診斷

SQL優化的本質是創造條件，發現可以提升的點，如SQL改寫，創建SQL索引等，從而讓數據庫優化器選擇最優或者次優的SQL執行計劃。下圖中間核心位置的是SQL優化引擎，兩邊是從核心能力衍生出的對外場景，左邊是對外提供的SQL自動優化的閉環，右邊是為用戶提供的SQL優化建議。那么單SQL優化診斷能力的構建面臨幾個主要的問題，首先是應該采用哪種優化推薦算法？是基于規則方式還是基于代價模型方式？針對WHAT-IF內核能力缺失的數據庫，應該如何選擇？第二點，足夠覆蓋度的測試集，既如何構建一個龐大的測試案例庫用于其核心能力驗證？擁有足夠覆蓋度，因為準確的測試案例庫往往是核心診斷能力構建過程中至關重要的一環。第三點，如何在大規模業務場景下提供診斷服務能力，阿里需要服務于云上幾十萬級的數據庫實例的SQL優化診斷，那么如何實現復雜的計算服務服務化拆分，計算服務的橫向伸縮，最大化的并行，資源訪問分布式環境下的并發控制，不同優先級的有效調度消除隔離，峰值緩沖等等？第四點，如何讓SQL診斷能力持續改進。

單SQL優化診斷 —— 優化推薦算法選擇·面臨挑戰

第一類推薦算法是基于規則式的，其明顯的特點是基于事先編輯好的規則來優化。第二類是基于代價評估方式。下圖左側是目前傳統商業化最優索引推薦引擎架構，SQL導入之后，對其進行分析，生成候選索引。然后通過代價評估，這時會通過數據庫服務器WHAT-IF能力獲得這些候選索引的代價。基于WHAT-IF接口返回的結果進行代價評估，最后進行最終的索引合并擇優。這是傳統數據庫中基于代價評估的最優索引推薦流程。但是，對于例如MySQL這樣的數據庫引擎，這個過程中還是面臨幾個挑戰：
挑戰一：在MySQL中WHAT-IF功能是缺失的；
挑戰二：MySQL中沒有完整的統計信息可使用；

因此需要對此架構進行優化，既在SQL引擎和數據庫服務器間加一個內置優化器，通過內置優化器提供WHAT-IF功能。但這種架構依然會面臨幾個挑戰：

挑戰三：如何最大限度縮小兩個優化器的差距；
挑戰四：內置優化器中的統計信息與MySQL中的統計信息存在差異，那么應該如何縮小或者優化它們之間的統計信息的差異？

單SQL優化診斷 —— 優化推薦算法選擇·基于代價評估方式

首先在內置優化器部分，阿里會在物理計劃基礎上進行代價評估，然后從中選擇。這里與傳統數據庫中的優化器不同點在于加入候選索引、SQL改寫的考量。另外，優化器是基于統計信息進行代價計算，因此在統計信息問題上采用了自適應采樣算法，自適應采樣實現在指定誤差范圍內自適應決定數據采樣量。還需要注意的一點是數據采樣的過程不能對目標數據庫實例造成太大的壓力。

單SQL優化診斷 —— 足夠覆蓋度的測試集·整體思路

為了保證SQL優化引擎覆蓋足夠全面，那么就需要足夠的測試集。選擇測試集時會面臨三個問題，首先在選擇的測試集中要包含什么樣的測試案例？第二點，多少測試案例能夠證明已經足夠全面？第三點，目前SQL優化引擎的能力在什么位置？測試集的選擇之所以困難是因為影響SQL優化的因素太多， 如何讓這些特征一一映射到測試案例也是較為龐大的工程。還有，測試案例設計需要專業知識且信息量大，對于單一測試案例設計也需要專業知識且測試案例中攜帶的信息量大。

測試案例覆蓋度分析報告是通過下圖右側的流程來生成，首先是分析影響SQL優化的因素，將其分解為多維度的測試案例特征集。之后通過特征形式化描述，生成測試案例形式化特征庫。之后借助阿里豐富的業務場景，收集線上全量SQL及全量慢SQL。然后結合形式化的特征，抽取線上測試案例，生成測試案例庫。最后結合測試案例運行系統和測試案例分析工具，評估測試案例覆蓋度，生成分析報告。整個過程中首先是在對多維度特征進行形式化轉化，然后通過線上資源構建通往引擎測試集的橋梁，另外，對引擎測試集構建查漏補缺的一把尺子。

單SQL優化診斷 —— 足夠覆蓋度的測試集·測試用例特征化

下圖展示了測試用例特征化的結構。首先從影響索引選擇的因素出發，列出這些因素。然后將SQL分為Single Table 和Multi Table兩個場景，分別從影響因素往下分SQL語句。再通過三種場景，完成特征集到能力級的映射。

這三種場景分別是L1、L2、L3。L1支持對核心標簽謂詞部分、聚合排序部分做全排列，保證非核心標簽被覆蓋，對謂詞聚合排序做粗粒度排列組合。L2包括對LIMIT的支持、NOT謂詞、聚合支持、函數支持、OR謂詞的支持、兩表的INNER JOIN、單表或兩表的UNION、SUBQUERY支持、隱式轉換等。L3包括三表到五表的INNER JOIN、UNION、SUBQUERY、LEFT/RIGHT JOIN、NATUAL JOIN等。

單SQL優化診斷 —— 大規模診斷能力與數據驅動

支持大規模的業務場景的診斷服務，SQL優化策略的實踐還需要完成很多的事情。首先對計算服務進行拆分、保證計算服務橫向伸縮、還要有效保證并行采樣效率、控制資源并發訪問、消除優先級調度隔離、緩沖業務峰值。這樣才能滿足在線上支持大規模業務場景的SQL優化的應用。

2. 基于Workload全局優化

上面一直在討論對單SQL的優化策略，那么從支持業務角度而言，還是需要從全局出發，做全局優化。全局優化是以Workload負載為優化單位，綜合考慮Workload中影響整體性能的特征，實現負載整體性能最大化提升，同時最大化降低空間消耗。如下圖左側，從全量SQL中提取Workload負載情況，通過SQL全局優化引擎，在考慮存儲約束條件S，以及成本約束條件C的情況下，輸出需要創建的新索引、需要改寫的新索引、需要刪除的新索引、并提供SQL改寫建議。

下圖左側的表格里是一系列簡單的SQL語句和Workload特征，包括INSERT語句，SELECT語句，在每個時間段內執行次數。如果從單SQL優化的角度，會推薦SQL2-SQL6的四條優化語句。但是從Workload全局優化角度考慮會推薦兩項SQL優化。Workload全局優化相比與單SQL優化整體RT下降了14.45%，索引空間節省了50%。

三、SQL自動優化閉環

1. SQL自動優化閉環 —— 實踐效果

SQL自動優化閉環指的是從問題SQL識別到基于Workload全局優化建議自動生成與評估、優化上線再到量化追蹤評估的全自動優化閉環。自動優化閉環將人工的被動式優化轉變為以智能化為基礎的主動式優化。下圖左側展示了整個SQL自動優化閉環的幾個關鍵優化節點。首先是持續24小時的跟蹤，進行指標異常檢測和Workload異常檢測，發現異常點。之后通過SQL優化引擎，給出優化建議。如果用戶采納自動優化建議，則灰度上線。如果不采納，則需要通過智能壓測驗證，再到灰度上線，然后進行優化效果跟蹤。
阿里實現了SQL優化的全自動化閉環，自動SQL優化持續保持數據庫實例運行在最佳優化狀態，目前阿里內部自動優化了4900萬慢SQL，全網慢SQL顯著下降了92%，全網慢SQL推薦率達到了75%。自動優化閉環在云上輔助自治了30萬多的服務實例，全網實例月增長率達到90%。SQL自動優化閉環希望從規模性、精準性、安全性、全面性、聯動性等方面持續優化提升，服務更多用戶。

2. SQL自動優化閉環 —— 生成基于壓測的優化收益報告

下圖左側是基于壓測的優化收益報告。根據SQL優化引擎生成的SQL優化的建議，選取用戶真實的負載數據情況，進行壓測。壓測完成之后生成在真實的場景下對優化建議的綜合評估，分析優化收益。

3. SQL自動優化閉環 —— 演示復盤

SQL優化為用戶提供了豐富的測試場景，基于SQL自動優化只是其中一個場景。那如何將SQL自動優化與其它測試場景混合到一起？這又將產生什么奇妙的效果？同時可以解決哪些問題？
下圖展示了隨時間變化的數據庫性能變化圖，以及過程中SQL自動優化做的事情。圖中黃色線條是活躍會話數，深藍色線條表示CPU利用率，淺藍色線條是IOPS利用率。第一個階段是橙黃色部分，既在2020年9月3日21:06 數據庫出現異常，此時可以1分鐘內發現異常、2分鐘內定位異常，并自動發現SQL限流，然后限流生效，黃色活躍會話數回歸原位，深藍色CPU利用率下降，業務恢復正常。到第二階段綠色部分SQL自動優化啟動，在2020年9月3日21:17 發起異常SQL優化診斷，緊接著優化索引變更上線，索引變更結束，進行24小時跟蹤，然后解除限流。隨即推出規格升配（Autoscaling）建議，根據負載的變
化升級數據庫規格。

作者：stromal

原文鏈接

本文為阿里云原創內容，未經允許不得轉載

?

總結

以上是生活随笔為你收集整理的DTCC 2020 | 阿里云梁高中：DAS之基于Workload的全局自动优化实践的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。