SpringBoot統計、監控SQL執行情況的方法詳解

2022-02-17 16:00:03

1 基本概念

Druid 是Java語言中最好的資料庫連線池。

雖然 HikariCP 的速度稍快，但是，Druid能夠提供強大的監控和擴充套件功能，也是阿里巴巴的開源專案。

Druid是阿里巴巴開發的號稱為監控而生的資料庫連線池，在功能、效能、擴充套件性方面，都超過其他資料庫連線池，包括DBCP、C3P0、BoneCP、Proxool、JBoss DataSource等等等，秒殺一切。

Druid 可以很好的監控 DB 池連線和 SQL 的執行情況，天生就是針對監控而生的 DB 連線池。

Spring Boot 預設資料來源 HikariDataSource 與 JdbcTemplate中已經介紹 Spring Boot 2.x 預設使用 Hikari 資料來源，可以說 Hikari 與 Driud 都是當前 Java Web 上最優秀的資料來源。

而Druid已經在阿里巴巴部署了超過600個應用，經過好幾年生產環境大規模部署的嚴苛考驗！

stat：Druid內建提供一個StatFilter,用於統計監控資訊。
wall：Druid防禦SQL隱碼攻擊的WallFilter就是通過Druid的SQL Parser分析。Druid提供的SQL Parser可以在JDBC層攔截SQL做相應處理，比如說分庫分表、審計等。
log4j2：這個就是紀錄檔記錄的功能，可以把sql語句列印到log4j2 供排查問題。

2 新增依賴

pom.xml

<!-- 阿里巴巴的druid資料來源 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.23</version>
</dependency>
<!-- mysql8 驅動-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<!--使用 log4j2 記錄紀錄檔-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-log4j2</artifactId>
</dependency>
<!--
mybatis，引入了 SpringBoot的 JDBC 模組，
所以，預設是使用 hikari 作為資料來源
-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
    <exclusions>
        <!-- 排除預設的 HikariCP 資料來源 -->
        <exclusion>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </exclusion>
    </exclusions>
</dependency>

3 設定相關屬性

設定Druid資料來源（連線池）：如同以前 c3p0、dbcp 資料來源可以設定資料來源連線初始化大小、最大連線數、等待時間、最小連線數等一樣，Druid 資料來源同理可以進行設定；

設定 Druid web 監控 filter（WebStatFilter）：這個過濾器的作用就是統計 web 應用請求中所有的資料庫資訊，比如發出的 sql 語句，sql 執行的時間、請求次數、請求的 url 地址、以及seesion 監控、資料庫表的存取次數等等。

設定 Druid 後臺管理 Servlet（StatViewServlet）： Druid 資料來源具有監控的功能，並提供了一個 web 介面方便使用者檢視，類似安裝路由器時，人家也提供了一個預設的 web 頁面；需要設定 Druid 的後臺管理頁面的屬性，比如登入賬號、密碼等；

注意：

Druid Spring Boot Starter 設定屬性的名稱完全遵照 Druid，可以通過 Spring Boot 組態檔來設定Druid資料庫連線池和監控，如果沒有設定則使用預設值。

application.yml

