一、課題背景

在老 DBA圈子裡曾流傳一句話，“研究資料庫就是研究索引，索引用好了能解決 80% 的資料庫問題”。這足以看出索引對於關係性資料庫的重要性。

對於傳統的業務 DBA 來說，其中一項重要的日常工作就是做 SQL 最佳化，而 SQL 最佳化中最主要的內容就是索引最佳化和語句的等價改造。

但是當業務和資料庫規模都達到相當的程度時，比如每日產生上億條慢查詢時，再依賴人肉運維將不是個現實的選擇。

在此前提下，資料庫運維領域慢慢發展出了基於規則、代價的慢查詢索引最佳化，這在一定程度上解放了人力，但仍舊存在一些不足，主要集中在特定規則無法滿足多變的業務需求、也無法適應不同水平的開發人員所編寫的各式各樣的 SQL，而且用來進行代價估計的統計資訊也不一定時時刻刻都是準確的。

鑑於上述，便有了此課題的由來，將機器學習的能力引入到原先需要依靠人肉運維的領域，也即——AIOps。

二、原理和關鍵流程

傳統的 SQL 最佳化主要從兩個角度出發：

根據 SQL 中使用的欄位，及其條件、關聯、聚合等用法，來判斷是否以及如何建立索引，提高資料檢索和處理效率；
分析語句的業務邏輯，然後在結構和語法上進行等價改寫，或透過 Hint，指導解析器給出更優的執行計劃。

本課題主要討論 第一種。

既然說到索引推薦，我們先回顧一下索引最佳化時的幾個要點：

常用業務邏輯中會根據哪個或哪些欄位做檢索；
常用多表關聯中使用到哪個或哪些欄位；
表以及對應欄位的統計資訊，也就是資料的分佈情況等統計特徵。

前兩點對於開發人員或者開發 DBA ，那些相對熟悉業務的人來說，問題不大，但對於運維 DBA 或者運維平臺來說，這屬於上難度了，需要事先去理解業務，或者透過篩選 TOP SQL 來做文字語義分析。

瞭解了上述過程，下面就可以介紹本課題所產出的工具了，原理上就是按照該流程進行設計的：

整個系統分為兩部分：一是模型訓練部分，屬於離線部分，即上圖左側；二是預測推薦部分，用於線上給出推薦索引，即圖中右側。

其中通用的模組包括：

SQL 語法解析和語義特徵提取
統計特徵提取
索引組合生成以及濾除
特徵工程
模型訓練、最佳化等

下面對每個模組做一簡略介紹。

2.1 語法解析&語義特徵提取

業界已有不少成熟且廣泛使用的工具，比如大名鼎鼎的 ANTLR 。但出於投入產出和專案整體考慮，本課題在該模組中選用 python 的 sqlparse 包實現。

sqlparse 是一個無驗證的 SQL 解析器，它支援解析、拆分和格式化SQL語句。其官方網址為，想要深入瞭解學習的可以直接閱讀原始碼，這個專案原始碼的可讀性還不錯，註釋很細緻。

其中包含 4 個主要的模組級別函式：split，format，parse，parsestream，以及三個很關鍵的基類：Token，TokenList 和 Statement。具體描述和用法就不在本文贅述，有許多資料可供參考。

接下來，要實現根據給定的 SQL 語句解析得到具有一定語義特徵的關鍵欄位，特徵包括：WHERE、JOIN、聚合函式、ORDER BY、GROUP BY，以及常見的等值和範圍查詢等等。

下圖簡單說明了解析後的 SQL（即 Tokens），如何根據不同語義提取所需要的欄位的大致流程。

2.2 統計特徵提取

上面完成了 SQL 語句的解析，得到了語句相關的表（table）和列（column），並且透過分析，獲取了語義特徵資訊。

但光有這些是不夠的，畢竟由此進行列舉和排列組合得到的單列及多列索引會很多，僅僅透過語義特徵去進行模型訓練，可以預想到的準確率並不會太高。所以接下來我們要做的就是透過訪問資料庫後設資料，來提取表、列的統計特徵。

再進行索引最佳化時，DBA 通常會關注以下指標：

