MySQL自治平台建設的核心原理及實踐（下）

本文作者在演講後根據同學們的回報，補充了很多技術細節，跟演講（視訊）相比，内容更加豐富。文章分成上、下兩篇，上篇将介紹資料庫的異常發現跟診斷方面的内容，下篇将介紹核心可觀測性建設、全量SQL、異常處理以及索引優化建議與SQL治理方面的内容。希望能夠對大家有所幫助或啟發。

• 0 前文回顧
• 1 核心可觀測性建設
• 1.1 核心可觀測性建設
• 2 全量SQL
• 2.1 實作方式
• 2.2 核心實作方式
• 2.3 全量SQL總體架構
• 3 異常處理
• 3.1系統架構
• 3.2 産品展示
• 4 索引優化建議與治理
• 4.1 索引優化建議
• 4.2 單SQL索引優化建議
• 4.3 基于Workload的索引優化建議
• 4.4 SQL治理

美團技術團隊，贊4

0 前文回顧

在《MySQL自治平台建設的核心原理及實踐（上）》一文中，我們主要介紹了資料庫的異常發現與診斷方面的内容，在診斷方面經常會碰到一些難以找出根因的Case。針對這些疑難雜症Case，通過本篇可以了解到，如何通過核心可觀測性以及全量SQL來解決這些問題。除此之外，在得出根因後，如何處理異常，如何對SQL進行優化，以及如何進行SQL治理等相關方面問題，也将在本篇中給予解答。

1 核心可觀測性建設

| 1.1 核心可觀測性建設

1.1.1 性能診斷挑戰

在自治性能診斷平台的建設過程中，我們發現如下兩大挑戰：

1. 很多SQL性能抖動的問題找不出根因，比如SQL的執行時長莫名其妙的突然變大，其執行計劃良好、掃描跟傳回的行數都很少，也沒有行鎖、MDL鎖相關鎖阻塞；檢視慢查詢日志，也沒有哪個字段的耗時比較高，但是SQL的執行時長就是突然變長，有時候達到幾十秒長，而平時往往是幾毫秒，各種分析後找不出原因。
2. 有時候在診斷一些名額異常的根因時，憑借的是不太嚴格的經驗，而不是量化分析，比如thread_running或者slow_queries值突然升高的時候，可能會通過表information_schema.processlist檢視目前的活躍會話中線程的狀态，看一下狀态是不是有行鎖或者MDL鎖之類的阻塞，或者通過慢查詢日志裡的相關資料來診斷根因。這裡的挑戰是：我們看到的是整個SQL某個時間點的瞬時狀态，或者隻是整個SQL執行過程中的部分資料，而不是整體的資料，是以得出的根因診斷可能是片面的，也許一瞬間看到的是行鎖，但是大部分時間被MDL鎖阻塞。

1.1.2 解決思路

如果使用的是社群版本的MySQL，基本上都會面臨上面兩大問題。我們先從核心的角度分析一下這兩個挑戰。對于第一個挑戰，主要是對MySQL在核心層面執行細節不夠了解，比如一條SQL執行了10s，而從核心層面來看的話，這十秒的時間可能會有幾百個步驟組成，檢查後可能發現row或者MDL鎖等待時間加起來隻有1秒，那麼其他的9秒的耗時在哪裡呢？可能發生在這幾百個步驟中的任何一個或者多個，是以如果沒有這幾百個步驟的明細資料，就無法診斷出突然出現的性能問題的根因。

第二個問題跟第一個問題從本質上來說是一樣的。由于采集的資料是某個時間點的快照資料（通過活躍會話），或者隻是部分名額的資料（通過慢查詢日志），是以我們看到的隻是片面的資訊，而沒有辦法擷取到整個SQL的完整的耗時分布資訊。

1.1.3 Wait耗時量化分析法

在分析完原因之後，我們參考了TSA的思想，同時結合MySQL自身的特點來做基于Wait的核心可觀測性的建設。從TSA可以看出，SQL執行的總耗時無非就是由Off-CPU跟ON-CPU兩大部分組成，如果SQL有耗時長的問題，首先要确認是在OnCPU還是在OffCPU上耗時多。如果是前者，那麼說明SQL本身有問題，比如消耗資源太多（比如無效索引造成掃描行數過多）；如果是後者，那麼說明SQL本身沒有問題，而是受到幹擾或者系統資源不足，進而造成OffCPU層面耗時過多。

