阿里云丨耗時又繁重的SQL診斷優(yōu)化,阿里sql優(yōu)化

阿里云丨耗時又繁重的SQL診斷優(yōu)化

作者：斯干，阿里云數(shù)據(jù)庫高級技術專家

在我們業(yè)務系統(tǒng)中，數(shù)據(jù)庫越來越扮演著舉足輕重的角色。

和其它公司一樣，在阿里巴巴業(yè)務場景下，大部分業(yè)務跟數(shù)據(jù)庫有著非常緊密的關系，數(shù)據(jù)庫一個微小的抖動都有可能對業(yè)務造成非常大的影響，如何讓數(shù)據(jù)庫更穩(wěn)定，得到持續(xù)優(yōu)化一直都是非常重要的訴求。

數(shù)據(jù)庫環(huán)境下的業(yè)務優(yōu)化，通常會提到三個層面：

1）應用層面優(yōu)化：應用代碼邏輯優(yōu)化，以更高效的方式處理數(shù)據(jù)；

2）實例層面優(yōu)化：通過環(huán)境參數(shù)調整，優(yōu)化實例的運行效率；

3）SQL層面優(yōu)化：通過物理數(shù)據(jù)庫設計、SQL語句改寫等優(yōu)化手段，確保以最佳的方式獲取數(shù)據(jù)。

開發(fā)者通常對于前面兩個比較熟悉，對于第三個即SQL層面的優(yōu)化會有些生疏，甚至會因由誰（數(shù)據(jù)庫管理員或應用開發(fā)者）來負責而產生爭論，但SQL優(yōu)化是整個數(shù)據(jù)庫優(yōu)化中非常關鍵的一環(huán)，線上SQL性能問題不僅會給業(yè)務帶來執(zhí)行效率上的低下，甚至是穩(wěn)定性上的故障。

按照經驗，約80%的數(shù)據(jù)庫性能問題能通過SQL優(yōu)化手段解決，但SQL優(yōu)化一直以來都是一個非常復雜的過程，需要多方面的數(shù)據(jù)庫領域專家知識和經驗。

例如如何準確地識別執(zhí)行計劃中的瓶頸點，通過優(yōu)化物理庫設計或SQL改寫等手段，讓數(shù)據(jù)庫優(yōu)化器回歸到最佳執(zhí)行計劃，另外，由于SQL工作負載及其基礎數(shù)據(jù)龐大且不斷變化，SQL優(yōu)化還是一項非常耗時繁重的任務，這些都決定了SQL優(yōu)化是一項高門檻，高投入的工作。

SQL診斷優(yōu)化服務是阿里云數(shù)據(jù)庫自治服務（DAS）中最為核心的服務之一，它以SQL語句作為輸入，由DAS完成診斷分析并提供專家優(yōu)化建議（包括索引建議、語句優(yōu)化建議以及預期收益等信息），用戶不必精通數(shù)據(jù)庫優(yōu)化領域專家知識，即可獲得SQL優(yōu)化診斷、改寫和優(yōu)化相關的專家建議，最大化SQL執(zhí)行性能。

另外，依托該能力，DAS的SQL自動優(yōu)化服務將SQL優(yōu)化推向了更高的境界，將重人工的被動式優(yōu)化轉變?yōu)橐灾悄芑癁榛A的主動式優(yōu)化，以自優(yōu)化的自治能力實現(xiàn)SQL優(yōu)化的無人值守。

接下來我們針對DAS的SQL診斷優(yōu)化服務能力構建進行詳細的解讀。

01、面臨的挑戰(zhàn)

當我們提到診斷優(yōu)化能力時，很自然會想到兩個問題：

能力是否靠譜能力是否全面

確實如此，完美地回答這兩個問題將面臨非常巨大的挑戰(zhàn)，現(xiàn)將其歸納為如下四點：

挑戰(zhàn)一：如何選擇靠譜的優(yōu)化推薦算法生成靠譜的建議

在SQL診斷優(yōu)化領域，基于規(guī)則方式和基于代價模型方式是兩種常被選擇的優(yōu)化推薦算法，在目前許多產品和服務中，基于規(guī)則的推薦方式被廣泛使用，特別是針對MySQL這種WHATIF內核能力缺失的數(shù)據(jù)庫，因為該方式相對來說比較簡單，容易實現(xiàn)，但另一面也造成了推薦過于機械化，推薦質量難以保證的問題，舉一個例子，例如對如下簡單的SQL進行索引的推薦:

SELECT*FROMt1WHEREtimecreated=2017125ANDconsumingtime1000ORDERBYconsumingtimeDESC

基于規(guī)則，通常會首先生成如下四個候選索引：

IX1(timecreated)IX2(timecreated,consumingtime)IX3(consumingtime)IX4(consumingtime,timecreated)