表的「總行數」
列的「型別」
列的「空值個數」
列的「不同值個數」
以及列的「選擇性」等等

當然還可以根據需要擴充統計特徵，只要能輔助判斷建立索引的必要性，就可以納入特徵向量的構建中來。

以 MySQL 為例，其統計資訊是指資料庫透過取樣、統計出來的表、索引的相關資訊，例如，表的記錄數、聚集索引 page 個數、欄位的 Cardinality 等等。MySQL 在生成執行計劃時，需要根據索引的統計資訊進行估算，計算出最低代價（或者說是最小開銷）的執行計劃。當然，其它資料庫統計資訊的概念也是相似的，甚至可能相比 MySQL 來說會更豐富、更完善。

MySQL 的持久化統計資訊儲存在 mysql.innodb_index_stats 和 mysql.innodb_table_stats 中，非持久化的統計資訊儲存在記憶體中，本文暫時不涉及。

對於 MySQL 8.0，官方才推出了直方圖（histogram），直方圖資料存放在 information_schema.column_statistics 這個系統表下，每行記錄對應一個欄位的直方圖，以json格式儲存。同時，新增了一個引數 histogram_generation_max_mem_size 來配置建立直方圖記憶體大小。

基於以上資訊，可以實現統計特徵的提取，如下圖所示，分別為：獲取表統計資訊、獲取指定欄位 cardinality、獲取指定欄位直方圖資訊。

2.3 索引組合

在索引相關的特徵提取完之後，到設計特徵向量之前，還有一步需要處理，就是如何生成完整的候選索引組合，其中包括單列索引和複合索引。

對於單個 SQL，其中每個表的訪問只能使用該表其中一條索引。所以需要針對該 SQL 涉及到的所有表，以及每個表在該 SQL 中的所有具有語義特徵的欄位進行收集，再進行排列，得到單列、雙列、三列索引（注：當前暫時不考慮 >3 列的索引推薦）。

下圖展示了不同索引組合的輸出：

2.4 特徵工程&建模

特徵，就是目標物件自身擁有的，或者可以透過加工計算得來的屬性。對於一個候選索引（準確地講，就是某個表的欄位組合）來說，前面已經介紹了其兩大類特徵：「語義特徵」和「統計特徵」。接下來就要基於此進行特徵向量的構建。

以上列出了本課題中使用到的具體特徵維度，當然也可以根據自身需要進行擴充套件。至於多列索引的特徵，需要將每個列的特徵拼在一起，同時需要注意按順序拼接，因為(col1,col2) 和 (col2,col1)是完全不同的兩個索引，索引推薦對欄位順序是敏感的。

分別針對單列、兩列和三列候選索引（欄位組合）進行特徵向量構建後，需要進行正負樣本的標記，即：針對某個查詢 Q1，涉及表 A 和表 B，其中 A 表生成的候選索引中，為查詢 Q1中使用到的索引標記為正樣本（tag=1），其餘候選索引均標記為負樣本（tag=0）。這樣，就為推薦模型準備好訓練集。

2.5 模型訓練和預測

前面準備好的訓練集還需要進行一些預處理，包括空值處理、全 0 行預清除、資料型別標準化等等。由於單列和多列索引的特徵維度不同（即欄位個數不同），不能進行模型混用，需要分開訓練和預測。本課題最終選用 XGBoost 二分類模型，離線訓練好的模型的可以透過 joblib 包儲存為二進位制檔案，並於線上模組匯入後進行預測。當然，真正是否推薦並且採納系統給出的索引時，還可以在模測環境進行實測驗證，每次驗證以及使用者的反饋得到的資訊，又可以存回訓練資料集用來迭代模型，用於提高預測準確率。

三、未來規劃

目前該課題的工程實現還只支援 MySQL 資料庫，後續需要擴充套件到其它關係型資料庫，如 openGauss 等。

另外，大模型技術的興起，也越來越被業界認可，尤其在問答領域。鑑於此也可以嘗試將開源大模型微調並接入索引推薦工作流中，為使用者提供更便捷的使用體驗。

作者：中國工商銀行軟體開發中心廣州技術部

DBA 圈傳瘋了！用好索引推薦，竟解決 80% 的資料庫問題？