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、核心設定項

屬性	用途	是否必填	預設值	備註
analysisSwitch	是否開啟分析功能	是	false
onlyCheckOnce	是否對一個sqlid只分析一次	非	true
checkInterval	每個sqlid分析間隔	非	300000毫秒	onlyCheckOnce 為false才生效
exceptSqlIds	需要過濾不分析的sqlid	非
sqlType	分析的sql型別	非	預設select、update	支援
scoreRuleLoadClass	評分規則載入器，用於擴充套件自定義規則	非
outModel	預設輸出方式	非	預設值：LOG	支援LOG、MQ兩種方式
outputClass	評分結果輸出類，用於擴充套件自定義結果輸出方式	非
sqlReplaceModelSwitch	sql替換模組是否開啟	非	預設 false
duccAppName	ducc設定的應用名稱（jdos）	非
duccUri	ducc uri設定	非
duccMonitorKey	sql替換組態檔對應的key	非

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、成本：接入成本低，無程式碼侵入。

作者：京東物流 扈海濤

來源：京東雲開發者社群