陷入人肉SQL最佳化的惡性迴圈怎麼辦？是時候跟它們說再見了

作為資料庫管理員或應用開發者，大家肯定有過SQL最佳化經歷。資料庫上執行的SQL千差萬別，且伴隨著業務快速迭代、資料分佈特徵變化、熱點變化、資料庫版本升級等持續動態變化，這些都使得SQL最佳化如同三餐般不可或缺。

看似相同的過程，但每次都充滿不一樣的挑戰:

如何利用綜合手段實現快速準確的問題定位？例如問題SQL，僅僅從慢日誌中分析是遠遠不夠的。

依據資料庫領域專家經驗或工具輔助，如何精準地識別瓶頸點，得出修復／最佳化建議？

如何全面地評估最佳化效果、影響面（包括副作用，如對相關SQL，寫操作的影響等），做上線前的安全評估？

對於複雜的部署（如大規模的分庫分表場景），如何選擇灰度策略、變更視窗、安全穩妥地推進線上變更？

如何持續的跟蹤效果，做到萬無一失？

除此之外，我們還要考慮兩個重要的時間點，如下圖所示，一個簡單的慢SQL趨勢，T1代表我們發現資料庫例項效能異常的時間點，從此刻開始著手慢SQL的最佳化，T2是最佳化過程完畢時間點，例項恢復常態。在傳統的最佳化處理中，這一過程一般完全依賴人力驅動，常常會暴露出兩個方面的嚴重不足：

• T1過於偏後，即異常發現不及時、響應不及時，即使發現時，問題可能已堆積多時，重病已纏身，已處在故障的邊緣；
• T2-T1 所代表的處理時間過長，一方面嚴重影響使用者體驗，另一方面大大增加故障風險。

除了上述的兩個問題， 我們還面臨著另外兩個更為嚴峻的挑戰：

• 如何實現持續最佳化？在第一時間發現問題及時最佳化，避免問題積累，保證穩定的同時保持資料庫例項持續處在最佳執行狀態；
• 如何縮短處理時長，最大限度減少影響，採用綜合治理手段保證資料庫例項穩定性，實現標本兼治？

傳統方式依賴人力驅動，這兩方面的侷限性會顯得尤為突出，常常處於故障驅動、疲於應對、四處救火的狀態。隨著業務規模發展，例項規模擴大，所有這些問題也隨之被放大，並且大機率會進入即使投入更多人力也沒有辦法解決的惡性迴圈狀態。

破解之道

自動SQL最佳化服務是阿里雲資料庫自治服務（DAS）中最為核心服務之一，以自最佳化的自治能力實現SQL最佳化的閉環。

如上圖其閉環能力具體體現幾個方面：

1）負載（Workload）異常檢測，識別資料庫業務變化，問題SQL的快速識別與定位，如新增慢SQL，效能惡化SQL，不高效SQL等；

2）針對問題SQL，自動呼叫SQL診斷最佳化服務生成最佳化建議，如最優索引的建立、SQL語句改寫、引擎推薦等等；

3）自動完成最佳化建議風險評估，根據資料庫例項負載情況、例項畫像自動生成灰度計劃，自動編排最佳化任務；

4）自動選取運維視窗，依據灰度計劃，完成相關線上變更，目前階段主要支援索引的自動上線變更；

5）針對上線的變更，啟動多維度的最佳化效果跟蹤，持續實時全面的效能迴歸風險評估，符合預期，自動計算最佳化收益，不符預期，自動回滾。

依託該全自動最佳化閉環，將重人工的被動式最佳化轉變為以智慧化為基礎的主動式持續最佳化，最終實現SQL最佳化的無人值守。試想下，它就如同一群資料庫專家7x24小時地守護在你的資料庫旁邊，不知疲倦，時刻保持資料庫系統執行在最佳最佳化狀態。

當然，為了上述的目標，實現過程中面臨諸多挑戰：

精準性，如何構建異常檢測機制，實現最佳化時機的精準識別，問題SQL的精準定位；

專業性，需要強大的專業性最佳化診斷後盾，沒有有效的專業診斷，最佳化就無從談起；

安全性，線上無小事，線上變更如何做到安全可控；

全面性，最佳化效果的全面多維度跟蹤，全面實時評估，也是保證安全性的要求；

聯動性，對於複雜的線上問題，有時需要綜合治理，如突發的惡性慢SQL問題，DAS的自動SQL限流，自動SQL最佳化需要形成聯動效應，實現問題的標本兼治；