無論是何種情況，我們都需要把OnCPU跟OffCPU的耗時做進一步的拆分，進而來檢視耗時瓶頸點到底在哪裡，并且對耗時比較多的關鍵代碼路徑交由核心團隊來進行埋點；相對MySQL performance_schema庫中統計的Wait資訊，實作非常輕量，是以對總體的性能的影響很小，并且耗時名額都是SQL明細粒度的，而不是類似performance_schema中的SQL模版級别的聚合資料。

耗時在OnCPU

在分析一條SQL執行耗時，如果發現像下圖一樣，耗時分布大部分都在OnCPU，這說明SQL本身有嚴重的性能問題（全表掃描、過濾效果不佳或者查詢優化器的bug等原因），我們可以把診斷的方向集中在如何優化SQL本身就可以了，而不需要去關注其他的方面，如鎖阻塞、磁盤或者網絡延遲等OffCPU方面的問題。通過使用getrusage方法來擷取某條SQL的OnCPU耗時時長，比如在SQL執行前，擷取目前線程的ru_utime、ru_stime時間，然後在SQL執行完畢時，再次擷取ru_utime、ru_stime值就可以得到SQL執行的CPU Time。

耗時在OffCPU

如果發現OnCPU的耗時占總耗時的比率比較低，通過OffCPU的相關名額發現其占總耗時的比率比較高，說明SQL本身沒有問題，可能是被鎖住了、硬體資源不足或者是被核心層面某個核心鎖給卡住了。我們就需要檢視到底是OffCPU相關的哪個，或者哪些名額耗時比較高，需要在核心層面對執行過程中的可能的、耗時比較長的執行代碼路徑進行埋點，隻有擷取到了跟性能相關的關鍵的資料，才能做出準确的判斷。

如何選擇合适而全面的OffCPU相關的代碼路徑進行埋點？經過探索，這裡提供了如下幾個方式：

1. 分析setup_instruments表中包含的關鍵埋點資訊，大緻知道有哪些關鍵的名額可以埋點，比如wait/IO、Mutex類等，再結合自身的經驗來判斷哪些名額可能有性能瓶頸的問題。
2. 根據實際疑難case來判斷選擇哪些Wait名額，比如看到"Thread xxx has waited at trx0trx.cc line 1193 for 241.00 seconds the semaphore: xxxxx mutex REDO_RSEG created trx0rseg.cc:211, lock var 1"，則對mutex REDO_RSEG進行埋點，看到"Thread xxx has waited at dict0dict.cc line 1239 for 245.00 seconds the semaphore: xxxxxx Mutex DICT_SYS created dict0dict.cc:1173, lock var 1"則對 Mutex DICT_SYS進行埋點。
3. 源碼分析，在核心層面對SQL的執行過程進行逐漸的Debug，根據經驗分析可能的瓶頸點來埋點；下面舉兩個例子分别就純源碼的角度來分析，SQL在讀、寫過程中哪些地方可能會造成性能抖動并做埋點。

例子1，在SQL讀操作的執行過程中，需要從buffer pool中請求記憶體資源，如果一直滿足不了記憶體資源的請求則一直會循環嘗試擷取，如果在2000ms内還是沒有從Buffer pool中請求到需要的記憶體，會列印日志“Difficult to find free blocks in the buffer pool......”，來表明記憶體很緊張；這種長時間等待記憶體資源的擷取會生産了性能抖動，這個時候就需要在對應的核心代碼處進行埋點擷取BP記憶體資源等待時間。

例子2，在SQL寫操作的執行過程中是需要寫redo log的，如果redo log空間不夠，則需要刷盤redo log跟BP中的髒頁，而刷髒頁可能是個很耗時的操作，并會引起包括活躍會話突增、慢查詢等性能抖動問題。這個場景跟上面的類似，也需要對源碼進行分析，并且在關鍵的位置進行埋點來統計刷髒的耗時。

1.1.4 Wait名額層次圖

通過上面的三種方式我們不斷地疊代累計的關鍵耗時名額，整理成如下的OffCPU跟OnCPU兩大類的分層架構圖。截止目前為止，我們核心團隊在核心層面埋點了100多個關鍵名額來輔助診斷，名額分為Statement跟Wait兩大層級。

Statement

SQL語句級别的名額，如QUERY_TIME、 Row_time行鎖時間、ROWS_EXAMINED、ROWS_SENT、Bytes_sent、NO_INDEX_USED、Full_SCAN等，但是這個層面的資料不足于判斷出SQL性能問題的根因，比如QUERY_TIME很長，但是ROWS_EXAMINED、ROWS_SENT都很小，就需要進入下一個Wait級别的名額進行進一步的分析。

