SQL是一種簡(jiǎn)單易用的業(yè)務(wù)邏輯表達(dá)語言，但隨著掃描數(shù)據(jù)量和查詢復(fù)雜度的增加，查詢性能會(huì)變得越來越慢。想要對(duì)SQL進(jìn)行調(diào)優(yōu)，往往需要關(guān)注以下幾個(gè)部分：

需要了解引擎架構(gòu)：用戶往往需要對(duì)SQL引擎的架構(gòu)特點(diǎn)有一定的了解，才能和業(yè)務(wù)的數(shù)據(jù)分布特征以及業(yè)務(wù)場(chǎng)景特征完美結(jié)合，來進(jìn)行數(shù)據(jù)建模，從而設(shè)計(jì)出符合SQL引擎架構(gòu)特點(diǎn)的表結(jié)構(gòu)。

SQL特征差異較大：即席查詢的SQL往往變化較大，包括參與Join的表個(gè)數(shù)、Join條件、分組聚合的字段個(gè)數(shù)以及過濾條件等。

數(shù)據(jù)特征差異較大：用戶的數(shù)據(jù)分布特征是會(huì)隨著業(yè)務(wù)特征的變化而變化的，如果一直按照最初的建模方式和SQL語句，也是無法保障能發(fā)揮出SQL引擎的最大優(yōu)勢(shì)，數(shù)據(jù)特征或者業(yè)務(wù)模型的變化，都會(huì)導(dǎo)致SQL性能回退。

基于此，AnalyticDB For MySQL（新一代云原生實(shí)時(shí)數(shù)據(jù)倉庫，語法兼容MySQL，以下簡(jiǎn)稱ADB）為用戶提供了高效、實(shí)時(shí)、功能豐富并且智能化的「SQL智能診斷」和「SQL智能調(diào)優(yōu)」功能，提供用戶SQL性能調(diào)優(yōu)的思路、方向和具體的方法，降低用戶使用成本，提高用戶使用ADB的效率。

下面我們通過「發(fā)現(xiàn)慢查詢」+「診斷慢查詢」兩個(gè)步驟，并結(jié)合一個(gè)場(chǎng)景Case，來介紹ADB新發(fā)布的「SQL智能診斷」功能。（PS：「SQL智能調(diào)優(yōu)」會(huì)在后續(xù)版本中發(fā)布）

一、發(fā)現(xiàn)慢查詢

用戶要定位慢查詢，首先需要找到慢查詢。ADB的用戶控制臺(tái)提供了多樣的方式來幫助用戶，例如「甘特圖」和「查詢列表」等，都可以在多個(gè)維度進(jìn)行檢索，幫助用戶快速定位慢查詢，而且診斷工具確保用戶可以進(jìn)行最近兩周的全量查詢檢索和分析。

（一）甘特圖

用戶可以通過「集群控制臺(tái)」-「診斷與優(yōu)化」 - 「SQL診斷」進(jìn)入SQL智能診斷功能。

首先會(huì)看到一個(gè)甘特圖（也稱泳道圖，查詢從不同的泳道流過，這里的泳道并不是ADB的查詢隊(duì)列，只是為了區(qū)分開不同時(shí)間執(zhí)行的查詢），甘特圖以圖形化的方式形象的展示了查詢?cè)贏DB實(shí)例上的執(zhí)行順序，每個(gè)色塊表示了一條查詢，色塊左側(cè)為查詢的提交時(shí)間，色塊右側(cè)為查詢的結(jié)束時(shí)間，色塊的相對(duì)長(zhǎng)度表示了某條查詢的執(zhí)行時(shí)間，色塊的顏色沒有特殊含義，只是為了區(qū)分不同的泳道。

通過甘特圖，用戶可以非常直觀的看到當(dāng)前時(shí)間范圍內(nèi)執(zhí)行耗時(shí)較長(zhǎng)的查詢，并且可以直觀的看到哪些查詢是在并行的執(zhí)行以及并行執(zhí)行的時(shí)間段，這可以幫助用戶判斷出哪些查詢是受到了某條Bad SQL的影響。色塊的密集度則可以用來直觀的判斷ADB實(shí)例壓力較大的時(shí)段是否和某些查詢的并發(fā)度較高相關(guān)。

（二）查詢列表