但最終推薦給用戶的是哪個（或哪幾個，考慮index oring/anding的情況）索引呢基于規(guī)則的方式很難給出精確的回答，會出現(xiàn)模棱兩可的局面。在這個例子中，SQL只是簡單的單表查詢，那對于再復雜一點的SQL,例如多個表Join,以及帶有復雜的子查詢，情況又會如何呢情況變得更糟糕，更加難以為繼。

與此不同，DAS中的SQL診斷優(yōu)化服務采用的是基于代價模型方式實現(xiàn)，也就它采用和數(shù)據(jù)庫優(yōu)化器相同的方式去思考優(yōu)化問題，最終會以執(zhí)行代價的方式量化評估所有的（或盡可能所有的，因為是最優(yōu)解求解的NP類問題，因此在一些極端情況下無法做到所有，只是實現(xiàn)次優(yōu)）可能推薦候選項，最終作出推薦。即便是如此，但對于MySQL這樣的開源數(shù)據(jù)庫支持，還將面臨其它不一樣的挑戰(zhàn)：

WHATIF內核能力缺失：無法復用內核的數(shù)據(jù)庫優(yōu)化器能力來對候選優(yōu)化方案進行代價量化評估；

統(tǒng)計信息缺失：候選優(yōu)化方案的代價評估，其本質是執(zhí)行計劃的代價計算，統(tǒng)計信息的缺失便是無米之炊。

挑戰(zhàn)二：如何具備足夠的SQL兼容性

SQL診斷優(yōu)化服務如何做到SQL兼容性，其中包括SQL的解析以及SQL語義的驗證，這直接關系到能力的全面性，診斷的成功率，它就像入場券，做不到做不全面都是問題。

挑戰(zhàn)三：如何構建具有足夠覆蓋度的能力測試集

長期以來，SQL診斷優(yōu)化能力的構建一直都是頗具挑戰(zhàn)性的課題，挑戰(zhàn)不僅在于如何將據(jù)庫優(yōu)化領域專家知識融入，還包括如何構建一個龐大的測試案例庫用于其核心能力驗證，它就像一把尺子可以衡量能力，同時又可以以此為驅動，加速能力的構建，因此在整個過程中，擁有足夠覆蓋度，準確的測試案例庫是能力構建過程中至關重要的一環(huán)。

但構建足夠好的測試案例庫是一件非常困難的事情，挑戰(zhàn)主要體現(xiàn)在兩個方面：

足夠完備性保證：影響SQL優(yōu)化的因素很多，例如影響索引選擇的因素有上百個，加之各因素之間形成組合，這就形成了龐大的案例特征集合，如何讓這些特征一一映射到測試案例也是非常龐大的工程；

測試案例設計需要專業(yè)知識且信息量大，例如對于單一測試案例設計也需要專業(yè)知識且測試案例中攜帶的信息量大，如索引推薦測試案例，它包括：

a)schema設計：如表、已有索引、約束等；

b）各類統(tǒng)計信息數(shù)據(jù)；

c）環(huán)境參數(shù)等等。

挑戰(zhàn)四：如何構建大規(guī)模的診斷服務能力

SQL診斷優(yōu)化服務需要具備服務于云上百萬級數(shù)據(jù)庫實例的能力，其線上服務能力同樣面臨巨大挑戰(zhàn)，例如如何實現(xiàn)復雜的計算服務服務化拆分，計算服務的橫向伸縮，最大化的并行，資源訪問分布式環(huán)境下的并發(fā)控制，不同優(yōu)先級的有效調度消除隔離，峰值緩沖等等。

02、能力構建

面對上面提到的眾多挑戰(zhàn)，下面著重從DAS中的SQL診斷優(yōu)化引擎核心技術架構以及能力測試集的構建兩個維度進一步解讀。

2.1核心技術架構

圖1:SQL診斷優(yōu)化引擎核心架構

上圖1是SQL診斷優(yōu)化引擎的核心架構，它實現(xiàn)一套獨立于數(shù)據(jù)庫之外的優(yōu)化器，包括自適應的統(tǒng)計信息收集以及執(zhí)行計劃的代價計算，以此為基礎彌補WHATIF內核能力缺失，自適應的統(tǒng)計信息收集彌補統(tǒng)計信息缺失。其具體的工作過程如下：

SQL解析與驗證：引擎對查詢語句做解析驗證，驗證輸入查詢語句是否符合標準，識別查詢語句的組成形成語法樹，例如：謂詞以及謂詞類型、排序字段、聚合字段、查詢字段等，識別查詢語句相關字段的數(shù)據(jù)類型。驗證SQL使用到的表、字段是否符合目標數(shù)據(jù)庫的結構設計。