Wait

Wait層面主要是MySQL核心層面的名額，比如Latch（Mutex、rw_lock、sx_lock、cond）的名額，這裡需要說明的是上面的分層圖跟MySQL自帶的performance_schema中的wait type分層圖看上去很像，那為什麼不直接使用而需要自研呢？這裡主要從如下幾個原因且核心團隊都很好的解決了：

1. performance_schema相關的統計資訊是自執行個體啟動後總體的wait時間的聚合資料，而更需要的是任何時間段、單SQL例子的明細資料，因為診斷的往往是某個時間點或者時間段的異常SQL問題。
2. performance_schema中其有不少Bug，除此之外，它隻支援OffCPU類型的名額，OnCPU耗時的統計不支援，甚至有些非常重要OffCPU的名額，比如row lock的wait統計資訊都不支援。
3. 開啟performance_schema相關的wait後，對總體的性能影響比較大，而基于核心埋點的實作方式很輕量級，對資料庫整體性能影響很小。

2 全量SQL

全量SQL，指的是把應用程式或者使用者通路DB的所有SQL集合，需要捕獲到這些SQL并且發至後端進行分析。在核心可觀測性建設之前，原來采用的方式是根據MySQL的協定，來解析TCP封包來提取出SQL。此種實作方式的缺陷也很明顯，就是除了SQL文本外，其他的能擷取到的關鍵SQL名額資訊非常少，這樣做資料庫的故障診斷跟SQL性能分析，會因為缺少關鍵名額而帶來挑戰。

針對此挑戰，我們的解決方案是：改造為直接從MySQL核心吐資料，來作為全量SQL的資料來源（前面的核心可觀測性分析介紹可知，單條SQL次元上能從核心層面輸出100多個關鍵名額，包含故障診斷跟SQL性能分析需要的重要名額）。

| 2.1 實作方式

全量SQL其實有好幾個實作方式，初版使用了抓TCP包的方式，現在逐漸從TCP抓包過渡到了基于核心輸出SQL文本跟關鍵名額的方案。

| 2.2 核心實作方式

從核心層面來說，MySQL對于使用者的連結将建立獨立的thd結構體，是以采集的全量SQL對應的SQL文本跟關鍵名額資料，可以存于thd結構體中。然後使用者線程将thd儲存的名額資料，複制到一個自定義記憶體池中（無鎖隊列）；輸出子產品有一個線程從記憶體池中依次擷取儲存的資訊後，寫入全量SQL檔案。具體流程如下圖所示：

| 2.3 全量SQL總體架構

如何把海量資訊（日均PB級别）上報到後端系統，來賦能故障診斷、SQL稽核、索引優化建議等場景是一個較大的挑戰。我們首先通過資料采集器（rds-agent）讀取Full SQL File檔案内的内容，再往後端傳輸。

但是由于資料量特别大，為了節省存儲資源，分析後選用Snappy壓縮算法進行壓縮，預設情況下壓縮效果隻有1～2倍，為此分析了Snappy的源碼，發現如果讓相似的SQL文本聚合在一起，那麼壓縮效果會有很大的提高，是以按照SQL文本的前N個字元（N取50，可以根據SQL文本的實際情況做調整）進行了排序後再壓縮，發現壓縮比提高到了7～8倍。後端的SQL存儲分成了兩種形式，一種是存明細SQL，另一種是存聚合後的SQL模版資訊。

産品展示

采集到的全量SQL既可以模版化展示，點選模版後也可以看到明細的SQL。

3 異常處理

根因分析後，就需要根據具體根因來進行相應的處理，但是如何安全、可靠的進行處理其實是一個很大的挑戰。具體的政策是根據其操作是“無損”還是“有損”來采取不同的處理政策。

如果是對業務“無損”的操作，比如磁盤空間清理、參數值調整、缺失索引添加等，目标是讓操作盡量的自動化。如果是對業務是“有損”的操作，比如需要Kill或者限流，把相關操作進行包裝，發送相關的聊天群讓DBA或者使用者确認後再進行相關的操作。

對于MySQL Hang的情況，高可用團隊會進行周期性探測，如果發現主庫MySQL Hang了，則會自動進行主從切換，如果發現從庫MySQL Hang了則會進行相應的MySQL執行個體下線替換動作。

| 3.1 系統架構

整個異常處理的系統架構圖如下，由“異常發現與分析”系統以及DBA團隊開發的“預案服務”系統組成，前者提供精确的故障根因，後者根據具體的根因來安全、可靠的執行對應的預案，使故障快速恢複。

