SpringBoot 如何統(tǒng)計(jì)、監(jiān)控 SQL運(yùn)行情況？

來(lái)源：juejin.cn/post/7062506923194581029

1 基本概念

Druid 是Java語(yǔ)言中最好的數(shù)據(jù)庫(kù)連接池。

雖然 HikariCP 的速度稍快，但是，Druid能夠提供強(qiáng)大的監(jiān)控和擴(kuò)展功能，也是阿里巴巴的開源項(xiàng)目。

Druid是阿里巴巴開發(fā)的號(hào)稱為監(jiān)控而生的數(shù)據(jù)庫(kù)連接池，在功能、性能、擴(kuò)展性方面，都超過(guò)其他數(shù)據(jù)庫(kù)連接池，包括DBCP、C3P0、BoneCP、Proxool、JBoss DataSource等等等，秒殺一切。

Druid 可以很好的監(jiān)控 DB 池連接和 SQL 的執(zhí)行情況，天生就是針對(duì)監(jiān)控而生的 DB 連接池。

Spring Boot 默認(rèn)數(shù)據(jù)源 HikariDataSource 與 JdbcTemplate中已經(jīng)介紹 Spring Boot 2.x 默認(rèn)使用 Hikari 數(shù)據(jù)源，可以說(shuō) Hikari 與 Driud 都是當(dāng)前 Java Web 上最優(yōu)秀的數(shù)據(jù)源。

而Druid已經(jīng)在阿里巴巴部署了超過(guò)600個(gè)應(yīng)用，經(jīng)過(guò)好幾年生產(chǎn)環(huán)境大規(guī)模部署的嚴(yán)苛考驗(yàn)！

• stat：Druid內(nèi)置提供一個(gè)StatFilter,用于統(tǒng)計(jì)監(jiān)控信息。
• wall：Druid防御SQL注入攻擊的WallFilter就是通過(guò)Druid的SQL Parser分析。Druid提供的SQL Parser可以在JDBC層攔截SQL做相應(yīng)處理，比如說(shuō)分庫(kù)分表、審計(jì)等。
• log4j2：這個(gè)就是 日志記錄的功能，可以把sql語(yǔ)句打印到log4j2 供排查問題。

2 添加依賴

pom.xml

????com.alibaba
????druid-spring-boot-starter
????1.1.23



????mysql
????mysql-connector-java
????runtime



????org.springframework.boot
????spring-boot-starter-log4j2




????org.mybatis.spring.boot
????mybatis-spring-boot-starter
????2.1.3
????
????????
????????
????????????com.zaxxer
????????????HikariCP
????????
????

3 配置相關(guān)屬性

配置Druid數(shù)據(jù)源（連接池）： 如同以前 c3p0、dbcp 數(shù)據(jù)源可以設(shè)置數(shù)據(jù)源連接初始化大小、最大連接數(shù)、等待時(shí)間、最小連接數(shù) 等一樣，Druid 數(shù)據(jù)源同理可以進(jìn)行設(shè)置；

配置 Druid web 監(jiān)控 filter（WebStatFilter）： 這個(gè)過(guò)濾器的作用就是統(tǒng)計(jì) web 應(yīng)用請(qǐng)求中所有的數(shù)據(jù)庫(kù)信息，比如 發(fā)出的 sql 語(yǔ)句，sql 執(zhí)行的時(shí)間、請(qǐng)求次數(shù)、請(qǐng)求的 url 地址、以及seesion 監(jiān)控、數(shù)據(jù)庫(kù)表的訪問次數(shù) 等等。

配置 Druid 后臺(tái)管理 Servlet（StatViewServlet）： Druid 數(shù)據(jù)源具有監(jiān)控的功能，并提供了一個(gè) web 界面方便用戶查看，類似安裝 路由器 時(shí)，人家也提供了一個(gè)默認(rèn)的 web 頁(yè)面；需要設(shè)置 Druid 的后臺(tái)管理頁(yè)面的屬性，比如 登錄賬號(hào)、密碼 等；

注意：

Druid Spring Boot Starter 配置屬性的名稱完全遵照 Druid，可以通過(guò) Spring Boot 配置文件來(lái)配置Druid數(shù)據(jù)庫(kù)連接池和監(jiān)控，如果沒有配置則使用默認(rèn)值。

application.yml