甘特圖能夠以直觀的方式體現(xiàn)出查詢之間的時(shí)間相關(guān)性，但是當(dāng)用戶選擇的時(shí)間范圍較大，甘特圖中的色塊會(huì)密集分布不容易分辨，而且甘特圖上的指標(biāo)較為有限，此時(shí)用戶可以使用診斷工具中的查詢列表功能。查詢列表提供了多大10余項(xiàng)查詢級(jí)別的重要指標(biāo)，例如數(shù)據(jù)庫名、用戶名、客戶端段IP、耗時(shí)、消耗內(nèi)存以及掃描量等，這些信息和指標(biāo)可以幫助用戶進(jìn)一步判斷慢查詢的來源和資源消耗等。

高級(jí)檢索能力方面，診斷工具提供了三種類型的檢索方法：

1.模糊檢索和精確檢索：用戶可以根據(jù)SQL中的關(guān)鍵字進(jìn)行模糊匹配，精確檢索功能則幫助用戶在確定查詢ID的情況下，精確的檢索到這條查詢；
2.字符串類型的檢索條件：檢索工具會(huì)自動(dòng)識(shí)別出用戶所選時(shí)間范圍內(nèi)使用的數(shù)據(jù)名、用戶名、客戶端IP以及資源組等，提供下拉框的形式供用戶選擇，提高用戶檢索效率；
3.數(shù)值類型的檢索條件：用戶可以自由選擇檢索的指標(biāo)單位，例如KB、MB、GB等，不需要進(jìn)行手動(dòng)的換算。

同時(shí)，用戶在使用診斷工具時(shí)，往往有對(duì)慢查詢的下載需求，下載后的慢查詢可以在例如Excel等工具中進(jìn)行更多自定義的慢查詢治理和分析，所以我們也提供了查詢列表的下載功能。

二、診斷慢查詢

（一）查詢?cè)贏DB中的執(zhí)行流程

在介紹ADB執(zhí)行流程前需要簡(jiǎn)單介紹一下三個(gè)相關(guān)的基本概念：

Stage

在執(zhí)行階段，ADB中的查詢會(huì)首先根據(jù)是否產(chǎn)生Shuffle被切分為多個(gè)Stage來執(zhí)行，一個(gè)Stage就是執(zhí)行計(jì)劃中某一部分的物理實(shí)體。Stage的數(shù)據(jù)來源可以是底層存儲(chǔ)系統(tǒng)中的數(shù)據(jù)或者網(wǎng)絡(luò)中傳輸?shù)臄?shù)據(jù)，一個(gè)Stage由分布在不同計(jì)算節(jié)點(diǎn)上相同類型的Task組成，多個(gè)Task會(huì)并行處理數(shù)據(jù)。

Task

Task是一個(gè)Stage在某個(gè)Executor節(jié)點(diǎn)上的執(zhí)行實(shí)體，多個(gè)同類型的Task組成一個(gè)Stage，在集群內(nèi)部并行處理數(shù)據(jù)。

Operator

Operator（算子）是ADB的最小數(shù)據(jù)處理單元。ADB會(huì)根據(jù)算子所表達(dá)的語義或算子間的依賴關(guān)系，決定使用并行還是串行執(zhí)行來處理數(shù)據(jù)。

下面以一個(gè)典型的分局聚合查詢?yōu)槔齺砹私釧DB中查詢的執(zhí)行流程，SQL語句如下：

SELECT COUNT(*), SUM(salary) 
 
FROM emplayee 
 
WHERE age>30 ADN age<32 
 
GROUP BY sex 

在ADB內(nèi)部，首先由前端的Controller節(jié)點(diǎn)接收SQL語句請(qǐng)求，并對(duì)SQL語句進(jìn)行語句解析和語法分析（Parser），最后使用優(yōu)化器（Optimizer）生成最終的執(zhí)行計(jì)劃，整體執(zhí)行計(jì)劃會(huì)根據(jù)Stage的劃分原則被切分為子計(jì)劃，如圖中的Plan0、Plan1和Plan2，分別被下發(fā)到對(duì)應(yīng)的節(jié)點(diǎn)上。

其中子計(jì)劃Plan2會(huì)并行的在4個(gè)計(jì)算節(jié)點(diǎn)上以Task實(shí)例的形式處理數(shù)據(jù)，首先執(zhí)行數(shù)據(jù)的掃描和過濾，然后執(zhí)行數(shù)據(jù)的局部聚合，處理完之后的數(shù)據(jù)會(huì)根據(jù)sex字段Repartition到下游的計(jì)算節(jié)點(diǎn)，即Stage1的節(jié)點(diǎn)，按照子計(jì)劃Plan1的要求執(zhí)行數(shù)據(jù)的最終聚合。最后，數(shù)據(jù)由Stage0的節(jié)點(diǎn)匯總并返回到客戶端。