| 3.2 産品展示

下圖是兩個異常發現、分析、給出建議以及處理的例子：1）異常發現、确認根因後，發現缺索引，系統會自動調用索引優化建議系統給出建議，使用者可一鍵添加索引；2）對于慢查詢或者主從延遲問題，分析根因後提供使用者進行限流、Kill、參數調優等操作的建議，避免異常被進一步放大。

4 索引優化建議與治理

衆所周知，很大一部分的資料庫的異常都是跟SQL性能有關。很顯然，日常的SQL性能問題的治理就很有必要，一個低成本但效果顯著的SQL性能優化方案是提供索引優化建議，并且可以将這種索引優化建議的能力應用于SQL生命周期的三個階段，這三個階段下文會進一步闡述：

1. 應用程式“釋出前”的性能問題SQL稽核（事前）；
2. SQL執行過程中的實時性能問題SQL發現（事中）；
3. SQL“執行完畢”後批量的SQL治理（事後）。

| 4.1 索引優化建議

建立适合的索引對SQL的性能提升效果很明顯，如何添加适合的索引是一個有挑戰性的任務。索引的添加不能光憑經驗，因為索引是否被用到不但跟表的資料的分布有關，還跟MySQL查詢優化器的Cost邏輯有關系，是以“人肉”進行索引建議的評估并不是一個好的方案。

解決方式是盡量要基于MySQL的Cost模型，利用MySQL自身提供的查詢優化器的能力，來給出最佳的索引；索引優化建議的建設一般分為三個階段：1）單SQL次元的優化建議；2）基于workload的整體優化建議；3）索引自維護。這幾個階段的建設需要按順序來進行，并且不可跳躍。下面我們将分别進行闡述。

| 4.2 單SQL索引優化建議

4.2.1 實作思路

單SQL索引優化建議，就是指輸入一條SQL語句後，優化建議系統給出一個索引優化建議的過程。想要利用查詢優化器本身的能力來實作索引建議的目标，就必須先要了解MySQL的查詢優化器是如何工作的。

這裡舉個簡單的單表SQL查詢的例子，幫助我們來初步了解其工作原理。我們先了解查詢優化器是如何從衆多的候選執行計劃中選擇最終的執行計劃的；在有了這個基礎之後，再來講是如何利用查詢優化器自身的能力來做索引優化建議。

比如有SQL語句“select * from test_db.table1 where c2=3 and c3=4 and c4<'3'”，分别在字段id、c2、c3以及c4上面建立了主健索引Primary以及三個二級索引idx_c2、idx_c3及dx_c4。假設最終查詢優化器從多個候選執行計劃中選擇了“執行計劃2”（通過索引idx_c2做index seek操作來執行SQL），問題是此SQL的候選執行計劃有多個，為什麼最後選中了通過索引idx_c2而不是其他的索引做index seek操作的執行計劃呢？總的來說，選擇idx_c2為索引的執行計劃，是因為相比于選擇其他索引生成的執行計劃的Cost更小。

具體來說，針對SQL的兩類表通路方式：1）基于全表掃描的通路方式；2）非全表掃描的方式（比如Index_scan、ref類型或者range_access等），下圖是這些通路方式Cost計算過程中涉及到的查詢優化器跟存儲引擎的關鍵函數，目标是假設某個索引存在的情況下，修改這些函數來模拟Cost的計算過程，來發現哪個索引存在的情況下對應的cost值最小。

流程中的各種資料通路方式，對其Cost計算方式逐個分析。

1）Table scan通路方式

從全表掃描cost計算公式可以知道Table scan的Cost分為IO的Cost跟CPU Cost兩個部分之和，大緻的公式為：IO-cost:#pages in table * IO_BLOCK_READ_COST + CPU cost:#records * ROW_EVALUATE_COST，其中IO-cost是通過table_scan_cost來計算的。

這裡有兩個關鍵的變量records跟pages in table，分别表示這個表有多少行跟多少page，這兩個變量的值是怎麼來的呢？如果使用的是innodb，那麼前者來自于ha_innobase::info_low（ha_innobase::info），後者來自于ha_innobase::scan_time()（而這兩個函數都是SQL層handler.h定義的虛函數，不同的存儲引擎分别實作之），知道了這兩個變量的值，就知道具體的Cost值了。

2）Index scan通路方式

