本文是SIGMOD 2019《Automatically Indexing Millions of Databases in Microsoft Azure SQL Database》論文的筆記刻蚯，個(gè)人見解。本文重點(diǎn)討論了Azure的自動(dòng)索引推薦系統(tǒng)攀甚，討論了整個(gè)過(guò)程的細(xì)節(jié)和反饋族淮。

1. 前言

自動(dòng)索引推薦是數(shù)據(jù)庫(kù)領(lǐng)域里的一個(gè)研究了很久的問(wèn)題枕面，也有很多種算法提出來(lái)嗅绰，但大多是基于數(shù)據(jù)表的統(tǒng)計(jì)信息或優(yōu)化器的計(jì)算方式娘侍，結(jié)合SQL語(yǔ)句中的查詢條件進(jìn)行選擇大溜，然后得到當(dāng)前SQL所需要使用的索引技矮。

然而抖誉，一個(gè)端到端的自動(dòng)索引推薦系統(tǒng)卻很少見，尤其是在大規(guī)模運(yùn)維的云上數(shù)據(jù)庫(kù)系統(tǒng)里更是沒(méi)見過(guò)有這類功能衰倦。主要挑戰(zhàn)點(diǎn)如下：

? ? 1. 如何大規(guī)模的應(yīng)用到所有的數(shù)據(jù)庫(kù)上袒炉。Azure上數(shù)據(jù)庫(kù)覆蓋了超過(guò)140個(gè)國(guó)家，涉及了50多個(gè)全球化區(qū)域樊零。

? ? 2. 要推薦出合適的索引我磁。推薦索引時(shí)還有很多附加因素，如索引占據(jù)的空間大小驻襟。

? ? 3. 最好的索引推薦算法是依賴查詢優(yōu)化器組件夺艰。數(shù)據(jù)庫(kù)理論上的最優(yōu)索引與查詢優(yōu)化器實(shí)現(xiàn)過(guò)程中采用的索引不一定是一致的，而生產(chǎn)環(huán)境下是不接受出現(xiàn)這種badcase的沉衣。（畢竟理論是理論郁副，實(shí)現(xiàn)是實(shí)現(xiàn)，在實(shí)現(xiàn)的過(guò)程中會(huì)權(quán)衡多種因素對(duì)理論做一定的妥協(xié)豌习。）

? ? 4. 自動(dòng)索引的操作不能影響用戶存谎。建索引可能會(huì)鎖表、會(huì)占大量cpu或I/O肥隆。

2. 方法

本文主要是介紹了框架既荚，包含如下幾個(gè)組件：

? ? 1. 控制面板（Control Plane）：作為整個(gè)系統(tǒng)的控制面板，掌握整個(gè)自動(dòng)優(yōu)化流程的生命周期栋艳，協(xié)調(diào)不同組件恰聘。

? ? 2. 索引推薦器（Index Recommender）：分析業(yè)務(wù)的workload，識(shí)別出哪些index需要被創(chuàng)建或者是被拋棄。這里主要是微軟以前做過(guò)的兩個(gè)工具M(jìn)I（Missing Index）和DTA（Database Engine Tunning Advisor）憨琳。

? ? 3. 驗(yàn)證器（Validator）：分析index對(duì)workload的影響，用于檢查新的index是否產(chǎn)生了導(dǎo)致SQL性能變差的情況旬昭。

微軟的這個(gè)工作已經(jīng)做了很多年篙螟，它的主要時(shí)間節(jié)點(diǎn)如下：

????2015年：自動(dòng)優(yōu)化系統(tǒng)啟動(dòng)

????2016年：整個(gè)系統(tǒng)GA

????2018年：已經(jīng)百萬(wàn)級(jí)別數(shù)據(jù)庫(kù)實(shí)例的應(yīng)用

3. 工程方面

在工程上，為了避免數(shù)據(jù)跨region的問(wèn)題问拘，微軟在每個(gè)region都部署一個(gè)auto-index的服務(wù)節(jié)點(diǎn)遍略，服務(wù)之間的調(diào)用采用了RPC調(diào)用，服務(wù)的內(nèi)部細(xì)節(jié)見Figure 4骤坐。

從Figure 4的流程上可以看出绪杏，Control Plane是整個(gè)流程的核心，它發(fā)起任務(wù)后纽绍，由SQL引擎根據(jù)workload進(jìn)行索引推薦蕾久，然后將建議反饋給Control Plane和用戶Portal，然后Control Plane再啟動(dòng)創(chuàng)建索引和驗(yàn)證索引流程拌夏，讓SQL引擎在Validator上基于Query Store的數(shù)據(jù)進(jìn)行驗(yàn)證僧著，再把驗(yàn)證結(jié)果反饋給Control Plane和用戶Portal。其中障簿，該服務(wù)中的有兩個(gè)重要的節(jié)點(diǎn)：

SQL Server查詢優(yōu)化器盹愚。它可以產(chǎn)生出一些缺失的索引，也會(huì)借助“what-if” API去解決索引配置站故。