和典型的數(shù)據(jù)倉庫一樣，ADB的執(zhí)行計(jì)劃一般分為「邏輯執(zhí)行計(jì)劃」和「物理執(zhí)行計(jì)劃」：

邏輯執(zhí)行計(jì)劃：宏觀層面了解查詢的處理流程

邏輯執(zhí)行計(jì)劃在較高的層面展示查詢的處理邏輯，基于規(guī)則的執(zhí)行計(jì)劃（RBO）會(huì)判斷過濾條件是否可以下推，而基于代價(jià)的執(zhí)行計(jì)劃（CBO）會(huì)判斷出多表關(guān)聯(lián)時(shí)的順序等。所以邏輯執(zhí)行計(jì)劃并不關(guān)注在物理執(zhí)行時(shí)的具體處理方式，例如是否在執(zhí)行時(shí)需要對(duì)多個(gè)算子進(jìn)行融合以減少函數(shù)調(diào)用，或者自動(dòng)生成代碼的粒度，這些邏輯執(zhí)行計(jì)劃并不關(guān)注，這也就導(dǎo)致了邏輯執(zhí)行計(jì)劃往往只包含了Stage級(jí)別的執(zhí)行統(tǒng)計(jì)信息。但是用戶調(diào)優(yōu)時(shí)往往是需要精確到算子級(jí)別的統(tǒng)計(jì)信息。

物理執(zhí)行計(jì)劃：微觀層面了解每個(gè)算子的處理性能

相對(duì)于邏輯執(zhí)行計(jì)劃，物理執(zhí)行計(jì)劃包含了算子間的數(shù)據(jù)處理流圖，也包含了算子的執(zhí)行統(tǒng)計(jì)信息，可以精確的看到某個(gè)Join算子或者聚合算子占用的內(nèi)存，也可以看到過濾算子過濾前后的數(shù)據(jù)量。但是并不是所有的算子用戶都需要能正確理解其含義，特別是有些物理算子和用戶的SQL語句找不到關(guān)聯(lián)之處，這也會(huì)給用戶單獨(dú)使用物理執(zhí)行計(jì)劃定位問題帶來較大的疑惑。

ADB的「SQL智能診斷」功能，提供給了用戶一個(gè)邏輯執(zhí)行計(jì)劃和物理執(zhí)行計(jì)劃的融合視圖，用戶使用融合的執(zhí)行計(jì)劃即可以從宏觀層面了解查詢的處理流程，也可以從微觀層面了解每個(gè)算子的處理性能，從而可以更加準(zhǔn)確快速的幫助用戶定位查詢的性能瓶頸點(diǎn)。

（二）SQL自診斷功能

雖然我們提供了融合的和分層的執(zhí)行計(jì)劃來幫助用戶分析查詢的性能問題，但是我們發(fā)現(xiàn)在兩類場(chǎng)景中用戶使用融合執(zhí)行計(jì)劃會(huì)遇到困難：

ADB的初級(jí)使用者

ADB為了減少M(fèi)ySQL用戶的學(xué)習(xí)和遷移成本，做到了絕大多數(shù)語法和MySQL兼容，但是ADB的后臺(tái)并非MySQL內(nèi)核，而是獨(dú)立自研的一套分布式數(shù)據(jù)存儲(chǔ)和分布式計(jì)算系統(tǒng)，面對(duì)ADB的執(zhí)行計(jì)劃，ADB的初級(jí)使用者往往不知道優(yōu)化的重點(diǎn)在哪里，無從下手。

ADB中的復(fù)雜SQL

對(duì)于復(fù)雜的SQL，往往涉及幾百張表的連接操作，Stage個(gè)數(shù)會(huì)達(dá)到幾百個(gè)以上，算子個(gè)數(shù)更是會(huì)達(dá)到上千，執(zhí)行計(jì)劃圖非常大，即使是ADB的高級(jí)使用者，面對(duì)這樣復(fù)雜的執(zhí)行計(jì)劃，往往也無從下手，如下圖是個(gè)196個(gè)Stage的執(zhí)行計(jì)劃圖：

針對(duì)以上問題，我們?cè)趫?zhí)行計(jì)劃圖中加入了SQL自診斷能力，SQL自診斷能力會(huì)將專家經(jīng)驗(yàn)以規(guī)則的形式體現(xiàn)在執(zhí)行計(jì)劃中，對(duì)于ADB的初次接觸者，即可以根據(jù)診斷結(jié)果確定查詢執(zhí)行過程中的性能瓶頸點(diǎn)，也可以根據(jù)診斷結(jié)果學(xué)習(xí)到ADB執(zhí)行計(jì)劃中需要關(guān)注的重點(diǎn)算子。針對(duì)復(fù)雜執(zhí)行計(jì)劃，SQL自診斷可以幫助用戶快速定位到執(zhí)行計(jì)劃中需要調(diào)優(yōu)的位置，并提供了調(diào)優(yōu)的相關(guān)方法和文檔，讓用戶在調(diào)優(yōu)過程中更有針對(duì)性。