覆寫索引的掃描，從index_scan_cost得知Index scan的Cost為：O-cost:#(records/keys_per_block) * IO_BLOCK_READ_COST+ CPU cost:#records * ROW_EVALUATE_COST，由于IO_BLOCK_READ_COST跟ROW_EVALUATE_COST都是常量，是以需要關注是keys_per_block（keys_per_block的計算跟block_size等有關）跟records這兩個變量，也就是說如果知道了這兩個變量的值，就知道了具體的Cost值了。

3）range access通路方式

從multi_range_read_info_const得知range access的Cost為：IO-cost:#records_in_range * IO_BLOCK_READ_COST + CPU cost:2*#records_in_range* ROW_EVALUATE_COST，由于IO_BLOCK_READ_COST跟ROW_EVALUATE_COST都是常量，是以隻需要知道records_in_range這個變量的值，就能計算其Cost了。

4）ref通路方式

從find_best_ref得知ref通路方式的Cost大緻為：IO-cost:#prefix_rowcount * IO_BLOCK_READ_COST + CPU cost:#prefix_rowcount * cur_fanout * ROW_EVALUATE_COST，需要實作cur_fanout這個變量，而這個變量跟info中的 rec_per_key（rec_per_key的意思是，比如對于select * from test_db.table1 where c2=3，table1中有多少行資料滿足c2=3）有關系，是以實作rec_per_key算法後就可以知道其Cost值了。

4.2.2 What_If索引建議政策

如何把上面的Cost計算方式的理論分析應用到索引優化建議中？假設表table1除了主鍵外沒有任何索引，索引優化建議工具最後怎麼能給使用者推薦出索引idx_2呢？關于這個問題可以用到一種稱之為What_If的政策（在微軟的AutoAdmin “what-if” index analysis utility中提出），思路就是假設索引idx_2存在并且能計算出在索引idx_2存在的情況下，此執行計劃的Cost值，如果發現其Cost隻要比目前存在的其他執行計劃的Cost值更小，就推薦這個索引。

但是MySQL計算Cost的時候，要求索引是真實存在的，為了給出索引優化建議在生産環境實際去建立各種索引是不可能的。解決的政策就是在非生産環境的資料庫執行個體上建立一個空表，并且添加所有可能被用到的索引（隻是一個中繼資料的create index操作而不去真正的向表内添加資料），最後讓優化器去自己選擇最佳的索引來生成執行計劃，這就是索引優化建議總體思路。

那麼問題來了，在非生産環境下建立的索引隻是一個中繼資料，不包含任何資料，如果就這樣讓查詢優化器去計算Cost肯定是行不通。主要原因是缺乏上面的分析的各個通路方式的關鍵變量值，是以最關鍵的如何擷取到那些影響Cost計算的關鍵的變量資訊。

從上面的“實作思路”分析可知，在計算Table scan、Index scan、range access或者ref的cost的時候，主要是從scan_time()、records_in_range()、info()這些在innodb存儲引擎裡實作的方法擷取相關統計資訊，來計算Cost大小。而索引沒有真實的資料，是以必須要修改存儲引擎裡scan_time()、records_in_range()、info()這些跟計算Cost相關的方法的實作代碼，讓其在索引資料不存在的情況下，也能比較真實地擷取到跟計算Cost值相關的統計資料。

比如，上面提到的records、keys_per_block、pages in table、records_in_range、cur_fanout等這些變量，就需要自己去計算；比如records這個變量，發現其是在Innodb中info()的實作，通過n_rows（records）指派給records，是以隻需要在info()中從生産環境的表裡擷取到真實的行數，指派給records就可以了，其實就是一個中繼資料的查詢而已。

再看一下ref類型通路方式的Cost計算過程中用到的cur_fanout變量，而這個值是來自info()中innodb_rec_per_key的值，它表示有多少個值滿足等值條件，隻需要在info()中實作innodb_rec_per_key的計算就可以了。

那麼innodb_rec_per_key又怎麼計算呢？我們可以對表進行了采樣（采樣的方案可以參考這篇《Random Sampling for Histogram Construction: How Much is Enough?》論文），采樣後的資料對某列統計有多少個不同的值，之後使用采集到的總行數/不同值的個數來擷取到innodb_rec_per_key值。

這裡還有一個非常重要的問題，上面的方案是需要修改對應存儲引擎的scan_time()、records_in_range()、info()這些關鍵的方法，但是innodb存儲引擎的代碼太複雜了，修改的難度比較大，一個比較好的方案是去修改federated存儲引擎裡的scan_time()、records_in_range()、info()這些方法，去擷取上面不同通路方式中需要用到的關鍵變量，因為其實作代碼比較簡單。

