美團點評SQL優化工具SQLAdvisor開源

jopen 7年前發布 | 29K 次閱讀 SQL 開源

介紹

在數據庫運維過程中，優化 SQL 是 DBA 團隊的日常任務。例行 SQL 優化，不僅可以提升程序性能，還能夠降低線上故障的概率。

目前常用的 SQL 優化方式包括但不限于：業務層優化、SQL邏輯優化、索引優化等。其中索引優化通常通過調整索引或新增索引從而達到 SQL 優化的目的。索引優化往往可以在短時間內產生非常巨大的效果。如果能夠將索引優化轉化成工具化、標準化的流程，減少人工介入的工作量，無疑會大大提高DBA的工作效率。

SQLAdvisor 是由美團點評公司北京DBA團隊開發維護的 SQL 優化工具： 輸入SQL，輸出索引優化建議 。 它基于 MySQL 原生詞法解析，再結合 SQL 中的 where 條件以及字段選擇度、聚合條件、多表 Join 關系等最終輸出最優的索引優化建議。 目前 SQLAdvisor 在公司內部大量使用，較為成熟、穩定。

現在，我們非常高興地將 SQLAdvisor 開源，項目 GitHub 地址： https://github.com/Meituan-Dianping/SQLAdvisor 。我們已經把相關開發工作全面轉到 GitHub 上，開源版本和內部使用版本保持完全一致。希望與業內有類似需求的團隊，一起打造一款優秀的 SQL 優化產品。

SQLAdvisor架構流程圖：

美團點評SQL優化工具SQLAdvisor開源

SQLAdvisor使用舉例

sql: SELECT id FROM crm_loan WHERE id_card = '1234567'
cmd: ./sqladvisor -h xx -P xx -u xx -pxx -d xx -q "SELECT id FROM crm_loan WHERE id_card = '1234567'"
SQLAdvisor輸出: alter table crm_loan add index idx_id_card(id_card)

SQLAdvisor快速入門教程

SQLAdvisor的優點

  • 基于 MySQL 原生詞法解析，充分保證詞法解析的性能、準確定以及穩定性；
  • 支持常見的 SQL(Insert/Delete/Update/Select)；
  • 支持多表 Join 并自動邏輯選定驅動表；
  • 支持聚合條件 Order by 和 Group by；
  • 過濾表中已存在的索引。

SQLAdvisor原理介紹

Join 處理

  1. Join語法分為兩種：Join on 和 Join using，并且 Join on 有時會存在 where 條件中。
  2. 分析 Join 條件首先會得到一個 nested_join 的 table list，通過判斷它的 join_using_fields 字段是否為空來區分 Join on 與 Join using。
  3. 生成的 table list 以二叉樹的形式進行存儲，以后序遍歷的方式對二叉樹進行遍歷。
  4. 生成內部解析樹時，right Join 會轉換成 left Join。
  5. Join 條件會存在當層的葉子節點上，如果左右節點都是葉子節點，會存在右葉子節點。
  6. 每一個非葉子節點代表一次 Join 的結果。

上述實現時，涉及的函數為：mysql_sql_parse_join(TABLE_LIST join_table) mysql_sql_parse_join(Item join_condition) ，主要流程圖如下：

美團點評SQL優化工具SQLAdvisor開源

where 處理

  1. 主要是提取 SQL 語句的 where 條件。where 條件中一般由 AND 和 OR 連接符進行連接，因為 OR 比較難以處理，所以忽略，只處理 AND 連接符。
  2. 由于 where 條件中可以存在 Join 條件，因此需要進行區分。
  3. 依次獲取 where 條件，當條件中的操作符是 like，如果不是前綴匹配則丟棄這個條件。
  4. 根據條件計算字段的區分度按照高低進行倒序排，如果小于30則丟棄。同時使用最左原則將 where 條件進行有序排列。

計算區分度

  1. 通過 “show table status like” 獲得表的總行數 table_count。
  2. 通過計算選擇表中已存在的區分度最高的索引 best_index，同時Primary key > Unique key > 一般索引。
  3. 通過計算獲取數據采樣的起始值offset與采樣范圍rand_rows：
    • offset = (table_count / 2) > 10W ? 10W : (table_count / 2)
    • rand_rows =(table_count / 2) > 1W ? 1W : (table_count / 2)
    • 使用select count(1) from (select field from table force index(best_index) order by cl.. desc limit rand_rows) where field_print 得到滿足條件的rows。
    • cardinality = rows == 0 ? rand_rows : rand_rows / rows;
    • 計算完成選擇度后，會根據選擇度大小，將該條件添加到該表中的備選索引中。