SQL自診斷能力通過「Query級(jí)別診斷結(jié)果」、「Stage級(jí)別診斷結(jié)果」、「算子級(jí)別診斷結(jié)果」這三層來展示診斷結(jié)果和優(yōu)化建議。

我們以一個(gè)線上的復(fù)雜SQL為例來介紹使用執(zhí)行計(jì)劃和SQL自診斷工具來進(jìn)行性能問題定位的例子。首先我們通過慢查詢檢索工具搜索到一個(gè)內(nèi)存消耗較大的查詢，點(diǎn)擊「診斷」打開該查詢的診斷頁面，切換到「執(zhí)行計(jì)劃」頁簽，我們會(huì)看到查詢級(jí)別診斷結(jié)果已經(jīng)判斷出當(dāng)前查詢數(shù)據(jù)一個(gè)內(nèi)存消耗較大的查詢，如下圖中的1所示：

這時(shí)，我們需要定位內(nèi)存效果較大的原因，我們點(diǎn)擊按內(nèi)存排序，可以看到在右側(cè)，會(huì)根據(jù)Stage消耗的內(nèi)存百分比進(jìn)行了倒敘排序，可以非常明顯的看出，Stage[1]占用的當(dāng)前查詢87%以上的內(nèi)存比例，我們點(diǎn)擊Stage[1]，診斷工具會(huì)自動(dòng)聚焦到執(zhí)行計(jì)劃樹的Stage[1]的位置，點(diǎn)擊Stage[1]，我們可以看到Stage[1]的執(zhí)行統(tǒng)計(jì)信息，同時(shí)，我們可以看到在5的位置，提示我們當(dāng)前Stage1內(nèi)部有個(gè)算子占用內(nèi)存較大，但是并沒有詳細(xì)信息，所以接下來，我們需要進(jìn)入到Stage[1]的內(nèi)部，看看Stage[1]具體是哪個(gè)算子占用了較多內(nèi)存。

點(diǎn)擊「查看Stage執(zhí)行計(jì)劃」，進(jìn)入到Stage[1]內(nèi)部，首先，我們依然根據(jù)內(nèi)存排序，可以看到，其中的Join[317]這個(gè)算子占用了整個(gè)Stage 99%以上的內(nèi)存，點(diǎn)擊該算子，算子執(zhí)行計(jì)劃樹自動(dòng)定位到當(dāng)前算子，這時(shí)我們就可以看到算子診斷結(jié)果的詳細(xì)信息了，信息提示我們，在構(gòu)建Hash表用戶Left Join時(shí)，占用了較大的內(nèi)存，診斷結(jié)果還提供了官方的調(diào)優(yōu)文檔鏈接，根據(jù)文檔中給出的調(diào)優(yōu)方法，我們就可以減少該算子的內(nèi)存占用。

以上的例子通過「查詢級(jí)別診斷結(jié)果」和「算子級(jí)別診斷結(jié)果」進(jìn)行SQL性能問題定位的方法，我們?cè)賮砜匆粋€(gè)「Stage級(jí)別診斷結(jié)果」的例子。

如下圖所示，我們可以看到根據(jù)耗時(shí)排序后，Stage[10]的耗時(shí)比例最大，點(diǎn)擊執(zhí)行計(jì)劃圖中的Stage[10]，可以在診斷結(jié)果欄看到兩類診斷結(jié)果，一類是“Stage診斷”，一類是“算子診斷”，其中的Stage診斷告訴我們當(dāng)前Stage的輸出數(shù)據(jù)有傾斜，并且告訴我們傾斜的字段是哪些（數(shù)據(jù)傾斜是分布式系統(tǒng)中嚴(yán)重影響性能的問題，Stage輸出數(shù)據(jù)傾斜不但會(huì)當(dāng)時(shí)當(dāng)前Stage處理數(shù)據(jù)在時(shí)間上存在長(zhǎng)尾，而且會(huì)導(dǎo)致下游的數(shù)據(jù)處理存在長(zhǎng)尾），同時(shí)可以看到有一個(gè)算子診斷結(jié)果，提示我們表掃描存在傾斜，那么我們可以初步判斷當(dāng)前Stage輸出數(shù)據(jù)傾斜是因?yàn)閽呙枇艘粋€(gè)數(shù)據(jù)傾斜的表導(dǎo)致的。接下來我們進(jìn)入到Stage[0]內(nèi)部進(jìn)行定位和分析。