4.2.3 索引建議整體流程圖

把之前的步驟整理後的索引優化建議的流程圖如下：

1. 從待優化的SQL的關鍵位置，比如Where、On、Ordr By等位置提取關鍵字段資訊，利用提取到的字段資訊生成候選索引；
2. 通過對涉及到的表的資料進行采樣來擷取查詢優化器計算Cost時需要用到的統計資訊，之後建立不包含任何資料的索引；
3. 修改存儲引擎的代碼重新實作的scan_time()、records_in_range()、info()這些函數，使得查詢優化器能利用資料采樣後計算出來的統計資訊來比較真實的擷取Cost值；
4. 選擇一個Cost代價最小的執行計劃，而此執行計劃背後的索引就是優化建議工具推薦的索引，整體思路就是這樣。

4.2.4 驗證與跟蹤

通過全量SQL等系統擷取待優化的SQL語句後，輸入索引優化建議系統，在給真實的生産環境添加這些索引之前，需要在非生産環境進行驗證，看一下添加了相關的索引後性能是否真的有提升。如果有提升，那麼把索引在生産環境添加後（比如通過開源的改表工具Ghost等），需要通過跟蹤被影響SQL的性能，來檢視其執行時間性能是否真的有所提高。如果有性能有回退，需要及時進行告警。

4.2.5 産品展示

下面的三個圖分别展示了提供給使用者的索引建議，以及索引添加前後的執行時間效果資料對比圖。

| 4.3 基于Workload的索引優化建議

具備了單SQL的索引優化建議能力後，那麼在有限的存儲空間的限制下（比如某個執行個體最多隻能添加100G空間的索引），如果想給某個DB或者執行個體添加索引，添加哪些索引才能使整個資料庫的性能提升達到最大化呢？思路主要是參考了微軟在基于Workload索引優化建議方面的一序列相關工作。

4.3.1 總體架構

基于Workload的索引優化總體架構如下，總共有6大步驟組成，每個步驟對應着一個核心元件。

1）COLUMN GROUP RESTRICTION元件

如果一個workload（典型業務周期内包含的所有SQL總和稱之為workload）包含大量的SQL模版，那麼潛在的索引組合的數量是極其龐大的，從中挑選出讓系統性能提升最大的索引組合是一個非常困難并且耗時的過程。是以，我們需要一種稱為列組合限制（COLUMN GROUP RESTRICTION）的技術，它的目的是消除大量可能組成索引的列組，因為這些列組最多隻能對最終推薦的品質産生很小的邊際影響（就是說就算推薦了索引，最後對資料庫整體的性能的提升很有限，比如某個SQL隻執行了一次，那麼給之對應的表加索引就沒意義了），是以本步驟的輸出是workload的一組“有趣”的、對整體性能提升可能有最大提升的列組。

那麼，怎麼樣選出這個“有趣”列組呢？我們需要定義一個叫CG-Cost (g) 的函數，其值為引用列組合g的workload中所有查詢SQL的Cost的某個分數值，查詢SQL的Cost可以通過優化器估計的成本（MySQL中explain給出的Cost）來擷取。如果CG-Cost(g) ≥ f ，列組g需要保留下來（其中的0 ≤ f ≤ 1是預先确定的門檻值），否則可以丢失這個列組g。看一個具體的例子：考慮下面的表T (A,B,C,D) 的workload由“Q1，... Q10”等10個SQL組成。如果查詢引用該列，則上述矩陣中的單元格包含1，否則包含0。為了簡單起見，假設所有查詢的成本均為1個機關。

假設指定的門檻值f = 0.2，那麼工作負載的有趣列組是{A}、{B}、{C}、{A,B}、{A,C}、{B,C} 和 {A,B,C} ，以及各組的 CG-Cost(g)為1.0、0.3、0.9、0.3、0.9、0.2、0.2，而D相關的列因為CG-Cost (g) 值小于0.2而不被考慮，這樣就可以在龐大的列組合的情況下排除掉大部分列組合，加快整個索引選擇的疊代優化的過程。

2）Candidate index selection元件

單列索引（假設索引）的建立，如果考慮workload的每一個可能的索引集，那麼就會有太多的what_if索引需要考慮，這樣整體的優化過程會非常緩慢。

一個比較好的思路是為每個Query獨立确定最佳的索引集，并将屬于這些最佳的索引集中的一個或多個索引視為候選索引集。該算法背後的直覺是，如果對于workload中的單個查詢，該索引不是最佳的索引集的一部分，也不可能是整個workload的最佳的索引集的一部分。