########## 設定資料來源 （Druid）##########
spring:
  datasource:
    ########## JDBC 基本設定 ##########
    username: xxx
    password: xxx
    driver-class-name: com.mysql.cj.jdbc.Driver   # mysql8 的連線驅動
    url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=Asia/Shanghai
    platform: mysql                               # 資料庫型別
    type: com.alibaba.druid.pool.DruidDataSource  # 指定資料來源型別
    ########## 連線池 設定 ##########
    druid:
      # 設定初始化大小、最小、最大
      initial-size: 5
      minIdle: 10
      max-active: 20
      # 設定獲取連線等待超時的時間(單位：毫秒)
      max-wait: 60000
      # 設定間隔多久才進行一次檢測，檢測需要關閉的空閒連線，單位是毫秒
      time-between-eviction-runs-millis: 2000
      # 設定一個連線在池中最小生存的時間，單位是毫秒
      min-evictable-idle-time-millis: 600000
      max-evictable-idle-time-millis: 900000
      # 用來測試連線是否可用的SQL語句,預設值每種資料庫都不相同,這是mysql
      validationQuery: select 1
      # 應用向連線池申請連線，並且testOnBorrow為false時，連線池將會判斷連線是否處於空閒狀態，如果是，則驗證這條連線是否可用
      testWhileIdle: true
      # 如果為true，預設是false，應用向連線池申請連線時，連線池會判斷這條連線是否是可用的
      testOnBorrow: false
      # 如果為true（預設false），當應用使用完連線，連線池回收連線的時候會判斷該連線是否還可用
      testOnReturn: false
      # 是否快取preparedStatement，也就是PSCache。PSCache對支援遊標的資料庫效能提升巨大，比如說oracle
      poolPreparedStatements: true
      # 要啟用PSCache，必須設定大於0，當大於0時， poolPreparedStatements自動觸發修改為true，
      # 在Druid中，不會存在Oracle下PSCache佔用記憶體過多的問題，
      # 可以把這個數值設定大一些，比如說100
      maxOpenPreparedStatements: 20
      # 連線池中的minIdle數量以內的連線，空閒時間超過minEvictableIdleTimeMillis，則會執行keepAlive操作
      keepAlive: true
      # Spring 監控，利用aop 對指定介面的執行時間，jdbc數進行記錄
      aop-patterns: "com.springboot.template.dao.*"
      ########### 啟用內建過濾器（第一個 stat必須，否則監控不到SQL）##########
      filters: stat,wall,log4j2
      # 自己設定監控統計攔截的filter
      filter:
        # 開啟druiddatasource的狀態監控
        stat:
          enabled: true
          db-type: mysql
          # 開啟慢sql監控，超過2s 就認為是慢sql，記錄到紀錄檔中
          log-slow-sql: true
          slow-sql-millis: 2000
        # 紀錄檔監控，使用slf4j 進行紀錄檔輸出
        slf4j:
          enabled: true
          statement-log-error-enabled: true
          statement-create-after-log-enabled: false
          statement-close-after-log-enabled: false
          result-set-open-after-log-enabled: false
          result-set-close-after-log-enabled: false
      ########## 設定WebStatFilter，用於採集web關聯監控的資料 ##########
      web-stat-filter:
        enabled: true                   # 啟動 StatFilter
        url-pattern: /*                 # 過濾所有url
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*" # 排除一些不必要的url
        session-stat-enable: true       # 開啟session統計功能
        session-stat-max-count: 1000    # session的最大個數,預設100
      ########## 設定StatViewServlet（監控頁面），用於展示Druid的統計資訊 ##########
      stat-view-servlet:
        enabled: true                   # 啟用StatViewServlet
        url-pattern: /druid/*           # 存取內建監控頁面的路徑，內建監控頁面的首頁是/druid/index.html
        reset-enable: false              # 不允許清空統計資料,重新計算
        login-username: root            # 設定監控頁面存取密碼
        login-password: 123
        allow: 127.0.0.1           # 允許存取的地址，如果allow沒有設定或者為空，則允許所有存取
        deny:                                        # 拒絕存取的地址，deny優先於allow，如果在deny列表中，就算在allow列表中，也會被拒絕

上述組態檔的引數可以在 com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties 和 org.springframework.boot.autoconfigure.jdbc.DataSourceProperties中找到;

3.1 如何設定 Filter

可以通過 spring.datasource.druid.filters=stat,wall,log4j ...的方式來啟用相應的內建Filter，不過這些Filter都是預設設定。如果預設設定不能滿足需求，可以放棄這種方式，通過組態檔來設定Filter，下面是例子。

# 設定StatFilter  
spring.datasource.druid.filter.stat.enabled=true
spring.datasource.druid.filter.stat.db-type=h2
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=2000
# 設定WallFilter  
spring.datasource.druid.filter.wall.enabled=true
spring.datasource.druid.filter.wall.db-type=h2
spring.datasource.druid.filter.wall.config.delete-allow=false
spring.datasource.druid.filter.wall.config.drop-table-allow=false

目前為以下 Filter 提供了設定支援，根據（spring.datasource.druid.filter.*）進行設定。

StatFilter
WallFilter
ConfigFilter
EncodingConvertFilter
Slf4jLogFilter
Log4jFilter
Log4j2Filter
CommonsLogFilter

不想使用內建的 Filters，要想使自定義 Filter 設定生效需要將對應 Filter 的 enabled 設定為 true ，Druid Spring Boot Starter 預設禁用 StatFilter，可以將其 enabled 設定為 true 來啟用它。

4 監控頁面

（1）啟動專案後，存取/druid/login.html來到登入頁面，輸入使用者名稱密碼登入

（2）資料來源頁面是當前DataSource設定的基本資訊，上述設定的Filter可以在裡面找到，如果沒有設定Filter（一些資訊會無法統計，例如“SQL監控”，會無法獲取JDBC相關的SQL執行資訊）

（3） SQL監控頁面，統計了所有SQL語句的執行情況

（4）URL監控頁面，統計了所有Controller介面的存取以及執行情況

（5）Spring 監控頁面，利用aop 對指定介面的執行時間，jdbc數進行記錄

（6）SQL防火牆頁面

druid提供了黑白名單的存取，可以清楚的看到sql防護情況。

（7）Session監控頁面

可以看到當前的session狀況，建立時間、最後活躍時間、請求次數、請求時間等詳細引數。

（8）JSONAPI 頁面

通過api的形式存取Druid的監控介面，api介面返回Json形式資料。

5 sql監控

設定 Druid web 監控 filter（WebStatFilter）這個過濾器，作用就是統計 web 應用請求中所有的資料庫資訊，比如發出的 sql 語句，sql 執行的時間、請求次數、請求的 url 地址、以及seesion 監控、資料庫表的存取次數等等。

spring:
  datasource:
    druid:
      ########## 設定WebStatFilter，用於採集web關聯監控的資料 ##########
      web-stat-filter:
        enabled: true                   # 啟動 StatFilter
        url-pattern: /*                 # 過濾所有url
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*" # 排除一些不必要的url
        session-stat-enable: true       # 開啟session統計功能
        session-stat-max-count: 1000    # session的最大個數,預設100

6 慢sql記錄

有時候，系統中有些SQL執行很慢，我們希望使用紀錄檔記錄下來，可以開啟Druid的慢SQL記錄功能

spring:
  datasource:
    druid:
      filter:
        stat:
          enabled: true         # 開啟DruidDataSource狀態監控
          db-type: mysql        # 資料庫的型別
          log-slow-sql: true    # 開啟慢SQL記錄功能
          slow-sql-millis: 2000 # 預設3000毫秒，這裡超過2s，就是慢，記錄到紀錄檔

啟動後，如果遇到執行慢的SQL，便會輸出到紀錄檔中

7 spring 監控

存取之後spring監控預設是沒有資料的；

這需要匯入SprngBoot的AOP的Starter

<!--SpringBoot 的aop 模組-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-aop</artifactId>
</dependency>

需要在 application.yml 設定:

Spring監控AOP切入點，如com.springboot.template.dao.*,設定多個英文逗號分隔

spring.datasource.druid.aop-patterns="com.springboot.template.dao.*"

8 去 Ad（廣告）

存取監控頁面的時候，你可能會在頁面底部（footer）看到阿里巴巴的廣告

原因：引入的druid的jar包中的common.js(裡面有一段js程式碼是給頁面的footer追加廣告的)

如果想去掉，有兩種方式：

(1) 直接手動註釋這段程式碼

如果是使用Maven，直接到本地倉庫中，查詢這個jar包

要註釋的程式碼：

// this.buildFooter();

common.js的位置：

com/alibaba/druid/1.1.23/druid-1.1.23.jar!/support/http/resources/js/common.js

(2) 使用過濾器過濾

註冊一個過濾器，過濾common.js的請求，使用正規表示式替換相關的廣告內容

@Configuration
@ConditionalOnWebApplication
@AutoConfigureAfter(DruidDataSourceAutoConfigure.class)
@ConditionalOnProperty(name = "spring.datasource.druid.stat-view-servlet.enabled",
havingValue = "true", matchIfMissing = true)
public class RemoveDruidAdConfig {
    /**
    * 方法名: removeDruidAdFilterRegistrationBean
    * 方法描述 除去頁面底部的廣告
    * @param properties com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties
    * @return org.springframework.boot.web.servlet.FilterRegistrationBean
    */
    @Bean
    public FilterRegistrationBean removeDruidAdFilterRegistrationBean(DruidStatProperties properties) {
        // 獲取web監控頁面的引數
        DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
        // 提取common.js的設定路徑
        String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";
        String commonJsPattern = pattern.replaceAll("\*", "js/common.js");
        final String filePath = "support/http/resources/js/common.js";
        //建立filter進行過濾
        Filter filter = new Filter() {
            @Override
            public void init(FilterConfig filterConfig) throws ServletException {}
            @Override
            public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
                chain.doFilter(request, response);
                // 重置緩衝區，響應頭不會被重置
                response.resetBuffer();
                // 獲取common.js
                String text = Utils.readFromResource(filePath);
                // 正則替換banner, 除去底部的廣告資訊
                text = text.replaceAll("<a.*?banner"></a><br/>", "");
                text = text.replaceAll("powered.*?shrek.wang</a>", "");
                response.getWriter().write(text);
            }
            @Override
            public void destroy() {}
        };
        FilterRegistrationBean registrationBean = new FilterRegistrationBean();
        registrationBean.setFilter(filter);
        registrationBean.addUrlPatterns(commonJsPattern);
        return registrationBean;
    }
}

兩種方式都可以，建議使用的是第一種，從根源解決

9 獲取 Druid 的監控資料

Druid 的監控資料可以在開啟 StatFilter 後，通過 DruidStatManagerFacade 進行獲取;

DruidStatManagerFacade#getDataSourceStatDataList 該方法可以獲取所有資料來源的監控資料，

除此之外 DruidStatManagerFacade 還提供了一些其他方法，可以按需選擇使用。

@RestController
@RequestMapping(value = "/druid")
public class DruidStatController {
    @GetMapping("/stat")
    public Object druidStat(){
        // 獲取資料來源的監控資料
        return DruidStatManagerFacade.getInstance().getDataSourceStatDataList();
    }
}