進(jìn)入到Stage內(nèi)存，我們根據(jù)耗時(shí)排序，可以看到TableScan算子耗時(shí)最大，這時(shí)我們點(diǎn)擊TableScan算子，可以看到在診斷結(jié)果里，有關(guān)于該表數(shù)據(jù)傾斜的詳細(xì)診斷結(jié)果信息，這張表由于數(shù)據(jù)分布字段選擇的不合適，存在嚴(yán)重的數(shù)據(jù)傾斜問題，同時(shí)可以看到有相關(guān)的官方調(diào)優(yōu)文檔，我們根據(jù)調(diào)優(yōu)文檔，就可以調(diào)整為合適的分布字段，減少表數(shù)據(jù)傾斜對(duì)查詢性能的影響。

通過以上的兩個(gè)例子，我們可以看到，融合執(zhí)行計(jì)劃和SQL自診斷功能，可以快速的幫我們定位到查詢的性能問題，并給出一定的調(diào)優(yōu)建議，減少大量不必要的時(shí)間和精力的浪費(fèi)，降低了初級(jí)使用者使用ADB的門檻。關(guān)于SQL自診斷更多的診斷結(jié)果可以參考官網(wǎng)文檔：SQL自診斷，目前已經(jīng)有20+診斷規(guī)則上線，涉及查詢相關(guān)的內(nèi)存消耗、耗時(shí)、數(shù)據(jù)傾斜、磁盤IO以及執(zhí)行計(jì)劃等多個(gè)方面，后續(xù)還有更多診斷規(guī)則陸續(xù)上線。

三、 后續(xù)規(guī)劃

通過以上的闡述和例子分析，可以看到當(dāng)前的診斷調(diào)優(yōu)工具已經(jīng)可以幫助用戶進(jìn)行多方面的SQL性能問題排查，但是我們?cè)趯?shí)際的線上問題處理和值班時(shí)仍然發(fā)現(xiàn)總結(jié)了多個(gè)用戶在分析實(shí)例性能問題時(shí)的需求：

我應(yīng)該調(diào)優(yōu)哪些SQL？

用戶在打開診斷調(diào)優(yōu)頁面時(shí)，面對(duì)實(shí)例上運(yùn)行的上萬甚至上千萬條SQL，雖然可以通過耗時(shí)、內(nèi)存消耗或者掃描量等進(jìn)行排序來初步篩選出需要調(diào)優(yōu)的SQL，但是其實(shí)其實(shí)用戶欠缺了一個(gè)特定診斷結(jié)果的視角，例如：

哪些SQL是數(shù)據(jù)掃描傾斜的？
哪些SQL是索引過濾不高效的？
哪些SQL是Stage輸出傾斜的？
哪些SQL是分區(qū)選擇不合理的？
用戶在針對(duì)某一個(gè)SQL的特定診斷結(jié)果調(diào)優(yōu)完成后，其實(shí)需要知道有哪些類似的SQL都需要調(diào)優(yōu)的，所以我們后續(xù)會(huì)提供給用戶一個(gè)從特定診斷結(jié)果維度進(jìn)行分析的工具，一次性地解決某個(gè)特定問題。

我的SQL有問題，和建表方式不優(yōu)有關(guān)嗎？

ADB后臺(tái)是一個(gè)分布式的數(shù)據(jù)存儲(chǔ)和分布式的執(zhí)行框架，依賴數(shù)據(jù)均勻的分布到各個(gè)后臺(tái)節(jié)點(diǎn)上，同時(shí)ADB針對(duì)不同的業(yè)務(wù)場(chǎng)景設(shè)計(jì)了不同的表類型，例如分區(qū)表、復(fù)制表，有些表字段在存儲(chǔ)時(shí)進(jìn)行聚集存儲(chǔ)，也會(huì)提升查詢性能，但是用戶往往不知道建表方式不優(yōu)到底影響了哪些查詢。后續(xù)我們會(huì)把「數(shù)據(jù)建模診斷結(jié)果」和「查詢?cè)\斷結(jié)果」關(guān)聯(lián)，用戶通過數(shù)據(jù)建模的診斷結(jié)果即可快速知道不良的表結(jié)構(gòu)影響了哪些SQL，同時(shí)反過來也可以通過某條SQL的診斷結(jié)果知道哪些表需要優(yōu)化。兩類診斷結(jié)果聯(lián)動(dòng)調(diào)優(yōu)，可以從根源上解決實(shí)例的查詢性能問題。