Query Store皆怕。這個(gè)工具將所有的SQL流水?dāng)?shù)據(jù)進(jìn)行存儲(chǔ)，并且對(duì)不同的SQL 模板進(jìn)行了統(tǒng)計(jì)西篓，記錄了SQL層面很多維度的數(shù)據(jù)愈腾。

3.1. Control Plane

Control Plane部分管控整個(gè)流程，主要的任務(wù)就是如Figure 4里的作用：

????1. 發(fā)起數(shù)據(jù)庫(kù)診斷和生成索引

? ? 2. 應(yīng)用索引推薦

????3. 驗(yàn)證推薦結(jié)果

? ? 4. 檢測(cè)索引應(yīng)用后的效果或告警

而推薦出的索引在整個(gè)流程中是有很多種狀態(tài)：

????Active：索引準(zhǔn)備應(yīng)用或者刪除（推薦刪除某個(gè)索引 也是索引推薦的一部分）

????Expired：索引由于生命周期原因?qū)е碌倪^(guò)期

????Implementing：正在執(zhí)行索引推薦的結(jié)果

????Validating：驗(yàn)證應(yīng)用索引建議后的效果

????Success：索引建議符合預(yù)期

????Reverting：索引建議Validate過(guò)程中出現(xiàn)性能變差的case岂津，系統(tǒng)正在回滾

????Reverted：回滾完成

????Retry：重試

????Error：失敗

跟蹤索引建議的這些狀態(tài)是管理索引推薦流程的重要環(huán)節(jié)顶滩，這樣對(duì)數(shù)據(jù)跟蹤有很大幫助，也能快速定位問(wèn)題寸爆。

3.2. Index Recommender

微軟的索引推薦器主要是兩個(gè)：MI（Missing Indexes）和DTA（Database Engine Tunning Advisor）礁鲁。這兩個(gè)工作均已經(jīng)發(fā)布很久，經(jīng)過(guò)了線上環(huán)境的驗(yàn)證赁豆。

其中仅醇，MI可以識(shí)別出數(shù)據(jù)庫(kù)中目前不存在且能提升SQL性能的索引，它通過(guò)DMV（Dynamic management view）或者執(zhí)行計(jì)劃把缺失的索引暴露出來(lái)魔种，相對(duì)比較輕量析二。MI是個(gè)局部?jī)?yōu)化，同時(shí)它不能對(duì)groupby join, order by類查詢產(chǎn)生優(yōu)化，并且也不能從workload視角對(duì)索引進(jìn)行優(yōu)化叶摄。DTA則是一個(gè)較為完整的physical design tool級(jí)別的工具属韧，它可以用workload作為輸入并產(chǎn)出基于workload級(jí)別優(yōu)化的索引建議，它也會(huì)使用 what-if 優(yōu)化器蛤吓。DTA對(duì)資源的消耗比較高宵喂。因此，微軟在機(jī)型偏差的數(shù)據(jù)庫(kù)上采用MI進(jìn)行推薦会傲，對(duì)機(jī)型較好的采用了DTA進(jìn)行推薦锅棕。

在workload選擇上，微軟利用QueryStore中的數(shù)據(jù)對(duì)業(yè)務(wù)workload進(jìn)行排序處理淌山，然后選擇占比較大的部分進(jìn)行整體性能優(yōu)化裸燎，例如占比workload>80%以上的SQL部分。

（對(duì)于MI和DTA的更多細(xì)節(jié)處理泼疑，以及微軟在應(yīng)用過(guò)程中處理的一些操作德绿，還是看原文吧，就兩頁(yè)紙退渗，這里不去翻譯了脆炎。只能說(shuō)，這里的細(xì)節(jié)坑都是生產(chǎn)環(huán)境中真實(shí)要處理的地方氓辣，如workload選擇秒裕、中間表優(yōu)化、優(yōu)化器badcase等問(wèn)題钞啸。）

對(duì)于索引刪除建議几蜻，這其實(shí)也是個(gè)很有難度的問(wèn)題，也是生產(chǎn)環(huán)境中被質(zhì)疑最多的地方体斩，主要難點(diǎn)：

? ? 1. 很重要的偶發(fā)SQL要用的索引梭稚。這種索引通常是特定業(yè)務(wù)需求下產(chǎn)生的SQL。例如絮吵，每個(gè)月底才運(yùn)行一次的報(bào)表SQL弧烤。

? ? 2. 用戶加了hint或者force index的查詢。在未優(yōu)化索引時(shí)蹬敲，開發(fā)同學(xué)可能會(huì)通過(guò)hint或者force index來(lái)糾正某些優(yōu)化器對(duì)SQL執(zhí)行的bad case暇昂。

? ? 3. 重復(fù)索引。有些索引可能已經(jīng)包含在另一個(gè)索引里伴嗡，如何找出和選擇這些重復(fù)的索引急波，也是很困難的。因?yàn)橛袝r(shí)優(yōu)化器的選擇跟我們理解的選擇還是有差異瘪校，會(huì)出bad case澄暮。