這個算法的挑戰在于如何為workload中的每個查詢Q确定最佳的索引集呢？其實針對單個查詢來确定最佳的索引集這個問題與根據整體的基于workload來選擇最佳的索引集這個問題，兩者沒有本質的不同。是以可以設計一個特殊的workload，而這個workload隻包含一個查詢，之後可以通過上面已經實作的索引優化建議工具本身，來為單個Query擷取最佳的索引集。

單查詢擷取最佳的索引集，如何為單個SQL（SQL模版）擷取最佳的索引集呢？一個方法是單個SQL模版進行解析，在SQL關鍵的位置比如where、on、order by、group by、select等的取出關鍵的列先建立單個列作為候選索引列，再結合下面的從“Multi-column Index Generation元件”那節建立出來的多列候選索引，讓查詢優化器自己去選擇最好的索引。同時這個步驟依靠優化器選出來的索引，作為下一輪算法（索引合并）的輸入。

3）Index merging元件

上面的“CANDIDATE SELECTION”組建選出來的索引是專門針對單SQL優化的索引集，但是對于整體workload來說，這個方法會導緻較大的存儲和索引維護成本。比如，針對單SQL選出的索引都是最佳的，但是這些索引加起來後的存儲大小已經超出了允許的範圍，需要進行一些索引的合并。

如果兩個索引的列存在部分重疊，則可能可以合并，比如包括關系的索引[a,b,c] 跟[a,b] 以及有部分重疊的索引[a,b,c] 跟[c,d}] 的合并，當然[a,b] 跟[c,d}]是不能合并的。索引合并的主要思想就是采用現有的一組索引生成一組具有更低存儲和維護開銷的新索引，同時索引被合并的情況下，SQL查詢的性能能保持不變或者影響比較小，索引合并的架構如下：

4）Configuration Enumeration元件

如果基于workload的索引集有n個候選索引，但是因為存儲大小限制，要求workload工具最多隻能選擇k個索引（一般是受到了存儲大小跟Cost大小的限制），那麼應該如何選擇這個k個索引呢？一般來說，原始的暴力枚舉算法将枚舉大小為k或更小的候選索引的所有子集，并選擇總成本最低的一個索引集，這個枚舉算法确實保證了最優解。

然而，這在工程上不切實際的。因為對于n=40和k=10的情況，枚舉的索引集的數量實在太龐大，無法進行詳盡的枚舉，是以需要一個性能更好的算法的“貪心算法”，其思想是假設有n個SQL（一般是實施的時候，是n個SQL模版來替代n個SQL執行個體），整個workload W表示為 {Q1, .., Qn}。

方案是利用暴力跟貪心算法的組合，運作Greedy(m, k)算法枚舉索引集合（m為通過暴力算法選擇的m個最佳索引，k為選擇的索引的總大小），選擇一組索引，直到選出所需數量的索引或總Cost無法進一步降低，該算法選擇大小為m（其中m <= k）的最佳索引集作為“種子”，然後以貪心算法擴充這個種子，直到選擇了所有k個索引，探索算法就是從所有的候選索引中選擇一個索引，使得加了這個索引後，整體SQL的性能提升效果最大化，雖然從全局角度看未必是提升最大化的。

5）Multi-column Index Generation元件

多列索引的選擇有一個比較大的挑戰，就是候選索引實在太多，比如對于表中給定的一組k列，最多可能有k!多列索引，考慮所有排列組合将會顯著增加Configuration（索引）空間，是以需要減少列組合的搜尋空間。采用疊代方法來考慮多列索引的寬度（列的個數）。在第一次疊代中，隻考慮單列索引，基于在第一次疊代中選擇的單列索引，在往後的疊代中選擇一組可接受的雙列索引。這組“兩列索引”與單列索引一起成為第二次疊代的輸入。

那麼，具體是如何在單列的基礎上選擇其他的列組成多列索引呢？這裡有多種算法，先使用符号M (a, b) 來表示列a和b上的雙列索引，其中a是雙列索引的前導列。多列索引選擇一般有MC_LEAD、MC_ALL、MC_BASIC三種算法，這幾種算法在實作的時候一般會采用MC_LEAD算法 ，因為被證明效果最好。