主要涉及的函數為：mysql_sql_parse_field_cardinality_new() 計算選擇度。

美團點評SQL優化工具SQLAdvisor開源

添加備選索引

  1. mysql_sql_parse_index()將條件按照選擇度添加到備選索引鏈表中。
  2. 上述兩函數的流程圖如下所示：
    美團點評SQL優化工具SQLAdvisor開源

Group 與 Order 處理

  1. Group 字段與 Order 字段能否用上索引，需要滿足如下條件：
    • 涉及到的字段必須來自于同一張表，并且這張表必須是確定下來的驅動表。
    • Group by 優于 Order by， 兩者只能同時存在一個。
    • Order by 字段的排序方向必須完全一致，否則丟棄整個 Order by 字段列。
    • 當 Order by 條件中包含主鍵時，如果主鍵字段為 Order by。 字段列末尾，忽略該主鍵，否則丟棄整個 Order by 字段列。
  2. 整個索引列排序優先級：等值>(group by | order by )> 非等值。
  3. 該過程中設計的函數主要有：
    • mysql_sql_parse_group() 判斷 Group 后的字段是否均來自于同一張表。
    • mysql_sql_parse_order() 判斷 Order 后的條件是否可以使用。
    • mysql_sql_parse_group_order_add() 將字段依次按照規則添加到備選索引鏈表中。
      美團點評SQL優化工具SQLAdvisor開源 美團點評SQL優化工具SQLAdvisor開源

驅動表選擇

  1. 經過前期的 where 解析、Join 解析，已經將 SQL 中表關聯關系存儲起來，并且按照一定邏輯將候選驅動表確定下來。
  2. 在侯選驅動表中，按照每一張表的侯選索引字段中第一個字段進行計算表中結果集大小。
  3. 使用 explain select * from table where field 來計算表中結果集。
  4. 結果集小最小的被確為驅動表。
  5. 步驟中涉及的函數為：final_table_drived()，在該函數中，調用了函數 get_join_table_result_set() 來獲取每張驅動候選表的行數。

添加被驅動表備選索引

  1. 通過上述過程，已經選擇了驅動表，也通過解析保存了語句中的條件。
  2. 由于選定了驅動表，因此需要對被驅動表的索引，根據 Join 條件進行添加。
  3. 該過程涉及的函數主要是：mysql_index_add_condition_field()，流程如下：
    美團點評SQL優化工具SQLAdvisor開源

輸出建議

  1. 通過上述步驟，已經將每張表的備選索引鍵全部保存。此時，只要判斷每張表中的候選索引鍵是否在實際表中已存在。沒有索引，則給出建議增加對應的索引。
  2. 該步驟涉及的函數是：print_index() ，主要的流程圖為：
    美團點評SQL優化工具SQLAdvisor開源

SQLAdvisor版本更新

  • Functionality Added or Changed
    • 調整架構將 SQLParser 與 SQLAdvisor 模塊隔離，方便調試。
    • 重新架構多表 Join 關系的 find_join_elements() 函數，思路更加清晰。
    • 修改選定驅動表的策略，確保驅動表為小結果集。
    • 添加 where 條件中的 like 處理。
    • 優化 Order by 邏輯，忽略 Order by primary key 場景。
    • 輸出索引建議前，增加判斷索引是否已存在。
    </li>
  • Bugs Fixed
    • 修復 SQL 無法處理中文問題。
    • 修復字段多次出現在 where 條件中從而導致多次出現在索引列中問題。
    • 修復在 find_best_index() 函數中，對 MySQL API 中的 result 對象提前 free，導致指針失效問題。
      • </ul> </li> </ul>

      愿景

      和各位同行共同打造一款企業級優秀的 SQL 優化產品，希望大家能夠積極參與。

      歡迎大家將需求或發現的 Bug 在 Github 上提交 issue，幫助 SQLAdvisor 逐漸壯大；也歡迎大家在 SQLAdvisor 用戶交流群（QQ: 231434335）相互交流，共同學習。

      SQLAdvisor手冊

      1. SQLAdvisor快速入門教程 .
      2. SQLAdvisor原理和架構 .
      3. SQLAdvisor release notes .
      4. SQLAdvisor開發規范 .
      5. FAQ .

       

      來自： http://tech.meituan.com/sqladvisor_pr.html

       本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
       轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
       本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！
        本文地址：http://www.baiduhome.net/news/view/23771a61