微軟在針對(duì)上面這3種問(wèn)題上名段，采用了更加詳盡的索引使用統(tǒng)計(jì)、避開force index泣懊、擴(kuò)大索引分析窗口長(zhǎng)度等方法伸辟，盡可能的將影響降到最低。

3.3. Implementation and Validation

當(dāng)索引推薦器產(chǎn)生出索引后馍刮，要對(duì)這些建議進(jìn)行驗(yàn)證信夫，才能應(yīng)用到線上環(huán)境。

在執(zhí)行的時(shí)間上渠退，操作的前提是選擇業(yè)務(wù)的低峰期忙迁，進(jìn)行索引變更脐彩，并且把變更索引的權(quán)重調(diào)整到最低碎乃，避免影響用戶。

在驗(yàn)證細(xì)節(jié)上惠奸，評(píng)價(jià)指標(biāo)值中只關(guān)注邏輯指標(biāo)（cpu time梅誓、logic read等），評(píng)價(jià)對(duì)象是只分析與索引建議相關(guān)的SQL佛南，評(píng)價(jià)方法上采用了Welch t-test來(lái)校驗(yàn)索引變更前后分布是否一致梗掰。若檢測(cè)到有性能退化，則回滾嗅回。

4. 實(shí)驗(yàn)階段

實(shí)驗(yàn)階段及穗，是指如何驗(yàn)證索引建議的正確性。由于業(yè)務(wù)的多樣性绵载，SQL也有很多種case需要去驗(yàn)證算法的覆蓋度埂陆。

微軟采用了一個(gè)非常實(shí)用且高效的方法：B-instance。思路非常簡(jiǎn)單：為了給A數(shù)據(jù)庫(kù)產(chǎn)生索引推薦娃豹，先做一個(gè)A的鏡像B焚虱，然后對(duì)鏡像B進(jìn)行索引推薦并應(yīng)用，這樣B就是具備新索引的節(jié)點(diǎn)懂版。然后鹃栽，當(dāng)業(yè)務(wù)SQL執(zhí)行到A的時(shí)候，同時(shí)發(fā)送給B一份躯畴，然后觀察B節(jié)點(diǎn)上的性能指標(biāo)民鼓，如果性能變好就說(shuō)明索引建議有效。在這個(gè)過(guò)程中蓬抄，創(chuàng)建鏡像B摹察、轉(zhuǎn)發(fā)SQL流量、B節(jié)點(diǎn)的結(jié)果均確保對(duì)A實(shí)例和業(yè)務(wù)無(wú)影響倡鲸。

這個(gè)方法僅在實(shí)驗(yàn)階段用于驗(yàn)證算法和方案的有效性供嚎，驗(yàn)證結(jié)束后的線上生產(chǎn)環(huán)境中并沒(méi)有對(duì)每個(gè)數(shù)據(jù)庫(kù)均做一套B-instance方案。

4.1. 實(shí)驗(yàn)結(jié)果

由于MI和DTA應(yīng)用在了不同機(jī)型，因此實(shí)驗(yàn)也分成了兩類建議效果的評(píng)價(jià)克滴。

基準(zhǔn)數(shù)據(jù)為User逼争，即數(shù)據(jù)庫(kù)上DBA自己產(chǎn)生的索引。通過(guò)隨機(jī)從這些索引中扔掉索引劝赔，再讓算法MI/DTA進(jìn)行推薦誓焦，然后對(duì)比前后的效果。

從Figure 6來(lái)看着帽，MI和DTA并沒(méi)有出現(xiàn)一個(gè)完全覆蓋另一個(gè)的情況杂伟，說(shuō)明了MI和DTA服務(wù)同時(shí)存在的必要性。算法推薦出的索引可以在85~90%的數(shù)據(jù)庫(kù)實(shí)例上達(dá)到甚至超過(guò)DBA調(diào)整的索引仍翰，說(shuō)明了算法的有效性赫粥。在Preminum tier中User的占比高于Standard tier中User，說(shuō)明在非常復(fù)雜的workload上予借，DBA人工的索引效果要更好一些越平。

5. 商業(yè)價(jià)值

截止到2018年10月，Auzre上產(chǎn)生了250 K的新建索引建議和3.4 Million的刪除索引建議灵迫。

在打開了自動(dòng)推薦索引的數(shù)據(jù)庫(kù)上秦叛，約50K的索引被創(chuàng)建，約20K的索引被刪除瀑粥。

約11%的索引建議產(chǎn)生了回滾挣跋。

6. 結(jié)論

微軟的工作做的非常細(xì)致，畢竟是應(yīng)用在Azure全部數(shù)據(jù)庫(kù)上且運(yùn)行了兩年以上的系統(tǒng)狞换。每個(gè)基礎(chǔ)工作均有扎實(shí)的基礎(chǔ)避咆，在確保不影響用戶的前提下也創(chuàng)新了很多操作，如低優(yōu)先級(jí)的index operation哀澈、invisible index牌借、resumable index create等等。