• MC_LEAD：從CANDIDATE SELECTION被優化器選擇的索引，再加上某個“indexable column” 列，這個“indexable column” 列對應的索引不一定在第一輪疊代中被選擇；
• MC_ALL：從CANDIDATE SELECTION被優化器選擇的索引，再加上某個“indexable column” 列，這個“indexable column” 列對應的索引一定在第一輪疊代中被選擇，這種條件比較苛刻，業界相關的測試表明效果明确不如MC_LEAD跟MC_BASIC，雖然多列索引的搜尋空間明顯減少，提升了總體性能；
• MC_BASIC：則是把單列跟多列索引混在一個疊代周期裡建立，而不是先根據單列建議一個索引，然後再通過疊代的方式在單列的基礎上逐漸增加索引的寬度。

6）Final Indexes元件

經過幾輪疊代，當同時滿足Cost跟Storage Size的要求後退出疊代優化過程，輸出一組最優的索引優化建議集合。

| 4.4 SQL治理

具備了SQL索引優化建議的能力後，就可以對有性能問題的SQL進行三個階段的治理，1）SQL釋出生産環境前，起到防患于未然、把問題扼殺在搖籃中的作用；2）SQL生産環境執行過程中，起到實時監控發現正在執行的問題SQL，快速止損，比如誤删索引的作用；3）SQL生産環境執行過後，對于執行過的SQL，基于整體的Workload優化政策，通過離線的方式進行批量的風險SQL治理。如下圖所示：

4.4.1 風險SQL稽核（事前）

此階段是程式釋出前對潛在的風險SQL進行卡點，在公司的CI/CD平台內建流水線裡增設SQL稽核卡控點，盡量防止風險SQL被帶到生産環境引發故障，起防患于未然的作用。

具體來說，指定了一序列的規則，發現風險SQL後可以暫停釋出，并且給出相應的索引優化建議來優化SQL。

4.4.2 産品展示

根據稽核規則，展示稽核結果與建議，包括風險提示跟SQL索引優化建議等内容，稽核的規則包括是否全表掃描、掃描行數過多等内容。

4.4.3 性能問題SQL實時發現（事中）

此階段是風險SQL的實時發現功能，主要針對在SQL運作過程中因為表結構的更改（如索引誤删除）、事前審計階段遺漏掉的性能問題SQL等場景，實時地發現有性能問題的SQL，并且給出優化建議。對于實時的性能問題SQL發現使用了兩種發現政策，一種是基于規則的發現政策，另一種通過資料模組化的方式來發現政策。

基于規則的風險發現政策，其中的規則包括機關時間内的慢查詢數量、SQL執行時間、掃描行數等内容。

基于資料模組化的風險發現政策，是通過全量SQL擷取SQL的曆史執行時長來模組化，再通過Process List等資料源，把目前正在執行SQL的執行時間輸入模型，從執行時間的角度來判斷是否有異常。

4.4.4 批量SQL治理（事後）

此階段是通過對SQL執行的曆史記錄進行批量分析，從全局的角度、利用Workload索引優化建議的思想提供最佳SQL索引優化建議，批量推送優化建議給使用者進行風險SQL的治理。

4.4.5 産品展示

對于批量的SQL優化建議，這裡提供了一鍵審批索引添加的功能，可以更快的讓被建議的索引添加到生産環境上。

5 本文作者

裕鋒，來自美團基礎研發平台-基礎技術部，負責美團資料庫自治平台的相關工作。

6 參考

• https://github.com/shenyufengdb/sql
• https://github.com/percona/percona-server/blob/release-5.7.41-44
• An Efficient, Cost-Driven Index Selection Tool for Microsoft SQL Server
• plan-stitch-harnessing-the-best-of-many-plans-2
• Random Sampling for Histogram Construction: How Much is Enough?
• AutoAdmin “what-if” index analysis utility
• What is a Self-Driving Database Management System?
• https://www.microsoft.com/en-us/research/publication/self-tuning-database-systems-a-decade-of-progress/
• Automatic Database Management System Tuning Through Large-scale Machine Learning
• Query-based Workload Forecasting for Self-Driving Database Management Systems
• The TSA Method
• https://blog.langchain.dev/langchain-chat/
• https://github.com/hwchase17/langchain
• REAC T: SYNERGIZING REASONING AND ACTING IN LANGUAGE MODELS
• Evaluating the Text-to-SQL Capabilities of Large Language Models
• SQL-PALM: IMPROVED LARGE LANGUAGE MODEL ADAPTATION FOR TEXT-TO-SQL

作者:裕鋒

來源:微信公衆号:美團技術團隊

出處:https://mp.weixin.qq.com/s/mJFzXdOhxtjpjl8iNj6Ffg