規模性，如何構建具備足夠擴充套件性的服務架構，以支撐幾十萬級、百萬級的大規模自動最佳化。

下面將從多個緯度進一步解讀DAS的解決方案。

---

1、實現架構

DAS 自動SQL最佳化是一個基於資料驅動的閉環流程，上圖簡單描述了整個流程：

• 異常事件，異常事件是觸發自動SQL最佳化的引信，異常事件由DAS事件中心統一管理，異常事件產生自實時異常檢測、離線分析、workload檢測、告警系統等等。
• 診斷髮起：自動SQL最佳化服務從事件中心收到異常事件後，會對例項進行初步判斷，向診斷引擎發起診斷請求並處理診斷結果（一條或多條建議），完成有效性評估，生成新的最佳化事件傳送至事件中心，驅動下一步最佳化流程。
• 建議推送：使用者進入DAS“自治中心”，在未開啟全自治模式下，使用者可以選擇是否接受最佳化建議，在自主決策下觸發後續自動化最佳化流程；
• 變更上線：選擇運維視窗期，下發變更命令，並確認執行情況；
• 效果跟蹤和衡量：當最佳化建議生效後，決策引擎會啟動跟蹤任務，對被最佳化的SQL及相關SQL進行效能跟蹤，如果效能出現衰退，則自動回滾。通常跟蹤24小時後，如無回滾則計算收益。

2、問題發現

SQL最佳化支援多種場景的SQL異常發現，概況起來為以下三種：

• 定時觸發

常規性在運維視窗期，定期對使用者例項發生的慢SQL進行離線分析，發起SQL最佳化。

• 部分SQL效能惡化觸發

Workload異常檢測演算法會實時發現效能惡性SQL，觸發SQL自動最佳化。對於複雜的線上問題，自動SQL最佳化和DAS的自動SQL限流會形成聯動效應，發起SQL自動最佳化，相關詳情請參見：《業務異常只能看著資料庫崩潰？看看應急處理利器——自動SQL限流》

• 例項workload變化觸發

隨著業務SQL的上線和下線，資料庫負載、資料量發生變化，現有索引不能很好匹配當前業務的效能要求，發起例項Workload層面的診斷最佳化。

3、診斷能力

DAS的SQL診斷最佳化服務是自動SQL最佳化強大後盾，它採用基於代價模型方式，也就是採用和資料庫最佳化器相同的方式去思考最佳化問題，最終會以執行代價的方式量化評估所有的可能推薦候選項，最終作出可靠推薦。

該服務已在阿里巴巴集團內部穩定執行將近3年多時間，日平均診斷量在5萬左右，支撐著整個集團業務應用的SQL最佳化，3年多來，SQL診斷成功率保持在98%以上，針對慢SQL的推薦率保持在75%以上。相關詳情請參見：《耗時又繁重的SQL最佳化，以後就都交給TA吧！》

4、安全變更

安全變更體現在變更前的安全檢查、灰度的變更策略、變更後的效能跟蹤。

安全檢查：為降低風險，變更僅發生在運維視窗期，同時我們會進行主備延遲、例項負載和表空間判斷，各指標都在安全範圍內時才進行變更。

灰度的變更策略：如大量分庫分表場景，為降低風險，自動生成灰度計劃，分批變更。變更過程中，系統會監控例項的主備延遲，一旦延遲超過閾值，立刻暫停該庫的全部索引變更任務，並保障每個庫僅允許一個變更任務執行。

效果評估：效果評估演算法會對被最佳化的SQL及相關SQL模板進行效能跟蹤，避免出現效能惡化導致故障。效能跟蹤的演算法基於決策樹模型，對SQL模板最佳化後的效能指標與最佳化前進行對比，綜合判斷SQL模板在該時刻是否發生了效能衰減。業務往往是以天為週期變化，預設跟蹤時間為24小時，沒有回滾，則認為本次最佳化成功，並計算實際最佳化收益。

久經考驗

DAS的自動SQL最佳化服務雲上釋出前，已在阿里巴巴集團內部穩定無故障執行將近2年多時間，截止到2020年4月，自動SQL最佳化已累計最佳化超4200萬慢SQL，集團全網慢SQL下降92%左右。

更為重要的是，自動SQL最佳化服務已經構建了有效的主動式分析，反饋系統，線上失敗案例，自動最佳化中的回滾案例會自動沉澱到案例系統，時刻不停地驅動著自動最佳化閉環、診斷服務在快速迭代中成長。