Mybatis-SQL分析組件

背景

大促備戰，最大的隱患項之一就是慢sql，帶來的破壞性最大，也是日常工作中經常帶來整個應用抖動的最大隱患，而且對sql好壞的評估有一定的技術要求，有一些缺乏經驗或者因為不夠仔細造成一個壞的sql成功走到了線上，等發現的時候要麼是造成了線上影響、報警、或者後置的慢sql採集發現，這時候一般無法快速止損，需要修改代碼上線、或者調整資料庫索引。

核心痛點：

1、無法提前發現慢sql，可能惡化為慢sql的語句

2、線上出現慢sql後，無法快速止損

解決思路

1、把問題解決在上線之前，最好的辦法就是在測試階段，甚至在開發階段就發現一個sql的好壞

2、線上發現慢sql後除了改代碼上線、調整資料庫表索引的方式外，支持熱更新的方式替換sql語句。

部門內部，目前大部分資料庫框架採用的mybatis，然後基於mybatis本身的實現機制中，開發一個mybatis組件，可以自動對運行的sql進行提取和分析，定製一套預設的分析規則，讓sql在開發環境和測試環境執行的時候，就能夠做初步的評估，把有問題的慢sql在這個階段暴露出來；同時具備sql替換功能，線上上出現問題sql的時候，可以通過ducc配置快速完成對一個sql的線上替換，大大降低線上問題的止損時間。

開源方案調研

目前，主流的sql分析組件，核心功能主要放在了兩個方向：1、慢sql的分析和優化建議 2、sql的優化重寫功能，而且主要偏運維的輔助功能無法做到無侵入的和應用代碼進行集成。也就無法實現我們的核心痛點，慢sql提前分析預警和動態sql替換。

設計方案

核心功能：SQL分析預警能力、SQL替換能力

詳細設計

主要分為8個功能模塊

模塊一：core 主要負責組件的接入到mybatis，以及其它模塊的編排調用

模塊二：config 主要負責組件配置信息的初始化

模塊三：extrat 主要通過解析mybatis 相關對象，提取完整的待執行sql

模塊四：analysis 主要拼接分析語句，執行explain分析語句並獲取分析結果

模塊五：rule sql分析規則的載入和初始化，支持自定義規則

目前預設規則（持續擴展）：

1、查詢未匹配索引

2、匹配索引過濾效果較差

3、返回行數過多

4、使用了文件排序

模塊六：score 基於分析結果和配置的評分規則進行匹配打分，優化建議組裝

模塊七：out 輸出模塊，對於輸出結果進行輸出，目前已error日誌、MQ兩種輸出方式

模塊八：replace替換模塊，可以對sql語句基於ducc配置進行動態替換

使用方法

1、引入依賴jar包

<dependency>
    <groupId>com.jd.sql.analysis</groupId>
    <artifactId>sql-analysis</artifactId>
    <version>1.2-SNAPSHOT</version>
</dependency>

2、配置組件xml

<configuration>
    <plugins>
        <plugin interceptor="com.jd.sql.analysis.core.SqlAnalysisAspect" >
            <!-- 開啟sql分析功能最簡配置 -->
            <property name="analysisSwitch" value="true"/>

            <!-- 開啟sql替換功能最簡配置 -->
            <property name="sqlReplaceModelSwitch" value="true"/>
            <property name="duccAppName" value="workbench-backend"/>
            <property name="duccUri" value="ucc://workbench-backend:[email protected]/v1/namespace/workbench_backend/config/default/profiles/test?longPolling=60000&necessary=false"/>
            <property name="duccMonitorKey" value="refundBugFlag"/>
        </plugin>
    </plugins>
</configuration>

3、核心配置項

4、預設分析效果展示

4.1、慢sql分析效果

4.2、sql動態替換效果

5、實踐使用方案

5.1、慢sql分析-日誌輸出+關鍵詞告警

<configuration>
    <plugins>
        <plugin interceptor="com.jd.sql.analysis.core.SqlAnalysisAspect" >
            <property name="analysisSwitch" value="true"/>
        </plugin>
    </plugins>
</configuration>

5.2、慢sql分析-日誌輸出+mq輸出+es存儲+Kibana分析

<configuration>
    <plugins>
        <plugin interceptor="com.jd.sql.analysis.core.SqlAnalysisAspect" >
            <property name="appName" value="workbench-backend"/>
            <property name="analysisSwitch" value="true"/>
            <property name="outputModel" value="mq"/>
            <property name="mqApp" value="qlstation"/>
            <property name="mqUser" value="qlstation"/>
            <property name="mqPassword" value="D1BCC547"/>
            <property name="mqAddress" value="jmq-testcluster.jd.local:50088"/>
            <property name="mqTopic" value="jdl_kds_key_node_log"/>
        </plugin>
    </plugins>
</configuration>

最終效果

5.3、慢sql替換-ducc配置動態更新sql語句

<configuration>
    <plugins>
        <plugin interceptor="com.jd.sql.analysis.core.SqlAnalysisAspect" >
            <property name="sqlReplaceModelSwitch" value="true"/>
            <property name="duccAppName" value="workbench-backend"/>
            <property name="duccUri" value="ucc://workbench-backend:[email protected]/v1/namespace/workbench_backend/config/default/profiles/test?longPolling=60000&necessary=false"/>
            <property name="duccMonitorKey" value="sqlReplaceConfig"/>
        </plugin>
    </plugins>
</configuration>

發現慢sql

ducc配置

線上sql被動態替換

註意：功能正式修複後，需去掉該配置，該功能僅供應急處理線上問題，不建議作為功能長期使用

性能測試

測試環境千次普通sql查詢，每種場景進行了5次測試

未啟用插件耗時：11108ms，10237ms，9482ms，7938ms，8196ms

開啟sql分析耗時：16619ms，17333ms，16321ms，19057ms，18164ms

實際配置，只有首次執行或者間隔時間執行，單次影響10ms左右）

開啟sql替換耗時：10642ms，8803ms，8353ms，8830ms，9170ms

基本無影響

適用場景

1、慢sql預防

2、線上問題止損

優勢

1、核心優勢：執行時分析sql，區別於傳統的依賴sql執行耗時來評估慢sql，直接基於語法和索引進行前置分析，不僅能預防某些壞sql在上線後發現是慢sql，還能給出sql優化建議，可以大限度的避免線上產生慢sql。支持動態對線上sql進行替換，可以對線上問題快速止損。

2、性能：基於性能和不同的使用場景考慮，支持定製化配置，每個sql是否僅進行一次檢查、或者按某個時間間隔進行配置。sql替換幾乎無損耗。

3、擴展：基於後續sql評分規則的擴展、以及分析結果以不同的方式輸出的考慮，支持評分規則、輸出方式的自定義擴展。

4、成本：接入成本低，無代碼侵入。

作者：京東物流 扈海濤

來源：京東雲開發者社區