##########?配置數(shù)據(jù)源?（Druid）##########
spring:
??datasource:
????##########?JDBC?基本配置?##########
????username:?xxx
????password:?xxx
????driver-class-name:?com.mysql.cj.jdbc.Driver???#?mysql8?的連接驅(qū)動(dòng)
????url:?jdbc:mysql://127.0.0.1:3306/test?serverTimezone=Asia/Shanghai
????platform:?mysql???????????????????????????????#?數(shù)據(jù)庫(kù)類型
????type:?com.alibaba.druid.pool.DruidDataSource??#?指定數(shù)據(jù)源類型
????##########?連接池?配置?##########
????druid:
??????#?配置初始化大小、最小、最大
??????initial-size:?5
??????minIdle:?10
??????max-active:?20
??????#?配置獲取連接等待超時(shí)的時(shí)間(單位：毫秒)
??????max-wait:?60000
??????#?配置間隔多久才進(jìn)行一次檢測(cè)，檢測(cè)需要關(guān)閉的空閑連接，單位是毫秒
??????time-between-eviction-runs-millis:?2000
??????#?配置一個(gè)連接在池中最小生存的時(shí)間，單位是毫秒
??????min-evictable-idle-time-millis:?600000
??????max-evictable-idle-time-millis:?900000
??????#?用來(lái)測(cè)試連接是否可用的SQL語(yǔ)句,默認(rèn)值每種數(shù)據(jù)庫(kù)都不相同,這是mysql
??????validationQuery:?select?1
??????#?應(yīng)用向連接池申請(qǐng)連接，并且testOnBorrow為false時(shí)，連接池將會(huì)判斷連接是否處于空閑狀態(tài)，如果是，則驗(yàn)證這條連接是否可用
??????testWhileIdle:?true
??????#?如果為true，默認(rèn)是false，應(yīng)用向連接池申請(qǐng)連接時(shí)，連接池會(huì)判斷這條連接是否是可用的
??????testOnBorrow:?false
??????#?如果為true（默認(rèn)false），當(dāng)應(yīng)用使用完連接，連接池回收連接的時(shí)候會(huì)判斷該連接是否還可用
??????testOnReturn:?false
??????#?是否緩存preparedStatement，也就是PSCache。PSCache對(duì)支持游標(biāo)的數(shù)據(jù)庫(kù)性能提升巨大，比如說(shuō)oracle
??????poolPreparedStatements:?true
??????#?要啟用PSCache，必須配置大于0，當(dāng)大于0時(shí)，?poolPreparedStatements自動(dòng)觸發(fā)修改為true，
??????#?在Druid中，不會(huì)存在Oracle下PSCache占用內(nèi)存過(guò)多的問題，
??????#?可以把這個(gè)數(shù)值配置大一些，比如說(shuō)100
??????maxOpenPreparedStatements:?20
??????#?連接池中的minIdle數(shù)量以內(nèi)的連接，空閑時(shí)間超過(guò)minEvictableIdleTimeMillis，則會(huì)執(zhí)行keepAlive操作
??????keepAlive:?true
??????#?Spring?監(jiān)控，利用aop?對(duì)指定接口的執(zhí)行時(shí)間，jdbc數(shù)進(jìn)行記錄
??????aop-patterns:?"com.springboot.template.dao.*"
??????###########?啟用內(nèi)置過(guò)濾器（第一個(gè)?stat必須，否則監(jiān)控不到SQL）##########
??????filters:?stat,wall,log4j2
??????#?自己配置監(jiān)控統(tǒng)計(jì)攔截的filter
??????filter:
????????#?開啟druiddatasource的狀態(tài)監(jiān)控
????????stat:
??????????enabled:?true
??????????db-type:?mysql
??????????#?開啟慢sql監(jiān)控，超過(guò)2s?就認(rèn)為是慢sql，記錄到日志中
??????????log-slow-sql:?true
??????????slow-sql-millis:?2000
????????#?日志監(jiān)控，使用slf4j?進(jìn)行日志輸出
????????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關(guān)聯(lián)監(jiān)控的數(shù)據(jù)?##########
??????web-stat-filter:
????????enabled:?true???????????????????#?啟動(dòng)?StatFilter
????????url-pattern:?/*?????????????????#?過(guò)濾所有url
????????exclusions:?"*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"?#?排除一些不必要的url
????????session-stat-enable:?true???????#?開啟session統(tǒng)計(jì)功能
????????session-stat-max-count:?1000????#?session的最大個(gè)數(shù),默認(rèn)100
??????##########?配置StatViewServlet（監(jiān)控頁(yè)面），用于展示Druid的統(tǒng)計(jì)信息?##########
??????stat-view-servlet:
????????enabled:?true???????????????????#?啟用StatViewServlet
????????url-pattern:?/druid/*???????????#?訪問內(nèi)置監(jiān)控頁(yè)面的路徑，內(nèi)置監(jiān)控頁(yè)面的首頁(yè)是/druid/index.html
????????reset-enable:?false??????????????#?不允許清空統(tǒng)計(jì)數(shù)據(jù),重新計(jì)算
????????login-username:?root????????????#?配置監(jiān)控頁(yè)面訪問密碼
????????login-password:?123
????????allow:?127.0.0.1???????????#?允許訪問的地址，如果allow沒有配置或者為空，則允許所有訪問
????????deny:????????????????????????????????????????#?拒絕訪問的地址，deny優(yōu)先于allow，如果在deny列表中，就算在allow列表中，也會(huì)被拒絕

上述配置文件的參數(shù)可以在 com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties 和 org.springframework.boot.autoconfigure.jdbc.DataSourceProperties中找到;

3.1 如何配置 Filter

可以通過(guò) spring.datasource.druid.filters=stat,wall,log4j ...的方式來(lái)啟用相應(yīng)的內(nèi)置Filter，不過(guò)這些Filter都是默認(rèn)配置。如果默認(rèn)配置不能滿足需求，可以放棄這種方式，通過(guò)配置文件來(lái)配置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 提供了配置支持，根據(jù)（spring.datasource.druid.filter.*）進(jìn)行配置。

• StatFilter
• WallFilter
• ConfigFilter
• EncodingConvertFilter
• Slf4jLogFilter
• Log4jFilter
• Log4j2Filter
• CommonsLogFilter

不想使用內(nèi)置的 Filters，要想使自定義 Filter 配置生效需要將對(duì)應(yīng) Filter 的 enabled 設(shè)置為 true ，Druid Spring Boot Starter 默認(rèn)禁用 StatFilter，可以將其 enabled 設(shè)置為 true 來(lái)啟用它。

4 監(jiān)控頁(yè)面

（1）啟動(dòng)項(xiàng)目后，訪問/druid/login.html來(lái)到登錄頁(yè)面，輸入用戶名密碼登錄

（2）數(shù)據(jù)源頁(yè)面 是當(dāng)前DataSource配置的基本信息，上述配置的Filter可以在里面找到，如果沒有配置Filter（一些信息會(huì)無(wú)法統(tǒng)計(jì)，例如“SQL監(jiān)控”，會(huì)無(wú)法獲取JDBC相關(guān)的SQL執(zhí)行信息）

（3） SQL監(jiān)控頁(yè)面，統(tǒng)計(jì)了所有SQL語(yǔ)句的執(zhí)行情況

（4）URL監(jiān)控頁(yè)面，統(tǒng)計(jì)了所有Controller接口的訪問以及執(zhí)行情況

（5）Spring 監(jiān)控頁(yè)面，利用aop 對(duì)指定接口的執(zhí)行時(shí)間，jdbc數(shù)進(jìn)行記錄

（6）SQL防火墻頁(yè)面

druid提供了黑白名單的訪問，可以清楚的看到sql防護(hù)情況。

（7）Session監(jiān)控頁(yè)面

可以看到當(dāng)前的session狀況，創(chuàng)建時(shí)間、最后活躍時(shí)間、請(qǐng)求次數(shù)、請(qǐng)求時(shí)間等詳細(xì)參數(shù)。

（8）JSONAPI 頁(yè)面

通過(guò)api的形式訪問Druid的監(jiān)控接口，api接口返回Json形式數(shù)據(jù)。

5 sql監(jiān)控

配置 Druid web 監(jiān)控 filter（WebStatFilter）這個(gè)過(guò)濾器，作用就是統(tǒng)計(jì) web 應(yīng)用請(qǐng)求中所有的數(shù)據(jù)庫(kù)信息，比如 發(fā)出的 sql 語(yǔ)句，sql 執(zhí)行的時(shí)間、請(qǐng)求次數(shù)、請(qǐng)求的 url 地址、以及seesion 監(jiān)控、數(shù)據(jù)庫(kù)表的訪問次數(shù) 等等。

spring:
??datasource:
????druid:
??????##########?配置WebStatFilter，用于采集web關(guān)聯(lián)監(jiān)控的數(shù)據(jù)?##########
??????web-stat-filter:
????????enabled:?true???????????????????#?啟動(dòng)?StatFilter
????????url-pattern:?/*?????????????????#?過(guò)濾所有url
????????exclusions:?"*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"?#?排除一些不必要的url
????????session-stat-enable:?true???????#?開啟session統(tǒng)計(jì)功能
????????session-stat-max-count:?1000????#?session的最大個(gè)數(shù),默認(rèn)100

6 慢sql記錄

有時(shí)候，系統(tǒng)中有些SQL執(zhí)行很慢，我們希望使用日志記錄下來(lái)，可以開啟Druid的慢SQL記錄功能

spring:
??datasource:
????druid:
??????filter:
????????stat:
??????????enabled:?true?????????#?開啟DruidDataSource狀態(tài)監(jiān)控
??????????db-type:?mysql????????#?數(shù)據(jù)庫(kù)的類型
??????????log-slow-sql:?true????#?開啟慢SQL記錄功能
??????????slow-sql-millis:?2000?#?默認(rèn)3000毫秒，這里超過(guò)2s，就是慢，記錄到日志

啟動(dòng)后，如果遇到執(zhí)行慢的SQL，便會(huì)輸出到日志中

7 spring 監(jiān)控

訪問之后spring監(jiān)控默認(rèn)是沒有數(shù)據(jù)的；

這需要導(dǎo)入SprngBoot的AOP的Starter

????org.springframework.boot
????spring-boot-starter-aop

需要在 application.yml 配置:

Spring監(jiān)控AOP切入點(diǎn)，如com.springboot.template.dao.*,配置多個(gè)英文逗號(hào)分隔

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

8 去 Ad（廣告）

訪問監(jiān)控頁(yè)面的時(shí)候，你可能會(huì)在頁(yè)面底部（footer）看到阿里巴巴的廣告

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

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

(1) 直接手動(dòng)注釋這段代碼

如果是使用Maven，直接到本地倉(cāng)庫(kù)中，查找這個(gè)jar包

要注釋的代碼：

//?this.buildFooter();

common.js的位置：

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

(2) 使用過(guò)濾器過(guò)濾

注冊(cè)一個(gè)過(guò)濾器，過(guò)濾common.js的請(qǐng)求，使用正則表達(dá)式替換相關(guān)的廣告內(nèi)容

@Configuration
@ConditionalOnWebApplication
@AutoConfigureAfter(DruidDataSourceAutoConfigure.class)
@ConditionalOnProperty(name?=?"spring.datasource.druid.stat-view-servlet.enabled",
havingValue?=?"true",?matchIfMissing?=?true)
public?class?RemoveDruidAdConfig?{

????/**
????*?方法名:?removeDruidAdFilterRegistrationBean
????*?方法描述?除去頁(yè)面底部的廣告
????*?@param?properties?com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties
????*?@return?org.springframework.boot.web.servlet.FilterRegistrationBean
????*/
????@Bean
????public?FilterRegistrationBean?removeDruidAdFilterRegistrationBean(DruidStatProperties?properties)?{

????????//?獲取web監(jiān)控頁(yè)面的參數(shù)
????????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";

????????//創(chuàng)建filter進(jìn)行過(guò)濾
????????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);
????????????????//?重置緩沖區(qū)，響應(yīng)頭不會(huì)被重置
????????????????response.resetBuffer();
????????????????//?獲取common.js
????????????????String?text?=?Utils.readFromResource(filePath);
????????????????//?正則替換banner,?除去底部的廣告信息
????????????????text?=?text.replaceAll("
",?"");
????????????????text?=?text.replaceAll("powered.*?shrek.wang",?"");
????????????????response.getWriter().write(text);
????????????}

????????????@Override
????????????public?void?destroy()?{}
????????};

????????FilterRegistrationBean?registrationBean?=?new?FilterRegistrationBean();
????????registrationBean.setFilter(filter);
????????registrationBean.addUrlPatterns(commonJsPattern);
????????return?registrationBean;
????}
}

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

9 獲取 Druid 的監(jiān)控?cái)?shù)據(jù)

Druid 的監(jiān)控?cái)?shù)據(jù)可以在 開啟 StatFilter 后，通過(guò) DruidStatManagerFacade 進(jìn)行獲取;

DruidStatManagerFacade#getDataSourceStatDataList 該方法可以獲取所有數(shù)據(jù)源的監(jiān)控?cái)?shù)據(jù)，

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

@RestController
@RequestMapping(value?=?"/druid")
public?class?DruidStatController?{

????@GetMapping("/stat")
????public?Object?druidStat(){
????????//?獲取數(shù)據(jù)源的監(jiān)控?cái)?shù)據(jù)
????????return?DruidStatManagerFacade.getInstance().getDataSourceStatDataList();
????}
}