候選索引生成：依據(jù)解析驗證后的語法樹，生成多種候選索引組合；

基于代價評估：代價評估基于內置獨立于數(shù)據(jù)庫內核的優(yōu)化器，獲取數(shù)據(jù)庫統(tǒng)計信息，在診斷引擎內部作緩存。診斷引擎內置優(yōu)化器基于統(tǒng)計信息計算代價，評估每個索引的代價以及不同SQL改寫方法下的代價評估，從而從代價選擇最優(yōu)索引或SQL改寫方法。

索引合并與擇優(yōu)：引擎輸入可以是一條查詢語句，也可以為多個查詢語句，或者整個數(shù)據(jù)庫實例所有的查詢語句。為多個查詢語句做索引推薦，不同的查詢語句的索引建議，以及已經存在的物理索引，有可能存在相同索引、前綴相同索引、雷同索引。

2.2能力測試集構建

如前面有關挑戰(zhàn)性章節(jié)所述，我們的目標是構建具有足夠覆蓋度的能力測試集，并以此為尺，度量能力，驅動能力構建。在這一過程中，如下圖2所示，我們構建了以用例系統(tǒng)為中心的開發(fā)模式。

圖2:案例系統(tǒng)

能力測試集構建的基本思想，首先通過特征化實現(xiàn)測試案例基于特征的形式化描述，形成測試案例形式化特征庫，并具備足夠的完備性；

在阿里巴巴集團內部，我們已經對全網(wǎng)數(shù)據(jù)庫實例上全部SQL進行實時采集和存儲，借助阿里巴巴這個大平臺業(yè)務的豐富性和SQL場景的豐富行，以特征化形式描述為抓手對線上海量全量SQL資源分析搜尋符合指定特征的真實案例，抽取測試案例所需的信息（注：案例庫的數(shù)據(jù)均來自阿里巴巴集團內部業(yè)務，所涉及的線上抽取信息，如統(tǒng)計信息，均經過加密脫敏處理，此過程為無人參與的全自動化過程），最終完成測試案例庫構建。

最后通過“測試用例形式化特征庫”和“測試案例庫”的特征比對，可實現(xiàn)測試完備度和覆蓋度的評估，例如：

1）哪些測形式化特征測試用例已被測試用例覆蓋，完備度是多少

2）哪些形式化特征測試用例，當前的診斷優(yōu)化能力未覆蓋或測試驗證失敗

3）在一段時間哪些測形式化特征測試用例出現(xiàn)頻繁的回歸問題

4）各能力級的測試用例覆蓋率怎樣

03、真金不怕火練

DAS的SQL診斷優(yōu)化服務云上發(fā)布前，已在阿里巴巴集團內部穩(wěn)定運行將近3年多時間，日平均診斷量在5萬左右，很好地支撐著整個集團業(yè)務應用的SQL優(yōu)化，使用場景應用場景主要包括：

1、自助優(yōu)化：集團用戶指定問題SQL,服務完成診斷并提供優(yōu)化專家建議；

2、自動優(yōu)化：自動優(yōu)化服務自動識別業(yè)務數(shù)據(jù)庫實例工作負載上的慢查詢，主動完成診斷，生成優(yōu)化建議，評估后編排優(yōu)化任務，自動完成后續(xù)的優(yōu)化上線操作及性能跟蹤，形成全自動的優(yōu)化閉環(huán)，提升數(shù)據(jù)庫性能，持續(xù)保持數(shù)據(jù)庫實例運行在最佳優(yōu)化狀態(tài)。

3年多來，SQL診斷成功率保持在98%以上，針對慢SQL的推薦率超過75%。

截止到2020年3月底，自動SQL優(yōu)化已累計優(yōu)化超4200萬慢SQL，集團全網(wǎng)慢SQL下降92%左右。

更為重要的是，SQL診斷優(yōu)化服務已經構建了有效的主動式分析，反饋系統(tǒng)，線上診斷失敗案例，用戶反饋案例，自動優(yōu)化中的回滾案例會自動回流到案例系統(tǒng)，一刻不停地驅動著診斷服務在快速迭代中成長。

文章推薦
迎戰(zhàn)電商旺季!跨境電商賣家需要把控好哪些環(huán)節(jié),跨境電商打包啥時候是招聘旺季
TikTok Ads出價方式最佳實踐,tiktok challenge
whatsapp網(wǎng)頁版為什么比手機版好用,whatsapp商業(yè)版和個人版
WhatsApp開發(fā)客戶技巧,whatsapp開發(fā)客戶技巧

特別聲明：以上文章內容僅代表作者本人觀點，不代表ESG跨境電商觀點或立場。如有關于作品內容、版權或其它問題請于作品發(fā)表后的30日內與ESG跨境電商聯(lián)系。