阿里云丨耗時(shí)又繁重的SQL診斷優(yōu)化,阿里sql優(yōu)化-ESG跨境

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

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

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

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

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

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

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

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

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

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

按照經(jīng)驗(yàn)，約80%的數(shù)據(jù)庫(kù)性能問(wèn)題能通過(guò)SQL優(yōu)化手段解決，但SQL優(yōu)化一直以來(lái)都是一個(gè)非常復(fù)雜的過(guò)程，需要多方面的數(shù)據(jù)庫(kù)領(lǐng)域?qū)＜抑R(shí)和經(jīng)驗(yàn)。

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

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

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

接下來(lái)我們針對(duì)DAS的SQL診斷優(yōu)化服務(wù)能力構(gòu)建進(jìn)行詳細(xì)的解讀。

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

當(dāng)我們提到診斷優(yōu)化能力時(shí)，很自然會(huì)想到兩個(gè)問(wèn)題：

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

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

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

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

SELECT*FROMt1WHEREtimecreated=2017125ANDconsumingtime1000ORDERBYconsumingtimeDESC

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

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

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

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

WHATIF內(nèi)核能力缺失：無(wú)法復(fù)用內(nèi)核的數(shù)據(jù)庫(kù)優(yōu)化器能力來(lái)對(duì)候選優(yōu)化方案進(jìn)行代價(jià)量化評(píng)估；

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

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

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

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

長(zhǎng)期以來(lái)，SQL診斷優(yōu)化能力的構(gòu)建一直都是頗具挑戰(zhàn)性的課題，挑戰(zhàn)不僅在于如何將據(jù)庫(kù)優(yōu)化領(lǐng)域?qū)＜抑R(shí)融入，還包括如何構(gòu)建一個(gè)龐大的測(cè)試案例庫(kù)用于其核心能力驗(yàn)證，它就像一把尺子可以衡量能力，同時(shí)又可以以此為驅(qū)動(dòng)，加速能力的構(gòu)建，因此在整個(gè)過(guò)程中，擁有足夠覆蓋度，準(zhǔn)確的測(cè)試案例庫(kù)是能力構(gòu)建過(guò)程中至關(guān)重要的一環(huán)。

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

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

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

a)schema設(shè)計(jì)：如表、已有索引、約束等；

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

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

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

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

02、能力構(gòu)建

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

2.1核心技術(shù)架構(gòu)

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

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

SQL解析與驗(yàn)證：引擎對(duì)查詢語(yǔ)句做解析驗(yàn)證，驗(yàn)證輸入查詢語(yǔ)句是否符合標(biāo)準(zhǔn)，識(shí)別查詢語(yǔ)句的組成形成語(yǔ)法樹，例如：謂詞以及謂詞類型、排序字段、聚合字段、查詢字段等，識(shí)別查詢語(yǔ)句相關(guān)字段的數(shù)據(jù)類型。驗(yàn)證SQL使用到的表、字段是否符合目標(biāo)數(shù)據(jù)庫(kù)的結(jié)構(gòu)設(shè)計(jì)。

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

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

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

2.2能力測(cè)試集構(gòu)建

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

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

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

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

最后通過(guò)“測(cè)試用例形式化特征庫(kù)”和“測(cè)試案例庫(kù)”的特征比對(duì)，可實(shí)現(xiàn)測(cè)試完備度和覆蓋度的評(píng)估，例如：

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

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

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

4）各能力級(jí)的測(cè)試用例覆蓋率怎樣

03、真金不怕火練

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

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

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

3年多來(lái)，SQL診斷成功率保持在98%以上，針對(duì)慢SQL的推薦率超過(guò)75%。

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

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

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

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